Log Shipping Issue With Subscriber Database

We had another Issue with our migration process.

I had a database created with replicated tables. Now the bigger question was how to create the replication in secondary (log shipped tables) database.

Be default, when you create log shipping, the secondary database is in read only mode. You are not allowed to create any additions to the tables. If you create a publication, on such a database, it will fail because it tries to create/update system tables, and add new tables regarding replications. (Yes it creates them in distribution database and in publication database)

So we came up with a different approach:

We decided not to create publications. We enabled replication on secondary server and on the day of transfer, we stopped all transactions and created replications manually.

But it has a different problem: By default transactional/merge replications require a snapshot replication to be created. We overrode the default settings saying just ignore the snapshot. As all the data was transferred, it will continue to function.

Did our plan worked? Well the answer is yes and no. The replication started working without snapshot is being created. However, we messed up few things in restoring the final log backup (tail-log backup) which forced us to deploy a fresh backup.

More on this tomorrow:

Just a piece to monitor: The picture here shows the setting we need to select when setting up replication using management studio.  The default is "drop existing object and create new one", but we need to select "keep existing object unchanged"

Share this post :

Log Shipping Issue

When we setup Log shipping manually it seems to be an easy task. But within a couple of days we were proved to be wrong. The log shipped database didn’t get the updates on time. Additionally we started receiving messages stating the log backup has failed. It took some time to identify the cause. All backups were set to start at the same time... So we manually changed the starting time so they started working fine.

Soon we faced the next situation: Since we have more than 60 databases, and setting them independently while giving enough room for log shipping to complete became a problem. Wither the time si not enough or we step into another log shipping time. Doing such a minute calculation was little costly for the operation we did, so we decided to live with this problem until the migration completes.

Do we have a solution for it. I think we have. We are planning to try that next week and I will update when we do that. Please keep your breath until then.