Have you ever wished you could easily gather information from all your SQL servers? Whether it’s job failures, versions, or a list of users, managing multiple SQL servers can be time-consuming. In this article, we will explore how you can remotely execute code on all your SQL servers, regardless of version or platform.
Remote execution refers to the ability to execute a stored procedure or a T-SQL statement on multiple servers from one location and view the results on one monitor. For example, instead of manually connecting to each server and executing a stored procedure, you can use remote execution to execute the same command on all production servers and view the outputs on one screen.
This remote execution setup can be used for various scenarios, such as:
- Executing commands on all production servers/instances
- Executing commands on specific types of servers/instances (e.g., SQL 2000 or SQL 2005)
- Executing commands on servers/instances owned by a specific department
- Executing commands on all servers/instances regardless of version, type, or owner
Setting up remote execution is relatively easy and doesn’t require advanced DBA skills. Here are the basic steps:
Step 1: Choose a Control Server
First, you need to select a server/instance where you have control. This server will be used to store and execute the code. It is recommended to choose a server/instance that is not a development, test, or UAT environment to prevent unauthorized modifications or deletions of your code and tables.
Step 2: Set up Trusted Connections
To enable remote execution, you need to make your control server “trusted” by all the other SQL servers in your network. This can be done by accessing Active Directory and configuring the trust settings for your server. It is important to consult with your Security Analyst before enabling this feature to ensure proper security measures are in place.
Step 3: Create the Databases and Tables
Next, you need to create the databases and tables that will hold the server/instance information and the results of the remote execution. The main database, called “SQLSrvOps,” will store the server/instance names, versions, owners, and other relevant information. Another database, called “SQLSrvReports,” can be used to save errors and data for reporting purposes.
Step 4: Execute Code Remotely
Once the setup is complete, you can start executing code remotely. The code provided in the example article demonstrates how to execute a simple SELECT statement on all the servers/instances. You can modify the code to suit your specific needs, such as executing stored procedures or collecting specific information.
Here is an example of how to execute a SELECT statement on all production servers:
SELECT '**** Failed MS SQL Jobs Report ****' AS ''
EXEC ('SELECT name FROM [' + @InstanceName + '].msdb.dbo.sysjobs A, [' + @InstanceName + '].msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0')
The results will display the names of the jobs that have failed on each production server/instance.
With remote execution, you can automate various tasks and generate reports for monitoring job status, analyzing error logs, and gathering server information. The possibilities are endless, and it can greatly simplify the life of a DBA managing multiple servers/instances.
Remember to always review and test your code before executing it remotely to ensure it meets your requirements and doesn’t cause any unintended consequences.
We hope you find this article helpful in your day-to-day DBA role. Feel free to share your experiences and how you use remote execution in your environment.