Transactional replication from SQL Server 2005 to SQL Server 7 - how?
- From: "Daniel Crichton" <msnews@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Feb 2006 16:45:13 -0000
I've recently set up a new server using SQL Server 2005, and have migrated
all my public databases to it. However, I still have the old server with SQL
Server 7 and would like to use it to offload reporting from the 2005 server,
and as I've already got transactional replication running with a SQL Server
2000 database to this v7 server I thought I'd do the same with 2005. So far
I've found a few issues, and I'm stuck, so any help would be appreciated.
Server A is SQL Server 2000 Standard
Server B is SQL Server 2005 Workgroup
Server C is SQL Server 7 Standard
I want to replicate individual tables from A to B (this is already working).
I then want to replicate all tables for certain databases (which include
replicated tables from A) from B to C.
(1) SQL Server Management Studio won't connect to SQL Server 7, so there's
no way to create a push subscription via a GUI
(2) SQL Enterprise Manager can't connect to SQL Server 2005, so I can't
create a pull subscription via a GUI
(3) So I'm left with trying to do this via T-SQL. I managed to create the
subscription using sp_addsubscription, and then used
sp_addpushsubscription_agent to set up the login details for the agent.
However, I then noticed a bunch of errors in the replication monitor and
event log. It appears that most of my tables have COLLATE against the
primary key columns, which I'm assuming are something that was added by
restoring backups to SQL Server 2005. This causes problems because SQL
Server 7 doesn't support COLLATE, and so all of these tables in my
publication don't get created at the subscriber.
(4) Tables in the database that I want to replicate that are themselves
created from subscriptions don't have a primary key (they do have a
clustered index though, these were created by the SQL Server 2000
replication wizard), and so the GUI won't let me mark these for replication
from 2005 to v7. I'd rather not have to set up additional push subscriptions
on SQL Server 2000 just for these tables, I'd rather keep all the
replication for this database from Server B to Server C. Will adding a
primary key to these tables break the existing transactional replication
from Server A to Server B?
Either I'm missing something obvious, or getting transactional replication
set up isn't anywhere near as simple as it was with SQL Server 2000. Any
pointers to solving this will be greatly appreciated.
Dan
.
- Follow-Ups:
- Re: Transactional replication from SQL Server 2005 to SQL Server 7 - how?
- From: Raymond Mak [MSFT]
- Re: Transactional replication from SQL Server 2005 to SQL Server 7 - how?
- Prev by Date: Re: Problem in merge replication with one direction
- Next by Date: Re: Drop an article through EM
- Previous by thread: Re: Drop an article through EM
- Next by thread: Re: Transactional replication from SQL Server 2005 to SQL Server 7 - how?
- Index(es):
Relevant Pages
|
