Log Shipping- Again!
-
Sorry for repeating my question but I didn't get the answer (and I honestly read all posts regarding log shipping here): after I got trans logs on StandBy DB/Server (that is in read-only mode) how can I restore secondary database to the primary one status? I mean do I need to create job (let say, every night) on StandBy server or use another way (common practice)? Thanks
-
You'll need to initially restore the database on the secondary server using either the norecovery or readonly option, both of which will allow you to restore further transaction logs (norecovery means that you can't see any of the data, readonly is fairly self explanitory).
Once that's done you transfer (ship) the transaction logs from the primary server and restore each of them in turn, again using the norecovery or read only options (there's a good article on it at http://www.sql-server-performance.com/ if you're interested).
You can either use the built in functionality with the enterprise edition of SQL or simple log shipping (as found in the resource kit), or like me, you can write your own method to control what is done when (it all depends on how much money/time/skill you have).
Once it becomes time to bring the database back online (i'm going to assume you're doing it for DR purposes) you need to execute the following commands.
restore database <database name> with recovery
This will bring the database online, committing any completed transactions and rolling back any that havent finished at the recovery point. depending upon the options you set you may also need to make the database multi-user again.
A word of warning though, if you use the read-only option so that you can use the secondary database for reporting against make sure that there are no connections made to the database before the transaction logs are restored because the restore process needs to have exclusive access for it to work (i suppose you could either suspend the restore or do it manually after you'd finished whatever you're doing if necessary).
In addition to this; if the original database is being used for full-text searching you will need to do this from scratch after the database has been restored to working order, if you do it in a controlled manner and still have access to the original system then you might be able to export the catalogs from the original server but that's a whole other topic.
-
Mike does bring up a good point. If the stand-by database is to be used for reporting then you will not be able to restore the transaction logs. I hold a second database for reporting which is restored on a weekly basis.
I currently have implemented a modified version of simple log shipping. Scripts can be found in the SQL 2000 Resource Kit. I have modified the scripts to include the retrieval of the last transaction log backup and apply it to the stand-by server. This occurs as a second step in the transaction log backup job. If you would like to know more please let me know.
BK -
Likewise from me, if you need any help or scripts then feel free to contact me.
We're currently log shipping a half terrabyte database from 64-bit to 32-bit systems in different countries without any problems whatsoever (grows by around 10 GB a week so it's not exactly insignificant
). -
Thanks, guys, more or less clear. But I figured out already that in my case (Enterpr edition) database restores automatically every 5 minutes when transaction log copies and restores (with this interval) on StandBy server. And actually that is what I need. Mike, I have couple questions:1)My guess is that you are using not Enterpr edition if you are doing database restore manually. How often you restore your secondary DB to keep it as primary warm copy? I mean do I actually need to restore my secondary DB every 5 minutes? 2)Regarding using StandBy server for reporting- if copy interval is 5 minutes I guess it's impossible? Otherwise I can break log restore?! Thanks again
-
Hi Yuri,
Actually we're using the 64-bit enterprise edition on the production cluster but only the standard version on the DR box, hence the requirement to write a custom set of jobs to do it (we're also looking at incorporating some compression methods into the backups at some point in the future (litespeed2005) which will significantly reduce the amount of data being shipped at any one time).
Because our data grows at such a large rate (around 10GB per week) we tend to do hourly transaction log backups to ship across to the secondary server (there's no point in doing them more often because there's no guarentee that they'd be copied across in time); in general we don't apply these for 3 hours after they arrive (they are checked immediately) because we don't want to be in a situation where something disasterous happens on the primary box and is transferred to the secondary server. The important this is that the transaction logs are send across to the secondary server.
Obviously this approach means that in the event of a major disaster we'd loose up to a maximum of an hours work but due to the nature of what we do that is preferable to the other alternatives.
And you are right, if the copy interval is just 5 minutes then it pretty much prevents you from using the second copy for reporting (although you could consider suspending the restores if required for a period of time, it all depends on the method you're going to use)
Hope this helps with your desicion process.
-
Thanks, Mike, and good luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply