I recently was needful of adding some instrumentation to the Dot Net Nuke code base, and decided to use the new SQL Server Developer Tools to load in the database as a project and manage it there. I had written some internal documentation for the project while it was still under wraps, and was glad to see it so strong when it was released for public consumption. I thought seeing how I used the application might give someone out there a hand.
Since we have an existing project, we have an existing database. Fortunately, SSDT has an app for that. You can add a new SQL Server Database Project, which will effectively take a snapshot of the database and expose it to Visual Studio as T-SQL Scripts. These are the scripts that will eventually make up the development base for the software
We will start by creating a new project. Right click on the Solution file and select New Project … The Project Selection dialog appears, and if you click Data, you get the template for the SSTP. Name the project and move forward.
This project represents everything that is part of a database file in SQL Server. There is a properties folder in the project that will show you all of the database level properties – usually handled by SSMS. This is just one of many examples of SSDT bringing the DBA and developer closer together, as shown in the figure to the left. Operational properties such as the filegroup and transaction details are at least available for viewing by the developer and alterable locally. Permissions still hold, so you as a developer have to be set up to change these kinds of details to change the production system. At least you can alter them locally and see what works without a call to the DBA.
The original project is empty. In order to get the existing database into the project, an import needs to occur. Right click on the new project and then Import Objects and Settings. Select the local database and pass in the appropriate credentials. I selected the DotNetNuke database from my developer’s instance but you should select whatever you want to incorporate.
The Import Database Schema Wizard has all of the options that define how you will interact with the database once it is in the project. I like the default settings, which define a folder for each schema, and a folder within for each database item type.
There is an option to import the SQL Server permission structure, but I find that most projects don’t use that. My DotNetNuke project uses the SQL Membership Provider, however, so there is a mapping between the login structure of the database and the Users table of the membership provider. For that reason, I do turn on Import Permissions.
Once the values are set, just follow the steps:
- Make a new connection
- Click Start
- Watch the magic happen
- Click Finish
- Let’s see what we have
What we have here is everything that the database has to offer, in T-SQL Scripts. This is important. Every change that is made can be included in a DAC, because there is source control and an understood level of alteration. Changes are known by the system, and go into a pot to turn over to operations, or to be reviewed by the DBAs. Changes are not made by altering the database anymore.
Taking a look at the DotNetNuke database project, you’ll see the main schema (dbo) broken into the familiar Tables, Views, sprocs and functions. The project also has scripts for three other database members – Scripts, Security and Storage.
Storage is just the database definition. In the case of this project, it is simply:
ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [DotNetNuke],
FILENAME = '$(DefaultDataPath)
SIZE = 9216 KB,
FILEGROWTH = 1024 KB)
TO FILEGROUP [PRIMARY];
It’s part of the completeness, but not something that you will alter a lot. The Scripts directory is another that you’ll change once and forget about – it contains the pre and post build scripts for the deployment of the database. Every time you build and push to the actual database server, these scripts will be run.
The Security folder is fascinating. It contains all of the roles and related schemas, and the associated authorizations. If you have a project that secures database assets in the database management system, this could be awesome.
The meat is in the schema folder, called ‘dbo’ in the DNN example. This is where the scripts you would expect to see in a project like this are held, and where we will be doing the majority of our work. Each entity in the database is scripted separately here, and we can modify or add to our hearts content, and deploy separately.
Set up some new entities
The first ting needed for the instrumentation being added is a table for some timing data. Start by right clicking on the Tables folder and selecting ‘Add New …’. Notice the nice Visual Studio integration that shows asset types which can be added. Select a Table. Name the table ‘Instrumentation’ in the Add New Item dialog and click OK. There is a base template for a table; go ahead and change it for the new fields:
CREATE TABLE [dbo].[Instrumentation]
InstrumentationId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
ClassName varchar(64) NOT NULL,
MemberName varchar(64) NOT NULL,
ElapsedSeconds bigint NULL,
Exception text null
There is a Commit button that is so tempting at this point, but it isn’t the button you want right now. Commit peeks at the declarative information in the script – effectively doing a pre-parse – and try and make appropriate changes to the target database. It is more or less like the Table Designer in SSMS at this point, using the references to concepts in the database to make decisions rather than just running the T-SQL as coded.
Click the Execute SQL button in the text window’s toolbar to run the CREATE TABLE procedure and save the table to the database defined in the project properties. (You can read more about that in the Deployment section below.) In this way, the database is effectively disposable. At any time, you can get a copy of the scripts from Team Foundation Server, and generate a whole new copy of the database, minus reference data. For right now, though, we are just adding this one table.
Great, that gives us a place to put the data. Next step is a way to get it there. Right click on the Stored Procedures folder and select Stored Procedure from the context menu. Just like the table above, Visual Studio will give a base template. I changed it to the code below:
CREATE PROCEDURE [dbo].[AddInstrumentationEvent]
@ElapsedSeconds bigint = 0,
@Exception text = ''
INSERT INTO Instrumentation
I need to make a quick shout out here for the IntelliSense. It’s expected I suppose, that this should support full IntelliSense integration but I was just shocked at how comfortable to use I personally found it. I do not care to write SQL code because it is so cumbersome. Having that modern development experience talked about in the introduction makes a big difference.
That’s all we got for new features – it’s a short paper after all. Clearly any entity that SQL Server supports can easily be added to the project, stored in source control, and managed like the rest of the application. What’s more, it can be tested and refactored like any other part of the application.
Red, green, refactor
After further review, it was decided that Instrumentation wasn’t a good enough name for the table, since it doesn’t accurately represent what was actually put in the rows of the table. Instead, the name InstrumentationEvents is supposed to be used, so we need to rename the table.
Right click on the table name in the CREATE statement of Instrumentation.sql and select Refactor -> Rename. Change the name to InstrumentationEvents and click Next. Notice, as shown in the figure to the left, that SSDT got it right. The preview is very helpful. It finds all of the consuming database members, and lets you determine which of them to apply the change to.. Even in the stored procedure, the pluralization is correct, changing AddInstrumentation to AddInstrumentationEvent rather than AddInstrumentationEvents. That trailing s might not seem like much to some people but it can make a big difference in a convention over configuration based system.
Rename isn’t the only refactoring available in SSDT, there are also T-SQL specific features. If you are working in DotNetNuke, open up the GetUsersByUserName.sql script in the Stored Procedures folder. It’s a little overdeveloped and has too much UI logic in it, but it works for this example.
Line 31 has a SELECT * on it, and frankly this procedure is too slow as it is. We don’t need to add a table scan. The refactor menu has an Expand Wildcards option, and I recommend its use here. Right click on the asterisk and select Refactor -> Expand Wildcards, then click on SELECT * in the treeview. The Preview Changes dialog now will show us how the sproc will look with the wildcard expanded, just like the figure to the right. Click Apply to have the changes applied to the procedure.
Don’t overlook the various features of the Visual Studio IDE that now can be used to manage the T-SQL code. For instance, consider that GetUserByUserName.sql file. Right click on the vw_Users token on line 10 and select Go To Definition to be taken to the View definition in the database project. The view doesn’t help us much, because we want to see the table in question. Scroll down in the view to the FROM statement and right click on Users and select Go To Definition again to see the Users table.
As expected, you can then right click on the Users table name and Find All References to see the 44 places that the Users table is used in the database project. The usefulness of this in a legacy environment can’t be overestimated. Finding your way around a project this easily, digging from the core code to the database and navigating using the built-in features will significantly reduce time spent in getting up to speed on existing applications.
What’s more, code-level refactoring isn’t the only thing that the data modeling group is pushing for in SSDT. There is project-level refactoring available as well, which is a step in the right direction of whole-project management. Across-the-board changes to software code are something that Visual Studio already excels at, and SSDT is working toward providing the same kinds of features for database projects.
For instance, right click on the database project and select Refactor from that context menu. Aside from the wildcard expansion seen in the code-level refactoring, not the Rename Server/Database reference. It’s a whole-project way to change references that would be manages in configuration files for a C# project, but needs to be controlled with refactoring in T-SQL.
Refactoring is an important part of software development. Though it has been available in third party tools for a while having a standardized experience that is so tightly integrated with Visual Studio will make a big difference to the average developer. While unit testing integration with Visual Studio still isn’t in there for T-SQL it is still a step in the right direction toward that modern development experience we have been talking about.