Always On in SQL Server is new high availability disaster recovery feature in SQL Server 2012. Initially it named as HADRON. HADRON means High availability Disaster Recovery Always On. Always On uses clustering technology for failover and mirroring in keeping multiple mirrored copies of the database.
In Always On we can failover multiple databases at the same time using availability groups.
Always On provides ability to have multiple replicas which is not available in Database mirroring. We can have one primary and up to 4 secondary replicas with Always On.
The Secondary replicas configured for read access. We can use for reporting purpose, backups etc.
Always On in SQL Server also have 2 availability modes
Synchronous mode provides Zero data loss.
Asynchronous mode provides better performance, chance of data loss.
For each replica we can configure separate mode.
Components of Always On
- Availability group
- Availability replica
- Primary replica
- Secondary replica
Availability Group is a container used to group databases. Always On availability groups contains multiple databases all of which can be automatically failover dover as single unit.
Availability Replica is a server that contains the availability group which can use for failover.
Primary Replica is a server the holds read-write copy of the databases contained within availability group.
Secondary Replica is secondary server that holds copy of primary replica databases, only read option is available here in an availability group.
Always On in SQL Server Installation
Always on does not require cluster installation. We can go through normal SQL Server installation. Here entire SQL instance will not be clustered only availability which participated in Always On will be clustered. We need to maintain same drive letters and paths for databases on replicas.
After installing SQL Server go to configuration manager, MS SQL Server, Right click and go to SQL Server properties next click Always on high availability. We will get windows failover cluster name as part of windows server failover cluster installation. Under that enable always on Availability Groups.
- Once we enabled Always on Availability Groups, we can create availability groups that contains all databases which we want to failover.
- Go to SSMS Right click on Availability folder, it opens wizard, select the databases which you want to keep in Always on in primary replica as well as replicas that will host on secondary replica here.
- The wizard automatically creates end points to synchronize data and takes backup of primary replicas and restore them on secondary replicas.
- If we don’t want, we can do backup and restore manually.
Architecture of Always On in SQL Server
Always on availability group is superior to SQL Server clustering.
- Configuration, deployment, installation is relatively simple comparing to normal clustering.
- All replicas (Nodes) will have a copy of databases, there is no shared storage hence we can avoid single point of failover.
- We have readable secondary replica hence we can distribute read-only load to secondary node and read-write load to primary hence better utilization of both servers. Whereas we must keep in passive state in clustering. There is no concept of readable databases as we keep everything in shared disk.
Always on superior to database mirroring
- We have up to 4 mirrored instances replicas here.
- We can use combination of synchronous commit mode for some databases and asynchronous commit mode for other databases at the same time, not possible in mirroring.
- Synchronous mode for high availability (Automatic failover) and asynchronous mode for disaster recovery purpose.
- We can combine 2 or more databases and failover them as a unit, we can perform this database mirroring.
- Databases are always in recovery mode in mirroring, here secondary replica databases are readable, hence we can take load from primary replica.
- Backup operations can be performed on secondary replica databases.
How Always On in SQL Server works
Always on feature based on windows cluster services only. Availability groups are a grouping database with a virtual name and IP Address that act as a single unit for users to access. If a server fails with in an availability group, the entire group is failover to the secondary replica of secondary server.
To create databases, we need SQL Sever to be installed on a server under windows clustered. SQL Server clustering does not need in Always On. Primary replica sends Transaction log records of each primary database to every secondary database. The secondary replica applies changes to the secondary database. Data Synchronization occurs between primary and secondary databases. Windows failover clusters monitors this resource group to evaluate health of primary replica.
The quorum for Always on Availability group is resides on all nodes of cluster which helps in failover. Here is o witness role in always on availability groups.
Types of failovers are like mirroring.
Synchronous commits support planned and manual failover. Automatic process and no data loss.
Asynchronous commits support only forced failover. Chance of data loss.
Advantages and Disadvantages of Always On in SQL Server
- We can maintain multiple secondary databases.
- Secondary database used for read-only access, we can use for reporting purpose and backups.
- Protects data against disk failure.
- Automatic failover and group of databases failover as single unit.
- Requires more disk space to store secondary replica data.
- Requires special setup configuration.
- Expensive to implement.