Newbie - Can replication do this?

Discussion in 'microsoft.public.sqlserver.replication' started by Andi A, Jun 21, 2010.

  1. Andi A

    Andi A Guest

    Hello,

    We have two separate web applications and each is using SQL 2005 severs.
    These two hosted in completely different locations and domains. We have a
    requirement that data from some tables(not all) needs to be in sync at both
    of these sites (few minutes of delay is acceptable). So, if a user enters a
    data at app1 they need to show up in app2 and same goes with updates and
    deletion. And if these tables that we are replicating has foreign key
    references and those references will not be available in second server and we
    don't want to replicate that data, what option do we have? Also, can we
    replicate Table name A from app1 to Table name B in app2? Are the column
    names and data types need to be the same at both the apps? I am new at this,
    can someone shed some light on it, point me to any helpful information, I
    really appreciate it.

    Thank you,
    Andi
     
    Andi A, Jun 21, 2010
    #1
    1. Advertisements

  2. Andi A

    Ben Thul Guest

    Hi Andi,

    It's unclear to me from what you state below whether you need uni- or
    bi-directional replication. That is, do you need data changes to flow from
    app2 back to app1? For the purposes of this discussion, I'm going to assume
    that you want unidirectional as it simplifies things.

    * You needn't enforce any foreign key relationships at the subscriber.
    * You can have differing object names at the publisher and subscriber.
    * You needn't have the same column names at the subscriber, but it'll take
    some work on your part. Specifically, you'd have to write custom stored
    procedures for each article for each of the three DML operations (insert,
    update, and delete) to accommodate this. I don't recommend it, but if it's a
    requirement, it's possible.
    * You needn't have the same data types at the subscriber, but the data types
    that you do have there need to subsume the ones for the corresponding columns
    at the publisher. That is, if you have a column that's a varchar(8) at the
    publisher, you need to have one that's at least that wide at the subscriber.
    So, an nvarchar(12) would work. Again, I advise against this as it makes
    things "tricky" (i.e. you're responsible for coming up with schema rather
    than having the scripting facilities built in to replication do it for you)

    So, the big question is "how?". Check out the various options to
    sp_addarticle. Specifically the @schema_option and @destination_table.
    Since you're new to this, I'd recommend trying to set up a "standard"
    topology first (i.e. one where most of the options are the defaults). Then,
    once you've gotten that, tweak one thing at a time until you get what you
    need. Replication can be tricky, so be patient and good things will come of
    it. Good luck!
     
    Ben Thul, Jun 21, 2010
    #2
    1. Advertisements

  3. Andi A

    Andi A Guest

    It's unclear to me from what you state below whether you need uni- or

    For now requirement is for unidirectional replication, which may change to
    bi-directional in future. If it changes to bi-directional requirement, what
    else needs to be done?

    Right now, app2 already has a Foreign key relationship some of the tables
    that need to be replication, do i need to remove the relationships? I don't
    want to lose referential integrity on the tables as the data can be entered
    into these tables directly from app2. What are the options I have in this
    case?
    is there a document to tell how to implement these custom stored procedures
    and where to add the calls to these stored procedures? Can this be done
    through the replication wizard from management studio or do i have to turn to
    T-SQL or RMO programming?
    I will set it up and play with it as you recommended.

    Thank you so much for valuable response Ben!
     
    Andi A, Jun 21, 2010
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.