Foreign in Snapshot Replication

When I initialized and started the snapshot agent, how can I move the table
schema along with all the foreign keys? The only way I can think of is add
the script for before and after the initialization.
Does that mean the Replication not support repicate the foreign keys?
If I have the foreign keys in each replicated table, is the transacational
replication smart enough to insert data into into the parent table first?
Thanks
Ed
Hi Ed,
You can replicate fks with the initial snapshot for transactional
replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
article schema option. There are, however a few things you need to watch out
for:
1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
case if you use concurrent snapshot, have circular references (including
self-referencing fk), or you have fks referencing from outside the
publication.
2) Even though transactional replication is suposed to replay what happened
at the publisher in the same order, an update on the primary key is
typically translated into deletes followed by inserts. This can lead to
temporary violation of fks referencing the pk and the only way to workaround
this is to make sure that your fks at the subcriber has the "NOT FOR
REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
a schema option to mark all replicated fks NFR when they are replicated to
the subscriber.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
> When I initialized and started the snapshot agent, how can I move the
> table
> schema along with all the foreign keys? The only way I can think of is
> add
> the script for before and after the initialization.
> Does that mean the Replication not support repicate the foreign keys?
> If I have the foreign keys in each replicated table, is the transacational
> replication smart enough to insert data into into the parent table first?
> Thanks
> Ed
|||In SQL 05, there is an option if I want to replicate the foreign keys. Do I
still have to include all table Dependencies? Replication is smart enough to
know which table/foreign key needs to be created first?
The reason why i ask the question is I have a Prod and a Dev Server.
Developers always ask me to transfer all tables only to the Dev Server. I am
thinking using snapshot / transactional replication to update tables/data for
them.
That's why I would like to make sure moving the tables along with the
foreign keys is good and the replication knows the sequence of creating the
keys.
Thanks
"Raymond Mak [MSFT]" wrote:

> Hi Ed,
> You can replicate fks with the initial snapshot for transactional
> replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
> article schema option. There are, however a few things you need to watch out
> for:
> 1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
> case if you use concurrent snapshot, have circular references (including
> self-referencing fk), or you have fks referencing from outside the
> publication.
> 2) Even though transactional replication is suposed to replay what happened
> at the publisher in the same order, an update on the primary key is
> typically translated into deletes followed by inserts. This can lead to
> temporary violation of fks referencing the pk and the only way to workaround
> this is to make sure that your fks at the subcriber has the "NOT FOR
> REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
> a schema option to mark all replicated fks NFR when they are replicated to
> the subscriber.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
>
>
|||You don't need to include all objects referenced by fks in your publication,
and the fks should be created in the right order (they only have to be
created after the unique\primary keys that they reference). The snapshot
agent history will tell you which fks cannot be replicated because they
reference something not in the publication.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...[vbcol=seagreen]
> In SQL 05, there is an option if I want to replicate the foreign keys. Do
> I
> still have to include all table Dependencies? Replication is smart enough
> to
> know which table/foreign key needs to be created first?
> The reason why i ask the question is I have a Prod and a Dev Server.
> Developers always ask me to transfer all tables only to the Dev Server. I
> am
> thinking using snapshot / transactional replication to update tables/data
> for
> them.
> That's why I would like to make sure moving the tables along with the
> foreign keys is good and the replication knows the sequence of creating
> the
> keys.
> Thanks
> "Raymond Mak [MSFT]" wrote:
|||Thanks.
While I am playing with the table "Customers" and "Orders" in Northwind
database in SQL 2000, when I checked on "Include declared referential
integrity", the foreign keys still not get copied. Am I missing something?
Ed
"Raymond Mak [MSFT]" wrote:

> You don't need to include all objects referenced by fks in your publication,
> and the fks should be created in the right order (they only have to be
> created after the unique\primary keys that they reference). The snapshot
> agent history will tell you which fks cannot be replicated because they
> reference something not in the publication.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...
>
>
|||The Orders table in the Northwind database has FKs referencing the Employees
table and the Shippers table as well. In SQL2000, the snapshot agent will
simply not script any foreign key constraints from the referencing table if
*any* of the referenced tables is not part of the publication. The SQL2005
snapshot agent implements a much more granular approach of analyzing each
individual foreign key so the foreign key from Orders to Customers will be
replicated with the SQL2005 snapshot agent despite the fact that you didn't
include Employees and Shippers in your publication (you will even see in the
snapshot history messages why the FKs referencing Employees and Shippers are
not scripted). Ed, I got exactly the same question (down to the same tables)
from one of our support engineers just two days ago, did you contact
Microsoft Support for this?
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...[vbcol=seagreen]
> Thanks.
> While I am playing with the table "Customers" and "Orders" in Northwind
> database in SQL 2000, when I checked on "Include declared referential
> integrity", the foreign keys still not get copied. Am I missing
> something?
> Ed
> "Raymond Mak [MSFT]" wrote:
|||It was not me but I will play with SQL 05 to see the result.
Thanks again.
"Raymond Mak [MSFT]" wrote:

> The Orders table in the Northwind database has FKs referencing the Employees
> table and the Shippers table as well. In SQL2000, the snapshot agent will
> simply not script any foreign key constraints from the referencing table if
> *any* of the referenced tables is not part of the publication. The SQL2005
> snapshot agent implements a much more granular approach of analyzing each
> individual foreign key so the foreign key from Orders to Customers will be
> replicated with the SQL2005 snapshot agent despite the fact that you didn't
> include Employees and Shippers in your publication (you will even see in the
> snapshot history messages why the FKs referencing Employees and Shippers are
> not scripted). Ed, I got exactly the same question (down to the same tables)
> from one of our support engineers just two days ago, did you contact
> Microsoft Support for this?
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...
>
>