Video: Deploying SSDT DACPACs in Visual Studio 2010


NOTE: This is a transcript, of sorts, from a Video I have posted at http://youtu.be/gXZFCZh6Hbg. You can watch the video here, as well. My video channel is http://www.youtube.com/gabworld. The playlist for data (.NET & SQL Server videos) is http://www.youtube.com/playlist?list=PLW9_oAiMp1h87qisEOi4PEfZpdiu_HXya.

Deploying a DACPAC, video version of this blog entry

I am going to show three ways of deploying a database via SSDT created .dacpac files.

  1. Deploying with Visual Studio
  2. Deploying with SQL Server Management Studio
  3. Deploying with SQLPackage.exe
      The Solution and SSDT project

    First, let’s look at the solution in Visual Studio 2010. This is a solution called Demo1 which comes from a course I am writing for Pluralsight on Behavior Driven Development, or BDD. The SSDT project is called emo1.HelloWorld.SSDT. In the SSDT project, you will see there are three tables.

If I look over at the /bin/Debug folder, you will see there is nothing here, as the project has not yet been built.

clip_image002

So I am going to right click on the project and choose Build.

clip_image003

Now you can see there are three files built, a DLL, a dacpac and a pdb. The imporatant one here is the dacpac, as it is what we need to deploy.

clip_image005

Deploying from SSDT in Visual Studio 2010

To do this, right click the Project Folder and choose publish

clip_image006

This brings up a dialog box. In this case I have not set the deployment server, so I will have to set that first.

clip_image008

I will click the Edit button and then choose my local server and change the database name to HelloWorld. I could also deploy to a database that already exists, if that is my choice.

clip_image009

I then click the OK button and then the Publish button.

clip_image011

After clicking Publish, I see the results in Visual Studio:

clip_image012

And I can go to SQL Management studio and right click the databases folder and choose Refresh.

clip_image013

We now see the database HelloWorld deployed.

clip_image014

Success on this one

Deploy with SQL Server Management Studio

You may wonder why you might want to deploy using SQL Server Management Studio. The simple answer is you can do this without having Visual Studio installed. A DBA can also use this tool for a database a developer is not allowed to install to.

clip_image015

To deploy it, I am going to right click the databases folder and choose Deploy Data Tier Application.

clip_image016

This opens up a wizard. We can ignore page 1 and click the Next button.

clip_image018

Now I will click the Browse button and find the DACPAC file.

clip_image020

I can then click the Open button on the Folder Dialog Box and the Next button on the Wizard. I then name the file and click the next button.

clip_image022

The next wizard screen gives a summary of the work, so I click the Next button and deploy the DAC.

clip_image024

Here are some screens of the DAC deploying. This should take about 30 seconds with this DAC.

clip_image026

clip_image028

If I right click the database folder and choose refresh.

clip_image013[1]

We now see the database HelloWorld deployed.

clip_image014[1]

Success on this one too

Deploy with SQLPackage.exe

You can also use the SQLPackage executable to install a dacpac. I have create a batch file that runs the following command:

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /Action:Publish /SourceFile:"E:\projects\Pluralsight\BDD\Module1\Demo1\Data\Demo1.HelloWorld.SSDT

\bin\Debug\Demo1.HelloWorld.SSDT.dacpac" /TargetServerName:(local) /TargetDatabaseName:HelloWorld

Here is the solution running.

clip_image030

If I go to SQL Server Management Studio and right click the database folder and choose refresh.

clip_image013[2]

We now see the database HelloWorld deployed.

clip_image014[2]

Success on this one too

Summary

I have summarized three ways to deploy the output of a SSDT project (.dacpac file).

  1. Deploying with Visual Studio
  2. Deploying with SQL Server Management Studio
  3. Deploying with SQLPackage.exe

Peace and Grace,
Greg

Twitter: @gbworld
YouTube: http://www.youtube.com/gabworld

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: