Tag Archives: github

Simple Database Inventory Manager 2.1

sdim_ver_2-1_trunc_275x400

A while back, I took on a fairly big project; build an database inventory manager that did the following:

  • Dynamically gathered information on SQL Instances/Databases
  • Dynamically gathered information on the OS underneath
  • Compiled and organized this data in a single repository
  • Provided a client GUI front-end for ease of use.
  • Was built on free and already available tools.

When I initially finished Inventory Manger 1.0 I wrote a 5-part series that took the user through the steps of how I built what I did and how everything worked. This was a good way for me to iron out details and also provide some documentation along the way. As the months have progressed I added updates to the code and altered the posts as necessary.

Then everything went silent as I moved on to other things, but I was constantly going back and adding new features and options. During this interim, I was not able to get the code updated on GitHub, and thus it fell behind. Also, it had morphed into its own beast, moving out of “project” and more into a standard “software” mode. To signify its new direction, its name is now Simple Database Inventory Manager™.

As such, I have added the new 2.1 version to its own GitHub project (previously it existed in the SQL code section there with other unrelated snippets) and will update it accordingly. You can just download the new files and overwrite the old ones to get the new version. Spiffy, right?

db_datapull_frontend_v2-1
Look! 200% more buttons!™

I will keep the old 1.x version in the original repository so it doesn’t break links for the walk-through articles.

All that said, what fabulous prizes are in the new version? Glad you asked!

CMS Integration

Now you can point to your Central Management Server and the Simple Database Inventory Manager™ will pull a list of servers and instances from there. It will then go through all of them recursively, and pull whatever data you want back.

In DB_DataPull.ps1, you can switch this on or off with the -UseCMS switch. If on, then you will need to specify the CMS Server with the -CMSServer ‘SOMESERVER’ parameter.

If you use the -UseCMS option, this will delete all data from the repository tables and repopulate them based on what you have in the CMS. This is backwardly compatible with the old system in that if you don’t use the option (off by default) then it will continue to use the Server and Instance List you provided manually.

Fresh, New Buttons

‘Services’ has been folded into the Inventory heading and Other Info is no more, replaced by the Reporting section (below).

Composite buttons have been created to give better side-by-side information from the GUI. Server\Instance and Instance\DB buttons have been added to do this. I think their names are pretty self-explanatory. Click on them. See what happens.

Reporting

Kind of. A bunch of stock reports were added to the DB_DataPull_FrontEnd.ps1 under the Reporting heading. These rely on Views in the Reporting Schema that build customizable information you want returned.

I’m going to leave this schema (Reporting) pretty much alone for now, so users can create their own views and then use those as datasets if you want for SSRS. You can use SDIM™ as the basis for reports that refresh as often as you want to run DB_DataPull.ps1

Here’s a quick list of the out-of-the-box reports given in the GUI:

Servers Grouped by OS and Service Pack – A count of all Server OS names and versions.

Instances Grouped By SQL Version – A count of all Instances at a specific server version (ignores Service Pack)

Instances Grouped by SQL Version, Edition – A count of all instances by SQL Version and Edition (ignores Service Pack)

Instances Grouped By SQL Version, Edition and SP – A count of Instances based on SQL Server Version, Edition and Service Pack

Bug Fixes/Minor Enhancements

  • Fixed an incorrectly spelled column.
  • Fixed incorrect calculation of Server number.
  • Added extended properties to all tables.
  • Added header at the top of all Views and Procedures

And All the Rest

This (Simple Database Inventory Manager™) is of course provided free of charge, use-at-your-own-risk. There is no warranty either expressed or implied. If SDIM™ burns down your data center, uninstalls all your favorite toolbars and ruins your best pair of dress socks, I’m not at fault. Remember to back up your databases!

And if you’ve skipped over everything just to get to the link, here it is: SDIMS v2.1

-CJ Julius

MongoDB – Deleting Lots of Data Without Lock

MongoLock
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

Automated MongoDB Restore with Powershell

mongodb-logo-powershell_sh2
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

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.

Scheduler

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.

Action:
Start a Program
Program/script:
PowerShell
Arguments:
-noprofile -nologo -noninteractive -executionpolicy bypass -file .\autoMongoRestore_Run.ps1
Start in:
C:\Script\Directory

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

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