16 Performing your first advanced SQL Server instance migration, part 2
Now that we have a plan to migrate our databases, we need to consider the other objects that are required for our new environment to work as expected. Just expand all of the data trees throughout SQL Server Management Studio (SSMS) to see how many components can potentially be migrated. We actually used SSMS as a guide to visualize the things we’d need to migrate, outside of SSRS, SSAS, SSIS, and other non-engine-related services.
Some of these objects can be seen in table 16.1, which is limited to SQL Server Database Engine, so SSRS, SSAS, SSIS, and so on are not mentioned. Ultimately, this list covers most of the potential objects to be migrated but is not exhaustive.
Table 16.1 Objects to migrate within the SQL Server Database Engine and dbatools support
|
Includes jobs, alerts, operators, schedules, proxies, categories, properties, and more | ||
|
Includes all properties and permissions, including SID, passwords, default language, and more | ||
That’s quite a list! But it is rare that all of these objects will need to be migrated. Sometimes, you need only databases, logins, and jobs.
In this chapter, we’ll cover a few common migration commands and strategies, including logins, SQL Agent, linked servers, and more.
16.1 Logins and groups
First things first: we’re going to need all our logins so that people and applications can still authenticate. When we think about the ways to authenticate to our SQL Server, we have local SQL Server logins, Active Directory logins, or Active Directory groups set up with permissions at the server level and/or database level. dbatools can help us migrate all of these to our new instance.
To migrate all our logins, we’re going to use Copy-DbaLogin, which does exactly what the name suggests. In the next listing, we have specified a list of logins to migrate.
Listing 16.1 Copying a list of logins from the source to the destination
PS> $copyLoginSplat = @{ Source = "sql01" Destination = "sql02" Login = "WWI_Owner","WWI_ReadWrite","WWI_ReadOnly", ➥ "adJaneReeves" } PS> Copy-DbaLogin @copyLoginSplat Type Name Status Notes ---- ---- ------ ----- Login - WindowsUser adJaneReeves Successful Login - SqlLogin WWI_Owner Successful Login - SqlLogin WWI_ReadOnly Successful Login - SqlLogin WWI_ReadWrite Successful
Here, we have three SQL logins and one Windows user, and all were successfully migrated to the destination instance. dbatools does some wizardry behind the scenes for this command: when the logins are copied, they maintain the SIDs and passwords set on the source server. Copying the SID means that you don’t end up with orphaned users in your destination database because of mismatching SIDs at the instance-versus-database level.
Note that although dbatools cannot decrypt a SQL login’s password, the password hash is copied as is to the new server, and the login will continue to work as expected with the old username and password.
As you have probably come to expect with dbatools, we have a lot of options via parameters when using the Copy-DbaLogin command. So far, we have copied a specific list of logins, but we could also copy all the logins except any that are classed as system logins. The code for this follows.
Listing 16.2 Copying all logins from the source to the destination
PS> $copyLoginSplat = @{ Source = "sql01" Destination = "sql02" ExcludeSystemLogins = $true } PS> Copy-DbaLogin @copyLoginSplat Type Name Status Notes ---- ---- ------ ----- Login - SqlLogin Chrissy Successful Login - SqlLogin Claudio Successful Login - SqlLogin Jess Successful Login - WindowsUser NT ServiceMSSQL$SQL2017 Skipped System login Login - WindowsUser NT SERVICESQLAgent$SQL2017 Skipped System login Login - SqlLogin Rob Successful Login - SqlLogin WWI_Owner Skipped Already exists ➥ on destination Login - SqlLogin WWI_ReadOnly Skipped Already exists ➥ on destination Login - SqlLogin WWI_ReadWrite Skipped Already exists ➥ on destination
It’s worth reviewing the output from this command because it notes the status for each login found on the source and any notes if the login was skipped. You can see that the service accounts were skipped because they are classified as system logins, and the WWI_* accounts were skipped because we already copied them over. You can use the -Force parameter if you want to copy the logins, even if they already exist—in that case, they will be dropped on the destination and copied again from the source.
16.1.1 Which logins/groups are still needed?
We mentioned earlier that quite often at migration time, the source SQL Server instance is littered with old logins that are no longer in use but haven’t been cleaned up. Hopefully your application team will know exactly which logins are still needed, but sometimes it’s not so clear. To make sure we don’t miss migrating any important logins, we could use an Extended Events session to capture any connections to the SQL Server, collect a distinct list of logins/groups, and then copy them across with Copy-DbaLogin, as shown in the following listing.
Listing 16.3 Tracking logins to migrate at a later date
# Import the Login Tracker XE template and start it up Get-DbaXESessionTemplate -Template 'Login Tracker'| Import-DbaXESessionTemplate -SqlInstance mssql2 | Start-DbaXESession # Login via SSMS so that you capture at least one login # then run get a list of the files to read $files = Get-DbaXESessionTargetFile -SqlInstance mssql2 ➥ -Session 'Login Tracker' # Look at $files and make sure you have access to the path # Then stop the session so that you can read all files without issue Stop-DbaXESession -SqlInstance mssql2 -Session 'Login Tracker' # Get a list of unique logins $Logins = (Read-DbaXEFile -Path $files.Fullname).server_principal_name | Select-Object -Unique # Migrate logins Copy-DbaLogin -Login $Logins -Source -Destination
When we’ve used the Login Tracker Extended Event template for our own migrations, we discovered that it tracks a majority of the active logins within 48 hours, but for best results, the Extended Event should run for about 60 days prior to migration. This allows you to capture logins that log in monthly or quarterly.
16.2 SQL Agent objects: Jobs, operators, and more!
The SQL Agent is also quite often a big part of any migration strategy, including all of our jobs, operators, and alerts, among other things. Again, dbatools can help us migrate all of these from one instance to another. Let’s start by looking at jobs.
SQL Agent jobs are useful parts of our database environments, allowing us to schedule the execution of T-SQL, SSIS packages, and even PowerShell. Quite often, jobs are set up by DBAs to schedule maintenance jobs, backups, or checks and balances. They can also be used by the business to encapsulate complex business logic to process data. We all know that maintenance jobs are important, but the jobs containing business logic are arguably even more so, making this a critical part of our migration strategy.
We can view the jobs we have deployed on a SQL Server instance by using Get-DbaAgentJob, as shown in listing 16.4. You can see a mixture of jobs here—some we’ll want to migrate, and some we will opt to ignore. Some jobs were created by Install-DbaMaintenanceSolution, which installs Ola Hallengren’s maintenance solution. Whether you migrate these or install/configure fresh on the new system will depend on how much you have customized them for your workload. In our case, we’ll skip those for our SQL Agent job migration and instead focus on the jobs within the sql02 category.
Listing 16.4 Viewing all SQL Agent jobs on sql01
PS> Get-DbaAgentJob -SqlInstance sql01 | ➥ select-Object SqlInstance, Name, Category SqlInstance Name Category ----------- ---- -------- sql01 CommandLog Cleanup Database Maintenance sql01 DatabaseBackup - SYSTEM_DATABASES - FULL Database Maintenance sql01 DatabaseBackup - USER_DATABASES - DIFF Database Maintenance sql01 DatabaseBackup - USER_DATABASES - FULL Database Maintenance sql01 DatabaseBackup - USER_DATABASES - LOG Database Maintenance sql01 DatabaseIntegrityCheck - SYSTEM_DATABASES Database Maintenance sql01 DatabaseIntegrityCheck - USER_DATABASES Database Maintenance sql01 dbatools lab job - where am I dbatoolslab sql01 dbatools lab job dbatoolslab sql01 IndexOptimize - USER_DATABASES Database Maintenance sql01 LSAlert_sql01 Log Shipping sql01 LSBackup_AdventureWorks Log Shipping sql01 Output File Cleanup Database Maintenance sql01 sp_delete_backuphistory Database Maintenance sql01 sp_purge_jobhistory Database Maintenance sql01 syspolicy_purge_history [Uncategorized (Local)]
To migrate SQL Agent jobs, it’ll probably be no surprise by now that we’ll be using another Copy command—this time, Copy-DbaAgentJob—to migrate our jobs. This command will also migrate our job categories if we’re using them and they don’t already exist on the destination. You can see the code in the next listing to copy across two specific jobs. If we left off the -Job parameter, the command would copy all SQL Agent jobs across.
Listing 16.5 Migrating two SQL Agent jobs from sql01 to dbatoolslab
PS> $copyJobSplat = @{ Source = "sql01" Destination = "sql02" Job = 'dbatools lab job','dbatools lab job - where am I' DisableOnSource = $true } Copy-DbaAgentJob @copyJobSplat Type Name Status Notes ---- ---- ------ ----- Agent Job dbatools lab job Successful ① Agent Job dbatools lab job - where am I Skipped Job is dependent ➥ on operator dba ②
① This job was successfully migrated.
② This job was not migrated because it depends on an operator, which must be migrated first.
The output shown in listing 16.5 shows the status of the migration and any notes that have been collected during the process. You can see that only one of our jobs was successfully migrated. The second one, “dbatools lab job - where am I,” has a status of skipped, and the Notes of the output explains that this is field because it is dependent on the operator named dba. If our jobs are using SQL Server Agent operators for notifications, we need to make sure to migrate those before we migrate the jobs.
We’ll fix this with the code in listing 16.6, where we’ll first migrate the dba operator with Copy-DbaAgentOperator, and then we’ll rerun the Copy-DbaAgentJob code to migrate the jobs. You can see in the output below each code snippet that the operator is successfully migrated. Then, in the second part, the first job was skipped because it already exists (we were able to migrate that on our first attempt), and then the second one has now been copied across successfully.
Listing 16.6 Migrating our missing operator and rerunning the the job migration
PS> $copyJobOperatorSplat = @{ Source = "sql01" Destination = "sql02" Operator = 'dba' } PS> Copy-DbaAgentOperator @copyJobOperatorSplat Type Name Status Notes ---- ---- ------ ----- Agent Operator DBA Successful PS> $copyJobSplat = @{ Source = "sql01" Destination = "sql02" Job = 'dbatools lab job','dbatools lab job - where am I' DisableOnSource = $true } Copy-DbaAgentJob @copyJobSplat Type Name Status Notes ---- ---- ------ ----- Agent Job dbatools lab job Skipped Already exists ➥ on destination Agent Job dbatools lab job - where am I Successful
One final component that we will cover migrating in this section is SQL Agent alerts. These are really useful for alerting DBAs when something goes wrong. Nothing is worse than a customer letting you know your transaction log is full. Instead, you can configure SQL Agent alerts to email an operator, probably your DBA team, to let them know an issue needs some attention.
Most DBAs set up a few alerts, for example, any errors with severity of 16–25. These might not be alerts you want to migrate because they are probably included in your SQL Server build process. However, you can also configure more custom alerts, for example, looking for a certain string or message in the event log. These might be good candidates for migration.
In the next code snippet, you can see we are using Copy-DbaAgentAlert to copy across our custom alert. Because this depends on a custom message, we’ll also need to copy that across, and for that, we can use Copy-DbaCustomError.
Listing 16.7 Migrating a custom error message and a SQL Agent alert
PS> $copyMessageSplat = @{ Source = "sql01" Destination = "sql02" CustomError = 50005 } Copy-DbaCustomError @copyMessageSplat Type Name Status Notes ---- ---- ------ ----- Custom error 50005:'us_english' Successful PS> $copyAlertSplat = @{ Source = "sql01" Destination = "sql02" Alert = 'FactoryApp - Custom Alert' } Copy-DbaAgentAlert @copyAlertSplat Type Name Status Notes ---- ---- ------ ----- Agent Alert FactoryApp - Custom Alert Successful Agent Alert Notification FactoryApp - Custom Alert Successful
As you can see, you have plenty of bits under the SQL Agent to consider when planning your SQL migration. We already mentioned how the databases are the most critical parts to move, but without your jobs, alerts, and operators, it’s likely you’ll be missing a lot of necessary functionality.
In the next listing, we use the functionality of Out-GridView with the -Passthru parameter to create a pop-up GUI to make selecting the jobs easier.
Listing 16.8 Easily copy one or more jobs using Out-GridView
PS> Get-DbaAgentJob -SqlInstance sql01 | Out-GridView -Passthru | Copy-DbaAgentJob -Destination dbatoolslab
16.3 Linked servers
Linked servers allow us to create a window through to remote data sources, such as other SQL servers or other OLE DB data sources. Linked servers do get quite a lot of negative press. Depending on how they are configured, they can create security vulnerabilities (e.g., if they use the saved credentials of a permissive account), and they don’t always lend themselves to stellar performance. However, they are still quite prevalent in the real world, and it’s worth checking out how dbatools helps make migrating these easy.
As you probably guessed, we’re going to use a copy command to migrate linked servers from one instance to another. This time we’ll be using Copy-DbaLinkedServer, as shown in the following code sample. In our case, we have only one linked server, and once again, the returned output shows the result and any notes that have been collected in the process.
Listing 16.9 Migrating all linked servers
PS> $copyLinkedServerSplat = @{ Source = "sql01" Destination = "sql02" } PS> Copy-DbaLinkedServer @copyLinkedServerSplat Type Name Status Notes ---- ---- ------ ----- Linked Server adSQL2O17 Successful SQLNCLI
Linked servers can be set to use the current security context, or they can use saved SQL credentials. If the connection is using a saved SQL connection, the credentials are stored in a table with the password encrypted. dbatools uses a technique explained in the post at sqlps.io/decryptlspwd to retrieve and decrypt the password. This means that, postmigration, the linked server will just work without you having to reenter the credentials. Note that both the Copy-DbaLinkedServer and Copy-DbaCredential commands require Windows registry access and, consequently, do not work on Linux.
Note that the table where the passwords for these credentials are stored is accessible only when using the dedicated administrative connection (DAC). That means that within the Copy-DbaLinkedServer, dbatools will try to use that DAC connection, so it needs to be enabled for remote connections on your SQL Server instance for the passwords to be migrated with your linked servers.
16.4 More migration fun
We’ve covered quite a lot of options when it comes to migrating parts of your SQL Server estate. It’s hard to believe, but you can migrate still more things with dbatools. All of the dbatools commands that deal with migrations begin with the Copy verb, and thanks to PowerShell’s handy Get-Command function, we can quickly list all of the applicable dbatools commands for review, as shown here.
Listing 16.10 Viewing all the dbatools copy commands
16.5 Hands-on lab
-
Migrate some other objects available on sql01, such as a SQL Agent job with an operator or a linked server.
-
Connect to the sql02 instance (destination), and confirm everything expected has been migrated successfully.