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.
