Automatically link databases to Red Gate SQL Source Control

For the ones that have many databases to keep under source control, it can be really useful to speed up the link-to-source-control process. The only way we have now is to use the GUI provided by Red Gate SQL Source Control. Actually, there’s a github project called SOCAutoLinkDatabases by Matthew Flat, a Red Gate engineer, but, unfortunately it works only on a shared database model (centralised) in TFS. Let’s see how to manage the link using Working Folder (which is also good for many SCM) and dedicated database model (distributed).

The problem

We have ten databases for each developer and we are working on a dedicated database model sandbox. We have frequent changes, merges and we are dealing with a continuous integration environment. Thus, we need to redo the databases often. This means that we need to drop/unlink them often. Why? because we generate many custom/fake data and we often must delete them. In most of cases, it is quicker to drop the database instead of writing code. Say, we are developer in a development environment, we really want to get the latest version with no obstacles.

I’m used to follow these steps for each database manually:

1. Create the new database with a script
2. Link the database to the source control
   • Choose the model
   • Choose the type of SCM (Working Folder)
   • Map the folder
3. Apply the comparison option
4. Apply the filter
5. Apply the latest version

The first two options (I’m a pretty quick one) took 1 minute (with no errors). Being optimistic, 10 databases, 10 minutes. Since we are on a release branch strategy, that value should be multiplied by the number of the branches, but it’s not mandatory to get them all, actually.

Let’s speak about 10 minutes, 7 developers. More than 1 hour.

I have thought that I could save time with automation. Unfortunately, the SQL Source Control API does not provide commands for getting from the source control and applying changes to the SSMS hosted database. Thus, I could automate just point 1 and 2.

Actually, with comparison SDK (which requires a license) it is possible to diff the SCM workspace folder with the database in order to bring it to SSMS, but it is not free, and it cannot be shared on the team, due to the license per developer.

The UnlinkDropRelinkDatabases.ps1 PowerShell script

With UnlinkDropRelinkDatabases.ps1 PowerShell script, we can link automatically databases to source control also in case of Working Folder management (which includes the most famous distributed SCM). It’s an extension of the script of Matthew (my thanks to him). In the attached package you can find:

root folder (VSTS workspace)

  – dev (branch folder)

    – foo.db1 folder (workspace db working folder)

    – foo.db2 folder (workspace db working folder)

    – foo.db3 folder (workspace db working folder)

  script.ps1

Moving these paths is possible, but the script must be changed on the top, in a dedicated area. The script will work only if SSMS is closed, if not, PoSh prompt will advice you to close the IDE.

Header – functions

The first part of the PoSh script is a set of useful function that are used later in the ps1.

Global settings section

In the global settings area there is the setup of your database folder, your database names and .sql scripts for creating the databases. If you would like to test the script on your paths, you should change the values of the variables with your naming conventions.

Working variable section

This section includes the folder in which SoC is installed and configured (I’ve tested only versions 4 and 5), the composition of the database name (by branch), a DROP DATABASE script and the list of the linked databases, taken from the configuration xml file.

The DROP DATABASE script is mandatory. Indeed, before proceeding to create and link a database, it will be dropped, if exists.

Connection section

In this section, the discovery of the instance will be made. However, the PoSh prompt will ask you the name of the named instance (optional).

Link database section

Before starting to link databases the base folder is created. The first lines of code implements a foreach loop (which loops on the soft coded collection of databases created on the top of the ps1) removes the nodes and folders previously mapped on the Red Gate local folder into the local app data Windows folder (functions RemoveAndSaveXmlConfiguration and RemoveWorkingBaseAndTransientFolders).

After this step, the database is dropped, if exists, and created (functions DropDatabaseIfExists and CreateDatabaseFromScript).

When the database is created, it is time to add the new configurations and to map the new folders into the xml. Finally, the source control workspace can be copied to the Red Gate SQL Source Control under-the-hood folders (functions CreateWorkingBaseAndTransientFolders, AddAndSaveXmlConfiguration and GeneratingWorkingBaseAndTransient).

This loop repeats for each database, however, when you execute the script, a menu pops up and you can choose between executing commands on a single database or managing every databases.

Feel free to contribute here