0

Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only about 100 GB can be spared at most).

So, I was wondering if it's possible to mirror partial portion of the database.

  1. Distinguish active data, pull them into separate database (maybe at most 20 GB worth of active data)
  2. Setup database mirroring for new database

Questions:

  • If partial database is mirrored, should I be worried about performance issue?
  • Are there other known solutions to prepare for DR situations?

It may be hard to provide best answer without knowing the detailed specs of servers, and configurations but I'd like to know how other servers are handled with limited resources.

Thank you!

  • Hi Vay, it give everyone clear indication to answer you better, when you describe what is mean by "due to limited resources" does it mean storage limitations at DR site or concerned about overload on primary server resources – Shekar Kola Sep 26 at 8:34
0

I think you should consider transactional replication instead of pulling data into a separate database and then mirror that database.

Replication in my opinion is not a high availability solution because for example it does not provide automatic failover, but I think it fits to your needs because you just want to replicate some tables and you can also apply filters to the published tables so you can replicate just part of the data and not the entire table (in case you want to do something like that). If you want to redirect applications to your replica, you could create a DNS entry pointing to the primary sql instance and set the connection string to use that DNS instead the servername or the ip and you just change the DNS to make it point to the principal instance or the replica so you don't have to touch your applications to failover.

Of course replication look at the limitations of replication and evaluate if this fits your needs.

As you mentioned, it is hard to provide a recommendation without knowing all the specs of your scenario, but I hope this can be an alternative to you situation.

0

Consider following are DR Solution available within SQL Server, but non of them got option for partial sync of database.

  1. Backup
  2. Log Shipping
  3. FCI (Multi Sub-net)
  4. Availability Group

Distinguish active data, pull them into separate database

Based on this i assume that there is no impact on the application when we do database archival, in that case you can proceed database archival using SSIS Packages/Replication/Custom scripts for one-time, and leave active portion of database for application, this would differently improves the performance, and you can plan for DR with following solutions:

Backup (offsite)

It is essential for any database, in your case having archival database backup (1 copy as there are no write operations) off-site would help in disaster recovery time.

Log Shipping

Configure Log shipping for the database (active portion). This basically takes frequent log back from primary site and restores at DR site, since we have control over Log backup schedule we could minimize the overload on the primary server (of course it has to compliance your RTO/RPO)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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