Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arashmahas
Starting Member
2 Posts |
 Posted - 2009-10-26 : 03:47:33
|
| I need to have a copy of my sql server on some other servers which there are unreliable and slow connections between them, is there any better solution than log shipping? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-26 : 09:30:05
|
| Database mirroring is a nice alternative to log shipping. It sends individual transactions rather than the log file. You may find this performs a little better depending on the size of the log files you would normally be shipping. I would reccomend trying both and seeing which one gives you what you're looking for. Do you need this copy for reporting, HA, DR?Mike"oh, that monkey is going to pay" |
 |
|
|
arashmahas
Starting Member
2 Posts |
Posted - 2009-10-27 : 12:26:19
|
| I need it for HA, will you tell me about mirroring VS Repliction ? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-27 : 13:32:58
|
| Sure.What mirroring essentially does is takes your transaction log buffer, ships it to the mirror server, writes it and in the case of synchronous mirroring the primary waits for the write to the mirror before committing so that the two are always in synch. You can do it synchronously or asynchronously. The difference being that the primary writes the transaction right away without waiting for confirmation from the mirror. This may be something for you to consider in your environment since you mention a slow connection. Doing it synchronously might slow down your app if the network is taking time to get the info across to your mirror. The primary advantage to mirroring is you can have an unattended failover or "lights out failover". Under 2008 the information sent to the mirror is compressed which would benefit you if you upgrade some time down the road. Keep in mind that unlike a cluster where a whole instance fails over, with mirroring it's just the 1 individual database you have mirrored. If you have a lot of production jobs that are critical to your business day, you may want to go further and look into a cluster.The closest thing in terms of replication is transactional replication. I actually don't have any experience using it so I can't speak to it much more than a few sentences. I want to be clear up front.... Replication is NOT an HA strategy. An HA strategy involves an instance or a database failing over without manual intervention and minimal user impact. Replication is a manual only failover unless you write some custom code to detect a failure and force your application to point elsewhere. Replication would be a valid DR strategy. Let's face it if your building burns down or worse and you are down for an hour while you failover you're still a heroe.I would strongly reccomend you spend some time with google, read some other forums/posts and articles and get a feel for what best suits your business needs. Keep an eye on this post as well as I'm sure more people will have opinions on this as the does go on.Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-27 : 14:44:44
|
| Mirroring and log shipping are disaster recovery solutions, replication is not. I'd start testing with asynchronous database mirroring. I would not use synchronous mirroring at all due to your slow connection. Check how backlogged the data transactions are to see if mirroring is able to keep up. Log shipping is going to have similar issues in that both need to send data over the wire. I use database mirroring, plus clustering, for all of our DR needs in SQL Server 2005. We use asynch for performance reasons to the DR site. We have a fast connection between the two sites, however they are 300 miles apart so ~15ms of delay in the two-phase commit really adds up. I would only recommend log shipping on SQL Server 2005 if you need to ship your logs to more than one server, read the data at the secondary server, or if you are also using transactional replication on the pirmary server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
uie87
Starting Member
2 Posts |
Posted - 2009-11-04 : 10:51:31
|
| i recall veritas - now symantec - had several clustering software options. I think one was "Storage Replicator". Not sure if they still have it but could be worth a look.<removed the spam> |
|
|
|
|
|
|
|
|
Is Log Shipping the best solution?