Skip to main content

Automate MSBI deployment using deployment framework


Microsoft BI tools are widely used in Business Intelligence (BI) projects nowadays due to their end to end BI capabilities and lower licensing costs. We work in an agile world where the application teams are making use of various tools like Jenkins to speed up and automate their deployments, whereas we see that BI teams still follow a more traditional approach for deploying BI artifacts. We can get out of this rut by leveraging the excellent compatibility between the MSBI deployment utilities and PowerShell.
The traditional MSBI stack comprises following tools:
  • SSIS: an ETL tool for moving data between systems. This stands for SQL Server Integration Services.
  • SSAS: an analysis tool for building cubes. This stands for SQL Server Analysis Services.
  • SSRS: a reporting tool for displaying and exporting data. This stands for SQL Server Reporting Services.
There are various ways we can deploy the code developed in above tools. This article will give an idea to deploy the artifacts developed in above tools from a single place. This article should not take more than 20 minutes to go through for a reader having some knowledge of MSBI tools. 

Tools and Technologies
  • SQL Server
  • MSBI 2012 or above
  • PowerShell ISE

Prerequisites

First, we must enable PowerShell execution on the server where you want to configure the deployment automation framework. To enable it, go to the Start Menu and search for "Windows PowerShell ISE". Right click and choose Run as administrator. In the top part, paste this code:
Set-ExecutionPolicy Unrestricted
Now run the script. Choose "Yes" in response to the prompts.
Good to Have
Enable the Check for object existence option in SQL Server Management Studio (SSMS). This can avoid syntax errors in the script in the case where the object is already present on target server and we are trying to deploy the updated version of it. To enable this, go to SSMS and click on the Tools menu. Then select Options and expand the SQL Server Object Explorer selection in the dialog. Select the Scripting option, then set "Check for object existence" to True. This needs to be enabled on each developer's machine who will be creating SQL scripts for deployment.









About the Deployment framework

Assuming that the project is being delivered in agile way, we might have following things to be deployed in every sprint/release.
  1. SQL Server scripts- There would be various SQL objects to be deployed in every release. The scripts could be for a database or for various databases on different servers.
  2. SSIS Builds- Considering that we are working in the SSIS project deployment model, where the deployment unit is a project (.ispac) file. The projects would be deployed to Integration services catalogs.
  3. SSRS reports (Native mode) - SSRS reports to be deployed on report server in certain folder. Reports might have shared data sources or datasets
  4. SSRS reports (SharePoint integrated mode) - SSRS reports to be deployed on SharePoint site in certain folder. Reports might have shared datasources or datasets.
Now, let's proceed towards one time configuration of the deployment automation framework. Follow the below steps to configure the PowerShell deployment framework for MSBI related deployments.
  1. Create a deployment folder (Deployments) anywhere on the development server. If we are using any source control tool (TFS, SVN, etc.), it is advisable to create the folder in the source control system. We can give any name to this folder.
  2. Download the attachment MSBI Deployment Framework.zip from the Resources files section of this article.
  3. Extract MSBI Deployment Framework.zip file and copy the contents (PS scripts and BuildFolderTemplate) from the extracted folder to the Deployments folder created in step1.
The folder would look something like the image below






Now, we need to do one time configuration and credentials settings for the PowerShell scripts to run. Open the "PS scripts" folder, and we will see various folders inside of it.






The Databases folder

The Databases folder will have a folder for each SQL Server databases that need to be configured for deployments. Each database specific folder will have its own PowerShell script. For simplicity, the name of the database specific folder could be same as the database name. If we have two databases of the same name on different servers, we can give a more qualified name (ServerName_DatabaseName) to the folder as well as the underlying PowerShell script in order to distinguish them.
The "PS scripts\Databases" folder has only one sample database folder along with the sample PowerShell script. If we have multiple SQL Server databases to be configured for deployments, we just need to make a copy of the sample folder (SampleDB) and rename the folder along with the underlying PowerShell script with the database name. E.g. if we have 3 databases, with names ActualDB1, ActualDB2 and ActualDB3, the Databases folder would look like the below image.






Open each database specific folder and edit the PowerShell scripts for configuring the connection details. Edit only the param section as shown below.
#Param
###########################################################################################
$server = "SQL Server Instance Name"  ## mandatory
$dbname = "Database Name"  ## mandatory
$sqlserveruserName = "sqlserveruser"       
$sqlserverPassword = "passwordforaboveuser"      
###################################################################################
If we want to use Windows Authentication to connect to the database, the $sqlserverUserName and $sqlserverPassword variable values need to be removed along with "=" sign. Save the script. We need to repeat the same process for all the SQL Server databases which need to be configured for deployments.

The SSIS Packages Folder
The SSIS Packages folder has PowerShell script to deploy SSIS builds (.ispac files) to SQL Server Integration Services Catalogs. We need to edit the param section of SSIS packages.ps1 script .
#Param
#############################################################################################
$ServerName = "SQL Server Instance Name"
$SSISCatalog = "SSISDB"
#############################################################################################
$ServerName is the SQL Server Instance name where we need deploy SSIS builds (.ispac) files. 
$SSISCatalog is the Integration Services Catalog name. It is usually SSISDB.
Note: The logged in user or the user who will be running the scripts should have permission to deploy SSIS projects (.ispac files) to Integration Services Catalogs.

The Report server reports Folder

The Report server reports folder has a PowerShell script (Report server reports.ps1) to deploy the reports to the report server (SSRS configured in native mode). Edit the param section of the script with the environment specific details.
#Param
#####################################################################################################
##Report server uri.
$reportServerUri = "http://<Server Name>/ReportServer/ReportService2010.asmx"
##Shared data source parent folder path - relative path
$DatasourceLibrary = "/path of shared datasource library/"
##Shared Datasets parent folder path
$DatasetLibrary = "/path of shared dataset library/"
##Set credentials that need to be used to deploy the reports to report server. 

##This need not be necessary same as that of the logged in user.
$secpasswd = ConvertTo-SecureString “password for domain\username" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential (“domain\username”, $secpasswd)
#####################################################################################################
$reportServerUri is the report server uri. Locate the report server URI from Reporting Services Configuration manager.
$DatasourceLibrary is the relative path the shared datasource library on the report server. This will be used to set shared datasource paths in each SSRS report if applicable.
$DatasetLibrary is the relative path the shared datasets library on the report server. This will be used to set shared dataset paths in each SSRS report if applicable.

The SharePoint reports Folder

The "SharePoint reports" folder has a PowerShell script (SharePoint reports.ps1) to deploy the reports to the SharePoint server (SSRS configured in SharePoint integrated mode). Edit the param section of the script with the environment specific details.
#Param
#####################################################################################################
##Report server uri.
$reportServerUri = "http://<Server Name>/<Site Name>/_vti_bin/ReportServer/ReportService2010.asmx"

##Specify report/sharepoint server parent library path where we need to deploy the report
$targetParentreportLibrary = "http://<Server Name>/" 

##Shared data source parent folder path
$DatasourceLibrary = "http://<Server Name>/Shared Data Soource Folder Path/"

##Shared Datasets parent folder path
$DatasetLibrary = "http://<Server Name>/Shared Data Sets Folder Path/"

##Set credentials that need to be used to deploy the reports to report server. 

##This need not be necessary same as that of the logged in user.
$secpasswd = ConvertTo-SecureString “password for domain\username" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential (“domain\username”, $secpasswd)
#####################################################################################################
$reportServerUri is the report server uri. Locate the report server URI from Reporting Services Configuration manager.
$TargetParentreportLibrary is the path of the parent folder on SharePoint. 
$DatasourceLibrary is the full path the shared datasource library on the SharePoint server. This will be used to set shared datasource paths in each SSRS report if applicable.
$DatasetLibrary is the full path the shared datasets library on the SharePoint server. This will be used to set shared dataset paths in each SSRS report if applicable.

The Build Folder

Now, it's time to configure the BuildFolderTemplate. Below are some quick guidelines.
The BuildFolderTemplate folder that has been extracted from the attachment is the standard folder. Notice that the folders present in the BuildFolderTemplate have exactly the same names as that of the folders present in the "PS scripts" folder.








Make the below generic changes to the BuildFolderTemplate folder.
Databases - Databases folder in BuildFolderTemplate will have one folder corresponding to each database that has been configured in the "PS scripts" folder inside the Databases folder. These database specific folders need to be kept blank in BuildFolderTemplate folder. However, when we will be creating a build folder from this template folder, we will keep .sql files corresponding to each databases in respective database named folder. Databases folders in "PS scripts" and BuildFolderTemplate would look something like the below image.






SSIS packages - The "SSIS Packages" folder in BuildFolderTemplate will contain folders that are present in Integration Services Catalogs (SSISDB). These folders need to be kept blank in the template.  However, when we will be creating a build folder from this template folder, we need to place SSIS (ispac) build files in respective folders where we need to deploy them to SSIS Catalog on server in actual deployment.
Report server reports - The "Report server reports" folder in BuildFolderTemplate will contain the complete folders hierarchy from the report server. These folders need to be kept blank in the template. However, when we will be creating a build folder from template folder, we will need to keep reports at the folder location in exactly similar folder where you need to deploy reports to report server. 






 Note: If the reports that need to be deployed using this framework has some shared datasources or datasets, the names given in report for such datasources or datasets should be exactly same as that of the used shared object. All the shared objects (datasources or datasets) should be in some common folder (Data Sources as in the above image) on the report server.
SharePoint reports - As stated above in Report server reports, we need to create similar folders as present on SharePoint folder. To get assistance on folder names to be created, login the SharePoint portal and browse various folders and get the folder name from the url. This is one time activity so might take a bit time.








Note: If the reports that need to be deployed using this framework has some shared datasources or datasets, the names given in report for such datasources or datasets should be exactly same as that of the used shared object. 

Validate the Setup

We should validate the configuration done so far, before configuring the build for deployment. Follow the below steps to validate the setup.
First, edit Caller.ps1 present in the "PS scripts" folder and change the variable $DeploymentFolderName to BuildFolderTemplate
#Please give the deployment folder
$DeploymentFolderName = "BuildFolderTemplate"
Double click Run.bat file and it should open the command prompt like below.








If the command prompt shows all green, congratulations!! We have done a great job so far.

Prepare the Build

Now, it's time to setup the build from the BuildFolderTemplate for a specific release. The typical build for a release may have .sql files to be deployed on various SQL Server databases, .rdl files to be deployed on Report server or SharePoint server, SSIS build (.ispac) files etc. Follow these simple steps to do that.
  1. Make a copy of the BuildFolderTemplate in the same folder where you have done the framework setup. Rename the copied folder with the name of the release. For simplicity, name it as per the data of release, e.g. DD.MM.YYYY.
  2. Now, place the deployable (.sql files, .rdl files or .ispac files etc) in appropriate folders of the build DD.MM.YYYY as stated in above steps.
The build folder would like below image.






Deploy the Build

First, edit Caller.ps1 present in "PS scripts" folder. Change the variable $DeploymentFolderName value to the name of the build we want to deploy (the folder name).
#Please give the deployment folder
$DeploymentFolderName = "BuildFolderTemplate"
Now that we have configured the build for deployment, double click the Run.bat file, and it should deploy the deployable to appropriate servers as per the configuration.

Review the Deployment Log

A deployment log gets created in the PS scripts folder, as shown in above image. No matter what the command prompt shows, it is advisable to review the deployment log after every deployment in order to ensure that all the objects have been deployed as expected.
In case of failure(s), we can find out the detailed reason of failure from the log file. And after corrective action taken, we can re-run the build again.

Deployment of the Builds in Different Environments.

We need to do one time similar configuration of "PS scripts" in all the other environments (TEST, UAT, PROD) as what we have done for DEV. Assuming that all the environments are parallel (same database names, report server folder structures, SharePoint folder structure and SSIS Catalog folder structures across all environments), the build that we have created in DEV, can be deployed to TEST, UAT or PROD without any future changes to build  DD.MM.YYYY. We just need to copy the build from DEV to other environment at deployment location. 

Limitations.

  1. Long running .sql files, e.g. index creation scripts, might produce timeout error in PowerShell. For such scripts, it is recommended to run them manually.
  2. The SSIS package deployment model deployment is not covered in this framework.
  3. The setting of environments in Integration Services Catalogs and assigning environment variables configuration to SSIS projects in the catalog is not considered in this framework. For the first time, we need to do this activity manually for every new SSIS build deployed. Subsequent upgrades to the SSIS build will not require any changes to the configuration unless there is any configuration upgrade
  4. Deployment of shared datasources and datasets used in SSRS reports is not covered in this framework.
  5. SSAS project deployment is not covered in this framework.

Download the framework

Please ignore the warning during the download of the file. Its because of the PowerShell files in zip folder. 

Please feel free to share your feedback or suggestions on this deployment methodology.

Comments

Post a Comment