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 :