Guidelines and Scripts for Migrating from SQL Server 2016

Recently I had a client who was wanting to migrate from SQL Server 2016 to SQL Server 2022. I documented the process and provided useful scripts to assist them on their project. I have provide that documentation and the scripts here.

Here is a zip file that contains the following documents:

• sql_migration_checklist.xlsx (List of prerequisites and migration task steps).
• sql_migration_scripts.sql
• sql_server_settings_documentation.sql
• SQL Server 2019 Features.docx
• SQL Server 2022 Features.docx

sql_migration_scripts.sql is a T-SQL script to support your migration:

1. Backup All User Databases – Uses sp_MSforeachdb to back up all non-system databases.
2. Restore from .bak Files – Template for restoring databases with file relocation.
3. Script Out Logins – Generates CREATE LOGIN statements for user-defined logins.
4. Script SQL Agent Jobs – Lists job metadata; full scripting recommended via SSMS.
5. Check & Update Compatibility Levels – Sets all user databases to compatibility level 160.

sql_server_settings_documentation.sql is a script to document your environment:

1. Server properties and configuration
2. Trace flags and linked servers
3. SQL Agent jobs, logins, and roles
4. Database settings and file info
5. Operators, alerts, endpoints, credentials
6. Audit specifications and Resource Governor

Here is a list of SQL Server 2016 prerequisites for the SQL Server Migration process:

• SQL Server 2016 SP3 or later is installed
• Processor: x64, minimum 1.4 GHz (2.0 GHz recommended)
• Memory: Minimum 1 GB (4 GB or more recommended)
• Disk Space: Minimum 6 GB on system drive
• Operating System: Windows Server 2016 or later
• .NET Framework 4.7.2 is installed
• SQL Server Setup Components: ODBC Driver 17, OLE DB Driver
• Upgrade account has local admin rights
• Upgrade account has sysadmin privileges on SQL Server
• All system and user databases are backed up
• Antivirus/endpoint protection temporarily disabled
• Run Data Migration Assistant (DMA) for deprecated features
• Check compatibility level and deprecated syntax
• Review SSIS/SSRS/SSAS package compatibility
• Review new features in SQL Server 2022
• Open required ports: 443, 53, 9354, 445, 12000
• Firewall and NSG rules allow SQL traffic
• Perform test upgrade in non-production environment
• Validate application compatibility
• Compare performance against baseline
• Validate security configurations
• Enable Query Store for performance monitoring

Here is a high-level overview of the SQL Server Migration process:

Assessment and Planning

• Inventory and Compatibility Check: Use tools like SQL Server Data Migration Assistant (DMA) to assess your current environment for deprecated features, compatibility issues, and unsupported objects.
• Decide on Migration Type:
  • In-place upgrade: Faster but riskier; leaves behind unused components.
  • Side-by-side migration: Safer and cleaner; allows rollback and avoids legacy component clutter.
• Evaluate Application Dependencies: Ensure all applications are compatible with SQL Server 2022.

Pre-Migration Preparation

• Backups: Take full backups of all user and system databases (except TempDB).
• Performance Baseline: Capture performance metrics to compare post-migration behavior.
• Security Review: Audit logins, roles, and permissions. Plan for migration of SQL Server Agent jobs, linked servers, and SSIS packages if applicable.
• Clean Up: Remove unused features or components from SQL Server 2016 to reduce migration complexity.

Migration Execution

• Option A: In-Place Upgrade
  • Run SQL Server 2022 setup and choose “Upgrade from a previous version.”
  • Be aware: shared features and external packages may remain post-upgrade, potentially introducing security risks.
  • Use tools like SCCM or PowerShell to identify and remove legacy components.
• Option B: Side-by-Side Migration
  • Install SQL Server 2022 on a new server or VM.
  • Use tools like:
    • Backup/Restore
    • Detach/Attach
    • Log Shipping
    • Database Mirroring
    • Always On Availability Groups
  • Migrate logins, jobs, and linked servers manually or with scripts.

Post-Migration Validation

• Functional Testing: Validate application connectivity and functionality.
• Performance Testing: Compare against baseline metrics.
• Security Testing: Confirm permissions and roles are intact.
• Cleanup: Remove deprecated components and validate that no legacy services are running.

Monitoring and Optimization

• Enable Query Store to monitor query performance regressions.
• Use Extended Events and Azure Monitor for ongoing diagnostics.
• Consider enabling features like Intelligent Query Processing and Accelerated Database Recovery in SQL Server 2022.

References and Resources

• Microsoft Learn: SQL Server Upgrade Guide
• Upgrade to the Latest Version of SQL Server – SQL Server
• Overview of Data Migration Assistant (SQL Server)  
• Supported Version and Edition Upgrades (SQL Server 2022) – SQL Server.
• SQL Server 2022: Hardware & software requirements – SQL Server