I have been working on a new skill this week and that is implementing transactional replication. I am of course doing all this testing and learning on a development system and thank goodness I am. I have found all kind of little nuances with getting this setup. A fun error I came across today after defining the subscriber was
“cannot be run because the distribution subsystem failed to load. the job has been suspended”
I tried a few basic things like restarting the job, deleting the subscription and recreating it, etc. I then turned to my trusty Google search and voila, the answer was found. It appears that after you configure replication for the first time, you must “RESTART” SQL Server Agent. Once I did that, BAM, my replication was actually replicating. It was like magic.
Now that I have two databases replicating I can restore my monster database and configure it to replicate in my development environment to bench mark how long the snapshot will take and all that other stuff so I can get this approved for production. Wish me luck.