Log Shipping and Best Practices
paulnxweb
Posts: 21
I can not find a best practices document concerning log shipping and backup or any white papers how redgate interacts with SQL Server to guess what I should do.
I plan to use log shipping to remote server across the internet using a shared drive with the encryption option. I have prototyped this setup using the log shipping wizard with log dumps and restores every 5 minutes on a small test database. It works nicely so far. My real world database is 10GB and growing at 1gb a month so the questions are
1) How do I tell the wizard to split the initial database backup into 10 files, prior to the log shipping configuration phase (the initial transfers thru the internet is brutal if this is one single file)
2) How do I reset the log shipping from the beginning phase each week. I feel unconfortable running the logshipping continously for months and months. I feel more comfortable restarting from the beginning of backing the database and restoring to the remote server then starting log shipping every 5 minutes.
3) What else do I need to do to insure the safety of my database? Hence the need for the best practices document. I have no clue if I can run redgate backup and log shipping at same time without it clobbering something.
My current environment prior to use of Redgate backup is to dump the entire database to a back up device nightly and then dump/backup the transaction log every 5 minutes (I can withstand a 5 minutes loss of data). So I want the same piece of mind that I get using my current implementation but I want to replace this using the redgate log shipping and get the extra advantage of having the backup done to a remote standby server.
I'm no database expert but some advice will be helpful! I can't imagine that I am the only person having these same questions.
I plan to use log shipping to remote server across the internet using a shared drive with the encryption option. I have prototyped this setup using the log shipping wizard with log dumps and restores every 5 minutes on a small test database. It works nicely so far. My real world database is 10GB and growing at 1gb a month so the questions are
1) How do I tell the wizard to split the initial database backup into 10 files, prior to the log shipping configuration phase (the initial transfers thru the internet is brutal if this is one single file)
2) How do I reset the log shipping from the beginning phase each week. I feel unconfortable running the logshipping continously for months and months. I feel more comfortable restarting from the beginning of backing the database and restoring to the remote server then starting log shipping every 5 minutes.
3) What else do I need to do to insure the safety of my database? Hence the need for the best practices document. I have no clue if I can run redgate backup and log shipping at same time without it clobbering something.
My current environment prior to use of Redgate backup is to dump the entire database to a back up device nightly and then dump/backup the transaction log every 5 minutes (I can withstand a 5 minutes loss of data). So I want the same piece of mind that I get using my current implementation but I want to replace this using the redgate log shipping and get the extra advantage of having the backup done to a remote standby server.
I'm no database expert but some advice will be helpful! I can't imagine that I am the only person having these same questions.
Comments
When you perform a backup using SQL Server (native backup), you can choose to either backup to a disk or tape device e.g. BACKUP DATABASE pubs TO DISK = ...
When SQL Backup performs a backup, it also uses the same BACKUP command as a native backup, with the difference that the backup is directed to one or more virtual devices e.g. BACKUP DATABASE pubs TO VIRTUAL DEVICE = ... A virtual device is an area in memory created by SQL Backup. So SQL Server writes the backup data into that memory area, SQL Backup compresses the data, and writes it out to disk. SQL Backup is basically an additional 'layer' between SQL Server and the disk device, providing compression and encryption functions.
It's thus subjected to the same backup 'rules' and principles as a native backup e.g. transaction log backups cannot be made for databases using a simple recovery model, for any one database only one backup can be running at any one time etc. By extension, any log shipping best practices should apply equally to log shipping using SQL Backup.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
2. There isn't an option to 'reset' the log shipping process. Are you aware that a full database backup does not break the transaction log sequence? E.g.
- full backup X
- transaction log backup A
- transaction log backup B
- transaction log backup C
- full backup Y
- transaction log backup D
- transaction log backup E
If you wanted to restore the database, you can either restore X, followed by A, B, C, D, E, or restore Y, followed by D and E. So you can still make a full database backup periodically (because you are afraid of the log shipping failing, or to minimise restore time), and still allow the log shipping process to restore the transaction logs.
However, you need to note that during a full backup (native or using SQL Backup), any transaction log backups performed by SQL Backup will fail, and be reported as an error. To avoid this, you need to ensure that you do not schedule any SQL Backup transaction log backups while a full backup may be running.
3. You could run RESTORE VERIFYONLY on the backup files. If you are using SQL Server 2005, you should consider performing backups using the CHECKSUM option. Running RESTORE VERIFYONLY then will perform more integrity checks on the backup files. See the SQL Server documentation for details.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8