0

My question has to do in regards to the ability of being able to create stored procedures that connect to multiple servers. If anyone's not familiar with it, there's a :CONNECT syntax in SQL that will switch where your query's being run from. For example: 

:CONNECT SERVERNAME

SELECT *
FROM Table

GO

This would run the query from where the table is stored as opposed to using linked servers (which cause serious performance issues). Does anyone know if it's possible (and how to achieve it) to create stored procedures that switch between servers? I keep getting various error messages when trying to achieve it. Here would be an example:

:CONNECT SERVERNAME

SELECT *
FROM Table

GO

:CONNECT SERVERNAME2

SELECT *
FROM Table

GO

This would connect to two different servers in the same query.

Thanks

UPDATE - 4.26.2018

All,

We've pretty much decided OPENQUERY is our best solution, at least for stored procedures. Unfortunately, we'll be limited by syntax but performance is MUCH better than using linked servers (which is what we're currently using). I appreciate everyone that's chimed in; Your input was invaluable. If you wish to add anything else, please feel free to do so.

Thanks

Anonymous
  • 43
  • 5
  • 5
    Does anyone know if it's possible (and how to achieve it) to create stored procedures that switch between servers? My Response: [Linked servers](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-2017)? Using this approach one doesn't need to switch you just select as if it was a table in your database. More info: https://stackoverflow.com/questions/4091960/sql-server-linked-server-example-query – xQbert Apr 25 '18 at 19:45
  • @xQbert is right and you need linked servers. – Alfabravo Apr 25 '18 at 19:46
  • 1
    You may want to ask a different question: why am I seeing serious performance issues with linked servers? You're not going to have any luck with :CONNECT in a stored procedure, as it's not a T/SQL command, it's a sqlcmd command. (And you can't use GO in a stored procedure, either; it's a client command.) – Matt Gibson Apr 25 '18 at 19:50
  • 1
    Instead of explicitly connecting to each server, you can also add sever name in your query like `Select * From servername.schemaname.tablename` – Aura Apr 25 '18 at 19:50
  • Thanks Matt. That was my fear, that SQL wouldn't be able to create stored procedures like this. The main reason we have performance issues is that the main tables we hit are extremely large and then when you add a linked server solution, it makes it even worse. Whenever I manually run queries using :CONNECT, it's a night and day difference in terms of performance. In some cases, it's 3 hours vs 5-7 minutes. – Anonymous Apr 25 '18 at 20:01
  • If your linked server performance is bad compared to connecting directly, we'd need to see the query plans to figure out why, and what's going on. It could even be as simple as having poor network connectivity in between your linked servers, but good network connectivity between your client and each of them. There are plenty of good ways of tracking down the problem, but they mostly begin with looking at the query plans to find out what's actually going on with your queries. (I've seen a problem like yours fixed by someone replacing a $1 network cable in a server room!) – Matt Gibson Apr 25 '18 at 20:07
  • @Matt Gibson That would be dreamy... We're getting hounded by the DBAs to increase performance but I just have no other solutions. I've even tried OPENQUERY but there are some limitations to that as well; Mainly to do with syntax. – Anonymous Apr 25 '18 at 20:26
  • 1
    `OPENQUERY` is one way to force the query to run on a remote server. But what are you really trying to do here? Do you need to generate datasets _across_ servers or do you need to just select wholly from a single server? It doesn't make sense to just select from a remote table. What are you doing with the dataset? The only way to troubelshoot this is to understand the root problem, rather than trying to address its symptoms – Nick.Mc Apr 25 '18 at 23:06
  • Thanks Nick. I agree with you on OPENQUERY. However, it severely limits us in terms of syntax which is why I was trying to avoid it. For example, scalar variables need to be re-declared and re-set within the query itself. It also gets kind of ugly when you need to troubleshoot. I talked with the DBA and the other developer this morning, and we've pretty much agreed OPENQUERY is our best solution... At least for stored procedures. – Anonymous Apr 26 '18 at 17:48

1 Answers1

1

Using Linked Servers in a 4-part naming convention will work. It can get ugly fast in regards to performance, so be careful. If you do use Linked Servers, I'd recommend against putting multiple servers in the same SQL statement (including the local server you're on). It isn't terribly efficient. It basically breaks up the query into local and remote queries, it then scrubs the data, and combines it locally before finishing. I've seen users execute queries with multiple servers and take down solid servers in the process.

Another option is the OPENQUERY() method. This still uses a Linked Server, but will send the query to the other server, to process the whole thing there and just ship the data back. This is typically faster and more efficient than the prior.

SELECT
     opn.Id,
     opn.ColumnName,
     opn.AnotherColumnName
FROM OPENQUERY([LinkedServerName],
     'SELECT
          tbl.Id,
          tbl.ColumnName,
          tbl.AnotherColumnName
     FROM DB.Schema.Object AS tbl
     WHERE tbl.ColumnName = ''SomeValue'''
) AS opn

This link gives some more good info: Click Here

SSIS is your best bet here. It's fast, good when using data from multiple servers, and not too difficult to learn (the basics anyway).

So, a few options I've listed...

  1. SSIS - Best option
  2. OPENQUERY() - Better option
  3. Linked Servers - "I guess it works" option
Utrolig
  • 251
  • 2
  • 13
  • Thanks Utrolig. We can't manage our own packages, so I don't want to go down the road of SSIS. We might have to wait weeks (if not longer) for packages to be deployed. As of now, we've pretty much agreed OPENQUERY is our best solution, at least for stored procedures. Linked servers is definitely the worst by far but it works as you stated. Just to give you an idea, I ran the same query this morning by way of OPENQUERY and Linked Servers. OPENQUERY returned the entire data set within 6-8 minutes. Linked Servers had only returned 7.9% in 11 minutes and 30 seconds. – Anonymous Apr 26 '18 at 17:54
  • 1
    You can remote EXECute through a Linked Server without having to use OPENQUERY. IIRC, the syntax is `EXEC('') AT [LinkedServerName]`. You need to have the LinkedServer's RPC options set correctly in either case. – RBarryYoung Apr 26 '18 at 18:09
  • 1
    Awesome tip Barry! I had no idea you could do that. I'll probably use this for my stored procedures. – Anonymous Apr 27 '18 at 16:25