Centralized storage of application-related documents, code files, and setups is crucial for creating a reliable and easy-to-maintain repository for project data. It also helps to keep track of changes made by individual developers. SQL Server 2005 provides SQL Server Management Studio, an enhanced version of the Enterprise Manager in SQL Server 2000, which offers various features including source control.
Source control is a valuable feature that allows developers and administrators to manage their database objects effectively. In this article, we will explore the source control option provided in SQL Server Management Studio and how it can be utilized to streamline the development process.
Getting Started with Source Control
SQL Server Management Studio integrates source control functionality, similar to what is provided in Microsoft Visual Studio. To set the default source control tool for SQL Server Management Studio, navigate to Tools -> Options and make the appropriate selection.
It’s important to note that everything in source control is stored as a query file. This means that in order to add stored procedures, user-defined functions, and other database objects to source control, they need to be scripted first.
Adding Database Objects to Source Control
The first step to add any database objects to source control is to create a new project through SQL Server Management Studio. This can be done by selecting New Project. A new solution will also be created in the process.
Once the solution/project is created, three default folders are generated to store connection details and queries. It is also possible to have multiple projects within one solution. To add the solution to source control, right-click on the solution name and select the “Add Solution to Source Control” option.
After specifying the name and location, the project is created and connected to the source control. The query files can then be moved to the solution/source control. It’s worth mentioning that scripting the database objects to a query window can be time-consuming, especially if there are numerous stored procedures and user-defined functions.
Making Changes Permanent
Once the query files are added to the project, they can be renamed as per requirement. To make the files/changes permanent in the source control, simply check-in the selected file or the entire project/solution.
Conclusion
Source control is a valuable feature for managing database objects in SQL Server Management Studio. However, it is important to script the database objects in order to keep them under source control. Any modifications to the database structure or script updates need to be reflected in the source control by scripting the file and updating the changes.
While the use of source control for database objects may not be relevant until it is based on actual objects, it is a feature that can be incorporated in future releases of SQL Server.