Tag Archives: 2008

Simple Stored Procedure to Compress Old Tables

Pretty sure the SQL Server compression code looks like this.

I’m always looking for a way to save space in SQL Server. From archiving old data to just flat out deleting unused objects, I take great joy in removing superfluous stuff. The less junk in the system, the easier it is to focus on the things that matter.

..and fit it in a 10 kg bag

The biggest useless space eaters are tables that are (supposedly) no longer used. I could script them with data to a file, but what if they’re 100+GB? I could also back them up to another DB and then drop them from the database; that would certainly free up the space in the original DB. What if they’re needed for some process that I was unaware of and we can’t wait for the time to restore/move them back?

My conundrum was this. So, I decided to implement a process that looked at a single DBA-controlled schema and compressed every table created prior to a certain date. I could TRANSFER the superfluous table to that schema, and leave it. At some point in the future a job would come along and compress it.

If the data was needed within X days, then the table could easily be transferred back to the original schema, no harm: no foul. Also, I would save space as tables would be automatically PAGE compressed and could be decompressed if needed. De/Compression is really fast in SQL Server.

It’s Compression Time

So, this super-simple stored procedure was created prCompressCleanupTables (click for github link). It takes the following parameters:

  • @CompressBeforeDate – A DATETIME variable that accepts how old the table must be before it is compressed (Looks at the created date)
  • @Schema – Sysname variable that takes the schema name that you want to compress. Keep in mind that this is the same schema for every database, so make sure it’s unique (I use the ‘Cleanup’ schema personally, hence the name).

It skips the following databases by default: master, tempdb, model, msdb, distribution, ReportServer, SSISDB. It will skip any database that is in any state other than ONLINE, too.

Also remember that compression is locked to certain editions of SQL Server, as well as being 2008+ (you really need to upgrade if being 2008 is a limiting factor).

I’m Also A Client

I have this implemented as a job on several servers which checks weekly for new tables to compress in the appropriate databases. It checks for any tables created prior to GETDATE() – 60. I have to say, that it runs very quickly even on large tables.

Let me know if this is helpful to you!

-CJ Julius

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

Three Job Schedules walk into a bar…
uh-oh
uh-oh.

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',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'SOME-GUID-HERE'

became this:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

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',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_30min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'RunAsSQLAgentServiceStartSchedule',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

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 – 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:

Required:

Preferred:

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

Updates:
2016-11-27
Addendum I: Simple Database Inventory Manager 2.1
2017-05-20
Addendum II: Simple Database Inventory Manager 2.3

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

–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