How to handle replication post SQL Server 2000/2008 migration?
I have two servers; server A host SQL Server 2000 and server B ( a remote server) host SQL Server 2005. I replicate data between these two servers using transactional replication. I recently migrated (side by side) the SQL server 2000 to SQL Server 2008 hosted on a virtual server. I would like to mimic the replication I already have between server A abd B. Can someone tell me the best practice of carrying out this task? Essentially, what I want to do is to set up a transactional replication between the SQL Server 2008 hosted on a virtual server and SQL Server 2005 hosted server B ( a remote server) . Do I have to create all the publications and subscriptions from scratch ? Thanks for your contribution in advance.
I would actually start with a clean install for replication. Removing all of the publications and subscriptions. Then start fresh. The reason for doing this is because of the way replication works. MS recommends no more than 2 down and one up when running replication between dissimilar versions of SQL Server. The distributor will run at the lowest version which is connected to it. In this case, it would run as though it were a SQL 2K box, and there are features that you may like to use that will not be available. In my experience, I also ran into issues when we were running a higher distributor with SS2K boxes causing many hours of headaches.