How to query a few MS SQL databases on different servers from single script

It’s quite common practice to keep databases on dedicated servers nowdays, especially if you use AWS RDS or Azure.

Relational databases performance is always painful and you might want to split data across at least a few databases. But if data is divided you still to have to do some logical operations across the whole amount and it’s quite simple, so let me show how it can be done using bot SQL Server Management Studio and CLI.

First of all, we have to create T-SQL script, I’ll use simple select:

:CONNECT rds-first.kagarlickij.com -U kagarlickij -P password
USE OData;
select * from dbo.Countries
GO

:CONNECT rds-second.kagarlickij.com -U kagarlickij -P password
USE Wallet;
select * from dbo.Currency
GO

To execute it from SQL Management Studio SQLCMD Mode must be enabled, you can enable it for single execution here:

Or you can enable this option by default here:

But what if you want to run some task automatically f.e. during CI/CD?

It’s even easier, you only have to have sqlcmd installed and use connection to any sql server (but check network connectivity from server which will execute script to target servers).

Script will be extremely simple:

#!/bin/bash

sqlcmd \
-U kagarlickij \
-P 'P@$$w0rd' \
-S rds-mgmt.kagarlickij.com \
-i 'select.sql' \
-o 'select.log'

 

Depending on your requirements you might also consider OPENDATASOURCE

I hope this info will be useful to you, and if you need any help feel free to use contact from on the main page.