locked
Queries on AlwaysON RRS feed

  • Question

  • Hi:

    I have some queries.

    - Can I failover 1 database out of 'N' databases in an availability group.

    - Is it possible that out of say 5 dbs in an availability group 2 are on server1 and 3 are on server 2, as in the case of mirroring.

    - My client requirement is that, if one database in availability group has issues, all databases should failover to the other server... .....This is the default behavior.. Am I right...?

    Thanks

    Monday, September 1, 2014 5:42 AM

Answers

  • Yet no solution on earth is fully bulletproof...

    For example "full transaction log": If you are mirroring in sync mode, either way, database mirroring or AlwaysON, after a failover the log of the mirror would still be full and maybe even on the principal if you restricted log file growth, for example. There will always be situations no HA technology on earth can deal with.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, September 2, 2014 8:38 PM

All replies

  • Hi there,

    no, you can only fail over the entire group. If you want them to fail over separately, you would have to create separate groups for them. But then again your customer's requirement to fail them over altogether would not be met.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 1, 2014 6:11 AM
  • Thank you for your reply, so If there is an issue with one DB, say Logfile is full, in this case will all the databases in the AG failover..
    Monday, September 1, 2014 6:18 AM
  • No, in this case you would just get the error message that the log file is full.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 1, 2014 6:30 AM
  • By the way, here's some reading abouit failover:

    Failover and Failover Modes


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 1, 2014 6:41 AM
  • Thanks for your post:

    It says :
    Issues at the database level, such as a database becoming suspect due to the loss of a data file, deletion of a database, or corruption of a transaction log, do not cause an availability group to failover.

    Which means unlike Mirroring, if there is issue with one database it won't failover and instead throw error, meaning the site would be down for what ever time period untill we manually failover the availability group or solve the issue.... am i right?.. If this is right, then I guess in my case i have to revert back to mirroring..

    Monday, September 1, 2014 1:26 PM
  • I'm afraid that's correct.

    However, with dbmirroring, failover occurs in the client, that is if the client uses the native client. There are applications that don't support the native client such as some ERP systems. You would have to fail them over manually.

    Some thoughts on that:
    If you need to react on certain database issues, you could still use AlwaysON and create your own jobs that look after your databases. In case that something is going wrong, you could failover the AG using T-SQL in that job.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 1, 2014 1:35 PM
  • O..Then I fear AlwaysOn is not full proof DR solution and Mirroring is getting scrapped in the future editions.. there needs to be something better.
    Monday, September 1, 2014 5:36 PM
  • O..Then I fear AlwaysOn is not full proof DR solution and Mirroring is getting scrapped in the future editions.. there needs to be something better.
    AlwaysOn Availability Group was configured so that all logically linked database can failover together. If your databases are not linked to each other then why don't you created one AG for each database to get same end effect as mirroring?

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, September 2, 2014 5:31 AM
  • Yet no solution on earth is fully bulletproof...

    For example "full transaction log": If you are mirroring in sync mode, either way, database mirroring or AlwaysON, after a failover the log of the mirror would still be full and maybe even on the principal if you restricted log file growth, for example. There will always be situations no HA technology on earth can deal with.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, September 2, 2014 8:38 PM