Saving Time with SQL Server Migrations Using Always On Availability Groups and Log Shipping

Posted on by Yvonne Vanslageren

Introduction

Migrating databases to a newer version of SQL Server is a critical task that often comes with challenges, especially when aiming to minimize downtime. Leveraging SQL Server’s Always On Availability Groups and log shipping features allows you to perform most of the migration work in advance, ensuring a seamless transition with minimal impact on your operations. This blog provides a step-by-step approach to migrating from SQL Server 2017 to SQL Server 2022, focusing on efficiency and continuity.

Overview

To migrate with the least amount of downtime, it’s essential to utilize strategies that enable pre-migration work. Setting up a SQL Server Always On Availability Group with a primary replica on SQL Server 2017 and a secondary replica on SQL Server 2022 is a viable solution. Alternatively, log shipping offers a method for continuous data synchronization between two SQL Server instances, allowing for a controlled cutover to the new version.

Key Considerations

Backward Compatibility

  1. Version Support: SQL Server Always On Availability Groups support replicas running different SQL Server versions.
  2. Primary Replica Version: The primary replica must be on the same or an older version compared to the secondary replicas.
  3. Migration Validity: Configuring SQL Server 2017 as the primary and SQL Server 2022 as the secondary is both valid and supported.

Database Upgrade Path

To ensure data integrity and minimize downtime during the upgrade:

  1. Add SQL Server 2022 as a Secondary Replica: Introduce the newer version into your environment without disrupting the primary operations.
  2. Fail Over to SQL Server 2022: Make the SQL Server 2022 instance the new primary replica.
  3. Upgrade the Former Primary: Update the SQL Server 2017 instance to SQL Server 2022.
  4. Re-add the Upgraded Server: Integrate the upgraded server back into the availability group as a secondary replica.

Feature Compatibility

Potential Discrepancies: Some features exclusive to SQL Server 2022 may not be fully compatible or may behave differently when the primary replica is on SQL Server 2017.
Testing: It’s crucial to thoroughly test applications and workloads in the mixed-version environment to avoid unexpected behaviors.

Support and Documentation

Official Guidance: Always consult the official Microsoft documentation on Always On Availability Groups for the latest information regarding version compatibility and supported configurations.

Licensing and Maintenance

License Verification: Ensure that your licensing agreements and maintenance plans allow for running different SQL Server versions within the same Always On configuration.
Upgrade Limitation: Running a mixed-version configuration is intended for upgrades and is not recommended for long-term use.

High-Level Setup Steps

Prerequisites

Primary Server: SQL Server 2017 installed and configured as the primary server.
Secondary Server: SQL Server 2022 installed and configured as the secondary server.
Network Configuration: Both servers should be part of the same Active Directory domain with proper network connectivity and DNS settings.

Step 1: Prepare the New Windows Server 2022 Node

Install Windows Server 2022 on the new server and join it to the existing Active Directory domain. Verify network connectivity and ensure that DNS settings are correctly configured to communicate with the other Availability Group nodes.

Step 2: Install SQL Server 2022

Install SQL Server 2022 on the new Windows Server 2022 instance. Use the same service accounts and configure the same endpoints as on the existing SQL Server nodes.

Enable Always On Availability Groups:

1. Open SQL Server Configuration Manager.
2. Navigate to SQL Server Services.
3. Right-click on the SQL Server (MSSQLSERVER) service and select Properties.
4. Go to the Always On High Availability tab.
5. Check the box for Enable Always On Availability Groups.
6. Click OK and restart the SQL Server service.

Step 3: Add the New Node to the WSFC Cluster

Use Failover Cluster Manager to add the new Windows Server 2022 node to the existing Windows Server Failover Cluster (WSFC). Confirm that the cluster recognizes the new node and that it reports as healthy.

Step 4: Set Up SQL Server 2017 as the Primary Replica

On the SQL Server 2017 instance:

  1. Ensure the databases are set to the Full recovery model.
  2. Take full and transaction log backups of the databases.
  3. Create and configure the Availability Group, including the desired databases.
  4. Set up the endpoints and listener as needed.

Step 5: Add SQL Server 2022 as a Secondary Replica

On the primary server (SQL Server 2017), add the SQL Server 2022 instance as a secondary replica.

Example Commands for Adding SQL Server 2022 as a Secondary Replica

On the Primary Server (SQL Server 2017):
ALTER AVAILABILITY GROUP [YourAGName]
ADD REPLICA ON N'SQL2022ServerName'
WITH (
    ENDPOINT_URL = N'TCP://SQL2022ServerName:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL
);
On the Secondary Server (SQL Server 2022):

Join the database to the Availability Group:

ALTER DATABASE [YourDatabaseName] SET HADR AVAILABILITY GROUP = [YourAGName];

Join the secondary replica to the Availability Group:

ALTER AVAILABILITY GROUP [YourAGName] JOIN;

Start data synchronization:

ALTER DATABASE [YourDatabaseName] SET HADR RESUME;

Step 6: Fail Over to SQL Server 2022

Once synchronization is confirmed:

  1. On the Primary Server (SQL Server 2017): 
    ALTER AVAILABILITY GROUP [YourAGName] FAILOVER;
  2. Verification: Ensure that the SQL Server 2022 instance is now the primary replica.

Step 7: Upgrade the Former Primary Server

Upgrade the SQL Server 2017 instance to SQL Server 2022. After the upgrade:

  1. Re-enable Always On Availability Groups in SQL Server Configuration Manager.
  2. Re-add the server to the Availability Group as a secondary replica using the steps outlined above.

Windows Server Failover Cluster Considerations

While the WSFC cluster can temporarily support mixed Windows Server versions during migration, it’s recommended to upgrade all nodes to the same Windows Server version for long-term stability. Consult Microsoft’s guidelines on Windows Server Failover Cluster version compatibility for detailed instructions.

Using Log Shipping for Migration

Log shipping is an alternative method that enables continuous data synchronization between the SQL Server 2017 and SQL Server 2022 instances.

Step-by-Step Migration Process

Step 1: Prepare the Environment

Install SQL Server 2022: Set up SQL Server 2022 on the new server.
Network Connectivity: Ensure that both servers can communicate over the network.

Step 2: Configure Log Shipping

On the Primary Server (SQL Server 2017):

Full Backup of the Primary Database:

BACKUP DATABASE [YourDatabaseName] TO DISK = 'C:\Backup\YourDatabaseName_Full.bak';

Copy the backup file to the SQL Server 2022 server.

On the Secondary Server (SQL Server 2022):

Restore the Full Backup:

RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak' WITH NORECOVERY;

Enable Log Shipping:

  1. On the Primary Server: Right-click the database in SSMS, select Properties, and navigate to the Transaction Log Shipping page.
  2. Enable Log Shipping: Check Enable this as a primary database in a log shipping configuration.
  3. Configure Backup Settings: Specify the backup directory and schedule.
  4. Add Secondary Server Details: Input the SQL Server 2022 instance details and the network path for copying logs.
  5. Configure Restore Settings: Choose No recovery mode for continuous restoring.

Step 3: Start Log Shipping

The SQL Server Agent jobs will handle the backup, copy, and restore operations according to the schedule you configured.

Step 4: Monitor Log Shipping

Use the Log Shipping Monitor or check the status in SSMS to ensure that the transaction logs are being backed up, copied, and restored without errors.

Step 5: Perform the Cutover to SQL Server 2022

On the Primary Server (SQL Server 2017):

Stop All Transactions:

ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Take a Final Log Backup:

BACKUP LOG [YourDatabaseName] TO DISK = 'C:\Backup\YourDatabaseName_LogFinal.trn';

Copy the final log backup to the SQL Server 2022 server.

On the Secondary Server (SQL Server 2022):

Restore the Final Log Backup:

RESTORE LOG [YourDatabaseName] FROM DISK = 'C:\Backup\YourDatabaseName_LogFinal.trn' WITH RECOVERY;

Set the Database to Multi-User Mode:

ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 6: Update Applications and Services

Modify application connection strings to point to the SQL Server 2022 instance. Verify that all services and applications are functioning correctly with the new database server.

Considerations and Best Practices

Testing: Always perform the migration process in a non-production environment to identify potential issues.
Downtime Planning: Plan for a maintenance window during the cutover to minimize impact.
Backup Strategy: Maintain up-to-date backups of all databases involved.
Monitoring: Keep an eye on the migration process to quickly address any issues.

Conclusion

By leveraging Always On Availability Groups or log shipping, you can significantly reduce downtime during your SQL Server migration. These methods allow most of the work to be done in advance, ensuring a smooth transition to SQL Server 2022. Careful planning, thorough testing, and adherence to best practices are key to a successful migration.

Leave a Reply

©2024 SQL Table Talk
Disclaimer: The views and opinions expressed on this blog are solely my own and do not reflect the views and opinions of Microsoft Corporation. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.