Version control for SQL Server Management Studio templates using GitHub

Reading Time: 7 minutes

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

I wanted to do this post for various reasons.

Previously I wrote a post about how to do version control for SQL Server Management Studio templates using Azure Repos. I wanted to highlight some things I did not point out in that post. In addition, I thought it was only fair that I showed how to do it with GitHub.

Plus, in my last T-SQL Tuesday post I mentioned the SQL Server diagnostic queries provided by Glenn Berry. Which reminded me to do this post. Because I want to do an example based on sharing one of the queries with your colleagues via GitHub. Like in the below diagram.

SQL Server Management Studio

Something I want to point out in this post is that SQL Server Management Studio can be used with various SQL Server related services. Including SQL Server Analysis Services and Azure Synapse Analytics SQL Pools.

Within SQL Server Management Studio you can open up templates that contain common scripts for SQL Server related services using Template Explorer. You can view this by clicking on the ‘View’ menu item and then selecting ‘Template Explorer’.

In reality, there are a variety of use cases for these templates. For example, running various security scripts.

In addition, they can be used to save popular scripts that are available online. Such as the SQL Server diagnostics scripts provided by Glenn Berry. Which caters for various versions of SQL Server including Azure SQL Database.

In this post I am going to cover how you can share your local template folder that contains these scripts with your colleagues. So that you can all share them between yourselves and all have the same templates locally.

I have used the missing indexes script a lot over the years, so I will demonstrate using that one.

Creating local Git repository

In my previous post about how to do work with templates with Azure Repos I showed you how to create a local repository using Visual Studio Code. In this post I will show you how to do it with Azure Data Studio. Which you can install with SQL Server Management Studio.

First of all, I opened up the location of the template folder in Visual Studio Code. By default, the folder for the latest version of SQL Server Management Studio can be found in ‘C:\Users\{USERNAME}\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\Templates\Sql’.

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. I did this by entering a comment and then clicking on the tick icon.

Once that was done my local template folder was also a local Git repository.

Adding a new template

I then went to the SQL Server Diagnostic Queries site. From there, I downloaded the latest version of the Azure SQL Database Diagnostic Information Queries.

To test that it worked I went back into SQL Server Management Studio and created a folder called ‘Diagnostic Queries’ in the template explorer. I then created a new template called ‘Missing indexes by Index Advantage’.

Afterwards, I right clicked the new template and selected ‘Edit’. I then copied into it the script to find missing indexes for current database by Index Advantage. Which has helped me a lot in the past. From there, I saved and closed the template in SQL Server Management Studio.

I then went back into Azure Data Studio. It was already showing that there was a new file that needed to be committed to the Git repository. In other words, a change to the files which had not been hardened to Git. So, I committed the change.

Once I had committed the new file, I was ready to synchronize it with a new repository in GitHub.

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

SQL Server Management Studio templates using GitHub

To start the process, I first created a new Git repository in GitHub. I kept the setup of it simple for the sake of this post. As you can see below.

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

Because my repository was in GitHub, I selected the ‘Add remote from GitHub’ option.

Before we go any further I just want to point something out. If you encounter an issue when you try to add a remote from GitHub, you might have to create what is known as a ‘personal access token’ in GitHub. So that you can enter it into Azure Data Studio. GitHub provides a guide on how to create a personal access token.

It might seem like a bit of a fuss. However, adding a remote from GitHub adds a new experience when you go to select new remotes in GitHub with Azure Data Studio. Because it allows you to find various GitHub repositories easily. As you can see below.

I selected my GitHub repository and gave the remote the name origin to keep inline with industry standards.

Once I had done that, I synchronized my local repository on my laptop to the remote one in GitHub by using the ‘Push’ menu item.

I then refreshed GitHub in my web browser to check that the repository had synchronized. In addition, I clicked the ‘Go to file’ button to confirm that the template that I added was there.

Sharing templates in GitHub

As I mentioned before, synchronizing your local template folder with a central GitHub repository hosted elsewhere opens up the possibility of sharing custom SQL Server Management Studio templates within your company.

Take the above example. You can update the diagnostic queries locally. Afterwards, you can share them easily with your colleagues by making use of the central repository. As you can see in the diagram below.

Of course, this can be used to share other templates as well. For example, custom templates to work with particular SQL Server databases or SQL Pools. You can read the Microsoft guide about how to use templates in SQL Server Management Studio for more ideas.

In reality, you can do this without the central GitHub repository. By adding the local Git repositories on your laptops as remote ones. However, this can make things a lot more complex.

It makes administration a lot easier if you use a central repository as a remote instead. Personally, I also think it also allows you to manage version control better as well. Plus, you get to use other GitHub features with your repository.

Other uses GitHub

GitHub is well known for storing code for open-source projects such as applications and PowerShell modules.

In addition, you can also use this method to share templates and configuration files for other applications as well. I listed some in my post about using version control for SQL Server Management Studio templates.

If you work with the Microsoft Data Platform you can use GitHub for other things as well.

For example, you can use it to store the code for SQL Server related database projects. Which you can use to create dacpac files for SQL Server and Azure Synapse Analytics SQL Pool deployments.

If you use a GitHub repository you can also look to use GitHub Actions. Which will allow you to do CI/CD on objects stored in GitHub. For example, there is a GitHub Action for Synapse workspace deployment.

Plus, you can use GitHub Actions to do CI/CD with the database projects I mentioned previously. I covered ways to do this other posts. Like the one I wrote about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions.

You can find out more about the GitHub features in GitHub Docs.

Final words

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

Plus, I really hope this encourages some of you to think about using version control for more things. It might seem hard to learn but it will bring you benefits long term.

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