Multiserver Query

As a developer it’s common practice that you work on a server farm with 2 or more servers, and a clustered or redundant production environment. Sometimes it’s necessary to run certain queries on all the machines you’re developing on or that you’re maintaining. For example if you want to know if all your instances run the same version (and/or Service Pack) of SQL Server. One of the options you’ve got is to run the query in multiple tabs or windows; one for each server or instance. This seems okay for 2 or 3 instances, but not for an entire OLAP environment.

Luckily the SQL Server team has build in the opportunity to run a query against multiple instances simultaneously. This can be accomplished by running a Multiserver query. It will run your query on a complete group of SQL Server instances. In the example below, I retrieve the version of each instance I connect to. I’ve done this by using the @@VERSION function that is shipped with SQL Server.

First, I’ve created a group of servers in the Registered Servers window in SSMS:

This group contains our development instances. These instances run on 1 hardware-platform, which contains different virtual machines. Each development team has it’s own machine with dedicated SQL Server instance.

If you right-click on the server group, you choose “New Query”:

If the new query window opens, you will see a different status bar. With the default settings of SSMS set, the bar will change to a pink color:

Also in the left corner of the status bar, you can see how many instances are in the group, and how much of these instances are (still) running. In my case, all six instances in the group are running, and will return the result.

For this example I used the query:


The @@VERSION function returns the version, processor architecture, build date and operating system for the current installation of SQL Server. For more info, see the MSDN article. Also, by default the servername you used to register the server will be shown:

As you see, all of our development machines run on the same version. That’s because we copied the instances from 1 base-image. As an example, I ran the same query on my local server group:

These are obviously different versions: I have a SQL Server 2008 R2 and 2012 Developer instance running on my laptop.

If you want to, you can change the options for Multiserver queries. For example, you can choose NOT to merge all results into 1 result set. You can do this via Tools -> Options -> Query Results -> SQL Server -> Multiserver Results. There you can change these options:

Only one remarkt is left. Writing this post I tried several things, but one thing I still don’t understand: why is the result ordered differently every time you run a Multiserver query? This might be because the results of the different instances are collected, and merged together to return a single result set. I’m not sure about this though! So if you know the answer to this question, please let me know!

Error: Permissions denied mssqlsystemresource – configurations

Last week I encountered a strange issue on a production SQL Server. We weren’t able to connect to some of them. If we tried to connect with a normal Windows NT (Active Directory) or SQL account, we got the following error (I recreated the error locally):

Message: The SELECT permission was denied on the object ‘configurations’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

For further information about the mssqlsystemresource database, please read my previous post about this database.

But in my case, there was nothing wrong with this Resource database. After all, no other database was unavailable or corrupt. So I needed a little bit more research. After a while I found out that the issue is coming from a security setting.

We allowed an external company of Data Warehouse consultants (KVL) to access our production SQL Server. They needed to create a Snapshot, and the person responsible for making this possible created 2 Stored Procedures (SP’s) for this task. One SP to create a Snapshot, and one to drop the Snapshot. These SP’s are stored in the Master database.

But because he wanted them to only access the SP’s, the created a user for this (again, created this locally):

And he added a server mapping for the user:

After that he tested it, and it worked like a charm! He e-mailed me the location of the SP’s and the names, so I could send them to our consultants. Then he added one more thing to the User Mappings without any of us knowing:

When the consultants tried to create the Snapshot, they couldn’t get it to work. After some research I found out that the User Mapping on the Master database was set to db_denydatareader. As you all know, deny permissions always overrule access permissions. In this case this worked against us.

So if you ever encounter this issue, please take a look at your security settings. If all of your other databases are still accessible, the error is coming from a lack of permissions. You’ll encounter this issue most of the time when a user (NT or SQL user) is a member of 2 separate groups with different permissions.

SQL Server system database – mssqlsystemresource

The database mssqlsystemresource is a read-only database that is shipped with SQL Server from version SQL Server 2005. It contains all the system objects that are included in SQL Server. An example of this are the sys.objects. These are stored in the Resource database, but appear in every other database. The sys.objects from a user database refer to the Resource database.

SQL Server cannot backup this database. You can create a backup yourself, but don’t back it up like a normal .MDF file. It also can’t be restored via SQL Server, but you can do it manually.

The Resource database makes upgrading to a new version easier and faster.

The mssqlsystemresource database is invisible for users, even for the System Administrator (sa). The database can be restored by copying the .ldf and .mdf files from the folder “[Drive]\Program Files\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQL\Binn” to a folder of your choice (in this case D:\Databases). After that, start a query with the user sa, and run the following script:

USE [master]

CREATE DATABASE [mssqlsystemresource_RESTORED] ON 
	(FILENAME = N'D:\Databases\mssqlsystemresource.mdf'),
	(FILENAME = N'D:\Databases\mssqlsystemresource.ldf')

Once you’ve done this, you can query the restored version of the Resource database.

Error: Unable to connect to SQL Instance

Every once in a while you encounter strange, but interesting issues. This time a colleague asked me to fix the problem that another colleague could not connect to his SQL Engine. After some research we found out that the machine he was working on ran 2 versions of SQL: SQL 2005 Express, and SQL 2008 Enterprise.

The first thing that came to mind was conflicting installations (shared DLL’s for example). That wasn’t it… Second thought: Both instances are default named instances, so I stopped the 2005 Express service. Still nothing…

Then it came to mind. I’ve seen this before! Both instances use the same default TCP/IP port 1433, and this causes issues! So I opened the configuration tool, and changed some IP addresses and port numbers and (already there, so why not) started the SQL Browser Service (why it was stopped? I don’t know!). Result: still nothing…

Now I’m getting pissed! This is impossible! After some searching I tried (as a last resort) to turn of the Windows Firewall. BAM! Works! Damn it!!! In order to make sure it isn’t a coincidence. Restart SQL Service: still works. Turn Firewall on: no connection possible. This is it! Finally, after 30 minutes of trial and error, I get a result.

So I ended up adding a custom rule to the Firewall to allow SQL connections through. This can be done by following these steps:

1) Open Firewall Window:

2) Click on “Inbound Rules”, and then “New Rule…”

3) Choose Port:

4) Add TCP Port 1433

5) You need to allow the connection:

6) You can choose whatever you want in this screen. I personally only allow Domain connections to my SQL Server:

7) Name the Firewall Rule:

When you click Finish, there’s no need to restart SQL Service or your workstation. The changes take effect instantly.

From now on you will be able to connect to your SQL Server Instance from another machine over the internet or your network.