Replication between SQL Server 2000 and SQL Server 2005

Do you know if it is possible to synchronize a database in SQL Server 2000 SP3a (Publisher) and a database in SQL Server 2005 (Subscriber)?

Has anyone tried it? What are the most common problems you faced? Are there many differences from the Replication process from SQL Server 2000 (publisher) to SQL Server 2000 (subscriber)?

Thanks Very Much

Yes for transactional replication, no for merge replication. Since you're limited to functionality of a SQL 2000 publication, you won't gain much in terms of replication with a SQL 2005 subscriber. But if you use your subscriber for other purposes, like reporting, then you get to take advantage of SQL 2005 features there.|||

Is there anything special you are doing to setup replication between SQL Server 2000 SP3(a) and SQL Server 2005 SP1? Both are enterprise versions and I am using transactional replication.

So far, I have not been able to make replication work. Using the same SQL 2K database and replicationg to another SQL 2K database, replication worked fine.

I just cannt seem to get SQL 2K to publish and SQL 2L5 to subscribe.

Is there something I am overlooking?

Thanks,

Roy

|||Is there an error you're getting?|||

Not sure if this is the issue you hit:

Make sure to use SQL 2005 Management studio to create publication and add subscription for SQL 2000 publisher, you can not connect to SQL 2005 from SQL 2000 Enterprise manager thus will not be able to add SQL 2005 subscription from SQL 2000 enterprise manager.

Or you can TSQL script to setup replication programmatically.

Thanks,

Zhiqiang Feng

|||

Does the SQL Server 2005 server have o be the distributor? I tried creating the distributor using SS 2K5 and was messaged that a subscription could not be created by 2005 on an older (2000) server.

Am I supposed to create the distributor and publisher using 2000 and the subscriber using 2005?

I really appreciate any and all help/advice

Roy

|||

For transactional replication, you can have SQL 2000 sp3+ publisher/distributor with SQL 2005 subscriber. But for merge replication, you must have distributor >= publisher >= subscriber, from your original posting, seems like you're using transactional replication, right?

By the way, what's the version number of your SQL 2000 publisher? (8.00.760 or up?)

Can you describe step by step what you did and exactly what error you're getting?

I just went through the process of setup SQL 2000 publisher & distributor from SQL 2005 management studio and didn't hit any issues.

Thanks,

Zhiqiang Feng

|||

MY SQL Server 2000 version number is 8.0.818

I went to the publisher server (mase), it is SQL2K and created the distributor, then the publisher. However, one thing that makes me wonder is having to check the box Server SQL Server 2000 on the form that is requesting what type of server will be subscribing. Obviously, 2K came before 2005 and did not have a box for 2005. Is the 2000 check box what I need to check?

I will post the rest of the the steps as I go through them.

Thanks a lot for your help.

Roy

|||

If you create SQL 2000 tran publication from SQL 2000 Enterprise manager, yes, it doesn't know SQL 2005 so you have to pick the SQL 2000 option for subscription, but that doesn't prevent you from adding a SQL 2005 subscriber from SQL 2005 mgmt studio which is officially supported. However, if you create SQL 2000 tran publication from SQL 2005 mgmt studio (use mgmt studio connecting to the remote SQL 2000 box), you will not see that prompt. That's why I recommend you setup everything from SQL 2005 management studio for the SQL 2000 publisher/distributor.

Thanks,

Zhiqiang Feng

|||

When I attempt to create a publisher using SS2K5, on SS 2K, I get a message saying the server 'mase' is an older version of SQL Server and cannot be the Distributor for server 'sql2'.

Maybe this is my problem, does the distributor need to be on SS2K5?

Also, to create a publisher on a SS2K server using SS2K5 Management Studio do you right clisck on Local Publication?

I created the subscriber using SS2K5 and all looks good. When I right click the subscriber and click View Synchronization Status I get this message:

The job failed. The Job was invoked by User sa. The last step to run was step 1 (Run agent.).

When I look at History I see this message: Message
Unable to start execution of step 1 (reason: Error authenticating proxy PMS\svcsqladmin, system error: Logon failure: unknown user name or bad password.). The step failed.

I know the password is correct for this login as we tested it just before attempting to create replication.

Is there anyyhnig I am doing incorrectly? I would love to get this set up. On top of everything else, this is the first replication I have ever tried to configure.

Thanks again,

Roy

|||

hi,

right now I facing the same problem.

you have said that the only thing that work is using transactional replication.

the problem is my db don't have primary key.

do you have other option, so we can have the same or at least munites delay database.

please advice

thanks