relational integrity and transactional replication ??

Hi Friends,
I have a db with more than 350 table, most of them have Relational Integrity
with PK and FK
we want to have this DB replication to another server.
What are the potential problems and solutions?
I had experienced problems and conflicts if db has Relational Integrity.
Thanks in advance,
Pat
Patrick,
what type of replication are you considering? In particular, are the
subscribers to be updating ones?
What problems have you experienced in the past?
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You may consider using the "NOT FOR REPLICATION" clause on your RI
definitions. The idea is that the integrity has already been enforced at the
subscriber or publisher so there is no need to enforce it again.
This will allow you to publish articles in multiple publications without
having to worry about the order that replication tries to inesert records.
"Patrick" wrote:

> Hi Friends,
> I have a db with more than 350 table, most of them have Relational Integrity
> with PK and FK
> we want to have this DB replication to another server.
> What are the potential problems and solutions?
> I had experienced problems and conflicts if db has Relational Integrity.
> Thanks in advance,
> Pat
>
>

Relation in transactional Replication

I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

Relation in transactional Replication

I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper
> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

Relation in transactional Replication

I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

Related tables with different publications

My database contains tables related together by foreign keys (Not for
Replication), and I want to design a transactional replication with queued
updating option between them, can I put each related table on a different
publication?
Saeed,
the general recommendation is to have the related articles in the same
publication, to ensure the same processing order is applied at the
subscriber. Is there any particular reason you want to split the publication
into two?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
our database is too huge, we have tables with millions of records, so if any
problem occured to any table or adding new column(s), we just want to start
reinitilize the effected table. not all related tables to minimize the
efforts on both the database and the network.
Thanks
"Paul Ibison" wrote:

> Saeed,
> the general recommendation is to have the related articles in the same
> publication, to ensure the same processing order is applied at the
> subscriber. Is there any particular reason you want to split the publication
> into two?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||OK - then you could have different publications and ensure
that they synchronize in the correct order.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Reinitializing transactional subscribers

In SQL2000, SP3, We want to periodically refresh our read-only
subscribers with the latest schema and data (including FK's, indexes,
etc) whenever we role out a new software release. The database is
relatively small and we can currently disable publishing\remove
subscribers, and add them back in about 15 minutes. As the database
grows, I'm not sure if this would be the best method of synchronizing
the subscribers, but management likes the idea of a complete snapshot.
Is there a typical approach to updating subscribers with all
differences that makes more sense? The subscribers are primarily for
Disaster Recovery, but are also used to reduce the need for our
European customers to come to the US for reads. When I tried the
"start synchronizing" option in the EM gui, it has difficulty
dropping/replacing tables because it can't drop tables w FK's.
Also, can anyone explain the difference between reinitializing and
synchronizing?
thx much!
Gary,
if you have a need to drop FKs then it is possible to apply a prescript
before the snapshot is applied (botom of snapshot tab on publication
properties).
Some schema changes can be carried out using the 'plumbing' of replication,
eg sp_repladdcolumn and sp_repldropcolumn. Some other changes eg addition of
an index, can be done using sp_addscriptexec.
As for the difference between initialization and synchronization, right at
the start when the snapshot is sent to the subscriber there isn't really
one. Once the snapshot has been applied, sending down transactions in the
case of transactional replication is referred to as synchronization, and
reinitialization would refer to a new snapshot being created and propagated.
HTH,
Paul Ibison
|||Thanks Paul... But rather than running a pre-script, and individual
system sp's, do you know if this methodology - dropping/re-establishing
replication as new releases are rolled out is a common approach to
keeping things in-sync?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I have seen it used, but sp_droparticle and sp_addarticle, along with
sp_repladdcolumn and sp_repldropcolumn are more common for small changes.
Regards,
Paul

Reinitializing Push subscription after it expired in Transactional Replication

I am using SQL 2000. How can I get my transactional replication reinitialized after it has failed with several attempts.

I know one way of doing it through enterprise manager and specifying the subscription to reinitialize. But this will apply the snapshot and will take long time.

Is there a fast way of doing it?

-Nipul

A reinitialize requires reapplying the snapshot. Can you be more clear what you're trying to accomplish? I think what you want to do is just restart the distribution agent, does this work?

Reinitialize subscriptions in transactional replication

If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.

If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken

If I do

Reinitialize subscriptions
start the snapshot agent
start the distribution agent

Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.

Any help is appreciatedIt will drop every article the subscriber subscribes and refresh the DDL and data from the publication.

reinitialization of subscriptions

If I have a subscription to a publication (transactional)
and the Distribution Agent tells me I need to
reinitialize.
What are the steps to do this?
Do I need to delete all the data on the subscriber? If
so, is there a way around this? If not, what is the best
practice of accomplishing this?
What if there are changes made at the subscriber do I
loose them if the subscription is reinitializatized?
Thanks...
Jimmy,
reinitialize means apply a new snapshot, so, you'll need to run the snapshot
agent first then run the distribution agent. When you say will it delete all
the data on the subscriber, I'm not sure why this is a concern. For
transactional replication, the subscriber data is treated as RO data. If
however someone has edited the data, then you could use binary checksums to
see what has changed and then do the same changes on the publisher. If the
subscriber is using updatable transactional replication then the changes are
already on the publisher. If you're using a queue, then be sure to run the
queue reader to send up the changes.
HTH,
Paul Ibison
|||expand your publishing database, right click on your publication, select
properties, click on your subscriber tab, highlight your subscriber, and
click the reinitialize button.
It will delete all the data of the replication objects and their schemas and
then replace them.
"Jimmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9dc801c43447$43f3a5a0$a601280a@.phx.gbl...
> If I have a subscription to a publication (transactional)
> and the Distribution Agent tells me I need to
> reinitialize.
> What are the steps to do this?
> Do I need to delete all the data on the subscriber? If
> so, is there a way around this? If not, what is the best
> practice of accomplishing this?
> What if there are changes made at the subscriber do I
> loose them if the subscription is reinitializatized?
> Thanks...
|||Paul,
You said...
then you could use binary checksums to see what has
changed and then do the same changes on the publisher
How?
Larry...

>--Original Message--
>Jimmy,
>reinitialize means apply a new snapshot, so, you'll need
to run the snapshot
>agent first then run the distribution agent. When you
say will it delete all
>the data on the subscriber, I'm not sure why this is a
concern. For
>transactional replication, the subscriber data is
treated as RO data. If
>however someone has edited the data, then you could use
binary checksums to
>see what has changed and then do the same changes on the
publisher. If the
>subscriber is using updatable transactional replication
then the changes are
>already on the publisher. If you're using a queue, then
be sure to run the
>queue reader to send up the changes.
>HTH,
>Paul Ibison
>
>.
>
|||Larry,
there's a good explanation of BINARY_CHECKSUM in books online (BOL). If you
have any problems with the explanation, please post back.
Regards,
Paul Ibison