March 27, 2012 at 3:12 am
Dear All,
We are planning to have a complete solution for SQL disaster situation.
We will have two servers, one is locally and the other is in another country where we have VPN connections between the two offices.
I planned to build a transactional replication with failover options to redirect our applications to the subscriber server.
My question is: Is this option (Transactional Replication) the most useful and best solution for mu purpose?
And if it is the best solution, how can our applications redirect to the subcriber server if the primary server is down???
Is there any thing to use that may check the primary server availability and redirect the applications to the subcriber server automatically???
Your help is very very very appreciated
Best Regards
March 27, 2012 at 4:23 am
I would say you need to do a bit more research into SQL's high availability options. Transactional replication "could" be used in this way but it is far from ideal due to the need to drop replication objects etc.
Far better options are:
Logshipping
Mirroring
Geo-Clustering
As you went for transactional replication can I assume minimal dataloss is expected from this solution?
If so, mirroring and geo-clustering is your best option depending on load. Probably err-ing to the mirroring choice as you are using a vpn.
March 27, 2012 at 4:29 am
obarahmeh 5846 (3/27/2012)
And if it is the best solution, how can our applications redirect to the subcriber server if the primary server is down???Is there any thing to use that may check the primary server availability and redirect the applications to the subcriber server automatically???
This supports mirroring further as you can use a failover partner for automatic switching.
You dont even have to do that with a cluster but there is certain information you havent mentioned such as the domains, subnets etcs
March 28, 2012 at 5:07 am
This was removed by the editor as SPAM
March 28, 2012 at 6:20 pm
I prefer "SAN replication" when possible.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2012 at 7:13 pm
Assuming you are looking for the best option have you considered SQL 2012 AlwaysOn High Avaialbility?
This can be configured as a Geo-cluster configuration but is only supported in Enterprise Edition.
Log shipping is more a Disaster Recovery Solution which is not really the same as High Availability. I also wouldn't consider Replication as HA, this is more to provide remote access to data for reporting purposes or in merge replication so distributed users can update their version of the database and have this merged back to the main DB. Good for thing like delivery a business or branches to a headoffice.
Mirroring would probably be your next best option if you use SQL Server 2008(R2). Configured for synchronous mirroring if the latency over your network isn't an issue. You need to confirm your applications can manage the reconnect to the mirror after a failover or provide an alternative mechanism like a DNS entry.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 29, 2012 at 3:41 am
Jeff Moden (3/28/2012)
I prefer "SAN replication" when possible.
Disk mirroring is always an "option" and isnt limited to SAN's. Third party software such as Doubletake accomplishes the same.
March 29, 2012 at 9:53 am
Define your disasters carefully - there are differences between what works well for disk failures (head crash), disk failures (read and/or write errors), network failures (cable unplugged to the server, cable to building cut by construction crew across the street), entire server rooms destroyed (fire, tornado), etc.
March 30, 2012 at 1:15 am
DR is an interesting beast - the two things you need to define before moving forward with any technical solution are the RPO (recovery point objective) and RTO (recovery time objective) - defining those two items with your business partners is probably the most import part of of any DR plan and will drive your technical requirements. It's funny, as "the business" is often much more willing to "lose work" or data than us DBA/Systems types are... especially when the sexy/technical "zero data loss" solution costs big bucks.
Joe
March 30, 2012 at 1:39 am
You can go for geo-clustering but if your DR center is going to be at a different place (which is how it should be), you might want to go for log shipping or database mirroring.
If you have databases that are independent of each other and can be operated with or without the presence of others you should go for mirroring (though if you are planning an upgrade to Denali, you might want to reconsider with AlwaysON coming up). But mirroring might affect your bandwidth during peak hours.
Otherwise, you can go for log shipping and schedule your jobs in a way to ensure you have all the databases that are required and not affecting your bandwidth at the same time.
March 30, 2012 at 4:31 pm
That's such an open question and all the responses here are valid
Do you have SAN in live and dr?
Do you have a large budget?
Do you have excellent (and i do mean class A) connectivity between sites?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 2, 2012 at 3:25 am
Have another look at Joe Clifford's post. If you do not know your RTO and RPO then you are very unlikely to build a DR solution that matches the needs of your business. You will either miss one or both of the RTO and RPO targets or you will spend more money and time on your DR solution than the business needs to have spent. Either way, your reputation will suffer when the business finds out what you have done.
If your business does not understand the RTO and RPO concepts then you need to look at educating them. When they realise you are trying to minimise costs and maximise benefits they should be interested in learning about these items, and respect you more for telling them.
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
April 2, 2012 at 5:32 am
Another thought for the long haul ..
Database Mirroring (SQL Server)
SQL Server 2012
http://msdn.microsoft.com/en-us/library/ms189852.aspx
which states:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply