SQL Server AlwaysOn Architecture and Step by Step Installation -2

Hi,

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

 

Read the first article of SQL Server AlwaysOn before this.

After completing the requirements mentioned in the previous article for AlwaysOn installation, we now have 2 Standalone SQL Server Instances which are on 2 different servers. These Standalone SQL Server Instances’ disks, IPs, etc. are all different, but they run as Windows Cluster with Windows Server Failover Clustering service feature as below.

 

 

In order to enable AlwaysOn feature on SQL Server,  both nodes must be a Cluster member. If all the prerequisites are met; This feature can be activated by following the steps below.

Open SQL Server Configuration Manager and Double click on SQL Server (MSSQLSERVER) service to go to the Properties section. AlwaysOn Availability Groups feature is enabled on the AlwaysOn High Availability Tab by clicking.

If Windows Cluster is not installed correctly in this step, you cannot activate this feature. Because SQL Server AlwaysOn does not work without Windows Cluster, just like SQL Server Failover Cluster.

 

After this process, the SQL Server service must be restarted.

 

 

 

When we look at the SQL Server Instance features after Restart, we can see that AlwaysOn feature is active as follows.

 

 

Now we can start the AlwaysOn installation. Connect to SQL Server Instance with SQL Server Management Studio to create AlwaysOn Availability Group in SQL Server 2012, 2014, 2016, 2017, 2019. Right-click on the Availability Groups under AlwaysOn High Availability on Object Explorer to launch the New Availability Group Wizard.

 

Click Next in this step.

 

 

 

 

A name is defined the Availability Group to be created.

 

On the Select Databases page, select the databases that are required to be included in the Availability Group. To be able to join a database in the Availability Group, Full Recovery Mode must be enabled and a full backup must be taken once before the operation.

 

 

 

 

 

 

Take backup as follows.

 

 

 

 

Meets prerequisites. Choose the database and Click the Next button.

 

On the Specify Replicas page, the second server to be syncronized (and the third server, if any) will be added from the Add Replica section by connecting to the other node as follows.

 

 

Specifications are made to both nodes for automatic failover, synchronous commit, readable secondary roles, depending on the scenario. (According to our scenario, each feature is selected, each server is readable secondary.)

Primary and Secondary Nodes have been added and Readable Secondary is chosen by Clicking Yes for every 2 Instance, so our Secondary Instances will Readable. By selecting the Automatic Failover option, we activate Automatic Failover, This feature is valid for up to 2 Instances.

 

 

 

Endpoints tab provides the necessary port settings. Endpoints Enable communication between servers for synchronization of SQL servers included in Availability Groups. Therefore, it is important to note that the port used for the endpoints must be opened on the Firewall.

 

 

In this step, determine where to take Backup, etc. To reduce the load on Primary it is recommended you to take Backups from Secondary.

 

 

 

 

 

If you want to access SQL servers with unique IP and listener name, you must define the Availability Group Listener from the Listener tab. Even if the Listener identification is not performed during installation, it can be defined later by the Add Listener option via the corresponding availability group. This listener is also referred to as the AlwaysOn listener name, which is the same as the Failover Cluster name and IP in the SQL Server Failover Cluster. After the installation, we will access AlwaysOn with this IP and name instead of the local IP.

Before the Listener is created, the Windows Server Failover Cluster object that hosts the availability group must have Create Computer Objects privilege. Otherwise, an error will be received as follows. (create computer objects)

 

 

The Select Data Synchronization step requires specifying a shared location where all replica servers have access. In addition, it should not be ignored that the read / write privileges of SQL Service and Analysis Service accounts must have at this step.

 

 

Click Next and all the controls are done.

 

 

Installation is started.

 

The installation was successfully completed as follows.

 

 

 

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 *