Best way to update different servers

We are looking at combining some systems that would allow our Sql Server
update the other systems Sql Server system when certain events happens -
names change for instance.
Looking at using Web Services as one way of handling the Data movement
between the 2 systems.
But if they are both Sql Servers, is there some way, through the use of
Triggers for instance, that we could call a stored procedure on another
server (not at our location) to do the updates. Web Services are fine, but
we still have the push/pull/polling problem of knowing when to update System
B when System A's data changes and vice versa.
Is it best to do this outside of the Sql world or would it be reasonable to
do this from Sql to Sql which I assume would be more efficient?
Thanks,
TomThere is nothing to stop you from updating another SQL Server as a result of
an event on your SQL Server. Triggers seem to be the most obvious.
Ideally, you would have a stored proc on the linked server and then execute
something like:
MyLinkedServer.MyDB.dbo.MyProc @.ID, 'The New Name'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O5kXts3oFHA.1148@.TK2MSFTNGP12.phx.gbl...
We are looking at combining some systems that would allow our Sql Server
update the other systems Sql Server system when certain events happens -
names change for instance.
Looking at using Web Services as one way of handling the Data movement
between the 2 systems.
But if they are both Sql Servers, is there some way, through the use of
Triggers for instance, that we could call a stored procedure on another
server (not at our location) to do the updates. Web Services are fine, but
we still have the push/pull/polling problem of knowing when to update System
B when System A's data changes and vice versa.
Is it best to do this outside of the Sql world or would it be reasonable to
do this from Sql to Sql which I assume would be more efficient?
Thanks,
Tom|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtY0lv3oFHA.1444@.TK2MSFTNGP10.phx.gbl...
> There is nothing to stop you from updating another SQL Server as a result
> of
> an event on your SQL Server. Triggers seem to be the most obvious.
> Ideally, you would have a stored proc on the linked server and then
> execute
> something like:
> MyLinkedServer.MyDB.dbo.MyProc @.ID, 'The New Name'
Would you have to set up each server as a linked server on each others
machines?
Would this be the best way even if we are doing this over the Internet?
Thanks,
Tom
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O5kXts3oFHA.1148@.TK2MSFTNGP12.phx.gbl...
> We are looking at combining some systems that would allow our Sql Server
> update the other systems Sql Server system when certain events happens -
> names change for instance.
> Looking at using Web Services as one way of handling the Data movement
> between the 2 systems.
> But if they are both Sql Servers, is there some way, through the use of
> Triggers for instance, that we could call a stored procedure on another
> server (not at our location) to do the updates. Web Services are fine,
> but
> we still have the push/pull/polling problem of knowing when to update
> System
> B when System A's data changes and vice versa.
> Is it best to do this outside of the Sql world or would it be reasonable
> to
> do this from Sql to Sql which I assume would be more efficient?
> Thanks,
> Tom
>|||Would you have to set up each server as a linked server on each others
machines?

Would this be the best way even if we are doing this over the Internet?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:elYgqz3oFHA.708@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtY0lv3oFHA.1444@.TK2MSFTNGP10.phx.gbl...
> There is nothing to stop you from updating another SQL Server as a result
> of
> an event on your SQL Server. Triggers seem to be the most obvious.
> Ideally, you would have a stored proc on the linked server and then
> execute
> something like:
> MyLinkedServer.MyDB.dbo.MyProc @.ID, 'The New Name'
Would you have to set up each server as a linked server on each others
machines?
Would this be the best way even if we are doing this over the Internet?
Thanks,
Tom
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O5kXts3oFHA.1148@.TK2MSFTNGP12.phx.gbl...
> We are looking at combining some systems that would allow our Sql Server
> update the other systems Sql Server system when certain events happens -
> names change for instance.
> Looking at using Web Services as one way of handling the Data movement
> between the 2 systems.
> But if they are both Sql Servers, is there some way, through the use of
> Triggers for instance, that we could call a stored procedure on another
> server (not at our location) to do the updates. Web Services are fine,
> but
> we still have the push/pull/polling problem of knowing when to update
> System
> B when System A's data changes and vice versa.
> Is it best to do this outside of the Sql world or would it be reasonable
> to
> do this from Sql to Sql which I assume would be more efficient?
> Thanks,
> Tom
>