Running querys against multiple SQL Server AND Oracle instances

Posted in: Technical Track

It is not unusual that a DBA needs to run the same query across multiple instances. The query can be anything from a simple line to retrieve a specific value (such as an instance version), to others that involve data modifications or schema changes.

SQL Server 2008 brought the ability to execute Statements Against Multiple Servers Simultaneously by simply creating a local server group or broadly using a Central Management Server and one or more server groups. Inside these groups there should be one or more registered servers.

There is also the commercial Red-Gate SQL Multi Script with an Unlimited edition license that allows you to run your code against any number of SQL server instances.

Before SQL Server 2008, there was no native support for running the same query against multiple instances using shipped tools, so it can be approached in one of these ways:

  • Manually connecting to each instance to run the code; this is very time consuming.
  • Using batch files to call OSQL or SQLCMD to loop against a defined set of servers; this involves a bit more work and control of the batch files.
  • Using linked servers and loops inside a T-SQL query.
  • Using DTS or SQL server Integration services.

I’ve used nearly all of them, but I have found that linked servers provide a higher degree of control on the target servers, error handling, and most important, the ability to use SQL server Encryption to store the credentials of source servers if some of the servers are using SQL authentication.

I’ve also managed to make this run against Oracle databases, but had to take the extra step of configuring configuring Oracles as linked servers as described in KB 280106.

The code has the following characteristics:

  1. It uses encryption to store and retrieve credentials for Oracle and SQL server instances that use SQL authentication. There is a way to connect to Oracle using windows authentication but I didn’t test that.
  2. The code uses an ASYMMETRIC KEY to encrypt the credentials.
  3. It uses SQL cursors to retrieve list of servers against which I will run the code.
  4. Use of TRY…CATCH for error handling.
  5. Code against SQL server is run using Sp_executesql. The supplied code is liable for any restrictions imposed by Sp_executesql; I found very long queries in particular to be sometimes a problem.
  6. Code run against Oracle instances are done through Openquery. I found that using direct Linked server name exposes some problems especially with metadata. The supplied code must be Oracle-compatible.

Remarks

  • You need at least SQL server 2005 to use TRY..CATCH and Encryption.
  • You need to format the source code to be syntax error-free, especially when it contains single quotes.
  • When it comes to retrieving data only, Openrowset looks like a fast alternative to creating then dropping a linked server. OpenRowset is a fast way to access remote data on the fly using an OLE DB data source.
  • I’ve not used the code much against Oracle—just simple tasks, but it worked for me.
  • The code can be used against any datasource (DB2, MySQL, etc.) that can be accessed using Linked servers.

Here is the code. sql-oracle.sql.txt I welcome your feedback and additions.

Interested in working with Mohammed? Schedule a tech call.

3 Comments. Leave new

Log Buffer #168: a Carnival of the Vanities for DBAs | Pythian Group Blog
November 6, 2009 1:48 pm

[…] Mohammed Mawla on the Pythian Blog bridges the gap with his item on running the same query against multiple SQL Server AND Oracle instances. […]

Reply

Nice write up on cross-platform querying!

Looking for a way to run queries against multiple data sources and get the output into Excel immediately? Check out my Excel based utility that does this.

http://dbbulletin.wordpress.com/downloads

Look for Data Getter.

Reply

Hi,
I am currently investigating the ability to use some of mentioned technologies to accomplish this (see link below).
What would be the best approach today. I could use SQL2008 or even 2012 if needed as “midrange”.
BR /Magnus
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4cfbd490-30c5-412b-8cc6-02a164ba3d6d

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *