The fourth segment in this series focuses on using XMLA to deploy databases. When you create SQL Server Analysis Services databases in Visual Studio, you often use target development server to validate the design and data with. This blog will focus on moving that development database to other environments and the required steps to make this work.
Generating the Create Database Script
The first step is the easiest. At this point, we are assuming that you have created a development database and have deployed it. Generating a create script at this point is fairly straightforward. Using the database shortcut menu, select Script Database as … CREATE To … New Query Editor Window as shown below.
This will in a CREATE script with all the metadata from the development database.
Changes Required during Initial Deployment Process
Before we move much further, a key point from the first section is that all the metadata from the development database is in this script. There are two areas that need to be updated as this script is deployed to different environments.
Data Sources. The data sources that are in the script currently point to development data. In most cases, these will need to be updated to reflect the data sources used in the target environment. For instance, you normally do not use the production data servers when developing cubes and unit testing the solution due to the performance impact on those servers. Furthermore, many database administrators hold the connection information that is required to connect to those sources and will not give that information to developers.
Role Membership. While we will create the roles in the development environment, we usually use development users and groups to test the security. Once deployed, this membership will need to be updated to the correct users and groups for the target environment.
Then next two sections describe the two methods for handling these changes. You may find a blended approach works for you as well depending on the target environment and your policies.
Applying Required Changes Post-deployment
First, you can apply these changes after the database has been deployed. To do this, simply run the XMLA script while connected to the server in the environment you are targeting.
After running the script you can use SQL Server Management Studio to update the data sources and role members. As shown below, in order to update the data sources, you will need to update each data sources connection string property to match the credentials and database used in that source.
You will go through a similar process with the role membership. In this case you will go to the Properties window for each Role and Add or Remove users and groups to match the current environment.
Once this is complete, you will be able to process the cube with the current data and make sure the correct users are set up. Just a note though, I would not process the database until the users have been set up as you will then have a database with data you did not intend to make available to developers.
Applying Required Changes Pre-deployment
The other method is to update the script prior to deployment. In this case you will be modifying the generated script and which will not require additional changes once applied.
To update the data sources you need to search for the <DataSources> element and manually update the connection string for each data source. Here is a sample of the XMLA you will be updating.
2: <DataSource xsi:type="RelationalDataSource">
3: <ID>Adventure Works DW</ID>
4: <Name>Adventure Works DW</Name>
5: <ConnectionString>Provider=SQLNCLI10.1;Data Source=ServerName;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008R2</ConnectionString>
Next, you need to search for the <Roles> element. You will be updating the <Member> elements for each role you need to update. The primary issue with this edit is that you may need to know the Active Directory SID for each user or group you need to add as a member. The XMLA sample below shows what you will be updating.
Check out the following blog entry from Derek Newton ( T | B ) for using PowerShell to retrieve an SID from AD: http://dereknewton.com/2010/11/finding-an-active-directory-users-sid-using-powershell/
Altering the Database
Usually you will not update an entire database except during the early development cycle. If you make enough changes you may find it beneficial to update the entire database as well. The data source and role member changes have been described above. However, be aware that when altering a database only the metadata in the script will be applied. The biggest area of concern is if you dynamically add partitions and aggregations to your database that have not been applied into the development environment. If you proceed to execute the Alter script without taking this into account you will lose all of that functionality. As your database becomes more mature, you will likely find that targeted changes are easier to manage and have much less risk. If you need to update the entire database, you should be sure to add the partitions, aggregations, and any other changes you may have made in production to the development database so your script contains the correct items. I will discuss partition management in an upcoming blog in this series and will have some other ideas there that can help.