The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
With each new release of SSMS and SQL Server the management studio become more and more closely aligned with Visual Studio, the development studio familiar to .Net code developers. One of the earliest benefits of the association with Visual Studio was the concept of projects and solutions with regards to your SQL Server scripts.
An SSMS Project is simply a collection of scripts and objects logically grouped by the creator. Most database administrators whose backgrounds stretch before the modern SSMS interface have used the project mind set by simply organizing their own scripts at the file system into folders. In fact much the same could be said about source control.
What is the benefit of creating a project? Take a complex maintenance effort for example. Let’s say you wanted to take a legacy system and introduce normalization on a single flat table. First you create new tables, then you add indexes, then one by one you move data from the legacy table into the new normalized structure, and finally you rename and recreate existing views to point to the new structure as opposed to the legacy table.
This is something you’d want to do methodically. You’d want to test it, document it, and keep it available for future audits of what you did. Organizing the scripts involved in this effort into a single logical SSMS project makes a ton of sense and in many organizations are a requirement by policy.
Creating a new Project in SSMS is as simple as clicking New – > New Project or by clicking Ctrl+Shift+N.
The New Project wizard gives you the option (by default) to create a SQL Server Scripts Projects or Analysis Services Scripts Project. For this walkthrough we’ll create a SQL Server project.
You’ll notice that when creating a project by default it must also belong to a solution. A solution is simply a collection of projects. When creating a new project you must either add your project to an existing solution or a new one. Having a solution for a large scale project makes a lot of sense when breaking tasks up into project chunks for individual members, windows, or subject areas.
The Solution Explorer will now break down your solution by Projects, Connections, Queries, and Miscellaneous Files / Items. Adding new queries to your project is as simple as right clicking the Query folder and selecting ‘Add’. You can also add them from the SSMS menu. Miscellaneous files are those open in the query window but not part of the project.
One of the more useful features of managing your scripts within a project is the ability to connect them to a connection object. Adding multiple connection objects makes running scripts in multiple environments much easier as well.
For large scale TSQL development projects or database projects I would recommend looking into the SSDT (SQL Server Data Tools) add-ins offered by Microsoft for the full version of Visual Studio. The choice to use SSMS vs VS with SSDT however isn’t always easy. Consider some of the features of SSMS such as query plans, statistics, script generation options etc. If your project or solution requires source control you might think Visual Studio is your only option. SSMS however does offer support for Source Control as well.
SSMS offers the ability for your project and solutions to interact with many popular source control clients such as subversion and TFS. We use TFS. There is an easy walk-through on MSDN for setting this up.
In short however… to wire SSMS up to TFS you’ll need to download the TFS MSSCCI provider from Microsoft. The link here is based on TFS 2013. You’ll also need to ensure that the TFS client is already installed on your system via VS2013 (or other version) and or the TFS client or server it’s self. Once you do this the source control client will be available in your SSMS options under source control, you just need to enable it.
From there it works similar to TFS interaction within Visual Studio! Whalaa (sp?), you now have source control on top of your SSMS solution.
Tomorrow we’ll take a look at SSMS and DacPac – an easy way to package up a complete schema in a portable project based object.