I manage a few servers used to host SQL Instances for development and test purposes. Each of those instances hosts databases covering multiple environments. So I’ve got multiple servers, with multiple instances, with multiple environments.

It’s important that issues in those environments don’t block development tasks, or invalidate or block testing cycles, so I like to keep them a little bit locked down. Code changes into those environments are generally made through builds and releases.

Equally though, sometimes developers need to be able to make changes that require sysadmin rights, without waiting for me to have time to help – or I might be on holiday.

Consequently, I’ve had to give elevated permissions to a few “trusted” devs. Result – bad things happen occasionally.

One common issue that bugs me is where databases have been moved from one instance to another, usually through backup and restore, and the files haven’t been moved as part of the restore so they get recreated in the data\log folders for the old instance.

This has caused me various problems, e.g. working out which instance is hammering the disk or using up all the space.

You can avoid this using the tick box in the Files page in the Restore dialog (if you’re restoring through the GUI):

I’ve written a quick script to identify any database files that are suffering this and identifying the user responsible for the restore (where this is available). I thought I’d share in case anyone else suffers from the same problem:

--Check for database files down the wrong path
DECLARE @DefaultDataPath VARCHAR(512);
DECLARE @DefaultLogPath VARCHAR(512);

SET @DefaultDataPath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(512)) + '%';
SET @DefaultLogPath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(512)) + '%';

SELECT @DefaultDataPath AS CorrectDataPath, @DefaultLogPath AS CorrectLogPath;

SELECT 
   SUSER_SNAME(d.owner_sid) AS Culprit, 
   d.name, 
   f.type_desc, 
   f.physical_name
FROM sys.master_files f
INNER JOIN sys.databases d
   ON f.database_id = d.database_id
WHERE d.database_id > 4
AND d.name != 'SSISDB'
AND (
      (f.type_desc = 'ROWS' AND f.physical_name NOT LIKE @DefaultDataPath)
   OR (f.type_desc = 'LOG' AND f.physical_name NOT LIKE @DefaultLogPath)
);

System databases and the SSIS catalog are excluded.

For once I won’t show the results of the script in action – I am however off to have a quiet word with a few culprits!

This month for T-SQL Tuesday #101 Jens Vestergaard asks us to blog about the essential tools in our SQL Toolbelt.

http://t-sql.dk/?p=1947

The concept of a Central Management Server (CMS) is one I’ve been meaning to blog about for a while – just because I get the impression not a lot of people know about it or use it even though it’s been around since at least SQL 2008.

A central management server is simply one of your SQL instances on which you can register a group or groups of other instances. You can then run queries against those groups as a set, it’s a very easy way of being able to run diagnostic queries – or deploy common objects across a group.

There are other ways of performing these sorts of tasks – such as using Powershell, but with a CMS it’s really quick and easy, and all you have to know is SQL.

I’ll run through how you set one up, then give a couple of examples of using it.

Setting up your CMS

Go to the View menu in SSMS and select Registered Servers.

Then in the Registered Servers window, expand Database Engine, right-click Central Management Servers and click Register Central Management Server.

In the New Server Registration dialog select the instance you want to use as a Central Management Server.

You just need to put in the connection details – though you can also give it a friendly name in the Registered server name field.

It’s worth noting that this server isn’t going to be doing any particularly heavy lifting, it’s just going to store registrations for other servers, so there’s no need to use a dedicated instance for this.

You’ll now see this instance listed under Central Management Servers in the Registered Servers window.

You can right-click on it and either register individual servers underneath it or create one or more groups beneath which you can register servers. You can even nest groups if you want.

Here’s what it looks like after I’ve registered a few servers / groups:

And that’s it setup – told you it was easy.

Running Queries against multiple instances

This is the clever bit. I can run a query against an individual SQL instance or against a group and any sub-groups.

I want to run something against all my instances in both groups, so I right-click on My CMS Server and select New Query.

Let’s have a look at the query window as there’s some differences:

You can see the status bar at the bottom is pink, that tells me I’m connected to multiple instances via a CMS. On the bottom left you can see I’m connected to 6 out of 6, i.e. I tried to connect to 6 instances and all are successfully connected.

Now let’s run a query. Suppose I want to check MAXDOP across my instances:

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'max degree of parallelism';

Here’s the results:

You can see the queries against each instance have been combined into one result set and a column added for the ServerName (the friendly name I specified).

In the messages tab, we also have the output from each query:

There’s no limit to what you can do with this, as long as you can express what you want to do in T-SQL, you can execute it easily across as many instances as you want.

I use this for deploying updated versions of Ola Hallengren’s procedures across multiple instances, or the First Responder Toolkit from Brent Ozar Inc.

I use this for running checks across multiple instances like the MAXDOP one above. Earlier, I used it to check for any databases that didn’t have PAGE_VERIFY set to CHECKSUM, and then I used it to enable that across the databases where it wasn’t set.

I’ll finish with another query to change my server configurations. Having run the first one to look at MAXDOP I’ve decided I want to set it to 4 for all the servers where it’s not set:

IF (SELECT value_in_use FROM sys.configurations 
    WHERE name = 'max degree of parallelism') = 0
BEGIN
   EXEC sp_configure 'show advanced options', 1;  
   RECONFIGURE;  
   EXEC sp_configure 'max degree of parallelism', 4;  
   RECONFIGURE;  
END

Here’s the output from the messages tab:

(The really sharp eyed amongst you will notice SQL2012_Local is missing, that’s because I tested the query on that one first).

And if I run the original query again to check the settings:

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'max degree of parallelism';

Sorted!

Summary

Content Management Servers are an exceedingly simple way of performing admin tasks or checks against a whole bunch of SQL Instances at once.

Once you’ve set it up, anyone else with access can connect to it run queries – or add other servers and groups.

There’s still a place for Powershell, particularly in automation of tasks. But when you’re doing tasks that are a bit more ad-hoc then using a CMS is perfect.