Worked example: Subversion (SVN)

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

This example uses:

• The Subversion source control system
• The Tortoise SVN client for Subversion

To follow this example, you should download and install the latest version of TortoiseSVN

In the example, the Magic Widget Company has a SQL Server database running on a live web server. 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 get their own dedicated copy to work on.

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 directory in the repository

1. In a Windows Explorer window, right-click, and from TortoiseSVN select Repo-browser:

   

   Note that the TortoiseSVN menu does not appear if the folder you right-click is a Windows 7 library.

   The URL dialog box is displayed.

   

2. In URL, type or paste the URL for your repository, and click OK

   The Repository Browser is displayed:
   

3. In the Repository Browser, in the left pane, select the folder in the repository where you want to source control the database. Then right-click the folder, and select Create folder:

   

   On the Create Folder dialog box, name the folder WidgetDev and click OK

   You may be prompted to supply a comment.

   The folder is created.

4. Right-click the new folder, and select Copy URL to clipboard

   You can now use the URL of this folder on the Setup tab in SQL Source Control to create a link between your database and the repository.

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 Subversion is selected.
5. In Repository URL, type or paste the URL for the folder you created.

   Note that the repository URL is case sensitive.

6. Under Development Model, ensure Dedicated is selected.
7. Click Create 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 now able to determine differences between the database and the repository.

   The database icon in the Object Explorer changes, showing 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 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.

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 Subversion is selected.
4. In Repository URL, type or paste the URL of the database in source control.

   Note that the repository URL is case sensitive.

5. 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.