Worked example: setting up SQL Source Control with TFS for development teams

This example demonstrates how to set up database source control so that teams of developers can work on a database update.

This example requires:

• The Team Foundation Server (TFS) source control system
• Microsoft Team Explorer client for Visual Studio

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 folder and link the database to that location.

Create a folder in source control

1. Open Visual Studio if it is not already running, and in the Team Explorer pane, under the server you are using, double-click Source Control

   The Source Control Explorer tab is displayed:
   

2. In the Folders pane, browse to your source control location.
3. In the file list, right-click, and click New Folder

   A folder is created. Name it WidgetDev

4. Right-click WidgetDev, and click Check In Pending Changes

   The Check In dialog box is displayed.

5. Type a comment, and click Check in

   The folder is committed to source control.

Create a link to source control

1. Open SQL Server Management Studio if it is not already open.
2. In the Object Explorer, select the WidgetDevelopment database, right-click, and click Link database to source control

   The SQL Source Control Setup tab is displayed.

3. Click Create new link to source control

   The Create Link to Source Control dialog box is displayed:

   

4. Under Source control system, on the left hand side, ensure Team Foundation Server is selected.
5. In Server URL, type or paste the URL for the server you are using if it is not already filled in.
6. In Source Control Folder, type or paste the location of the WidgetDev folder you created.

   Alternatively, click Browse and browse to the location of the folder.

7. Click Create Link

   You may be prompted for login credentials for your source control repository.

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

   The database icon in the Object Explorer changes, indicating that the database is linked to source control, and that there are changes to commit:

   

   Note that the database objects have not yet been committed to source control.

3. Commit the database objects to source control

To complete the process of 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 begin making development changes.

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

Linking the database

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

   The Link Database to Source Control dialog box is displayed.

3. Under Source control system, on the left hand side, ensure Team Foundation Server is selected.
4. In Server URL, type or paste the URL for the server you are using if it is not already filled in.
5. In Source Control Folder, type or paste the location of the WidgetDev folder.

   Alternatively, click Browse and browse to the location of the folder.

6. Click Link

   You may be prompted for login credentials for your source control repository.

   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.