In my current project, we're playing with SQL Server Log Shipping to see if it can be used to maintain a relatively fresh backup database. We haven't switched over to SQL Server 2005 yet, so we have not yet tried Data Mirroring.
I ran into an interesting situation when putting the monitor server on the secondary server (i.e., the SQL Server to which the transaction logs are shipped). The monitor was saying that the secondary database was out of synch. But in the SQL Server logs, I could see the transaction logs being restored to the secondary database.
With some digging around on the Internet, I found an article that led me to the source of the problem.
It started when I first registered the secondary server in SQL Enterprise Manager on the primary server. The secondary server has both internal and external IP addresses. At some point during the configuration process, the server alias was resolving to the external IP and that was causing a problem. I don't remember exactly what was going wrong and I do not yet want to re-create the situation to find out.
Needless to say, I registered the secondary server using its internal IP address instead of its alias.
This had the side effect of storing the IP address of the server in the log_shipping_secondaries table in the msdb database of the monitor server. One of the jobs of the log_shipping_secondaries table is to track the last transaction log restored to the secondary database.
The SQL job that updates log_shipping_secondaries was looking for a record with its secondary_server_name field set to the server's alias. But the only record in the table contained the server's IP address, not its alias. So the record was never updated. To the monitor, it looked as though the transaction logs had never been restored.
The solution was to update the value of the secondary_server_name field to the server's alias. Log shipping continues to work properly.
--
Sean Winstead
Tags: SQLServer, LogShipping