Post reply

Replication using ''NOT FOR REPLICATION'' option

  • March 15, 2007 at 5:49 am

    #171785

    Hello all,

    I have a database without referential integrity defined between its tables (no, it is not my call).

    This database is on a pubilsher.

    The subscriber has a subset of the tables of that database; the subset does have referential integrity defined.

    My question: is it still possible to use the "NOT FOR REPLICATION" option?

  • March 21, 2007 at 11:27 pm

    #696303

    NOT FOR REPLICATION for what? What type of objects are you wanting to use this option on?

    Identity Columns, Triggers or Foreign Keys?

    Kindest Regards,

  • March 22, 2007 at 2:57 am

    #696318

    Trigger,

    The option would be used for the foreign keys. But the thing is: there are no referential integrity relationships set on the published tables, while the subscriber tables would have referential integrity relationships.

    regards,

    Hans

  • March 22, 2007 at 7:16 pm

    #696520

    So what you are syaing is that the proposed Replication strategy you have in mind will be 2 Database of different design?

    NO NO NO! You will get yourself into lots of issues with this in mind.

    Best practice for any Replication model is to have an initial Database snapshot of the Publishing Database to all Subscribers regradless of which Replication Model you choose!

    Please elaborate on what you are trying to achieve.

    Kindest Regards,

  • March 23, 2007 at 8:39 am

    #696626

    The situation is as follows:

    We have a database (database A) with about thousand tables (it is a financial database).

    We are developing an application that uses five tables out of these thousand.

    We cannot directly access these tables, so we want to create a subset of 5 tables in a separate database (database B). The structure is the same, except the fact that we like to build our application on top of a database that uses referential integrity.

    We cannot force the referential integrity requirement on database A, but we still would like to use replication to get updates for the 5 tables. Database A would be the publisher, database B the subscriber.

    The 5 tables have parent-child relationships, and I am afraid that will cause problems during replication.

  • March 23, 2007 at 1:23 pm

    #696700

    I see your problem.  Have you thought of replicating the data and then using some type of ETL process to move the data to the tables with referential integrity.  However, one question, if it is not set up with referential integrity now, how will you know which records should be joined?

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • March 26, 2007 at 2:48 am

    #696820

    One issue is that the referential integrity of the database on the publisher is arranged via code (originally, the application used a propriety database; only later it has been setup for SQL Server also). The developer of the application does not allow referential integrity arranged via SQL Server.

    The subscriber tables are used by our own application, and we do allow referential integrity, but are not sure how replication will turn out to work when our publisher does not use it.

  • March 26, 2007 at 6:48 pm

    #697027

    It appears that your RI requirements, needs & Implemenattion is rather complex, so I would suggest you use another form to copy data rather than use Replication.

    I can see you getting into lots of setup issues & replication issues with your unique Databases/Applications.

    Kindest Regards,

  • March 28, 2007 at 3:16 am

    #697337

    Thank you for the help Trigger,

    we will have another look to see how we can get around this issue, indeed perhaps choosing for another solution to copy the data.

    regards,

    Hans

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply