Version control for SQL Server Management Studio templates using Azure Repos

Reading Time: 6 minutes

In this post I want to cover how to do version control for SQL Server Management Studio templates using Azure Repos.

I wanted to do this post for various reasons. However, I am mostly doing this post because my post about using version control for SQL Server Management Studio templates has been viewed nearly every single day this year.

With this in mind, I thought it would be a good idea to do a post about how to do it using Azure Repos as a Christmas present for those who enjoyed the original post. Plus, it gives me a chance to go into more detail about some things.

I want to cover a couple of things before I go too far into this post.

First of all, for those of you who are not aware Azure Repos is one of the default services that comes with Azure DevOps. It is the service you use to store your Git repositories.

Secondly, I recommend reading a couple of other posts before this one. Which are as follows:

• How SQL Server Professionals can benefit from using version control
• Increase in demand for Data Platform automation

SQL Server Management Studio templates

Within SQL Server Management Studio you can take advantage of Template Explorer to quickly open up templates that contain common SQL scripts. You can view this by clicking on the ‘View’ menu item and then selecting ‘Template Explorer’.

When you first open it you will see that there are templates already created for you. As you can see below. In addition, you can create your own templates for use by right clicking inside the Template Browser.

You can read more about what you can do with these templates by reading the Microsoft guide about how to use templates in SQL Server Management Studio.

Before you do either, you need to find out where your template folder is currently stored.

There are a couple of ways you can put a copy of your template folder into Azure Repos. You can either convert your template folder so that it is also a local Git repository or just copy the contents of the folder into an existing Git repository in Azure Repos.

By default, you can find the templates for the latest version of SQL Server Management Studio in ‘C:\Users\{USERNAME}\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\Templates\Sql’. Remember to replace {USERNAME} with your windows user name.

In the below example I cover how to convert your template folder so that it is also a local Git repository. Afterwards I show how to synchronize it with Azure Repos.

Local Git repository

Now, there are various ways you can make the folder a Git repository. To make this example relatively easy to follow I set it up using Visual Studio Code.

First of all, I opened up the folder in Visual Studio Code. I then went into the ‘Source Control’ section where it straight away asked me to ‘Initialize Repository’.

From there I did an initial commit. Which is basically the first commit that you do inside a repository.

To test that it worked I made a change to a test template file that I had created in SQL Server Management Studio. Once I saved the change to the file, I went back into Visual Studio Code and checked that Git recognized the change.

Once I had confirmed that my Git repository was working locally, I then looked to synchronize it with a new repository in Azure Repos.

To help with some jargon, I mean setup my local copy of the Git repository so that it is linked to another Git repository that is in Azure Repos.

SQL Server Management Studio templates in Azure Repos

I first created a new Git repository in Azure Repos. I kept its setup simple for the sake of this post.

Afterwards, I then added the new repository as a remote in Visual Studio Code. So that my local Git repository would be linked to the new one in Azure Repos.

Once I had done that, I synchronized my local repository to the remote one by using the ‘Push’ menu item. I then refreshed Azure Repos in my web browser to make sure that the repository had synchronized okay.

To test that it all worked I made another change to my template file. I then committed the change to my local copy of the Git repository in Visual Studio Code. Just like I had done before.

From there I ran the ‘Sync’ command to synchronize my local repository with the one in Azure Repos.

Afterwards, I went back in to Azure Repos to check that the update had taken place.

Sharing templates in Azure Repos one step further

Synchronizing your local template folder with a central Git repository hosted elsewhere opens up the possibility of sharing custom SQL Server Management Studio templates within your company.

It allows you and your colleagues can create your own repositories locally. From there you can share them easily with each other by making use of the central repository. As you can see in the diagram below.

In reality, you can do this without the central Git repository. By adding each others Git repositories as remote ones. However, this can make things more complex.

It makes things a lot easier if you use a central one as a remote instead. Personally, I also think it also allows you to manage version control better as well.

You can also use this method to share templates and configuration files for other applications as well. I list some of them in my post about using version control for SQL Server Management Studio templates.

If you work with the Microsoft Data Platform you can use Azure Repos for a lot of other things as well. For example, you can use it to store database projects for both SQL Server and Azure Synapse Analytics SQL Pools.

You can then uses them as a source for Azure Pipelines to do various deployments. For example, CI/CD for serverless SQL pools.

More Azure DevOps?

If you are keen to learn more about using Azure DevOps, you can attend the training day by myself and Sander Stad at SQLBits 2022. Which I covered in my post about our Azure DevOps based Training Day at SQLBits 2022.

Final words

I hope this post about how to do version control for SQL Server Management Studio templates using Azure Repos proves to be useful. Especially since the original post this is based on appears to be so popular.

Plus, I also want to wish you all happy holidays.

Of course, if you have any comments or queries about this post feel free to reach out to me.