Getting a database under version control: How to link a database to source control and initially commit objects (4276685)

Challenge

For the purpose of versioning a SQL database, there should be a mechanism that allows for quickly linking a database to an existing or to a blank repository and to initially Commit all database objects, or at least a set of objects that needs to be versioned.

We also want a mechanism to version static data, that is data that generally doesn’t change like zip codes. It can be considered akin to object definitions themselves and is often included in the initial commit step. Static data is out of the scope of this article but will be covered in a subsequent article, How to link and initially commit SQL Server database static data

This article covers the linking process of a database as well as the initial commit of all database objects that will be versioned.

In this article, the term linking means to create a connection between a database and a source control system. We assume that both a database and an existing source control (e.g. Subversion) repository exists. We’ll use the term commit to mean to push, or otherwise script and place your database object definitions into source control. With the database objects scripted and placed under source control, and the link established between your database and the repository, you will not only be able to re-commit new and or changed objects, but also to work iteratively between the systems, making sure each database change is updated in the source control system simultaneously.

Solution

ApexSQL Source Control is a 3^rd party tool for versioning SQL Server databases under source control. By saving database objects as SQL files that can be committed to source control, it can track any DDL change. ApexSQL Source Control natively supports all popular source control systems such as Git, Team Foundation Server, Subversion, Mercurial, and Perforce. It can be used with any other source control system that supports the CLI, as well as with the local folder, in case there is no any source control system in place.

For the purpose of this article, we’ll use a blank Subversion repository. Pubs database will be used as an example. The goal is to link a database to source control and to initially commit all database objects to source control, so they can be versioned after that.

In order to link Pubs database and initially commit all objects, the following steps should be performed:

1. Start SQL Server Management Studio
2. Connect to a SQL Server instance that hosts Pubs database
3. Right click a database in the Object Explorer pane and from the context menu, select the Link database to source control option:

4. This initiates the Source control setup wizard, specifically the Connection type step. For the purpose of this article, we’ll select the Subversion system, from the Source control system drop down list:

5. The Development model step offers an option to link a database using the Dedicated or Shared model. The Dedicated model means that each developer is working on his own local copy of a database, but the team is targeting the same repository. On other hand, the Shared model means that the entire team is working on (linking) the same database to the same repository. For the purpose of this article, the development model used is irrelevant, as the linking process as well as the initial commit is the same. In any case, we’ll choose the Dedicated model:

6. The Object filtering step allows the user to filter any object (or a set of objects) from being versioned. Objects can be filtered by schema, by object type, or on an individual level. Just by unchecking the appropriate boxes, specific objects, or a set of objects (tables, procedures) can be excluded from being linked (versioned). The user can get back to this step at any point later, to refine the filtering (in case any objects need to be included or excluded). We’ll go will all objects selected, for the purpose of this article:

7. The Script options tab allows the user how to set the way database objects will be shown. Specifically, there is a set of object attributes that can be ignored in the comparison process, and in that way, show only differences that are relevant for the user. We’ll leave all options unchanged in this case:

8. The System login step requires the user to provide source control credentials as well as the repository URL. Optionally, a separate folder inside the repository can be used to link a database to. In fact, it is recommended that each linked database have at least one separate folder inside the repository due to a folder structure that ApexSQL Source Control creates. We’ll specify a Subversion user (in this case ApexSQLDev) and credentials, along with the repository URL:

9. Once everything is set in the final step, clicking the Finish button initiates the linking process. The linking process means that all previously selected database objects will be scripted. After the scripting is done, the latest state from the repository will be pulled from the repository in order to compare it with already scripted objects. The result of the comparison process is shown in the Action center tab that presents the main communication channel between a database and source control:

10. The comparison result shows that all objects exist in a database and no objects are found in source control. This is because the Pubs database is linked to an empty repository, so the right side of the Action center tab shows no objects at this point while the left side shows all the objects from a database. Because ApexSQL Source Control is aware that a repository is empty (this means that the initial commit is about to be performed), all objects will be checked by default and the Action column show the Commit operation:

11. To perform the initial commit, we’ll need to provide a commit message as it is required by default. After the commit message is specified, clicking the Apply button will commit all objects to source control (simply uploads all scripts to the specified repository):

    After the initial commit is done, ApexSQL Source Control will re-compare Pubs database with source control, and again, show comparison results in the Action center tab:

    This time, there will be no differences, since all objects are committed. The Action center tab shows that a database and source control are synced. From this point, Pubs database is under source control and any additional changes made against versioned objects will be shown in the Action center tab.

    By checking the repository, we can verify that all objects are committed:

    In addition to this, we can verify specific files, by browsing the appropriate folder (for instance the stores table):

    Using the described workflow any database can be linked to source control and objects initially committed.