Caruncles (3/25/2014)
I'm also interested in this setup. We have agent jobs performing a full backup every 24 hours and transaction logs every hour (both to local folders). The last step of each job is to copy the backup file across the network to a shared folder on another box. All this has worked well for a long time. However, we don't have a failover instance which is up-to-date.The primary server is SQL 2008 standard 64 bit. The secondary box is running SQL 2008 R2 64 bit, so there shouldn't be any version difference problems that I know of. I tried setting up an Agent job on the 2nd box to restore the full backup once every 24 hours and restore the logs every hour scheduled after the copy jobs from the primary server. My problem is that the hourly log copy is too recent for the database which is in "restoring mode" and it throws an error on the agent job. Basically it's out of sequence. I think my problem is timing. The full backup restores fine, but there have been several logs since the full backup was copied, and I'm trying to restore the most recent log. The recovery model is Full on both instances.
Based on my recent research of log shipping, what I'm trying to do is a rigged up version of it. My question is are there any ramifications of changing the settings of my production server to "Enable this as the primary database in a log shipping situation"?
Also, will converting to log shipping require a restart or any down time on the production server?
Can these backups replace my current backups? Are they the same type of backup? Can the log shipping backups be used to restore the primary production server from which they are taken?
Some of these questions weren't mentioned in the articles I've read on log shipping. I'm just thinking I may need to scratch what I have and set up log shipping.
Thanx!
Technically this is considered hijacking of someone elses question. Not a big deal, but you're likely only going to get responses from the people who are already subscribed to this question. If you want a wider audience it's a good idea to post a new question for yourself.
As far as setting up your version of log shipping, that's fine. You just can't skip logs. They must be applied in order. So, if the full backup was taken at midnight, say. And the logs are at midnight and 1 am and 2 am and 3am, but it takes that long to copy the full backup over and restore it, you should start restoring logs from the midnight backup, not the 3am backup. Then you have to do the 2am and the 3am to catch up to that point.
Yes, log shipping or mirroring do have some impact on production boxes. It's not huge, but it's there. One common issue is that the secondary machine goes offline, killing log shipping, but people don't worry about... until the log file fills the drive because it's waiting for all the logs to get shipped. You'll want to think that through carefully.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning