Running querys against multiple SQL Server AND Oracle instances

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.