Best way to create a emergency online "backup" database

Question

Let's imagine a physical database running Sql Server (latest version).

We are NOT 24x7. However at worst we don't want to lose anything more than 24 hours of work.
Regular offsite backups are being done and that will continue independent of what we are now proposing

We want a system where if the current database was to catch fire we would have a "backup" server we could switch to with minimal disruption, and that includes sending an email to users saying they need to configure clients and stuff.

We don't want shared storage/cluster solutions unless we HAVE to.

An analogy would be having a fileserver where we start using FS2 because FS1 has failed.

We have about 16TB of data on the existing server. Jobs often run overnight and (I am told) we have a far higher "transaction rate" than most companies.
We are a research department holding records for millions of people and the researchers run a considerable amount of statistical analysis on data on the server (the processing is not done of the server itself). Mostly reads but sometimes lots of read/writes.

Is there a process where we can mirror the database to another one each night, and leave the second database in a state where we can activate it as a replacement for the first in the case of "disaster". During the night processing is likely to be occurring, so we can't take the current server down.

ideally the (cough) "replication" procedure needs to be simple and we know where we stand with it. As I said we are willing to sacrifice 24 hours of data (that can be recreated). A key requirement is I can look the DB admin in the eye and say "this will have no impact on performance during work hours" ("minimal impact" is not acceptable).

Answer 1

This sounds like a perfect example of using Log Shipping. You can have the primary database the way it is right now and have a copy on another server that is restoring logs waiting for the next log and could even be read/only (would require licensing the secondary) if you wanted to.

But with your requirements of not having impact during work hours, etc. I would go with Log Shipping. You can choose how often you restore on the other server, but this also serves another purpose and that is testing your backups that they are restorable.

Answer 2

We are a research department holding records for millions of people and the researchers run a considerable amount of statistical analysis on data on the server (the processing is not done of the server itself). Mostly reads but sometimes lots of read/writes.

You also have ability to use Availability groups(AG). Although my heart is inclined to Los Shipping which Ben Miller has suggested I would also like you to try AG.

1. You would have passive server which can be made active anytime without manual intervention. Logshipping would need manual intervention and changing of connection string in case of failover.
2. You would have ability to direct read only queries on the secondary replica if in case you want to reduce load on primary, this is called Read Only Secondary Replica. Note this would require licensing the secondary then.
3. You would have automatic failover which again would failover to secondary replica without any manual intervention.

** AG is enterprise only feature. If you have standard edition you can still use Basic Availability Groups (BAG) but in BAG you would not have readable secondary replica.

Answer 3

Another one to throw in the mix is snapshot backups of the whole machine. That would require some 3:rd party software, which does snapshot and does them properly (including the SQL Server part, of course). Snapshot backups are not 100% online, since you will have a "silent" part for some 5-15 seconds for the databases, when the snapshot is established. One option might be Veeam (just to mention one vendor), you could look into them and see if you could end up with something useful.

"No impact" won't happen (as impossible as "real time", as I understand it - it all depends on where you set the thresholds). Log shipping has impact when the log backups are produced. Etc. The only potential "no impact" I can think of is some storage level async mirroring technique, but that is outside my expertise.

Log shipping would be my first choice.

Answer 4

Hi @AndreaJohnson ,

You can also try database mirroring. But you can’t read or write from the Secondary database. But you can read from the snapshot of the secondary database. There is automatic failover if you set Witness Server and set it synchronously. But availability group by making a group of multiple databases. It is both more flexible and easier to manage than Database Mirroring.

Please read the blog Database Mirroring VS Always On Availability Group VS Log Shipping. Refer to Editions and supported features of SQL Server 2019.

Best regards,
Cathy Ji

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Thank you all for your comprehension answers and suggestions. I will do some googling and present an option to the DBA