Query on SQL Server 2014 AlwaysOn

Question

We have two DB servers with the name of DB1 & DB2. We are planning to implement SQL 2014 AlwaysOn on PROD environment.

I need clarification on below points. 
a. Is it mandatory to build windows clustering to implement SQL 2014 AlwaysOn ?
b. If the answer is Yes for above question, can you tell me how may IP address/or total number of IP's are required to configure AlwaysOn for SQL 2014 (we have two DB servers (DB1: Primary & DB2: Secondary)?
c. Is there any monitoring server/or heartbeat server is required for automatic fail-over (like witness server in DB mirroring).

Please don't share any URL for above questions, we have already done some R&D on SQL Server 2014 and we didn't get accurate answers for above questions.

Thanks in advance.

Answer 1

Answers:

A) Yes.

B) 1 IP per subnet for the CNO, 1 IP per subnet for each unique interface on the node, 1 IP per subnet for the listener.

C) Clustering does this, nothing extra is required from the stand point. In order to have automatic failover, the following must be true:

1. Both replicas are set to synchronous commit and automatic failover

2. The databases must be "synchronized"

3. The cluster must have quorum (clustering does the automatic failover, not SQL)

-Sean

---

The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

Answer 2

One of our team said that we need 4 IP. Can you provide clear clarifications on this

<<

(for the same network subnet ) 2 IPS for DB1 DB2; 1 IP for Windows Cluster; 1 AG listener  - 4--- 
 for multi subnet 2 more-- Cluster and AG listener

>>

And also can you tell me which IP address we have to use in application config file (string connection to connect DB server)

Answer 3

Hi, 

Four IPs means including the  IPs for Two DB Server   , One IP for Cluster ( Which is using for Heart Beat )  and Other one for AG Listerner. 

In addition to that you need to configure a quorum, Whcih can be a fileshare witness quorum on a different server. Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster. 

Please check below article for more information 

AlWays on Availability group and Fileshare witness Quorum , Windows 2012 & SQL Server 2012

You can connect your Application via AG Listener , You have to replace the server in the Connection string with Listener name. 

Thanks

Shabeer

Answer 4

Thanks Shabeer.

Is it mandatory to configure Quorum/or shared drive to configure AlwaysOn in SQL 2014? Is it not possible to put sql server data and log files on local disk?

Our project Solution Architect placed order for 4 TB for disk space (2 TB for DB1 & 2 TB for DB2 server). How we have to allocate this storage for SQL 2014 AlwaysOn and Windows Failover Clustering.

My Core technology is Oracle not SQL Server and I don't have in-depth knowledge. Please don't irritate to answer my basic questions :)  

Answer 5

Hi, 

Quorum is important

A quorum node set is a majority of the voting nodes and witnesses in the WSFC cluster. The overall health and status of a WSFC cluster is determined by a periodic quorum vote. The presence of a quorum means that the cluster is healthy and able to provide node-level fault tolerance.
The absence of a quorum indicates that the cluster is not healthy. Overall WSFC cluster health must be maintained in order to ensure that healthy secondary nodes are available for primary nodes to fail over to. If the quorum vote fails, the WSFC cluster will be set offline as a precautionary measure. This will also cause all SQL Server instances registered with the cluster to be stopped.

It is possible to put all data in same drive but It is a best practice and all of us prefer to have separate disk  ( drive  ) for Data/Log/ and Temp DB. It will make a huge impact in SQL performance  

AlWays on Availability group doesn't require any shared disk as it require only a shared folder ( Min 500MB in Size ) on a different node other than the nodes participating in AG for File Share Witness Quorum 

Thanks

Shabeer

Answer 6

OK got it. quorum is mandatory for healthy cluster.

Can you pls review below disk space allocation and let me know if any changes required

Server name: DB1

Local disk (C drive): 150 GB

Server name: DB2

Local disk (C drive): 150 GB

Server name: app-DB server

Local disk (C drive): 150 GB

SQL Data files (E): 2.5 TB (both DB1 & DB2 data files placed on this drive)

SQL Log files (F): 500 GB (both DB1 & DB2 log files placed on this drive)

DB Backup (G): 1 TB (Only DB1 server backups placed on this drive)

Total quorum capacity is 4 TB and I am splitting this for data, log & backup. This quorum drive should be located on app-DB server

Any changes required?

Answer 7

Hi ,

You misunderstood , You dont need to put data and log on another server. it should be on the same server but separate drives , Only for  File Share Witness For WSFC on another server 

For example see below , disk size you can adjust based on your disk availability 

Server name: DB1

Local disk (C drive): 150 GB -

Local disk (D drive): 2.5 TB  - Data 

Local disk (E drive): 500 GB  - Log Local disk (F drive): 500 GB  - Temp DB (if Possible) 

Server name: DB2

Local disk (C drive): 150 GB

Local disk (D drive): 2.5 TB  - Data 

Local disk (E drive): 500 GB  - Log 

Local disk (F drive): 500 GB  - Temp DB (if Possible) 

Server name: app-DB server

Local disk (C drive): 150 GB

DB Backup (D): 1 TB (Only DB1 server backups placed on this drive)

Create Share Folder here for File Share Witness  e.g : D:\FileShareWitness

Answer 8

OK now it's clear.

Is it recommended to take DB backups on DB2 server as well ?

Have you faced any issues during implementation (like windows clustering, prerequisites, OR any other) and after implementation (DB log file growing issues, DB connectivity issue OR any other)?

Answer 9

Just I want to know what are the pre-cautions I have to take before implementation because it's PROD environment.

Answer 10

Hi, 

Read below article  

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)

The database transaction log file increasing for the DBs Participating In AlwaysOn Availability Groups

Thanks

Shabeer 

Answer 11

Is it recommended to take DB backups on DB2 server as well ?

Answer 12

 There are three options on AG backups ,  there is nothing that prevents you from running supported backup types on any of the replicas.

Prefer Secondary - Will backup on secondary with highest priority. If no secondary's are available, it will backup on primary.
Secondary Only - Will backup on secondary with highest priority. If no secondary's are available, no backups will occur.
Primary - Backups will occur on the primary only, whichever instance that happens to be at the time of the backup.
Any Replica - Looks just at the backup priority and backups on the replica with the highest priority.

Answer 13

we are using veritas netbackup tool for DB backups. Do we include DB1 & DB2 (both servers) in netbackup policy? OR DB1 is sufficient? Is MS recommended to take primary & secondary DB backups in AlwaysOn environment? 

Answer 14

Hi, 

No need to add both servers on backup. Based on your backup preference you can configure backup on preferred server. To reduce the workload on the primary it is better to add backup on the secondary replica. 

Assume DB1 as primary and DB2 as secondary ,  then you need to configure backup on DB2

Thanks

Shabeer