miércoles, 23 de septiembre de 2015

ALWAYSON FEATURES

This part describes the new SQL High Availability feature introduced with SQL Server 2012 version: AlwaysOn Availability Group.




ALWAYSON FEATURES

First of all, do not confuse “AlwaysOn Availability Group” and “AlwaysOn FCI”. This is two distinct solutions:

ALWAYSON FCI (FAILOVER CLUSTER INSTANCE)

This is the classic SQL Cluster based on the WSFC (Windows Server Failover Clustering) functionality. FCI provide local high availability through redundancy at the server-instance level (a single instance installed across multiple nodes). In case of failure of a node, the instance will start on another node. Clients connect to this instance from a VNN (Virtual Network Name) which is a Cluster resource.
FCI required Shared Storage (SAN, SMB…) for database data and logs. This storage must be configured on all nodes participating to an FCI. In the FCI solution, Shared Storage is a SPOF (Single Point Of Failure).
SQL Services (Instance) can failover between WSFC nodes (so the loss of a server is covered). If the Storage fails the service stops.
Note:
  • For FCI on the same site it’s possible to eliminate the Storage SPOF with SAN Replication.
  • For FCI across remote sites you need to implement SAN Replication.
Implementing “SAN Replication” Cons:
  • generates additional costs (this is also the case for AAG; FCI is available on the SQL Standard version, AAG required an SQL Enterprise version) except if it’s included in your SAN license.
  • it’s more complicated to implement on remote geographic sites.
  • it’s require additional storage skills for the operation team.
  • the Availability Group offer more options (like asynchronous replica, backup on secondary replica, ..)
The choice of use FCI or AAG depends of your Business requirement (RTO, RPO, DRP, Remote sites …)
For comparison, AlwaysOn Availability Groups doesn’t require Shared Storage.
I will write an article about AlwaysOn FCI with Windows 2012 R2 SOFS Storage (Scaled-Out File Server).

Part1-AlwaysOn-FCI

ALWAYSON AVAILABILITY GROUP

The Availability Group feature is a mix of SQL Clustering and SQL Mirroring (it is also presented by MS as an alternative to SQL Mirroring which is deprecated since SQL Server 2012).
The AG is also based on a WSFC cluster; the difference is that on each node a SQL instance is installed and active.
AAG is composed of replicas (a replica is a group of one or more database). There is one Primary Replica and one to four Secondary Replicas (eight for SQL 2014) (a Secondary Replica is a copy of databases from the Primary Replica, when a DB is modified, changes are replicated on all Secondary replica). An AAG is a WSFC cluster resource group (which contains at least the VNN and VIP cluster resources on which the clients will connect). At time T, the AAG is hosted by an instance, so this is the primary replica (database(s) accessible in R/W) and all other replicas synchronize thereon. There are two types of synchronization: synchronous and asynchronous.
If the instance that hosts the primary replica becomes unavailable the AG (the cluster group resource) will switch to another instance and the secondary replica on this instance will become the Primary replica (this failover requires that the secondary replica is set in “synchronous” mode and the status is “Synchronized”).
Automatically failover cannot be done to an asynchronous secondary replica. In asynchronous mode only “forced manual failover” (with possible Data loss) is allowed, this action must be performed by a DBA. This mode is generally used for a DRP on one or more remote site or if the maximum number of replica in synchronous mode is reaches – Max for SQL 2012/2014: 3 (1x Primary + 2x Secondary)
A Cluster Resource Group (for SQL the AG) can be hosted only by one instance at a time. So, on a given AG only one instance at a time can be “active” (active = which hosts Database(s) accessible in R/W = the Primary Replica).
On all replicas in an Availability Group only one can be “Primary Replica” at a time.
For example, with an AG composed of two nodes and containing two databases, it’s impossible to have one Database active on one node and the second DB active on the second node. To achieve this configuration we need to create two AG (on the same two nodes) with one Database in each AG (so there are two Primary Replica, this is the configuration that I will do in this article).

USE THE BOTH FEATURES

It’s possible to used AlwaysOn FCI in an AlwaysOn Availability Group solution. In this case a SQL Failover Clustering Instance cans host an Availability Replica for Availability Group
Note for Failover Mode: SQL FCIs do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.
The implementation of a FCI in an AG will be cover in another article.
Overview (from TechNet):



LICENSE


High Availability features for SQL Server 2012 Licenses:
Feature Name
Enterprise
Business Intelligence
Standard
Web
Express
Server Core supportYesYesYesYesYes
Log ShippingYesYesYesYes 
Database mirroringYesYes (Safety Full Only)Yes (Safety Full Only)Witness onlyWitness only
Backup compressionYesYesYes  
Alwayson Failover Cluster InstancesYes (Node support: OS maximum)Yes (Node support: 2)Yes (Node support: 2)  
AlwaysOn Availability GroupsYes (up to 4 secondary replicas, including 2 synchronous secondary replicas)    
…     


High Availability features for SQL Server 2014 Licenses:
Feature Name
Enterprise
Business Intelligence
Standard
Web
Express
Server Core supportYesYesYesYesYes
Log ShippingYesYesYesYes 
Database mirroringYesYes (Safety Full Only)Yes (Safety Full Only)Witness onlyWitness only
Backup compressionYesYesYes  
Alwayson Failover Cluster InstancesYes (Node support: Operating system maximumYes (Node support: 2)Yes (Node support: 2)  
AlwaysOn Availability GroupsYes (up to 8 secondary replicas, including 2 synchronous secondary replicas)    
…     

For more information on Features/Licenses:

TERMS AND DEFINITIONS


TermDefinition
availability groupA container for a set of databases, availability databases, that fail over together.
availability databaseA database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).
primary databaseThe read-write copy of an availability database.
secondary databaseA read-only copy of an availability database.
availability replicaAn instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Two types of availability replicas exist: a single primary replica and one to four secondary replicas.
primary replicaThe availability replica that makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica.
secondary replicaAn availability replica that maintains a secondary copy of each availability database, and serves as a potential failover targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.
availability group listenerA server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.


OVERVIEW OF AN AAG

Part1-AlwaysOn-Availability-Group

AVAILABILITY GROUP

An AG consists of:
  • Two or more Availability Replica (max for SQL Server 2012: 5 / max for SQL Server 2014: 9)
  • One (or more) Availability Group listener

AVAILABILITY REPLICA

An Availability Replica contains:
  • A set of Databases (at least one) (there are no maximum for a set; this depends on the load and Server Performances).
          A Database add to an AG is known as Availability Database
Each availability replica is hosted by an instance of SQL Server residing on different nodes of a WSFC cluster (All nodes members of the same Cluster).

ROLE:

An Availability Replica can have the role of:
  • Primary Replica – Database(s) is accessible in Read/Write
  • Secondary Replica – Database(s) is accessible in Read Only (if it’s configured)
On a given AAG, only one availability replica can be “Primary” at a time, others are “Secondary”. Secondary Replica contains a “copy” of databases from Primary (during creation a backup is created from the Primary DB and imported on all secondary).

AVAILABILITY MODE:

An Availability Replica can be configured in mode:
  • Asynchronous-commit mode – Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
  • Synchronous-commit mode – Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

ALLOW READABLE SECONDARY:

When you configure the AAG, you can configure this option (Known as “Active Secondary Replica”):
With this option you can do the following action:

AVAILABILITY GROUP LISTENER

An availability group listener is a Virtual Network Name (VNN) to which clients can connect to access a database (without knowing the name of the instance). Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica. The listener provides fast application failover after an availability group fails over.
A VNN it’s a WSFC cluster resource and consists of:
  • A DNS Name (a VNN)
  • A Port Number
  • An IP Address (a VIP)
The Listener is always owned by the SQL Server instance where the primary replica resides.
Note about Port:
A Listener can share a same port with an Instance (for example default instance port is used: 1433, and the Listener is also configured with the 1433 port) or use a different port.
For more information:
Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)

DATABASE MIRRORING ENDPOINT (SQL SERVER)

Instances participating to an AlwaysOn Availability Group (or a Database Mirroring) use “Database Mirroring Endpoints” to communicate among themselves.
Each Instance must have its own dedicated Endpoint (only one per instance, you cannot create several endpoints for a same instance). After SQL Server Instance installation, the endpoint is not created.
Note: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. “Database Mirroring Endpoint” is a specify endpoint use by AAG or SQL Mirroring for instances communications.
To host an availability replica an instance must have a Database mirroring endpoint (when you create a Replica you have to specify the Endpoint URL of the instance that will host the replica).
The instance uses this endpoint to listen messages from availability replicas hosted by other instances.
Database mirroring endpoints use TCP protocol to send and receive messages between the server instances. The Endpoint URL is composed with the Server FQDN (can be also the Server name, an IPv4 or IPv6 address) and a defined port number. The port number uniquely identifies an instance.
Example: Two instances on one server, you can have two endpoints:
TCP://sql-srv1.ad.corp:5022 => refers to sql-srv1\i1
TCP://sql-srv1.ad.corp:5023 => refers to sql-srv1\i2

AUTHENTICATION MODE

Two types of Authentication are available for database mirroring endpoints:
  • Certificate-based authentication (If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a non-domain account, you must use certificates for endpoint authentication).
    This part is not covered in this article, for more information see:Use Certificates for a Database Mirroring Endpoint (SQL Server)http://technet.microsoft.com/en-us/library/ms191477.aspx
  • Windows Authentication
    • If all instances use the same domain account, no extra configuration is required.
      (Microsoft recommends using the same account)
    • If instances use different domain accounts, the login of each account must be created in master on each of the other server instances, and that login must be granted CONNECT permissions on the endpoint.

DATA ENCRYPTION

By default encryption is configured to “Required” on a DB Mirroring Endpoint. You can change this (not recommended) to “Disabled” or “Supported”
Encryption algorithms:
You can also change encryption algorithms. By default on an AlwaysOn instance encryption is configured with AES algorithm.
TechNet:
For more information, see TechNet article “Choose an Encryption Algorithm“: http://technet.microsoft.com/en-us/library/ms345262.aspx


WSFC (WINDOWS SERVER FAILOVER CLUSTERING)

The AlwaysOn Availability Group (and also the AlwaysOn FCI) feature is based on the WSFC service. All SQL Server nodes participating to an AAG are members of a WSFC Cluster.
On each node an SQL Server 2012/2014 Enterprise Instance must be deployed.
An AAG is register as a cluster Resource Group in the WSFC Cluster (Resource Group name is the AAG Name). The corresponding Listener (*) of AAG is also register as resource in the Resource Group:
  • Resource – AAG-Listener Name (VNN)
  • Resource – AAG-Listener IP (VIP)
(*) By default one Listener per AAG is sufficient, but if need, it’s possible to create additional Listener (only via PowerShell or WSFC Console)
The WSFC Cluster is responsible of Resources monitoring, in comparison with the SQL Mirroring solution there is not SQL Witness, the WSFC Quorum is used.
It is best practice to always have an odd number of quorum votes in a WSFC cluster.

AAG HEATH MONITORING

The WSFC Cluster monitors the health of the AlwaysOn Availability Group (physical and logical cluster resources). Few points about WSFC Monitoring:
  • The active SQL Server instance periodically reports a set of component diagnostics to the WSFC resource group.
  • Issues at the database level, such as a database becoming suspect due to the loss of a data file, deletion of a database, or corruption of a transaction log, do not cause an availability group to failover.

Note: For “Automatic Failover” a Failover Policy is defined and can be customized:

A flexible failover policy provides granular control over the conditions that cause automatic failover for an availability group. By changing the failure conditions that trigger an automatic failover and the frequency of health checks, you can increase or decrease the likelihood of an automatic failover to support your SLA for high availability.
For more Information see TechNet article: Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server): http://technet.microsoft.com/en-us/library/hh710061.aspx

FAILOVER

During a failover, a Secondary Replica becomes the Primary Replica.
There are three Failover Mode:
  • Planned manual failover (without data loss) – DBA action
  • Automatic failover (without data loss) – In case of failure
For these two modes, the Primary and Secondary Replica must be configured in “Synchronous Availability Mode” and the Secondary must be synchronized.
  • Forced manual failover (with possible data loss)
       This is a Disaster Recovery option; it can only be initiated manually.
It is the only form of failover that is possible when:
          – the target secondary replica is not synchronized with the primary replica.
– the target secondary replica is in “Asynchronous availability mode”
Note: All manual failover actions must be done through the SQL Management Studio, PowerShell or Transact-SQL. No action should be done through the WSFC console.

RESTRICTIONS

Availability Replica:
  • Availability replicas must be hosted by different nodes of one WSFC cluster
  • One primary replica and up to four secondary replicas per AAG
  • All of the replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode.
Maximum number of availability groups and availability databases per computer:
  • No Limitation, depends of Server Performances
  • Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine
Database (To be eligible to be added to an availability group):
  • Use the full recovery model
  • Possess at least one full database backup (After setting a database to full recovery mode, a full backup is required to initiate the full-recovery log chain.)
  • Be a read-write database. Read-only databases cannot be added to an availability group.
  • System databases cannot belong to an availability group.
TDE Protected Databases:
If you use transparent data encryption (TDE), the certificate or asymmetric key for creating and decrypting other keys must be the same on every server instance that hosts an availability replica for the availability group. For more information, see Move a TDE Protected Database to Another SQL Server.
You can find here a FAQ about AlwaysOn capabilities: SQL Server 2012: Always On FAQs

TECHNET RESOURCES

AlwaysOn Availability Groups (SQL Server)

Publicado por en
Etiquetas:

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)