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).
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:
- Create the new database with a script
- Link the database to the source control
- Choose the model
- Choose the type of SCM (Working Folder)
- Map the folder
- Apply the comparison option
- Apply the filter
- 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: