Connected Development with SQL Server Data Tools

Although SQL Server Data Tools (SSDT) encourages 'disconnected' database development, it also provides the SQL Server Object Explorer (SSOX) tool in SSDT's Visual Studio shell to connect to a live development instance or view an SSDT project once all references have been resolved.

SQL Server Data Tools (SSDT) promotes the disconnected style of SQL Server development. This means that, instead of writing, deploying and testing your code on an actual instance of SQL Server, you write your code in .sql or .txt files which are compiled and verified before being deployed to a SQL Server.

It is quite a subtle but important difference to understand when coming from a more traditional method of developing databases. When using SSDT, you get a more obvious separation between writing, testing and deploying code. This is, however, not the whole picture: SSDT includes the “SQL Server Object Explorer” or SSOX to enable you to connect to an actual SQL Server instance and perform some basic tasks. In this article we look at what the SSOX can do, and where it can be used to help developers.

What is SSOX?

The SSOX is broken down into two parts, the first part lets you connect to an instance of SQL Server in order to enumerate such things as databases, tables, views and procedures much like the object explorer in SSMS. The second part shows you a view of the objects as they exist in your project. In this screen shot you can see that any connections to live SQL Servers are at the top, in this case there is one connection. Below where it shows ‘ Projects ‘ is the view of the projects as they exist in your project.

2349-ssox1.png

Connecting to live instances

The view here is very similar to that of the object explorer in SSMS. You can see a list of databases and some objects which are managed in SSDT such as logins:

2349-ssox2.png

If we right-click on a table in the live database list, we get a number of options including:

  • Starting a data compare operation
  • Script the object as:
    • Create
    • Drop
    • Drop and Create
  • View Code
  • View Designer
  • View Permissions
  • View Data
  • Delete, Rename, Refresh and View Properties

The point to remember when looking at anything in the SSOX live databases section is that you are connected to an actual SQL Server instance so that anything you do, such as viewing and editing the data, or altering the metadata, will happen on the live database; conversely any changes you make using the editors does not change the object in the SSDT project.

If we right-click on a table and choose ‘ View Designer and compare to that of right-clicking a project in “Solution Explorer” choosing ‘ View Designer ‘ we can see a couple of interesting differences:

2349-ssox3.png

On the left hand side we have the designer opened using the solution explorer. This provides the disconnected style development. The right hand side shows the connected version of the table, changing either of these will not result in the other being changed.

If we change the disconnected version then we will then need to deploy our changes for them to be repeated on the live database and if we make changes to the connected version we will have to manually copy our changes or do a schema compare back from the live database to the project.

You can tell the difference between them because the connected version has an ‘ Update button and a connection indicator at the bottom of the window that shows the name of the instance and the database it is connected to.

Updating code using the connected development

The editor windows that are opened using the SSOX for live databases gives us an additional button over and above that available to the designer using disconnected development in SSDT. The ‘ update ‘ button is interesting because it takes the code in the window and performs a mini compare and merge. If we use the SSOX to view the designer for the table “Build” we get a “create table” statement which we can modify and then click ‘ update ‘. If we were in SSMS we would have to write our own ALTER statement or use the table designer, but here we can simply declare the table as we want it and SSDT will generate a deployment script. For example if we add a column to the Create Table statement and press ‘ update ‘ we can view the deployment script that is generated:

2349-ssox4.png

The mini-compare and deploy does not get all the features of the standard SSDT deployment. If, for example, you try to add a non-null column then you are not given the option of enabling ‘smart defaults’ which would put a value into the column for you. You are given the option of deploying the change or viewing the script, so if you wanted to modify it you could very easily do so.

You can see any warnings in the update preview dialog that comes up when you click ‘ Update ‘. You can then either choose to modify the script before it is run against the live database or to run the update directly:

2349-ssox5.png

Clicking ” Generate Script ” opens the deployment script in a connected query window:

2349-ssox6.png

The query menu at the top lets us execute the query or use some of the common things that you’d find in the SSMS query editor window such as validating that the code is correct and showing an estimated and actual execution plan.

We can then also see the generated ALTER TABLE statement which happened because we changed the definition of the table of a CREATE TABLE. Finally at the bottom we see the connection properties such as the server and database name.

If we execute the query we get see any record-sets or messages, in this case because there is no default, so the query will fail with an error:

2349-ssox7.png

The update window will take any CREATE statements and apply them to the database so you could choose to view the designer on a table and delete the CREATE TABLE statement and replace it with a CREATE TABLE and a CREATE PROCEDURE and those two objects would be created and most importantly the original table would not be dropped.

The SSOX also allows you to create certain types of objects such as Tables, Views, Users, Logins but all you get is a default template which you need to modify and then update. Unlike SSMS you do not get a wizard or user-interface to create many of the objects so you need to modify the T-SQL directly, this is an example of creating an event session:

2349-ssox8.png

SQL Server Object Explorer Projects

The second part to the SSOX allows you to inspect a representation of the projects as they appear once they are built and any database references are resolved. If we had multiple projects and linked them together using “Same Database” references then we can see how this works. The project “Keys” stored the primary keys for the database and uses a “Same Database” reference to link them together. In the SSOX if we expand the tables section for the main project we can see that the table has no keys defined but if we expand the same table under the Keys project we can see that the table does have a primary key:

2349-ssox9.png

When you view the code on an object in the projects section of the SSOX the actual designer in the SSDT project is opened up and any changes are therefore made to the project and will need to be deployed to make the change to a database.

Right-clicking on an object lets you open the ‘View designers’, view the code, or select the object in the solution explorer. The latter can be useful if you have lots of objects, and the project is not very well structured. For objects that can be refactored using the in-built refactoring support, you can also do things such as to rename an object or move an object to a different schema which is particularly useful because it results in a sp_rename rather than a drop / create on the object.

Conclusion

SSDT provides a new way of writing and modifying SQL Server database code. It promotes the disconnected style of development which encourages the use of source control and Continuous Integration.

The SSOX is a way to bridge the gap between disconnected development and the requirement you sometimes have to connect to a live, normal development instance. It also gives us a way to view the materialised view of an SSDT project once all references have been resolved to show us how the database will actually end up. Whilst it is perfectly possible to do all this without using the SSOX, it is a useful tool in the SSDT tool belt and something that all SSDT developers should be aware of. It is particularly useful to appreciate the complexities of what is and isn’t updated in the connected database or the disconnected SSDT project.