Here are a few things to remember when working with Availability Groups:
Backup and Restore
NOTE: Using the Recovery Advisor GUI in SSMS is advised as it can quickly devise the restore sequence with multiple LOG and BAK locations for a given database.
NOTE: To restore a database that is a part of an availability group, you will first need to remove it from the availability group.
Seeding New Databases on Asynchronous Replicas
When adding a new database to the AG, Remember it will auto-seed the database to the synchronous replicas, but the asynchronous replicas will need a FULL and TAIL LOG backup to add them to the AG on that replica. Here is the script below to perform that task:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
--AGBackupAndRestoreTemplateForAsyncReplica -- REF: https://www.mssqltips.com/sqlservertip/4687/fix-sql-server-alwayson-availability-group-error-1408-joining-database-on-secondary-replica-resulted-in-an-error/ -- Backup database and tail log from primary replica BACKUP DATABASE MyDatabase TO DISK = '\\MySAN\sqlbackups\PRIMARY01\MyDatabase_Copy.bak' WITH COPY_ONLY; GO BACKUP LOG MyDatabase TO DISK = '\\MySAN\sqlbackups\PRIMARY01\MyDatabase_Copy.trn' WITH COPY_ONLY; GO -- Restore backup and tail log to asynchronous replica RESTORE DATABASE MyDatabase FROM DISK = '\\MySAN\sqlbackups\PRIMARY01\MyDatabase_Copy.BAK' WITH NORECOVERY, REPLACE; GO RESTORE LOG MyDatabase FROM DISK = '\\MySAN\sqlbackups\PRIMARY01\MyDatabasel_Copy.trn' WITH NORECOVERY, REPLACE; GO |
Distributing Workloads
READ_ONLY Routing
Configuring read replica’s allows you to utilize the secondary servers for processing SELECT queries off of the primary replica, thus increasing the performance on the primary for data updates and inserts.
Below is the connection string attribute that the client applications should add if they are able to use the secondary read capabilities of the availability group and do not need to read from the primary:
Server=AGCorp,1433;Database=AdventureWorks2016;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly
Configuring the read-only routing URL’s:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE MASTER GO ALTER AVAILABILITY GROUP AGCorp MODIFY REPLICA ON 'AlwaysOnN1' WITH (secondary_role(read_only_routing_url='tcp://AlwaysOnN1:1433')) ALTER AVAILABILITY GROUP AGCorp MODIFY REPLICA ON 'AlwaysOnN2' WITH (secondary_role(read_only_routing_url='tcp://AlwaysOnN2:1433')) ALTER AVAILABILITY GROUP AGCorp MODIFY REPLICA ON 'AlwaysOnN3' WITH (secondary_role(read_only_routing_url='tcp://AlwaysOnN3:1433')) |
Configuring the read-only routing list:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE MASTER GO ALTER AVAILABILITY GROUP AGCorp MODIFY REPLICA ON 'AlwaysOnN1' WITH (primary_role(read_only_routing_list=('AlwaysOnN2','AlwaysOnN3','AlwaysOnN1'))) ALTER AVAILABILITY GROUP AGCorp MODIFY REPLICA ON 'AlwaysOnN2' WITH (primary_role(read_only_routing_list=('AlwaysOnN3','AlwaysOnN1','AlwaysOnN2'))) ALTER AVAILABILITY GROUP AGCorp MODIFY REPLICA ON 'AlwaysOnN3' WITH (primary_role(read_only_routing_list=('AlwaysOnN1','AlwaysOnN2','AlwaysOnN3'))) |
NOTE: some aspects of configuring READ_ONLY routing can only be configured with T-SQL. There are no SSMS GUI screens to configure them. See page 156 of Apress’s ‘Pro SQL Server AlwaysOn Availability Groups’ book.
NOTE: READ_ONLY routing stops working when the primary replica is offline. “Read-only routing does not work if the primary is down, because the availability group listener is offline as well. Clients would have to connect directly to the read-only secondary replicas for read-only workloads. Prior to SQL 2014, you could not read from a secondary replica database if it was in a disconnected or not synchronized state. Hence the direct connection to read from the secondary is only available from SQL Server 2014 onwards.”
Replica Synchronization
IMPORTANT: Suspending the HADR activity on the database will cause a backlog of log blocks and cause the transaction log file(s) to grow on the primary and other remaining replicas that do not have data synchronization suspended.”. In short, always try to keep database synchronization flowing as much as possible to minimize this. %nbsp;
Data Latency
Remember that on the AlwaysOn Dashboard the data latency column is based on the log being committed. If the secondary is reporting 0ms latency, that does not necessarily mean that the database is current. The REDO thread still needs to apply them yet to the database itself.
MONITOR THE REDO QUEUE SIZE ON ALL SECONDARIES
This has a big impact on the Primary replica. Set REDO queue thresholds for alerting. Read more about this on page 162.
Upgrades, Updates and Hotfixes
When planning for any version upgrade, service packs, or hotfixes, read the “Pro SQL Server Availability Groups” book chapter 13 completely before starting. It walks you through every step, including temporary configuration changes required BEFORE and AFTER the upgrade process.
Rolling Upgrade Best Practices
Similar to the best practices list for upgrading a regular SQL Server instance, we have a list of best practices for performing rolling upgrade for availability group replicas. Following the best practices will avoid data loss, maximize the uptime, and minimize the downtime for client applications using the availability groups.
– Take a full database backup of all databases (if you already don’t have one).
– Run DBCC CHECKDB on all databases and ensure that there are no errors.
– Manually failover on at least one of your synchronous-commit secondary replicas and ensure that it fails over as expected.
– Remove automatic failover from all synchronous-commit replicas. This is to avoid any unintended fail-overs during the upgrade process.
– Change the automated backup preference as discussed in chapter 11 to ensure that the backups will not run on the replica being upgraded.
– Always upgrade the remote secondary replicas first followed by the local secondary replicas, and the primary replica the last.
– Before upgrading the primary replica, fail over the availability group to an upgraded synchronous-commit secondary replica with a SYNCHRONIZED state. If you fail over to an asynchronous-commit secondary replica, then you can have data loss and all availability databases will be suspended requiring us to manually resume them.
– If the replicas are SQL Server failover clustering instances (FCIs), then upgrade the inactive node of the FCI before you upgrade the active node.
NOTE: Failure to fail over to an upgraded secondary replica before upgrading the primary replica will affect the applications and users and will extend the downtime during the primary replica upgrade.
Views – 1226