none
Best SQL backup strategy

    Question

  • I've been working with a customer on a project that started small, and has grown significantly.   It is a retail environment, and we are taking live transaction data from the POS system, and loading into SQL server for reporting and online presentation.  My experience has been mostly with smaller SQL dbs, nothing involving multiple servers.

    The SQL database file is started at less than 6GB, and is now ~25GB  6 months later, and will only grow.  They currently have a single SQL 2012 Server, with nightly full backups to a NAS.

    They bought a second physical server to be a standby / DR back up in a second physical location.  The IT Manager quit before they set this server up, and they have asked me to step in to complete the project.

    What would be the best strategy to ensure DR and high availability given this set up?

    My initial plan is to install the second SQL into a VM on the new hardware then set up replication from the first SQL. Would this be good enough to eliminate the nightly full back up?  Can I then just do backups on the VM? What about clustering?

    Thank you for any suggestions.

    Wednesday, July 24, 2013 8:51 PM

Answers

  • Hello,

    What would be the best strategy to ensure DR and high availability given this set up?

    Well, I guess that depends... You said you wanted both HA and DR, but there isn't really an SLA or RPO/RTO combination there. There isn't anything stopping you from mixing and matching technologies either. It also depends how much money they want to sink into this, how many transactions per second they are taking in, log generation rate, bandwidth, etc.

    My initial plan is to install the second SQL into a VM on the new hardware then set up replication from the first SQL. Would this be good enough to eliminate the nightly full back up? Can I then just do backups on the VM?

    Absolutely not. Nothing, and I mean nothing (read: nothing, no matter what anyone tells you, vendor or otherwise) is a replacement for a known good backup. Having said that, nothing says you have to do a nightly full backup. We don't know RPO and RTO so we can't say what a good restore strategy would be and translate that into a backup strategy.

    What about clustering?

    Clustering is a single HA technology and option, it can be used in conjunction with many other things. Since you're using SQL Server 2012 you also have Always On Availability Group which work on top of clustering, there is also mirroring, etc. I stated above you can mix and match, so there isn't any reason you couldn't do clustering and mirroring, except for the fact you only have two servers.

    My advice would be to ask the customer what they are looking for in terms of HA and DR, especially if they are having IT Manager turnover. You'll most certainly want something official from the company and then give them an estimate back of what it'd take to accomplish what they want. If you're limited to two servers, each in a different physical location that may or may not be in the same domain or network then your options are going to be severely limited.

    I'm not trying to over-complicate the issue or sound like the messenger of doom, I've just been involved in too many "... they said it was highly availably 100% of the time ..." discussions. It's nice to do it right, but also have to work within the bounds of what you have.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, July 24, 2013 11:30 PM
    Answerer

All replies

  • Hello,

    What would be the best strategy to ensure DR and high availability given this set up?

    Well, I guess that depends... You said you wanted both HA and DR, but there isn't really an SLA or RPO/RTO combination there. There isn't anything stopping you from mixing and matching technologies either. It also depends how much money they want to sink into this, how many transactions per second they are taking in, log generation rate, bandwidth, etc.

    My initial plan is to install the second SQL into a VM on the new hardware then set up replication from the first SQL. Would this be good enough to eliminate the nightly full back up? Can I then just do backups on the VM?

    Absolutely not. Nothing, and I mean nothing (read: nothing, no matter what anyone tells you, vendor or otherwise) is a replacement for a known good backup. Having said that, nothing says you have to do a nightly full backup. We don't know RPO and RTO so we can't say what a good restore strategy would be and translate that into a backup strategy.

    What about clustering?

    Clustering is a single HA technology and option, it can be used in conjunction with many other things. Since you're using SQL Server 2012 you also have Always On Availability Group which work on top of clustering, there is also mirroring, etc. I stated above you can mix and match, so there isn't any reason you couldn't do clustering and mirroring, except for the fact you only have two servers.

    My advice would be to ask the customer what they are looking for in terms of HA and DR, especially if they are having IT Manager turnover. You'll most certainly want something official from the company and then give them an estimate back of what it'd take to accomplish what they want. If you're limited to two servers, each in a different physical location that may or may not be in the same domain or network then your options are going to be severely limited.

    I'm not trying to over-complicate the issue or sound like the messenger of doom, I've just been involved in too many "... they said it was highly availably 100% of the time ..." discussions. It's nice to do it right, but also have to work within the bounds of what you have.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, July 24, 2013 11:30 PM
    Answerer
  • Hello Kerberos,

    Sorry but to answer your question completely .I want to know some points

    1. What is max data loss affordable to your client in case of disaster

    2. What is RPO(recovery point object) and RTO(recovery time object ) defined in case of disaster

    Replication is good option but has some limitations.

    Clustering is very good but also has some (very few) limitationse 

    Please reply to my question so that i can give u complete answer


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 25, 2013 4:31 AM
  • https://www.microsoftvirtualacademy.com/tracks/mission-critical-confidence-using-microsoft-sql-server-2012

    http://www.sqlservercentral.com/articles/FCI/92196/

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 25, 2013 5:31 AM