master | development |
---|---|
DBOps is a Powershell module that provides Continuous Integration/Continuous Deployment capabilities for SQL database deployments. In addition to easy-to-use deployment functions, it provides tracking functionality, ensuring that each script is deployed only once and in due order. It will also grant you with ability to organize scripts into builds and deploy them in a repeatable manner on top of any previously deployed version.
The deployment functionality of the module is provided by DbUp .Net library, which has proven its flexibility and reliability during deployments.
Currently supported RDBMS:
In addition it also supports Azure Synapse Analytics SQL Pools (both dedicated and serverless). Be aware that you cannot use the schemaversion table with the serverless SQL Pool - it needs to be explicitly disabled by setting SchemaVersionTable to $null
. All scripts deployed in such scenario should be idempotent and re-runnable.
The most notable features of the module:
#{customVarName}
tokensgit clone https://github.com/dataplat/dbops.git dbops
Import-Module .\dbops
Make sure to have the following modules installed as well:
Install-Module dbops
Perform plain-text script deployments using a single command:
Example code:
# Ad-hoc deployment of the scripts from a folder myscripts
Install-DBOScript -ScriptPath C:\temp\myscripts -SqlInstance server1 -Database MyDB
# Execute a list of files as an Ad-hoc query
Get-ChildItem C:\temp\myscripts | Invoke-DBOQuery -SqlInstance server1 -Database MyDB
Each package consists of multiple builds and can be easily deployed to the database, ensuring that each build is deployed in proper order and only once.
Example code:
# Deployment using packaging system
New-DBOPackage Deploy.zip -ScriptPath C:\temp\myscripts | Install-DBOPackage -SqlInstance server1 -Database MyDB
# Create new deployment package with predefined configuration and deploy it replacing #{dbName} tokens with corresponding values
New-DBOPackage -Path MyPackage.zip -ScriptPath .\Scripts -Configuration @{ Database = '#{dbName}'; ConnectionTimeout = 5 }
Install-DBOPackage MyPackage.zip -Variables @{ dbName = 'myDB' }
# Adding builds to the package
Add-DBOBuild Deploy.zip -ScriptPath .\myscripts -Type Unique -Build 2.0
Get-ChildItem .\myscripts | Add-DBOBuild Deploy.zip -Type New,Modified -Build 3.0
# Install package using internal script Deploy.ps1 - to use when module is not installed locally
Expand-Archive Deploy.zip '.\MyTempFolder'
.\MyTempFolder\Deploy.ps1 -SqlInstance server1 -Database MyDB
There are multiple configuration options available, including:
Example code:
# Setting deployment options within the package to be able to deploy it without specifying options
Update-DBOConfig Deploy.zip -Configuration @{ DeploymentMethod = 'SingleTransaction'; SqlInstance = 'localhost'; Database = 'MyDb2' }
Install-DBOPackage Deploy.zip
# Generating config files and using it later as a deployment template
New-DBOConfig -Configuration @{ DeploymentMethod = 'SingleTransaction'; SqlInstance = 'devInstance'; Database = 'MyDB' } | Export-DBOConfig '.\dev.json'
Get-DBOConfig -Path '.\dev.json' -Configuration @{ SqlInstance = 'prodInstance' } | Export-DBOConfig '.\prod.json'
Install-DBOPackage Deploy.zip -ConfigurationFile .\dev.json
# Invoke package deployment using custom connection string
Install-DBOPackage -Path Deploy.zip -ConnectionString 'Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;'
# Invoke package deployment to an Oracle database OracleDB
Install-DBOPackage -Path Deploy.zip -Server OracleDB -ConnectionType Oracle
# Get a list of all the default settings
Get-DBODefaultSetting
# Change the default SchemaVersionTable setting to null, disabling the deployment journalling by default
Set-DBODefaultSetting -Name SchemaVersionTable -Value $null
# Reset SchemaVersionTable setting back to its default value
Reset-DBODefaultSetting -Name SchemaVersionTable
dbops CI/CD flow assumes that each package version is built only once and deployed onto every single environment. The successfull builds should make their way as artifacts into the artifact storage, from which they would be pulled again to add new builds into the package during the next iteration.
CI/CD capabilities of the module enable user to integrate SQL scripts into a package file using a single command and to store packages in a versioned package repository.
Example code:
# Invoke CI/CD build of the package MyPackage.zip using scripts from the source folder .\Scripts
# Each execution of the command will only pick up new files from the ScriptPath folder
Invoke-DBOPackageCI -Path MyPackage.zip -ScriptPath .\Scripts -Version 1.0
# Store the package in a DBOps package repository in a folder \\data\repo
Publish-DBOPackageArtifact -Path myPackage.zip -Repository \\data\repo
# Retrieve the latest package version from the repository and install it
Get-DBOPackageArtifact -Path myPackage.zip -Repository \\data\repo | Install-DBOPackage -Server MyDBServer -Database MyDB
Submitting issues - GitHub issues
SQLCommunity Slack: https://sqlcommunity.slack.com #dbops or @nvarscar