SQLDeploy

SQLDeploy 2.3+

As of SQLDeploy package version 2.3, the Nuget package defaults to using DacPacs for SQL Server database deployments. Prior versions didn't specify any deployment by default which required more setup by the user to configure what to deploy and how. We believe this change will make SQLDeploy as easy as possible to get started with. That being said, all older SQL deployment options still exist in the package if you need them.

Basic Setup

1. Add a new Class Library project to your solution which will act as your SQL deployment project

2. Add the MSDeployAllTheThings.SqlDeploy Nuget package to your SQL deployment project

3. Open the [ProjectName].spp.targets file and update the DatabaseProject node to the name of the SQLServer database project in your solution.

4. Build the project with the /p:DeployOnBuild=true flag and the MSDeploy package will be created in the bin/[configuration]/sqlDeployment folder.

Class Library Project

This seems odd to use a Class Library Project to deploy a SQL database. We would prefer to use the Database Project itself but the problem is Nuget doesn't allow you to add a package to a Database project. Not sure why. So, you need another project, it doesn't matter what kind, that will kickoff MSBuild and support Nuget packages.

Parameterization/Deployment Targets

For SQLDeploy we use WebDeploy Parameterization to define the target database servers you wish to deploy to. The SQLDeploy Nuget package automatically adds a parameters.xml and setParameters.local.xml for you. All you need to do is update the value for the MyDb-ConnectionString parameter in the setParameters.local.xml file to the SQL database connection string of the SQL server you wish to deploy to.

For more details about Parameterization with Database Projects checkout the following blog post -

http://www.dotnetcatch.com/2016/02/10/deploying-a-database-project-with-msdeploy/

DacPac Options

You can optionally add DacPac deployment options in the *.spp.targets file by adding a node to the DbDeployments node. For example,

  ...
    <UseTransaction>true</UseTransaction>
    <DacPacOptions>DropPermissionsNotInSource='false';IgnoreFilegroupPlacement='true'</DacPacOptions>
  </DbDeployments>
  ...

Build Agent Requirements

• MSDeploy/WebDeploy
• Visual Studio 
• SQL Server Data Tools

The version of each will depend on the versions you use on your local development machine.

Common Errors

MSB4175: The task factory "CodeTaskFactory" could not be loaded

Update your MSDeployAllTheThings.PublishingPipeline package version.

ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER

There have been reports of the following error with VS2015:

MSDEPLOY : error Code: ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER [D:\Builds\31\EP\Development.Mozaic_Robb\src\Mozaic\Mozaic Sites\DatabaseDeployment\DatabaseDeployment.csproj] More Information: The SQL provider cannot run because of a missing dependency. Please make sure that Microsoft SQL Server Transact-SQL ScriptDom is installed. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER.

Apparently one of the SQL dlls isn't properly installed in the GAC. You can resolve this by manually installed the dll:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.2 Tools\gacutil" /i "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

https://github.com/appveyor/ci/issues/977

SQL Server Requirements for DacPac

http://www.iis.net/learn/publish/using-web-deploy/dbdacfx-provider-for-incremental-database-publishing