Replication between SQL Server 2000 and Yukon (2005)

Lately I've been working on a solution to replicate data between two servers in remote locations. Server A is the master server running Windows Server 2000 and MS SQL Server 2000 while Server B is the subscribing server using Windows Server 2003 and MS SQL Server 2005 beta 2. I ran into a few snags and ended up investigating several methods to accomplish my goal. Here are the methods I looked into:

• Snapshot replication (pull and push models)


• Transaction replication (pull and push models)


• DTS Import/Export package building and MS Agents to run the package


• Yukon's Business Intelligence Development Studio to create or modify an existing DTSX package

I ended up getting all of them working (to one degree or another) minus the SQL Server Agents launching a DTSX package. For some reason, I could run the DTEXEC function and it would work. Launch that one with a SQL Server Agent and it would fail with some security thing. My end guess is that SQL Server (in a dev environment only) was running under the local system account instead of a named account and therefore mucked it up.

The sad part of all this research and investigation is that I ended up going with the first solution I looked into (snapshot push model replication). Here was the biggest problem I ran into with the snapshot push (from SQL Server 2000 to Yukon).

If you add the servers all in and request the subscription from Server B (Yukon), the server is added as a Remote Server on Server A. Everytime the connection attempt would occur, I would receive an error that I had to use the Management Studio or SMO to manage Server B (Yukon). I ended up killing the remote server entry and adding a Linked Server entry for Server B using the MS OLEDB for SQL Server Provider. I also had to alter a setting under the Publication's subscribers to alter the connection to use a named SQL Server Login (due to the System account usage for the SQL Server Agent process). This information from Cryer.com helped.

Anyway, I hope this can help someone else out there.