Personal Notes of Edwin Eu

Azure SQL 2016 Clustering

In Microsoft SQL Server on 07/14/2017 at 1:50 PM

On my continue IT learning and passion.  I have setup two nodes Microsoft SQL Server 2016 clustering on Azure.

There are many preparations that must be done like:

  • Setup Azure Networking
  • Setup Windows Server 2012 R2
  • Setup Domain Controller
  • Setup Window Server Clustering.
  • Setup Microsoft SQL Server 2016 Clustering
  • Setup SQL Service accounts, sql ports, firewall, etc
  • Setup the storage like
    • msdt
    • quarum
    • SQLDATA1
    • SQLMASTER1
    • SQLTEMPDB1
    • SQLTRANSLOG1
    • SQLBACKUP1

 

 

 

 

 

 

 

 

The Domain Controller computer is called IND-DC01

 

 

 

 

 

 

 

 

 

The two nodes computers are called  INDAXSQLT1N1 and INDAXSQLT1N2 as shown picture below:

 

 

 

 

 

 

 

 

 

Then, I started the Microsoft SQL Server 2016 setup on Node1 and Node2.

Run Setup.exe as administrator.

Agree to license terms

Choose Instance Features

Database Engine Services (SQL Server Replication, Full-Text Search and Data Quality Services are automatically selected)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Uncheck DHCP and enter the virtual IP address for this instance.  You may need to enter the Subnet Mask if this is the very first installation of sql on this box.  Leave the Network name as is.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enter an account and password for each service.  Leave startup type as manual because it is a cluster.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Adding the second nodes

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MS SQL Server AlwaysOn

In Microsoft SQL Server on 01/19/2017 at 11:05 AM

I continued to explorer my skills and knowledge on MS SQL Server 2016 on AlwaysOn.

I have been tested and  setup three (3) nodes clustering that running on VMWare environment on MS Window Server 2012 R2:

  • Node 1 – CRMSQLQ1N2 has installed, setup and configure MS SQL Server 2016.
  • Node 2 – CRMSQLQ1N3 has installed, setup and configure MS SQL Server 2016
  • Node 3 – CRMSQLQ1N4 has installed, setup and configure MS SQL Server 2016

On this example, on node 1 CRMSQLQ1N2  I will create several user databases.Then, turned on the SQL Server AlwaysOn features.

After completed setup, and configure SQL 2016 AlwaysOn.  The CRMSQLQ1N2 node1 should replicate those user databases into Node 2 and 3.

sql2016-alwayson sql2016-alwayson_page5 sql2016-alwayson_page4 sql2016-alwayson_page3 sql2016-alwayson_page2

Cluster resource SQL Server Agent in clustered role ‘ZZZCRMSQLP1VS1’ failed

In Microsoft SQL Server on 12/13/2016 at 8:42 AM

Problems: Cluster resource SQL Server Agent in clustered role ‘ZZZCRMSQLP1VS1’ failed

Resolutions:

  • Requesting a temp local admin right on both cluster nodes
  • RPD into both WIndow Server 2012 cluster nodes
  • Opened the Failover CLuster Manager
  • Trying to bright online MS SQL Agent, but the MS SQL Agent Failed to  run and be online.
  • Then, I opened up SQL Server Configuration Manager and make sure all the SQL Server services are running.  Immediate, I found SQL Server Agent stopped running.  Then, I tried to restart the SQL Agent services but the SQL Agent services failed.
  • Next, I investigated the WIndow Server Cluster event and logs and the cluster event and logs’ showed:

Cluster resource ‘SQL Server Agent (ZZZCRMSQLP1VS1)’ of type ‘SQL Server Agent’ in clustered role ‘ZZZCRMSQLP1VS1’ failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

The Cluster service failed to bring clustered role ‘ZZZCRMSQLP1VS1’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

I recalled that one of DBA team just updated he SQL Agent service account passwords,  could the password updated caused this errors.

So, I requested the SQL Agent service account password from Server team and re-enter the password.  Then, bring the SQL Agent resource online.  Amazing to me that the SQL Agent resources are back online and running.  It’ safe my day!!!

Moving  or Fail-over the SQL Cluster Role from Node 1 to Node 2. And make sure the SQL Server cluster instance and Server Agent resource were online, up and running.

Manually fail-over the SQL Cluster Roles from Node2 to Node1 and make sure the SQL Server cluster instance and resources were online,  up and running.

sqlcluster_issues