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