Log shipping is a high-availability technique that allows you to bring a standby database online in case the primary database encounters problems. Replication can be used to distribute load across multiple servers, move data from a central server to remote servers, or combine data from multiple sources into a single location. You'll also find environments where replication is used for high availability. In this tip I'll discuss some of the issues encountered when marrying log shipping and replication.
Why combine log shipping and replication
You might wonder why you'd ever want to use the two techniques in tandem. Suppose you replicate transactions from multiple publishers to a single destination; this is a common way to combine transactional data on a reporting server. If any of your publishers fail you must restore the published database from a backup.
Combining reporting queries with INSERT / UPDATE / DELETE statements issued on the transactional server isn't a good idea, so you don't have the option of using the reporting server for transaction processing. If your database is relatively small and you can afford lengthy downtime, you might restore the latest full backup of the transactional database and apply any transaction log backups taken since the last full backup. But an easier solution would be to continuously apply transaction logs to the standby database on a different server -- log shipping. If the primary database becomes unusable, perhaps due to hardware failure, you can bring the log-shipped database online and point your application to the standby server.
What to watch out for when log shipping a replicated database
1. Make sure that you have plenty of horsepower on the standby server to accommodate your users in case the primary server fails. If you want to provide your users with optimal application performance, the standby server should have as much processing power and memory as the primary server. You should do this whether or not your primary database is replicated, but replication does add some overhead on the publishing, distribution and subscriber servers.
2. Keep in mind that in case the primary database fails, you'll have to replicate transactions from the log shipping destination server. If you're using a separate distributor server, make sure that the log shipping destination server is configured as a publisher on the distribution server. Also make sure that any servers subscribing to log shipping source data are also set up as subscribers for your log shipping destination server.
3. Script all publications on your primary server and store them in a secure location that you can access easily. If the primary database becomes unavailable you will have to set up your publications on the standby server. Although you can view all your publications under replication folder on the standby server, those publications are associated with the primary server. When you failover to the standby database you must set up those publications on the standby server.
You should be aware of dropping a log-shipped database used for replication. If your log shipping destination server encounters a hardware error, the log-shipped database may be marked as suspect and the log shipping restore jobs will fail until you drop and recreate the standby database.
There is a catch: When you try to drop a replicated standby database, SQL Server will raise an error: "Cannot drop database (your_database_name) because it is used for replication." This happens regardless of whether you try to drop the database using Enterprise Manager or by issuing DROP DATABASE statement from Query Analyzer. Obviously you don't care about the standby database being replicated because it has no subscribers. To drop replication from the database you can typically you choose Tools | Replication | Configure publishing, subscribers and distribution within Enterprise Manager, or execute sp_replicationdboption system procedure. But since the log-shipped database is always in read-only mode, you can't drop replication using either of these two methods.
To get around this error you'll have to make a change in sysdatabases table within the master database. The category column within this table contains a bitmap of information used for replication. The published database will have a bitmap of 1. The following statement will change the category column value so that the database is no longer published:
Use master
GO
UPDATE sysdatabases
SET category = 0
WHERE dbid = DB_ID('database_name')
Once the database is no longer published you can drop the suspect database and set up log shipping again.
About the author: Baya Pavliashvili is a DBA manager with Healthstream - the leader in online healthcare education. In this role, Baya oversees database operations supporting over one million users. Baya's primary areas of expertise include performance tuning, replication and data warehousing. He can be reached at
baya.pavliashvili@healthstream.com.
More information from SearchSQLServer.com
Tip: Understanding replication methods: Snapshot, transactional and merge
Tip: Hardware clustering vs. replication: A case study
Tip: When to use data replication
