Skip to content

Tag: AlwaysOn

SQL Server AlwaysOn 1

Availability Group Listener: Benefits and Configurations

What is Listener?

An Availability Group Listener is a Virtual Network Name (VNN). Clients can connect to this VNN to access the database in a primary replica. They can also connect to a secondary replica of an Always on Availability Group.
Listener allows application to connect to the replica without knowing physical name of instance of SQL server.

Only TCP protocol is supported by availability group listener

When an availability group failover occurs, the listener terminates its connection with the affected instance. It then establishes a connection with the new primary instance. Because of this we do not need to make any changes on application side after failover.

Read only routing can be set up for one or more readable secondary replicas. In this setup, read-intent client connections to the listener are redirected automatically. They are directed to a readable secondary replica.

Listener Parameters

A) A unique DNS Name

This is also known as Virtual Network Name(VNN).
Active Directory naming rules for DNS host name are applied here.

B) Virtual IP Addresses

Listener can have one or more virtual IP addresses.
These IP addresses are configured for one or more subnets to which the availability group can failover.

C) IP Address Configuration

We can use dynamic IP address but as per recommendations you should use static IP address.
If the Availability groups that extends to multiple subnets must use Static IP addresses.

D) Listener Port

If you use default port 1433 for listener you can directly connect to AG using listener name.
If you are using a port other than 1433, you must mention the port number. You need to include it with the listener name explicitly in the connection string.

You can use below views to perform checks on availability group listener.
1. sys.availability_group_listener_ip_addresses
2. sys.availability_group_listeners
3. sys.dm_tcp_listener_states

Check all related articles

By Tushar Kothawade Posted on

SQL Server Availability groups 2

SQL Server Availability Group in AlwaysOn

In earlier article we learn about the “requirements/ prerequisite for SQL Server AlwaysOn Configuration”. Now lets see about the SQL Server Availability Group.

SQL Server Availability Group is an alternative to database mirroring.

In SQL Server, Availability Group is a collection or group of user databases which failover together. Databases participated in this are called as Availability Databases. In mirroring, replicas are limited to principle and mirror database. However, in availability group, it supports read-write primary databases. It also supports multiple secondary databases which are accessible for read-only operations.

Database failover is not related to individual database issue such as database file or transaction log file corruption. It is related to the issues at a SQL server instance level. Failover occurs on per replica basis and all the databases in the SQL server Availability group replica fail over.

Availability Modes

Availability Modes of SQL Server Availability Group depends on data loss and transaction latency requirements. There are two types of availability modes.

  1. Asynchronous-commit mode
  2. Synchronous-commit mode

Asynchronous-commit mode

In your SQL Server Availability Group, if replicas are placed at geographically dispersed locations, you must use Asynchronous-commit mode. When you configure a secondary replica with Asynchronous-commit mode, the primary does not wait for the secondaries to write log records to disk. It will run with minimum transaction latency.

If you configure the primary replica with Asynchronous-commit mode, then the transactions for all replicas will be committed asynchronously. This happens irrespective of the mode of each secondary replica.

Synchronous-commit Mode

In Synchronous-commit mode transaction latency is more, but it minimizes the chance of data loss when automatic failover happens. Each transaction is applied to secondary before being written to the local log file. The primary database always verifies that the transaction has been applied to the secondary. After that, it enters into the Synchronized state.

SQL Server Availability Groups Properties

Failover Modes

As we already discussed, in SQL Server Availability groups failover at availability replica level. When failover happens one of the secondary replica becomes a primary and original primary replica becomes secondary replica. SQL Server Availability groups supports three types of failover modes.

  1.  Automatic failover
  2. Planned manual failover
  3. Forced manual failover

Automatic Failover

Automatic failover occurs without manual intervention. There will be no data loss occurs during this type of failover. It is supported only if the current primary and at least one secondary replica are configured with automatic failover.

The most important point is automatic failover can occur only if the primary and secondary replica are in synchronous-commit mode.

Planned manual Failover

In planned manual failover, failover is triggered by the administrator. This type of failover is used in case of maintenance activities. There will be no data loss in this type of failover. To perform planned manual failover at least one of the secondary need to be in Synchronized state.

You can perform planned manual failover only if the primary and secondary replicas are in synchronous-commit mode.

Forced Manual failover

Forced Manual failover involves the possibility of data loss. Use this type of failover when none of the secondary replicas are in a synchronized state. You should also use it when the primary replica is unavailable.

If asynchronous-commit mode is used on the primary, then forced manual failover is used. The same applies if the only available replica uses asynchronous-commit mode.

Check all related articles

By Tushar Kothawade Posted on

Disaster Recovery 2

Configure AlwaysOn: Windows & SQL Server Requirements

Nowadays, most suitable high availability and disaster recovery solution is AlwaysOn. It is a mixture of SQL Server Failover Cluster Instance and Database Mirroring. Here we are going to see requirements to configure AlwaysOn.

Requirements from Windows server side to Configure AlwaysOn:

  1. Ensure that the server is not a Domain Controller. Availability Group is not supported on Domain Controllers.
  2. Also make sure that each server participating in this configuration is running on Windows server 2012 or Later.
  3. Each server has to be the part of the Windows Server Failover Cluster (WSFC).
  4. For best performance use separate network adapter for AlwaysOn availability group.
  5. All the nodes must have sufficient disk space. As the primary database grows, their corresponding replica also grows by the same amount.
  6. To manage the Windows Server Faiolver Cluster, the user must have administrator access on each node of the cluster.
  7. The instances of SQL Server that hosts replicas for an Availability group resides on separate nodes of the same cluster.
  8. In all the nodes you need to use same SQL Server Service Account.
  9. You need to register the Service Principal Name (SPN) with Active Directory (AD). Do this on the SQL Server Service Account. This is required for the Virtual Network Name (VNN) of the availability group listener.
  10. Keep in mind that, if you change the SQL Server Service Account, you need to manually re-register the SPN.

Requirements from SQL Server side:

  1. All nodes of cluster need to have the same version of SQL Server.
  2. Also, you must use same SQL Server Collation for all the instances.
  3. Enable AlwaysOn feature on each server instance.
  4. Availability replicas must be hosted on different nodes of one Windows Server Failover Cluster (WSFC).
  5. Availability group name must be unique and length should not be more than 128 characters.
  6. Each Availability group supports one primary replica and up to 8 secondary replicas.
  7. Up to 3 replicas will run on Synchronous-Commit mode. This includes 1 primary and 2 secondary. Other replicas will run on Asynchronous-Commit mode.
  8. Do not use the Failover Cluster Manager to fail over availability groups. You need to use the SQL server management studio to do it.

Note:

  1. We can’t add System databases in availability group.
  2. We can’t add read only databases to Availability group.

To know more about the SQL server availability groups in AlwaysOn Configuration, please check below article.

SQL Server Availability Groups in AlwaysOn Configuration

Patching of SQL servers in Availability Group

Check all related articles

By Tushar Kothawade Posted on