Introduction

Centralized storage of any application related documents, code files, setups etc is very important for creating a reliable and easy to maintain repository for project data. It also helps to keep track of the changes made by individual developers. Various tools are available which provide easy user interface to manage the documents under a source control. Also, various development environments are providing built-in features for managing the projects in source control. [Examples are Microsoft Visual Studio 2003/2005]

SQL Server 2005 provides SQL Server Management Studio, which is an enhanced version of the Enterprise Manager in SQL Server 2000. With many new features, it is proving out to be a better option. Source control option is one feature that was expected by many of the database developers/administrators. This article will describe about the source control option provided in SQL Server Management Studio. The source control used for this article is Microsoft Visual Source Safe 6. It is assumed that a source safe database is created.

How to Source Safe?

Source control is integrated with the SQL Server

Management Studio and is similar to the feature which is provided in Microsoft Visual Studio 2003/2005.

If you have multiple source control tools installed,

then you can select the default for SQL Server Management Studio by using the

Tools->Options menu as shown in Figure 1.

[Figure 1]

One thing to remember is that everything in the source control is stored as a query file

i.e., if you want to add stored procedures, user-defined functions and other

database objects in source control, it has to scripted first as shown below.

[Scripting the database objects]

The first step to add any database objects in source

control is to create a New Project through SQL Server Management Studio. For

this task, select New Project as shown in Figure 2. This step will create a new

solution also.

[Figure 2]

It is also possible to add the solution/project directly

to source control while creating them. There is an option Add to Source

Control on the screen, which pops up when new project/solution is selected.

After a new solution/project is created successfully,

three folders are created by default to store the connection details and

queries. The structure looks like Figure 3.

[Figure 3]

It is also possible to have multiple projects in one solution.

It can be done by right clicking on the solution [Figure 4 ]in solution explorer or by

using File menu.[Figure 5]

[Figure 4]

[Figure 5]

To add the solution in the source control, right click on

the solution name and select Add Solution to Source control option as shown in Figure 6.

[Figure 6]

If you have created a source safe database, the login

screen should pop-up and after successful login the following screen appears [as shown in Figure 7]. You can specify the location where the actual location.

[Figure 7]

After you specify the name and location, the project is created and connected to the source safe. The query files can

then moved to the solution/source control [Figure 8]. [As a reminder, the database objects needs to be scripted first to a query window] It becomes a time-consuming task if the numbers of stored procedures, user-defined functions are more.

[Figure 8]

After the query files are added to the project, it will look like Figure 9. You can rename the file as per requirement.

[Figure 9]

To make the files/changes permanent in the source safe, you

just need to check-in the selected file or the entire project/solution.

Conclusion

All the database objects are required to be scripted, in order to keep them under source control.

So, whenever the database structure is modified or a script has been updated,

the only way to reflect the changes in the source control is to script the file

and update the changes. Use of source control feature for database objects is

not relevant till it is based on actual objects. A feature, which can be incorporated in future releases of SQL Server.