Category Archives: SQLServerPedia Syndication

Growing It At Home: Building Your Own Tools

Short On Money?

Sometimes that is the motivation. Maybe the tool you want doesn’t really exist yet? For me it was those things and the joy of building something useful. I’ve built dozens if not hundreds of tools in the form of scripts, batch files and executable code. Did you know some of the tools you are paying your employers hard earned money for came up from the same roots? Someone needed a tool to do X, and lo’ there were others who were willing to pay for that work.

Great, Someone Has Written The Tool…

They also want something called money for me to use it. That may be the case but some for profit companies are very open about what they do. One of my personal favorites is Confio’s Ignite. They have NEVER been dodgy about the data they gather or how they gather it. I’ve seen them in presentations say things like “you can gather this exact data from these DMV’s” and show people the query to pull that data. They will gladly show you their schema and how to write your own queries against it. It seems like they are giving away the secret sauce! Yet, they have a very awesome product that I and others are happy to pay for. Sometimes it isn’t just gathering the data it is everything else rapped around the data that is the real value.

I’m Still Broke, Now What?

If you read my last few blog posts you also now know there are a TON of opensource alternatives for the majority of the tools you would pay for. Some are of better quality, some aren’t. Some don’t do everything you want but maybe two of them together do meet your needs. Again, there isn’t a direct cost in dollars but there may be an indirect cost in your time. For me it isn’t just about the time spent but the enjoyment of building something new or building on someone else’s work to meet my specific needs. My overriding mantra for most opensource tools is “Good Enough”. In general, it may not have the polish of X commercial tool but it is good enough.

What I’m Working On Now?

Isn’t that the question of the day. I’ve currently got not one but two irons in the fire. One is an extension of an existing opensource project another is a project that was all my own and now I’ve started to incorporate some work done by others into it.

MSSQLCompressedBackup

This was a project I recently picked up based on a need I had at my work. As some of you know, I’ve got some experience with backup compression and encryption. We are a SQL Server shop. About 70% of my servers are still on 2005 at the moment. I also wanted to move our encryption to the backup level and not at our tape drive level. We really didn’t want to spend the extra money for one of the commercial products out there if we didn’t need to. Fortunately there was an opensource project that came close to meeting our needs. With a little work I was able to add some additional features like fast LZ compression and AES encryption. Is it as fast as all the commercial products out there? No, probably not. It is fast enough for us though. Does it have all the features of the commercial products out there? Again, that would be a big fat no. Right now though, we don’t need the extra bells and whistles that come with those products, if we do and I can’t find an opensource alternative and I can’t build the feature we need then we will look at the cost again.

Central Operations and Reporting Engine

I’ve been kicking this can around for the better part of 15 years. It really came it to focus at my time with The SCOOTER Store. At one point I came close to releasing a commercial version but the timing was off. That hasn’t stopped me from working on it and releasing bits of it in the form of my SQLDIY stuff. I’ve decided to quit just puttering around with it and go full bore again just to see what would happen. Previously everything was written in C# and T-SQL. It had it’s own scheduling engine, reporting engine and GUI. Most of it is woefully out of date now and I’ll be using powershell to do the heaving lifting where possible. I’m also working on a dashboard, web configuration tool and some other reporting bits to make it even more useful. When the project goes up on codeplex I’ll write a full post on it.

Digging Around For Free Tools – Google Code

Digging Around In Google Code

Google Code was very thin when it comes to SQL Server. I did find a few of interesting projects that may appeal to others as well as me. I will say there was a TON of use SQL Server with x programming language.

Project RoundhousE

“Professional Database Versioning and Change Management”

RoundhousE is an automated database deployment (change management) system that allows you to use your current idioms and gain much more.

It seeks to solve both maintenance concerns and ease of deployment. We follow some of the same idioms as other database management systems (SQL scripts), but we are different in that we think about future maintenance concerns. We want to always apply certain scripts (anything stateless like functions, views, stored procedures, and permissions), so we don’t have to throw everything into our change scripts. This seeks to solves future source control concerns. How sweet is it when you can version the database according to your current source control version?

This was probably the most well rounded project I’ve seen on any of the open source repository sites. It was also one of the most complete versioning and change management solutions as well. I also have seen it on one of the other repository sites but this one seemed more up to date.

NCrontab

This article shows how to use NCrontab to generate occurrences of a crontab-style schedule as a table in SQL Server (2005 or later), which can then be used in queries and especially joins to do interesting things.

This is a solid example of using the CLR for something other than regex. Documentation isn’t horrible and it is unique bit of kit.

dbrefactor

C# library for versioning and refactoring database structure using Microsoft SQL Server

I picked this one because it was a good idea but has been abandoned. This is one of those projects that could be revived by the right developer. Refactoring SQL Server schema and code can be a complete pain in the ass and any tool that makes that better is worth looking at, even if it has aged a bit.

Next up:

SourceForge

Digging Around For Free Tools – BitBucket

Digging Around In BitBucket

BitBucket was very thin when it comes to SQL Server. I did find a couple of interesting projects that may appeal to others as well as me.

BonSQL

Turn any version of Microsoft SQL Server into a platform capable of acting just like MongoDB – only you get transactions and the ability to join with traditional relational data as well!

This is a nice example of using newer methods with our good ol’ SQL Server engine.

DeploySQL

Scripts to auto-deploy SQL from SQL Server and to dump database objects to disk (so they can be searched or committed to source control).

There are lots of other projects like this that are designed to aid in versioning and source control. I picked this one because I didn’t want to have just one project from BitBucket 🙂

So, this is why I don’t search BitBucket very often. There just isn’t enough there for me to bother with.

Next up: Google Code

Digging Around For Free Tools – SQL Server

Tool Archaeology, Digging Through The Web

About every three to six months I will burn some time and just go digging for tools. Mostly, I’m looking for stuff related to SQL Server, PowerShell or C# development. There are lots of places to search through that could yield your next life saving tool. All the major sites have a search feature but if you just put in SQL Server you will get a ton of hits that don’t have anything to do with SQL Server. I usually do something like “SQL Server” NOT MySQL NOT Postgresql NOT DB2 NOT Oracle to keep the other databases from cluttering up the results. The other thing that can be difficult is how to filter out projects that don’t have any code or are very old. Most will allow you to sort by downloads or ratings and this can be a good indicator. Lastly, some of the sites don’t really enforce descriptions or comments on the project. Sourceforge is real hit or miss while Codeplex is really good about project descriptions. This can mean the difference in just reading about it and having to dig into the project to get any relevant details. Most of the time, if there isn’t any kind of project description I will just pass them by.

BitBucket

Open source host has some stuff but doesn’t seem to be a big one for Microsoft or SQL Server. I actually had to ~~google~~ Bing how to search BitBucket projects.

Google Code

One of the largest hosts for open source software, again not big on Microsoft or SQL Server. What I have found is lots of stuff to help you connect SQL Server to other things like Python and Node.js

SourceForge

The grand daddy of them all. There is a HUGE amount of projects. Unfortunately, most of them have gone dormant. There are gems to be had here for sure if you are willing do dig for them.

GitHub

The new hotness. The project mix is very good and they tend to be documented. Also, since GitHub isn’t that old you don’t have projects from the 90’s to filter out.

CodePlex

It’s a Microsoft world. You won’t find things like how to make SQL Server to work with X open source language or other tool but all of the projects have a description which is really nice.

My preferences

I spend most of my time on GitHub, SourceForge and Codeplex. I host my projects on GitHub and Codeplex. As far as GitHub is concerned the reason I chose them is I LOVE git. GitHub is easy to use and robust. I also choose Codeplex mainly because if you are Microsoft focused this will be one of the first places to search. Also, now that Codeplex supports git it is much easier to use from a project point of view.

Next we will take a look at some of the projects on each of them to give you an idea on what you can find if you dig hard enough!

Oh, The Free Stuff!

Ah, Shwag

I’ve been doing the whole conference thing for a while. Not as long as some but quite a while. Along the way I’ve collected quite a bit of stuff. I’ve also given away most of it to the local user group too.

Little Things

I always keep a few little things like ducks and note books.

I also keep a few of the drink-ware items too.

Wearable Stuff

Oh the humanity, the amount of clothing I have from conferences is just crazy. I give away quite a bit but always try to keep one shirt or item from everywhere I go.

Carry All Your Stuff

With all those shirts I need all these bags to carry them! I’ve kept some of the bags I’ve been given over the years. My two favorite are the Idera bag I got from the ACE program (which I carry right now) and the SQL Pass summit 2003 bag, which gave up the ghost two years ago when all the stitching for the bottom finally gave way.

Valuable Stuff

In 2004-05 when Microsoft REALLY loved SQL Server they were giving away crazy amounts of software. I gave away over one hundred copies of SQL Server 2005 Developer edition and around a dozen Windows 2003 Enterprise and SQL Server 2005 Standard edition copies too.

Special Stuff, Never To Come Again

I also had the privilege of attending the last great launch event for SQL Server 2005 in San Francisco.

Found This in my drink:

Oh, more software!

Ok, I’m impressed now.

Lastly,

The greatest thing I get from each and every one of these events is meeting people like you! See you at the PASS Summit.