Tags: Always On Availability Groups, high availability
One of the largest US national credit unions needed to upgrade their obsolete SQL Server database to take advantage of the latest Always On Availability Groups offerings.
We came up with a database upgrade solution that included re-architecting of the customer’s applications. In the end, our customer started using a truly high availability database system while improving its failover features.
Learn more about the technical issues of this upgrade project from the following video.
Outdated Original System
The original customer’s system included a significant number of SQL Server 2008 databases. They were using the shared storage for disaster recovery. Shared storage remains the only available solution for the database systems working on SQL Server 2008.
Microsoft ended mainstream support for SQL Server 2008 on July 8, 2014. Then our customer decided to upgrade his database system in order to leverage the latest high availability features.
Microsoft introduced Always On Availability Groups solution for disaster recovery in SQL Server 2012 release. So, our customer needed to upgrade their database system to at least SQL Server 2012 in order to set up the Always On Availability Groups.
Cleaning Up the Source Code
On the preliminary step, we needed to analyze the possibility of seamless database upgrade. We leveraged the SQL Server Upgrade Advisor to identify the deprecated features in the source code. Later Microsoft replaced the SQL Server Upgrade Advisor tool with Data Migration Assistant.
We identified not only the already deprecated features, but also the features that would become unavailable in the coming releases of SQL Server. We converted them replacing the deprecated syntax with unified syntax according to Microsoft’s suggestions.
Database Migration
Then we split the customer’s system into two parts according to the existing licenses. We decided to move the production unit to SQL Server 2016. Also, we utilized the SQL Server 2012 edition for the test environment.
Of course, the database upgrade process included the hardware upgrade, too. We installed the latest versions of SQL Server on the new servers, while the machines with SQL Server 2008 were no longer in use.
In the scheduled downtime we created backups of the old databases and copied them to the SQL Server 2016 environment, thus completing the database upgrade.
Leveraging the Always On Availability Groups
In the end, the customer’s system comprised of 4 servers. Two of these servers were running on SQL Server 2012 and another pair utilized the SQL Server 2016. We consolidated the identical server pairs to set up the Always On Availability Groups. Now the data replication between a couple of servers was performed on the database level.
Observations
We’ve learned quite a lot of nuances on High Availability systems after completing this project. Keep in mind that a High Availability system can still go down. So, setting up the Always On Availability Groups doesn’t mean you don’t need to keep an eye on your databases. We strongly recommend continuing to monitor your database system, identify the possible issues and the steps to recover them.
Microsoft introduced the Always On Availability Groups in SQL Server 2012, and since then they’ve improved this solution a lot. So, you need to bear in mind the version of the SQL Server you’re using because the latest versions provide more valuable features.
For example, a database in availability group could be offline due to a disk failure, and the rest of the instance can be totally active. With SQL Server 2014 and earlier implementations, failover would not be triggered because the overall health of the instance is still good. In SQL Server 2016, all databases within the affected AG would failover.
The Distributed Transaction Coordinator (DTC) allows you to manage transactions across multiple databases and instances. Sadly, the DTC is not supported in availability groups in SQL Server 2014 and earlier, so, the absence of DTC may result in data loss.
In SQL Server 2014, you can use three synchronous replicas. Although only two of those can be designated as automatic failover targets. This means those two synchronous replicas would be the primary failover target for each other. In SQL Server 2016, all three synchronous replicas can now be designated as failover targets. As a result, if an issue occurred during or even after a failover, the third synchronous replica could also participate as a failover target.
Results and Benefits
To make the project even more cost-effective, we reduced travel expenses and performed all upgrade operations remotely, using the RDP connection.
Finally, we provided the customer with a full set of documentation related to the upgraded system and the best practices on using and upgrading the high availability database system.
The list of customer’s benefits included:
- Optimized database code with no deprecated features;
- Efficient high availability production system running on the latest edition of SQL Server;
- Increased performance, security and failover features.
