At the end of a phase of a business intelligence project, when it has been accepted and it is time to move into production, comes the deployment on the production environment. The deployment itself should also be testable, reproducible and of course be completely debugged. The method largely depends on the customer’s IT team’s way of working. Some will insist on Installshield executables, some will prefer moving a server from acceptance to deployment, some will restore backups, some will work script based. I prefer that last approach and go into some detail on how to do this for all the typical parts of a MS BI solution: the database, integration services packages, SQL Agent jobs and the SSAS cubes.
Deploying the Database Contents via Script
The contents of the database consists of:
- a) database objects: tables, views, indexes, stored procedures…
- b) the contents of fixed dimensions (e.g. the time dimension), mapping tables, …
- c) special contents for dynamic dimensions (e.g. special “unknown” elements)
a) Scripting database objects is simple: use SQL Server Management Studio. Right-click on the database, select Tasks then Generate Scripts: this launches a wizard. Do not check “script all objects”. In the options table set “Script Drops” to true and “Script Indexes” to true (it defaults to false – duh!). You will probably not want to deploy logins so make sure it is set to false. Next steps of the wizard are self-explanatory. At the end you have a script that will generate all the selected database objects. Save it as DWH_DB_CreateAll.sql.
b) Scripting table contents is less obvious.
I have adapted a T-SQL script written by Nigel Rivett (original is here) to output insert statements given a list of tables. If its output is saved as a file (an option in SQL Server Management Studio) it creates all the inserts for every record in the given table list.
It adds IDENTITY INSERT statements where necessary, correctly handles NULLs and adds enough GO statements so that the script will still execute even if it gets very long. (I have made a 40+ Mb script with it!)
Download the source of the stored procedure sp_CreateDataLoadScript here.
Example of usage:
DECLARE @databaseName varchar(128) DECLARE @TblNames varchar(max) SET @databaseName='DWH_DB' SET @TblNames='Z_Packages,Z_Parameters,Z_SQLStatements,Z_Zones' EXECUTE [DWH_DB].[dbo].[sp_CreateDataLoadScript] @databaseName, @TblNames
Its output looks like:
use DWH_DB go set nocount on /* Z_Packages */ truncate table Z_Packages insert Z_Packages ( [PackageID],[PackageName] ) select 1,'PckgA' insert Z_Packages ( [PackageID],[PackageName] ) select 2,'PckgB' ... go /* Z_Parameters */ truncate table Z_Parameters insert Z_Parameters ( [ParameterName],[ParameterValue],[Comments] ) select 'PrmA','1','PrmA comment' ...
Save your output as DWH_DB_InitialContents.sql.
c) Adding special initial contents is again simple: these are mere insert statements, either created by hand or using the same procedure as in b). If for example there is a product dimension that will be fed by some source system and you want to link the unmatched sales values to a specific element you will need that element in the dimension table:
/* H_DimProduct */ truncate table H_DimProduct insert H_DimProduct ( [ProductID],[ProductCode],[ProductName],[RunID],[RunUpdID] ) select -1,'UNKNOWN','Unknown',0,null go
Save these statements in a file DWH_DB_InitialExceptions.sql.
The 3 SQL scripts thus created can be called from one batch script. Save the following as CreateAndLoadDatabaseObjects.bat:
set DBSERVER=myserver sqlcmd -S %DBSERVER% -i DWH_DB_CreateAll.sql sqlcmd -S %DBSERVER% -i DWH_DB_InitialContents.sql sqlcmd -S %DBSERVER% -i DWH_DB_InitialExceptions.sql
Calling CreateAndLoadDatabaseObjects.bat will (re-)create and load all necessary database objects.
You may want to add another script: one with GRANT EXECUTE statements should your security setup require this.
Deploying SSIS packages via Script
Packaged can be deployed to the MSDB database.
The configuration to deploy in this example consist of 3 SSIS packages, PackageA, PackageB and PackageC, to be put in the MSDB folder ProjectX on the server TheServer. The sources of the SSIS packages, i.e. the dtsx-files, are located on Z:\Sources\ProjectX.
@echo off rem CHANGE THE FOLLOWING 3 VARIABLES ACCORDING TO DEPLOYMENT NEEDS rem set DESTSERVER=TheServer set DESTLOCATION=projectX set SOURCEFOLDER=Z:\Sources\ProjectX @echo checking existence of %DESTLOCATION% dtutil /SOURCESERVER %DESTSERVER% /FE SQL;%DESTLOCATION% if ERRORLEVEL 1 GOTO CREATEDEST @echo ok goto INSTALLPACKAGES :CREATEDEST dtutil /SOURCESERVER %DESTSERVER% /FC SQL;\;%DESTLOCATION% if ERRORLEVEL 1 GOTO ERRCREATE @echo %DESTLOCATION% created goto INSTALLPACKAGES :ERRCREATE @echo could not create %DESTLOCATION% - batch stopped goto DONE :INSTALLPACKAGES set SSISPACKAGE=packageA @echo deploying %SSISPACKAGE% to %DESTSERVER%/%DESTLOCATION% dtutil /FILE %SOURCEFOLDER%\%SSISPACKAGE%.dtsx /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%SSISPACKAGE% /Quiet if ERRORLEVEL 1 GOTO ERR set SSISPACKAGE=packageB @echo deploying %SSISPACKAGE% to %DESTSERVER%/%DESTLOCATION% dtutil /FILE %SOURCEFOLDER%\%SSISPACKAGE%.dtsx /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%SSISPACKAGE% /Quiet if ERRORLEVEL 1 GOTO ERR set SSISPACKAGE=packageC @echo deploying %SSISPACKAGE% to %DESTSERVER%/%DESTLOCATION% dtutil /FILE %SOURCEFOLDER%\%SSISPACKAGE%.dtsx /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%SSISPACKAGE% /Quiet if ERRORLEVEL 1 GOTO ERR @echo All done goto DONE :ERR @echo Error deploying package %SSISPACKAGE% - batch stopped. ONE @pause
Dowload the above code here.
If you would also want to script the SQL Agent job that runs the packages, then things get trickier.
You will have to:
- create a credential to use for the proxy that will run the job (this cannot be scripted, I believe);
- use a script to create the proxy and grant it the right to launch SSIS packages:
USE [msdb] GO EXEC msdb.dbo.sp_add_proxy @proxy_name=N'ProjectX_Writer_proxy',@credential_name=N'ProjectX_Writer', @enabled=1 exec sp_grant_proxy_to_subsystem @proxy_name = N'ProjectX_Writer_proxy', @subsystem_id=11
- use a script to create the SQL Agent job: start with an existing job and script that via the Management Studio. Next edit it to make it server independent. Remember that the SSIS packages contain Datasources and you probably want to overrule these in the job! Something along this line:
set @cmnd=N'/DTS "\MSDB\ProjectX\PackageA" /SERVER "' + @@servername + N'" /CONNECTION Datawarehouse;"Data Source=' + @@servername + N';Initial Catalog=ProjectXDB;Provider=SQLNCLI.1;Persist Security Info=False;Integrated Security=SSPI;Auto Translate=false;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E' EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run PackageA', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'SSIS', @command=@cmnd, @database_name=N'master', @flags=0, @proxy_name=N'ProjectX_Writer_Proxy'
The above code assumes the SSIS server and database server from which the package reads its data are the same, which is probably true in 99.99% of the cases. There are possibly multiple connections in a package, e.g. a connection to a relational database and a connection to analysis services. Review them and overrule where necessary.
First script the SSAS database via the Management Studio wizard (Script database as… / CREATE To / File …) The result is an XMLA file.
Inside the XMLA file is the connection string that tells the cube(s) where to get their data from. When deploying you will probably want these to point to the correct environment. To do this, we will replace the servername and password with a ascmd variable.
Open the XMLA file and look for an entry <ConnectionString>.
Replace the server name with a variable $(AS_Server). The connection string will look something like:
Provider=SQLNCLI.1;Data Source=$(AS_Server);Integrated Security=SSPI;Initial Catalog=ProjectXDB
The next entry in the XMLA file is the impersonation info. Normally there is no password information there. Add a line with a variable that refers to the password:
ImpersonateAccount MyDomain\ProjectX_Reader $(ProjectXReaderPass)
The modified XMLA will server as input to the ascmd.exe utility.
The last step is the batch file that actually calls this utility and sets database and password (you might as well script the username).
The contents of the batch file DeployCube.bat:
set AS_SERVER=TheServer ascmd -S %AS_SERVER% -i ProjectXCube.xmla -v AS_Server="%AS_SERVER%" DWHReaderPass="123foobar"
At the end of all this we have:
- a script that deploys the database objects and its initial contents
- a script that deploys the SSIS packages and the SQL Agent job that runs them
- a script that deploys the SSAS cubes.
Every script sets the connection strings of the objects it deploys. This is a clean, fast, repeatable and easy way to deploy a complete BI solution.
This article sums up quite a few details about deploying a Microsoft BI solution via script. If it helped you, feel free to leave a comment.