This will focus on the DBATools utility that was originally built for SQL Migration by Chrissy LeMaire (t) and has evolved into an awesome tool that will help your average DBA automate tasks and be able to get a glimpse in to all of their SQL Servers without having to learn the guts of PowerShell. It is all presented to you with great help and community support.
Today I have a project that is migrating SQL Server 2012 to SQL Server 2016. This process will be performed for 12 development environments before finally happening in production. The DBATools utility is perfect for this effort. I can create the process with the appropriate functions and then we have a repeatable process I can use to make this happen for each of my servers in this project.
Pre-Migration Steps:
There are a few features we use in our environment that the DBATools utility does not handle, so I am performing these manually until I can learn enough PowerShell to script these items on my own.
Service Start Mode Settings
Note: Originally we did not deploy all services when servers were built. However, as things evolved we decided to install all services and disable the ones not in use. This allows us to enable and configure them if the request came in without having to install and reboot.
Current Server Services Startup
New Server Services Startup
Startup Parameters
Current Server Startup Parameters
New Server Startup Parameters
Custom SQL Port
Current Server TCP SQL Port
New Server TCP SQL Port
SSL Certificate
Current Server SSL Certificate
New Server SSL Certificate
TDE Master Key
On the new server I run this command to create a Master Key for use with TDE.
|
1 2 3 4 |
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NotMyRealPassword'; GO |
Encryption Certificate
From the old server I run the BACKUP Certificate then copy the files created to the new server and CREATE Certificate from files that were backed up from old server.
|
1 2 3 4 5 6 7 |
CREATE CERTIFICATE ELV_Certificate FROM FILE = 'D:\DBA\PRSDBS01__Certificate_NLS.cer' WITH PRIVATE KEY ( FILE = 'D:\DBA\PRSDBS01__Certificate_NLS.pvk' , DECRYPTION BY PASSWORD = 'NotMyRealPassword' ); |
Configure SSIS Catalog on New Server
We use SSIS Catalog and the best migration method I have found is to perform the generic configuration on the new server then migrate the database with the DBATools migration.
T-SQL
|
1 2 3 4 5 6 7 |
EXEC sp_configure 'Show Advanced Options', 1 EXEC sp_configure 'CLR enabled', 1 RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE GO |
PowerShell
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
# Load the IntegrationServices Assembly [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") # Store the IntegrationServices Assembly namespace to avoid typing it every time $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" Write-Host "Connecting to server ..." # Create a connection to the server $sqlConnectionString = "Data Source=PRSDBS11;Initial Catalog=master;Integrated Security=SSPI;" $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString # Create the Integration Services object $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection # Provision a new SSIS Catalog $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "NotMyPassword") $catalog.Create() $sqlConnection.Close(); $command = New-Object System.Data.SqlClient.SqlCommand("EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=30", $sqlConnection); $sqlConnection.Open(); if ($command.ExecuteNonQuery() -ne -1) { echo "Failed"; } $sqlConnection.Close(); $command = New-Object System.Data.SqlClient.SqlCommand("EXEC msdb.dbo.sp_update_job @job_name=N'SSIS Server Maintenance Job',@owner_login_name=N'sa'", $sqlConnection); $sqlConnection.Open(); if ($command.ExecuteNonQuery() -ne -1) { echo "Failed"; } $sqlConnection.Close(); $command = New-Object System.Data.SqlClient.SqlCommand("USE SSISDB EXEC dbo.sp_changedbowner @loginame = N'sa'", $sqlConnection); $sqlConnection.Open(); if ($command.ExecuteNonQuery() -ne -1) { echo "Failed"; } $sqlConnection.Close(); |
Copy Files on File System
This server hosts a 3rd Party database that uses a couple of files stored on the file system. I have copied these files over manually to the proper location.
Migration Steps:
Now on to the fun stuff; Let’s fire up DBATools and get a major SQL migration going.
New Server SQL Display before Migration
New Server SQL Agent Display before Migration
First thing we do is load the DBATools module.
|
1 |
Import-Module C:\GitHub\dbatools\dbatools.psd1 -force |
Then we are going execute the Start-SqlMigration function with all of our parameters:
|
1 |
Start-SqlMigration -Source 'PRSDBS01' -Destination 'PRSDBS11' -NetworkShare '\\PRSDBS11\s$\Backups' -NoRecovery -BackupRestore -DisableJobsOnDestination -NoSaRename -WithReplace |
So here is the one command that will save you time and frustration. Here is what the Start-SqlMigration is doing.
- -Source = Your current server
- -Destination = Your new server
- -NetworkShare = The shared network path that both servers can access
- -NoRecovery = Will leave the migrated databases in NO RECOVERY state
- -BackupRestore = This will take a full backup of the databases to migrate and then restore on new server
- -DisableJobsOnDestination = This will migrate SQL Agent Jobs and leave them in a disabled state
- -NoSaRename = Will not change SA account on new server
- -WithReplace = This will overwrite the database if exists. I did this because I had to pre-build SSISDB
New Server after Migration
New Server SQL Agent after Migration
- All SP_Configure settings
- Any Custom Error Messages
- Any SQL Credentials
- All Linked Servers
- Database Mail along with Configuration and Profiles and Accounts
- All user objects in System Databases
- All Backup Devices
- Any System Triggers
- All User Databases
- All Logins
- Any Data collectors
- Any Security Audits
- All Endpoints, Policy Management, Resource Governor, Extended Events
- And Finally All SQL Server Agent Jobs, Schedules, Operators, Alerts
Post-Migration Steps:
Bring Databases Online
|
1 2 3 |
RESTORE DATABASE [DBAdmin] WITH RECOVERY; RESTORE DATABASE [NLS] WITH RECOVERY; RESTORE DATABASE [SSISDB] WITH RECOVERY; |
Trustworthy
|
1 |
ALTER DATABASE [NLS] SET TRUSTWORTHY ON; |
Due to dependencies we have for our 3rd party product it is crucial we set Trustworthy to TRUE.
DB Owner
|
1 |
Set-DbaDatabaseOwner -SqlServer 'PRSDBS11' |
This will set all database owners to SA where they do not currently match SA
Enable SQL Agent Jobs
I migrated all SQL Agent Jobs in a disabled state, so they would not attempt to run before all migration steps have been confirmed.
Enable all jobs via SQL Agent
Disable ReportServer databases
|
1 2 |
ALTER DATABASE ReportServer SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE ReportServerTempDB SET OFFLINE WITH ROLLBACK IMMEDIATE; |
Conclusion:
In conclusion while I have a lot of steps outside of the DBATool set, that is specific to my environment. The core of the migration is done with DBATools and took a little over a minute.
I love DBATools and it has many more functions that help extend the reach of the DBA.