2

I inherited a very interesting environment and I'm tasked with finding and implementing High Availability/Disaster Recovery solution as soon as possible.

Simply speaking our environment looks like this:

Server 1 - production database(200GB)

Servers 2, 3, 4 - need daily restored copies of production database (have to be updated with production data once per day)

Server 5 - DR server (data loss should be as little as possible, no more than 5 minutes)

We use:

  • Windows 2012 R2 on all servers
  • SQL Server 2012 Enterprise
  • We are happy to upgrade software and hardware (within reason) if that's necessary.

Requirements:

We need solution that:

  • would require as little maintenance work as possible (only 1 super busy DBA is available)

  • would allow us to continue producing full, differential and transactional backups(we need them for auditors)

Options I already considered:

I thought about it and I excluded following methods:

1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.

2) Mirroring only works between 2 servers (principal and mirror) so it doesn't solve problem of additional servers that have to be refreshed daily. Maybe I can use it on only one server and something else on the other 3 servers.

3) Replication is mostly meant for single tables and functions so I'm afraid its not possible to replicate one database on a slow server to 4 other servers.

4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.

I would be very grateful if you could suggest HA/DR solution that can be implemented in the described environment.

Improve this question

1 Answer 1

Reset to default
2

Depending on your level of expertise, I would suggest you - Logshipping or AlwaysON AG.

1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.

Logshipping. It allows you to logship your databases to as many secondaries as you want.

You can choose standby option on secondary that will allow you to read data when the logs are not being restored. Think of it as reporting ONLY.

You can take COPY_ONLY log backups that wont break your log chain (or restrict users to COPY ONLY backups) and you can archive your log backups (depending on the frequency you have set up) - so that your auditors can store them. Just dont delete them or have a higher retention period in clean up job of logshipping.

4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.

You can implement AlwaysON availablity groups and happily take your Full, diffs and T-Log backups.

The important thing is that there is a learning curve for you. All the servers should be part of the same windows cluster, there is DNS dependency, you application should be able to use the new provider and connection string, etc. Since you are using Enterprise editon, this is something that you can try as a Proof of Concept and once you are confident, you can transition from logshipping to AlwaysON.

Note: We are currently transitioning from Logshipping to AlwaysON for our huge environment of 1000+ databases being hosted in different continents of the world.

Some references to get you started on ALwaysON part :

Improve this answer
6
  • Thank you Kin! You are making perfect sense. We are now taking log backups every minute and each is takes at average 2,000-3,000 KB. Log shipping should be able to handle sending them to 4 servers, right? Could they be restored every minute on DR server and say once a day on other servers? I wouldn't want reporting database to be down while restoring database during office hours.
    – QWE
    Commented Feb 17, 2016 at 18:00
  • Thats 2-3 MB which is very tiny. Make sure you have good bandwidth especially when you do index maintenance. I would suggest that - start with what you are most comfortable and then buildup/embrace more advance stuff through learning. Logshipping should handle that.
    – Kin Shah
    Commented Feb 17, 2016 at 18:01
  • If I use log shipping and server hosting production database is destroyed... will I be able to change "read-only" standby DR database into read-write? Management will certainly want to know that.
    – QWE
    Commented Feb 17, 2016 at 18:05
  • Yes - that is called failover. You have to be careful when there are multiple secondaries involved. I use reverse logshipping and have automated it in my environment. See this answer as well. People have written scripts which you have to study and adjust as per your needs and environment.
    – Kin Shah
    Commented Feb 17, 2016 at 18:09
  • Sorry to be a pain and ask plenty of questions. Would enabling writes on secondary database be possible if entire server hosting primary database was inaccessible and I was left without last transactional backup?
    – QWE
    Commented Feb 17, 2016 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.