Personal Notes of Edwin Eu

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

SQL Server 2012 AlwaysOn

In Microsoft SQL Server on 10/11/2014 at 2:16 PM

Starting Microsoft SQL Server 2012 version, the SQL Server clustering is called SQL Server 2012 AlwaysOn.

Recently, I have setup, configured, and installed SQL Server 2012 Clustering in VMware environment.

These are five nodes clustering  that consisted of:

SQL-Node1 

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node2

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node3

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node4

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN  +  Public LAN

SQL-Node5

  • O/S                        : Window Server 2012 R2
  • RAM                      : 8 GM
  • 2 Network Cards  : Private LAN + Public LAN

Next, configured and setup the Microsoft Failover Cluster as show below:

AlwaysOn5Nodes_Setup1

 

 

 

 

 

 

Run the Cluster Failover test and it should completed as showed below;

AlwaysOn5Nodes_Setup2