“Here’s an easy one,” the boss said. I tensed my toes inside my shoes, knowing it would NOT be an easy one. “Let’s get a look at the amount of space the databases are taking up.”
There’s a short silence. I say, calmly, “Which databases?”
“Oh, all of them.”
This…is NOT an easy one.
It seems like a no-brainer that “pulling together tons of information about the systems” would be a thing that database administrators could do easily. After all, the word “data” is in our title.
But somehow, this is the exact thing that DBAs have struggled against for actual decades now. We’ve had partial solutions: For one, server groups let you query multiple servers at once, but I’ve always been deeply dissatisfied with how fiddly this is. I end up spending so much more time adding and removing servers to groups than I’d like. Another solution – homegrown scripts – is much better, but again, isn’t complete.
I wanted a solution that was elegant and effortless.
First though, let’s consider a few more “easy questions” that can take up hours and hours of time:
- “Can you find whether Alex has system admin permissions, and on which servers?”
- “How many different copies of the BAL database do we have, and where are they?”
- “Do we have the latest version of the backup scripts on all instances?”
- “Which servers aren’t up-to-date on security patches?”
This enterprise level view – in other words, this ability to search multiple instances of SQL – is the major shortcoming of SQL Server administration. Where’s the elegance? The effortlessness?
We had to create our own solution, make it our full-time job, and make a company out of it. Now we have that solution, and we use it literally everywhere we find a database.
Using Minion Enterprise, the answers to each of these questions really are “the easy ones”:
“Let’s get a look at the amount of space the databases are taking up.”
SELECT SUM(UsedSpaceKB) as UsedSpaceKB, SUM(AvailableSpaceKB) AS AvailableSpaceKB, SUM(SizeKB) as SizeKB FROM Collector.DBFilePropertiesCurrent;
Note that that’s the big easy, for sure. We just pulled the information on ALL of our databases from an automatically populated collection of data. Also note, we can do anything we like with this query. Let’s convert to GB, and limit to just “Gold” level servers:
SELECT SUM(UsedSpaceKB) as UsedSpaceKB, SUM(AvailableSpaceKB) AS AvailableSpaceKB, SUM(SizeKB) as SizeKB FROM Collector.DBFilePropertiesCurrent WHERE ServiceLevel = 'Gold';
Easy.
“Can you find whether Alex has system admin permissions, and on which servers?”
SELECT ServerName, LoginName, Sysadmin FROM Collector.LoginsCurrent WHERE LoginName = 'MyCo\AlexSmith' AND Sysadmin=1;
“How many different copies of the BAL database do we have, and what versions of SQL they’re on?”
SELECT ServerName, DBName, Version, Edition FROM Collector.DBPropertiesCurrent WHERE DBName = 'BAL';
“Do we Minion Backup installed on all instances?” This query pulls a list of all SQL Server instances that do NOT have the Minion Backup job on it.
SELECT Serv.ServerName, SQLVersion, SQLEdition FROM dbo.Servers as Serv LEFT OUTER JOIN Collector.SysJobsCurrent AS Job ON Serv.InstanceID = Job.InstanceID AND Job.name LIKE 'MinionBackup-AUTO' WHERE Serv.IsActive= 1 AND Serv.IsSQL = 1 AND Job.name IS NULL;
“Which servers aren’t up-to-date on security patches?”
SELECT * FROM dbo.SQLVersionLevelsGet WHERE ISNULL(LatestPatch, 'latest') <> ISNULL(CurrentPatch, 'current');
This is a very small sample of the environment-wide queries and reports available to the DBA, with Minion Enterprise. We LOVE getting people this kind of power in their environment, and it’s been helpful (to us and to others) in shops with 5 instances, and in shops with 1,000 instances!
Give us a shout today – email Sales, or email me directly, or talk to us on Twitter – and we’ll get you a demo and a trial run. Our trials cover your ENTIRE enterprise – all of your SQL Server instances, and any Windows servers you want to manage, too.