April 25, 2012 at 7:35 am
I've been using log shipping for years and it has worked great...Especially the fact you can delay the restore to protect against corruption or user deletion....etc. However, the Exec's want failover to be instantaneous...That being said, what is the best solution to failover to another site?
Thanks,
Greg
April 25, 2012 at 8:25 am
gregtm (4/25/2012)
I've been using log shipping for years and it has worked great...Especially the fact you can delay the restore to protect against corruption or user deletion....etc. However, the Exec's want failover to be instantaneous...That being said, what is the best solution to failover to another site?Thanks,
Greg
do you want redundancy at the server level or per database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2012 at 8:58 am
It depends, if the replication technology sync's accounts...etc then Database. if not Server level.
April 25, 2012 at 9:32 am
gregtm (4/25/2012)
It depends, if the replication technology sync's accounts...etc then Database. if not Server level.
accounts are not synced thats a manual process, so what it is you are trying to protect against?
A sql server cluster will mitigate the failure of a node\server
a sql server database mirror session will mitigate against failure of an individual database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2012 at 9:38 am
It also depends on whether you're talking about failing over to a server in the same physical location. I see you're on a WAN...does this mean the failover server is not on the same network, or not in the same geo location as the primary?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 9:56 am
Nothing moves user accounts over. Clustering is essentially pointing two servers to the same set of physical data, but if there's a disk issue, you have no failover, only fail.
Mirroring gives you automatic failover, but it's database level only. If you have logins, you need to move them manually, or script something. Same for packages jobs, though you also need detection on the failover server that enables jobs when the failover occurs.
There isn't a simple solution here. Replication can work, but it's a lot of administrative work. You really ought to read about all these technologies and then make a recommendation or ask more questions. Note that if you move to 2012, and write an upgrade check, you can get a better set of failover options, though you do not get complete server failover. The logins and jobs can still be an issue.
April 25, 2012 at 10:09 am
I'm located in BC and am looking to move my DR to Ontario to protect against EarthQuakes...etc. 2000 miles away... We host a Point of sale web site and need high availability. The Datbase server is running SQL 2008 R2 and the Database is 200 GB and growing. The SQL accounts shouldn't change that much so It might be easy to get away with Mirroring.
However when the primary comes back online I need to replicate back and switch to the primary without a lot of headaches. There are a lot of senarios that could occur...Like ISP down, Server down...I need to be able to failover without user intervention...etc?
April 25, 2012 at 10:12 am
Async mirroring comes to mind first. Enterprise Edition or Standard? Async mirroring (good for separate geo locations or slower links) is Enterprise only.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 10:37 am
Mirroring just moves the log data from one to the other, so there's no replication. It's a replay of the same transactions from the primary on the secondary mirror. When you failover, the former mirror becomes the primary, and when you bring the old primary back up, then you have it set to receive the transactions from the primary, become the secondary, and then failover back to your original server.
It gets confusing to talk about, but essentially you reverse roles. Once that's done, if the remote server is still up, data flows the other way.
This might help: http://msdn.microsoft.com/en-us/library/ms189850.aspx
April 25, 2012 at 10:52 am
PS Automatic failover is not supported in Async mode and either way, with mirroring you have to manually keep your logins and other instance-level objects and settings synced.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 1:58 pm
Steve Jones - SSC Editor (4/25/2012)
Nothing moves user accounts over.
Probably not an option here but there are contained DBs in SQL 2012.
April 26, 2012 at 5:34 am
Before devising a BCP / DR approach, you need to thimk of a number of aspects....
> In a failover cluster, it is a mere disconnection for the end user; in case of mirroring, the application needs to be intelligent enough to route its transactions to the current server serving the client
> You need to consider the bandwidth between the two sites; if it is low, how are you planning to reroute your application
The best approach is a load balancer routing transactions to the available site where you have a dedicated setup and the database mirrored. If you want to have more than one site, you can use replication or log-shipping (with role-reversal scripts).
The above aproach can be modified as per your requirement and can be used for load balancing using merge replication (with disadvantages of conflicts)
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 26, 2012 at 7:30 am
Has anyone tried any third party solutions?
http://www.falconstor.com/solutions/business-applications/microsoft-sql-server-solutions
http://www.marathontechnologies.com/sql-high-availability.html
https://support.ca.com/phpdocs/0/5878/support/xosoft_support.html
http://www.visionsolutions.com/Products/DT-Avail.aspx
Thanks,
Greg
April 26, 2012 at 8:40 am
I have not tried any third-party solutions for SQL Server HA. I'll just throw this out there, if you're thinking of buying into a third-party product the as part of that exercise I would recommend evaluating the prospect of upgrading your environment to gain access to SQL Server 2012 Always On Availability Groups.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 9:48 am
cfradenburg (4/25/2012)
Steve Jones - SSC Editor (4/25/2012)
Nothing moves user accounts over.Probably not an option here but there are contained DBs in SQL 2012.
Yes, should have clarified. Nothing in 2008 does this, but 2012 starts to fix this.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply