SQL Server AlwaysOn Architecture and Step by Step Installation -1

Hi,

I will explain What is the SQL Server AlwaysOn Architecture and Step by Step Installation in this article series.

 

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.

 

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

 

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 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.

 

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.

 

 

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.

 

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

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *