Why is Replication not Working?

First of all, what is Replication about?  You take a copy of a database - all of it, part of it, whatever you need.

• Maybe you want to copy data to a redundant server so that it's ready to take over if the original one fails.  Today you might use mirroring or AlwaysOn, but before these technologies came along you might use Replication
• Maybe the master database contains a price list - you can send an up to date price list to every branch every morning at 0700 ready for the working day.  

Here's a one page intro that I like.  

So why is it not working?  No doubt there are hundreds of reasons why replication isn't working.  Here's one more to add to the list.  I set up what I thought was a straightforward Publication, using the wizard that Microsoft kindly provide.

I won't go into detail - just work through the wizard, tell it what table you want to publish, etc, etc and schedule it to run at an appropriate time - every hour, let's say - that's the default, fine by me.

So the wizard creates a publication - it takes data from a database and makes it available for other databases, probably on other servers, to read.  Sorry, to subscribe to.  

OK, so much for Publication, now for the Subscription.  Again, follow the wizard:

Start by telling it the name of the Publication you just created

 Connect to the subscription server i,e, the destination:

Schedule the job, and set it off.  Easy peasy.  Actually, there's a bit more work than I have shown here, but essentially you follow the wizard and it sets up two SQL Agent jobs - one to publish the data from Location A, and one to subscribe to that data and copy it to Location B.

The problem that cropped up was that I scheduled the jobs using the wizard to run every hour - as it assumes by default.  Then I changed one item of data in the original, and sat back to watch it replicate to the destination.

It didn't.

There's a Replication Monitor (just open up Replication and right click on the Publication or Subscription) and it said it ran fine - no errors, nothing.  But the changed data did not appear in the destination.

It took me a little while to work it out, and then a bit longer to stop kicking myself. The wizard assumes that the jobs will run every hour on the hour.  So by default the Publication and the Subscription both start at the same time i.e. the subscription starts before the publication has finished.

Therefore a change to the original Article gets published okay, but it won't get across to the Subscriber for an hour.

Solution - edit the schedule of the subscriber job to run 2 minutes after the hour. (Publication might only take a few seconds to run, depending of course on how much you are publishing)

Thus:

18:17 Change is made to the original data

19:00 Article is published

19:02 Subscriber receives article

Had I been a bit more patient, it would have worked out fine, an hour later.  But I wanted to go home!