Continual Deployment of Visual Studio SqlProj

2016-05-16 910 words 5 minutes

Unveil the inner workings of the esoteric build system…

As a data professional, I’ve never worked extensively with msbuild or other pipelines. I’d been mostly focused on just running schema comparisons and publishing. However, I’ve had the needed to try and deploy a database project from visual studio automatically, and this is my process through it.

There are benefits for those who don’t necessarily want to run this against production, but instead for those who want to continually deploy check-ins and run tests, documentation, or other tasks against the deployed schema. This was my personal goal.

There are other solutions that plug into this that can make the process easier, such as Red Gate’s DLM Automation with SQL Continuous Integration. For this case, since migrating to a new project format wasn’t possible until later, I worked with the native sql project from the SQL Server Data Tools in Visual Studio 2015.

Terminology

Build Controller: This is like Service broker. It handles the queuing and assignment of the defined builds to the appropriate agents. It doesn’t do any actual building, but instead handles the delegation of the work.
Build Agent: This is the “wrapper” for msbuild. It does the actual work of building whatever you pass to it.

Initial Setup & Install

Configure Build Service Wizard Choose the configure option of just the build service
Configure collection
Choose configuration options for the build services
Setup build service account
Finished with configuration. After running checks and resolving any issues (I thankfully had none for this simple install of just build controller/agent; you can proceed
Create Build Agents

Future Configuration of Build Agents

Once you’ve finished the install of the build service and agents, you need to configure them. After completing the install the Team Foundation Server Administration Console should open, if not open manually (start menu)

Review build service configuration
Build Configuration Pane
Reviewing Build Agent Properties

Setup of Build

Create new build
set trigger to continous integration
map your working folder to the database project
setup the process details Make sure to the map the project to the .sqlproj file to only build the sqlproj. You can adjust other items as you desire, but this should cover the core settings.
Copy Local Make sure to have the new profile copied locally as part of the build or it won’t have any publish profile copied when the build is triggered, and therefore might result in hours of you wondering why it is ignoring your profile target settings (true story). You can configure to not do this of course, if you want to have a fixed file on your drive instead of copying from source control each time. Recommendation - Prepare a Publish Profile file
Setup Parameters for Build The arguments I choose to use were: (note the publishprofile parameter which wasn’t in older versions of SSDT)

/t:Build /t:Publish /p:SqlPublishProfilePath=foobar.publish.xml" /p:PublishScriptFileName=foobar.publish.sql /p:TargetDatabaseName=“foobar”;TargetConnectionString=“Data Source=localhost;Integrated Security=True;Pooling=False” /p:PreBuildEvent= /p:PostBuildEvent= /p:VisualStudioVersion=14.0

The infuriating thing about working through this is that when it doesn’t find the publish profile, it defaults to the “Deploy” settings, so in my case it kept trying to deploy the changes to a local database named the same thing as my project. Please don’t waste the same amount of time grinding your teeth at MSBuild as I did, and watch out for the paths to be correct! Make sure you have it set to copy the publish profile over or it will not exist and default to the deploy settings.

When I omitted the VisualStudioVersion option, it had an error “unable to connect to target server”…… 2 days of work later I realized the version parameter was critical for it to use the right msbuild version and deploy. I didn’t go into more research on this, so feel free to comment if you more details on this.

Running MSBuild Manually

A few tips from my work with launching the process to build the database locally, if you experience an issues. This is work I was doing with MSBUILD 14 (visual studio 2015). I would trigger msbuild and it would deploy the database, but never exit the process to report success to powershell allowing my script to continue.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


/p:UseSharedCompilation=false               --> (Roslyn compiler bypassed http://bit.ly/1WmMVzx)
/m:4                                        --> (limit to only 4 cores http://bit.ly/1VSl9uF)
/nr:false                                   --> same link above
/verbosity:quiet
/p:Configuration=Release                    --> don't need debugging for this output, so just output release
/p:DebugSymbols=false                       --> no need for extra debugging, potential improvement in timing to get rid of this
/p:DebugType=None
/t:Build;Deploy                     --> optional. Could rebuild if you want to
/p:PreBuildEvent=                   --> bypass any prebuild/post build events if you have something doing copying of files around (optional)
update after got everything working:
/p:VisualStudioVersion=14.0 --> ensure you match the version of msbuild you need

Optional: If you have further issues with 2015 and want to disable globally the node reuse settings then you can do this with a registry entry. Following the directions from TechDocs I did this.

Resources

MSDN Documentation Tasks

Creating tasks is documented in

The MSBuild XML project file format cannot fully execute build operations on its own, so task logic must be implemented outside of the project file. The execution logic of a task is implemented as a .NET class that implements the ITask interface, which is defined in the Microsoft.Build.Framework namespace.

The task class also defines the input and output parameters available to the task in the project file. MSBuild Tasks

Webmentions

(No webmentions yet.)