Worked example: Mercurial - SQL Source Control

You can use SQL Source Control with any source control system with a command line interface. This example demonstrates how to set up using SQL Source Control with the Mercurial source control system.

The example uses:

• The Mercurial source control system
• The TortoiseHg client for Mercurial

In the example, the Magic Widget Company has a SQL Server database. This database contains a number of tables, views, stored procedures, and other database objects. The Magic Widget Company's development team wants to begin working on an update to this database. They have already created a copy of the production database, as a baseline to develop against.

They now need to source control the development database, so that each developer can work on their own dedicated copy.

This example has 6 steps:

1. Set up the database
2. Link the database to source control
3. Commit the database objects to source control
4. Get the latest version
5. Make development changes
6. Commit the development changes to source control

1. Set up the database

This worked example uses the WidgetDevelopment database.

To create the database on your SQL Server:

1. If it already exists, delete WidgetDevelopment
2. Click here to view the SQL creation script for the database.
3. Copy the script, paste it into a query window in SQL Server Management Studio, and run it.

The database and its schema are created.

2. Link the database to source control

Linking associates the database with a location in source control.

That location must be an existing, empty folder.

We will create a working folder and link the database to that location.

Create a working folder

To create an empty Mercurial working folder:

1. In a Windows Explorer window, right-click, and from TortoiseHg, select Create Repository Here:

   

2. Specify a location for the repository, and click Create.

Alternatively, in the Mercurial command line interface, create a folder, navigate to it, and type hg init.

If you are linking to a database already in source control, you first need to get the latest version to a local working folder.

Create a link to source control

1. In SQL Source Control, on the Setup tab, ensure a database is selected, and click Link database to source control.

   The Link to Source Control dialog box is displayed:

   

2. Under Source control system, select More:

   

3. Type, or browse to, the location of your working folder.
4. Select the config file for your source control system.

   In this example, select Mercurial.

   A config file is an XML file containing command line hooks, that let you automate source control operations (Add, Edit, Delete, etc) for different source control systems. For information on the commands in the config file, see Working with config files

   When you first link a database to source control, the config file you select is committed to the working folder. Note that:

   • if you make any changes to a config file in a working folder, you must commit the changes using your source control system.
   • if you remove the config file from your working folder, you may encounter errors using SQL Source Control.
5. Select a development model.

   For more information, see Database development models

6. Click Link.

   The database is linked to source control.

   The database icon in the Object Explorer changes to show that the database is linked:

   

Note that linking only associates the database with a location in source control.

3. Commit the database objects to source control

To finish source controlling the database, commit the objects:

1. In SQL Source Control, click the Commit Changes tab.

   The Commit Changes tab displays a list of all the objects with database versions that do not match the latest source control version:

   

   Because none of the objects yet have versions in source control, they are all listed.

   You can view the creation script for an object by clicking it. The script is displayed in the Object Differences pane, below the list of objects to commit.

2. In Comment to add on commit, type or paste a comment.

   Comments are recommended as they help provide a detailed change history.

   In this example, type Initial commit of all objects.

3. Click Commit

   SQL Source Control displays a message dialog box showing the progress of the commit.

   When the commit is complete, click OK to close the message box.

The objects are committed to source control and other users can now get the latest version of the database.

The Object Explorer is updated to show that there are now no outstanding changes to commit.

4. Get the latest version

Now the database is in source control, another user can get the latest version, and make development changes.

They create a new database, link it to source control, and update it with the latest version of all the objects.

Creating a clone of the database in source control

Before you can link the database, you need to get the latest version to a local working folder. You can do this by creating a clone of the database already in source control:

1. In a Windows Explorer window, right-click, and from TortoiseHg, select Clone:

   

2. Specify the location of the database working folder in source control, and a location for the local working folder.
3. Click Clone.

Linking the database

1. In SQL Server Management Studio, create a new database, with the name WidgetDevelopment
2. In the Object Explorer, ensure WidgetDevelopment is selected. On the Setup tab, click Link database to source control

   The Link Database to Source Control dialog box is displayed.

3. Under Source control system, on the left hand side, ensure More is selected.
4. Type, or browse to, the location of the local working folder.

   The Mercurial config file in the working folder is selected automatically.

5. Click Link.

   A link to the repository is created, and SQL Source Control is able to determine differences between the database and the repository.

   However, the database has not yet been updated with the objects from source control.

Getting the latest version

1. Click the Get Latest tab.

   Because you do not yet have any of the objects in your database, all the objects in WidgetDevelopment are listed here.

   

2. Ensure all of the objects are selected.
3. Click Get Latest.

   A progress dialog box is displayed while SQL Source Control updates the database.

   The database is updated to the latest version.

5. Make development changes

Development proceeds normally, and the database is modified.

A column is changed in the table Widgets, to allow longer widget descriptions. This change is committed to source control.

1. In SQL Server Management Studio, open a new query window, and type or paste the following SQL statement:

   USE WidgetDevelopment

   GO

   ALTER TABLE [dbo].[Widgets] ALTER COLUMN [Description] [nvarchar] (100)

2. Click Execute or press F5

   The script runs; the database is updated.

3. SQL Source Control detects the change to the database, and highlights the affected object in the Object Explorer:

   

You can now commit the change.

6. Commit the development changes to source control

1. In the Object Explorer, right click the table Widgets, and click Commit changes to source control

   The Commit changes tab is displayed:

   

   The change to the table Widgets is listed as an edit you can commit.

   The Object Differences pane shows the difference between your database version, and the latest version in source control.

2. In Comment to add on commit, type or paste a comment.

   In this example, type Modified Description column

3. Click Commit

   SQL Source Control displays a message dialog box that shows the progress of the commit.

   When the commit is complete, click OK to close the message box.

Your change is committed to source control. The Commit Changes tab lists no objects with changes to commit, and no objects are highlighted in the Object Explorer.

Other users can now get the latest version of the table.