:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

SQL Server 2012 AlwaysOn Availability Groups

 

The AlwaysOn Availability Groups (AAG) feature is an HA and DR solution that provides an enterprises a substitute to DB mirror. New in MS SQL Server 2012, AlwaysOn Availability Groups make the best use of the availability of a set of user databases for an enterprise. Installing AlwaysOn Availability Groups involves creating and configuring one or more availability groups. Each availability group is a place holder for a distinct set of user databases, known as AlwaysOn availability databases, that allows failover. An AlwaysOn availability group can require multiple possible failover targets (secondary DB copy replicas). Furthermore, you may configure secondary DB replicas to support read-only access to secondary databases and for running backup against the secondary DB. AlwaysOn Availability Groups offers a rich set of choices that increase database availability and that allow improved resource use. The main mechanisms are as follows:

• Supports one primary database replica and allows up to 4 secondary database replicas targets.
• Asynchronous-commit mode. This availability mode is a DR solution that works great when the availability replica copies are distributed with not so stable connection.
• Synchronous-commit mode. This availability mode put emphasis on high availability and allows data protection over performance, con is transaction latency.
• Allows automatic page restoration against page corruption.
• Backupable and Read-only access to the secondary databases
• Fast application failover is provided by availability group listeners.
• The greater failover control is achieved by Flexible failover policy.

 

Database Replicas Basics

Each AlwaysOn availability group designates two or more failover associates known as availability replicas (db copies). Database Availability replicas are components of the availability group that are held by separate instances of SQL Server be present in on different nodes of a WSFC failover cluster. Each server instances is either one a failover cluster instance (FCI) or standalone instance on which you require enabled AlwaysOn Availability Groups. Each availability replica hosts a copy of the AlwaysOn availability databases in the availability group.

Every availability replica copy is assigned apreliminary role which designatesprimary role or the secondary role, which is congenital by the availability databases of that replica. The role of a given replica controls whether it hosts readwrite databases or readonly databases. primary replica, is assigned the primary role and hosts readwrite databases, which is a primary databases. At minimum one extra replica, known as a secondary replica, is assigned the secondary role. A secondary replica hosts read-only databases, known as secondary databases.

To add a database copy to an AlwaysOn availability group, the database must be an online database and read-write database that exists on the server-instance that clouds the primary replica. When you add a database, it joins the AlwaysOn availability group as a primary database, while remaining available to clients. No conforming secondary database exists until backups of the new primary database are repaired to the server instance that hosts the secondary replica (using “RESTORE WITH NORECOVERY”). The new secondary db is in the RESTORING state until it is linked to AlwaysOn availability group. By joining, this places the secondary database into an ONLINE state and starts data syncwith the conforming primary database. Data synchronization is the procedure by which makes alterations to a primary database are copied on a secondary database. Data synchronization contains the primary replica sending transaction log records for the primary database to the secondary replica, this writes the transaction log archives to disk (hardens the log) and spread on the log records to the secondary database.

 

AlwaysOn Availability Modes
The availability mode is a property of each of the availability replica. This regulates whether the primary replica waits to commit transactions on a database until secondary copy write the transaction log records to disk . AlwaysOn Availability Groups allows two modes—asynchronous-commit mode and synchronous-commit mode.

• Asynchronous-commit mode
  asynchronous-commit mode allows 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
  How synchronous-commit mode works is, before committing transactions, a synchronous-commit primary replica delays for a synchronous-commit secondary replica to grant that it has finished hardening the log. Synchronous-commit mode guarantees that once a given secondary database is synchronized with the primary database, committed transactions are fully safe. This safetyfeature down side is that it increases transaction latency.

Various Type of Failovers

Within the environment of a session between the primaryreplica and a secondaryreplica, the primary and secondaryroles are possiblyexchangeable in a process known as failover. Secondary replica transitions to the primary role during this process, and it becomes the new primary replica. The new primaryreplica brings its databases online as the primary databases, and server apps can connect to it. When the previous primary replica is available, it transitions to the secondary role. The earlier primary databases become secondary databases and data synchronization continues. These are different type of failovers, automatic, manual, and forced. The method of failover supported by a arranged secondary replica.

 

Secondary database copies

An AlwaysOn availability database copy can be constructed so that, when carrying out the secondary role, it takes client side connections for read only access to its databases. You can supply clientside connectivity to primary database replica duplicate of a given availability group by using one of followings:

Unique network name
You can generate a unique network name for an AlwaysOn availability group. To the extant primary replica of the AlwaysOn availability group, applications can specify this network name in connection strings. After the AlwaysOn availability group fails over, the network name directs connections to the new primary replica. You must create a network name that is unique in the domain for each AlwaysOn availability group. As you create a network name, a virtual IP (VIP) address is assigned to the network name.

Only TCP is supported for using a network name and, optionally, a F5 VIP or Virtual Address to connect to an AlwaysOn availability group. connection-strings that your applications use to connect to the databases in the AlwaysOn availability group must specify the AlwaysOn availability groupunique network name, not the host name. Applications will join directly to the current primary replica.

Here are the Examples of successful connection strings from MS:

Server=tcp:Mynetwork name;Database=MyDataBase;IntegratedSecurity=SSPI

Server=tcp:Mynetwork name,1433;Database=MyDataBase;IntegratedSecurity=SSPI

 

DB mirror connection strings
If an AlwaysOn availability group owns only two availability replicas and is not designed to allow read-access to the secondary replica, clients can connect to the primary replica using mirroring connection strings. This method can be useful aswhereas migrating an existing application from db mirror to an AlwaysOn availability group, as long as you limit the AlwaysOn availability group to two replica copies. Beforehand, you will want to create a VNN for the AlwaysOn availability group and update your application to use the VNN.

When consuming DB mirror connection strings, the app client can use either SQL Server Native Client or .NET Framework Data Provider for SQL Server to connect to the App DB. The connection string provided by a client App must at least provide the name of one server instance, the initial partner name, to recognize the server instance that originally hosts the availability replica to which you aim to connect. The connection string can also providethe name of another server instance, the failoverpartnername, to identify the server instance that originally hosts the secondary replica copy as the failover partner name.