SQL Server Data Tools (SSDT) was introduced as a replacement to the old Business Intelligence Development Studio that’s been around since SQL 2005. But it does more than just create Integration Service or Analysis Service projects. Microsoft has taken the old BIDS and combined it with the Data parts of Visual Studio for a whole new tool; SSDT.
SSDT is a new way to develop SQL database objects. It ties into Visual Studio 2010 and now developers can work in a familiar environment. SSDT can be installed as a feature of a SQL 2012 installation or as a separate download from Microsoft. If you’ve already installed VS 2010 Professional or above you’ll need to upgrade to with SP1 first, if you haven’t already. If you’re not running VS 2010 SSDT will install a VS 2010 shell for your use.
Working against an online database is done very similar to working in Management Studio. Open SSDT, then open SQL Server Object Explorer and create a connection to your server. Once you’ve done that you can view databases and their objects. This is very similar to what you see in SSMS. To open a new query window click the New Query icon (right button in SQL Object Explorer). The query window is a slimmed down SSMS query window, but the same functionality is there; there’s buttons on the toolbar for Estimated and Actual Execution plans or viewing the results in a grid or text. You’ll see your results and messages in separate tabs on the bottom, or like VS you can switch where the code and results display. Intellisense is available in the query pane. The main difference is that you execute your code with Ctrl + Shift + E instead of F5. The little green Execute arrow is still there if you rather use that method.
My favorite features come when you work in disconnected mode.When you’re disconnected you’re working in a local sandbox, completely separated from other users. You can work on tuning that stored procedure while not affecting any application.
There’s a few ways to do this, but I found the easiest is to right click on the database you want to create a copy of, then choose “Create New Project”. You’ll get the screen below where you can set your project options. One of the most helpful is the last one, on file structure. You can organize your project by schema, by object, by both, or by neither. I personally prefer both.
After you click Start SSDT will create the objects in your new project. In the Solution Explorer are all the objects created and sorted as you chose when you created the project. You’ll also see a new entry in the SQL Object Explorer pane, called LocalDB\ProjectName. You’ll also notice that the database doesn’t appear to have any objects yet. Press F5 to build and deploy your project, refresh the SQL Object Explorer and voila! There they all are.
Note that this only creates the objects. It doesn’t populate the tables.But that’s a piece of cake. Back in SQL Object Explorer, go to your original source database, right click on the table that has the data you want, and choose View Data. A new query window will open showing the records just like SSMS does. You can change the number of records returned as well, though I don’t see a way of placing a filter on the results. Anyway, if you click on the Script button a new query window will open with an INSERT statement for each record in the results. Change the connection to the LocalDB database, run the query, and congratulate yourself. You’ve just populated your local table! You can query the LocalDB, update or delete records, whatever you need, without affecting anyone else.
First a word about that LocalDB database you just created. Don’t confuse it with a database on your local instance. You may not even have one. What SSDT uses is a database it creates that doesn’t need SQL. If you look at the properties of the LocalDB instance it will say “Microsoft SQL Express Edition”, but this isn’t the Express Edition available as a download.It doesn’t run as a service, and as far as I can see it’s only available when SSDT is running. If you drill into the directory where you created your project you’ll spot a Sandbox folder, and inside will be a .mdf and .ldf file for your database. There are some warning about using LocalDB, mainly that it doesn’t support all SQL features. I know FILESTREAM is one.You’ll probably want to check on BOL for others.
Snapshots and Schema Compare
My next favorite feature is the ability to create snapshots of your project and to compare schemas between databases.
Snapshots are a representation of your database project at a given moment. Back in Solution Explorer, right click on the project name and select Snapshot Project. SSDT will create a file with the .dacpac extension. The default name is the database with a timestamp but you can call it anything you like. You can create snapshots anytime, at different stages of your development. And that’s where Schema Compare really shines.
Let’s say you connect to a database and create a new database project. And you create a snapshot before you start development. While developing you make changes to existing objects or create new ones. Now you want to create a change script, but maybe you forgot what objects you touched. And you can’t go back and compare to your source database as maybe it has changed as well from other users. You can still use Schema Compare and use the snapshot files you created as a source or target. Schema Compare will work the same way it has against “real” databases, showing you the differences in the schemas. You can either create a script to run or click the Update button to apply your changes. You can filter by schema or object if you don’t want to compare large projects.
There’s more to SSDT than just what I’ve written here. I’ll try to write another post on SSDT going a bit deeper. In the meantime, check out SSDT in Books Online, or just Google it. This is a very handy tool for SQL developers to add to their arsenal.