Friday, February 01, 2013 2:56 PM
We have an SQL 2012 Cluster
According to MSDN (An FCI runs in a WSFC resource group with one or more WSFC nodes. When the FCI starts up, one of the nodes assume ownership of the resource group and brings its SQL Server instance online. The resources owned by this node include: SQL Server Agent service)
The big Question is this ..
Given that I have a Job with multiple Steps.. (1...10)
And a failover occured at step 5. What will happen to the transaction?
Will the back up cluster fail over ..i.e starts from the Step 5.. or do all the Jobs starts afresh?
Thanks in Advance
Friday, February 01, 2013 3:11 PM
The user need to start the job manually, if failover happens while job was executing, the job will be in failed \ aborted state. So the best practise is to include 'send mail' alert or any kind of alert on failure of any of the job step, so that notifier can start the job from failed step.
Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.
Friday, February 01, 2013 3:32 PM
Thanks for your feedback.
are you telling me that Alwayson functionality is a marketing GaG..
I mean as the names implies, the system should be Alwayson.. right..?
Is there any best practices.. to create a workaround.. Eg.. log .. Sql log file with a script or .. something..
I am looking for a way to automate this process. should the unexpected occur. I mean there must be an automated way of doing this..
I have a one crazy service level agreement.. that I need to satisfy
Help.. help .. help
Sunday, February 03, 2013 9:29 AM
AlwaysOn is a marketing umbrella term that covers two features - failover clustering instances (FCIs) and AGs. I assume you have implemented an FCI based on your description. FCIs have worked the same way since 6.5 - an FCI goes through a stop on the old node and a start on the new one during failover. This is just like if you powered down and powered up a standalone. SQL Server will go through recovery (as it should). Any incomplete transactions will be rolled back, etc. That's how it has always worked. So your transaction will not be left in some weird state - if it's incomplete, it will roll back.
As for the Job, no it will not restart at step N after failover. It's never worked like that.
You should never agree to service levels and implement a solution without understanding if it meets your needs. FCIs are tried and true, but sounds like you need to build your own logic to go the rest of the way.
Allan Hirt Blog: http://www.sqlha.com/blog Coming in 2013: Mission Critical SQL Server 2012 - the followup to Pro SQL Server 2008 Failover Clustering