locked
Alert me MS SQL database backup to other site RRS feed

  • Question

  • Hi,

    I have a microsoft SQL 2008 R2 Standard database in Site A and want Site B to be the back-up just in case Site A losses power for what ever reason or the server on Site A goes down. What would be the best disaster recovery method for this? I have heard of replication, log shipping and mirroring and am thinking to use mirroring. Would this be the best option? 

    Another scenario, if I have two physical machines each having their own sql database, can I use mirroring?

    Thanks!

    Friday, September 11, 2015 2:34 AM

Answers

  • HI Janus,

    I have a microsoft SQL 2008 R2 Standard database in Site A and want Site B to be the back-up just in case Site A losses power for what ever reason or the server on Site A goes down. What would be the best disaster recovery method for this? I have heard of replication, log shipping and mirroring and am thinking to use mirroring. Would this be the best option? 

      You need to consider few factors while planning a DR depending on your business requirements. There is nothing such thing called ''this is the best''.You need to find the right technology that will suit your needs.

    SLA'S like  Down time to failover, Data Loss  needs to be considered. Looking at your scenario you may need to implement some thing like Clustering depending on your needs, if you want to have to implement DR at SQL Server Instance level. You also need to have Enterprise edition of Windows Server to implement this.

    DB Mirroring is a deprecated feature - so it is not recommended to use this feature in new development work.

    Also see this link  on planning a DR solution

    Another scenario, if I have two physical machines each having their own sql database, can I use mirroring?

    No You can't. Please see the above DB mirroring link to understand what actually DB mirroring is before implementing it.

    Friday, September 11, 2015 4:38 AM
  • You should, as bhanu said, first define what you want to achieve... Do you need automatic failover? Is data loss acceptable? How much of it? etc.

    Mirroring is deprecated, but unfortunately in a Standard Edition setup it might still be an option you need to take into consideration depending on your answers above.

    And regaring your second scenario: Nothing is stopping you from having Database A mirror from Primary site to DR site and at the same time Database B mirror from DR site to Primary. Mirroring strictly works on a database level... So having multiple DBs is not a problem, BUT in the end the Mirror of the DB is just that: a MIRROR. Meaning: The database on the mirror will/has to look EXACTLY like the one on the primary. So what you can not do is mirror some tables from one DB into another...

    Friday, September 11, 2015 8:22 AM
  • I just want to add that starting from 2012 MS was saying Mirroring would be removed from future version but its still there in 2016 so YES you can use it and its supported. Having said that I also want to emphasize that AlwaysON is more advanced feature of mirroring I would suggest you to use it.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Friday, September 11, 2015 9:27 AM

All replies

  • HI Janus,

    I have a microsoft SQL 2008 R2 Standard database in Site A and want Site B to be the back-up just in case Site A losses power for what ever reason or the server on Site A goes down. What would be the best disaster recovery method for this? I have heard of replication, log shipping and mirroring and am thinking to use mirroring. Would this be the best option? 

      You need to consider few factors while planning a DR depending on your business requirements. There is nothing such thing called ''this is the best''.You need to find the right technology that will suit your needs.

    SLA'S like  Down time to failover, Data Loss  needs to be considered. Looking at your scenario you may need to implement some thing like Clustering depending on your needs, if you want to have to implement DR at SQL Server Instance level. You also need to have Enterprise edition of Windows Server to implement this.

    DB Mirroring is a deprecated feature - so it is not recommended to use this feature in new development work.

    Also see this link  on planning a DR solution

    Another scenario, if I have two physical machines each having their own sql database, can I use mirroring?

    No You can't. Please see the above DB mirroring link to understand what actually DB mirroring is before implementing it.

    Friday, September 11, 2015 4:38 AM
  • You should, as bhanu said, first define what you want to achieve... Do you need automatic failover? Is data loss acceptable? How much of it? etc.

    Mirroring is deprecated, but unfortunately in a Standard Edition setup it might still be an option you need to take into consideration depending on your answers above.

    And regaring your second scenario: Nothing is stopping you from having Database A mirror from Primary site to DR site and at the same time Database B mirror from DR site to Primary. Mirroring strictly works on a database level... So having multiple DBs is not a problem, BUT in the end the Mirror of the DB is just that: a MIRROR. Meaning: The database on the mirror will/has to look EXACTLY like the one on the primary. So what you can not do is mirror some tables from one DB into another...

    Friday, September 11, 2015 8:22 AM
  • I just want to add that starting from 2012 MS was saying Mirroring would be removed from future version but its still there in 2016 so YES you can use it and its supported. Having said that I also want to emphasize that AlwaysON is more advanced feature of mirroring I would suggest you to use it.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Friday, September 11, 2015 9:27 AM