PowerShell SQL Deployment – Part 1: PowerShell

In his two-part series, Andrew Hinkle explains how to deploy SQL databases through Powershell scripts.

DogFoodCon 2019 is October 3rd – 4th.  Ben Miller will be presenting [DevOps] DBAs working Smarter not Harder with PowerShell and [SQL/BI] Inside the DBAtools PowerShell Module.  I interviewed Ben Miller regarding his presentation and have included a summary at the end of this article.

Azure build definitions provide the capabilities to get code from source control, compile the code, run its unit tests, and stage the code, along with many other features.  Azure release definitions take the staged code and replace tokens with environment specific values, run UI tests like Selenium, and much more.  Jonathan Danylko showed us how to apply the same concept for SQL by Deploying Databases using Azure DevOps Pipelines with DACPACs.

An alternative is to deploy your SQL files via PowerShell.  You may create as many SQL files as you want following a simple naming convention to enforce the scripts are applied in the order you want.  The files are plain text, so you may review them before deployment.

In this article I'll walk you through the steps of setting up Visual Studio Code with PowerShell and the SQL Server module.  Then you'll run a simple Invoke-Sqlcmd query against your SQL Server instance containing a database.  I'll provide a PowerShell module that wraps up the Invoke-Sqlcmd to handle an array of sql files with output messaging.  Finally, we'll create a script that wraps the call to the module with exception handling and output messaging targeting Azure.  In Part 2 I'll cover the next steps of creating a build and release definition, add release definition variables for each environment that will be supplied as parameters to the PowerShell script, and test.

Assumptions

• Your Windows Account has permissions to create stored procedures on a SQL Server database

Setup

1. Install Visual Studio Code
   1. https://code.visualstudio.com/
   2. Avoid most permission issues by running Visual Studio Code as an Administrator
2. Install Visual Studio Code > PowerShell Extension
   1. Run Visual Studio Code as an Administrator > Extensions > Search for "powershell" > Click Install
      
      
      
      
3. Install PowerShell > SQL Server module
   1. https://www.powershellgallery.com/packages/SqlServer/21.1.18068
      
      
      
      
   2. "New Features and Best Practices of SQL Server PowerShell" is a great tutorial on installing the PowerShell SQL Server module. That article goes into great detail, so the following is the short version of what I did.
      1. I did run into some issues while trying to install the module beyond what was mentioned here that you may read in my notes
   3. Copy the install module command and run it in the Visual Studio Code > PowerShell extension
   4. Install-Module -Name SqlServer -RequiredVersion 21.1.18068
      1. I was prompted to install the latest NuGet PackageProvider, which I answered yes to.

Invoke-SqlCmd

Execute a Query to verify you're setup correctly.  The primary function that I've used in this process is Invoke-Sqlcmd.  Let's try it now.

1. In Visual Studio Code > PowerShell run the following commands
   1. Replace $ServerInstance with the SQL Server instance name, which may be as simple as the computer name depending on how it was setup. When you open SQL Server Management Studio and connect to a server instance, that's the name you use.
   2. Replace $Database with the database name.
   3. Make sure you are using actual hyphen "-" and not the bigger dash "–" that MS Word and other text editors like to replace it with.
      
      Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $database -Query "SELECT GETDATE() as TimeOfQuery"
      
      

Taking it Further

With this command alone you can build a process around it that will string together any number of PowerShell commands to prep your environments, run your queries, and clean up after yourself.  The following sections are files with their contents.  I created the following folders and placed all of the SQL files (.sql) in the sql folder and the PowerShell scripts (.ps1, .psm1) in the scripts folder to test them.

1. C:\temp\scripts
2. C:\temp\sql

I chose to separate the concerns of applying the SQL script into a reusable module.  I isolated the gathering of the SQL files and Azure exception handling in the DeploySql PowerShell script.  You can choose to combine the module and script into a single script.

I like the flexibility to custom the exception handling to the deployment pipeline without affecting the core script.

00.Setup.sql

--Initial setup steps
--ex. For Feature/DEV/QA/Staging Environments
--Include a database refresh to a desired state

01.Deploy.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Drop the stored procedure if it exists.
IF EXISTS (
          SELECT  *
          FROM    sys.objects
          WHERE   object_id = OBJECT_ID(N'[dbo].[GetTimeOfQuery]')
                    AND type in (N'P', N'PC') )
          DROP PROCEDURE [dbo].[GetTimeOfQuery]
GO
CREATE PROCEDURE [dbo].[GetTimeOfQuery]
AS
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT GETDATE() AS TimeOfQuery
GO
--GRANT EXECUTE ON [dbo].[GetTimeOfQuery] TO [{DatabaseRole}]
--GO
-- Verify stored procedure was created by displaying its definition
-- sp_helptext GetTimeOfQuery

##.*.sql

Add as many SQL Statements to deploy as you need.

99.Teardown.sql

-- Teardown steps
-- Ex. Delete files necessary for the
-- deployment, but are no longer needed.

COMPANYNAME.DevOps.Sql.psm1

function Invoke-DeploySql {
    <#
    .SYNOPSIS 
        Deploy SQL files to a database.
    .DESCRIPTION 
        Deploy SQL files to a database.
        User must have permission to perform the steps within the sql file.
        Requires PowerShell module SqlServer installed on the server running the PowerShell script.
    .PARAMETER ServerInstance
        The SQL server instance where the SQL will be deployed.
    .PARAMETER Database
        The database where the SQL will be deployed.
    .PARAMETER SqlFiles
        The SQL files that will be deployed.
    .EXAMPLE
        # Adjust directories to reflect where the module and sql files are located.
        Import-Module ".\COMPANYNAME.DevOps.Sql.psm1" -Force
        $sqlFolder = "..\sql\DATABASE1\"
        $sqlFiles = Get-ChildItem $sqlFolder -Filter *.sql | Sort-Object
        Invoke-DeploySql -ServerInstance SERVERINSTANCE1 -Database DATABASE1 -SqlFiles $sqlFiles
        Import-Module ".\COMPANYNAME.DevOps.Sql.psm1" -Force
        $sqlFolder = "..\sql\DATABASE2\"
        $sqlFiles = Get-ChildItem $sqlFolder -Filter *.sql | Sort-Object
        Invoke-DeploySql -ServerInstance SERVERINSTANCE2 -Database DATABASE2 -SqlFiles $sqlFiles
    #>
    [Cmdletbinding()]
    param(
        [Parameter(Mandatory=$true)]
        [String]$ServerInstance,
        [Parameter(Mandatory=$true)]
        [String]$Database,        
        [Parameter(Mandatory=$true)]
        [Array]$SqlFiles
    )
    Write-Output "$($MyInvocation.MyCommand) - Start"
    $totalSqlFiles = ($SqlFiles | Measure-Object).Count
    Write-Output "Total sql files: $totalSqlFiles"
    $SqlFiles | Foreach-Object {
        $sqlFile = $_.FullName
        Write-Output "Processing file: $sqlFile"
        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -InputFile $sqlFile
        Write-Output "Completed file:  $sqlFile"
    }
    Write-Output "$($MyInvocation.MyCommand) - End"
}

DeploySql.ps1

<#
.SYNOPSIS 
    Deploy SQL files to a database.  Includes root level exception handling.
.DESCRIPTION 
    Deploy SQL files to a database.  Includes root level exception handling.
    User must have permission to perform the steps within the sql file.
    Requires PowerShell module SqlServer installed on the server running the PowerShell script.
    Intended as a step within an Azure DevOps release definition.
    When run within an Azure DevOps release definition, the agent must have the permissions
    and the server the agent is running on must have the PowerShell module SqlServer installed.
    If you standardize where you install your PowerShell modules following
    PowerShell module recommendations, then consider removing the parameter
    in favor of referencing the module directly.
.PARAMETER DevOpsSqlModule
    The DevOps SQL module file path.
.PARAMETER ServerInstance
    The SQL server instance where the SQL will be deployed.
.PARAMETER Database
    The database where the SQL will be deployed.
.PARAMETER SqlFolder
    The folder containing the .sql files that will be deployed.
.EXAMPLE
    .\DeploySql -DevOpsSqlModule "C:\temp\scripts\COMPANYNAME.DevOps.Sql.psm1" -ServerInstance SERVERINSTANCE1 -Database DATABASE1 -SqlFolder "C:\temp\sql\DATABASE1\"
    .\DeploySql -DevOpsSqlModule "C:\temp\scripts\ COMPANYNAME.DevOps.Sql.psm1" -ServerInstance SERVERINSTANCE2  -Database DATABASE2 -SqlFolder "C:\temp\sql\DATABASE2\"
#>
[Cmdletbinding()]
param(
    [Parameter(Mandatory=$true)]
    [String]$DevOpsSqlModule,
    [Parameter(Mandatory=$true)]
    [String]$ServerInstance,
    [Parameter(Mandatory=$true)]
    [String]$Database,    
    [Parameter(Mandatory=$true)]
    [String]$SqlFolder
)
$scriptFile = Get-Item $PSCommandPath;
Write-Output "$($scriptFile.Name) - Start"
Try {
    Write-Output "Importing DevOps SQL module"
    Import-Module $DevOpsSqlModule -Force
    Write-Output "Applying scripts to server instance '$ServerInstance' on database '$Database'"
    $sqlFiles = Get-ChildItem $SqlFolder -Filter *.sql | Sort-Object
    $totalSqlFilesFound = ($sqlFiles | Measure-Object).Count
    If ($totalSqlFilesFound -eq 0) {
        Write-Output "##vso[task.LogIssue type=warning;]Warning: no sql files found."
    }
    else {
        Invoke-DeploySql -ServerInstance $ServerInstance -Database $Database -SqlFiles $sqlFiles
    }
}
Catch {
    # Azure DevOps errors
    Write-Output "##vso[task.LogIssue type=error;] $($scriptFile.Name)"
    Write-Output "##vso[task.LogIssue type=error;] Script Path: $($scriptFile.FullName)"
    Write-Output "##vso[task.LogIssue type=error;] $_"
    Write-Output "##vso[task.LogIssue type=error;] $($_.ScriptStackTrace)"
    Exit 1
}
Write-Output "$($scriptFile.Name) - End"

Run the Powershell Script

In Visual Studio Code > PowerShell run the following command.  Remember to adjust the ServerInstance, Database, and SqlFolder appropriately.

.\DeploySql -DevOpsSqlModule "C:\temp\scripts\COMPANYNAME.DevOps.Sql.psm1" -ServerInstance SERVERINSTANCE1 -Database DATABASE1 -SqlFolder "C:\temp\sql\DATABASE1\"

Next Steps

Given these example PowerShell scripts research the steps and learn what each command does.  Adjust them to meet your needs and get excited that you deployed SQL from PowerShell!

In Part 2, I'll cover the next steps of creating a build and release definition, add release definition variables for each environment that will be supplied as parameters to the PowerShell script, and test.

References

• New Features and Best Practices of SQL Server PowerShell
• PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server
• Navigate SQL Server PowerShell Paths
• PowerShell Documentation and Comments

DogFoodCon 2019

DogFoodCon 2019 is October 3rd – 4th.  Ben Miller will be presenting [DevOps] DBAs working Smarter not Harder with PowerShell and [SQL/BI] Inside the DBAtools PowerShell Module.  Ben Miller walked me through the entire process he follows to deploy SQL to the hundreds of databases he services.  I was thoroughly impressed with the attention to detail to ensure the stability, accuracy, and reliability he invested into his deployment process.  I highly recommend attending his session if you have any interest in deploying SQL in an automated manner.  Whether you prefer to deploy DACPAC or SQL files you'll learn from the insights he'll provide in this session.

From Ben Miller's site https://dbaduck.com/about-me/:

Ben has been a member of the SQL Server Community for over 17 years now. He is currently working at MaritzCX revolutionizing the SQL Server environment and loving it. He also consults as well. He is a SQL Server MVP and Microsoft Certified Master (MCM) and has spent time in the field using SQL Server since 1997. He has worked at various companies throughout the US as well as at Microsoft for 7 years. He is passionate about SQL Server and automation and integration and uses SMO and PowerShell regularly.

By attending his session you'll learn the following:

1. Pros/Cons of SQLCompare and Database Projects
2. Shred the DACPAC into deployment files by categories in a specific order enforced by Microsoft standards
   1. This section is the primary focus
   2. Why/How to shred the DACPAC
   3. Standards to enforce a reliable/stable deployment experience
3. PowerShell SQL Deployments

What is covered here, plus additional details Ben Miller

Conclusion

I've demonstrated how to setup Visual Studio Code with the PowerShell extension and Sql Server module and verified the setup by executing a simple query.  You have learned how to deploy multiple SQL files with exception handling and Azure output messaging.  Next steps include Part 2 where you'll learn how to setup an Azure Build and Release definitions to deploy SQL through your DevOps pipeline.  Next steps also include attending Ben Miller's sessions at DogFoodCon 2019.

Were you able to run the Invoke-Sqlcmd statement?  Were you able to deploy any SQL scripts to your database?  Do you deploy your SQL scripts via DACPAC or SQL files?  Have you automated the process or is it fairly manual?  Do you deploy your SQL in a different way? Share in the comments below.