SQL Server Analysis Services Database Deployment

By: Siddharth Mehta

Overview

Deploying databases and database objects across different production and non-production environments is one of the most fundamental and day-to-day tasks performed by a SQL Server Analysis Services (SSAS) administrator. Generally in development environments, development teams develop the SSAS project and create SSAS database as well as database objects on the SSAS Development server. Once the code is ready, typically an administrator takes it from there and deploys the code to higher environments. In this chapter we would look at different methods of deploying SSAS databases.

Explanation

In general, there are five standard methods of deploying SSAS databases which are mentioned below.

• XMLA Scripts - Deployment using scripts is one of the most common methods used by developers as well as admins in the SQL Server world. In SSAS instead of T-SQL Scripts, XMLA scripts get generated for database objects. The same scripts can be used for deployment. Right-click on the target database to be scripted in the object explorer window of SSMS, and select "Script database as" option to generate a Create / Alter / Delete script. This script can be saved to a file / query editor window and can be executed on the target server and/or database. The below screenshot shows an excerpt of a script generated for the AdventureWorks SSAS DB installed on my machine which has more than 61,000 lines of code. So when the script gets generated, we need to be patient if it takes some time.
  

• Deployment Wizard - As a standard development practice, a SSAS solution is generally version controlled using Team Foundation Server (TFS). All the solution objects are generally checked into TFS and an admin is also provided access to the source control system. Using the files contained in the bin folder of the SSAS solution, the Deployment Wizard can be used to deploy the SSAS database. A detailed explanation of using SSAS Deployment Wizard can be read from this tip.
  
  
• Synchronize Wizard - This wizard facilitates synchronization of database objects from one server to another. We will discuss this topic in the next chapter.
  
  
• Backup / Restore - As with any database environment, backup and restore is a standard and routine process, and can be used as a deployment option. We will discuss this option in upcoming chapters.
  
  
• AMO Automation - Highly experienced developers / administrators can use Analysis Management Objects (AMO) which is an SDK interface to programmatically administering SSAS, for deploying SSAS databases. Arguably, admins use this option as their last choice due to its complexity, though it provides maximum flexibility and control.

Additional Information

• XMLA Scripting is a relatively new concept for admins who are new to the SSAS world. Consider reading this tip to learn more on XMLA.