| Author |
Topic |
|
Stanley Tan
Starting Member
25 Posts |
 Posted - 2002-10-23 : 02:36:55
|
| Hi,I need all of your expert advices on this one. We are going to deploy a mission-critical application which obviously cannot fail. I realize it's a very small possibility that the hard drive will fail, but I want to make sure everything runs smoothly.QUESTION: Can replication solve this by replicating the data to another machine? If so, how much of a performance penalty would be incurred?Any help or insights is greatly appreciated. Thank you.Stanley |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-10-25 : 13:03:57
|
| We use a failover cluster, with the database files located on a Raided disk array that supports hot swappable hard drives. The clustering technology seems to be a little difficult for our Network crew. It's been brought down several times by Network problems, when the Cluster lost it's connections to the networked drives. The failover cluster is supposed to protect from freak hardware failures (network card goes bad, motherboard fails, etc). And it is automatic,so it switches over without supervision after business hours. But since the drives are not 'in the box' you're vulnerable to 'accidents' that you might not otherwise worry about.If you go with replication, you are probably looking at the merge replication scenario.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_0qia.asp There is an impact, but it depends on the type of traffic and the amount of updating. For strict OLTP it works pretty well. Start updating 100,000s of rows or replacing tables on a regular basis, and replication can become a burden.Edited by - kevin snow on 10/25/2002 13:05:13 |
 |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-10-25 : 15:07:37
|
| I would suggest you look into Clustering and Log shipping, instead of replication. Transactional replication is only meant for replicating data for reporting purposes and merge replication is not suited for your scenario.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-10-28 : 04:24:43
|
quote:
I would suggest you look into Clustering and Log shipping, instead of replication. Transactional replication is only meant for replicating data for reporting purposes and merge replication is not suited for your scenario.--HTH,Vyashttp://vyaskn.tripod.com
Thank you to the two who replied. I have a follow-up question. What do you mean that transactional replication is only meant for replicating data for reporting purposes?Basically, we need to mirror the data to another machine so if one goes down the other will either manually or automatically take over WITHOUT any data loss.I'll look into fail-over clustering. Thanks again. |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-10-28 : 10:53:54
|
| In most replication schemes, one server 'publishes' the data, and 1 or more other servers 'subscribe' to the publication. Hwowever, updates and inserts only occur on the 'publishing' PC. If the publisher went down, all currently subscribed data would be available from the other servers, but no updates or inserts would be possible. The subscribing servers are not equipped to take over for the publisher.In merge repliication, two servers are publishers and subscribers of each other....but inserts and updates are often machine specific (ie. Server A might receive updates and Inserts from South America, and subscribe similar data from Server B which receives updates and inserts from North America...Both servers would have full sets of data. Technically, if one server went down updates and inserts could be routed to the other.)However, merge replication is not intended to be used as a failover system.Clustering with raid 10 (raid 5 plus mirroring) may be what you are looking for, if you have the budget for it.Logshipping is less expensive, but the load doesn't automatically switch to another server, and there may be a small amount of data loss.Edited by - kevin snow on 10/28/2002 11:03:02 |
|
|
|
jharwood
Starting Member
41 Posts |
Posted - 2002-10-28 : 11:18:33
|
| Has anyone tried/used sqlup!? In theory this software performs clustering at the application level eliminating the need for external arrays and identical hardware. Supposely it doesn't even require Enterprise Editions. This may be a possible solution (costwise). I have downloaded the trial version (fully functional for 30 days) but have not tested it yet. Has anyone had any experience with this? http://www.incepto.com/products.htm |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-28 : 11:29:26
|
| In our datacenter, we use fail-over clustering, and it's working like a champ. It was NOT easy to setup and get going, but once it's going, it's a thing of beauty.I highly reccomend getting the hardware for a failover cluster from Hp/Compaq, Dell, or Gateway. They will give you support when you are setting it up. You'll need it.We use HP, and we've been pretty happy with them and their support.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-10-29 : 04:35:51
|
quote:
In most replication schemes, one server 'publishes' the data, and 1 or more other servers 'subscribe' to the publication. Hwowever, updates and inserts only occur on the 'publishing' PC. If the publisher went down, all currently subscribed data would be available from the other servers, but no updates or inserts would be possible. The subscribing servers are not equipped to take over for the publisher.Edited by - kevin snow on 10/28/2002 11:03:02
Thanks for the lengthy explanation. I do not understand why the subscribing SQL Server machine cannot take over. If it has all the data, why can't it simply take over operations (even if I have to manually repoint all the clients to the new machine).As we have to implement this really soon, I don't think we have enough time for failover clustering as all of you have mentioned that it is difficult to setup. Log shipping seems to be the only feasible option. Is there a way to minimize data loss using log shipping? |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-29 : 09:43:53
|
| The subscribing server cannot take over because the subscribing sever only allows writes through the replication process. Therefore, when failover occurs, you would not be able to write to the database. And as stated earlier, your situation implies fault tolerance which is in regards to log shipping and clustering. Yes you are correct, clustering takes a long time, because you need to be prepared and understand in complete totality how the cluster works. Log shipping can be implemented very quickly, a good article can be found here: http://www.sql-server-performance.com/sql_server_log_shipping.asp***************************************Death must absolutely come to enemies of the code! |
|
|
|
|
Replication Question