November 17, 2008 at 3:50 pm
Can Service Broker technology be used as an alternative to linked servers and what would be the pros/cons of that?
Does anyone have any experience with this that they would like to share?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 17, 2008 at 4:37 pm
Marios,
What are you trying to do? They usually perform separate actions, but if you are looking to just update another server, I might recommend looking at Service Broker.
November 18, 2008 at 8:15 am
Steve Jones - Editor (11/17/2008)
Marios,What are you trying to do? They usually perform separate actions, but if you are looking to just update another server, I might recommend looking at Service Broker.
Yes, I realize it is a strange question. I have known about Service Broker - at a high level - for some time, and never thought of it in this context. My manager talked to someone from Microsoft the other day, and the MS person suggested Service Broker as an alternative to linked servers (perhaps more in the context of SQL 2008). My manager has asked me to investigate further... :w00t:
I really am "fishing" with this post, I realize. Just wondering whether anyone has had any experience with this. Googling it hasn't given me anything to work with...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 19, 2008 at 3:15 pm
I've dabbled a bit with SB from a custom maintenance job perspective and might have some insight.
What you can do is set up a queue on each server and let them send messages back and forth with stored procedures bound to each queue. The stored procs would handle the input from the messages like:
'add 4 widgets to inventory'
or
'send salary info employeeid=12384'
You need to build a parser, or use well-formed XML to handle this.
This could take the place of linked servers, depending on the use.
I don't know if it would be worth it, but it's possible with limited use.
~BOT
Craig Outcalt
November 19, 2008 at 4:48 pm
SQLBOT (11/19/2008)
I've dabbled a bit with SB from a custom maintenance job perspective and might have some insight.What you can do is set up a queue on each server and let them send messages back and forth with stored procedures bound to each queue. The stored procs would handle the input from the messages like:
'add 4 widgets to inventory'
or
'send salary info employeeid=12384'
You need to build a parser, or use well-formed XML to handle this.
This could take the place of linked servers, depending on the use.
I don't know if it would be worth it, but it's possible with limited use.
~BOT
Thanks for the input! In our environment we have many linked servers - perhaps too many - and we are trying to come up with alternative ways of exchanging data from SQL instance to SQL instance. SSIS is one method, OPENQUERY/OPENROWSET is another. We are exploring the potential of Service Broker as yet another way of exchanging data between instances. Let's face it, linked servers are a pain to maintain, so we are looking for alternatives that are perhaps easier to administer and perform the same role as linked servers do.
Can Service Broker be used for returning a resultset back instead of a single value? I'm guessing that is possible with XML...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 19, 2008 at 5:38 pm
Service Broker sends messages back and forth. I think it's more designed for a notification of some sort, or an update of some values, not really moving result sets back and forth. That being said, it's possible it could be used for sending larger data sets.
What types of things do you do through linked servers?
November 19, 2008 at 6:21 pm
Steve Jones - Editor (11/19/2008)
Service Broker sends messages back and forth. I think it's more designed for a notification of some sort, or an update of some values, not really moving result sets back and forth. That being said, it's possible it could be used for sending larger data sets.What types of things do you do through linked servers?
Most common scenario is multi-join queries (SELECT or UPDATE) where one of the joined tables comes from a linked server: eg.
UPDATE A
FROM tbl1 A JOIN linkedServer.db1.dbo.tbl2 L
...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 20, 2008 at 8:23 am
Marios Philippopoulos (11/19/2008)
UPDATE A
FROM tbl1 A JOIN linkedServer.db1.dbo.tbl2 L
...
Service Broker won't help you with this...
March 12, 2018 at 5:51 am
Did this original poster ever find a suitable alternate solution to using these linked servers for her/his cases ?
I agree S/B would not be suitble solution, but am interested in know the reasons around why the data are on separate servers and what the final resolution was in this case. Did anyone ever here more a out this question ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply