Newbie - Can replication do this?

[Andi A]

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

[Ben Thul]

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

Post by Andi A
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]

Post by Ben Thul
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?

Post by Ben Thul
* 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?

Post by Ben Thul
* 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?

Post by Ben Thul
* 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
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!