Tag Archives: data

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

SQL Server on Linux: First 30 minutes

They put a ring on it.
They put a ring on it.

What I gushed over a few posts ago has finally happened! SQL Server has a come to Linux (sort of). The database engine is now available as CTP1 and you can get it by adding the repository and running the setup script.

You can follow the walk through for your favorite flavor of Linux, so I won’t repeat that here. it’s really very simple, just a matter of pointing to the correct repository and then apt-get install (Ubuntu). It comes with a setup script that pretty much does all the heavy lifting for you. Keep in mind that this is just for preview so there’s not a lot of options and it sticks everything in a single set of directories (logs/data/tempdb).

I had a small problem when I did the install, but it turned out I just needed to update a few packages. In the event you’re not a Linux person, here’s the easiest way to fix this:

$ sudo apt-get update
$ sudo apt-get upgrade

There’s a lot of stuff to dig into in this release, and as newer versions come out I’ll get more in-depth, but I just wanted to make a quick post about what I did in my first thirty minutes.

Behold in awe my INSERT abilities.
Behold in awe my INSERT abilities.

After the install, I connected via SQLCMD, as there is no SSMS in Linux yet, using the sa and sa password set in the install. I then created a table, dropping a single row into it and then selecting. Not terribly complex stuff.

I took care to try different cases, adding and neglecting brackets ‘[]’ and semicolons. It responded how I expected it to react if I was on a Windows system, which is very reassuring. It’s nice that my T-SQL skills translate seamlessly to the Linux environment, at least internally to SQL Server.

Connected via a my own username.
It doesn’t look or act any different than it I would have if connected to a Windows SQL Server instance.

Next, I put my box ‘U64’ on the network and lo-and-behold I was able to remote into it by its Linux hostname from SSMS 2016 on a Windows machine. No additional setup was required. Microsoft appears to be taking this integration of the Linux and Windows environments seriously.

I then created a SQL login for myself and logged in that way. No issues.

Now, as fun as this was, there’s a whole lot missing. The list includes, but is not limited to:

  • Full-text Search
  • Replication
  • Extended Stored Procedures
  • AD authentication
  • SQL Server Agent
  • SSIS
  • SSAS

This is of course just for CTP1, so a lot of these items will probably show up later. I mean, SQL Server without the SQL Server Agent? That doesn’t even make sense (I’m looking at you Express Edition). There is sort of cascade effect as other items like Maintenance Plans and such that rely on these missing features also being MIA.

The gang's all here!
The gang’s all here!

Also, larger items like Availability groups will also be absent because there’s no Linux analogue for them currently. From what the SQL Server team said in their AMA on reddit they’re toying around with RedHat clustering as a replacement for this in the Linux environment.

The last thing I did before the end of my 30 minutes was to look at the version. As you may or may not know, the Linux version is based on SQL Server vNext, which (as the name implies) is the NEXT version of SQL Server. There was some talk about it being a port of SQL Server 2016, which does not appear to be the case.

SELECT @@VERSION
----------------------------------------------------
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Ubuntu 16.04.1 LTS)

Note that SQL Server 2016 is version 13.0.

And that’s it! As mentioned before I’ll be doing deeper dives into this as time goes on, at the very least with each CTP. But I have to say I’m happy with the results so far. Everything (that was available) worked as I expected it to work. Nice work MS!

-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

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

Vietnam and My Interest in the Minutiae

Image (c)2010 by Think0.
Image (c)2010 by Think0.

As I had mentioned on a placeholder post (since deleted) I have been in Vietnam for the past few weeks on an academic trip. This blog is generally geared towards technology, so I won’t be focusing on my trip per se, but on the technology I encountered there. There are a few things of interesting note to me and perhaps others that are part of every day life in Vietnam. I decided to combine these all into this one post.

This has got to be a nightmare for installers.
This has got to be a nightmare for installers.

Keep in mind that this is from an American’s point of view, so some of this stuff may be, and is, used all over the world, but this was my first encounter with it in mass. The air conditioning systems mentioned later are a good example of this minutiae that I find interesting, but is probably old-hat for people who’ve always used this stuff.

The first thing that struck me when I arrived was the cabling over the streets. While Vietnam is generally well “wired” in the sense that basic broadband was available in the cities I went to, the majority of it seems to be above ground. Cabling that would normally be hidden beneath the streets was up on posts, creating some very haphazard-looking displays close to that of spider webbing.

I actually saw some installers putting in some new wiring, but I was unable to catch any video of it. It mostly involved threading the wiring around the post and to its destination. It wasn’t clear to me how they were differentiating different cables from each other, or how they were avoiding cross-talk and interference, or if they were even concerned about that.

One of the wiring boxes that I, uh, "found" open.
One of the wiring boxes that I, uh, “found” open.

Speaking of being wired, the city of Da Nang was in the process of implementing a city-wide WiFi service. Even though it wasn’t officially available (it should be by the time this post hits) I was able to use it almost everywhere in the city with varying levels of success. It was about what you’d expect from a public wireless service. Useful, but not as robust as a privately-owned system.

3G service was fairly ubiquitous, and the VNMobile Blackberry that I had been given had signal just about everywhere I went. I did not have the ability to test data transfer speeds, but 3-4 bars was present in most locations, and cities were generally solid throughout. Mobile devices themselves were everywhere, just as in any city anywhere in the world, though I saw much fewer tablets than state-side. I’m not sure the reason for this, but I imagine transportation might be part of it. Most Vietnamese ride motorbikes so maybe finding a place for a device of that size is difficult. I can only speculate.

The timers on the lights are a really neat idea.
The timers on the lights are a really neat idea.

Moving on to more minutiae, the traffic light systems are quite similar to what you’ll find in just about every country, with the addition of a timer. Especially in the larger cities, lights had timers that would tell you how many seconds until it would change. It was my understanding that this was prevent people from preempting the lights and causing accidents, as well letting motorists check their mobile devices or do other things at a stop light without holding up traffic when it suddenly went green.

Also, while this might be odd to point out, the air conditioners, both in private residences I visited as well as in most hotels, were these single-room setups. They were mostly operated by a remote, and as I found out later, called “ductless” air conditioners. Here in the United States, A/C units are usually large affairs (especially in the case of central heating and air), even the small units, and have to be planted on the outside of a residence. The ones I encountered in Vietnam used less power, could be placed anywhere in a building and were hyper-efficient. However, they had the drawback of not quite offering quite the cooling power of some of the Western ones that I’m accustomed to.

The A/C on the inside feeds through a tube to a fan mounted somewhere on the outside of the building.
The A/C on the inside (top) feeds through a tube to a fan (bottom) mounted somewhere on the outside of the building.

Lastly, along the same line as the air conditioners, the most common type of water heater was not a tank water heater as is common in the States. Almost every place I went used in-line tankless water heaters. These work by heating water as it’s used rather than heating and holding it until use. These can be set up to heat with electricity (the most common I saw), natural gas or even propane. The only problem I had with these was that they sometimes didn’t get hot enough or took a long time to get “warmed up”. Again, very efficient but not as robust as the tank ones I’m used to using in the US.

I did a lot more on this trip than look at water heaters and street lights, but I thought that these little tidbits were the best suited for this blog. I find the differences in the technology that people use on a daily basis the most interesting, as all “good” technology intertwines itself seamlessly into our lives.

-CJ Julius