Category: SQL

Many SQL Server DBAs know that there is a very convenient way of delivering a full copy of the database structure to a different SQL Server: it’s called dacpac, or a Data-tier Application package. You can extract the database any time using SQL Server Management Studio context menu: Tasks -> Extract Data-tier Application, which would guide you through the extraction options and output a compressed package file that would contain all necessary schema definitions, as well as table data. This package can be further on deployed on a completely different server using a Deploy Data-tier Application context menu item.

What people do not widely know, however, is how much fine-tuning a simple deployment might require, and how difficult it is sometimes to force the deployment operation to behave in a certain way. Good thing, is that there is now a convenient way of automating this process using dacpac-related functions from the dbatools Powershell module.

This article would explain how to:

• Extract dacpac from a database
• Choose whether you want to include data or not
• Deploy the dacpac package on any version of SQL Server higher than 2008 SP2
• Exclude object types from the deployment process
• Generate deployment scripts
• Create a Jenkins job that will take care of this whole process for you

Extracting dacpac package

Let’s use this simple script to extract dacpac from an existing database:

Export properties here are defined to control the extract process:

• Ignore database permissions, as there is rarely a need to add permissions into the mix
• Define an option to include data into the package

Export-DbaDacpac function will take care of the extraction process and will return all details about the extraction, including the .Path property that would direct us to the extracted file:

You can check all other available properties (which are basically command line parameters) in a corresponding article: https://msdn.microsoft.com/en-us/library/hh550080.aspx

Deploying dacpac package

There is one important note about dacpac deployment: it requires a so called Publish Profile for the deployment. Publish profile is an xml file that contains deployment parameters that you want to enable during the deployment. You can create such profile by using one of the following options:

• Run New-DbaPublishProfile, specifying the connection parameters. The resulting file will enable you to run the deployment, but it is highly recommended that you modify it according to your needs
• From Visual Studio SSDT project, select Project -> Publish. It would open a dialog, that allows you to load, save and modify the Publish profile.
• Manually, using documentation from Publish parameters section of the same article

The parameters that we’re going to use during deployment are as follows:

• AllowIncompatiblePlatform – would enable me to deploy to an earlier version of SQL Server.
• CreateNewDatabase – will re-create the database every time
• ExcludeObjectTypes – semicolon-separated list of objects that will be excluded from the deployment.
• ExcludeLogins and ExcludeUsers – somehow, these two ended up being independent parameters, not included in the previously mentioned ExcludeObjectTypes. Excludes logins and/or users from the deployment.
• IgnorePermissions – ignores object permissions during the deployment.
• DropObjectsNotInSource – boolean value, which is True by default, that controls whether existing objects in the database are going to be dropped if they are not present in the package. This is a very important parameter if you are planning to deploy dacpac to a non-empty database.
• IgnoreRoleMembership – ignores role membership

This is an example of the Publish profile file that we’re going to use in this exercise:

Once we have a Publish profile ready we can start the deployment by using Publish-DbaDacpac function, specifying:

• SqlInstance – target server
• Database – target database
• Path – path to the dacpac package
• PublishXml – path to the publish profile

Other parameters of Publish-DbaDacpac that you might find useful:

• GenerateDeploymentScript – will generate a deployment script prior to deployment
• ScriptOnly – will not perform the deployment, generating a deployment script instead

Full script

This script below would extract the dacpac from an existing database and deploy it to a different server using the publish.xml file we created above. Feel free to modify it to your own liking!

Creating a Jenkins job

In order to create an interface and a scheduler at the same time, I would definitely recommend to make this scenario available on your Jenkins instance, same way we did it before in Refresh database & keep permissions – SQL Server Jenkins labs (Part 2). The parameters are going to be almost the same, so feel free to use examples from the Jenkins labs to create the job that will use instead a dacpac deployment script.

Conclusion

Dacpac packages are a convenient way of copying a full database when other methods are unavailable for whatever reason. It has some learning curve associated with it, but it can be a great help when used in an automated manner.

It is also a perfect workaround for database copying, when a source server has a higher SQL Server version than the target server: this will work even between 2017 and 2008, if all the objects are compatible with the target database version.

It also work pretty well as an alternative for a snapshot replication, being much less restrictive in its approach.

UPDATE: Updated the CreateNewDatabase parameter with a new description, apparently it recreates the database every time instead of just creating a new database if needed.

Welcome back to the SQL Server Jenkins labs! In the first part of the labs we created a Jenkins job that restored a database based on historical data. This time, let’s try something very similar, but add some flavor to it.

We’re going to setup a job that copies a single database in its current state from one server to another. It is very similar to the previous lab exercise, except, this one will take care of much more things at once. Let’s add into the mix most of the activities that accompany the database restore, such as:

• Changing database owner after restore is completed;
• Cleaning up existing permissions on the restored database;
• Restoring previous permissions, in case the database is being replaced with a fresh copy.

On top of that, let’s make some of them optional, increasing the flexibility of the solution.

Writing the code

Once again, there is no reason not to use dbatools commands for this purpose, similarly how most of us wouldn’t want to reinvent the wheel. The scenario is going to be quite simple:

1. Define parameters – connections, database names, paths, etc.
2. Backup the source database to a network share with COPY ONLY
3. Export permissions from the target database if needed
4. Restore the target database
5. Change database owner
6. Remove all users from the database if needed
7. Re-apply old permissions if needed

The restore process, when it comes to replacing an existing database, is a very delicate operation, so let’s make sure that the code would stop whenever an error is occurred. This will be achieved by using -EnableException switch of the dbatools functions, that will throw an error instead of a simple warning, which is a default behavior. Let’s also define $ErrorActionPreference = 'Stop' to ensure that the script is not executed any further once the error has occurred. Lastly, it would be a good idea to save existing permissions to the disk drive in case a manual intervention is required.

Most of the output will be hidden in the Verbose stream, which allows us to control the amount of output we want to see on our end. Just add a -Verbose switch, if you need to see more!

Here is the script we are going to use this time. It has slightly more action inside, but it still consists of the same building blocks, most of which are regular dbatools commands. Let’s name it Copy-Database.ps1:

[Cmdletbinding()]
Param (
    [Parameter(Mandatory)]
    [string]$SourceServer,
    [Parameter(Mandatory)]
    [string]$SourceDatabase,
    [string]$TargetServer,
    [Parameter(Mandatory)]
    [string]$TargetDatabase,
    [Parameter(Mandatory)]
    [string]$BackupFilePath,
    [string]$DBOwner,
    [switch]$KeepPermissions,
    [switch]$DropUsers
)

#Stop on any error by default
$previousErrorActionPreference = $ErrorActionPreference
$ErrorActionPreference = 'Stop'

#Temporary permissions will be stored here
$permissionsFile = ".\Permissions-$TargetDatabase.sql"

#If target server is not specified, it's assumed that it's going to be the same server
if (!$TargetServer) { $TargetServer = $SourceServer }

if (Test-Path $BackupFilePath) {
    #Removing old temporary backup if it still exists for some reason
    Write-Verbose "Removing old backup file $BackupFilePath"
    Remove-Item $BackupFilePath
}
#Run copy-only backup
Write-Verbose "Initiating database backup`: $SourceServer.$SourceDatabase to $BackupFilePath"
$backup = Backup-DbaDatabase -SqlInstance $SourceServer -Database $SourceDatabase -BackupFileName $BackupFilePath -CopyOnly -CompressBackup -Checksum -EnableException

if (!$backup.BackupComplete) {
    throw "Backup to $BackupFilePath was not completed successfully on $SourceServer.$SourceDatabase"
}

#Record and store permissions
if ($KeepPermissions -or $DropUsers) {
    $permissions = Export-DbaUser -SqlInstance $TargetServer -Database $TargetDatabase
    Write-Verbose "Exported permissions from $TargetServer.$TargetDatabase`: $permissions"
    Write-Verbose "Storing permissions of $TargetServer.$TargetDatabase in a file $permissionsFile"
    $permissions | Out-File -FilePath $permissionsFile
}

#Restore the database on top of existing DB
Write-Verbose "Initiating database restore`: $BackupFilePath to $TargetServer.$TargetDatabase"
$backup | Restore-DbaDatabase -SqlInstance $TargetServer -DatabaseName $TargetDatabase -WithReplace -ReplaceDbNameInFile -EnableException

#Update database owner, stop throwing errors at this point, as further actions are not critical
$ErrorActionPreference = $previousErrorActionPreference

if ($DBOwner) {
    Write-Verbose "Updating database owner to $DBOwner"
    Set-DbaDatabaseOwner -SqlInstance $TargetServer -Database $TargetDatabase -TargetLogin $DBOwner
}

#Drop users if requested
if ($DropUsers) {
    $users = Get-DbaDatabaseUser -SqlInstance $TargetServer -Database $TargetDatabase -ExcludeSystemUser
    foreach ($user in $users) {
        Write-Verbose "Dropping user $($user.Name) from $TargetServer.$TargetDatabase"
        try {
            $user.Drop()
        }
        catch {
            # No need to throw, maybe a user owns a schema of its own
            Write-Warning -Message $_
        }
    }
}

#Restore permissions
if ($KeepPermissions) {
    Write-Verbose "Restoring permissions of $TargetServer.$TargetDatabase from a file $permissionsFile"
    Invoke-DbaSqlQuery -SqlInstance $TargetServer -Database $TargetDatabase -Query $permissions
}

#Remove backup file
if (Test-Path $BackupFilePath) {
    Write-Verbose "Removing backup file $BackupFilePath"
    Remove-Item $BackupFilePath
}

#Remove permissions file
if (Test-Path $permissionsFile) {
    Write-Verbose "Removing permissions file $permissionsFile"
    Remove-Item $permissionsFile
}

Creating a new Jenkins job

Configuration check

Let’s re-iterate on this and ensure that our Jenkins instance has all necessary tools to implement the job we have in mind.

Plugins list

The following Jenkins plugins are going to be used in this job:

• • Parameterized Builds – to provide parameters starting the job

• • Powershell Plugin – to run Powershell commands

Agent setup

Ensure that the Jenkins executor(s), which you intend to use, runs under an account that was granted with sysadmin privileges on both source and target SQL Servers. Make sure to confine the job to that particular executor(s) using labels.

Also let’s ensure that executor node(s) has the latest version of dbatools installed:

Install-Module dbatools

Finally, let’s copy our Copy-Database.ps1 script to the local folder using the same exact path across all the Jenkins nodes, and ensure that job executor has r/w access to that folder. If you want to use a remote folder (file share), make sure that your Execution Policy is set to RemoteSigned on each node: Set-ExecutionPolicy -ExecutionPolicy RemoteSigned.

For advanced Jenkins users I would recommend using a Source Control Management repository to store your code and retrieve it using the SCM section in the job definition.

Creating the job

Finally, it’s time to create the job! Start with clicking ‘New’ item on the left-hand side of the Jenkins window:

Input a new project name and make sure to choose a Freestyle project, which is a most generic Jenkins job type:

In the job configuration window, set the project to be parameterized (click the corresponding checkbox). Add 4xString parameters (server and database names for both source and destination), and 2xBoolean parameters (those will represent our optional switches):

Parameter name of each of the added parameters will become an environment variable, so make sure to not to use special characters in the parameter name.

Provide a descriptive definition for the parameters:

• String parameter 1
  • Name: SourceServer
  • Default value:
  • Description: Full server name of the source instance
  • Trim the string: Yes
• String parameter 2
  • Name: SourceDatabase
  • Default value:
  • Description: Name of the source database to copy
  • Trim the string: Yes
• String parameter 3
  • Name: TargetServer
  • Default value:
  • Description: Full server name of the target instance
  • Trim the string: Yes
• String parameter 4
  • Name: TargetDatabase
  • Default value:
  • Description: Name of the target database. Will be replaced, if exists.
  • Trim the string: Yes
• Boolean parameter 1
  • Name: KeepPermissions
  • Default value: Unchecked
  • Description: Check if you want to re-apply all the previously existed permissions on top of the restored database
• Boolean parameter 2
  • Name: DropUsers
  • Default value: Unchecked
  • Description: Check if you want to drop all the users from the target database upon restore.

If you want this project to run only on specific agent nodes, click Restrict where this project can be run and provide space-separated list of labels in the field:

Now we have our job parameterized and running only on specific nodes, the only thing left is to pass those parameters to the Powershell script. In the Build section click Add build step and select Windows PowerShell:

In the Command section let’s put a call to our script file, that should be already accessible for the agent nodes, and pass all the parameters from your job:

& D:\Scripts\Copy-Database.ps1 -SourceServer $env:SourceServer `
  -TargetServer $env:TargetServer `
  -SourceDatabase $env:SourceDatabase `
  -TargetDatabase $env:TargetDatabase `
  -KeepPermissions:([bool]::Parse($env:KeepPermissions)) `
  -DropUsers:([bool]::Parse($env:DropUsers)) `
  -BackupFilePath "\\Some\Path\$($env:SourceDatabase).bak" `
  -DBOwner sa `
  -Verbose

Several things to note about this script:

• $env:ServerName is a call for an environment variable, that was defined by the job parameter.
• [bool]::Parse($env:KeepPermissions) is used to convert the job’s parameter to [bool]. All job parameter are strings in essence and boolean parameters will have one of the following values: ‘true‘ or ‘false‘. We’re simply converting them to [bool]  using a public method Parse of the [bool] class.
• -DropUsers:([bool]::Parse($env:DropUsers)) is used to pass a boolean value to a switch parameter, which requires a colon notation.

Save the job by pressing the Save button on the bottom of the screen.

 Running the job

Let’s start the job now! Click Build with Parameters from the job overview screen:

Input all the parameters and start the job:

In the Build History section of the page, you will see the progress of the job. By clicking on it you would be able to see the details, such as Console Output:

Conclusion

There are multiple ways of introducing automation to your environment, and Powershell is definitely one of them, yet, sometimes it’s hard for people to embrace automation without a simple UI that would help them to do the first step.

Jenkins is one of such tools that enables anyone to use automated solutions with little to no knowledge of the code that is executed behind the curtain. It can be just a frontend for your Powershell scripts, or it can become an ultimate tool in your hands that controls multiple aspects of your day-to-day tasks, and is integrated with your whole enterprise.

Cheers! And stay tuned – there is more to come!

Hello there, and welcome to SQL Server Jenkins labs!

Some time ago I talked about using Jenkins as a robust scheduling tool, and now it is absolute time to follow up on that, and demonstrate some of the solutions that can be achieved by using a combination of Powershell, dbatools and Jenkins.

The first example to go down that pipe is going to be a simple backup restore job. The job is going to get backup history from the source server, build a proper restore sequence and perform the restore on a server of your choosing. The restore step would also:

• Change file paths of the database files to a default data/log directory
• Rename database files to match the new database name
• Optionally, perform a point-in-time recovery

Preparations

First, let’s ensure that our Jenkins instance has all necessary tools to implement this job.

Plugins list

The following Jenkins plugins are going to be used in this job:

• • Parameterized Builds – to provide parameters starting the job

• • Powershell Plugin – to run Powershell commands

You can setup plugin by clicking Manage Jenkins on the left-hand side menu and choosing Manage Plugins in the list. On the PluginManager page, switch to the Available tab and use Filter textbox to search for the plugins you want to install.

After you selected the plugins, click Install without restart. Most of the plugins do not require restart, however, some of them will not work properly until the whole Jenkins instance is restarted.

Agent setup

Ensure that the Jenkins executor(s), which you intend to use, runs under an account that was granted with sysadmin privileges on both source and target SQL Servers. Depending on your agent type, it can be configured in Manage Jenkins -> Manage Nodes (Windows service agent) or in the Services console on your server (Java agent).

If you have more than one agent, make sure that all of the agents are properly configured, otherwise set up agent Labels that would allow you to confine the job to that particular executor(s).

Also let’s ensure that executor node(s) has the latest version of dbatools installed:

Install-Module dbatools

Creating a new Jenkins job

Start with clicking ‘New’ item on the left-hand side of the Jenkins window:

Input a new project name and make sure to choose a Freestyle project, which is a most generic Jenkins job type:

In the job configuration window, set the project to be parameterized (click the corresponding checkbox). Add 5 String parameters by clicking the Add Parameter button; these parameters would represent server and database names for both source and destination databases, as well as a restore time parameter for point-in-time recovery:

Parameter name of each of the added parameters will become an environment variable, so make sure to not to use special characters in the parameter name.

Provide a descriptive definition for the parameters:

• String parameter 1
  • Name: SourceServer
  • Default value:
  • Description: Full server name of the source instance
  • Trim the string: Yes
• String parameter 2
  • Name: SourceDatabase
  • Default value:
  • Description: Name of the source database to copy
  • Trim the string: Yes
• String parameter 3
  • Name: TargetServer
  • Default value:
  • Description: Full server name of the target instance
  • Trim the string: Yes
• String parameter 4
  • Name: TargetDatabase
  • Default value:
  • Description: Name of the target database. Will be replaced, if exists.
  • Trim the string: Yes
• String parameter 5
  • Name: ToDate
  • Default value:
  • Description: Specify if you want the database to be restored to a certain time, or leave blank to retrieve the latest backup
  • Trim the string: Yes

If you want this project to run only on specific agent nodes, click Restrict where this project can be run and provide space-separated list of labels in the field:

Now we have our job parameterized and running only on specific nodes, let’s add a Powershell build step, that would represent the actual restore process. In the Build section of the job creation page click Add build step and select Windows PowerShell:

In the Command textbox of the new Windows Powershell item insert the following code:

$p = @{
    SqlInstance = $env:TargetServer
    Database = $env:TargetDatabase
}
if ($env:ToDate) {
    $p += @{ RestoreTime = $env:ToDate}
}

Get-DbaBackupHistory -SqlInstance $env:SourceServer -Database $env:SourceDatabase | Restore-DbaDatabase @p -WithReplace -ReplaceDbNameInFile -TrustDbBackupHistory

This code will call two procedures from the dbatools module:

• Get-DbaBackupHistory – retrieve backup history from the source server. Parameters:
  • SqlInstance – source SQL Server name
  • Database – source database name, which would be used to search for backup records
• Restore-DbaDatabase – out of all the backup history records, restore the most relevant files, starting with full and followed by diff and log backups. Parameters:
  • SqlInstance – target SQL Server name
  • Database – desired database name on the target server
  • RestoreTime – will restore the database to the specified date/time. This parameter is added dynamically, based on whether the ToDate job parameter was specified.
  • WithReplace – replace the target database if it exists
  • ReplaceDbNameInFile – replace the part of the filename that contains the source database name with a new name
  • TrustDbBackupHistory – skip reading backup headers from the files, relying instead on the information from the source server

The parameters of the job are persisted through the execution as environment variables, which is why in order to use them we’ll have to use $env:VariableName notation.

Save the job by pressing the Save button on the bottom of the screen.

Running the job

Let’s start the job now! Click Build with Parameters from the job overview screen:

Input all the parameters and start the job:

In the Build History section of the page, you will see the progress of the job. By clicking on it you would be able to see the details, such as Console Output:

Conclusion

In this example we used very few moving parts, which, however, enabled us to create a simple and effective tool that, from now on, is going to save us hours of time when it comes to restoring databases.

Stay tuned for the future updates!