Crossed transactional replication

Say we have office1 and office2, each has a copy of the database. We
want changes on one to be reflected ASAP on the other. And we have
the luxury of knowing that about 99.44% of the time, the two offices
work on separate accounts, so collisions, while not impossible, are
going to be possible on only a few tables and will happen very
infrequently, and say further that their resolution will be easy.
What I'd LIKE to do is set up unidirectional transactional replication
from #1 to to #2, and separately from #2 to #1. Will this even begin
to work, or will SQLServer forbid a publisher from also being a
subscriber to the same table? Yes, I could try it. No, I haven't
noticed it in BOL.
Now, it seems this would produce a looping situation, where an update
on #1 will cause an update on #2, which will then cause a redundant
update on #1, etc. UNLESS the replagent on the subscriber was smart
enough to turn on the "not for replication" flag automagically before
doing its thing. Is that logically correct? Is the capability
available? And if so, would it still be smart enough to detect
collisions if they occurred?
I think we have a pretty common scenario so maybe someone (or many
someones) is already doing it, but I am totally unclear from BOL as to
whether it's possible to solve directly with current tools. I can see
some home-grown ways to work around things, but they are necessarily
more complex than if the tool can do the job in the first place.
Thanks.
Josh
See "immediate updating option, overview" in BOL.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:i9c5f11pn844ka549jb4hcgduuoae6m77t@.4ax.com...
> Say we have office1 and office2, each has a copy of the database. We
> want changes on one to be reflected ASAP on the other. And we have
> the luxury of knowing that about 99.44% of the time, the two offices
> work on separate accounts, so collisions, while not impossible, are
> going to be possible on only a few tables and will happen very
> infrequently, and say further that their resolution will be easy.
> What I'd LIKE to do is set up unidirectional transactional replication
> from #1 to to #2, and separately from #2 to #1. Will this even begin
> to work, or will SQLServer forbid a publisher from also being a
> subscriber to the same table? Yes, I could try it. No, I haven't
> noticed it in BOL.
> Now, it seems this would produce a looping situation, where an update
> on #1 will cause an update on #2, which will then cause a redundant
> update on #1, etc. UNLESS the replagent on the subscriber was smart
> enough to turn on the "not for replication" flag automagically before
> doing its thing. Is that logically correct? Is the capability
> available? And if so, would it still be smart enough to detect
> collisions if they occurred?
> I think we have a pretty common scenario so maybe someone (or many
> someones) is already doing it, but I am totally unclear from BOL as to
> whether it's possible to solve directly with current tools. I can see
> some home-grown ways to work around things, but they are necessarily
> more complex than if the tool can do the job in the first place.
> Thanks.
> Josh
>
|||On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
>See "immediate updating option, overview" in BOL.
Yes, I have, but it seems to be an alternative to crossed replication,
not really a satisfaction of it. It depends on using DTC to go behind
the replication and have the "subscriber" work directly against the
publisher, rather than cross replications. Anyway, it also uses GUIDs
and triggers, which are two more things we'd like to avoid.
Does anybody actually do it that way?
I mean, look, what if the two offices worked on COMPLETELY distinct
subsets of data, so IF we could have crossed replication it would
never have collisions, but the replication system would still have to
know the ranges on either side. We could probably implement this with
custom SPs or DTS filters, IF the crossed replication were legal in
the first place. Perhaps it is, that's what I'm asking, until I have
the time to simply try it - but I pretty much expect something in the
system will bark at me and it won't work. Again, I think this is a
very common requirement, and it would be nice to have it work out of
the box. Yukon?
Josh
|||I think most people just use immediate updating. Probably gonna have to test
your idea to see if it works.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:0ai7f1pqhmulj1ln5v7qhumkgan7ftt3ds@.4ax.com...
> On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
> Yes, I have, but it seems to be an alternative to crossed replication,
> not really a satisfaction of it. It depends on using DTC to go behind
> the replication and have the "subscriber" work directly against the
> publisher, rather than cross replications. Anyway, it also uses GUIDs
> and triggers, which are two more things we'd like to avoid.
> Does anybody actually do it that way?
> I mean, look, what if the two offices worked on COMPLETELY distinct
> subsets of data, so IF we could have crossed replication it would
> never have collisions, but the replication system would still have to
> know the ranges on either side. We could probably implement this with
> custom SPs or DTS filters, IF the crossed replication were legal in
> the first place. Perhaps it is, that's what I'm asking, until I have
> the time to simply try it - but I pretty much expect something in the
> system will bark at me and it won't work. Again, I think this is a
> very common requirement, and it would be nice to have it work out of
> the box. Yukon?
> Josh
>
|||On Tue, 9 Aug 2005 13:23:24 -0700, "ChrisR" <noemail@.bla.com> wrote:
>I think most people just use immediate updating. Probably gonna have to test
>your idea to see if it works.
Guy at work claims he already has it going, he just had to customize
the SPs to prevent loops. When and if I actually get a look, I'll let
you know. FWIW he still had the distributors on the publishers.
J.

Cross-Domain Replication

Hi All - Need some Replication help...

I built SP's to create the Distributor/Publisher and Subscriber parts of Snapshot Replication. All run fine on 2 SQL2K installs on the SAME Domain.

BUT if I try to cross to another Domain (SQL2K as well) = NO JOY.

Error @. Subscriber doing a PULL: "Cannot connect to Distributor..."

Tried using sa, administrator, NEW Local user, etc. & continue getting the error. ALSO tried changing the Login of SQL & SQL Agent Services to the SAME Login on BOTH machines.

Help me beautiful SQL Gurus!!!

RobbieDAre the domains are trusted?|||Satya - They are SUPPOSED to be, but we haven't successfully confirmed it.

One small detail that I KNOW is related:
When adding a SQL instance in EM from the 'other' Domain, we HAVE TO use the Full Name of the server ie. Machine.Domain.com.

R|||Sounds like TRUST between domains are not properly authenticated, what are privileges and kind of account used to start SQL Services on both the machines.

Also check whether subscriber is enabled as a subscriber or chances of not impersonating the sql server agent account.

To do so In EM go to Tools | Replication |Configure Publishers, subscribers, and distributors, select the subscribers tab, ensure the check box beside your subscriber is enabled, and then click on the three ellipses beside your server. Ensure that the impersonate option is selected.|||Thanks satya.

Have set up the same LOCAL (NOT Domain) account on each Server with Admin rights. The SQL & Agent services on both boxes ALL start with that identical user.

Setup should be okay. Leaving the Publisher/Dist. the SAME, if I attempt a new subscription to another SQL install on the SAME Domain = it works.

Robbie|||I feel there is a glitch to workout the replication the SQL service account must have privilege to access the other domain's server.

Try to create an account with same name on both the domains and give required permissions (I think Admin is required) to carry on.

Then after try the replication process.|||Satya -

Have done as you suggested & still the same problem.

Other things we have tried:
1) Running DCOM utility changing launch permissions to the NEW User.
2) Changed the "Logon as Batch Job" service to the new User.

I have almost exhausted a local Consultant!!!

There was an artice on www.SSWUG.org regarding these changes, but little else we hadn't tried.

Thanks for helping|||May i dare adding my suggestion, i had the same problem. I had to setup replication b/w two different domains over the internet. The domains didn't have trust-relationship. I tried connecting the subscriber through SA and windows-users, same problem u mentioned.
I squared it away by doing this:
Made a user SQLREP (member of domain-users group) in both domains. Added the user into the local-administrator groups of both the servers(publisher and subscriber).
Checked "THE ACCOUNT IS TRUSTED FOR DELEGATION" option in the "Account-Properties" of this user at both Domain-Controllers. Note: the password for this user must be same at both ends.
SQLREP is the startup-service agent account for both the servers.
Subscriber connects to the other domain through VPN.
With this option; it allows the SQLREP user to be validated on both domains and i can access the systems(over the internet through VPN) only through this user. If i change the password of the user at any side, replication fails.
Obviously if ur domains or on LAN, exclude the VPN setup.
May be i am re-inventing the wheel!! Satya has already mentioned.

Regards!!|||You're NOT going to believe this Satya & Talat...

We just found OUR problem - which may or may not be the same for others.

We had to change the DCOM impersonations for ALL the Replication modules. Not just the Snapshot & Remote Distributer - ALL of them.

Also, we had to add the IP's to the HOSTS file of the Fully-Qualified names of all the machines involved in the Replication.

A busy operation!!!

Thanks for all your help & Happy Friday!

Robbie|||Glad you found the culprit and resolved the issue and also appreciate for posting the resolution though.

Cross owner table replication

Hello,
We have a number of tables for which we need to setup a push type
replication. In the source db, the owner of those tables is dbo, but in the
subscriber dbs, all tables must belong to a different owner (our
application requirement). As I followed replication wizard, I could not
find any option to set it up this way (I got dbo owned tables in the
subscriber dbs).
TIA,
Vitaliy
When you get to the specify article dialog box, click on the browse button
to the right of the tables and in the general tab enter the name of the user
you wish to own the tables on the subscriber in the destination owner text
box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vitalik" <address@.domain.com> wrote in message
news:%23NbuNnu7FHA.4012@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have a number of tables for which we need to setup a push type
> replication. In the source db, the owner of those tables is dbo, but in
> the
> subscriber dbs, all tables must belong to a different owner (our
> application requirement). As I followed replication wizard, I could not
> find any option to set it up this way (I got dbo owned tables in the
> subscriber dbs).
> TIA,
> Vitaliy
>

Cross Domain replication using non-static subscriber end points.

in viewing the demo about Sql Server 2005 Express Edition (SS2K5EE). One of the frames states "With SQL Server Express, you can now host information on a server and multiple users can easily access your data." My question is IF I have SQL SERVER 2005 Standard Edition (SS2K5SE) servicing subscribers SS2K5EEs Does the new achitecture between SS2K5SE and SS2K5EE support cross domain replication? I see that SS2K5EE only supports subscription replication. Can the express editions exist on stand alone computers where they are separated from SS2K5SE by the internet?
If installations of my application (each uses a SS2K5EE) are each on a computer on the internet (not static IPs) can each update itself via subsciption replication to the SS2K5SE without me having to write the plumbing code that gets the information to and from the SS2K5SE? (((Such as web service call with a big xml payload that represents data to the SS2K5SE and a whole messaging infrastructure that accomplishes inserts updates deletes, etc. )

If this is possible now with the new products can someone point to any white paper(s) that detail the how to?

I think web sync merge replication may fit into your scenario. Please refer to Books On line,

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/21f8e4d4-cd07-4856-98f0-9c9890ebbc82.htm