February 28, 2016 at 3:26 pm
I'm working for a customer that has 800 GB data divided in 13 DBs. The have SQL 2008 R2 standard Edition, so cluster is not an option.
We can only do log shipping, mirror or replication.
We have the VM on the same location, but when I finish the configuration they are going to courier the DR VM to another city 1000 miles away.
So my question is what would be the best DR option?
I've tried configuring log shipping, but having more than 5 DBs over 50 GB each, the logs were big and the log shipping chain was broken, so I had to copy a new backup file and restore it all over again. So , doesn't look like the best option, as when they send the VM to this other city, the backups would fail.
So I'm considering mirroring the DBs, but we have 4 DBs that use Filestream...
Damm it... Mirror doesn't support filestream!!!
So what you would do? Just combine both? 9 DB using mirroring and one using log shipping??
February 28, 2016 at 7:02 pm
michellefs (2/28/2016)
I'm working for a customer that has 800 GB data divided in 13 DBs. The have SQL 2008 R2 standard Edition, so cluster is not an option.We can only do log shipping, mirror or replication.
We have the VM on the same location, but when I finish the configuration they are going to courier the DR VM to another city 1000 miles away.
So my question is what would be the best DR option?
I've tried configuring log shipping, but having more than 5 DBs over 50 GB each, the logs were big and the log shipping chain was broken, so I had to copy a new backup file and restore it all over again. So , doesn't look like the best option, as when they send the VM to this other city, the backups would fail.
So I'm considering mirroring the DBs, but we have 4 DBs that use Filestream...
Damm it... Mirror doesn't support filestream!!!
So what you would do? Just combine both? 9 DB using mirroring and one using log shipping??
Replication is not a DR tool, it is an HA tool, so you can knock that off the list.
Log Shipping should be fine for all. If you are having trouble with a broken log chain then mirroring is probably also going to cause you trouble. You need to have a stable primary and secondary regardless of which technology you choose.
I find Log Shipping to be easier to setup but mirroring will typically give you a smaller RTO (if you can configure auto failover) and a shorter RPO since it is constantly restoring to the secondary. Your mileage may vary of course.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 29, 2016 at 10:33 am
Have you considered using the VMWare technologies (vs. SQL's) to meet your DR requirements?
Might be worth a look:
March 1, 2016 at 2:27 pm
Thanks guys, we decided to combine them, so it is log shipping and mirroring, once done, they will be looking into an upgrade, so we'd definitely try cluster AlwaysOn which is way better than log shipping or mirroring.
March 2, 2016 at 5:08 am
One thing that seems to be missing from this discussion are Recovery Time Objective (RTO) and Recovery Point Objective (RPO) (OK, maybe two things...).
The RTO and RPO should be agreed with the business, and used to design the DR and HA strategy. If you are not focussed on these items you will either build something more capable than the business wants (for 'more capable' also read 'more expensive') or less capable than the business wants which could put the entire business at risk.
If you do not have any formal RTO and RPO for each of your databases then getting these agreed should take a higher priority than working out how to implement a technical solution. Also, if we in the forum know your RTO and RPO we can give better comments on how you can meet these with the tools you have available.
BTW, SQL standard edition does support 2-node active/passive clustering for no additional SQL licence fee.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply