Hello,
I would like to know how do I carry out this two cases in efficient way:
1. I have two SQL 2005 servers running. One as primary, one as secondary. I would like to synchronize or replicate the transactions at real-time.
2. When primary goes down, I would like my secondary server to take place in needless of IP changes and application settings.
If you have a good architecture on this, please share with me. I am not sure whether my questions are clear enough or not. I also will be reading some articles about these. Thank you in advance.
Regards,
Paing
Hi,
First of all this is not an easy question. You need to ask few questions to yourself
(a) What is the level of availability you want for data
(b) what is the down time you have
(c) what is the budget you have ; can you buy new hardware for cluster
(d) what is the SQL Server Edition u have
(e) what is the disaster recovery method you want to have
From your scenarion what i understand is , u want such an architecture that do not demand any DBA intervention when primary fails.
In this case what i feel is you may go for Database mirroring or SQL Clustering. But the later one needs extra hardware and windows licensing etc. If you have that it is well and good. Otherwise you can go for Database Mirroring Highavailability and Synchronus Mode. As you said replication can be real- time(transaction replicaiton) but when the primary fail , the secondary will not up automatically. Replication Need DBA intervention.
Before going for implimention you need to read a lot. there are many article available in net. Search for "High Availablity and Disaster Recovery in SQL 2005.
I hope this could lead to your goal. Keep posting your observation....
All the best
Madhu
|||Hi,
Thank you for your reply.
a). I would like to have as far as possible. Means, I would like to have up to the last transactions that made just before primary went down.
b). My customer will not be able to afford even a minute downtime as they're having at least 3 or 4 transactions per minute.
c). Currently have two Windows 2003 Servers running. Servers' hardware are at their standards (I don't remember the spec tho).
d). Using SQL Standard Edition 2005.
e). This is the plan for DR and am not sure which method to go. If you have any recommendation, I'd much appreciate.
I would like to know a recommendation which is well known plan and best practice. We're still at gathering information on how-to. But my customer said they do not want to go for clustering. I do not know why. I will need to talk to their DBA later on if it is recommended here. I am hoping to hear more from you experts on this topic. I will do research on my own as well. And thanks for the topic tip.
Paing
|||
For Clustering your OS should be either Windows EE or Windows Datacenter. So first you should confirm this. If you want a High availability system which has server wide failure support and better performance and you have enough budget and your Operating system is EE or Windows Datacenter the first choice should be Clustering. The problem in database mirroring is it does not support Server wide failure. It supports only the database failure. So it is a good point for debate.
Database mirroring on SQL Server 2005 Standard Edition does not have all the features in Database mirroring. Feature like Database Snapshot, Parellel Redo, safety=off are not supported in Standard Edition. It is again is a new feature in SQL 2005 . For automatated failover in database mirroring you may need 3 servers.
So , what I would say is , you read these two option in detail and need to take a dicision very carefuly according to the avialable OS and Sql Server resource.
All the best and happy new year
Madhu
No comments:
Post a Comment