Are you a big user of the SQL Server database projects?  You know…what used to be known as ”data dude”?  Did you get way into doing command line deployments of your databases using vsdbcmd.exe?  Well, if you are one of those developers and you’ve upgraded to the latest version of the SQL Server Database Projects, then you might be wondering just where vsdbcmd.exe went. 

Answer: vsdbcmd.exe has been replaced with SqlPackage.exe

There were a lot of updates for the VS2012 version of the SQL Server Database Projects so it makes sense that vsdbcmd would get tweeked, too.  Especially, since the outputs of database projects have completely changed.  (Goodbye, *.dbschema files.  Hello, *.dacpac.)

For this blog post, let’s assume a very simple SQL Server Database Project called Benday.SampleApp.Database (as shown in Figure 1) that, upon “compile”, generates a dacpac file named “Benday.SampleApp.Database.dacpac”.

SNAGHTML12611e2a

Figure 1 – The database project

Thankfully, IMHO, SqlPackage.exe is about a trillion times easier to work with.  If I wanted to deploy this database to an instance of SQL Server named demo2012util, here’s what my command line syntax would be:

SqlPackage /Action:Publish
   /SourceFile:”Benday.SampleApp.Database.dacpac”
   /TargetDatabaseName:BendaySampleAppDb
   /TargetServerName:”demo2012util”

Pretty easy, right?  Set the /Action to Publish.  Specify the /SourceFile and point it to the *.dacpac.  Tell it what to name the database using /TargetDatabaseName.  Give it the /TargetServerName for the SQL Server instance.  Done. 

You can make this even easier if you add a “Publish Database” profile to your project.  In Figure 2, you can see the two profiles that I created – one for the “dev” environment named Benday.SampleApp.Database.dev.publish.xml and one for the “prod” environment named Benday.SampleApp.Database.prod.publish.xml.  These files contain all the configuration values that you’d use to deploy a SQL Server dacpac into a particular environment. 

SNAGHTML126a1bca
Figure 2 – The “Publish Database” profiles in the SQL Server Database Project

Since the profile (*.publish.xml) file has all the config values, when you go to deploy the database from the command line using SqlPackage.exe, you don’t have to supply the /TargetDatabaseName or the /TargetServerName anymore and instead you just provide the path to the dacpac using /SourceFile and then specify the profile using the /Profile argument.

Here’s what that command would look like:

SqlPackage
   /Action:Publish
   /SourceFile:”Benday.SampleApp.Database.dacpac”
   /Profile:”Benday.SampleApp.Database.prod.publish.xml”

At this point, you’re probably sarcastically thinking “WHOOPTEEDOOO!!! I’ve exchanged 4 command line arguments for 3 command line arguments! Think of all the extra free time I’ll have now!  Thanks, Ben!”  If your deployment arguments are as simple as server instance and database name then yah…not that impressive.  But there are a lot more configuration options available to you on the command line and typing/managing all those values will definitely be an annoying and error-prone process.

Just for giggles, try double-clicking on one of those *.publish.xml files.  You should see the Publish Database dialog for that deployment profile as shown in Figure 3.  This dialog lets you set the target database connection and the database name. 

image
Figure 3 – The Publish Database editor dialog

Impressed yet?  Not really?  Ok. Click the Advanced button.  KA-POWWWWWW!  Aw snap!  You’ve just been pwned!  Or whatever the cool kids say in order to indicate that something kinda awesome just happened up.  I’m pretty solidly lame.  Cut me some slack already.  Granddad’s tired.  (I’m 38.  Practically a senior citizen in the development world.)

ANYWAY!  You should now see the Advanced Publish Settings dialog and it should look something like Figure 4.  There are a TON of settings that you can…uhmmm…set for your database deployments. 

image
Figure 4 – The Advanced Publish Settings editor dialog

As these settings get complex, it becomes more and more important that these settings aren’t left up to chance and are under version control just like everything else.  One of the biggest time savers when it comes to deploying code and databases is having a well-known automated way to deploy where all your config settings are scripted out.  If it’s scripted, it’s easy.  If it’s under version control, it’s traceable. 

Create Your First Database Publish Profile

Ok. How do I create this profile?  

Step 1: Right-click the database project and choose Publish (Figure 5)

SNAGHTML1280ee36
Figure 5 – Right-click the database project and choose publish from the context menu

Step 2: Populate the values for your settings and click the Create button (Figure 6)

image
Figure 6 – Enter your settings and click Create Profile

After you click Create Profile, you should see a new *.publish.xml file in your project (Figure 7).

SNAGHTML12840f8b
Figure 7 – The new deployment profile

Step 3: This step is optional but I think it’s handy.  Change the “Copy to Output Directory” setting on the new *.publish.xml file to “Copy Always” (Figure 8).  This ensures that when the project is compiled (via the Build command) that the publish profile is copied to the bin directory along with the *.dacpac file.  This makes scripting the deploy using SqlPackage.exe easier when you’re deploying from a developer machine and MUCH easier if you’re deploying from an automated build. 

SNAGHTML1285e032
Figure 8 – Set the Copy to Output Directory to Copy Always

 

I hope this is helpful and/or useful.

-Ben

 

– Want to streamline your database and binary deployment process?  Looking for suggestions on how to make all this run smoothly from Team Foundation Server’s automated build?  Thinking it might be nice to get some training on how to put your insanely complex database under source control using SQL Server Database Projects?  Drop us a line at info@benday.com.

Share →

23 Responses to Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe

  1. Robert Vincent says:

    Ben,

    Have you found the new VS2012 DB projects improved enough to stop using RedGate’s SQL Source Control?

    • Ben Day says:

      I’ve never gotten hooked on the Red Gate stuff but it always looks pretty good. The VS2012 projects are very solid and a lot less buggy than the previous release.

      -Ben

  2. max says:

    Is there any support for migrations? In case my dev environment is on version 2.0.1.146, my first QA environment is on 1.5.6.18, my second QA is 1.6.3.128, and production is 1.0.7.5?

    • Ben Day says:

      If by “migration” you mean applying an incremental schema update over an existing database while preserving the data, the answer is yes.

      -Ben

      • max says:

        Thanks, yep, that’s what I meant. :) How does it know which upgrades to run?

        • Ben Day says:

          Magic. :)

          The dacpac has the schema definition version that you want to update to. When you run SqlPackage /Action:Update, it internally generates a diff and then generates and applies the appropriate updates to make the target database schema the same as the dacpac version.

          So…yah…magic. :)

          -Ben

  3. Mark Wojciechowicz says:

    Hi Ben,
    How do you version databases when you publish them? By version, I mean store the version number and time that it was applied in a table.

    Do you use the dacpac version number or some other manual method for versioning? i.e. in the past, we would manually increment the version number in a post deployment script.

    -Mark

    • Jamie says:

      Hi Mark,
      I’ve been using dacpacs for a while now and I still stick with the latter method that you describe there – manually increment the version number in a post deployment script.
      Actually, more accurately we insert the build number from our CI build – but that’s effectively the same result. Its a number inserted into a table from a post-deployment script.

      In other words, we don’t use the dacpac version number.

      regards
      Jamie

  4. [...] you want a bit more info on Publish Profile files and sqlpackage.exe check out Ben Day's blog post Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe Published Wednesday, May 09, 2012 12:07 AM by jamiet Filed under: sql server, SQL Server 2012, [...]

  5. Narasimham says:

    I created a build process template as described in http://msdn.microsoft.com/en-us/library/ff805001.aspx and used SqlPackage.exe instead of vsdbcmd.exe. I used the parameters that you specified as arguments in my build. However, my build always fails with the below error.

    Exception Message: File not found: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe (type FileNotFoundException)
    Exception Stack Trace:
    Server stack trace:
    at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.ProcessWrapper.Start()
    at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.InvokeProcessInternal.RunCommand(AsyncState state)
    at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
    at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

    My Build Controller and Build Agent are on two separate servers. Will that be the cause of this issue?

    • Ben Day says:

      Narasimham,

      I think you might need to install Visual Studio 2012 on that agent machine.

      -Ben

      • Narasimham says:

        Ok. Let me try that. Thanks for the update.

        • Narasimham says:

          I installed VS 2012 and also rebooted. I still get the same error as before. Am I missing something else.
          Exception Message: File not found: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe (type FileNotFoundException)
          Exception Stack Trace:
          Server stack trace:
          at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.ProcessWrapper.Start()
          at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.InvokeProcessInternal.RunCommand(AsyncState state)
          at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
          at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

          Exception rethrown at [0]:
          at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
          at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
          at System.Func`2.EndInvoke(IAsyncResult result)
          at System.Activities.AsyncCodeActivity`1.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
          at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

          Inner Exception Details:

          Exception Message: The system cannot find the file specified (type Win32Exception)
          Exception Stack Trace: at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
          at Microsoft.TeamFoundation.Build.Workflow.Activities.InvokeProcess.ProcessWrapper.Start()

  6. David Turner says:

    Ive never liked the automation side of this as I dont like the delta being generated on the fly to production servers. What I have done is created a “Sql Migrations” type thing like Entity Framework migrations while still using the power SqlPackager and SqlCmd.

  7. Eric Aldinger says:

    Thanks so much for this concise answer to a process I was trying to understand. And the tip about Always Copy should not be optional, it is so helpful.

  8. zhi an says:

    Is there anyway to stop sqlPackage.exe from running the post-deployment script?

  9. Praneet says:

    Can we override the argument supplied by the .xml publish file?
    e.g.: If the .xml publish file applies TargetServerName as “ABC”, can it be overridden to another value (say TargetServerName=”xyz”)applied via command line argument?

  10. Amjid says:

    Hi, great blog. It’s been real useful. You say you have been using it for a while. I am thinking of using dacpac for a system I support. We do releases every two weeks. Schema changes on most releases. Have you been using it for many releases on the same database? If so how many releases and have you ever had any issues regarding data loss of incorrect schema changes?

    • Ben Day says:

      Hi Amjid –

      I don’t have any customers who are currently doing incremental deploys from automated builds using dacpacs right now. The number of releases shouldn’t matter because the upgrade path is always from whatever is deployed in the db to the dacpac version. There are options in the update process that will block the upgrade on possible data loss. Personally, I wouldn’t worry too much but I’d *DEFINITELY* test my updates in a staging environment before going to production.

      -Ben

  11. Richard Schaefer says:

    It appears SQLPackage.exe doesn’t support standard long path names. It isn’t in my system path so I ran it from where it was installed and specified the full path/file for the DACPAC and Publish Profile as well as my target connection string. The command returned dozens of errors everywhere there was a blank in one of the parameters. This is my command:

    sqlpackage.exe /a:Publish /op:”c:\temp\” /pr:”C:\Users\rs02130\Documents\Visual Studio 2012\Projects\Database1\Database1\bin\Output\Database1.publish.xml” /sf:”C:\Users\rs02130\Documents\Visual Studio 2012\Projects\Database1\Database1\bin\Output\Database1.dacpac” /tcs:”Data Source=CNU1370MFL\MSSQLSERVER12;Initial Catalog=Database1;Integrated Security=True;”

    Any thoughts? I thought MS was past the whole “short path” thing…

  12. Richard Schaefer says:

    I ran the command from the directory where SQLPackage is installed. Still has blanks due to the TargetConnectionString:

    “C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe” /a:Publish /op:”c:\temp\” /pr:”Database1.publish.xml” /sf:”Database1.dacpac” /tcs:”Data Source=CNU1370MFL\MSSQLSERVER12;Initial Catalog=Database1;Integrated Security=True;”

    I get these errors:

    *** Unrecognized command line argument ‘Source=CNU1370MFL\MSSQLSERVER12;Initial’
    .
    *** Unrecognized command line argument ‘Catalog=Database1;Integrated’.
    *** Unrecognized command line argument ‘Security=True;’.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>