SQL Server Data Tools in Visual Studio 2012–Publish Database Profile

One of the new features in SSDT, and what I consider to be my favorite, is the Publish Database Profiles. With database projects you could set a multitude of settings, everything from ANSI NULLS to whether to drop and create the database with each build. The only issue was these settings applied to the entire project; you had to change them each time you wanted to deploy to a different server, or to change the rules (overwrite vs. incremental for example).

New with SSDT are Publish profiles. They allow you to establish a set of rules and save them for reuse. To start with, right click on the project name and pick Publish from the menu.

image

You’ll now see a blank publish page.

SNAGHTML360f9789

Let’s start by tweaking some database settings. Click the Advanced button on the lower right.

SNAGHTML36114075

Here you can get to all of the options you can use to fine tune your database deployment. The most common appear at the top, the less changed ones appear in the list below. In this image I’ve checked on the option to Always re-create the database. This option will wipe out the existing database and recreate it from scratch.

Use this particular option with caution, especially if you are doing it to a database you are sharing with your co-workers (or even worse, production!). When your rebuild the database you’ll also lose any data and have to reload. Sometimes this is a good option, especially in the early stages of development when you’ve made massive changes to the database, or perhaps have gone into the database and made a lot of changes outside the scope of SSDT.

There may be other options you need to change, based on your environment or DBA requirements. Once you’ve changed your options click OK to return to the previous screen.

Back on the Publish Database settings dialog I’ll set the target database connection, and the name I want to use for the database. I can also set the output script name if I wish.

 

SNAGHTML36153d0b

Next, I want to be able to save this profile so I can reuse it later. Check on the “Add profile to project” option in the lower left, then click the Save Profile As… button.

SNAGHTML36195ad8

I gave it a good name, and made sure to include the most important options such as RecreateDB to indicate a database recreate was one of the options.

As I write this however, there is a bug with SSDT. When you click the “Add Profile to project” button it immediately adds a profile with the original default name. Then when you click the Save button in the dialog above, it adds the profile again, totally ignoring the name you give it. Instead it uses the default name again, only this time with an _1.

I’ve been assured that this bug is already known and has been fixed, and will be released with the next update to SSDT in VS2012. So depending on when you read this, it may or may not be an issue. Regardless, the fix is very easy, just rename the new .publish.xml file to reflect what you wanted it to be.

Once saved come back and hit Publish. The database will now be deployed to the server and the profile will be added to the solution. Here it is, after I’ve renamed the publish profile.

image

Note that I’ve given it a naming convention that specifies the database name, the target server, and any critical options. Here I’ve added “Overwrite” to indicate what will happen when I run it.

To run it, just double click on it. First, Visual Studio will do a build of the SSDT project. If there are any errors the process will be halted and you’ll need to fix them. If not, you’ll be presented with the publish dialog, this time with everything filled out.

SNAGHTML98453db

All you have to do is click Publish and the database will be created/updated using the options you’d picked previously, to the server which you had previously indicated.

Now for the real fun. Repeat the above steps only this time do NOT check the overwrite database option. Now, (after renaming the new profile) you have two publish profiles to pick from.

image

Take this even further. In my current project I have 8 profiles. An incremental and overwrite option for my local computer, the development server, the user acceptance testing server, and the production server. (In my case it’s a one man project, I’m the developer and the DBA all in one.) No longer do I have to juggle the server name, or even worse do a publish but forget to change the server from production back to local.

By far I think this is my favorite feature in SSDT.

About these ads

4 Responses to “SQL Server Data Tools in Visual Studio 2012–Publish Database Profile”

  1. SQL Server Data Tools in Visual Studio 2012–Schema Comparison « Arcane Code Says:

    [...] me stress something. This is not the way you should apply changes to your databases! The publish feature is the proper way to do that, in it are options to generate incremental [...]

  2. Managing Schemas And Source Control For Databases | James Serra's Blog Says:

    [...] them in.  Then push the change to the live database in your preferred way such as Deploy (called Publish in Visual Studio 2012), Schema Compare, [...]

  3. Jamison White Says:

    Thanks for the write up. Have you used any command line tools to execute the publish?

  4. Alex Says:

    i have instaled SSDT in VS2012 and no provide option had appeared, still looking for solution


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

Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: