Log shipping - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log shipping

Author

Topic

mgole
Starting Member

9 Posts

Posted - 2005-03-08 : 19:06:03

Hi

I have a few questions regarding log shipping in sql server 2000

1.Are all database actions logged such as table truncations etc?If not which actions are not logged?

2.Also if new users are created after log shipping is set up,are these new users transferred to the standby during shipping of logs.
Or do we have to transfer the logins and users through DTS?

3.If either of the servers(primary or standby) are restarted does this effect log shipping?If yes how can i work around it?

Thanks

Madhukar Gole

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-08 : 19:10:45

1. When truncating occurs, the page deallocations are logged.
2. The login is not transferred as that is in the master database, so you must have a separate job to keep these in sync; we use a stored procedure and not DTS for this
3. No that does not affect log shipping. It'll pick up where it left off.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-08 : 19:54:01

3. You need a little clarification here. If you are in the middle of restoring one of the log files when the backup server is restarted, it could cause problems. There is a possiblity the database will get stuck in a (loading) state. You would then have to reinitialize the backup sequence (full restore followed by consecutive log restores to catch up).

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mgole
Starting Member

9 Posts

Posted - 2005-03-08 : 20:07:51

Thanks for the replies.

Are there any actions(operations) which are not logged and i need to take care of while log shipping?

Madhukar

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-08 : 22:55:26

all 3 of your scenarios are really rather trivial to test in house. that would be a good exercise to see what happens in all 3 of your cases you cited.

-ec

jason
Posting Yak Master

164 Posts

Posted - 2005-03-09 : 16:13:28

Tara,

What stored procedure do you use?

To the YAKS:

Why do you call yourselves "Yak"? Does this mean you are shaggy and fat?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-09 : 16:22:36

I have posted the one that we use in the forums here. It is called isp_Transfer_Logins. Derrick has also posted MS' in the forums as well. That one is called sp_help_revlogin. The MS version keeps the sids in sync where as mine does not so it requires you to unorphan them with sp_change_users_login. I just hadn't heard about the MS version until after I had wrote mine.

Tara

jason
Posting Yak Master

164 Posts

Posted - 2005-03-09 : 16:31:58

Those posts aren't coming up on my search. Can you tell me how to find it?

I'm beginning to understand the Yak a bit more...