September 18, 2008 at 8:10 am
Hi all,
I am setting up transaction log shipping on our network. Everything seems to have gone well with the setup, but when I look at the secondary server database it says it is "restoring..." all the time.
Is this normal? Because it is restoring all the time, it won't let me do anything with the database, like check to make sure it is actually being updated.
Any help would be greatly appreciated.
Thanks,
Jim
September 18, 2008 at 10:52 am
when setting up log shipping it can have 1 of 2 states, WITH STANDBY or WITH NO_RECOVERY
STANDBY is the first. The database is in a read only state and clients are disconnected when restores take place.
The second is as you see now, log files are constantly restored until you issue the following command
RESTORE DATABASE mydata WITH RECOVERY
or restore the last log WITH RECOVERY
😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 18, 2008 at 12:23 pm
So if I'm understanding correctly...
If this is just a standby server database, I can do it in standby, and it will be functional except for when a restore is taking place.
Is that correct?
September 19, 2008 at 1:32 pm
Add "Read Only" and you are correct, but I'll offer some caveats...
1) By using the database for anything else, you must now license the backup server. Based upon discussions I've had with licensing geniuses (I'm definitely not one), you're allowed a passive instance of SQL Server for every license that you own. This can be a passive cluster node, a standby server using log-shipping, or mirror. If you use the instance, it must be licensed.
2) If you have shipping set up for every 5 minutes, even though you set up "WITH STANDBY", you're still only getting sporadic availability. A user could be using the database one second, then it is is unavailable the next.
I've personally never done it, but you can set up a mirror then set up a snapshot against the mirrored db to use. The snapshot will get "out of date", but taking a new snapshot, will alleviate that problem. Doing this will still have the licensing implications, but you'll at least have consistent availability and relatively up-to-date information available to analyze.
September 20, 2008 at 12:11 am
You need to determine the objectives of the secondary database. If it is for high availability purposes, you would need to apply the transaction logs as soon as possible. Making it read-only for reporting purposes would be next to useless, if the restore interval is very short e.g. every 5 minutes, as every time the transaction log is restored, all users would be kicked off the database.
If the objective is to maintain a read-only database, then you could increase the log restore interval e.g. 4 hours. This makes it more user-friendly, with the caveat that the information retrieved could be up to 4 hours old.
You could also set up 2 secondary databases in your log shipping setup, one for high availability and another for read-only purposes. Obviously, each would have different restore intervals to cater for different needs.
To set up a database for read-only access, select the 'Standby mode' for the secondary database, as depicted in Step 7 of this document (http://www.sqlbackuprestore.com/logship2005_ssms.htm) . If you want to monitor the status of the log shipping setup, you can use the log shipping status report, as shown here (http://www.sqlbackuprestore.com/logship2005_report.htm ).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply