- To empower the developers to do database deployment into the required environment and reducing the dependence on me.
- To reduce the complexity of deploying the database projects.
- To have an easy deployment process to document i.e. run this script.
- In a way to document the deployment of the database projects.
As mentioned before I needed to automate the deployment of my database project for the unit tests. I couldn't use the MS-Test deployment functionality as I needed a post deployment script within a composite project to be executed. I decided to use PowerShell to automate SQLPackage.exe for the following reasons:
- It would be easier to manage the arguments required for SQLPackage
- Easier to capture the output of SQLPackage into a log file.
To deploy the successful builds the tool that was used within the company, at the time, was: TFSDeployer ( http://tfsdeployer.codeplex.com/ ). Unfortunately I need to point out that this is not an active project, more information can be found here: http://tfsdeployer.codeplex.com/discussions/449767. To get TFSDeployer to deploy the dacpac files, the build output for database projects, I needed to create a PowerShell script. I wanted to find a way of using PowerShell without using an external process, SQLPackage or MSDeploy, and using $LASTEXITCODE to detect errors.
While searching for an alternative method I came across the DAC Services API (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacservices.aspx. The API supports .Net which allows interaction to all the functionality of the framework. With this API I was able to create a reusable function to do the following:
- Generate deployment report and script.
- Optionally deploy the changes to target server and database.
- Capture all the events into log files.
The scripts can only be executed within PowerShell 3 or above as DAC service API library requires Microsoft .Net 4.0 or above. Also the following SQL Server redistributable components are required:
- Microsoft SQL Server Data-Tier Application Framework -DACFramework.msi (http://www.microsoft.com/en-us/download/details.aspx?id=43370)
- Microsoft System CLR Types for Microsoft SQL Server 2014 - SQLSysClrTypes.msi
Microsoft SQL Server 2014 Transact-SQL ScriptDom - SQLDOM.msi