November 22, 2015 at 8:53 pm
We updated our SQL cluster that uses always on to service pack 2. This update was pushed via SCCM. I am now receiving the below error.
availability group is not ready for ready for automatic failover
Any advice is greatly appreciated.
November 23, 2015 at 3:46 am
Is the failover target SYNCHRONIZED
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
November 23, 2015 at 4:54 am
Is it an asynchronous replica?
November 23, 2015 at 4:58 am
what is the current state of data movement?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2015 at 8:44 am
It is 'Synchronous Commit'. Seems like everything is working as expected. It just fails over to DR and particular server, when failing over to DR give the following output:
DATE/TIME:11/23/2015 6:00:05 AM
DESCRIPTION:(None)
COMMENT:(None)
JOB RUN:(None)
NOTE: This didn't start happening until we pushed SP2. Has anyone else seen this before?
November 23, 2015 at 9:08 am
AlwaysOn: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2015-11-22 20:16:37.08 spid18s AlwaysOn: The local replica of availability group 'GroupName' is starting. This is an informational message only. No user action is required.
2015-11-22 20:16:37.09 spid20s Error: 35262, Severity: 17, State: 1.
November 24, 2015 at 1:42 am
NOTE: This didn't start happening until we pushed SP2. Has anyone else seen this before?
Yes- does this apply? http://blogs.msdn.com/b/sqlreleaseservices/archive/2015/01/21/alwayson-availability-groups-may-be-reported-as-not-synchronizing-after-you-apply-sql2012-sp2-cu3-or-sql2012-sp2-cu4-or-sql2014-cu5.aspx
November 24, 2015 at 2:13 pm
SQLSERVER SP2 Issues:
1. Pushed SP2 to All of our 2012 AlwaysOn SQL Clusters via SCCM
2. One cluster failed to start SQLSERVER Services and Agent
a. Error: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.
b. Troubleshooting: Tried repairing the SQL, recived additional errors. After 6 hours of troubleshooting, I restored our VM.
c. Error: Network had to be reconfigured
d. Error: Start SQL SERVER services: Script level upgrade for database 'master' failed because upgrade step SSIS_Hotfix_install.sql' encountered error 945, state 2, severity 25. this is a serious error condition which might interfere with regular operation and the databaase will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.
FIX:
1. Start SQL Server service with Trace Flag 902:
a. Open a CMD prompt
b. Net Start MSSQL$InstanceName /T902
c. Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
d. Open New Query, execute the SSIS_hotfix_install.sql script which can be found inInstall folder under \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName \MSSQL
2. Stop SQL Server services:
a. Net Stop MSSQL$InstanceName
b. Start SQL server service from SQL Server configuration manager
c. Add SSISDB back to Availability Group
NOTE: SQL Server services started, but still saw issues when we tried to fail over. Rebooting DR/PROD fixed this issue.
One Cluster failed to start SQLSERVER Services and AGENT
Error: MSSQLDatamssqlsystemresource.ldf does not match the primary file. It may be from a different database or log may have been rebuilt previously. Database MSSQLsystemrecource cannot be opened due to inaccessible files or insufficient memory or disk space.
FIX: Reset permissions for SQLSERVER Service account on Microsoft SQL Server\Instance\MSSQL\Binn directory to Full Control where myssqlsystemresource.ldf/.mdf files reside
This is a great article and would probably had worked in my environment as well. Thank you Beatrix Kiddo.
we would like to inform you of a problem that we have discovered.
When you apply SQL Server 2012 Service Pack 2 Cumulative Update 3 or SQL Server 2012 Service Pack 2 Cumulative Update 4 or SQL Server 2014 Cumulative Update 5 on instances that have databases participating to AlwaysOn Availability Groups (aka AG), the AG might be reported as NOT SYNCHRONIZING. When this happens, as you query sys.dm_exec_requests, you may find that there is intermittent lock blocking between users sessions and a session whose command is reported as 'DB_STARTUP.' You may also observe blocking between CHECKPOINT and DB_STARTUP.
This KB article gives some additional technical insights.
This is not a systematic problem. You may be able to apply these Cumulative Updates on an AlwaysOn configuration without hitting this problem. If you have already applied these cumulative updates and did not notice this problem its means that your system is not affected and can discard this message.
If you apply these cumulative updates in the future and experience this problem, the recommended approach at this point is to:
1.Disable automatic failover if it is activated
2.Restart the SQL Server instance that is acting as Primary of the availability groups
3.Re-enable automatic failover if it was activated.
We are aware of the implications of this issue and are sorry for the inconvenience this causes to you.
Hotfixes for both SQL Server 2014 and SQL Server 2012 Service Pack 2 are available here.
SQL Server 2012 RTM or Service Pack 1 are not affected by this problem.
The current plan is to roll up these hotfixes respectively into SQL Server 2012 SP2 CU#5 (should ship by end of March 2015) and into SQL Server 2014 RTM CU#6 (should ship by end of February 2015)
November 24, 2015 at 10:08 pm
Verify that at least one secondary replica is configured as automatic failover. If there is not a secondary replica configured as automatic failover, update the configuration of a secondary replica to be the automatic failover target with synchronous commit.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply