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.

QWE
  • 508
  • 7
  • 15

1 Answers1

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 :

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245