SQL Server AlwaysOn Availability Groups

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:
https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx
Monitor Alwayson Availability Groups

ALWAYS ON Availability Groups:
https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx

Monitor Performance for AlwaysOn Availability Groups
https://technet.microsoft.com/en-us/library/dn135338(v=sql.110).aspx

Always On–When Is My Secondary Failover Ready?
https://blogs.msdn.microsoft.com/psssql/2013/04/22/how-it-works-always-onwhen-is-my-secondary-failover-ready/

SQL Server 2012 AlwaysOn – What is it?
http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/07/sql-server-2012-alwayson-what-is-it.aspx

SQL Server AlwaysOn Availability Groups Cheat Sheet
http://sqlturbo.com/sql-server-alwayson-availability-groups-cheat-sheet/

SQL Server AlwaysOn Availability Groups Gotchas
http://sqlmag.com/blog/common-sql-server-alwayson-gotchas

Powershell Script TO Enable:

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\TESTSERVER\DEV2012

https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx
http://blogs.msdn.com/b/sqlcat/archive/2014/02/03/alwayson-availability-groups-listener-named-instances-port-numbers-etc.aspx

Handling MetaData