SQL Server Data Tools in Visual Studio 2012–Snapshots

A new feature of SSDT, one not found in VS Database Projects, is the ability to do snapshots. A snapshot captures a copy of your database project as it exists at the time you take the snapshot. Once you have a snapshot there are several useful things you can do with them.

Taking a snapshot is simplicity itself. Using the same project we’ve been using over the last few posts, right click on the project name, and pick “Snapshot Project” on the menu.

Once you do, you’ll see a new file appear in the solution. You’ll be given the chance to rename it, but for this demo I’ll just take the default name.

So now that you have a snapshot, how do you use it? Well let’s start with a database comparison. First, let’s make a change of some type to the database. I’m going to use the safe refactor (see my blog post on this). I’m going to open the Employee table in the HumanResources schema and safe refactor the JobTitle column to become JobName.

With the file saved, let’s now decide we want to compare our current database project to what we’ve done in the past, namely our snapshot. The same tools we used for schema compare can also be used with a snapshot.

Right click on the snapshot and pick Schema Compare (for more info see my previous post). When the schema compare window appears the snapshot will be in the source side. Over on the right, use the pick target to pick the current project, then click compare. You will now see the differences between your snapshot and your current project.

So looking at differences is nice, but what if you want to dig in and see the entire snapshot? If you’ll notice, the snapshot is created as a dacpac file. If you happened to notice in the earlier post on creating an SSDT project, in addition to importing from an existing database, we also have the option to import from a dacpac file.

Right click on the solution, and pick Add, New Project.

Now pick a new SSDT project and give it a name. I’m going to name mine after the snapshot. Now right click on the project, and pick Import. then pick “Data-tier Application (*.dacpac).

Now navigate to the snapshot dacpac file you created and import it. (Hint, to find out where it’s at, before you start the import right click on the snapshot file, and pick properties. One of the properties is “FullPath”, it will hold the full path / file name of the snapshot.)

Once you click start, your new project will have an exact copy of your project at the time the snapshot was created.

Move down the AdvWorks_20120821_07-19-43 project tree and open up the Human Resources schema, then the Employee table in both projects. Assuming you were following along you will now see the snapshot as the Job field as JobTitle, the name prior to the change.

Within our AdvWorks project, also navigate down to the Employees table, and you’ll see it has the new name of JobName for the job column.

While snapshots will capture versions of your database over time, be aware they are not a substitute for good source code control. Snapshots are manually created, and are part of the project. Source control will capture each version upon check in, but more importantly serves as a good back up.

Snapshots can also be useful when asking for help. Simply take a snapshot and e-mail it to your friend. They can simply import it and create a copy of your project. Much easier than trying to zip up the entire project and mail it around.

SQL Server Data Tools in Visual Studio 2012–Schema Comparison

A feature carried over, but improved upon, from Visual Studio Database Projects is the Schema Comparison tool. The tool allows you to compare one database to another, a database to your project (or vice versa). It will also allow you to do comparisons between dacpacs and projects or databases (or them to dacpacs).

Doing one is pretty simple. We’ll keep using the project we’ve been using in previous lessons. For today’s example I’ve done a safe refactor on the JobTitle column in the HumanResources.Employee table, renaming it to JobName. I have also added a table and view to the dbo schema. For your example simply rename something in your project. After you make the changes, don’t publish them! We need something different for this example.

To kick off the schema compare, go to the SQL menu, then pick Schema Compare, New Schema Comparison. The dialog will be mostly empty, we’ll start with the upper portion.

As you see, on the left we hit the drop down and will pick Select Source.

Here you have three choices. The top is to pick a project as your source. The second will let you pick an existing database as a source. The final choice will allow you to pick a dacpac file. For this example, we’ll pick the database that we created from our AdvWorks project, one prior to the changes you just made.

On the right, hit the drop down and for the target pick your AdvWorks project. Now click the Compare button in the upper left. SSDT will do the comparison and populate a dialog with the results.

In the upper half you will see a list of all the changes found. If you click on a change, the Object Definition area in the lower half populates with the code that creates the objects, and highlights the differences. In this example you’ll see that our source system has the JobTitle column, whereas the project on the right has our change to JobName.

The check boxes allow you to select or deselect individual changes. For example, you could go to the upper half which is designated to delete the ArcaneCode table and view and uncheck them. Then when you apply the changes these would be left untouched.

If you have a lot of changes you wish to omit, you can buik apply the exclusion (and likewise the inclusion) of files. Right click on a grouping (here the groups are changes and deletes), in the popup menu you can choose to Include All or Exclude All.

To apply the changes, simply click the Update button.

You can choose how to group the comparisons findings. In the schema comparisons toolbar, you can choose to group by Action (the default), by the Schemas, or by Type (types being tables, views, etc).

You could have a situation that could result in data loss, for example the deletion of a table. By default SSDT will block any changes that will result in data loss in the target. Your target, however, might be a test database in which you don’t care if you lose data. There may also be other options you wish to override.

To see the options for applying updates, click on the gear immediately to the left of the grouping toolbar button.

Here you can see a vast list of options available to you, that will affect the way in which SSDT applies updates to the target. You can see the “Block on possible data loss” option under the mouse, and could uncheck it to force your changes. There’s a lot of options here, so scroll through the list to see what other options you might be interested in.

Between the dropdowns for source and target is a little double arrow symbol. Click it will swap the source and target. Do it now, so the database now becomes the target and the project becomes the source. Now run the Compare again.

You should now see the button between the Update and Options buttons become enabled. This is the Generate Script button, and becomes active when the target is a database.

Click it an a new window will appear with all the T-SQL that will change the target database to make it in sync with the project.

Let 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 updates.

This option is more for times when you have a test database you want to quickly get in sync, but for whatever reasons don’t want to create a full publish profile.

The Schema Comparison tool is surprisingly useful. A true story, I was working on a project one time that had considerable changes to an existing database. It was a short term project, so not a lot of time. In theory the source database was supposed to be left unchanged. Note I say “in theory”.

Bright and early Monday morning the very new to the job DBA comes to us and says “Oh by the way we had some issues over the weekend so I had to apply a bunch of changes.” When we asked for his scripts he just shrugged and said “I didn’t keep any of that junk. The changes are in the database just go get ‘em.” And with that wandered away, presumably to provide more sunshine to the lives of other dev teams.

At one time this would have been a major set-back. Fortunately the Schema Comparison tool quickly brought our project up to date. We were able to see the database changes before we applied them to our project, and in a few cases exclude the automatic changes and instead make them manually in our project.

Play around with the Schema Comparison tool, you can run it without actually applying changes. Knowing how to use it will help you on that fateful day when a new dba spreads a little sunshine into your own life.

SQL Server Data Tools in Visual Studio 2012–Customizing the Table Designer Layout

With this post I want to show you a few of the nice shortcuts provided to you in Visual Studio SSDT for quickly customizing the layout of your designers. A few of the items only apply to the table designer, but many apply to other windows within Visual Studio, no matter what project type is being hosted.

Number 1 points to the pane swap button. Clicking it will simply swap the positions of the grid and T-SQL windows, like so:

The double bar pointed to by number 2 is the resizing handle. Click and drag to adjust the amount of space used by either pane.

Note the change of the cursor shape when it’s hovered over the double bars.

There are three buttons pointed to by number 3. The middle one is the default, and indicates you want to split the panes horizontally. If you click the left most of these 3, it will split the panes vertically.

Vertical mode is really nice when you have a super wide screen monitor. As you can see, the three buttons have now shifted to the bottom center of the screen, next to the mouse in the above image.

What if you are working on a really small screen, and don’t even have enough real estate to work comfortably with any size split? Well that’s where the right (or bottom if vertically split) button comes in. Click it to shift to tabbed mode. (Note, I suggest you shift back to the default horizontal split first, otherwise the tabs will be on the right instead of the bottom and not quite as easy to use).

The last button, number 4, is for the T-SQL pane. It’s also found in almost all code editor windows in Visual Studio. Using it you can split the code view so you can see two different sections of you code at the same time.

Great for working with especially large code bases. And this split should exist in any text editor, not just the designer. Whether it’s straight T-SQL, VB.Net, C++, F#, or C# it should work for you.

For a typical desktop user, you’ll probably set these once and forget. But for folks like me who travel a lot, these are a real blessing. When I’m at home, with my laptop hooked up to my 25inch wide screen monitor, I can quickly shift to split screen vertical mode to take advantage of all that width.

When I’m on the road though, working on my laptops small screen (12 inches), I can shift back to horizontal mode, or more often (for me) tabbed mode, for doing my work.

Experiment with different layouts, and find out what works best for you!

SQL Server Data Tools in Visual Studio 2012–Safe Refactoring

With Visual Studio Database Projects (VSDB), you entered into Safe Refactoring mode through the Schema View window. But in SSDT, the Schema View window no longer exists. So how the heck do you do it??

Well first off, let’s define safe refactoring for those who may not have been familiar with the feature in VSDB. It allowed you to right click on a column name, and pick rename. You could then enter a new name, and hit the preview button. Visual Studio would comb through your source code and find every occurrence of that column name for that table and show it to you. If you clicked OK, Visual Studio would then go through and make the change everywhere for you, ensuring you didn’t miss anything.

Before we can do a safe refactor, we need to have something to refactor, and to test with. For this demo, we’ll create a simple view to read the dbo.ArcaneCode table we created in the previous post. Since there is no folder in the dbo schema for views, we’ll first have to create it.

Still working with our AdvWorks sample, open the dbo folder. Right click on it, and pick Add, Folder. Name the folder Views. Let me interject that strictly speaking, this isn’t a requirement. We could have placed the new view source file we’re about to create anywhere. But keeping a file/folder structure consistent with this and other SSDT projects will make maintenance far easier.

OK, once the folder is there, right click on it, again pick add, only this time pick a new View. Name the view dbo.vwArcaneCode.

CREATE VIEW [dbo].[vwArcaneCode] AS 
  SELECT [BlogUrl], [BlogAuthor]
    FROM dbo.ArcaneCode

And save it. Now return to the table designer for the new ArcaneCode table.

Let’s decide we don’t like the name BlogUrl, and we’d rather call it BlogSite. Well, first we could try putting our cursor into the T-SQL area, and just typing over BlogUrl with BlogSite. What happens?

Well, as you can see above, the designer on the upper half changes, but the column name in Create Index did not update. Not nearly what we wanted.

Let’s start over. Restore the name in the T-SQL area back to BlogUrl. Now go into the grid at the top, and change it there.

Hey, that’s better! Once we tabbed out of the Name column (or clicked elsewhere) it changed the name in all the locations in the T-SQL area. Just what we wanted!

Or is it?

Remember, we also used this column in the vwArcaneCode. Go take a look at its code.

As you can see, it remained unchanged. Even worse, there’s now a big red squiggly under the BlogUrl name, indicating we have now created an error in our project. Sigh. OK, one more time.

Go back to the table designer, and reset the name back to BlogUrl. Now we’re finally ready to do this the right way.

Put your cursor somewhere in the column name in the T-SQL area, then right click on the column name. Note, this will not work if you try it in the grid area at the top!

From the menu, pick Refactor, Rename.

You’ll now see a dialog appear, giving you a place to enter a new name. Change the name to BlogSite, and make sure the Preview Changes is checked on, then click OK.

Once you do, a new window will appear. It will show you everywhere the change would be made.

Looking at the window, the upper part shows you all the files and the line of code in the file for which a change would be made. If you click on a line, the new version of the code will appear in the lower window. Ahh, there’s the view, and you can see it is being shown with the new change that will be made.

Note that no change has occurred as of yet. You must click the Apply button for anything to actually change. You can also click Cancel to abandon the change.

If you need to know everywhere a column name is used, but don’t want to change it, there’s an easy way to do that too. When we right clicked on the column name and picked Refactor, well a few menu options down was another option called “Find All References”. Picking that will populate a window in your Visual Studio environment.

When you click on a line in the above window, your central display in Visual Studio changes to now show the file you clicked on in this window.

There you go, you can now not only safely refactor column names in Visual Studio 2012 SSDT, but you can also find all references to that column within your project.

SQL Server Data Tools in Visual Studio 2012–Table Designer–Other Objects

Above is the screen shot of where things were left at the end of the previous post. We had just added a new table and used the designer to create four columns, along with their data types. We also saw how we can edit the table in T-SQL and see updates in the designer’s grid above.

But what’s all that stuff to the right of the grid? It would seem to list the various objects that would be associated with a table. In this case there’s only one, the unnamed Primary Key. But through this are we can also add new objects.

Let’s add a simple index. Right click on the Indexes (0) area. from the menu pick Add New, then for this example we’ll pick a standard Index.

When you do, a new index name appears below the Indexes (0) area, with a default name.

Since this is going to be for the BlogUrl, I’m going to change the name to IX_ArcaneCode_BlogUrl. You’ll see the Indexes collection area has updated to reflect the new name, and the number in parenthesis has been updated to (1), to reflect the number of Indexes.

In the T-SQL area at the bottom, you’ll see some new T-SQL setup and ready for you to update. Here, all you have to do is change the [Column] to be the actual field name you wish to use in the index. In this case, [BlogUrl].

Think of the new designer as a combination of easy to use UI with a code generator. Creating other objects works just like you’ve seen here. You right click, pick what you want to add, and Visual Studio SSDT inserts the code template read for you to update.

SQL Server Data Tools in Visual Studio 2012–Table Designer

One of most noticeable enhancements to the data tools (over the previous database projects) is the table designer. Using the AdvWorks project we started in previous posts, let’s add a new table. Since the dbo schema has few tables, let’s add it there.

Expand the dbo schema, right click on the Tables folder, right click and pick Add, Table as you can see in this illustration.

Next you’ll be asked to confirm the type of object you wish to add, and what you want to name it. Ensure the “Table” object type is selected (the red arrow points it out below). Then, give your new table a good name. If you use multiple schemas in your database (and you should) then get into the habit of always typing in the schema name before the table name, even if it’s the default schema. This will prevent you from putting tables into the wrong schema, then having to clean up the mess later.

You are now presented with the spiffy new table designer. Using it is fairly straightforward, but has some nice abilities.

You can begin by simply going to the Name area, and typing in new column names. I’m going to start by changing the word Id to ArcaneId. Next, move to the Data Type box and hit the dropdown. You’ll be presented with a dizzying array of data types!

For now I’ll leave it as int, since this will be my primary key, but I’ll add other types momentarily. I’ll leave Allow Nulls off, as well as leaving the default empty. Now add a column by moving down to the next row in the grid, perhaps call it BlogUrl, nvarchar(256). Note that when you pick the nvarchar column type, you’ll have to type right inside the Data Type text area to change the length of the column. Finally add a DateUpdated column, Date data type, an set the default to GETDATE().

Note that as you’ve filled in your columns in the designer, the T-SQL in the box underneath is also updating. It’s a two way street, shift down to the T-SQL code on the bottom. Let’s add a fourth column, but put it under the BlogUrl but above the DateUpdated. Let’s name it BlogAuthor, nvarchar(256), NULL (we’ll allow nulls) and no default.

When you get to the end of the line and VS has confirmed this is valid T-SQL code, it will update the designer area on the top to reflect what you’ve done below.

There is one more thing we should do, something that’s quite common especially in data warehousing. We should have the primary key be an Identity type, that is a column whose value auto-increments with each inserted record. We can’t do that via the designer area at the top. While we could move down to the T-SQL area at the bottom and just type it in, there is a way to do it graphically.

In the designer, click on the row with the ArcaneId. Now go to the Properties window (generally over on the right, below the Solution Explorer if you still have the default VS seutp). About 2/3 the way down you’ll see a property called Identity Specification. Using the + button expand it, then change the Is Identity property to true.

Now your designer window should look something like:

But that’s just the start, for this isn’t just a table designer, but a designer for keys, constraints, indexes, and more! But that will wait for the next post in the series.

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.

You’ll now see a blank publish page.

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

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.

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.

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.

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.

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.

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.

SQL Server Data Tools in Visual Studio 2012–Importing a Database

In the previous post we saw how to create a new project using SSDT. In this entry we’ll see how to import an existing database into the project. Start by right clicking on the project (not the solution) and pick Import, Database.

The Import Database is similar to the one from the 2010 database projects, but simplified. Use the New Connection button to setup a connection to your database (here I picked Adventure Works 2012). Target Project is disabled, since it’s in the context of the current project.

Import settings can be left at their defaults. The one thing to note is the Folder structure drop down. I personally prefer the default of Schema\Object Type. You can also pick None, which will put all the SQL files in the root of the project. I wouldn’t recommend this option, as it will quickly get difficult to find the files you need to edit. You can also organize by just Schema, or just Object Type. If you are a hard core DBA you might find Object Type more comfortable, since it’s closer to the Object Explorer in SSMS. As I said though, my experience has been Schema\Object Type is the easiest to work with.

When it’s done just click finish, and you’ll see the new structure in the Solution Explorer. Each folder at the top level represents a Schema, or database level object such as Database Triggers.

In the image below, you can see I expanded two of the schemas, HumanResources and Person. Under these are folders for all of the present object types.

Note that the HumanResources schema has a folder for Stored Procedures, while Person does not. This is simply because in the database the Person schema has no stored procedures. If you want to add a stored procedure to the Person schema, you’ll want to add a folder to the Person structure and name it Stored Procedures. This isn’t required, you can put the SQL file anywhere you want, but if you mimic the existing organization structure you’ll make it much easier to maintain and expand the SSDT project as you move forward.

Lets expand a branch to see all the files.

Finally! We’ve drilled down to the lowest level and can see the individual files that are needed to make up the project.

In the next installment we’ll look at altering some of the database settings. Over the next few weeks we’ll be looking at deployment tools, database snapshots, and how to edit the various file types, and some of the enhancements there, especially around the table editor.

SQL Server Data Tools in Visual Studio 2012

In August I’ll be giving a couple of presentations at devLink. One of them will be on the new SQL Server Data Tools that was released with SQL Server 2012. As you may be aware, I’ve been a proponent of Visual Studio Database Projects since their initial release with Visual Studio 2005.

With SQL Server 2012 the SQL team took ownership of the database projects. They completely retooled them so now they can release them as “out of band” add-ons for Visual Studio. The new version is called SQL Server Data Tools, or SSDT for short. It’s included with VS2012, or you can download a version compatible with Visual Studio 2010 at http://msdn.microsoft.com/en-us/data/tools.aspx.

I’ve been using it for a real world production project for some time now. While I like it, there are some major differences between the new SSDT and the former database projects. Over the next few blog posts I want to highlight some of those differences, culminating with the devLink presentation.

For this series of posts I’ll be using the Visual Studio 2012 Release Candidate, which from here on I’ll simply refer to as VS2012. As this is a Release Candidate there shouldn’t be any noticeable changes between now and the final release.

If you are still on VS 2010 don’t fret, what I’ll describe applies to it as well, assuming you have gone to the link above and downloaded the SSDT add in.

The first difference is with creating a new project. With VS 2012 the older database projects are gone. Only in 2010 can you still do both. Here’s the new project screen shot from VS2010:

Here is the screen shot from VS2012.

As you can see, the Database branch is gone and only the SQL Server Database Project exists.

Now for the next difference. With VSDB Projects, when you created a new project you were immediately walked through a wizard that helped you with various default choices, and allowed you to import a database. With SSDT, once you create a new project you are given a blank slate, an empty project to start from.

If you are creating a new database from scratch, it is left to you to create the entire folder structure, and to name your files correctly.

I’d highly suggest though that you import at least one database, to see how the wizard organizes things, so that you can follow suit. Importing a database is as easy as it was in VSDB Projects, but we’ll save that for the subject of the next blog post.

Data Dude Webinar for Pragmatic Works

Just wanted to let everyone know I’ll be doing my first webinar for Pragmatic Works this Thursday, June 9th 2011. It will be at 11 am Eastern time.

I’ll be doing an introduction to “Data Dude”, Visual Studio Database Projects. We’ll cover such things as generating a project from an existing database, using the safe refactoring tools, and generating sample data. There’s no cost, and I hope you can join us live so I can answer any of your questions. If you can’t, then we’ll be recording the session for later viewing. To register, jump on over to our website and register:

http://pragmaticworks.com/Resources/webinars/Default.aspx

If Data Dude isn’t your thing don’t worry, there are lots of upcoming webinars. Next week Brian Knight is doing an intro to MDX session, and Ben Evans will be doing one on Data Driven Website Design. There’s also a nice selection of already recorded past webinars available for instant viewing. And like I said, it’s all free. How cool is that!

Visual Studio Database Project Course

I’ve recently completed another course for Pluralsight, this one is on Visual Studio Database Projects. In it, I cover how to reverse engineer an existing database, then the many options you can configure for deployment. I then cover data comparisons, schema comparisons, and pre/post deployment scripts.

One of the most powerful features of Visual Studio is the data generation tools which give you the ability to generate vast quantities of realistic, predictable test data. Finally, I cover how to unit test your database code.

While I use Visual Studio 2010 and SQL Server 2008 R2 in the course, everything I teach is applicable all the way back to Visual Studio / SQL Server 2005, so even if you are still on an older version of your development tools the lessons you learn here can still be applied.