"Daniel Crichton" <msnews@worldofspack.co.uk> wrote in message
news:eJ6sv07OGHA.1180@TK2MSFTNGP09.phx.gbl...
> 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
>

> 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 sheet 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@worldofspack.co.uk> wrote in message news:eJ6sv07OGHA.1180@TK2MSFTNGP09.phx.gbl...
>> 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
>>