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 |
|
Kristen
Test
22859 Posts |
 Posted - 2011-11-10 : 11:23:44
|
I need to set up Log Shipping between two servers running SQL 2008 (R1) Standard edition.Not done this before, but I understand the principles. Looked into it on SQL 2000 (or maybe SQL 7 !!), and I don't know how much has changed since in terms of what it can do for me out-of-the-box.So, in ignorance of what that might be, I was planning:We make ALL backups using our own SProc - e.g. MyBackupSproc which takes the name of the database, and a parameter for Full / Diff / Log.The SProc looks up the configured Path etc. from an Admin "Config" table, makes a filename including Date/Time, and so on.This ensures that we don't get some well-meaning DEV creating a one-time "just in case" backup on their C-Drive which is then not available when we want to restore the DB. The DEVs are trained to just type in:EXEC MyBackupSproc 'MyDbName', 'log' and the file is created in the correct folder, with a consistent name, and it gets removed by the purge routine after N days. etc. etc. ...I was thinking that I could add some attributes to my Admin "Config" table for:Copy backup files to an additional location (i.e. remote failover server)Tell remote server to restore the logSo my procedure would do:Make backup (as at present)If copy-to-another-location is required then launch a command prompt - we'd use a template for this, something like:ROBOCOPY x:\SourcePath \\RemoteServer\Share\Path SourceFile /IPG:80 /LOG+:x:\LogPath\MyFileName.LOGwith the Paths / Filenames substituted (e.g. from Admin "Config" table's parameters)then on completion of Copy we would use LinkedServer to execute an Sproc on the Remote Server to Restore the log fileThat sproc, on remote server, would log the filename (in an admin table) and then delete it after, say, 24 hours. My thinking is to keep the log files for 24 / 48 hours and then if we lose synchronisation I can easily copy of latest Full / Diff backup and reinstate log shipping using the recent LOG files already on the remote server.I think I'm leveraging something I already have, but maybe you will tell me I'm making a rod for my own back? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-10 : 11:33:03
|
| That's similar to what I usually implement - except that I leave the remote server in charge of the restore - checking every minnute for a backup.seehttp://www.nigelrivett.net/SQLAdmin/LogShipping.html==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 11:48:56
|
Thanks Nigel, very helpful to see an existing worked-solution.We like to "drive" from the primary server. That enables us to log outcomes locally, and then provide Status pages within our application. (We have a Dashboard page, and that queries all known potential breakage points to make sure everything that should have done has run, without error, within a set timeframe).However, I don't want the Backup to then Copy if the copy might take a long time, as that will block the next iteration of the Backup from starting.So I think, instead:Make backupAdd a row to Queue for Xfer to remote location, and a flag if a remote server is to restore that file.Then I can have a separate process make the Copy.Again, I would prefer the copy is by Push from Primary to Remote. The reason being that at times of very heavy server load we reduce the "speed" of such large file-copies to reduce impact on the network (hence the "/IPG:80" parameter in the RoboCopy sample above). If this is driven from the Primary end then an Admin user can use the DashBoard in the application to reduce, or increase, the speed of file-copy.Also, if the Restore, on Remote machine, is triggered from the Primary then again the outcome can be logged and displayed on the Dashboard.Reading your notes I have the following thoughts:"Done" folder on Remote for files, once restored - good idea  Add a Flag on Primary to allow a FULL backup to be passed to the Remote (which in turn would cause the Remote to make a normal Backup Restore, rather than a Log Restore). This to be used if the chain breaks.Q: Do you think it would be advisable to also copy Full & Diff backups to the Remote? (and delete them when they are purged from the Primary). I'm thinking this would allow a quicker restore if the chain broke - rahter than waiting for a new FULL to be made - and would be an additional back-up to the Tape system.Also, it would give me everything I needed on the Remote to make a restore to point-in-time, which I might well want to do to investigate something, and particularly given that the Remote server will be on 0.1% CPU utilisation whereas the Primary is likely to be flat-out Last question:Would it be better to have Remote restoring in STANDBY or NORECOVERY?Obviously I can make read-only use of it in STANDBY, which is attractive (and your notes say that's the mode you use), but are there any downsides? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-10 : 13:49:22
|
| There's really no downside to using STANDBY other than having the extra space for the undo file.FYI if you have the original SQL Server MVP Deep Dives book, Chapter 35 has a roll-your-own log shipping process you could use. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 14:23:39
|
Thanks. The only old books I have are Henderson's "Guru's Guides" - and they are Programming rather than Admin.I have Celko's "SQL for Smarties", but I can't find it in there  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 14:32:22
|
| I found a preview on Safari Books. The first half of each page which gave me the gist, thanks. |
|
|
|
|
|
|
|
|
Home Grown Log-Shipping