FK and replication

(previously posted to another SQL group; no answer so far)
We are looking into how to do replication on a SQL Server 2000 db, for
disaster recovery. One thing we found was that FK contraints won't replicate
well. So we decided to drop the FK constraint, then add it back with a bit
of code that says it is not for replication.
We got this idea from someone who is using DB2 or Oracle (I can't remember
which). Will this work on SQL Server as well?
Here's my code (with some table and field names replaced for this post):
ALTER TABLE tablename DROP CONSTRAINT FKwhatever
ALTER TABLE tablename
ADD CONSTRAINT FKwhatever
FOREIGN KEY (REGIONID)
REFERENCES whatever NOT FOR REPLICATION
The article properties, snapshot tab gives you the option to take DRI. This only works if the related articles are in the same publication. If you use this, there will be no need to run other scripts. If you don't use this, then you could do the same thin
g in postscripts or sp_addscriptexec. "Yes, Not For Replication" has specific uses, and you may or may not use it for DR (I wouldn't as then it's fewer things to change afterwards). Note that there are many differences to log shipping which is a truer DR
technology: you'll loose defaults, users, permissions and user defined datatypes. All these will need taking into account.
Regards,
Paul Ibison
|||Thanks, but to be honest, I didn't understand much of what you said. Do you
have a link to some basic tutorial on this? I am not a DBA, BTW.
I should add that I just found out that the customer who's doing this is
doing it in real time.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D89471D5-134D-4DBD-84F7-B0EE73400E8C@.microsoft.com...
> The article properties, snapshot tab gives you the option to take DRI.
This only works if the related articles are in the same publication. If you
use this, there will be no need to run other scripts. If you don't use this,
then you could do the same thing in postscripts or sp_addscriptexec. "Yes,
Not For Replication" has specific uses, and you may or may not use it for DR
(I wouldn't as then it's fewer things to change afterwards). Note that there
are many differences to log shipping which is a truer DR technology: you'll
loose defaults, users, permissions and user defined datatypes. All these
will need taking into account.
> Regards,
> Paul Ibison
|||The Not for Replication (NFR) option is well explained in Books Online
(BOL), as well as the whole of replication stuff.
Briefly:
1. If you have a DRI, you can't insert a FK row where the PK row does not
exist yet.
2. The NFR option means that the DRI is not enforced for replication only.
In other words, if Foreign Key data goes to susbscriber before the Primary
Key, then the FK data is allow to be inserted at the subscriber, even though
the PK row is not there yet. If you do not specify this option then you end
up with a conflict.
If you want your DRIs to be replicated, you will have to specify it in the
article properties. Again, please have a look in BOL.
Raj Moloye
|||What's DRI?
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:u8TOJRcEEHA.3568@.tk2msftngp13.phx.gbl...
> The Not for Replication (NFR) option is well explained in Books Online
> (BOL), as well as the whole of replication stuff.
> Briefly:
> 1. If you have a DRI, you can't insert a FK row where the PK row does not
> exist yet.
> 2. The NFR option means that the DRI is not enforced for replication only.
> In other words, if Foreign Key data goes to susbscriber before the Primary
> Key, then the FK data is allow to be inserted at the subscriber, even
though
> the PK row is not there yet. If you do not specify this option then you
end
> up with a conflict.
> If you want your DRIs to be replicated, you will have to specify it in the
> article properties. Again, please have a look in BOL.
> Raj Moloye
>
|||Middletree,
there's loads of good info in Books On Line (BOL). Look for the "replication, overview" section.
As for tutorials, have a look at the links in www.replicationanswers.com. At the top I've listed some tutorials I've come across. There are no specialist replication books as yet, but you'll find simple tutorials in most Administration books.
Ask as many questions on this newsgroup as you like - understanding this technology is important to making the correct business choice and there's plenty of experienced people willing to help.
Regards,
Paul Ibison
ps you can't get real-time synchronization, but using transactional replication you'll get pretty close to it, if you minimise the POLLINGINTERVAL parameter (perhaps add another post about optimization later).
|||Sorry, I was a bit lazy to write the longer definition.
DRI=Data Referential Integrity (this is the equivalent for FK Constraint)
Raj Moloye
"middletree" <middletree@.htomail.com> wrote in message
news:ea0UQTcEEHA.2576@.TK2MSFTNGP11.phx.gbl...
What's DRI?
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:u8TOJRcEEHA.3568@.tk2msftngp13.phx.gbl...
> The Not for Replication (NFR) option is well explained in Books Online
> (BOL), as well as the whole of replication stuff.
> Briefly:
> 1. If you have a DRI, you can't insert a FK row where the PK row does not
> exist yet.
> 2. The NFR option means that the DRI is not enforced for replication only.
> In other words, if Foreign Key data goes to susbscriber before the Primary
> Key, then the FK data is allow to be inserted at the subscriber, even
though
> the PK row is not there yet. If you do not specify this option then you
end
> up with a conflict.
> If you want your DRIs to be replicated, you will have to specify it in the
> article properties. Again, please have a look in BOL.
> Raj Moloye
>
|||thanks!
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:OoZe$YcEEHA.3672@.TK2MSFTNGP09.phx.gbl...
> Sorry, I was a bit lazy to write the longer definition.
> DRI=Data Referential Integrity (this is the equivalent for FK Constraint)
> Raj Moloye
>
> "middletree" <middletree@.htomail.com> wrote in message
> news:ea0UQTcEEHA.2576@.TK2MSFTNGP11.phx.gbl...
> What's DRI?
>
> "Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
> news:u8TOJRcEEHA.3568@.tk2msftngp13.phx.gbl...
not
only.
Primary
> though
> end
the
>
>
|||This looks like a great resource; Thanks!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:15C068AB-E0A3-4DD1-A663-D8CB65EF0AC9@.microsoft.com...
> Middletree,
> there's loads of good info in Books On Line (BOL). Look for the
"replication, overview" section.
> As for tutorials, have a look at the links in www.replicationanswers.com.
At the top I've listed some tutorials I've come across. There are no
specialist replication books as yet, but you'll find simple tutorials in
most Administration books.
> Ask as many questions on this newsgroup as you like - understanding this
technology is important to making the correct business choice and there's
plenty of experienced people willing to help.
> Regards,
> Paul Ibison
> ps you can't get real-time synchronization, but using transactional
replication you'll get pretty close to it, if you minimise the
POLLINGINTERVAL parameter (perhaps add another post about optimization
later).
>