SQL Server Analysis Services Database Deployment




By:
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.
Sample XMLA script generated from SQL Server Management Studio for a SSAS database
  • 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.

Last Update: 4/7/2016




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools