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.
- Distinguish active data, pull them into separate database (maybe at most 20 GB worth of active data)
- 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!
Best Answer
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.