Transactional Replication: A Journey into the Unknown Part 1

As excited as I am about the potential that an OLAP database would offer for reporting and other business intelligence solutions, I discovered yesterday that with regard to SQL Server concepts, high availability is currently more on the minds of our development team. As a result I have started exploring concepts such as replication, mirroring, fail-over databases, and such.

After reading over all the options, I have decided to try to create a transactional replication solution as a test case. My desktop will be the primary server (where the transactional database lives) and also the Publication server (where the database is "staged" for replication). My laptop will be the Subscription server (where the remote duplicated database lives). 

After installing SQL Server Management Studio and the Replication tools onto my laptop, I ran the SSMS wizards on both my desktop and my laptop. The desktop SSMS installation ran the Publication wizard without a hitch, excecpt that the SQL Agent didn't run. I started the SQL Agent service manually after the fact. At that point, everything appeared normal.

Then I ran the subscription wizard. I had trouble getting the SSMS on the laptop to connect to the desktop server instance until I figured out that the SQL Server Browser service was needed but was not running. I started the browser, and then the laptop connected to the desktop like a champ. I walked through the wizard without any trouble until I got to the message that the initial snapshot of the source database needed to be made before I could continue.

I checked the desktop SSMS replication properties to verify the existence of the snapshot, only to instead find out that the snapshot did not exist. I tried to run the SQL Snapshot Agent manually, but I started getting errors during the process. Something about GROUP BY and the need for LEFT OUTER JOIN. 

Well, this took me a couple more hours than I was expecting, so I will have to leave the troubleshooting of the Snapshot Agent for another day. But once I figure out what is happening with that, I'll post the next chapter in the transactional replication adventure.