(Almost) Everything is Going Open Source Now… and I LOVE it.

Why can't we be friends?
Why can’t we be friends?

While I’m putting together my big update on Inventory Manager, I thought I’d take some time to throw confetti into the air. There may be some excited clapping as well. I warned you.

I largely see myself as platform-agnostic. While I think that certain companies do individual products well, I also believe it’s fair to say that none of them do everything well. I use Android phones and Apple tablets, Linux for home (mostly) and Windows at work. Heck, I’ve got a Roku and a Chromecast because they both do things that the other doesn’t.  I’m all over the map, but all over the map is a great place to be, especially in the tech industry now.

Despite all of this, I have to admit I am partial to Free Open-Source Software (FOSS). Give me a choice between Ubuntu and Windows, and all other things being equal, I’ll choose the Debian-based option. I’ll admit my biases.

So, when MS started moving in this direction I was happy. I wanted to see this trend continue, and boy has it. First of all…

1. .NET Core is now running on Redhat.

When Microsoft announced that .Net was going open-source, I was cautiously optimistic. I’m not a big .Net coder, but I could see the benefit and was hopeful that MS would continue down this path.  This lead to some cool things that I thought I’d never see in a million years, like .Net running on Redhat.

There’s understandably some cynicism about Microsoft’s true intentions, as well as their long term goals, but this is the cross-over that I’ve been wanting to happen for a while. Blending the strengths of RHEL with .NET on top is a great start. If the .NET development platform can be ported, why not parts of the Windows Management Framework? We could even one day see…

2. Powershell on OSX and Linux.

I didn’t always like Powershell, in fact prior to Powershell 3, I just referred to it as PowerHell. Since 4.0, however, it’s no secret that I’m a fan; one look at my github will tell you that. I like its logical approach to (most) things and that it works for simple scripts quite easily, while being a powerhouse (no pun intended) behind the scenes.

Sorry, THIS is the coolest thing ever.
This is the coolest thing ever.

This shell coming to OSX and Linux will be a boon for both systems. While I am, and will probably always be, a bash scripting guy, Powershell in Windows just makes everything so gosh-darn easy. If I could whip up a PS1 script with a few imported modules and attach it to a cron job with ease, then I think everybody wins,  mostly me. But, if I decide that I want to use bash instead, that’s okay because…

3. Bash is running on Windows.

This isn’t a one way transition. Microsoft is making a trade, bringing one of the most widely used shells to Windows. This not only makes scripts more portable, but also knowledge.

Have some ultra-fast Linux bash script that works wonders? Super, you now have it Windows, too. Wrote a script to do some directory work in Powershell? Great, you now know how to do it in Linux.

You can't tell me that isn't the coolest thing ever.
I’m sorry, THIS is the coolest thing ever.

There are very few downsides to this, other than the obvious security issues and that it isn’t truly a stand-alone shell (it’s part of Ubuntu on Windows). In any case, it allows interoperability  between software from different systems. This is great now that…

4. SQL Server is on Linux.

This isn’t technically going open source, as it will run inside a container, but the idea that this will now be possible and supported is like something out of my greatest dreams.

I have a maybe-controversial opinion that SQL Server is the best relational database system out there. For all its faults, I’d rather use SQL Server 2005 SP1 than Oracle 12c. Just the way I feel, and for reasons I won’t go into here. I hope the things I like about SQL Server translate to the Linux environment.

The fact that Ubuntu is supporting this with Microsoft is great. I can’t wait to use my favorite OS with my favorite database engine on the same system.

Last thoughts

There are other items I’ve glossed over, but these are the big ones to me. Soon, we will be able to run SQL Server on Ubuntu Linux with cron jobs executing Powershell for a .Net application that resides on an RHEL box. *excited clapping* (I warned you.)

It’s a great time to be in the tech industry.

-CJ Julius


A Funny Thing Happened on my way to enable Management Data Warehouse

Three Job Schedules walk into a bar…

Management Data Warehouse is a neat tool for collecting data on your instances with regards to performance, disk usage, and anything else related to SQL Server you might want to know. I’ve recently been rolling it out to various servers in test environments to get a good handle on all that it can do, and maybe more importantly, how it’s going to affect performance.

When I was satisfied that it was working the way I wanted in these environments, I started to push it to what you could call QA. Immediately I was hit with litany of errors, all of which said basically the same thing:

Caught error#: 14684, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_set, Line: 203, with Message: Caught error#: 14262, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_set, Line: 80, with Message: The specified @schedule_name ('CollectorSchedule_Every_30min') does not exist. (Microsoft SQL Server, Error: 14684)

Um, what? Of course it doesn’t exist, I’m installing it now. Just to verify, I did some poking around, and I found that it was indeed attempting to add a schedule that didn’t exist (as you would expect) to the collection set. After a while my Jr DBA (read: Google) turned up this article on removing MDW which says, in no uncertain terms:

The fact is that, while these schedules may look like they were created by MDW, they are created by default in a default installation of SQL Server 2008 or above. You can delete these easily, if you really want to, and if you are sure you will never enable MDW again. I would still suggest scripting the schedules and saving those scripts to your file system or source control so that you can recover them in the future.

I took a look back on the TEST instances where I was previously successful and found these schedules existed both on instances where MDW had been installed, and in places where it had not. These were part of the defaut SQL Server install and had been manually removed. Whoops.

As far as I know, there isn’t any direct way to script out schedules, so I could port them to the boxes where they had been removed. To get around this, I added them to the collector set jobs and then scripted that. Then I was able to change a few variables (and remove the schedule_uid) so that this:

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CollectorSchedule_Every_15min',

became this:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',

I ran these add_schedules on the QA boxes, went through the Configure Management Data Warehouse Wizard again, and tada!

Green means good.
Green is good. Green is life.

For your convenience, I’ve scripted out the four schedules I needed to get it going below. These are not from the most current version of SQL Server (2016 as of this writing), and are not all of the schedules included out of the box, but these let me get MDW running so I could add my own custom schedules for the remainder.

The moral of this story: Don’t remove MDW schedules unless you really mean it, for all time, forever and ever, pinky swear.

The four ‘core’ schedules I needed, for your convenience:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_6h',

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_30min',

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'RunAsSQLAgentServiceStartSchedule',

If one of these schedules gets created twice you can look up the job id or use SSMS to ‘Pick’ the schedule for the duplicated schedule (the id is in the first column). And run this to remove it:

--EXEC msdb.dbo.sp_delete_schedule @schedule_id = 9999

-CJ Julius

Creating a Simple Database Inventory Manager with Powershell – Part IV: GUI Front-End

Last Time, on Inventory Manager…
Now we make things pretty.
Let’s make things pretty.

Our data pull script has run, the database contains all our server  \ instance .database information and flowers are blooming; things are good. If this doesn’t sound like anything you have done, head back to the Introduction to see if you missed something.

Now it’s time to get everything connected so we can just fire up a GUI and press some buttons, to get the data we need fast.

How the Sausage is Made

If you’ve been following along with this series, and you’ve set up everything as instructed, then you should be able to download the pre-made GUI script and run it out of the box. If you’re pointing to a custom instance or database just change the $RepositoryInstance and/or $RepositoryDB before firing it up. If you want to learn more about how this was put together, keep reading. If you don’t care how the whazits work, you’re done.

At the top of our list is to create a form with buttons and give them names so we can call them in our Powershell. You can either build the form manually with this guide here, or use Visual Studio*. I’m going to be using the latter method because it’s the most versatile, and frankly the easiest. If you use the former, then you’re kind of on your own. Sorry.

In the  Visual Studio method (I’ll be using 2013 Ultimate) you’ll be utilizing Windows Forms and then running them through a “cleaner” to make them Powershell ready. This guide at FoxDeploy explains the whole thing spectacularly and shows you how to create some very complicated UI’s that are Powershell-friendly. I’d recommend going through Parts I and II as they’ll be the things you’ll need to create what we’re going to use here and then come back. Don’t worry, I’ll wait.

Then We Build

Got the GUI code? Cool. The first part of Stephen’s code uses a -replace to filter the Windows Form code and make it work in Powershell. I took that piece and made it a second script so I could just have the clean version of the XAML in my final code. You can find that code here.

Just copy/paste your <Window>…</Window> code over the commented area and run the script. It will spit out the final code and tell you all of the objects you can tie actions to (Name, Value). Then drop in Stephen’s XAML reader code to the main script with the cleaned code and you should have a GUI… that does nothing.


As I mentioned before, when you pushed the XAML code through WPF_to_PSForm.ps1 it will tell you what the objects are on your form. For our purposes, this is simply a few buttons that need to be tied to stored procedures. We do this though .Add_Click() as in the example below:

$sqlCommand = "
EXEC dbo.prGetInventory;
$dataset = Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand $sqlCommand
Write-Host $dataset
$dataset | Out-GridView -Title "Database Inventory"

Nothing crazy that we haven’t been doing other than using Out-GridView. This cool little cmdlet pushes datasets out to a customizable table with filtering, sorting the ability to remove columns etc. -Title “SomeTitle” is the window title.

Sample sorted Database List with a few columns removed.
Sample sorted Database List with a few columns removed.

Once you’ve coded all of the buttons, then add the form display at the bottom, using out-null to suppress messages:

$Form.ShowDialog() | out-null

And that’s done. A Winner is You!

What now?

Using these scripts, you can go out and grab any information from the servers\instances you specify, pull it back into a centralized location and then use a GUI front-end to make fine tuning and retrieval easy. As I stated previously, this is a bare-bones system to centralize your database information. You can gather any piece of information from the Server, Instance or Database level by using the same tools that are currently collecting and retrieving this information.

It’s been a long journey, but thanks for sticking with it! If you want to make any alterations to the code or tighten it up (Odin knows that it needs it), feel free to make the changes and shoot them back to me. I’ll definitely give you credit for significant changes in this blog or the code itself.

Also, and I think this goes without saying, but if you want to use this in your personal or business environment: have at it! Just please make sure you give me proper credit, with maybe a link back to my blog/Twitter/Linkedin? That’d be super cool of you.

Thanks again and happy Inventorying!

–CJ Julius

*Full disclosure: I have not tried this with the Community version of Visual Studio, so all the features may not be there.

Creating a Simple Database Inventory Manager with Powershell – Part III: Data Pull

Powershell time; no really.
I come bearing scripts.

Now it’s time to get to get this thing moving. We’re going to go out to each of our server\instances and pull back the information for our tables, updating them with the stored procedures from the last section.

We’re going to be looking at this script [DB-DataPull.ps1]. It’s about as simple as I could get it for our needs. There’s not a lot of frills, but it’s a good cop and it. gets. results.

If you think you missed something you can go back to Part II: Stored Procedures or check out the Introduction.

Get this Jalopy on the Road

The only thing you need to do is specify where the repository is. If the repository is on your local machine in the DBAdmin database then you need to change nothing.

$RepositoryInstance = '(local)'
$RepositoryDB = 'DBAdmin'

After that you’re done. Seriously. The rest of this post is going to be about the nuts and bolts of the script and what does what and why. If you’re looking to just get it fired up then you’re done. Be gone with you.

What’s in the box?

The first few functions (Get-Type and Out-DataTable) are required to turn multi-line WMI-Object output into DataTables so we can insert them into the Repository. These have been cleaned up and/or modified to fit our needs but are based on the code in the two links I provided.

The Invoke-SQL function is a pared-down version of a pretty popular script for sending dynamic SQL directly to a SQL server. There’s not much to be said about this one other than it opens a connection, sends the command and returns the results as a datatable.

Time to get into the meat of the process. First up, let’s grab all the Instance information using the stored procedure we built in the last post.

$ConnectionString = Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prGetConnectionInformation;

Using a foreach loop to cycle through the rows and thus connecting to each instance to pull the information. We’ll remove the ‘\\MSSQLSERVER’ part since that will actually break our connection, even though it’s the name of the instance (For more information on why this is, see every other Microsoft product ever created).

foreach ($Row in $ConnectionString.Rows)
$SubConnection = $($Row[0]) -replace '\\MSSQLSERVER',''
$InstanceID = $($Row[2])
Write-Debug $InstanceID
Write-Debug $SubConnection
$Version = Invoke-SQL -datasource $SubConnection -database master -sqlCommand "
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition'), @@VERSION

And then with another loop we use dbo.UpdateInstanceList to push all that into our database.

Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prUpdateInstanceList
@MSSQLVersionLong = '$MSSQLVersionLong'
,@MSSQLVersion = '$MSSQLVersion'
,@MSSQLEdition = '$MSSQLEdition'
,@MSSQLServicePack = '$MSSQLServicePack'
,@InstanceId = $InstanceID

That’s it for the Instance information, let’s get the database information. We use the same process to generate the connections as we did before, so I’m going to skip that. The only change you should note is the inclusion of the statement TRUNCATE TABLE dbo.DatabaseList since we are going to completely repopulate it. This way no matter if databases are added or removed, we’re starting each pull with a clean slate.

We get our data via a cte…

$DataPull = Invoke-SQL -datasource $SubConnection -database master -sqlCommand "
with fs
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
$InstanceID AS 'InstanceId',
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) AS DataFileSizeMB
from sys.databases db

…and push it into the Repository via our stored procedure.

Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prInsertDatabaseList
@DatabaseName = '$DatabaseName'
,@InstanceListId = '$InstanceListId'
,@Size = $Size"

Lastly, we’ll get Service and Server information with the same rinse-and-repeat method, with one notable exception. If you try to return the results of a WMI-Object and parse it into a SQL table, then you’re going to have a bad time.

This is where our two functions from the beginning come in to play. Out-DataTable and its sidekick Get-Type return the results into the proper type for our foreach loop.

$ServerInfo = Get-WmiObject win32_Service -Computer $Row[0] |
where {$_.DisplayName -match "SQL Server"} |
select SystemName, DisplayName, Name, State, StartMode, StartName | Out-DataTable

Now, if you run EXEC dbo.prGetInventory on your Repository database, you should see all of the information you could ever want right there. Magic.

But Wait, There’s More!

Now we’ve got all the data in one place, which is nice and all, but what if we want to get this information quickly? Sure we can jump into SSMS and run the procedures that have the data we want. However, I propose we make a GUI front-end so we can win friends and get free drinks.

Something like this?

We’ll do that in Part IV: The Voyage Home GUI Front-End.

–CJ Julius

Creating a Simple Database Inventory Manager with Powershell – Part II: Stored Procedures

Dynamic SQL? Them's fightin' words.
Dynamic SQL? Them’s fightin’ words.

Now that we’ve got the tables built and populated them with data (you did that right?) we can define how our Powershell script is going to access that data. If you don’t know what database or tables I’m talking about, check out the Introduction and Part I.

You can send the dynamic SQL to your database via Powershell but you really want all interactions to go through stored procedures. Why? Lots of reasons, but here’s 2:

  1. Standardization – Every time you call a stored procedure the code will be exactly the same every time. If you change a hard-coded query in one part of your script, but not another, you can get inconsistent results.
  2. Performance – Unless you’re accessing thousands of instances with this script then you may not care too much about performance, but SQL Server has a hard time building query plans off of dynamic SQL; it does so wonderfully when the code is in a compiled stored procedure.

So, that’s all well and good, but what do we need for our Powershell project to do? I’ve created the procedures that will allow you to do the basic tasks that you might be interested in. The sky is the limit if you want to do more.

The Procedure List

[prGetConnectionInformation] – Setup script for 2012


  • Not a gosh-darn thing.


  • Server\Instance string as ‘Connection’
  • ServerList.Id as ‘Server ID’
  • InstanceList.Id as ‘Instance ID’

Spits out a connection-string friendly version of all the servers and instances in the database.


[prGetDatabasesAndSize] – Setup script for 2012


  • Not a gosh-darn thing.


  • InstanceList.InstanceName
  • DatabaseList.DatabaseName
  • Size of the Database in Gigabytes as ‘SizeinGB’

Returns the database and the instance it belongs to along with the sizes of said DBs in Gigabytes for all databases in the database (Yo dawg, heard you like databases).


[prGetInstances] – Setup script for 2012


  • Not a gosh-darn thing.


  • InstanceList.InstanceName
  • InstanceList.MSSQLVersion
  • InstanceList.MSSQLEdition
  • InstanceList.MSSQLVersionLong

Basically returns everything in the dbo.InstanceList table that someone might be interested in.


[prGetInventory] – Setup script for 2012


  • Not a gosh-darn thing.


  • ServerList.ServerName
  • ServerList.IPAddress
  • ServerList.OSName
  • ServerList.OSServicePack
  • InstanceList.InstanceName
  • InstanceList.MSSQLVersion
  • InstanceList.MSSQLEdition
  • InstanceList.MSSQLVersionLong
  • Size of the Database in Gigabytes as ‘SizeinGB’

Leaves for a while and then comes back with everything in your Inventory. Does not bring back anything from dbo.ServiceList for visual reasons (we’ll see this later in the Powershell).


[prGetServerNames] – Setup script for 2012


  • Not a gosh-darn thing.


  • ServerList.ServerName

Just brings back all the servers. Nothing else. Stop asking.


[prGetServers] – Setup script for 2012


  • Not a gosh-darn thing.


  • ServerList.ServerName
  • ServerList.OSName
  • ServerList.OSServicePack

Returns all the information at the Server Level excluding IP Address.


[prGetServerServices] – Setup script for 2012


  • Not a gosh-darn thing.


  • ServerList.ServerName
  • ServiceList.ServiceName
  • ServiceList.ServiceDisplayName
  • ServiceList.ServiceStartMode
  • ServiceList.ServiceStartName as ‘Service_Logon’

Spits out everything you and your friends ever wanted to know about Services in the dbo.ServiceList table and the name of the Server they are on.


[prInsertDatabaseList] – Setup script for 2012


  • DatabaseName VarChar(MAX) (surprise! parameters!)
  • InstanceListId BigInt
  • Size Float


  • Error Code if applicable

Inserts new Database entries as provided by the Powershell script.


[prInsertServiceList] – Setup script for 2012


  • ServerName VarChar(MAX)
  • ServiceDisplayName VarChar(MAX)
  • ServiceName VarChar(MAX)
  • ServiceState VarChar(MAX)
  • ServiceStartMode VarChar(MAX)
  • ServiceStartName VarChar(MAX)


  • Error Code if applicable

Wow. Look at all the VarChar(MAX)’s. These could probably be turned into other datatypes that are smaller, especially ServiceStartMode. In the interest of brevity I just made these as versatile as I could.  This may change in the future if I get a chance to tighten this up.

Oh, and this stored procedure inserts new services into the dbo.ServiceList table by finding the ServerName in dbo.ServerList via text search.


[prUpdateInstanceList] – Setup script for 2012


  • MSSQLVersionLong VarChar(MAX)
  • MSSQLVersion VarChar(MAX)
  • MSSQLEdition VarChar(MAX)
  • MSSQLServicePack VarChar(20)
  • InstanceId BigInt


  • Error Code if applicable

Updates dbo.InstanceList with values passed from the script. If things have changed, this will overwrite the old data in the column.


[prUpdateServerList] – Setup script for 2012


  • IPAddress VarChar(20)
  • OSName VarChar(100)
  • OSServicePack VarChar(100)
  • ServerName VarChar(MAX)


  • Error Code if applicable

Adds Server information to dbo.ServerList by matching on the ServerName. This too will overwrite old data with new values passed to it.


[prInsertNewServerAndInstance] – Setup script for 2012


  • ServerName VarChar(MAX)
  • InstanceName VarChar(MAX)


  • Error Code if applicable

Utility that Creates New Server and Instance if they don’t exist already.

I Was Told There’d Be PowerShell

And that’s the end of our Database setup. We now have a DB somewhere with tables and procedures, ready to be populated with our Server\Instance.Database information.

With the frame now in place, seats and tires (nice choice of body color maybe), we are prepared to drop the engine in. We’ll get started on that in our next section.

–CJ Julius

Creating a Simple Database Inventory Manager with Powershell – Part I: Building the Repository Database

I've got it. We'll put the databases in a database
I’ve got it! We’ll put the databases in a database.

This first part is simply setting up the database and the tables underneath of them. I’ve tried to make this as painless as possible by providing scripts do do most of this for you. I’ll use this infrastructure piece to explain some of the data that we’ll be pulling back.

This is by no means an exhaustive list of all the information that can be retrieved, but it serves as a foundation to show how all data could be pulled back to these tables. The only limit is determining how you’re going to retrieve this data.

This part will require some legwork from you as entering the ServerName and InstanceName with corresponding Id fields will be necessary. You should only have to do this once*.

If you’re unclear about what all this is then, and think you missed something, check out the Introduction.


[DBAdmin_QA.sql] – Setup script for 2012 can be retrieved here.

This is the database that will act as the repository for all of the information we want to collect. You can call it whatever you want, as you can change this in the code later, but I don’t recommend it the first time around. Why make it more complicated than it needs to be?

Make sure you take a look at the settings and verify they fit to your environment. It is intentionally small and grows slowly as we most likely will not be making it larger by leaps and bounds.

You will want to also make sure that the user you will be making updates/deletes/etc as, has full access to this database. This is a ‘durh’, but I always have to say it.


[dbo.ServerList.sql] – Setup script for 2012 can be retrieved here.

This is our master list of all Servers that contain SQL instances we want to know about. Initially, you’ll want to insert all the Server Names (fully qualified if necessary for your environment) leaving the other fields blank. I don’t put things in bold unless they’re important.

An example of T-SQL code to insert these items:

INSERT INTO dbo.ServerList
( ServerName )

Notable Columns:

Id – Identity Column for all Servers
ServerName – Manually entered Server Name
IPAddress – IP Address of Server
OSName – Name of the Operating System
OSServicePack – Number of the Service Pack


[dbo.InstanceList.sql] – Setup script for 2012 can be retrieved here.

Contains all of the Instances and the proper ServerListId (Foriegn Key to dbo.ServerList.Id) as well as some related information. You’ll need to INSERT all of the Instances and match them up to the ServerList.Id column when you inserted the servers.

At some point in the future I may add to the code where it dynamically builds the InstanceList, but that has not been done. This is because there are some instances that ‘exist’ on servers are shut down or disabled in some way. This allows the Instance to be listed in your Full Inventory even if it can’t be accessed. Wouldn’t be much of an Inventory if we weren’t able to inventory it.

An example of T-SQL code to insert these items:

INSERT INTO dbo.InstanceList
(InstanceName,ServerListId )

If you’re a lazy bum and don’t like matching ServerList.Id’s to InstanceList.ServerListId:

INSERT INTO dbo.InstanceList
( InstanceName
FROM dbo.ServerList sl
WHERE sl.ServerName = 'SOMESERVER'

UPDATE: I’ve made this even easier with a new stored procedure Utility.prInsertNewServerAndInstance. If this is your first time seeing this then there was no update and this procedure has always been here. Look! Over there! Something distracting!

Notable Columns:

InstanceName – Manually entered name of the Instance
ServerListId  – Manually entered FK to dbo.ServerList.Id
MSSQLVersion – Version of MSSQL running this Instance
MSSQLVersionLong – The long-form version of previous column
MSSQLServicePack – Current Service Pack of the MSSQL engine
MSSQLEdition – Edition of the MSSQL engine
isProduction – Manual bit flag to designate a production instance


[dbo.ServiceList.sql] – Setup script for 2012 can be retrieved here.

The dbo.ServiceList table contains information on MSSQL services running on the server. That does include the MSSQL Database Engine, but also other items such as Reporting Services or Full Text Services.

dbo.ServiceList is dynamically filled so there is no need to add any information to this table.

Notable Columns:

ServiceDisplayName – The Display Name of the Service
ServiceName – The system name for the service
ServiceState – State of the Service (ie “Running”)
ServiceStartMode – Start Mode of Service (ie “Auto”)
ServiceStartName – User the Service is running as


[dbo.DatabaseList.sql] – Setup script for 2012 can be retrieved here.

This table is the dynamically generated list of all the tables in the database. As you’ll see later in the Powershell, anything we can pull back from sys.databases (or anything we can join to it) can be put in this table or a related table.

Notable Columns:

DatabaseName – Name of the database (crazy, right?)
SizeInMB – Size of the database returned in Megabytes

dbo.DatabaseList is dynamically filled so there is no need to add any information to this table.

Database Ready

And that’s it for the tables and database. Keep in mind that this is a basic structure that can be the core of any setup you’d like. Any data you can capture via T-SQL or Powershell can be compiled and put into these tables or better yet, other related tables.

In the next post, I’ll talk about how we’re going to be allowing this information to be put into the database via stored procedures.


*Unless you forgot to make backups and accidentally wipe the tables. In this case, you can think about your mistake while you re-enter all the data again.

–CJ Julius

Creating A Simple Database Inventory Manager with Powershell – Introduction

Which databases names have the letter “B”?

And then we shall rule the world!
And then we shall rule the world!

All DBAs should keep track of their Servers/ Instances/ etc not only for their own edification, but for Management and security reasons as well. If you’re not, then you need to, as it comes in incredibly handy even if it isn’t a requirement of the job.

Most of the time, this information is compiled into a spreadsheet of some kind or possibly in a word processing document somewhere. Keeping this data up-to-date and accurate is a pain, especially when you have to break it out into multiple tabs and/or over multiple documents.

You could get a full-blown inventory manager that collects and compiles all the data and organizes it for you. But there’s a definite cost to that solution and not one that all companies will find useful (Read: “It’s not in the budget this quarter”).

What if you can’t get someone to shell out the money for a product like that? Then you have to either keep with the spreadsheets (yuck) or you need to find another solution with the tools you have.

What’s the Catch?

A simple frontend for your Database Inventory
A simple front-end for your Database Inventory

So, this is my attempt to resolve this issue using two tools that any MSSQL DBA should have: Powershell and SQL Server. I will point to other software products or versions both paid and free below, but the core code should run using things you should already have. That said, here’s my software list:



Also I will be making a few assumptions:

  1. Your infrastructure security is set up using Active Directory.
  2. Setting up a new instance is already done and you can connect to it.
  3. Your personal login or the login you are using to execute the code is able to query the relevant system tables and server info on each of the target systems.
  4. You know your current Server\Instance setup.
  5. There is no fifth thing.

What You Get

By the time this series is finished you’ll have a simple GUI front-end (shown above) for current data with all of your servers, instances and all the information you could want about them.

We can pull back and organize any data that SQL Server or Windows Server can spit out including, but not limited to:

  • OS Version, Service Pack.
  • SQL Services running, their statuses and logon information.
  • SQL Server Instance names, versions, and editions.
  • SQL Server Database names and sizes.
  • Ability to dynamically re-pull any of this information if needed.

I will walk you through my solution piece by piece over 4 posts (5 including this intro) that will consist of the following parts. This list will be updated with links to the different sections as they are released.

Part I: Building the Repository Database and Tables
Part II: Creating the Repository Stored Procedures
Part III: Coding the Data-Pulling Powershell
Part IV: Putting together the GUI Front-end

Addendum I: Simple Database Inventory Manager 2.1
Addendum II: Simple Database Inventory Manager 2.3

See you soon in Part I: Building the Repository Database and Tables

–CJ Julius

MongoDB – Deleting Lots of Data Without Lock

Newer versions of MongoDB (3.x) handle locking better.

MongoDB doesn’t handle locking too well. It’s getting better in new versions (3.2 looks to be a strong contender) with collection and object level locking, but it’s still not great.

In an environment I was working on some time back, there was a need to remove lots of data on a regular basis to keep the size of the database manageable. The developers had implemented a snazzy maintenance system that NULL-ed the large fields that contained payloads for the data in order to save space after their utility was over. This maintenance system was run once a day and locked the collections/databases for that time; the brief period of locking was considered acceptable in this environment.

However, this didn’t free up any space in the system, because of the way MongoDB, (at the time 2.6, but 3.0 seems to have the same issue without Wired Tiger) NULL-ing the field did free up the space, but did not actually allow for new data to be inserted into its place.  The only way to free the space was to take the database offline and repair or compact it once a month. Not an option.

Because of the needs of the system, we were able to delete the whole object and reinsert it without the payload, but this caused excessive overhead and just turned our disk space problem into a CPU and memory one.

It was decided that since we did nightly backups, deleting the entire object was the way to go, but we’d need to do it manually until development could make the appropriate changes to the maintenance system. The problem then arose of deleting at least a week’s worth of data all in one go. This could take several hours and lots of locking if done all at once.

The solution was to have a process that deleted a set of the data from the database, pause for a moment to let other systems insert/delete/update, and it couldn’t require a large development cost (ie the DBA [spoiler: me] had to do it).

My solution as  a patch job was to have a java-script “job” run by a Windows batch script every week to delete data older than a certain date.  You can view my simple mongoMaint batch script on my github here and the javascript file that it calls here.


-CJ Julius

A Collection of Collections of Free Microsoft Books

Image modified from a free one provided by http://www.norebbo.com/
Microsoft has lots of free stuff out there. Image provided by norebbo.

There are a lot of free materials out there for learning Microsoft products, and suprisingly (or not?) a lot of them are from Microsoft themselves. I thought I’d take a moment to organize and collect my list of free resources in the hopes that not only will it help me organize and find what I need, but also help others of you who don’t know about this stuff.

The one main source I’m using here is the MSDN MSsmallBiz  blog with posts by Eric Ligman. There are a massive number of titles to look at, but I’ve not seen them compiled into one place. Keep in mind that some of these are older and all the links may not work. I will update this list in the future if I find new/interesting free education materials in this genre.

The Collections

Huge Collection of 60+ MS titles on various topics

This was the first list to go up and start the whole series. Almost all of the offerings come in multiple formats (PDF, EPUB, MOBI).

Noteworthy sections:

Visual Studio 2010 – Office 365 – Windows 8 – SQL Server 2012


Large Collection of 20+ MS titles on various topics

The second in the series, and the least interesting of the groups, but it does come with some interesting titles. This group only comes in one format: PDF.

Noteworthy sections:

Own Your Space (a book for teens, no really) – SQL Server 2012 Dev Training Kit


Gigantic Collection of 200+ MS titles on various topics

The last group contains quite a few of the previous two sections (but not all, I’ve found). Most are in PDF or DOCX (word) format with a few in portable and non-portable formats thrown in.

Noteworthy sections:

MS Office – Powershell 4.0 (this stuff is really good) – CRM – Quick Start Guide group – even more SQL Server 2012


If you’re looking for information on specific Microsoft technologies or if you’re gearing up for an MS cert, check out the Microsoft Virtual Academy.  They’ve got kind of a neat gamification thing going on where you get points for completing certain courses.


-CJ Julius

Automated MongoDB Restore with Powershell

Powershell 3 & MongoDB 2.6

As I’ve mentioned before, running MongoDB on Windows can present a unique set of challenges since MongoDB is, for all intents and purposes, Linux native. Recently, I was tasked with creating a method by which the following could be done in a Windows environment via automation:

  • Be spun up on a “clean” Server
  • At a specific time.
  • Restore a dumped database.
  • Report any errors.
  • Clean up afterwards.

The purpose of this is two-fold. We want to be able to continuously verify backups that we were making and test the system that would be our restore point should the primary go down.  In sum total, this method would test all of our backup and restore processes. If/When we needed to restore a backup, we would know that backup system worked and the backups were valid (mongorestore does validation on restore).

Since we’re on a Windows system, and we didn’t want to install anything we didn’t have to, the obvious choice was to script it in Powershell 3, assign it as a Scheduled Task and then have it report to a custom Event Log. The Event Log would allow an email alert to be sent out when an error was recorded.


Powershell (Here I used 3 since it was the lowest version with all the Event-Logging methods needed) has a few idiosyncrasies that presented more than one roadblock.  Enumerating each of these would not be terribly informative, as they’re better documented elsewhere, so I’ve narrowed it down to the few that were specific to MongoDB itself.

First of all, launching a straight mongod instance from PS results in the script stopping until the instance shuts down. To get around this, I used two scripts, executed at different times. The first script sets up the environment and starts mongod  with the given parameters.

CMD /C "mongod.exe --port 27017 --smallfiles --dbpath $restoreDir"

The second script does the restore, error reporting and the shuts down the instance. At that point, the first script resumes, cleaning up and exiting.

CMD /C "mongo admin --port 27017 --eval `"db.shutdownServer()`""

You’ll notice that I had to call CMD and then run mongo/mongod within that. This creates a problem when error reporting as the stderr doesn’t always get reported properly to PS from CMD (for reasons I won’t go into here, but if you’re interested check out these two StackOverflow articles here and here).

To get around this, stderr needs to be piped from the CMD into the stdout and put into a string. Also, mongorestore needs the –quiet switch so it doesn’t report extra information into error.

$someerror = CMD /C "mongorestore.exe --port 27017 $dumpDir --quiet" 2>&1

Then later, push that error string, if it exists, into the Event Log. This keeps every piece of informational output from being reported.

if($someerror) {
Write-EventLog -LogName Application -Source "MongoDB Auto-Restore" -EntryType Warning -EventId 10010 -Message "Mongo Auto-Restore: `n$someerror"

Also, you’ll want to delete the mongod.lock file when doing the restore. If this file exists in the dump directory, it will cause the restore to fail. You’ll get an error on the order of “mongorestore does not know what to do with this file.”

if (Test-Path $dumpDir\mongod.lock) {
Remove-Item $dumpDir\mongod.lock

A mongod.lock file usually means that the instance was shut down improperly, but can be generated for other reasons as well. For the purposes of testing a restore environment, it’s superfluous and can be outright deleted.

If you’d like to see my two scripts in their entirety, you can get them from my github.


The next task was to get them running on a schedule, which is less trivial than it sounds. Task Scheduler is simple, and just sends arguments and maybe a path to an executable. Since mongo/mongorestore/monod are producing output, you’ll have to run PS as an executable and send it the file with explicit instructions to not display anything and to allow scripts to be run on the local machine.

Start a Program
-noprofile -nologo -noninteractive -executionpolicy bypass -file .\autoMongoRestore_Run.ps1
Start in:

You’ll also need to give the person executing the script Local Admin permissions (run scripts, create directories etc.) as well as various other abilities through gpedit.msc. The basics of this are outlined in this forum post.


Testing this setup is pretty straight-forward. If you don’t have production dumps that you can initially test with, or that is unreasonable, you can spin up a database, insert a few objects and then dump it. The size and number of the databases shouldn’t be relevant.

First, restore the database(s) manually; Spin up your own mongod and then restore to that instance. Then, set up your scripts to restore this test database and see how it goes.

Next, make sure you test for corruption in both your indexes and the database itself. This is as simple as opening the index/db and replacing a few } or ” with something else.  When the restore runs, it should cancel the restore and throw an error. If you’re using the scripts I provided above, it will write this to the event log.

Lastly, you’re going to want to restore with your actual production data. Depending on your database size, this may take a while, but it’s worth it to see if the backup machine can handle the load and has enough space.

Restore Complete

While this is my method for testing the Windows  restore environment, I’d be interested if anyone else has tried this sort of procedure. I like the ease of this setup, but the fact that two different scripts must be run at differing times seems a bit janky. I’ve tried other ways of starting and then continuing on, such as start /b or running the mongod with –service, but neither of them were stable enough to repeat consistently.

Again, you can check out my scripts on github, or leave me a comment here with any efficiency or method improvements. I’d be interested in seeing them.

-CJ Julius