May 11, 2009 at 8:03 am
We currently have 3 SQL 2005 servers.
2 at a live site run on a active/passive (virtual IP etc) basis and a 3rd at a DR site which is constantly updated and ready for use should the live site go down completely. We currently use Symantec volume replication to enable all 3 SQL servers to be upto date with all changes.
However this has been causing major problems recently in the any failover to the DR site is just not working for us.
Do we have to use Symantec (or another 3rd party application) to keep all 3 SQL servers upto date ? Or is this standard functionality within SQL server ?
Any help would be great.
Si
May 11, 2009 at 8:11 am
You can set up the DR with Log shipping from SQL Server itself. Set to log ship every min. The Max data that you can loose would be 5 min if both your Mirrored server dies.
-Roy
May 11, 2009 at 8:15 am
So I could set up transactional replication between the two lives servers to keep them upto date. Allow windows clustering to deal with IP's etc.
That allows a failover at live.
Then log ship to the DR site. Jobs a good 'un !
May 11, 2009 at 8:22 am
There are a few DR options
Site A (2 servers)
- Windows clustering - Active/Passive
- Mirroring
Site B
- Log Shipping - quite robust but slower to recover your DR database
- Mirroring - depending on your network speed. (Async mirroring is an Enterprise only feature)
May 11, 2009 at 8:34 am
Thats cool. Not a SQL question now but how would you control the 'heartbeat' - that controls which server is the live IP etc ?
May 11, 2009 at 8:35 am
Why not make a Cluster of two servers with a shared Disk as your Primary server (Active/Passive) and then set the Log shipping for DR?
Then you dont have to bother with anything. That would be a better way than Setting up transactional replication.
The issue with transactional replication is that you need to have Primary key for all tables. Also makes life difficult to Alter tables or constraints. When adding new tables, then you have an additional step. But with SQL Clustering, you dont have that issue.
-Roy
May 11, 2009 at 8:36 am
Simon Smith (5/11/2009)
Thats cool. Not a SQL question now but how would you control the 'heartbeat' - that controls which server is the live IP etc ?
If you set up Clustering in Windows, it will take care of the heart beat if I am not mistaken. But dont quote me on that one.
-Roy
May 11, 2009 at 8:39 am
Windows clustering will take care of the heartbeat - generally you will run a crossover cable between the 2 nodes for private cluster traffic. Your application is unaware of which host it currently lives on.
Then you have the mirroring which is unaware of clustering - your application would connect to the principal db first (cluster) and then the mirror if unavailable.
cheers!
May 11, 2009 at 8:44 am
Ok great guys. Tricky one, lots to consider.
We have a meeting tomorrow with our hosting people (who suggested the Symantec route) and am looking for viable alternatives. Anyone have some good bedtime reading links ?
May 11, 2009 at 8:53 am
I'm not too sure why they are suggesting 3rd party products when SQL 2005 has quite a lot DR capabilities built in.
Some good reads...
Clustering - (thx Brad)- http://www.sql-server-performance.com/articles/clustering/cluster_sql_server_2005_p1.aspx
Mirroring - http://www.sql-server-performance.com/articles/clustering/mirroring_2005_p1.aspx
May 11, 2009 at 9:00 am
Thanks for that.
I agree entirely - 3rd party products just up the price everytime. Each edition of SQL gives us more to use. If we can use standard functionality they why try and replicate it elsewhere and pay for it as well !
May 11, 2009 at 9:04 am
I second that. Use Third party software only when you cannot use the Native methods provided.
-Roy
May 13, 2009 at 12:20 pm
Simon Smith (5/11/2009)
Thanks for that.I agree entirely - 3rd party products just up the price everytime. Each edition of SQL gives us more to use. If we can use standard functionality they why try and replicate it elsewhere and pay for it as well !
Amen!
* Noel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply