Deploy using SQL Server Management Studio
Published 20 May 2016
Configure your project to output a re-usable deployment package during build, which can be deployed onto all of your database environments using SQL Server Management Studio.
SQL Change Automation's SQLCMD packages are sophisticated enough to figure out whether any migration scripts need to be applied and run them, or simply do nothing if the database is already up-to-date. If any errors occur during deployment, then the deployment will be immediately halted, allowing the transaction to be rolled back (see warning note further below to find out how this works). Additionally, the database will be automatically created on the SQL Server on-premise or Azure SQL instance if it does not exist yet.
Initial project setup
Within Visual Studio, right-click the project in the Solution Explorer and select Project Properties.
On the Project Settings tab under Outputs, check the SQLCMD package (.sql file) option.
Build the solution.
In the output window, you'll notice that a package file is now being created as part of the build:
In this case, a file called AdventureWorksAzure_Package.sql is being created. If the build is successful, the package file is automatically moved to the .\bin\Debug sub-folder.
Ensuring your package is up-to-date
Always perform a build after you've made a change to the project, such as adding a new migration or updating a programmable object script.
This ensures that the package always includes up-to-date copies of all of your project assets.
You can view the artifacts produced by the build by browsing to the output folder. To do so, right-click the project in the Solution Explorer, and select Open Folder in File Explorer:
Navigate to the bin\Debug sub-folder and locate the <ProjectName>_Package.sql file:
This package file contains a concatenated copy of all of your migrations, pre/post deployment scripts, and programmable objects (if enabled), along with a set of conditions to keep track of which scripts have been executed.
We'll use this file in the next section to deploy the database to a remote SQL Server instance.
Deploying within SQL Management Studio
You can execute the SQLCMD Package created in the previous step by following these steps:
Open <ProjectName>_Package.sql file in SQL Server Management studio.
Within the Query menu, select SQLCMD Mode.
Within the header of the file, you'll notice a section entitled SQLCMD Variables. This contains a list of variables from your SQL Change Automation project, along with the appropriate default values for each item.
Variable values
To override the default values, simply enter in your own values within the :setvar statements.
For example, if you want to deploy to a different database than the name specified in the project, replace the value for the DatabaseName variable.
Select all of the lines beginning with :setvar, then, from the Edit menu, select Advanced... Uncomment Selection (or press CTRL-K then CTRL-U).
To deploy your database, de-select the variable lines and click Query... Execute (or press F5).
The results of the deployment will appear in the Messages window.
Deployment behavior
When the script is executed:
- If the database does not yet exist, it will be created and all migrations will be executed (including the Pre-Deployment, Post-Deployment and Programmable Object scripts).
- If the database exists, and this is the first time you are deploying your SQL Change Automation project, SQL Change Automation will check if a baseline is present in the source project.
- If the baseline is set, the script will create the __MigrationLog table in your database, marking the baseline migrations as deployed in the table.
- If a baseline has not been set, SQL Change Automation will stop the deployment by disconnecting from the SQL Server in order to prevent the database from being unintentionally overwritten. However you can force the deployment to continue without a baseline by setting the ForceDeployWithoutBaseline variable to True. See Working with an existing database for more information.
- If the database exists, and there are pending migrations (or any Programmable Objects that have changed since last deployment), an incremental deployment will be performed to preserve the state of the target database. Pre-Deployment and Post-Deployment scripts will also be executed.
- If the database exists, and there are no pending migrations, then only the Pre-Deployment and Post-Deployment scripts will be executed.
Note that all of your migrations and programmable objects will be executed within a single user transaction, unless the TransactionHandling setting within one or more scripts has been set to Custom (see Transaction Handling for more information). If the deployment is successful, the transaction will be automatically committed by the package script.
Deployment failures
If an error occurs during deployment, the deployment will automatically be halted to prevent further errors from occurring. However, when executing SQLCMD scripts within SQL Server Management Studio, the environment will not automatically roll the transaction back (it will simply leave the transaction open). To roll back the deployment, close the document window and if prompted to commit the uncommitted transactions, click No.
Note that this behavior does not apply when executing the package script using SqlCmd.exe or via PowerShell: when deploying the database using either of these methods, the transaction will automatically be rolled back in the event of deployment failure.
