[Story] Replication from remote site

Welcome to a next part of my DBA stories. This time I’m going to tell the story about replication problems I have experienced recently.

The task was pretty standard – set up a transactional replication of a database (size of few gigabytes) from a remote site to central office using our WAN and schedule it to synchronize at night. At first it seemed easy, there was one week deadline for it, the database had already been replicated in the past, so I thought it’s not a big deal.

First it turned out that about a half out of 300 tables in the database does not have a primary key. I had to contact development team to solve it – it appeared that it was caused by change of collation on a database which they had requested before! Luckily, some old copies existed so they could be scavenged for definitions of missing objects.

After fixing missing primary keys I proceeded with setting up publication. I chose everything to be published (there were no filtering requirements), generated the snapshot but then it couldn’t be applied at the Subscriber! I wasted two days (since I ways trying to get it done in line with assumed schedule) for trying to get a snapshot applied. After research I found that views were causing all the mess.

Two of the views were referencing linked servers which weren’t defined at the Subscriber.

One of the views was referencing a table in another database which existed at the Publisher, but not at the Subscriber.

One of the views was actually a disaster. I spent over an hour trying to get past it, until I saw it last. The script was like that:

CREATE VIEW viewName
AS

/* CREATE TABLE someTable
( column1,
column2,
...
columnN)
GO

*/

... actual view definition

The message in the Replication Monitor was “unable to find closing comment mark ‘*/’”. And after some it hit me – GO! Seeing all the issues and knowing that I already missed the deadline I decided to remove the views from the publication and recreate them at the Subscriber, if necessary.

So then the snapshot was OK, so I started synchronizing it during the day. But what happened was that network bandwidth was maxed, so literally al network traffic to remote site slowed to a crawl. The snapshot was applied in 14 hours, but now everything is back to normal. The correct schedule is applied, but I think I have some more grey hair due to this dam replication. It’s not my favourite high-availability technology.

About these ads