SQL Server AlwaysOn ( Availability Group ) Architecture and Step by Step Installation -1

I will explain What is the SQL Server AlwaysOn Architecture and Step by Step Installation in this article series. Let’s review the SQL Server AlwaysOn ( Availability Group ) Architecture and Installation

 

 

SQL Server AlwaysOn Step by Step

 

Microsoft introduced the AlwaysOn Availability Groups feature with SQL Server 2012 release. This feature is actually a combination of SQL Server features that we know previously Log Shipping, Failover Clustering, and Mirroring features.

 

 

 

If you don’t know what is the Log shipping, You can read the following Log shipping articles.

SQL Server Log Shipping & Disaster Recovery Install and Configuration -4

 

If you don’t know what is the Failover Clustering, you can read following Failover Clustering articles.

SQL Server Failover Cluster Installation -4

 

Disaster Recovery was provided with Log Shipping but Real-time synchronization does not exist with Log Shipping.  Mirroring has Real-time synchronization but there was no Read-Only option, and Witness Server required for Automatic Failover.

Application has access to 2 nodes via Failover Cluster name same time with Failover Clustering , but the failover cluster was only a High availability solution.

Microsoft has combined all of the advantages Log Shipping, Mirroring and Failover Cluster in the SQL Server Always On.

 

 

SQL Server Availability Group

SQL Server AlwaysOn Architecture is as follows.

 

 

 

 

AlwaysOn is a new SQL Server solution that provides both High Availability and Disaster Recovery between 2 Servers running on Windows Server Failover Cluster (WSFC) installed on at least 2 servers.

 

AlwaysOn provides High Availability because If Primary Node has been down at T time, Secondary node will be Primary via Automatic Failover.

AlwaysOn provides Disaster Recovery because When Storage or Motherboard ( or other part of Server ) of Primary Server has failed , you can recover database with Automatic Failover. Because database physically exists at Secondary node and syncing from Primary database.

You can also use Secondary Node for Reporting and Backup purposes.

 

In short, if you are considering High Availability and Disaster Recovery together for your SQL Server databases running in your Company, the only solution is AlwaysOn.

 

Create Availability Group

AlwaysOn Availability Group: It is a structure that releases with SQL Server 2012 that can be used as an alternative to database mirroring, Log shipping and Failover Clustering. With the AlwaysOn Availability Group, changes in a database on a server are synchronized to a different server. The advantages of this structure over mirroring are that more than one secondary server can be used Actively. In addition, while writing operations and other DML operations are performed on the primary server, Backup and Reporting operations can be done on the Secondary server.

Requirements for AlwaysOn Setup are as follows.

 

 

 

AlwaysOn Availability Group Create

For the SQL Server AlwaysOn Availability Group method to be applied to the database, there must be two or more servers with the same properties, in which the Windows Server Failover Cluster structure is configured as follows. In addition, the version must be at least SQL Server 2012 Enterprise Edition.

 

 

To enable AlwaysOn on SQL Server 2012 + Above, both nodes must be a Cluster member as follows.

 

 

The necessary definitions for access between the first node to be configured and the second node via ports 1433 and 445 must be done.

In addition, File share definitions must be done on a folder that must be defined during the availability group configuration and the initial backups of the databases to be included in the availability group will be taken. (Normally, it is sufficient to give the SQL Server and SQL Agent accounts of both servers read / write privileges on the corresponding folder. ) However, if the SQL Server account on the production server is the ‘LOCAL SYSTEM’ user, the authorization to be granted on the shared folder must be ‘everyone – read / write’ Permission.

 

 

The user who will configure the SQL Server on the source server must has Administrator on Windows and sysadmin on SQL Server Permission.

Since the disks of the servers on which AlwaysOn Availability Group will be applied are seperate and independent of each other, the folders to be used for the data and log files of the databases to be located on the servers must be created with Same name and Same paths.

You should install StandAlone SQL Server for AlwaysOn. You can use the following article to install StandAlone SQL Server Instance.

SQL Server 2017 Step by Step Installation -2

 

I will continue to explain AlwaysOn Installation in the Next Article.

 

You can access the Next posts related with SQL Server Always On Installation with the following link.

 

https://ittutorial.org/sql-server-alwayson-architecture-and-step-by-step-installation-2/

 

 

 

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then Click and read the articles.