SQL Server Always on Availability Related Queries:
How do I switch Primary / Secondary on Switch Always on Availability Groups ( Swap Primary / Secondary ):
When the automatic failover is setup , the Primary / Secondary gets swapped during maintenance tasks.
To swap back the Primary/Secondary , connect to the server which’s secondary and run the following command. If you run it on the principal node, you will encounter the following error.
USE master; GO ALTER AVAILABILITY GROUP sqlclusteravialabilityname FAILOVER GO /* Msg 41122, Level 16, State 12, Line 4 Cannot failover availability group 'sqlclusteravialabilityname ' to this instance of SQL Server. The local availability replica is already the primary replica of the availability group. To failover this availability group to another instance of SQL Server, run the failover command on that instance of SQL Server. If local instance of SQL Server is intended to host the primary replica of the availability group, then no action is required. */
Always-On Replica Cluster Status:
USE master; GO SELECT is_failover_ready, * FROM sys.dm_hadr_database_replica_cluster_states GO
Remove Database from AlwaysOn Availability Group and Set Database OFFLINE
ALTER AVAILABILITY GROUP sqlcaggroup6608c REMOVE DATABASE [UserDBName]; ALTER DATABASE [UserDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE -- Secondary RESTORE DATABASE [UserDBName]WITH RECOVERY GO ALTER DATABASE [UserDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE
SELECT SERVERPROPERTY ('IsHadrEnabled');
Failover is Not Immediate. WSFC failover is pretty impressive—it usually happens in a matter of 2-3 seconds or less (where there are ‘clean’ failures). Still, once a SQL Server instance spins up somewhere and takes control of database resources, it’s going to have to walk through the recovery process. With Availability Group Replicas, this recovery time is typically going to be brief (when we’re talking about synchronous replicas)—where brief can be on the order of 20-40 seconds. With Failover Cluster Instances, however, the new active node is basically starting from scratch—a cold-start as it were—and typically takes a bit under a minute to be ready to start serving all databases as needed. For more info, check out my post on indirect checkpoint—a VERY cool (but powerful) feature of SQL Server 2012.
Alwayson Throughput and Performance Related Issues:
AlwaysOn Performance Related Issues
Monitor Alwayson Availability Groups:
Monitor Alwayson Availability Groups
ALWAYS ON Availability Groups:
Monitor Performance for AlwaysOn Availability Groups
Always On–When Is My Secondary Failover Ready?
SQL Server 2012 AlwaysOn – What is it?
SQL Server AlwaysOn Availability Groups Cheat Sheet
SQL Server AlwaysOn Availability Groups Gotchas
Powershell Script TO Enable:
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\TESTSERVER\DEV2012