Support Center

Automating SQL Server Database Deployments

Last Updated: Apr 18, 2016 08:22PM AEST

This article has moved to a new home. For up-to-date documentation please continue reading at https://documentation.red-gate.com/display/RR1/Continuous+Integration+and+Deployment 


ReadyRoll projects can be deployed to a remote server by command line or automated using a Continuous Integration server like TeamCity.


For each target environment in your organisation, you will need to generate a unique script to deploy your database:
 

 


But don't worry: the generated scripts will only contain migrations you have written yourself, so there is no guessing as to how your database will be affected at deploy time.




Deployment from the Command Line using MSBuild

To perform a deployment from the command line use the following syntax:

 
C:\Windows\Microsoft.NET\Framework\v4.0.30319\MsBuild.exe 
  MyDatabase.sqlproj 
  /p:TargetServer=MYSERVER\INSTANCE 
  /p:TargetDatabase=MyDatabase 
  /p:DBDeployOnBuild=True


This will connect to the target server/database (by default, using Windows Authentication) produce a SQL file containing any unapplied Deploy-Once migrations, and then use the SQLCMD utility to execute the generated script against the target.

        

NOTE: If you don't want ReadyRoll to actually execute the scripts, you can specify /p:DBDeployOnBuild=False generate to produce a script for review.

The generated script can be found within your project sub-folder under bin\[Configuration].

By default, ReadyRoll will use Windows Authentication to connect to your database. If you'd prefer to use SQL Server Authentication to connect, use the following syntax:


C:\Windows\Microsoft.NET\Framework\v4.0.30319\MsBuild.exe 
  MyDatabase.sqlproj 
  /p:TargetServer=MYSERVER\INSTANCE 
  /p:TargetDatabase=MyDatabase 
  /p:DBDeployOnBuild=True
  /p:TargetUserName=...
  /p:TargetPassword=...

 

 

Build/Continuous Integration Server Pre-Requisites


ReadyRoll database projects can be built on any of the following operating systems:

 

  • Windows Server 2008 R2 SP1
  • Windows Server 2012
  • Windows 7 SP1
  • Windows 8

 

Once you’ve got an operating system up-and-running, you’ll need to get the supporting software installed. This includes:

 

  • SQL Server Data Tools (update March 19 2014: no longer required for ReadyRoll 1.8.2+)
    Provides build-time support for SQL parsing and .NET CLR compilation.
  • SQL Server Express 2012 (optional)
    If you would like to test the deployment of your database during build, install SQL Server on your build agent to allow ReadyRoll to perform a "shadow" database build (more about this later, under "Gated Deployment").

 

Installing the abovementioned tools can be quite a time consuming and tedious task, so instead of doing this manually, an alternative is to use the setup automation tool Chocolatey to get the job done.

 

To install these pre-requisites using Chocolatey, open a command prompt in administrator mode and run the following (not that this will also retrieve Chocolatey if you don't already have it installed):

 
@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin

CALL cinst SSDT11

CALL cinst MsSqlServer2012Express

@ECHO Adding [NT AUTHORITY\Authenticated Users] to sysadmin role on local SQL instance

"%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S . -E -Q "ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Users];"

 

These commands will take a while to run, but you shouldn't have to intervene or confirm any dialog boxes, and a restart should not be required.

 

Please note that your acceptance of the Microsoft EULA’s is implied when you run these commands (legal information here).

 

Regarding the last command (ALTER SERVER ROLE...), this ensures that your build agent can access the newly-installed SQLEXPRESS instance (which installs as the default SQL Server instance on the machine). You might choose to make this access more restrictive if you know which user your build agent will run-as.

 

 

 

 

Bootstrapping Build-Time Dependencies with NuGet

 

ReadyRoll allows you to commit build-time dependencies to source control using the power of NuGet. This means that you can rely on the consistency of your database builds between deployment environments (ie. TEST/QA/PROD). It also means that there’s no additional software to install on your build agents to get your ReadyRoll builds to work (apart from the items mentioned above).

 

 

To integrate the build-time dependencies into your project, open the Package Manager Console tool-window and install the package from the NuGet gallery:
PM-ReadyRoll-MSBuild
This will install the package into the current solution, rather than into a specific Database Project within the solution. This is a current limitation in NuGet, so to work around this we’ll need to link the page to the database project manually.

 

 

To do this, firstly select the ReadyRoll Database Project in the Solution Explorer, then click Project… Unload Project.

VS_Solution_NuPkg_Edit_Proj_1
Then, right-click the project in the Solution Explorer and click Edit Project.
VS_Solution_NuPkg_Edit_Proj_2

Find the <ReadyRollTargetsPath> element and replace the contents as follows:

 
<PropertyGroup>    <ReadyRollTargetsPath>..\packages\ReadyRoll.MsBuild.1.8.2.652\tools\ReadyRoll.Data.Schema.SSDT.targets</ReadyRollTargetsPath></PropertyGroup>

Save the project file, then right-click the project in the Solution Explorer and select Reload Project
VS_Solution_NuPkg_Edit_Proj_4
Confirm that the build-time dependencies have been successfully linked to the project by building the solution:
VS_Solution_NuPkg_Build_Proj
Now commit the solution/project changes along with the package files to source control.

** Please note that ReadyRoll is not currently compatible with NuGet package restore, so for the moment you will need to check all the package files into source control.

With these build-time dependencies in source control, your ReadyRoll projects can now be built on any compatible build agent using a Continuous Integration server such as TeamCity.

 

 

 

 

Automated Deployment using Continuous Integration

 

If you would like to set up automated deployment using Continuous Integration (ie. instead of using a separate tool such as Octopus Deploy), provide the following settings in a new build configuration:
 

Runner type: MSBuild
Build file path: MyDatabase/MyDatabase.sqlproj
MSBuild platform: x86**
MSBuild version: .NET Framework 4.0
MSBuild tools version: 4.0
System Properties:
∙ TargetServer = MYSERVER\INSTANCE
∙ TargetDatabase = MyDatabase
∙ DBDeployOnBuild = [True/False]
∙ Configuration = Release
∙ TargetUserName = [SQL Auth User]***
∙ TargetPassword = [SQL Auth Pwd]***

 

** If your build agent is running a x64 version of Windows, you will need to specify the x86 version of the MSBuild executable in your build configuration: C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe

 

*** If you'd prefer to use Windows Authentication to connect to your database, simply omit the TargetUserName & TargetPassword properties from your build configuration.

 

These settings will run a build that generates a SQL script containing any unapplied migrations (based on the TargetServer/TargetDatabase provided). The generated script can be found within your project sub-folder under bin\Release.

 

NOTE: If you don't want ReadyRoll to actually execute the scripts, specify DBDeployOnBuild=False to produce a script for review prior to execution. To later deploy the generated script, call the Deploy target.

 

 

 

 

Adding Gated Deployment to your Continuous Integration build

To add an additional layer of confidence to your database deployments, you can choose to introduce Gated Deployment to your Continuous Integration server (builds within Visual Studio already use gated deployment).

How it works: during project build, a separate copy of your database is created, which is called the Shadow database. This step ensures that all the migration scripts are syntactically and referentially correct prior to deployment; only once the Shadow database has been built successfully will your live database be affected.
 
Additionally, gated deployment also generates a deployment preview report, as well as a drift-analysis report, giving you a high degree of visibility into your database changes.

    
Gated Deployment creates isolated copy of your database prior to deploying your live database

 

To add Gated Deployment to your Continuous Integration server, simply specify the ShadowServer property in your build configuration with a valid SQL Server instance reference, e.g. MYDEVSERVER.

ReadyRoll will use an auto-generated  database name for the Shadow (in the format [ProjectName]_[ExecutingUsername]_SHADOW). If you would like to define a specific name for the Shadow database, specify the ShadowDatabase property.
 

NOTE: To avoid contention with Production resources, it is highly recommended that you use an isolated SQL Server instance for the ShadowServer property (e.g. a Dev or Test environment).


You can also perform gated deployments from the command line, e.g.

MsBuild.exe MyDatabase.sqlproj 
  /p:TargetServer=MYSERVER\INSTANCE 
  /p:TargetDatabase=MyDatabase 
  /p:DBDeployOnBuild=True
  /p:ShadowServer=MYDEVSERVER  
  /p:ShadowDatabase=MyDatabase_SHADOW

The output from the build will be divided into two steps:
    
 
   

 Note that, if you plan to deploy your database using a different tool (eg. Octopus), simply omit the DBDeployOnBuild property argument to perform a gated deployment build without actually performing the Deploy step.

 

 

Further Reading

 

 

 

 

readyroll.support@red-gate.com
http://assets1.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete