Skip to content

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.

Diagram showing how yourself and others can synchronize with a GitHub repository to share SQL Server management Studio templates.

Octocat logo courtesy of GitHub
Octocat logo courtesy of GitHub

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’.

Template Explorer in SQL Server Management Studio
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’.

Initialize repository in Azure Data Studio
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.

Initial commit of SQL Server Management Studio templates in Azure Data Studio
Initial commit

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.

Committing a new template in Azure Data Studio
Azure Data Studio already showing a new file

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.

Creating a new GitHub repository for the SQL Server management Studio templates
New GitHub repository

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.

Adding a new remote in Azure Data Studio
Adding new remote

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.

GitHub experience in Azure Data Studio
GitHub experience in Azure Data Studio

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.

Finding the new template in GitHub
Finding the new template in GitHub

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.

Diagram showing how yourself and others can synchronize with a GitHub repository to share SQL Server management Studio templates.

Octocat logo courtesy of GitHub
Octocat logo courtesy of GitHub

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.

Published inGitHubGitHub ActionsSQL Server

4 Comments

  1. Beefy Dog Beefy Dog

    Looks like MS changed something (again!), getting the dreaded message from github

    Please upgrade VS Code

    We’ve made some updates to our authentication process for VS Code and GitHub. Please upgrade to the latest release of VS Code (1.66+) or download from here.

    Which is completely WRONG – I have the latest version of VS Code installed!
    RANT:
    Why version control has become such a disconcerted nightmare, messy, extremely buggy, and takes an insane amount of time just to configure, is beyond me. TFS was manageable and just plain worked! (I’ve lost countless hours by a slight command line typo on both Git & Github and even lost data on a couple of occasions – it was designed and written by sadists – It’s f’in 2022 and we’re still mucking about on command lines like it’s the 1980s – what gives? why the aversion to GUIs? Some of us just want to write code and get work done and not have to “experiment” with CLIs! (one small command line error and POOF! work destroyed, time and money wasted, and more years taken off your life (no wonder so many grads quit their jobs after a few months – no degree can prepare one for the mess that software development has become and the latest FAD coding paradigm every 6 months – not to mention the constant deprecation of stuff you took a few years to master, then it’s obsoleted overnight and replaced with a “must have” paradigm that simply reinvents the wheel and makes development more complex than it needs to be CI/CD is more like “let’s put garbage code up so it looks like we’re moving quickly, THEN fix it later”.
    Git/Github is a nightmare to configure (especially with Azure Data Studio – you have to change tutorials every 3 months just to learn anything and docs are continually outdated). Why don’t they just write in REAL version control (not files, but actual Tables, Views, SPs, UDFs, etc) into SSMS and leave it at that?

Leave a Reply

Your email address will not be published.