locked
SQL Server 2012 Alwayson Forced failed over Failure

    질문

  •  

    Hi,

    We are currently setting up an environment to evaluate SQL server 2012 and in particular the alwayson feature. The set up we have is as follows:

    2 X Windows server 2008 r2 sp1 (vm's)

    Both are up to date with windows patching

    Both have .net 4 on.

    A cluster has been set up to include both nodes of which can manually be failed over.

    A shared folder has been created on server 1 of which both nodes have full access to.

    SQL 2012 has been installed on both - running under the same service account

    The same account which is running the service have also been added into the database with SA permissions.

    The same account has been added to the local admin group on both servers.

    Alwayson has been set up and an avaliability group created to include both nodes and one database called db1

     

    Once we had set all this up we then started to carry out some testing around failover. The failover could occur if we initiated it manually as expected however if I was to stop the SQL service running on one server the failover failed and left the other server in a "Resolving" state within SQL and from the windows cluster manager screen the cluster had failed and been taken offline.

    Has anyone else had this issue? Im assuming I have either misconfigured something or missed a step out as it seems quite an obvious test.

    Any suggestions would be greatly appreciated.

    Regards

    Andy

    2012년 2월 28일 화요일 오후 5:09

답변

  • Hi Sateesh,

    The databases were in sync mode and after trying some of the different configuration options I was still struggling to find the cause. I did find however that I could get it to fail over the once but any subsequent attempts to failover would not be successful.

    As I was only testing the function I only had one primary and one secondary and so decided to add a file share into the cluster to ensure that the votes were an odd number however this had no effect on the failure.

    Due to time constraints in the end I had to strip the databases off as well as destroying the cluster and rebuilt it (following the same steps as I did first time round). After this all seemed to be well and so far the simple tests I have carried out have passed. Over the next couple of weeks we will be testing the functionality vigorously to see if it is suitable for production.

    Thank you all for your suggestions and am sorry for not finding the root cause.

    Thanks

    Andy

    • 답변으로 표시됨 AndyN98 2012년 3월 7일 수요일 오전 11:44
    2012년 3월 7일 수요일 오전 11:44

모든 응답

  • Make sure you are in synchronous mode with your replica.  Asynch replicas will not fail over automatically.

    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    2012년 3월 3일 토요일 오전 12:05
  • Are you using sync or async mode? The Availability group can only failover when the databases in the availability group are synchronized (for sync mode) and synchronizing (for async mode)
    2012년 3월 6일 화요일 오후 8:03
  • Hi Sateesh,

    The databases were in sync mode and after trying some of the different configuration options I was still struggling to find the cause. I did find however that I could get it to fail over the once but any subsequent attempts to failover would not be successful.

    As I was only testing the function I only had one primary and one secondary and so decided to add a file share into the cluster to ensure that the votes were an odd number however this had no effect on the failure.

    Due to time constraints in the end I had to strip the databases off as well as destroying the cluster and rebuilt it (following the same steps as I did first time round). After this all seemed to be well and so far the simple tests I have carried out have passed. Over the next couple of weeks we will be testing the functionality vigorously to see if it is suitable for production.

    Thank you all for your suggestions and am sorry for not finding the root cause.

    Thanks

    Andy

    • 답변으로 표시됨 AndyN98 2012년 3월 7일 수요일 오전 11:44
    2012년 3월 7일 수요일 오전 11:44