SQL Server AlwaysOn provides a high-availability and Disaster-recovery solution for SQL Server.
An availability group supports one set of primary databases and one to eight sets of corresponding secondary databases.
Each set of availability database is hosted by an availability replica. Two types of availability replicas exist: a single primary replica. which hosts the primary databases, and one to eight secondary replicas, each of which hosts a set of secondary databases and serves as a potential failover targets for the availability group.
The primary replica makes the primary databases available for read-write connections from clients. The primary replica sends transaction log records of each primary database to every secondary database. This process – known as data synchronization – occurs at the database level. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database. Data synchronization occurs between the primary database and each connected secondary database, independently of the other databases. Therefore, a secondary database can be suspended or fail without affecting other secondary databases, and a primary database can be suspended or fail without affecting other primary databases.
By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.
Optionally, you can configure one or more secondary replicas to support read-only access to secondary databases, and you can configure any secondary replica to permit backups on secondary databases.
AlwaysOn Failover Clustering Instances(FCI)
Built on Windows® Failover Clustering, AlwaysOn Failover Clustering Instances (FCI) are designed to provide server/instance level protection from both planned and unplanned downtime. AlwaysOn FCI provide automatic failover in the event of an unplanned server failure, and they offer no data loss. If a server in an AlwaysOn FCI fails, then the SQL Server service is started on one of the remaining nodes, and any uncommitted transactions are applied to the new SQL Server instance.
There can be a several-minute delay for the failover process to complete because of the length of time it takes to start the service and reapply all of the outstanding transactions. Windows Failover Clusters can have up to 64 nodes with Windows Server® 2012 R2.
AlwaysOn FCI needs shared storage like an iSCSI or Fibre Channel SAN that can be accessed by all of the nodes in the cluster. AlwaysOn FCI can be implemented on physical SQL Server systems, or it can be implemented on SQL Server systems that are running as VMs. The different cluster nodes can reside on the same host, or they can span different hosts as long as they can access the shared storage.
On the SQL Server 2014 Enterprise edition, AlwaysOn FCI supports up to 64 nodes with Windows Server® 2012 / R2. The SQL Server 2014 Standard and Business Intelligence editions are limited to two-node clusters.
Like you might expect, to use AlwaysOn FCI you need to first create a Windows Failover Cluster. You give the cluster a unique name and IP address, which network clients use to access the clustered SQL Server instance. After you create the cluster, you need to use the SQL Server Installation Center and select the New SQL Server failover cluster installation. Then, on all of the other additional cluster nodes, you would launch the SQL Server Installation Center, and run Add node to a SQL Server failover cluster to install SQL Server on the remaining cluster nodes.
AlwaysOn Availability Groups
Today, AlwaysOn Availability Groups (AG) are probably the most widely used SQL Server availability technology. AlwaysOn AGs were first introduced with SQL Server 2012, and they provide database-level protection from unplanned downtime. AlwaysOn AGs offered several advantages over database mirroring, which was SQL Server’s previous database-level, high availability technology. Database mirroring was limited to a single secondary mirror server. It could only protect a single database. Plus, it required you to choose between asynchronous and synchronous replication between the primary and the secondary mirror server.
AlwaysOn AG addresses all of these limitations. With SQL Server 2014, AlwaysOn AGs can have up to eight replicas, two of which can be synchronous. It provides the ability to protect multiple-user databases. You can simultaneously implement synchronous replication for high availability and automatic failover, as well as asynchronous replication for disaster recovery.
Secondary replicas can offer optional read-only access. Failover is automatic and takes only a couple of seconds. AlwaysOn AGs work by forwarding transactions from the primary to the secondary replicas. For the SQL Server 2014 Enterprise edition, AlwaysOn AGs can have a maximum of eight replicas. For the SQL Server 2014 Business Intelligence and Standard editions, they are limited to two nodes.
You can combine AlwaysOn FCI and AlwaysOn AG. For example, you might use AlwaysOn FCI to provide local high availability within a data center, and AlwaysOn AG for disaster recovery business requirements.
SQL Server Failover Cluster Instance (FCI)
The entire instance. That includes all server-objects (logins, SQL Server Agent jobs, etc.). This also includes databases and their containing entities. It’s a great solution for highly available SQL Server instances, because that is going to be the level of containment with this given solution.
AlwaysOn Availability Groups
An availability group is going to be the logical containment of high availability here, whereas an availability group consists of a number of databases and a virtual network name (the listener, an optional cluster resource). It is worth noting that server objects such as logins and SQL Server Agent jobs will not be part of the solution, and special consideration needs to be taken to ensure that these are properly implemented with an availability group.
Sources:
Comments