Git Blitzed: An Experiment of Automation with Git, PowerShell, and SQL Server

Introduction

Since there is no known way to receive SQL Server First Responder Kit patches automatically, I contrived a plan to avoid the scenario below:

6 months? Ok, well I’m not doing this manually. Where is the fun in that?

I. Install These Things

Let’s lay some of the foundations that will make this work.

Verify PowerShell version >= 5.0

This version or higher will allow you to access the PowerShell Gallery. It’s a package manager for PowerShell, making downloading and installing cmdlets a whole lot easier than it used to be.

To see your PowerShell version run $PSVersionTable in… PowerShell.

$PSVersionTable

Grab a copy of Git

During the install, I left all of the default options but paid special attention to the checkbox below. Super convenient.

II. Getting Warmed Up

I created a directory on my dev box -> C:\Temp (this is where I hoard all my cool projects).

Fired up a Git terminal by right-clicking C:\Temp -> “Git Bash Here”. In the terminal, I pasted in the link to clone the repo for SQL Server First Responder Kit. The highlighted Blitz in the image below will be the root directory of the clone.

Just like most Git commands, there is no shortage of options and switches. If you get bored one day check out Git’s fantastic documentation.

Voila!

 

III. The Next Level

I prefer to use bash shell for Git, but for this exercise, I’ll be adding Git to my PATH for PowerShell execution.

git.exe is here by default -> C:\Program Files\Git\cmd and also here -> C:\Program Files\Git\bin

Copy the path of both paths and paste it in your… PATH. The system… PATH.

You’ll need to open a new instance of PowerShell for the changes to take effect but once you do you can check the status of Git.

But not too satisfying to look at, right? Let’s change that.

Install PoshGit

Open an administrative PowerShell session and execute the following script:


Install-Module PowerShellGet -Force -SkipPublisherCheck
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
Update-Module PowerShellGet -Force

Install-Module Posh-Git -Scope AllUsers

After you get Posh-Git installed you’ll have two options to activate it.

# Option 1 - This command will add Import-Module Posh-Git to your PowerShell profile and will 
# load every time PowerShell starts up. Most convenient if you'll use Git and PowerShell often.
New-Item -Name $(Split-Path -Path $profile) -ItemType Directory -Force 'Import-Module Posh-Git' | Out-File -Append -Encoding default -FilePath $profile

OR

# Option 2 - Stick this at the top of all your Git scripts
Import-Module Posh-Git

Enter PowerShell

Before we start, delete the Blitz directory. We’ll want a clean slate to make sure it’s working.

Now the fun part. Let’s write some PowerShell. Run this script and watch the Blitz directory reappear. This is why I love computers.


# Local Variables
$base_path = "C:\Temp"
$local_repo_name = "Blitz"

# Remote Variables
$remote_repo_url = "https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit.git"

# Git commands
$clone_cmd = "git clone " + $remote_repo_url + " " + $local_repo_name;

# Change the working directory
CD $base_path

# Clone the repository as long as there is no clone in existence
IF ( -not (Test-Path( $local_repo_name )))
{

Invoke-Expression $clone_cmd

}

IV. Further

I can’t help myself. I need to make sure I always have the latest copy of sp_Blitz.

Versioning

To get the latest and greatest copy of the repository, we’ll add a few new lines of code.


# Local Variables
$base_path = "C:\Temp"
$local_repo_name = "Blitz"
$local_repo_directory = $base_path + "\" + $local_repo_name

# Remote Variables
$remote_repo_url = "https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit.git"

# Git commands
$clone_cmd = "git clone " + $remote_repo_url + " " + $local_repo_name;
$merge_cmd = "git pull origin master"

# Change the working directory
CD $base_path

# Clone the repository as long as there is no clone in existence
IF ( -not (Test-Path( $local_repo_name )))
{

Invoke-Expression $clone_cmd

} ELSE {

CD $local_repo_name
Invoke-Expression $merge_cmd

}

To test out the versioning concept, you’ll need to Reset your HEAD. Instead, for a visual, let’s just move it for a moment. Yeah I know it’s weird. Follow me.

Look at the line with SHA1 hash 5a0bb1. If you look to the right, you’ll see that this is where HEAD is attached, meaning, this is the current working directory.

Move it and see what happens.

git checkout 2ea7bde

You will see this message -> HEAD is now at 2ea7bde Add check constraint info 

We can look at the log again, this time adding the branch name to the end and we can see where HEAD is located in comparison to the dev branch.

git log --oneline -10 --decorate dev

And… back to the dev branch.

git checkout dev

Crap, I’m on the Dev Branch

Git is super flexible and that’s one of the greatest features of the product. However, your admin policies are hopefully a little less flexible; we can’t be havin’ experimental features hitting our SQL Servers.

This blog is about solving problems so let’s investigate.

What branches do I have in my local repo?

git branch

Hmmm, ok. Let’s check the remote.

git branch -a

That’s the one we want. Let’s adjust our PowerShell script to grab this branch and allow the flexibility to grab any of them. It’s probably a good time to add a bit of error checking as well.

Once again, delete the Blitz directory to see how this works and then we’ll execute the following script.

# Local Variables
$base_path = "C:\Temp"
$local_repo_name = "Blitz"
$local_repo_directory = $base_path + "\" + $local_repo_name

# Remote Variables
$remote_repo_url = "https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit.git"
$branch_name = "master"

# Git commands
$clone_cmd = "git clone " + $remote_repo_url + " --branch " + $branch_name + " " + $local_repo_name;
$merge_cmd = "git pull origin"
$branch_check_cmd = "git ls-remote --heads " + $remote_repo_url + " " + $branch_name

# Change the working directory
CD $base_path

# Check the existence of the remote repo and capture the number of rows that match
# in the result set of the query in $branch_check_cmd.
# Then clone or merge, depending if the local repo exists.
$number_of_matches = Invoke-Expression $branch_check_cmd | Measure-Object

IF ( $number_of_matches.Count -eq 1 ) {

IF ( -not (Test-Path( $local_repo_name )) ) {

# The local repo does not exist, let's grab a copy
Invoke-Expression $clone_cmd

} ELSE {

# The local repo exists, commence merge to pull in most recent changes
Invoke-Expression $merge_cmd

}

} ELSE {

$wshell = New-Object -ComObject Wscript.Shell
$wshell.Popup("Crap, something went wrong.",0,"Error",0x1)

}

I’ll break down some of the changes in the code above.

  • $branch_name – entered the target branch for the remote repo
  • $clone_cmd – modified to include the target branch
  • $branch_check_cmd – returns a list of remote branches that match the branch name
  • $number_of_matches – returns a Measure-Object object of the output from $branch_check_cmd
  • With the new variables, we are able to run through conditional statements to decide which Git command to execute (see comments in code).

Results of our updated PowerShell script:

Now, there is only one thing left to do!

V. Bringing It All Together

Disclaimer: It goes without saying, but I’m going to say it anyway. This is an experiment. You should run experiments in an experimental appropriate environment, e.g., Dev, Test, local machine, etc. Unless of course, your local machine is production; then, you should probably rethink your setup and bookmark this page for later.

Now that disclaimers are out of the way, let’s get a new module installed and our stored procedure updated in SQL Server.

Install SQL Server PowerShell Module

Open an administrative PowerShell session and execute the following script to install the SQL Server PowerShell module:

Install-Module -Name SqlServer

Push to SQL Server

You guessed it! A few additional lines of code will be needed.


</pre>
# Local Variables
$base_path = "C:\Temp"
$local_repo_name = "Blitz"
$local_repo_directory = $base_path + "\" + $local_repo_name

# Remote Variables
$remote_repo_url = "https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit.git"
$branch_name = "master"

# Git commands
$clone_cmd = "git clone " + $remote_repo_url + " --branch " + $branch_name + " " + $local_repo_name;
$merge_cmd = "git pull origin"
$branch_check_cmd = "git ls-remote --heads " + $remote_repo_url + " " + $branch_name

# Change the working directory
CD $base_path

# Check the existence of the remote repo and capture the number of rows that match
# in the result set of the query in $branch_check_cmd.
# Then clone or merge, depending if the local repo exists.
$number_of_matches = Invoke-Expression $branch_check_cmd | Measure-Object

IF ( $number_of_matches.Count -eq 1 ) {

IF ( -not (Test-Path( $local_repo_name )) ) {

# The local repo does not exist, let's grab a copy
Invoke-Expression $clone_cmd
CD $local_repo_name

} ELSE {

# The local repo exists, commence merge to pull in most recent changes
CD $local_repo_name
Invoke-Expression $merge_cmd

}

# Database Variables
$instance_name = "localhost\sql16"
$database_name = "master"

# Retrieves SQL scripts that match desired criteria and executes them in the target database
Get-ChildItem |

Where-Object { ($_.Name -eq "sp_Blitz.sql") } |

ForEach-Object {Invoke-Sqlcmd -ServerInstance $instance_name -Database $database_name -QueryTimeout 65535 -InputFile $_.FullName}

} ELSE {

$wshell = New-Object -ComObject Wscript.Shell
$wshell.Popup("Crap, something went wrong.",0,"Error",0x1)

}

After running the script, I am now all up to date. Thanks for stopping by.

References