Speaking at SQLSaturday #206 in Madison, WI

I'll be presenting on SQL Server Data Tools (SSDT) for Visual Studio 2012 at SQLSaturday #206 in Madison, WI at Madison Area Technical College on April 6th. I'll be in the 4PM timeslot in Room 142B.

This will be a very similar talk to the one I gave at FoxPASS in February. The revised abstract:

In 2007, Microsoft introduced Data Dude for Visual Studio 2005 to help bring the processes, controls and tools for code management to Database Development that Application developers had been enjoying for ages. It had its problems, but it mostly worked and was a welcome step in the right direction. Many versions later we have SQL Server Data Tools for Visual Studio 2012 (and 2010). A more mature product that finally brings database development up to par with traditional application development and has many features data geeks, like ourselves, will truly love. This talk will provide an overview of SSDT’s features and show you how to get started using it to save time, implement better code controls and be a better DBA or database developer.

Odd Behavior With .NET SQL Client Connection Pooling

This is an odd phenomenon that I encountered recently and wanted to share. I have some Powershell scripts that I use to drop and recreate databases that are used for testing some of our applications. The script first checks to see if the target database already exists and then drops it if it does. It checks first for open connections to the database (in sys.sysprocesses) and kills them if there are any (using the KillAllProcesses(TargetDB) method of the Microsoft.SqlServer.Management.Smo.Server object). What was happening is that for some databases, my check for active connections would return 0, but when the script would go to drop the database, it would fail saying there were active connections. Oddly, on the instances where it did work, the check for open connections would always return >0, even if I knew there were no open connections when the script was kicked off.

That last item got me thinking that the script itself was keeping connections open. Sure enough watching active connections while the script ran showed that when the script checked to see if the database already existed, the explicit close of the connection was not actually closing the connection. The quick fix was to disable connection pooling when opening the connection to check for the existence of the database. Doing so would actually close the connection when I tell it to close, rather than keeping it open for later re-use.

  $conn2 = New-Object System.Data.SqlClient.SqlConnection "Server=$TargetSQLInstance;Database=$TargetDatabase;Integrated Security=SSPI;Pooling=false;"
  try
    {
      $conn2.Open()
      $conn2.Close()
    }
  catch
    {
      CheckForErrors
    }

What I was seeing on the server where the script worked, was what the that after the existence check but before the drop, sp_who2 and sp_lock showed a .NET Client Connection from my workstation to the target database still open with a shared DB lock to the target database.
sp_who2:

sp_lock:

The script would see the open connection, ask me if I wanted to kill it, I'd say yes, the script would kill my own connection, and the drop would succeed. On the server where it did NOT work, here's what I'd see at the same spot in the script:
sp_who2:

sp_lock:

Notice that the connection left open by my existence check is to master, not the target database, but the connection still has a shared lock open on the database, so the drop would fail. it would seem that on the first instance of SQL Server, closed pooled connections remain in the database context in which they were opened, but on the second instance, it changes to the context of the master database when the connection is closed.

After trying this out on a bunch of instances here, I've determined that the behavior appears to be specific to the exact version of SQL Server.I don't have enough time to test a bunch of configs to see where the behavior changed, but I see the connection left open to master on 2008R2RTMCU9 and older and I see it on the target database on 2008R2SP1CU4 and newer. Somewhere in-between, the behavior seems to have changed.

Refactoring T-SQL using SSDT - Part 2

First, my apologies for the delay in posting. Life sometimes has a way of getting in the way of the best intentions. Rest assured it was not due to a lack of desire to do so, just a lack of time.

In this, the second part of my short series on Refactoring T-SQL in SSDT, we're going to go over fully qualifying object names and moving objects to a new schema. These two items are pretty closely related. For instance, perhaps you wish to move all of the objects for a particular application to its own schema. If you previously had not been fully qualifying your object names, you'll need to now.

3. Fully Qualify Object Names

When developing database code, if all your objects reside in the dbo schema, it is pretty easy to fall into the habit of not qualifying your object names. After all, the code works without them. There are good reasons to do so, though. For me the biggest reason is that it simply improves readability. But there are other technical reasons as well. For instance, at compile time, all the objects referenced in the code are resolved by a lookup. If you have not specified the schema, the query optimizer will first look in the users own schema or whatever their default schema is. That extra lookup, while small and only occurring at compile time, is still an extra lookup that could be avoided. Also, specifying the schema now will make changing that schema later, should it be necessary, much easier.

SSDT makes all of this easy. We can refactor a specific object or an entire project, depending on where we right-click. For some reason, however, we're not able to refactor all objects of a specific type (i.e. Stored Procedures) by right-clicking that folder in the solution explorer. For this example, let's refactor a single stored procedure. We first right click on the procedure in the Solution Explorer and select Refactor > Fully-qualify Names...

This will bring up the Preview Changes window just like we saw previously with the Expand Wildcard and Rename refactor tasks. Let's have a look at the kind of things that will be refactored here.

Note that it added the schema to the name of the stored procedure itself as well as to all table names within the stored procedure. It also added table aliases to items in the select list. Had the procedure called any user-defined functions, those would have been qualified as well. Clicking Apply will make the previewed changes to the stored procedure. It's that easy. One interesting thing to note is that references to Common Table Expressions will be fully qualified as well. For instance, if we refactor the MERGE statement used in the post-deployment script that loads the State table, we see that all the references to the CTE for the target of the merge, appropriately called 'Target', are now fully qualified.

4. Move an Object to a Different Schema

SSDT makes this operation a breeze. Previously, you'd have to change the schema of the object and then hunt down every reference to that object and change the schema in all those references. Now it can be done in a single operation. In this case, though, invoking the refactor dialog is a bit tricky. You can't just right-click the object in the Solution Explorer, you have to have the item open and then right click on the object's definition. For instance, let's move the State table to a new schema called 'Lookup'. We need to open the definition for the State table and then right click on the CREATE TABLE statement.

This will bring up the 'Move to Schema' dialog. I'll select 'Lookup' for the New Schema, make sure 'Preview Changes' is checked and click OK.

We're presented with the Preview Changes dialog and we can see that not only is it changing the table definition, but all the constraints on it as well as referencing procedures, functions and pre/post deployment scripts. How cool is this?!

Potential Gotchas

As mentioned above, just where you'll find the refactor menu item you're looking for can be difficult. Many items can be refactored only at the individual item or entire project level, and nothing in-between. Also, some items like the Rename (from Part 1) and Move to Schema refactor presented here are only accessible when right clicking on the object definition, not the object's file in the Solution Explorer.