Author Archives: Wes Brown

About Wes Brown

Wes Brown is Vice President at Cantata Health and maintains his blog at http://www.sqlserverio.com. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

Go, Go, Golang!

WHAT!

 

Yep, you read that right. I’m building tools in Go.

I can hear you now…. But Wes you are a SQL Server guy, a WINDOWS guy! What about C#? What about Powershell? What about ANY THING ELSE???

Making Decisions

Sometimes you make choices, not based on what you want to use but what you must use when building your tool set.

Here was my decision making path I hope it isn’t too complex.

It must run on everything from Windows Server 2003 through Windows 2012 R2 where I don’t have total control over the OS or what is installed on it.

That was it. I had no clue it would be so difficult to accomplish. Let me run down several candidates.

C#/.Net/mono

This is my default go to stack. I have been writing in C# for quite a while and I’m fairly comfortable with the language and love the amount of third party libraries and tooling around it. Oh and ReSharper. Microsoft is also pushing hard to open up C# and all aspects of .Net going so far as buying Xamarin, the developers behind mono. All of that is great! There is still one HUGE downside with the .Net framework. You have to have the .Net framework.

But Wes, the framework has been shipping for YEARS and is already installed on probably 90% of all the servers you are going to be using your tools on! You are absolutely correct, which version though? That is a horrible problem to solve. Pretty much the only way I found to solve it was to only use the 2.0 framework. That meant a HUGE part of the improvements to the .Net framework was now unavailable. Oh yeah, ever try to file a bug report to a third party library developer about a bug that only effects the 2.0 framework? You end up building “new” code and now maintaining legacy code from third parties.

Not all is lost! You can compile a “native app” but that can only target Windows 10, unless you use mono. With mono It is possible to build a stand alone self contained executable for Windows.

I’m saved!

Well…. The up side is I get the new shiny bits but mono doesn’t implement the full .Net framework. Also it is hit or miss on third party libraries and if they will also work with mono. Oh and the build process is right out of the 90’s. If all of that wasn’t enough there are possible licensing issues with embedding the mono run time in your application vs. linking to it as a dynamic library.

Powershell

See Above.

No seriously, it suffers from the same kind of problems. I am back to building scripts that target Powershell V1 and hoping security issues don’t blow up in my face.

Ruby/Python

I don’t want the Ruby or Python folks to flatten my tires, I’m not saying they are the same weakly typed, single-threaded interpreted language. Python doesn’t have Rails. ZING!

All kidding aside, I personally like Python over Ruby but that again is a personal preference. Honestly though I am more old school and really love strictly typed languages.

Both have TONS of third party libraries, that run on Linux or OSX. Windows is a bit thinner though. They both have ways of rapping up their respective run time into an object that looks like a standalone executable. There are several companies running both Ruby and Python on Windows and ship software installers to end users. I know it can be done. I also know it is kinda painful too.

Other Contenders

I didn’t stop there!

I looked again at Nodejs which has tooling to build command line applications. Having built a couple of applications using Nodejs and having no love for the “sideways mountain” that is the async call back pattern of Javascript I gave it a pass.
Lua, another favorite language of mine mostly due to is simplicity and ability to write and then read that same code again later.
Open Pascal/Delphi was given a cursory look but it just doesn’t have the critical mass it once had.
C++ is awesome if you want to crash both your program and the computer. I dare you to figure out what part of the 1000 page specification you aren’t going to use to maintain some level of productivity.
C has a huge draw for me but again it is a double-barrel shotgun but one barrel points backwards.

Finally, Go

I eventually found my way to Googles’ Go language. I had poked around with it several years ago but it was a pain to compile for Windows. Fast forward to 2016 and now Go compiles easily on Windows. Most third party packages are written in Go which means it generally compiles on Windows with zero changes. These executables are statically linked that means executables that will probably be larger than some but come with the bonus of not having to carry around all the run time and libraries or hope they are installed on the machine.
Go, as a language, is simple. It isn’t as terse as say K but it is much closer to C and Python than C# or C++ is.

Go is absolutely opinionated. There is the way I like to write code then there is the Go way. They don’t always agree. You want generics? NOT IN MY LANGUAGE YOU DON’T! Declare a variable and then don’t use it? No compile for you! Want to pick your own way of structuring your source code files? That ain’t going to fly.

These are all choices specifically made to satisfy writing LOTS of code worked on by LOTS of people, a Google’s worth so to speak.

So, why does this matter to me and the hand full of people that may actually have to work on my code?

More than you think. There are suggestions, fences, guide rails and walls in the Go ecosystem. They are there to help prevent bugs and improve productivity at scale. I have worked with a lot of programming languages over the years. My favorite ones allowed me to build stuff quickly and hand it to someone else knowing they could also get up to speed quickly. To that end I am finding that I agree with some of the guiding principles of Go.

Simplicity, safety and readability are paramount.
Minimal design: There is one way to write a piece of code.
It’s about expressing algorithms, not the type system.
Tooling is as important as the language.
Build times shouldn’t take over night.
Compiles naively across all platforms.
Things of interest should be easy; even if that means not everything is possible.

That means I have to give up some control. It also means Go may not be the best choice in all situations. To me Go is starting to feel like T-SQL, it isn’t horribly complicated yet tremendously powerful. I’ll be writing about the good and the bad around Go, or golang if you like, soon. I’m only in my first two weeks with it now.

I Am Not An MVP

But You Can Be

I have been honored to have been a part of this group of most excellent people. As with all good things my time as an MVP has come to an end. With my departure that means someone new will be awarded, or maybe someone who had left the program will be coming back. Ether way, I am making room for someone else who is in the running for the October cycle.

Nothing Has Changed

Yet everything has. My life has taken a turn and I have to chose how to balance my life. It isn’t just about doing SQL Server stuff anymore. I’ve got a lot more responsibility and a lot more to work on and learn about other than SQL Server.

It’s A Balancing Act

Joe Webb has spoken and written about that balancing act. He stunned me when he made the choice to back off from all of his community work. He also shared with me that he wasn’t going to pursue the MVP for a seventh year. I just couldn’t fathom why someone would just give up. Now I understand, he wasn’t giving up. He was making a choice. It’s easy to try and cling to something like the MVP award. There is no doubt it isn’t just the prestige of being able to call yourself a current MVP. There is the access to the SQL Server team, all the perks that Microsoft gives you and other software companies give you too. To me though, I just couldn’t, in good conscience, put my self forward for consideration this year. Even though it would mean losing out on some great stuff it isn’t worth keeping someone else from enjoying those same benefits who have worked for it and deserve a chance at the award.

Making Way For You

I’ve written what it meant to me to be nominated and finally to be awarded. I’ve talked about the fact that this is an award, not a guarantee for your hard work and evangelizing SQL Server. I’ve been crushed when being passed over. I’ve been overjoyed when I was recognized. Now, I’m a bit sad, but hopeful that maybe this will be YOUR YEAR to be overjoyed. If it is I will be overjoyed with you. My only words of advice are don’t ever let something like the MVP award define who you are. You will be awarded because of who you are now, not to be awarded and made into something special, that part has already happened.

 

 

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

Digging Around In Codeplex

Codeplex is probably the best source for Microsoft focused projects. You will find a lot more than just base SQL Server bits. Codeplex covers a large range of technologies and also covers pretty much every aspect of SQL Server. Codeplex isn’t as old as SourceForge so if you do find a project that has been abandoned it may not be too old to use or may be easier to update. When Codeplex finally added git support they on me over in a big bad way. The search feature is a bit simplistic but you still have some sorting and filtering options available to you.

SQL Power Doc

SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.

This thing is just crazy in the amount of data it gathers along with base recommendations it can produce. I’ve used this a few times and have yet to find it lacking. It isn’t the speediest thing but if you need to get a look at an environment for the first time I’d recommend you take it for a spin.

SQL SPADE

SPADE simplifies the process of standing up a new SQL Server instance by applying standard Operating System pre-configurations, Installing SQL Server and then applying post-configurations and creating standard objects.

SPADE is a tool that is designed to speed up your standard deploymets of SQL Server. You may be saying “But I can already do an unsattended install”…but that’s not all that’s involved in most server builds. There are Operating System configurations like Microsoft Distrubuted Transaction Coordinator (MSDTC), Local Security Policy and others. I’m sure that you also have standard SQL objects that need to be deployed like Stored Procedures, Agent Jobs, Operators, etc. All of this can be done by SPADE automatically by running 1 simple PowerShell script.

SQL SPADE is a tool I’ve come to rely on more and more these days. Mike Wells has put a ton of work into automating something that can be difficult at the best of times. This is a complete end to end solution for deploying SQL Server.

DatabaseLint

DBLint is an automated tool for analyzing database designs. DBLints ensures a consistent and maintainable database design by identifying bad design patterns.

Evaluating the quality and consistency of a database schema by a manual review is time-consuming and error-prone. To accommodate this challenge, we propose DBLint, a fast, configurable, and extensible tool for automated analysis of database design. DBLint currently includes 46 design rules derived from good database design practices. The rules discover design errors, which are collected as issues and presented in an interactive report. The issues are used to calculate a score for each table and an overall score. The scores are based on the severities of the issues, their location in the schema, and a table-importance measure. DBLint has been tested extensively on more than 35 real-world schemas, identifying a large number of relevant issues. Developers from four organizations have evaluated DBLint and found it to be useful and relevant, in particular the overall score and report.

This is one of the more unusual yet useful projects I’ve come across in a very long time. I’m a huge fan of lint’ers for lots of other languages having a toolset like this for SQL Server I’ve been evaluating it and will probably be rolled into our build tools.

Up next: GitHub

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