Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Source Control in SQL Server

By Vasant Raj,

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.

Total article views: 13717 | Views in the last 30 days: 21
 
Related Articles
BLOG

Red Gate’s SQL Source Control and better SQL objects organization

Red Gate’s SQL Source Control (RGSSC) is very decent solution source control solution for database d...

ARTICLE

Stairway to Database Source Control Level 1: Setting Up For Database Source Control

The first level of this stairway introduces the basics of source control, some common technologies a...

FORUM

Data Source Project Object

Using data source project object to set "server name" for connection manager

FORUM

Solutions Explorer Not Displaying Projects

Solutions Explorer Not Displaying Projects

ARTICLE

Stairway to Database Source Control Level 2: Getting a Database into Source Control

In this level, we're going to continue the philosophy of learning by example, and get a database int...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones