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 |
|
NewMedia42
Starting Member
35 Posts |
 Posted - 2012-01-10 : 03:38:59
|
| I've been using LogShipping successfully for about the last year or so to keep my local development server sync'd to the production server - I've found it to be very valuable to have the latest database accessible in a safe environment where I don't have to worry about data errors, etc. About a week ago I made the mistake of moving one of the tables into a new filegroup and bam, log shipping was broken. Unfortunately I didn't realize it until it had deleted all the logs, so now I can't get it going again.The first problem is the DB size, which is about 100GB, so it's too large to realistically sync any longer - so I physically went to the datacenter the server is located at, and made a backup thinking I could restart the logging and it would start at that point. Surprise! That's not the case, the first .trn file it created is the entire size of the backup, so I'm back at square one.So, is there a way that I can have the two databases synchronize themselves realtime without transferring all the data? Ideally it could perform an MD5 or SHA1 or whatever on the tables/rows and then only resync the ones that don't match. Is this even possible and if so, what can I use to do it? I don't mind purchasing software if that's necessary - Redgate SQL Data Compare looked like it performs this functionality, but I don't know if once I do it, if the log shipping will run again happily or not.Also, is there a way (and if so, what are the steps) to add another secondary server to an existing log shipping setup? I know you can do this:http://msdn.microsoft.com/en-us/library/ms191308.aspxBut how do you get the db on the new server sync'd up?Any help would be greatly appreciated! Thanks! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-10 : 05:25:09
|
| You're going to need to copy over a full backup, then start shipping the logs again.While there are ways to sync up two databases, for log shipping to work the secondary server must be restoring or standby, meaning at best readonly.The log size is based on the amount of log space used since the last log backup (not full backup), so if you've done 100GB of logging (index rebuilds is an example), then the next log backup will be that size. The one after is likely to be smaller.p.s. 100GB is a very small database.--Gail ShawSQL Server MVP |
 |
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2012-01-10 : 13:12:46
|
| Ok, so none of the tools like the Redgate one are an option? Are they an option to consider since I'm not using log shipping for a hot spare, but just as a copy, or is it not worth the trouble and I should just stick with what I was doing before?Thanks again!PPS, didn't mean to say 100GB is large for a DB, just too large to easily backup across to my dev machine on a T1. Was just looking for an alternative to using RFC1149. ;) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-10 : 16:10:09
|
| SQLDataCompare and SQLCompare are great for syncing two databases (I used them for that) and they have command-line modes , replication is another option.I thought you were asking about syncing up the databases in order to restart log shipping (which won't work)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Broken log shipping - looking for options...