T-SQL Tuesday #46 – Rube Goldberg Machine

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Rube Goldberg Machine”. If you want to read the opening post, please click the image below to go to the party-starter: Rick Krueger (Blog | @DataOgre).

This months topic is about being creative with SQL Server, and how you sometimes bend the rules a little bit, to fix a problem. This might not be the best solution, but it’s the only solution or quickest solution at the time. Everyone has a story like that, and so do I…

Just like a normal project: no budget
A few years back, I worked for a company as a web developer and team-DBA. One of our projects was to build a new warning system, so administrators and developers knew if something went wrong in the production environment. But the checks (like heartbeats, disk space checks, etc.) needed to be stored in SQL Server. But instead of creating budget for a new SQL Server, they told us to solve it with SQL Express.

The version we used was SQL Server 2005 Express, and all went well in the development phase. But the moment we tested the new system in the test environment, the data grew exponentially within a few hours. And after a few days, we stopped the test, because we couldn’t use a single database anymore. The checks generated close to 4 GB of data each month, and that’s the limit for SQL Server 2005 Express edition.

Solving it, MacGyver-style
So we needed to come up with a solution within a few days. And there was no possibility to change to a full SQL Server license. So we needed to find a solution, that worked with SQL Express. We finally solved it with a 3rd party tool, that was able to run SQL Server Agent jobs on the machine, which contained a single step, that started a stored procedure. This stored procedure contained a lot of dynamic SQL (yes, I’m sorry, but we had no other option at the time), that moved data into an archive database.

The job ran every night at a few minutes past midnight. The stored procedure first checked if there was data in the database, that was older than today. If so, it than checked if there was an archive database for that month. If there wasn’t a database, it created a database with a dynamic name: “Archive” + “_” + %ApplicationName% + “_” + %Month% + “-” + %Year%.

So now that we made sure there was an archive database, we moved the data from the day(s) before today to the archive database. The flow would look something like this:

Don’t try this at home kids!
So you can image that, looking back at this solution, I’m not proud of the path we were forced to choose. The technical solution however, is something that I look back on with pride. Back then I just started working with SQL Server, and didn’t have a lot of experience with building these types of solutions. But the solution we build was pretty stable. The only downside was, that if the job didn’t run at night for some reason, we needed to move the data by hand during the day. And because the database ran in “production” (there was a possibility of running transactions) we needed to move the data bit by bit, without locking the SQL server. This meant that if the job didn’t ran, I would spend most of the day moving data, waiting for that operation to finish, moving the next chunk of data, and so on.

So in the end, the man hours we put into it probably didn’t weigh up to a SQL Server license, but that would have made a cleaner solution. But in the end, the manager was right (unfortunately). We never found the time after that to perfect the checks, and the system administrators went with another 3rd party application, because it was easier to maintain. So a full SQL Server license would have been wasted money after all, if we couldn’t use that for another project. But looking back, it was a great experience to build it, and to design such a solution.