Prev: Delivering Replicated Transactions
Next: About Snapshot Replication
From: Andi A Andi on 21 Jun 2010 11:07
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
From: Ben Thul Ben on 21 Jun 2010 16:48
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


"Andi A" wrote:

> 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
From: Andi A on 21 Jun 2010 17:57
> 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.


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?


> * You needn't enforce any foreign key relationships at the subscriber.

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?

> * 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.

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?

> * 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!

I will set it up and play with it as you recommended.

Thank you so much for valuable response Ben!
 | 
Pages: 1
Prev: Delivering Replicated Transactions
Next: About Snapshot Replication