Replication between SQL 2000 and 2005

Question

Replication between SQL 2000 and 2005

bessebo

Say Hey Kid

Points: 702
More actions
April 11, 2006 at 8:13 am

#167689

All I want to do is to replicate a couple of tables between one server running SQl 2000 and another running SQL 2005. First of all, can this be done and second of all, is there a lot to doing this? I am a little concerned because the SQL2000 server runs our production ERP databases. The tables to be replicated are custom tables but I am concerned that turning on replication could cause problems. I would appreciate any advice on using replication. On the one hand I really would like to have the exposure to replication but, on the other hand, I am a little leery of using it.
Any help or advice would be appreciated.

Viewing 15 posts - 1 through 15 (of 22 total)

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

Depending on the replication topology, you cannot replicatate 2000 to 2005. Here's a link to the Microsoft site which explains.

http://msdn2.microsoft.com/en-us/library/ms143241(SQL.90).aspx

bessebo Say Hey Kid Points: 702 More actions · Answer 2

I'm not sure why you say you cannot replicate 2000 to 2005. This is a direct quote from that article:

Using a SQL Server 2005 Distributor with a Publisher Running a Previous Version

SQL Server 2005 supports using a remote Distributor for Publishers running SQL Server 2000 and SQL Server version 7.0. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures allow you to change properties that are new in SQL Server 2005:

Cory-149901 Valued Member Points: 63 More actions · Answer 3

We've been replicating successfully from 2000 to 2005 for almost four months now. The only (non)issue I ran into was during the initial publisher/subscriber setup, when 2000 tries to register the 2005 server so it appears in Enterprise Manager. After the error though, the setup continues just as if you're replicating to another 2000 server. The rest has been error free.

Good luck!

Kindest Regards,

Cory

bessebo Say Hey Kid Points: 702 More actions · Answer 4

Cory,

Do you remember exactly what the error said when trying to register the 2005 server?

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 5

Cory, what type of replication?

SQL Server Versions Supported

Microsoft SQL Server version 7.0 service pack 4 (SP4) is the minimum version required to participate in a replication topology with SQL Server 2005. If you use SQL Server 2000, service pack 3 (SP3) or greater is required. If you have more than one version of SQL Server in a replication topology:

A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
A Publisher can be any version as long as it less than or equal to the Distributor version.
Subscriber version depends on the type of publication:
- A read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server version 7.0 Publisher can have SQL Server 2005 Subscribers; and a SQL Server 2005 Publisher can have SQL Server version 7.0 Subscribers.
- An updatable Subscriber to a SQL Server 2005 transactional publication can be any version equal to or greater than Microsoft SQL Server 2000 SP3.
- A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

When you replicate between different versions of SQL Server, you are often limited to the functionality of the earliest version used. For example, if you upgrade a Distributor to an instance of SQL Server 2005, but you have a Publisher running an instance of SQL Server 2000, and a Subscriber running an instance of SQL Server version 7.0, you are limited to the general functionality and replication functionality of SQL Server version 7.0.

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 6

I've had the same issue. Couldn't solve it. Working with dts now. Small tables.

Replication from 2005 to 2000 is not a problem.

Error was that Management studio is needed in sql 2000.

JV

TM-348672 Grasshopper Points: 16 More actions · Answer 7

Been there, Done that.

What you need to do is those steps:

1. Register the 2005 server inside 2000 enterprise manager. You will retrieve error that you can not work with this server (DMO problem) - I can't remmember the exactly error - click ok and continue (it will ask you to register it anyway - click on yes).

2. Create the destination DB on the 2005 server.

3. Add the subscription to the 2005 server. You cannot select the DB from list - this is the reason you create it before - so write the correct name.

That it foxs.

Hope it help you..

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Thank you friends..

Replication is working between SQL 2000 to SQL 2005.. It's rocking..

Please post incase of any new articles like this..

Regards,

Mallik.

Aubrey-314204 Say Hey Kid Points: 667 More actions · Answer 9

Thanks for the help! I just wanted to note to get the option to Click ok and continue (it will ask you to register it anyway - click on yes) you CAN NOT be in the Registration Wizard.

Thanks again!

Kokilambal SSC Rookie Points: 33 More actions · Answer 10

Hi,

I am using sql server 2000 SP(4) for Publisher and Distributor. For Subscriber i am using sql server 2005. Problem is that there is no entry in sysreplicationalert table when replication alert fires.

It is working fine if i am using both as sql server 2000.

please reply me as soon as possible.

Thanks,

Kokilambal.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 11

when i got the registration error on 2000, i just started registering all the 2000 servers via Management Studio and using SSMS to set up replication and it's been working with no errors

Ashok Bachu Grasshopper Points: 13 More actions · Answer 12

Hi,

I am trying to achieve the same thing here, but I could n't.

I am getting an error "The process could not connect to Subscriber xxxx" from the Distribution agent.

I am using sql 2000 as publisher and distributor and trying to replicate to SQL server 2005.

Thanks

Ashok

joe bailie SSC Rookie Points: 29 More actions · Answer 13

I had a simular issue with replication 2000 to 2005 across domains, I needed to add the publisher computer to the admins group on the sql2005 server. Then it worked fine. I know there is a better safer way but haven't figured that out yet.

noeld SSC Guru Points: 96590 More actions · Answer 14

Ashok Bachu (8/6/2007)
Hi,
I am trying to achieve the same thing here, but I could n't.
I am getting an error " xxxx" from the Distribution agent.
I am using sql 2000 as publisher and distributor and trying to replicate to SQL server 2005.
Thanks
Ashok

you need to register the 2005 subscribers on the 2000 distributor and then create your PULL subscriptions.

Cheers.

* Noel