Re: Transactional replication from SQL Server 2005 to SQL Server 7 - how?

Thanks Raymond, your reply makes perfect sense and I'll look at making those
changes today.

Dan


Raymond wrote on Mon, 27 Feb 2006 12:21:27 -0800:

Hi Daniel,

It pains me quite a bit to say that replicating between different versions
of SQL Server (using transactional\snapshot replication at least) does
require a fair amount of forethought and intervention from the end-user
(the larger the version difference, the greater the effort required) today
even though our documentation states that your configuration (2005->7) is
supported (well, it is certainly possible... within limits). One of the
main reasons why customers like yourself have to go through such painful
experience is that we need to strike a very delicate balance between the
conflicting needs of supporting new features in newer versions of SQL
Server and maintaining backward compatibility with downlevel subscribers.
Unfortunately, compromises need to be made one way or the other thus
making virtually all customers unhappy about something. Now, to answer
some of your inquiries in your post:

1) & 2) are basically the result of a conscious decision of not supporting
SQL Server 7.0 from the SQL2005 management tools but I would encourage to
file a design change request (DCR) at the MSDN product feedback center
just so this gets a chance to be reconsidered in a future service pack. In
the mean time, configuring a subscription to your 7.0 subscriber using
T-SQL commands would be the most convenient option.

3) By default, SQL2005 Management Studio includes copying column-level
collations for table articles as part of the article schema options. As
column-level collation is not supported by SQL Server 7.0, you would need
to exclude the option by changing the article properties through the UI or
use sp_changearticle to exclude 0x1000 from your article schema options.
As a precautionary measure, I would also like to mention that user-defined
data types are schema-qualified in SQL2005 but not in any previous
versions, and while there are undocumented options to tell the snapshot
agent to script the user-defined data types without schema qualification,
I would recommend that you simply enable the 0x20 schema option to convert
all user-defined data types to the corresponding base types if you try to
replicate from SQL2005 to any lower version subscribers.

4) By default, primary keys are replicated as unique indexes if you
configure replication using the SQL2000 Enterprise Manager. To ensure that
primary keys are replicated as primary keys to your SQL2005 subscriber,
you can either check the "Include declarative referential integrity " box
on the article property *** or simply enable the 0x2000
(PrimaryKeyUniqueKeyAsConstraints) schema option using sp_changearticle.
If you plan to reinitialize your SQL2000->SQL2005 subscription in the
future, I would encourage you to spend some time understanding the
implications of doing so and make the appropriate adjustments to your
replication configuration.

Sorry for the rather long-winded post, I certainly hope that the
information I have given above proves useful to you.

-Raymond

"Daniel Crichton" <msnews@xxxxxxxxxxxxxxxxxx> wrote in message news:eJ6sv07OGHA.1180@xxxxxxxxxxxxxxxxxxxxxxx
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


.