I have two identical (schema/data) databases, one on SQL 2005 the other =
SQL 2000. They communicate over a VPN (T-1).
Different sets of apps on both ends read and write data to their =
respective subset of tables in their local database.=20

There is however an intersect of tables (2% or so) on which both =
applications rely.=20
All but 2 of these tables in the intersect are written to on one side =
and read from on the other side.
2 tables are written to and read from by applications on both sides.

I thought I would use Transactional Replicatoin and take all tables on =
each side that are written to (and read from on the other side) and =
create a publication/push subscription.
So both the 2005 and 2000 SQL Servers would be a distributor and each =
have a publication containing the tables written to on that side and do =
a push subscription.
This way when a write occurs (insert or update) to a table in this =
intersect on one side, it would replicate to the other side for the apps =
that read it.

So I went to configure this (locally first, not across the VPN).
First I started on the 2005 side. i setup the publication (articles and =
filters) then on to the subscription.=20
But when the initial snapshot tries to run, I get all these errors =
saying these tables have Foreign Keys and cannot be dropped.
It makes sense to me that if a table has dependencies (even if those =
dependencies are not in the intersect) they must also be included in the =
publication/subscription but it almost seems like there also needs to be =
an order specified or something.=20

Do all dependencies have to be included?
What is the proper way to handle this foreign key problem?

Also any other suggestions that would help me achieve what I have =
outlined would be greatly appreciated.

Thanks all!

Hi Terry,

The foreign key references should be handled in a better manner if your =
subscriber is SQL2005 as well, but it shouldn't be broken as badly as =
you have described unless:

1) You have circular FK references among your articles (there is no =
"correct" drop order possible) The snapshot agent will log a message to =
the MSsnapshot_history table at the distribution database if it detects =
a cycle.
2) You have different FKs at the subscriber (dependency ordering is =
analyzed based on publisher's configuration)
3) You have FKs referencing from tables outside of the publication to =
tables in your publication at your SQL2000 subscriber

If none of the above applies to you, I would be interested to learn more =
about your scenarios. I am also curious to know why you don't simply =
start off with an empty database at your SQL2000 subscriber if you =
choose to use "drop" as the article pre-creation anyway. Furthermore, =
dependency analysis is kind of inadequate in the SQL2000 snapshot agent =
so you will likely run into more problems going from SQL2000 to SQL2005. =
If possible, you should probably setup your SQL2005 server as the =
distributor of the SQL2000 publisher so the SQL2005 snapshot agent will =
be used for the SQL2000 to SQL2005 direction as well.

-Raymond
[quoted text, click to view]
I have two identical (schema/data) databases, one on SQL 2005 the =
other SQL 2000. They communicate over a VPN (T-1).
Different sets of apps on both ends read and write data to their =
respective subset of tables in their local database.=20

There is however an intersect of tables (2% or so) on which both =
applications rely.=20
All but 2 of these tables in the intersect are written to on one side =
and read from on the other side.
2 tables are written to and read from by applications on both sides.

I thought I would use Transactional Replicatoin and take all tables on =
each side that are written to (and read from on the other side) and =
create a publication/push subscription.
So both the 2005 and 2000 SQL Servers would be a distributor and each =
have a publication containing the tables written to on that side and do =
a push subscription.
This way when a write occurs (insert or update) to a table in this =
intersect on one side, it would replicate to the other side for the apps =
that read it.

So I went to configure this (locally first, not across the VPN).
First I started on the 2005 side. i setup the publication (articles =
and filters) then on to the subscription.=20
But when the initial snapshot tries to run, I get all these errors =
saying these tables have Foreign Keys and cannot be dropped.
It makes sense to me that if a table has dependencies (even if those =
dependencies are not in the intersect) they must also be included in the =
publication/subscription but it almost seems like there also needs to be =
an order specified or something.=20

Do all dependencies have to be included?
What is the proper way to handle this foreign key problem?

Also any other suggestions that would help me achieve what I have =
outlined would be greatly appreciated.

Thanks all!