I’ve already spoken about source controlling database using Visual Studio Online and Red-Gate SQL Source Control in this post. The described kind of approach brings a drawback, due to the nature of the plugin and VSO APIs: High latency when getting and syncing local database and workspaces.

Due to this problem, I’ve changed my settings when linking my databases, switching them from “Team Foundation Server (TFS)” to “Working folder“, as in the following picture:

When we link a database to a source controlled working folder, we’re using a local folder in which we’ll put files instead of calling the source control manager APIs. We gain “speed” when syncing with local folders, but we lose the direct online repository synchronization. Actually it’s not a big deal, because the most part of developers is using Visual Studio as IDE (or an equivalent product) and it’s syncing local workspaces with those tool. Without a source control management client, is more difficult to apply the “Working Folder” approach (for a team explorer client out of Visual Studio, see Team Explorer Everywhere).

Actually Team Explorer is mandatory when creating a new workspace. When we got a workspace, we can use power tools shell integration on Windows in order to manage the source controlled items with right-click menu.

How the “Working Folder” mode works?

Suppose to have a local workspace mapped to a physical path like “C:\TFS_Workspace\Private\“. This is the path which is mapped by Visual Studio Online remote repository:

The database is linked to the “C:\TFS_Workspace\Private\Databases\MyDatabase1” path. Every change made to that folder has to be managed via Team Explorer in Visual Studio. More details about Team Explorer here.

How to get the latest version and sync from the source control online repository

Using Team Explorer, we can get the latest version of a project/file/folder. Actually we can get certain versions based on labels, date, changeset number and so on.

In this sample, we’re getting a table from the source control, which is stored as a .sql file in the “C:\Workspace\Private\Databases\MyDatabase1\Tables” path (notice that Tables is a folder created by Red-Gate tool automatically). The table is not available on the database but it’s on the filesystem, so we need to apply changes to the database.

Filesystem

Database status

As you can see the Tables folder got a file (dbo.Foo.sql table DDL script) and MyDatabase1 is not synced with the downloaded version. So, we need to go to the source control tab in order to apply changes on the database:

After this action, the table is created. This operation is the same of the “Team Foundation Server (TFS)” mode. Red-gate SQL Source Control creates under the hood working bases (hidden folders into the AppData path) in order to sync the local databases with the downloaded versions.

How to create/save a changeset 

Let’s do some change to the database.

Create a stored procedure with the following script:

CREATE PROCEDURE dbo.proc_Foo_Get
AS
BEGIN
SET NOCOUNT ON;
SELECT
id
, fooData
FROM
dbo.Foo
END;
GO

Modify the dbo.Foo table with the following ALTER:

ALTER TABLE dbo.Foo ADD InsertTimestamp datetime NOT NULL DEFAULT(GETDATE());

Now, go to the source control tab called “commit changes” and watch the new changes ready to be committed:

Pressing the “Save changes” button stores the scripts to file. Keep in mind that we’re speaking about filesystem, not remote source control. So, this COMMIT phase, saves FILES and does not send file to the online repository.

How to sync with remote online repository

The source control tab in SQL Server Management Studio is totally empty now. All changes are stored to the filesystem. We need to sync the online repository hosted by Visual Studio Online. As in get phase, we need to use Team Explorer. Since we’re not editing scripts with Visual Studio (we’re changing objects using SSMS) the first checkin/delete operations of an object are not available on the “Included changes” section. We can find them into the “Detected” area of the “Pending Changes“:

Table dbo.Foo is changed and it’s visible in the “Included Changes“. The other change is under the “Detected” section. We’ve to “Promote” the files:

The latest operation is to checkin (sync with the online repository). Don’t miss the checkin comment and press checkin button.

That’s it.

Conclusions

As we can see, there are some differences between Visual Studio Online direct link and “Working folder” approach:

1. We need to use a source control management client out of SQL Server Management Studio in order to sync the local workspace with the Visual Studio Online remote repository.
2. The commit phase stores the changeset to filesystem, does not trigger any send to the repository of Visual Studio online.
3. First-time added/deleted objects are candidate changes. They’re not ready to be committed.

These operations seem, let’s say, complicated, but they completely remove latency issues related to the get/commit phases (using Visual Studio Online APIs). After a short learning curve, we can improve our productivity and control of your code to be committed. Additionally, we will use a single-point of commit instead of SSMS + VS. With this pattern, all changes (app + db) are committed by Team Explorer. If you put just SQL Server database under source control (I guess not the most cases) this approach brings an overhead (more tools needed).

Stay Tuned!