A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Saturday, November 20, 2010

Transactional Replication: A Journey into the Unknown Part 2

I worked some more on this transactional replication process this morning. I discovered that the snapshot creation problem was being caused by one or more "views" that included select * somewhere in them. I eliminated all my personal views and left only the "official" views created automatically by the Shelby v.5 system, and the snapshot creation process completed normally.

After this things went smoothly until I tried to initiate the first replication process, at which point I started getting errors saying that the "replication subsystem failed to load" and also that the process as "rejected" because the job was "suspended." It took more digging, but I discovered that all I needed to do was to restart the SQL Server Agent service. After I restarted it and tried again, the synchronization process "started successfully."

As I type this the first synchronization is still in progress. Its been running for several minutes, and I'm not sure when it will complete. I'm going to let it keep running and I'll post again once I have more to report.

For now, here are three things I learned about implementing Transactional Replication that was not included in the 1-2-3 steps of the SQL Server Management Studio wizard:

  1. Make sure the SQL Server Browser service is running on the Principal/Distribution server(s).
  2. Do not include any views as replication articles if they include select * anywhere in them.
  3. After setting up the Subscription on the Subscriber server, restart the SQL Server Agent.

Knowing these three points would have helped me avoid several minutes of hunting down the causes of problems and then following several trouble-shooting steps. I'm sure there will be more to learn ahead.

No comments:

Post a Comment