SQL Replication Publisher 2000 with Subscriber and Distributor 2008
Hi
I am trying to setup Replication between SQL 2000 and SQL 2008.
I'm trying replication topology:
Publisher SQL 2000 SP4
Distributor and Subscriber SQL 2008
This topology should work according to docs.
But the snapshot agent fails to start I get login error. The distributor cannot connect to publisher. See attached.
We are testing with domain admin account so security should not be an issue.
I tried another topology to get more information
Publisher SQL 2000 SP4
Distributor SQL 2008
Subscriber SQL 2005 SP2
We get same error.
This topology worked
Publisher SQL 2005 SP2
Distributor SQL 2008
Subscriber SQL 2005 SP2
Then I read in the article:
Using a SQL Server 2005 or SQL Server 2008 Distributor with a Publisher Running SQL Server 2000
SQL Server 2005 and SQL Server 2008 can be used as a remote Distributor for Publishers that are running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures let you change properties that were introduced in SQL Server 2005:
sp_MSchange_snapshot_agent
sp_MSchange_logreader_agen
sp_MSchange_distribution_a
sp_MSchange_merge_agent_pr
And from there I'm a little lost I must say. I am not a DBA :l
What properties would I have to change to allow the topology with SQL 2000 Publishing? (If this is the solution at all)
Any ideas?
Thanks
CM
I am trying to setup Replication between SQL 2000 and SQL 2008.
I'm trying replication topology:
Publisher SQL 2000 SP4
Distributor and Subscriber SQL 2008
This topology should work according to docs.
But the snapshot agent fails to start I get login error. The distributor cannot connect to publisher. See attached.
We are testing with domain admin account so security should not be an issue.
I tried another topology to get more information
Publisher SQL 2000 SP4
Distributor SQL 2008
Subscriber SQL 2005 SP2
We get same error.
This topology worked
Publisher SQL 2005 SP2
Distributor SQL 2008
Subscriber SQL 2005 SP2
Then I read in the article:
Using a SQL Server 2005 or SQL Server 2008 Distributor with a Publisher Running SQL Server 2000
SQL Server 2005 and SQL Server 2008 can be used as a remote Distributor for Publishers that are running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures let you change properties that were introduced in SQL Server 2005:
sp_MSchange_snapshot_agent
sp_MSchange_logreader_agen
sp_MSchange_distribution_a
sp_MSchange_merge_agent_pr
And from there I'm a little lost I must say. I am not a DBA :l
What properties would I have to change to allow the topology with SQL 2000 Publishing? (If this is the solution at all)
Any ideas?
Thanks
CM
lcohan
Why can't you keep the Distribution on the same SQL as the Publisher I mean on the SQL 2000 SP4?
ASKER
We don't want to put more load on the SQL 2000.
That is why my topology has to be the one I explained.
Thx
That is why my topology has to be the one I explained.
Thx
I thought that would be the answer but how then you tested
<<
This topology worked
Publisher SQL 2005 SP2
Distributor SQL 2008
Subscriber SQL 2005 SP2
>>
and why the SQL 2000 which BTW is long ago not supported can't be upgraded to a more recent supported version? And let me guess <1try:)> the application/system using the back end SQL database(s) cannot be modified for some (misteriously or too much not worth it effort...) reason so...
My guess the error you'r getting is because the NT login THEPALACEUS\PALACE57$ (I gues this is the SQL 2008 Agent startup account) does not have sufficient rights in your SQL 2000 (maybe does not even exists) for whatever reason so Distribution Agent SQL job running as that NT login can't connect back to SQL 2000.
Another option may be to try to create a SQL Login and use it instead of NT login like described here:
https://support.microsoft.com/en-us/kb/321822
<<
This topology worked
Publisher SQL 2005 SP2
Distributor SQL 2008
Subscriber SQL 2005 SP2
>>
and why the SQL 2000 which BTW is long ago not supported can't be upgraded to a more recent supported version? And let me guess <1try:)> the application/system using the back end SQL database(s) cannot be modified for some (misteriously or too much not worth it effort...) reason so...
My guess the error you'r getting is because the NT login THEPALACEUS\PALACE57$ (I gues this is the SQL 2008 Agent startup account) does not have sufficient rights in your SQL 2000 (maybe does not even exists) for whatever reason so Distribution Agent SQL job running as that NT login can't connect back to SQL 2000.
Another option may be to try to create a SQL Login and use it instead of NT login like described here:
https://support.microsoft.com/en-us/kb/321822
When you build the replication you should choose your own login if it has the proper permissions instead of the instance default login.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Issue was not resolved with comments and had to find an alternate which is the route i'm taking to fix the issue.