Introduction
Microsoft introduced the AlwaysOn Availability Groups feature with SQL Server 2012 release.
AlwaysOn Availability Groups can be used to replace the previously described database mirroring and log shipping solution.
It covers two features in SQL Server: availablity groups (AGs) and failover clustering instances (FCIs).
Disaster Recovery was provided with Log Shipping but Real-time synchronization does not exist with Log Shipping. Mirroring has Real-time synchronization but there was no Read-Only option, and Witness Server required for Automatic Failover.
Application has access to 2 nodes via Failover Cluster name same time with Failover Clustering , but the failover cluster was only a High availability solution.
Microsoft has combined all of the advantages Log Shipping, Mirroring and Failover Cluster in the SQL Server Always On.
Microsoft has combined all of the advantages Log Shipping, Mirroring and Failover Cluster in the SQL Server Always On.
SQL Server AlwaysOn Architecture:
Microsoft has combined all of the advantages Log Shipping, Mirroring and Failover Cluster in the SQL Server Always On.
AlwaysOn provides Disaster Recovery because When Storage or Motherboard ( or other part of Server ) of Primary Server has failed , you can recover database with Automatic Failover. Because database physically exists at Secondary node and syncing from Primary database.
You can also use Secondary Node for Reporting and Backup purposes.
In short, if you are considering High Availability and Disaster Recovery together for your SQL Server databases running in your Company, the only solution is AlwaysOn.
You can also use Secondary Node for Reporting and Backup purposes.
In short, if you are considering High Availability and Disaster Recovery together for your SQL Server databases running in your Company, the only solution is AlwaysOn.
Using availability groups for HA and DR provides the following
advantages:
► You can group more than one user database into a single unit of failover. In contrast , database mirroring permits only one user database as a unit of failover.
► Availability groups multiple secondary's enable the user to unify the HA and DR solution into one technology, instead of using multiple technologies used in the earlier solution.
► Secondary replicas can also be configured to permit read-only workloads to obtain close to real-time data from them. Unlike with log shipping, ongoing read-only connections to the secondary replicas do not need to be disconnected in order to see ongoing data modifications against the primary replica. Secondary replicas can also be used to offload full database and transaction log backup operations.
► Availability groups and the associated availability group listener support automatic client redirection to the primary replica or redirection to available readable secondary's. Availability group listeners remove the need to designate a failover partner in the client connection string.
PRE-REQUISITES
1. The system should not be a domain controller.
2. Primary & Secondary Server should be connected to Same Domain.
3. All applicable Window hot fixes should have been installed on every server to be a part of WSFC With latest service pack installed on each node.
4. Each computer to be part of Windows Cluster should have Failover cluster Manager installed on it.
5. .Net Frame work 3.5 should be installed.
6. User creating Windows Cluster should be a domain Administrator or domain user with create computer.
Create Objects rights given to SQL Service Account.
7. Primary & Secondary Server should be on the Same WSFC (Windows Server Failover Cluster).
8. File Share Witness to be allocated for Quorum Creation.
9. Virtual Name and IP to be allocated for Windows Cluster, MSDTC Resource, SQL Listener, SQL AG Group.
10. Primary & DR Server Drive Letters should be exact Replica along with Software & Patches.
11. SQL Server 2012 Enterprise Edition required along with SP1.
12. Windows 2012 STD Edition & Above along with latest patch set.
REQUIREMENTS
1. SAN storage on DR AG node with exact same drive letters as per Primary Node.
2. Windows cluster virtual IP and Always on Listener IP.
3. Windows Cluster Name, SQL Listener Name & SQL AG Group Name.
4. Domain User with privileges to Create Objects in Active Directory, Read Attributes and Modify.
5. Service Account for SQL Server i.e. Domain User.
6. File Share Witness connected to Primary & Secondary Server with Read-Write Permission to SQL Service Account & Windows Cluster Name.