Linking to SQL db not working correctly

Linking to SQL db not working correctly

Post by Francisco Tapi » Thu, 26 Jul 2001 02:13:40



I want to start using the Linking feature in SQL, where we have 2 SQL7
Servers.  What I want to do is the following...
    ServerA is in house, ServerB is visible on the internet.  When data
updates are made to ServerB, I'd like ServerA to be aware of the changes and
issue updates to that data.  Say if you have a column where data is entered
as 1+1, then ServerA could pull the data store it but also issue the answer
which in this case happens to be 2.

I've gone through BOL, but I can only link to ServerB but am unaware on how
to set up the triggers.. plus I noticed I cannot see the Stored procedures
or view etc....

--
-Francisco

 
 
 

Linking to SQL db not working correctly

Post by Michael Carrol » Thu, 26 Jul 2001 07:22:27


You can execute stored procedures on linked servers.  You can also access
all the tables and views.  You won't see them through Enterprise Manager
when looking at the linked server howerver.  I would strongly urge against
accessing a linked server through a trigger.  I think you will have some
severe performance problems with that.  Make a job to handle changed rows.
Or you might consider using Transactional Replication between the two
servers.  ServerB changes table X.  Transactional rep to ServerA.  ServerA
reads changes to table X and inserts answers into table Y.  Transactional
rep table Y to server B.  You can set transactional replication to make
changes immediately but it will handle the condition where one of the
servers is unavailable.

-Mike


Quote:> I want to start using the Linking feature in SQL, where we have 2 SQL7
> Servers.  What I want to do is the following...
>     ServerA is in house, ServerB is visible on the internet.  When data
> updates are made to ServerB, I'd like ServerA to be aware of the changes
and
> issue updates to that data.  Say if you have a column where data is
entered
> as 1+1, then ServerA could pull the data store it but also issue the
answer
> which in this case happens to be 2.

> I've gone through BOL, but I can only link to ServerB but am unaware on
how
> to set up the triggers.. plus I noticed I cannot see the Stored procedures
> or view etc....

> --
> -Francisco


 
 
 

Linking to SQL db not working correctly

Post by Francisco Tapi » Thu, 26 Jul 2001 08:03:55


"I see" said the blind man...so in the scenario I'm attempting to create,
Can I create replication between my ServerB and ServerA for only 1 table and
then via a linked server (service) execute procedures to update these
tables... the trick I'm attempting to do is to only provide data to the
external server, based on what a user login in has rights to.. such that a
user that has rights to USA would only see USA, but maybe an AU user would
log in and get only AU data... of course the client application is also
developed by us so we are executing sp's with Where, to keep the data
seperate.. :)

--
-Francisco

> You can execute stored procedures on linked servers.  You can also access
> all the tables and views.  You won't see them through Enterprise Manager
> when looking at the linked server howerver.  I would strongly urge against
> accessing a linked server through a trigger.  I think you will have some
> severe performance problems with that.  Make a job to handle changed rows.
> Or you might consider using Transactional Replication between the two
> servers.  ServerB changes table X.  Transactional rep to ServerA.  ServerA
> reads changes to table X and inserts answers into table Y.  Transactional
> rep table Y to server B.  You can set transactional replication to make
> changes immediately but it will handle the condition where one of the
> servers is unavailable.

> -Mike



> > I want to start using the Linking feature in SQL, where we have 2 SQL7
> > Servers.  What I want to do is the following...
> >     ServerA is in house, ServerB is visible on the internet.  When data
> > updates are made to ServerB, I'd like ServerA to be aware of the changes
> and
> > issue updates to that data.  Say if you have a column where data is
> entered
> > as 1+1, then ServerA could pull the data store it but also issue the
> answer
> > which in this case happens to be 2.

> > I've gone through BOL, but I can only link to ServerB but am unaware on
> how
> > to set up the triggers.. plus I noticed I cannot see the Stored
procedures
> > or view etc....

> > --
> > -Francisco

 
 
 

Linking to SQL db not working correctly

Post by Michael Carrol » Thu, 26 Jul 2001 09:13:58


So are there multiple client sites then?  One thing I can definately
recommend is allways run you select statement on the remote machine.
Inserts to a remote machine are much slower than a select from a remote
machine.
So this is good:
INSERT INTO local_table
SELECT * FROM remotesrv.db.dbo.table

this is very bad for performance:
INSERT INTO remotesrv.db.dbo.table
SELECT * FROM local_table

I'm not exactly sure what the big difference is but the first method is the
only realistic choice for performance.

It sounds like you need all data on your external server and then filter it
there.  Unless only one type of user will be logging in at each external
server.  One thing to keep in mind is that linked server operations are not
very fast at all so you shouldn't plan on using the for on demand queries.
Update statements are probably the slowest over linked servers.  We only use
selects and insert/selects for our data moving.  We then process the
deletes, updates, etc. locally.


> "I see" said the blind man...so in the scenario I'm attempting to create,
> Can I create replication between my ServerB and ServerA for only 1 table
and
> then via a linked server (service) execute procedures to update these
> tables... the trick I'm attempting to do is to only provide data to the
> external server, based on what a user login in has rights to.. such that a
> user that has rights to USA would only see USA, but maybe an AU user would
> log in and get only AU data... of course the client application is also
> developed by us so we are executing sp's with Where, to keep the data
> seperate.. :)

> --
> -Francisco


> > You can execute stored procedures on linked servers.  You can also
access
> > all the tables and views.  You won't see them through Enterprise Manager
> > when looking at the linked server howerver.  I would strongly urge
against
> > accessing a linked server through a trigger.  I think you will have some
> > severe performance problems with that.  Make a job to handle changed
rows.
> > Or you might consider using Transactional Replication between the two
> > servers.  ServerB changes table X.  Transactional rep to ServerA.
ServerA
> > reads changes to table X and inserts answers into table Y.
Transactional
> > rep table Y to server B.  You can set transactional replication to make
> > changes immediately but it will handle the condition where one of the
> > servers is unavailable.

> > -Mike



> > > I want to start using the Linking feature in SQL, where we have 2 SQL7
> > > Servers.  What I want to do is the following...
> > >     ServerA is in house, ServerB is visible on the internet.  When
data
> > > updates are made to ServerB, I'd like ServerA to be aware of the
changes
> > and
> > > issue updates to that data.  Say if you have a column where data is
> > entered
> > > as 1+1, then ServerA could pull the data store it but also issue the
> > answer
> > > which in this case happens to be 2.

> > > I've gone through BOL, but I can only link to ServerB but am unaware
on
> > how
> > > to set up the triggers.. plus I noticed I cannot see the Stored
> procedures
> > > or view etc....

> > > --
> > > -Francisco