Migration for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

Deployment migration

Migration is taking a solution from a development environment and bringing it to a production environment. This typically will involve three environments and two migration phases:

Migrate from development to test.
Migrate from test to production.

Development

Development of the solution can typically be done on a single server that uses all the required services. This includes Microsoft SQL Server and Microsoft SQL Server 2008 Analysis Services (SSAS) installed locally. This is ideal as typically in development you want the following:

Full control on the environment for fast turnaround on issues that are not specifically related to solution development.
Simplified server setups.
Focus on solution design and requirements.

Test

The main purpose of the test environment is to validate the design in the development environment on an environment that will most closely match the production environment. The test environment will focus on the following:

Business logic validation
Performance testing
Security and access control

Production

The production environment is the go-live environment that will be accessed by the end consumers of the solution. This environment should have backups and redundancy built in so that there is no loss any significant data from IWs.

Migration of the planning solution will involve moving and reconfiguring the following assets from a source environment and into a target environment:

Planning database on SQL Server
Planning database on SQL Server Analysis Services
Excel form templates on Microsoft SharePoint Server 2010
ETL packages built by using Microsoft SQL Server Integration Services (SSIS)

Relational database

Migration of the relational database can be performed by using a simple backup and restore process. Make a backup file of the SQL Server database by using SSMS:

The backup file can then be used to restore the database to the target environment’s SQL Server. Once the database is restored on the target environment, we can start with the restore of the multidimensional database.

Multidimensional database

Migration of multidimensional database can also be performed by using a backup and restore process similar to the relational database. Considerations on post database restore include the following:

Update role based security
Update data sources

Update role-ased security

Security is typically configured differently between the various migration environments. For instance, in the development environment, there will be many more expert users who have the permission to process and alter the multidimensional database than compared to that in a production environment. When setting up security, it is a good idea to consider the necessary security rights that are required for users within each migration environment and to remove any roles that are not relevant for that environment.

Development environment security

It is recommended to create a single SQL Server Analysis Services role that will contain all the expert users for the development environment. This development role will have permissions that are elevated over most other roles as measured by interactivity with the multidimensional database. For example database processing permissions and the ability to create/alter/remove database objects will be granted here. Creating a single role will let the administrator easily remove or update users in the role as it is moved from one environment to another. For improved manageability, use Active Directory groups when you are adding membership to the role. This will let IT to manage security without accessing into SQL Server Analysis Services at all.

Test environment security

In the test environment, our goal is to emulate the security setup that should be found in a production environment. When setting up security, there are some considerations to take. Depending on the complexities of the security requirements, we can have:

Role created based on each secured slice of data.
Role per IW, each that contains their own security definition.

For the most part, it is acceptable to have the development role available in the test environment. Also, for users who will only exist in the test environment for user acceptance testing (UAT), we recommend that you create separate roles for them and have them be clearly identifiable. When moving to production, development and test roles must be removed.

Production environment security

The production environment should not contain any development or test roles. Remove these roles after a restore of the multidimensional database when in a production environment. Ensure that the security roles that are set up are only for IWs that should have access to the production environment.

Update data sources

On the multidimensional database, make sure that you update the connection string property for the data source to point to the target environment’s SQL Server.

Ensure that the SQL Server Analysis Services server has access to the relational database server. To test that everything is configured correctly, perform a database process to reload multi-dimensional database.

Excel form templates

Transfer form templates from the SharePoint document library into another library created for the targeted migration environment. Update the connection string properties for the data connections used by the PivotTables and point them to the target environment’s multi-dimensional database.

ETL packages

Packages written for data transfer between source systems and the planning solution may have to be updated as we move the packages from a source to target environment. Depending on how the configuration properties are set up within the SSIS packages, updates to the connection strings may be required by working with the following:

XML configuration file
Environment variable
Registry entry
Parent package variable
SQL Server

For more information about how to manage connection strings in SSIS packages, see Understanding Integration Services Package Configurations.

For production and test environments, we recommend that you have the following four server configuration. The critical server that require being on stand-alone computer is the SQL Server Analysis Services OLAP server. For SQL Server Analysis Services based planning solutions, the core bottleneck will typically be from the SQL Server Analysis Services server as it is primarily responsible for interactivity between the multidimensional data model and Excel 2010 PivotTables. This includes answering all what-if and other ad-hoc queries. In addition, many calculations will also be evaluated dynamically by the SQL Server Analysis Services server when responding to query requests. Thus, it is typical to require both high computational capability and large memory allocation for the SQL Server Analysis Services server. However, the exact server configuration will depend on the complexity of the solution and its usage requirements by the IWs of the system.

Migration for BI planning solutions and scenarios

Deployment migration

See Also

Concepts

Additional resources