Replication between SQL 2000 and 2005

Question

Replication between SQL 2000 and 2005

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

We use replication heavily between 2000 and 2005 servers.

We have a central master data server (SQL 2000 sp4) that is the publisher and distributor. There is transactional replication running to four SQL 2000 servers (all sp4) and three SQL 2005 servers. They are all push subscriptions.

This has all been running for more than a year with few incidents and none related to SQL versions.

As for setup, I configured the publication and distribution on the SQL 2000 server from Enterprise Manager. I added the SQL 2000 subscribers from there as well. When I received the error configuring the SQL 2005 servers (because they could not be registered in Enterprise Manager) I simply opened up Management Studio, Registered the SQL 2000 server, and configured the SQL 2005 subscribers from there.

I have abandoned the SQL 2000 replication UI and I use the SQL 2005 Replication Monitor for all management. The old one worked, but the new one works better and has a few new features.

To add to this, we also have merge replication running with pull subscriptions to a mobile device we created. This is using one of the SQL 2005 servers as a publisher. Getting the error handling to work on the mobile device was a bit tricky, but merge replication to a compact edition database in SQL 2005 works very well.

kburndorfer SSC Enthusiast Points: 186 More actions · Answer 2

I'm trying to follow the same steps and I get the 2005 server registered but there is a connection error with it: "Connection Failed, check SQL Server Registration Properties"

I've tried both the SA account as well as an authorized Windows domain account and the error stays the same.

When I setup the 2005 subscriber it fails stating: "The process could not connect to Subscriber 'myserver'"

Am I missing something on the setup here?

My 2000 server is at 8.0.2162 (SP4)

My 2005 server is at 9.0.1406

pcwik SSC-Addicted Points: 404 More actions · Answer 3

Yes you can, you will get the error, click properties. You will get another dialog stating that you need Management Studio to register, do you want to register anyway? Click Yes.

That's all there is to it.

Peter Cwik

kburndorfer SSC Enthusiast Points: 186 More actions · Answer 4

Not sure if it helps, but I called MS and they suggested the Pub and sub process be entirely managed from 2005 using the Management Studio Replication features. We have a mix of both 2000 and 2005 publishers all going to 2005 subscribers. There were problems until I dropped all the existing replication I initialted from 2000 and replaced it from what the management studio generated.

J. Cote SSC Enthusiast Points: 163 More actions · Answer 5

Here is an interesting little twist:

I am doing a 2000 SP4 to 2005, cross domain, but there is very heavy security setup in between the two. I have opened the ports for the SQL agent to run across, but I don't have a workstation that has Management Console that can access both the 2000 and 2005 server at the same time (because of security).

I can add the 2005 server to the 2000 Enterprise Manager, (but I got the previous mentioned Comm error), and I can get the subscription built, but every time I try to synch it, the status changes to "Pending" and then nothing happens.

I'm thinking that I am going to have to build a bridge, a workstation that has Management Console and can access both servers on the SQL port in order to get the subscription to work. Because of security, it MUST be a push subscription from the 2000 server. Has anyone else run into this?

Thanks.

Ric Sierra SSCrazy Points: 2260 More actions · Answer 6

In case of different domains my solution was:

Create a local admin users in both servers with the same name and pwd.

Give access to the replication share folder to that user locally (in the distribution server).

In my case the security is very strong so, I used the IP address instead of the server name.

Saludos!

im.sahirkhan Grasshopper Points: 11 More actions · Answer 7

Hi,

I have an issue with the Replication from Sql Server 2000 to Sql server 2005, any body can help me to do the replication from Sql server 2000 to sql server 2005.

Thanks,

Sahir

kk999 Grasshopper Points: 17 More actions · Answer 8

Error may be as follows: "SQL Server replication does not support nicknames, .........."