Category Archives: SQL Server

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!

Finding SQL Server Installs Using Powershell

Old Dog, New Tricks

I’ve been writing tools for SQL Server for a lot of years. Some of these tools were never completely released. Some of them were just for me. Some of them overlapped other tools already on the market and free for all. Recently, I started updating my bag of tricks and tools. This seemed like a great time to get back into PowerShell. I decided to pull out a bit of C# code I cobbled together nine years ago as part of a tool to find SQL Server instances on a network. I never really got around to making it a “production” ready tool since there was already a most excellent one on the scene in the form of SQLPing from Chip Andrews. SQLPing is a fantastic scan tool and can scan many more things than the method covered here.

Hello Operator?

When Microsoft implemented named instances with SQL Server 2000 they had to have a way to direct incoming traffic pointed to a single IP to the correct TCP port. So, they built in a traffic cop. When SQL Server 2005 came around it was pulled from the core network listener and put into its own service, the browser service. There was little documentation on how the browser worked. Luckily for poor sods like me using a network packet sniffing tool it was pretty easy to figure out how to talk to the browser. Once I figured out how to get the browser service to tell me what instances it knew about it was trivial to implement. These days Microsoft is being much more open about these kinds of things and actually have released documentation on how the browser service and the SQL Server Resolution Protocol works.

The Basic Mechanics.

As most of you know SQL Server’s default instance is on 1433 and the browser service is on 1434. Our goal is to send a UDP packet to port 1434. According to the docs we only need to send a single byte containing the number two. This prompts the listener to give us a list of instances and what port they are bound to. When I wrote my implementation it really was that simple. I dug around and figured out how to get PowerShell to send a UDP packet. I tested it and lo’ it worked, on my machine….

It’s Never So Easy.

When I tested it on my lab VM cluster with multiple nodes and multiple instances it would fail! I just didn’t get it. My C# code from the stone age worked just fine. My PowerShell code was a hit or miss. I started troubleshooting the problem just as I had in the beginning. I fired up my network sniffer and watched the traffic flow back and fourth. Again, I saw exactly what I expected, or more accurately what I wanted to see.  A single packet with the hex number two in the first byte position. I ran the test several times over the next hour or so. Eventually, I just had to walk away from it. The next day I started over again. I read the documentation, it still said the same thing. I ran the test, still a two in the first byte position. Then I spotted it. The packet wasn’t one byte long. I went back and read the document again. It gives an upper boundary of 32 bytes but no lower limit. The packet I saw come through wasn’t one byte long it was always more than that. Armed with that I started big and worked my way down until I got errors. Now I know that a packet of three bytes always triggers a response. a  two with two zeros.

Meet QuerySQLListener.

Here is the function I put together. It takes a server name and returns an array of strings with the fun bits in it.

function QuerySQLListener{
    [cmdletbinding(
        DefaultParameterSetName = '',
        ConfirmImpact = "low"
    )]
    Param(
        [Parameter(
            Mandatory = $True,
            Position = 0,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [string]$Computer
    )
    Begin {
        $ErrorActionPreference = "SilentlyContinue"
        $Port = 1434
        $ConnectionTimeout = 1000
        $Responses  = @();
    }
    Process {
        $UDPClient = new-Object system.Net.Sockets.Udpclient
        $UDPClient.client.ReceiveTimeout = $ConnectionTimeout
        $IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
        $UDPClient.Connect($IPAddress,$Port)
        $ToASCII = new-object system.text.asciiencoding
        $UDPPacket = 0x02,0x00,0x00
        Try {
            $UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
            $UDPClient.Client.Blocking = $True
            [void]$UDPClient.Send($UDPPacket,UDPPacket.length)
            $BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
            [string]$Response = $ToASCII.GetString($BytesRecived)
            $res = ""
            If ($Response) {
                $Response = $Response.Substring(3,$Response.Length-3).Replace(";;","~")
                #$i = 0;
                $Response.Split("~") | ForEach {
                $Responses += $_
            }
            $socket = $null;
            $UDPClient.close()
        }
        }
        Catch {
            $Error[0].ToString()
            $UDPClient.Close()
        }
    }
    End {
        return ,$Responses
    }
}

 

It Isn’t Perfect But It Works.

I”m sure there is a cleaner way to implement it but I’m really just getting into PowerShell again after several months of tinkering with it last time. If you have any suggestions or improvements I’ll gladly take them!

Changing Directions

I See Dead Tech….

Knowing when a technology is dying is always a good skill to have. Like most of my generation we weren’t the first on the computer scene but lived through several of it’s more painful transitions. As a college student I was forced to learn antiquated technologies and languages. I had to take a semester of COBOL. I also had to take two years of assembler for the IBM 390 mainframe and another year of assembler for the x86 focused on the i386 when the Pentium was already on the market. Again and again I’ve been forced to invest time in dying technologies. Well not any more!

Hard drives are dead LONG LIVE SOLID STATE!

I set the data on a delicate rinse cycle

I’m done with spinning disks. Since IBM invented them in nineteen and fifty seven they haven’t improved much over the years. They got smaller and faster yes but they never got sexier than the original. I mean, my mom was born in the fifties, I don’t want to be associated with something that old and way uncool. Wouldn’t you much rather have something at least invented in the modern age in your state of the art server?

Don’t you want the new hotness?

I mean seriously, isn’t this much cooler? I’m not building any new servers or desktop systems unless they are sporting flash drives. But don’t think this will last. You must stay vigilant, NAND flash won’t age like a fine wine ether. There will be something new in a few years and you must be willing to spend whatever it takes to deploy the “solid state killer” when it comes out.

Tell Gandpa Relational is Soooo last century

The relational model was developed by Dr. EF Codd while at IBM in 1970, two years before I was born. Using some fancy math called tuple calculus he proved that the relational model was better at seeking data on these new “hard drives” that IBM had laying around. That later tuned into relational algebra that is used today. Holy cow! I hated algebra AND calculus in high school why would I want to work with that crap now?

NoSQL Is The Future!

PhD’s, all neck ties and crazy gray hair.

Internet Scale, web 2.0 has a much better haircut.

In this new fast paced world of web 2.0 and databases that have to go all the way to Internet scale, the old crusty relational databases just can’t hang. Enter, NoSQL! I know that NoSQL covers a lot of different technologies, but some of the core things they do very well is scale up to millions of users and I need to scale that high. They do this by side stepping things like relationships, transactions and verified writes to disk. This makes them blazingly fast! Plus, I don’t have to learn any SQL languages, I can stay with what I love best javascript and JSON. Personally, I think MongoDB is the best of the bunch they don’t have a ton of fancy PhD’s, they are getting it done in the real world! Hey, they have a Success Engineer for crying out loud!!! Plus if you are using Ruby, Python, Erlang or any other real Web 2.0 language it just works out of the box. Don’t flame me about your NoSQL solution and why it is better, I just don’t care. I’m gearing up to hit all the major NoSQL conferences this year and canceling all my SQL Server related stuff. So long PASS Summit, no more hanging out with people obsessed with outdated skills.

Head in the CLOUD

Racks and Racks of Spaghetti photo by: Andrew McKaskill

Do you want this to manage?

Or this?

With all that said, I probably won’t be building to many more servers anyway. There is a new way of getting your data and servers without the hassle of buying hardware and securing it, THE CLOUD!

“Cloud computing is computation, software, data access, and storage services that do not require end-user knowledge of the physical location and configuration of the system that delivers the services. Parallels to this concept can be drawn with the electricity grid where end-users consume power resources without any necessary understanding of the component devices in the grid required to provide the service.” http://en.wikipedia.org/wiki/Cloud_computing

Now that’s what I’m talking about! I just plug in my code and out comes money. I don’t need to know how it all works on the back end. I’m all about convenient, on-demand network access to a shared pool of configurable computing resources. You know, kind of like when I was at college and sent my program to a sysadmin to get a time slice on the mainframe. I don’t need to know the details just run my program. Heck, I can even have a private cloud connected to other public and private clouds to make up The Intercloud(tm). Now that is sexy!

To my new ends I will be closing this blog and starting up NoSQLServerNoIOTheCloud.com to document my new jersey, I’ll only be posting once a year though, on April 1st.

See you next year!