Restrictions on a Log Shipped database

Question

In SQL 2005, what are the things that you should not do to a database that has Log Shipping enabled (and that runs under the Full Recovery Model)?

I gather that scheduling additional Transaction Log backups to a different location will break the log shipping (because the full log chain is no longer reaching the secondary server).

I also gather that Truncate Table is OK with log shipping (since Sql 2000).

Are there any other activities/commands that should be avoided?

edit: e.g is database shrink or log shrink ok?

Answer 1

Hi,

You are correct. You should not define any other transaction log backups outside of the Log Shipping configuration, in order to ensure that you maintain the natural Log Sequence.

Should you however wish to perform an Ad-Hoc transaction Log Backup, heaven forbid because you are performing some live maintenance on the production database for example, then you can call the SQL Server Job that Log Shipping uses to perform your transaction log backups. It is typically called LS_Backup. This will maintain the LSN.

To my understanding, no operational features of the database being Log Shipped are limited by using this availability technology.

Some things that can cause complications:

Encryption

If you are log shipping to another server and are using SQL Server native encryption then you will not be able to access encrypted data within the log shipped database unless SQL Server is using the same service master key.

Assemblies

You may experience difficulties accessing signed assemblies within a log shipped database, as you cannot enable the trustworthy property.

Permissions

If you intend to provide read access to the Log Shipped database then the SQL Server Logins will need to have the same SID as those from the source server in order for the logins to automatically map correctly.

Hope this helps. Cheers.

Answer 2

Database/logs growths/shrinks are fine, they will get shipped over and the standby will grow/shink as well. The only thing I'm aware of that will break things are:

BACKUP LOG WITH TRUNCATE_ONLY

Changing the recovery mode

Taking the primary database offline (not sure about this one, never tried it)

Everything else is fine, but doing mass REINDEXES can make for some very large logs that are difficult to deal with on a large database.