Deploying SSDT During Local and Server Build
Join the DZone community and get the full member experience.Join For Free
4 years ago, I wrote a post about how to deploy VS DB (Visual Studio Database Project) to your local SQL Server during your local build.
As you may already know that a new version of SSDT (SQL Server Data Tool) has been released with VS 2012 to replace the normal VS DB project that was existing with VS 2010 and of course the deployment changed.
For more information and understadning about SSDT, see this post:
What’s New in TFS 2012?- SSDT (SQL Server Data Tool)
Now we have a feature called publish profile which you can put all your setting in that file and use it afterwards to deploy the DB.
This post has 2 sections:
- Deploy and publish SSDT to local SQL Server during local build
- Deploy and publish SSDT to a shared SQL Server during TFS Build
Deploy and publish SSDT to local SQL Server during local build
If you need to work with SSDT for SQL Server 2014, then you will need to install the last SSDT, it’s not included in VS update 3, download SSDT
1.Using SqlPublishTask of MSBuild
I edit the SSDT project file as the following:
<Target Name="DeployDB" AfterTargets="build" Condition="'$(Configuration)' == 'Local'"> <Message Importance="high" Text="************** Star Deploying DB **************" /> <Message Importance="high" Text="Deploying Project: $(MSBuildProjectDirectory)\$(MSBuildProjectName).sqlproj" /> <Message Importance="high" Text="Deployment Profile: $(MSBuildProjectDirectory)\PublishProfiles\$(MSBuildProjectName).local.publish.xml" /> <MSBuild Projects="$(MSBuildProjectDirectory)\$(MSBuildProjectName).sqlproj" Properties="SqlPublishProfilePath=$(MSBuildProjectDirectory)\PublishProfiles\$(MSBuildProjectName).local.publish.xml" Targets="sqlPublish" /> </Target>
I used some of MSBuild Reserved and Well-Known Properties like $(MSBuildProjectName) and $(MSBuildProjectDirectory), for complete list, click on this link
2.Using MS Build Command line with SqlPublishTask
msbuild /t:build;publish "C:\TFS Source\Model\Calculator\Calculator.DB\Calculator.DB.sqlproj" /p:SqlPublishProfilePath="C:\TFS Source\Model\Calculator\Calculator.DB\PublishProfiles\Calculator.DB.local.publish.xml" "$(MSBuildProgramFiles32)\MSBuild\12.0\Bin\msbuild" /t:build;publish "C:\TFS Source\Model\Calculator\Calculator.DB\Calculator.DB.sqlproj" /p:SqlPublishProfilePath="C:\TFS Source\Model\Calculator\Calculator.DB\PublishProfiles\Calculator.DB.local.publish.xml"
- If we run the MS Build from withing the project path we can use relative paths instead of absolute paths
- To use MSBuild without path, we need to open Visual Studio command line otherwise we may having this error “exited with code 9009” which means it couldn’t find the MSBuild.exe”, so I will need to use the full path and this is always the case when working with pre and post build events
SqlPackage.exe is a command line utility that automate some of the DB development tasks
We can call SqlPackage as a post build event for SSDT project as the following:
"$(MSBuildProgramFiles32)\Microsoft SQL Server\110\DAC\bin\sqlpackage" /a:publish /sf:"$(SolutionDir)\$(ProjectName)\$(OutputPath)\Calculator.DB.dacpac" /pr:"$(SolutionDir)\$(ProjectName)\Calculator.DB.local.publish.xml"
The .dacpac file is the output of building sqlproject, as the dll is the output of building a class library
Deploy and publish SSDT to a shared SQL Server during TFS Build
There are many ways too, the SSDT’s team blog create a great post for all ways, for more info, click on the following link:
SQL Server Database Projects and Team Foundation Build
1.Using SqlPublishTask of MSBuild
I will send some MDBuild arguments in the build definition as the following:
But because my solution has many projects not only SSDT project, which means I build the solution file not the SSDT project file only, this means, the build argument will be sent to each project file in the solution and this gives error with one of the Win Form project as the following:
MSBuild error MSB3021: Unable to copy file C:\Program Files (x86)\MSBuild\12.0\bin\amd64\Microsoft.Common.CurrentVersion.targets (4691): Could not copy the file “exe.manifest” because it was not found.
This error will disappear if we only build SSDT, of course I can use other ways like using SqlPackage.exe with custom build but as this is the way I did on my local build, I prefered to be the same on the Build server, so I customize the build as the following
I used 2 editors:
Which makes me be able to select multiple build project and multiple publish profiles, for more info about Build Editors, see the following post:
TFS Build Editors and Build Process Metadata
Published at DZone with permission of Mohamed Radwan, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
How To Use Git Cherry-Pick to Apply Selected Commits
New ORM Framework for Kotlin
TDD vs. BDD: Choosing The Suitable Framework
Front-End: Cache Strategies You Should Know