16 Performing your first advanced SQL Server instance migration, part 2
Beyond moving databases, a successful SQL Server migration must account for many instance-level objects so the new environment behaves as expected. Using SSMS’s object tree as a checklist helps reveal the breadth of components to consider, and dbatools provides extensive support for copying many of these Database Engine items. Not every organization needs to move everything—often databases, logins, and jobs are enough—so the guidance emphasizes deciding what is essential and then using focused, repeatable PowerShell workflows to move those parts safely.
The chapter begins with logins, highlighting how Copy-DbaLogin preserves SIDs and password hashes to prevent orphaned users and keep existing credentials working. It shows how to migrate specific accounts, exclude system logins, re-copy with force, or skip selected principals as needed. Because many instances contain stale accounts, the authors demonstrate using an Extended Events “login tracker” to capture active logins over time, producing a precise list to migrate. Practical PowerShell tips—like capturing command output with OutVariable—help make the process auditable and repeatable.
It then turns to SQL Agent, where Copy-DbaAgentJob moves jobs and their categories, while addressing dependencies by migrating operators (Copy-DbaAgentOperator) and custom alerts (Copy-DbaAgentAlert) along with any required custom error messages (Copy-DbaCustomError). For Linked Servers, Copy-DbaLinkedServer carries over configuration and, when possible, saved credentials; this requires DAC access to decrypt stored passwords and Windows registry access, so Remote DAC must be enabled and Linux hosts are not supported for that step. The chapter closes by reminding readers that many other components can be migrated with dbatools’ Copy-* commands, encouraging discovery and tailored migration plans.
Hands-on Lab
Migrate some of the logins from sql01 to sql02.
Migrate some other objects available on sql01. For example 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.
FAQ
What SQL Server objects besides databases should I plan to migrate?Common candidates include SQL Agent (jobs, alerts, operators, schedules), logins, credentials, Linked Servers, Database Mail, Extended Events, custom errors, audits, availability groups, backup devices, Central Management Server, master certificates, Policy Management, Resource Governor, sp_configure settings, startup procedures, system triggers, and user objects in system databases. Not everything needs to move—focus on what the new environment actually uses. Some areas (for example, Maintenance Plans, Replication, Service Broker, custom server roles) may not be supported by dbatools copy commands.How do I copy specific logins and keep SIDs and passwords intact?Use Copy-DbaLogin with the -Login parameter. The command preserves SIDs and copies password hashes, preventing orphaned users and keeping existing passwords working on the destination.How do I copy all non-system logins (and optionally exclude a few)?Use Copy-DbaLogin -ExcludeSystemLogins to skip system accounts. To skip specific logins, add -ExcludeLogin with one or more names. If a login already exists on the destination and you want to overwrite it, include -Force.Can I capture command output to reuse later in my script?Yes. PowerShell’s common parameter -OutVariable records output to a variable while still displaying it. Example: Get-DbaDatabase -SqlInstance sql02 -ExcludeSystem -OutVariable databasesHow can I find which logins/groups are actually still in use before migrating?Run the “Login Tracker” Extended Events template to capture successful connections, collect a distinct list of principals from the XE files, and feed that list to Copy-DbaLogin. Run the session long enough (often 48 hours captures most activity; ~60 days is best to catch monthly/quarterly activity).What’s the best way to migrate SQL Agent jobs and handle dependencies?Use Copy-DbaAgentJob. It can create missing categories and optionally disable jobs on the source. If a job depends on an operator, migrate the operator first with Copy-DbaAgentOperator, then rerun the job migration.How do I migrate SQL Agent alerts and any required custom messages?Migrate custom error messages with Copy-DbaCustomError first, then migrate the alert with Copy-DbaAgentAlert. This preserves alert definitions and associated notifications.Is there a quick way to interactively pick which jobs to migrate?Yes. Pipe jobs to Out-GridView -Passthru, then into Copy-DbaAgentJob. Example: Get-DbaAgentJob -SqlInstance sql01 | Out-GridView -Passthru | Copy-DbaAgentJob -Destination sql02How do I migrate Linked Servers, including saved credentials?Use Copy-DbaLinkedServer. When Linked Servers use saved SQL credentials, dbatools reads and decrypts the stored password via the Dedicated Administrative Connection (DAC) and Windows registry access, then recreates the mapping on the destination. Ensure Remote DAC is enabled; note this password migration method isn’t supported on Linux.How do I list all dbatools migration (Copy-*) commands I can use?Run: Get-Command -Module dbatools -Verb Copy. This shows all available copy-focused commands, making it easy to discover what else you can migrate.
pro $24.99 per month
access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
Learn dbatools in a Month of Lunches ebook for free
