My computer has the below spec
Intel(R) Core (TM) i7-3612QM CPU @2.10GHZ (8 CPUs)
Memory : 8192MB RAM
Harddisk : 1 TB
OS : Windows 8 Pro 64-bit
My Virtual server setting
Domain Controller (DC) - 1 Virtual Processor, RAM 512, Virtual harddisk - size 127gb (It will expand when disk needed); I DC file size (3 gb)
SQL-A - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
SQL-B - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
SQL-C - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
SQL-D - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
Most of the time I only turn On DC, SQL-A, SQL-B and it more then enough for Testing.
Sunday, August 04, 2013
Saturday, August 03, 2013
Quorum - Node and File Share Majority; Shutdown one node
Cluster service, Change Quorum to Node and File Share Majority; Shutdown one node
SQL-B is Primary Replicas
Shutdown SQL-B server, simulate Primary replicas SQL-B fail and see if the database will auto-failover to SQL-A
Cluster service - SQL-ALWAYSONCL.Contso.com still available with warning
SQL-B Node is down
Failover database Happen and SQL-A become Primary Replicas and Availability Database and Availability Group Listeners still can be access
SQL Availability Dashboard
- SQL-A Replicas is online
- SQL-B Replicas is offline
SQL Error log
Availability replica
'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'; to 'PRIMARY_PENDING'; to 'PRIMARY_NORMAL'
Availability database
'SECONDARY' to 'RESOLVING'; Changed to 'PRIMARY'
Start SQL-B server
SQL-B node is back online
Current Host server still SQL-A and failover didn't happen
SQL Server
SQL Availability Dashboard
SQL Error Log (SQL-A)
SQL-B is Primary Replicas
Shutdown SQL-B server, simulate Primary replicas SQL-B fail and see if the database will auto-failover to SQL-A
Cluster service - SQL-ALWAYSONCL.Contso.com still available with warning
SQL-B Node is down
Failover database Happen and SQL-A become Primary Replicas and Availability Database and Availability Group Listeners still can be access
SQL Availability Dashboard
- SQL-A Replicas is online
- SQL-B Replicas is offline
SQL Error log
Availability replica
'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'; to 'PRIMARY_PENDING'; to 'PRIMARY_NORMAL'
Availability database
'SECONDARY' to 'RESOLVING'; Changed to 'PRIMARY'
Start SQL-B server
SQL-B node is back online
Current Host server still SQL-A and failover didn't happen
SQL Server
SQL Availability Dashboard
SQL Error Log (SQL-A)
Change Quorum to Node and File Share Majority
70-462 Cluster service, Change Quorum to Node and File Share Majority
Can sustain failure of 1 node(s) if the file share witness remains available.
Can sustain failure of 0 Node(s) if the file share witness becomes unavailable
Compare to Default Quorum - Node Majority (not recommended for your current number of nodes
can sustain failures of 0 node(s)
Friday, August 02, 2013
2 nodes with Default Quorum (Node Majority) setup; Shutdown Secondary node
Cluster service, 2 nodes with Default Quorum (Node Majority) setup;
Shutdown server node with Secondary Replicas host (SQL-A); What will happen to SQLServer?
Shutdown Server node SQL-A where Secondary Replica sit.
After SQL-A server node shutdown,
- SQL-B SQLServer availability database testing in "Recovery Pending"
- AlwaysOn High Availability groups - ALWAYSONAG in "Resolving"
- Availability Replicas, SQL-B in "Resolving" mode
- Availability Group Listeners is missing
Availability Dashboard
SQL Error log
why this happen ? We expect the Sqlserver (SQL-B) for Primary should not affected if Secondary server down (SQL-A).
System Error log (SQL-B)
Remember in the cluster quorum configuration, we use default quorum with node majority whereby cluster service will sustain with 0 node down?
When on node down, cluster service can't sustain and SQL Availability database is down as well
2 nodes with Default Quorum (Node Majority) setup; Cluster service will sustain with 0 node down
Restart SQL-A server
SQLServer up
SQL Error Log
Shutdown server node with Secondary Replicas host (SQL-A); What will happen to SQLServer?
Shutdown Server node SQL-A where Secondary Replica sit.
After SQL-A server node shutdown,
- SQL-B SQLServer availability database testing in "Recovery Pending"
- AlwaysOn High Availability groups - ALWAYSONAG in "Resolving"
- Availability Replicas, SQL-B in "Resolving" mode
- Availability Group Listeners is missing
Availability Dashboard
SQL Error log
why this happen ? We expect the Sqlserver (SQL-B) for Primary should not affected if Secondary server down (SQL-A).
System Error log (SQL-B)
Remember in the cluster quorum configuration, we use default quorum with node majority whereby cluster service will sustain with 0 node down?
When on node down, cluster service can't sustain and SQL Availability database is down as well
2 nodes with Default Quorum (Node Majority) setup; Cluster service will sustain with 0 node down
Restart SQL-A server
SQLServer up
SQL Error Log
Thursday, August 01, 2013
Shutdown Primary sqlserver service; 2 nodes with Default Quorum (Node Majority) setup;
SQL Cluster : 2 nodes with Default Quorum (Node Majority) setup;
Testing with Shutdown Primary sqlserver service and verify if the database failover to secondary Availability Replicas and secondary availability promote to Primary Replicas
Availability Group Properties
Synchronous commit , Failover mode = Automatics
SQL-A primary, SQL-B secondary
Shutdown SQL-A, SQLService to simulate Primary SQLServer (SQL-A) fail and see if the database will auto-failover to SQL-B
SQLServer SQL-B become Primary Replicas; Auto-Failover is happen when SQL-A fail.
From Replicas, we can see SQL-A become secondary and down
SQL AG (alwaysonag)Dashboard
SQL-B error log
SQL-B:
Availability Replica status : SECONDARY_NORMAL -- RESOLVING_NORMAL -- PRIMARY_PENDING -- PRIMARY_NORMAL
Availability Group database status : SECONDARY - RESOLVING --PRIMARY
Bring back SQLService - SQL-A
SQL-B still act as Primary Availability Replicas
SQL-A is back online and show as Secondary Availability Replicas
SQL-B : SQL Error log
Testing with Shutdown Primary sqlserver service and verify if the database failover to secondary Availability Replicas and secondary availability promote to Primary Replicas
Availability Group Properties
Synchronous commit , Failover mode = Automatics
SQL-A primary, SQL-B secondary
Shutdown SQL-A, SQLService to simulate Primary SQLServer (SQL-A) fail and see if the database will auto-failover to SQL-B
SQLServer SQL-B become Primary Replicas; Auto-Failover is happen when SQL-A fail.
From Replicas, we can see SQL-A become secondary and down
SQL AG (alwaysonag)Dashboard
SQL-B error log
SQL-B:
Availability Replica status : SECONDARY_NORMAL -- RESOLVING_NORMAL -- PRIMARY_PENDING -- PRIMARY_NORMAL
Availability Group database status : SECONDARY - RESOLVING --PRIMARY
Bring back SQLService - SQL-A
SQL-B still act as Primary Availability Replicas
SQL-A is back online and show as Secondary Availability Replicas
SQL-B : SQL Error log
Shutdown secondary sqlserver service; 2 nodes with Default Quorum (Node Majority) setup;
Cluster service, 2 nodes (SQL-A,SQL-B) with default Quorum (Node Majority) setup;
SQL Server AG group (alwaysonag) with 2 node (SQL-A, SQL-B)
Test shutdown secondary sqlserver service (SQL-B), will it affect primary replicas (SQL-A)
Shutdown SQL-B, SQLService to simulate SQL-B SQLServer fail
From SQL-A, sql-error log:
- A connection timeout has occurred on a previously established connection to availability replica 'SQL-B' with 'DAF385F5-FE27-4A48-A5DC-7E21214498C7].
Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role
- AlwaysOn Availability Groups connection with secondary database terminated for primary database 'testing' on the availability replica with Replica ID : {daf385f5-fe27-4a48-a5dc-7e21214498c7}
SQL-A database still ONLINE, from
AlwaysOn High Availability --Availability Group -- AlwaysONAG -- Availability Replicas
we can see SQL-B replicas is down
SQL AG (alwaysonag) Dashboard
From Availability group state -- Critical --- Critical (1), Warning (3)
Some synchronous replicas are not synchronized
In this availability group, at least one synchronous replica is not currently synchronized. The replica synchronization state could be either SYNCHONIZING or NOT SYNCHRONIZING
Some availability replicas are not synchronizing data
In this availability group, at least one secondary replica has a NOT SYNCHRONIZING synchronization state and is not receiving data from the primary replica
Some availability replicas are disconnected
In this availability group, at least one secondary replica is not connected to the primary replica. The connected state is DISCONNECTED
Availability group is not ready for automatic failover
The availability group is not ready for automatic failover. The primary replica and a secondary replica are configured for automatic failover. however, the secondary replica is not ready for an automatic failover. Possibly the secondary replica is unavailable, or its data synchronization state is currently not in the SYNCHRONIZED synchronization state.
From Availability replica -- Critical (1), Warnings (1)
Start SQLService --SQL-B
From SQL-A, sql-error log:
A connection for availability group 'ALWAYSONAG' from availability replica 'SQL-A' with id [C8B53A5D-75CE-40CC-BB27-50CFBA48CB32] to 'SQL-B' with id [DAF385F5-FE27-4A48-A5DC-7E21214498C7] has been successfully established. This is an informational message only. No user action is required.
AlwaysOn Availability Groups connection with secondary database established for primary database 'testag' on the availability replica with Replica ID :{DAF385F5-FE27-4A48-A5DC-7E21214498C7}. This is an informational message only. No user action is required
AlwaysON Availability Groups connection with secondary database established for primary database 'testag' on the availability replica with Replica ID :{DAF385F5-FE27-4A48-A5DC-7E21214498C7}. This is an informational message only. No user action is required
SQL-B back online in availability Replicas
SQL AG (alwaysonag) dashboard
SQL Server AG group (alwaysonag) with 2 node (SQL-A, SQL-B)
Test shutdown secondary sqlserver service (SQL-B), will it affect primary replicas (SQL-A)
Shutdown SQL-B, SQLService to simulate SQL-B SQLServer fail
From SQL-A, sql-error log:
- A connection timeout has occurred on a previously established connection to availability replica 'SQL-B' with 'DAF385F5-FE27-4A48-A5DC-7E21214498C7].
Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role
- AlwaysOn Availability Groups connection with secondary database terminated for primary database 'testing' on the availability replica with Replica ID : {daf385f5-fe27-4a48-a5dc-7e21214498c7}
SQL-A database still ONLINE, from
AlwaysOn High Availability --Availability Group -- AlwaysONAG -- Availability Replicas
we can see SQL-B replicas is down
SQL AG (alwaysonag) Dashboard
From Availability group state -- Critical --- Critical (1), Warning (3)
Some synchronous replicas are not synchronized
In this availability group, at least one synchronous replica is not currently synchronized. The replica synchronization state could be either SYNCHONIZING or NOT SYNCHRONIZING
Some availability replicas are not synchronizing data
In this availability group, at least one secondary replica has a NOT SYNCHRONIZING synchronization state and is not receiving data from the primary replica
Some availability replicas are disconnected
In this availability group, at least one secondary replica is not connected to the primary replica. The connected state is DISCONNECTED
Availability group is not ready for automatic failover
The availability group is not ready for automatic failover. The primary replica and a secondary replica are configured for automatic failover. however, the secondary replica is not ready for an automatic failover. Possibly the secondary replica is unavailable, or its data synchronization state is currently not in the SYNCHRONIZED synchronization state.
From Availability replica -- Critical (1), Warnings (1)
Start SQLService --SQL-B
From SQL-A, sql-error log:
A connection for availability group 'ALWAYSONAG' from availability replica 'SQL-A' with id [C8B53A5D-75CE-40CC-BB27-50CFBA48CB32] to 'SQL-B' with id [DAF385F5-FE27-4A48-A5DC-7E21214498C7] has been successfully established. This is an informational message only. No user action is required.
AlwaysOn Availability Groups connection with secondary database established for primary database 'testag' on the availability replica with Replica ID :{DAF385F5-FE27-4A48-A5DC-7E21214498C7}. This is an informational message only. No user action is required
AlwaysON Availability Groups connection with secondary database established for primary database 'testag' on the availability replica with Replica ID :{DAF385F5-FE27-4A48-A5DC-7E21214498C7}. This is an informational message only. No user action is required
SQL-B back online in availability Replicas
SQL AG (alwaysonag) dashboard
2 nodes with Default Quorum (Node Majority) setup; Cluster service will sustain with 0 node down
Test cluster service, 2 node with Default Quorum (Node Majority);
What will happen if one of the node down;
More information about quorum
WSFC Quorum Modes and Voting Configuration (SQL Server)http://msdn.microsoft.com/en-us/library/hh270280.aspx#QuorumModes
With Default Quorum (Node Majority) and 2 nodes cluster
Quorum Configuration warning
Node Majority - Warning: Failure of a node will cause the cluster to fail. Check the status of the nodes
Shutdown SQL-D, simulate SQL-D server fail
System event log at SQL-C
SQL Cluster is missing from Failover Cluster Manager
Bring SQL-D back online
Cluster service - Clustertest.Contso.com back online.
Cluster service will offline, if other node down.
Custer service will sustain with 0 node down.
What will happen if one of the node down;
Create a cluster – clustertest.contso.com with 2 node :
SQL-C and SQL-D
Default Quorum : Node Majority
More information about quorum
WSFC Quorum Modes and Voting Configuration (SQL Server)http://msdn.microsoft.com/en-us/library/hh270280.aspx#QuorumModes
With Default Quorum (Node Majority) and 2 nodes cluster
Quorum Configuration warning
Node Majority - Warning: Failure of a node will cause the cluster to fail. Check the status of the nodes
Shutdown SQL-D, simulate SQL-D server fail
System event log at SQL-C
SQL Cluster is missing from Failover Cluster Manager
Bring SQL-D back online
Cluster service - Clustertest.Contso.com back online.
Cluster service will offline, if other node down.
Custer service will sustain with 0 node down.
Subscribe to:
Posts (Atom)