Using PowerShell To Deploy Scripts From GitHub to Multiple SQL Servers

nature-wildlife-zoo-turtle-reptile-fauna-shell-wild-life-animals-tortoise-vertebrate-turtles-box-turtle-galapagos-el-salvador-common-snapping-turtle-emydidae-african-turtle-1280797 (1).jpg

Now I’m all for making my life easier and one thing that makes life easier for me is having a single process that I can run to install\update all my favourite utility stored procedures quickly and on all my SQL Servers.

I’ve done this a few different ways in the past but this time I got thinking about GitHub.  We keep all our utility procs in a GitHub repository so wouldn’t it be cool if we could just access those scripts directly from GitHub and run them on multiple SQL Servers?  This could be especially useful if I wanted to install or update these procs on an offsite customer’s Server where I might not easily have access to all my scripts.

I’m the first person to admin that I’m a bit of a noob when it comes to PowerShell but it seemed the best place to go to to get this done.  The following scripts go out to your GitHub repository, pullback the scripts and run them on your specified SQL instances.  I’ve purposely avoided using POSH-Git in these scripts as I wanted them to be something that could be run without the need to install Git.

 Run A Single Specific Script On SQL1 and SQL2
$ScriptURL should be set to the URL of the raw script


Add-PSSnapin SqlServerCmdletSnapin120
Add-PSSnapin SqlServerProviderSnapin120

#set variables
$ScriptURL="https://raw.githubusercontent.com/SQLUndercover/UndercoverToolbox/master/sp_restorescript.sql"
$SQLInstances = "SQL1,SQL2"
$DefaultDatabase = "master"

ForEach ($SQLInstance in $SQLInstances.Split(","))
{
 echo "Running Scripts on " $SQLInstance
$ScriptFromGit = Invoke-WebRequest $ScriptURL

Invoke-Sqlcmd -Query $ScriptFromGit.Content -ServerInstance $SQLInstance -database $DefaultDatabase
}

Run All Scripts From Specified Repository
$GitRepository should be set to the URL or the repository or folder within the repository that holds your scripts

Add-PSSnapin SqlServerCmdletSnapin120
Add-PSSnapin SqlServerProviderSnapin120

#set variables
$GitRepository = "https://github.com/SQLUndercover/UndercoverToolbox"
$SQLInstances = "SQL1,SQL2"
$DefaultDatabase = "master"

ForEach ($SQLInstance in $SQLInstances.Split(","))
{
echo "Running Scripts on " $SQLInstance

#get all sql files from GIT repository
$files = (Invoke-WebRequest $GitRepository).Links | ? href -like *.sql

#loop through all files and run them into SQL
foreach ($file in $files.href)
{
$ScriptURL = ("https://raw.githubusercontent.com" + $file.Replace("/blob/","/"))
echo "Running " $ScriptURL
$ScriptFromGit = Invoke-WebRequest $ScriptURL

Invoke-Sqlcmd -Query $ScriptFromGit.Content -ServerInstance $SQLInstance -database $DefaultDatabase
}
}

The above scrip will run all the scripts in the repository and in no particular order.  What do we do if we only want to run in specific scripts or to run them in a certain order.  In that case I decided to add a manifest file.  This is just a text file that contains the scripts that you want to run and the order that they should be run.  It a simple text file which lists the name of each script on a separate line, in the order that you want them run.

Run Scrips As Specified in the Manifest
$GitManifestURL is the URL of the raw manifest file

Add-PSSnapin SqlServerCmdletSnapin120
Add-PSSnapin SqlServerProviderSnapin120

#set variables
$GitRepository = "https://github.com/SQLUndercover/UndercoverToolbox"
$GitManifestURL = "https://raw.githubusercontent.com/SQLUndercover/UndercoverToolbox/master/Manifest.txt"
$SQLInstances = "SQL1,SQL2"
$DefaultDatabase = "master"

ForEach ($SQLInstance in $SQLInstances.Split(","))
{
    echo "Running Scripts on " $SQLInstance

    #get  manifest
    $Manifest = (Invoke-WebRequest $GitManifestURL).Content

    #loop through prereqs in order of the manifest and run them into SQL
    #foreach ($PreRec In $Manifest)
    ForEach ($Script in $Manifest.Split("`r`n"))
    {
        $ScriptURL = ($GitRepository + "/" + $Script)
        echo "Running " $ScriptURL
        $ScriptFromGit = Invoke-WebRequest $ScriptURL
        Invoke-Sqlcmd -Query $ScriptFromGit.Content -ServerInstance $SQLInstance -database $DefaultDatabase

    }
}

Thanks for reading and I love to hear from you if you’ve got a different way of doing this 🙂

2 thoughts on “Using PowerShell To Deploy Scripts From GitHub to Multiple SQL Servers

Add yours

  1. Looks like this would be useful for an “All Databases on a given Azure ‘server’ ” scenario. Server stays the same, but the database changes. We’ve had a need to run scripts for each database on an instance and this would help. Have to open/close a connection for each one due to the way Azure SQL works.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: