The Daily Travails of a Reluctant DBA

SQL Patch Generator

carpdeus@gmail.com — Thu, 23 Jun 2011 01:43:24 GMT

Over the last several months, I’ve had to write patch scripts for database updates. Writing the scripts tends to be easy and I came up with a fairly effective way to implement changes but writing the actual script got to be tedious. So, like any good programmer, I automated the task.

My Patch Implementation

There are a lot of ways to write patch scripts, but one main concern is that you don’t overwrite procedures with old versions. For my environment, I came up with a fairly simple solution for procedures and user defined functions. Every update to a procedure or UDF creates a time stamped named version of itself. If I was generating a patched version of auditing.GeneralLogInsert today, I would check to see if auditing.[GeneralLogInsert-2011-06-22] exists. If it does, then I don’t proceed any further with that stored procedure. If it doesn’t, then I populate an nvarchar(max) variable with the create statement for the procedure/UDF, execute a rename on the existing object and then execute the create. It looks something like this:

IF NOT EXISTS(SELECT * FROM sys.all_objects WHERE name = GeneralLogInsert-2011-06-22' and schema_name(schema_id) = 'auditing')
BEGIN
declare @SQLCommand nvarchar(max)
SET @SQLCommand = '-- =============================================
-- Author:        Josef Finsel
-- Create date: 2010-11-20
-- Description:    Record Log Information
-- =============================================
CREATE PROCEDURE auditing.GeneralLogInsert
           (@DomainName nvarchar(50)
           ,@FolderPath nvarchar(2083)
           ,@PageName nvarchar(2083)
           ,@Method nvarchar(10)
           ,@IPAddress nvarchar(50)
           ,@UserID nvarchar(255)
           ,@LogEvent nvarchar(100)
           ,@LogMessage nvarchar(max)
           ,@ParameterInformation xml)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
declare @InternalUserID bigint
INSERT INTO [auditing].[GeneralLog]
           ([DateLogged]
           ,[DomainName]
           ,[FolderPath]
           ,[PageName]
           ,[Method]
           ,[IPAddress]
           ,InternalUserID
           ,[LogEvent]
           ,[LogMessage]
           ,[ParameterInformation])
     VALUES
           (getdate()
           ,@DomainName
           ,@FolderPath
           ,@PageName
           ,@Method
           ,@IPAddress
           ,@InternalUserID
           ,@LogEvent
           ,@LogMessage
           ,@ParameterInformation)
END
'
exec sp_rename 'auditing.GeneralLogInsert, GeneralLogInsert-2011-06-22', 'object';
EXEC (@SQLCommand)
END
GO

To generate a script like this, I used to have to go into SSMS, right click on the object and script as create to new query window, remove the header information, change all sinqle quotes to double single quotes and then put it into the script. On more than one occasion I found myself having to fix scripts simply because I didn’t match the object names up correctly. Now I don’t have to do this, I can use my utility to create the script block for me.

Running the Utility

The heart of the utility is actually in a class that can be expanded on and used in a number of places, but I have put the class inside an EXE that can be called. From there I can redirect the output into a file.

GenerateSQLPatch.exe -? -c[onnection]:dbConnection -s[chema]:SchemaName -o[bjectname]:ObjectName -update
-? shows this message
-c[onnection] the connection string used to connect to the server
-s[chema] the schema name of the object
-o[bjectname] name of the object to be created)
-update Flag to indicate whether this is a new object or update of an existing

Get the Code

I’ve posted the code in an open project on GitHub. You can find it here. Feel free to modify it to suit your needs and to offer up contributions for improving it.

Technorati Tags: SQL Server,Patching,Utilities

Git Along

carpdeus@gmail.com — Tue, 08 Feb 2011 13:43:47 GMT

So, I’ve been slowly but surely switching my version/source control over to Git. And, for the purposes of having some open source code for that, I’ve set up a GitHub project for storing useful SQL Scripts at https://github.com/CarpDeus/ReluctantDBAScripts. At the moment I only have two scripts there but I’ll be going through my list of scripts and adding more as I go through my archives.

To Infinity and BEYOND!-ish

carpdeus@gmail.com — Fri, 26 Mar 2010 13:22:22 GMT

So, this morning I encountered an error that was new to me, one of those errors that makes perfect sense when you think about it, however. The error was one that offered common sense help as well that is totally useless:

Sorry for the image, I couldn't get a screen shot except by using my camera phone... for some reason Paint Shop Pro wouldn't capture the float.

Yes, when casting from a number, the value must be less than infinity. Which makes one wonder how much less than infinity? C#'s unsigned, 64-byte int (uint64) supports a value of 18446744073709551615. The following line of code:

UInt64 ui = Convert.ToUInt64("18446744073709551616");

results in a Value was either too large or too small for a UInt64. Leaving aside for a moment the fact that this is a past tense statement which might imply that the value could be right-sized now and the grammatical error of using a before a vowel sound and U is definitely a vowel sound, it's a fairly concise error. Also, it has a finitely defined value. The error above can easily be recreated, just use the following:

UInt64 ui = Convert.ToUInt64(DBNull.Value);

This moment of silliness has been brought to you by the letters M,S and C and the symbol #.

To Infinity and BEYOND!-ish

carpdeus@gmail.com — Fri, 26 Mar 2010 13:22:04 GMT

Sorry for the image, I couldn't get a screen shot except by using my camera phone... for some reason Paint Shop Pro wouldn't capture the float.

UInt64 ui = Convert.ToUInt64("18446744073709551616");

UInt64 ui = Convert.ToUInt64(DBNull.Value);

This moment of silliness has been brought to you by the letters M,S and C and the symbol #.

MDD vs. TDD in Development

carpdeus@gmail.com — Fri, 12 Feb 2010 19:45:41 GMT

There has been a surge in recent years involving Test Driven Development, something that Mike Amundsen and I were discussing last night. It's not a development style that we work with much and that discussion led to this post and a new style of programming we call MDD, матрёшка doll development. матрёшка (Matryoshka) dolls are nesting dolls, looking something like this:

To understand the MDD, let's look at TDD and some of the reasons it seems to have exploded in popularity. One reason that I posited was that TDD is really great for quickly creating demo software. Create the stub of a class, wire it up to a UI and you have a working, though non-functional demo that can become functional as stub methods are replaced with functional code. Stubbing code is a good way to write code, but TDD requires such things, and requires writing the tests before writing functional code. But while this is a benefit to TDD, it turns out there is a very large downside to TDD and that is the tight coupling of code.

TDD is a way to force code to be specific and stateful. The code has to match consistently in all ways in order for it to work properly. And, while you may be saying to yourself that such tight binding is good, I suspect that the scenario can be broken down quickly to show that tight binding is not as beneficial as some folks want you to believe.

For an analogy, let's look at a house, built in TDD mode. It's a simple, shotgun house with a Living Room, Dining Room, Kitchen and Bedroom in that order. All we want to do is to change the dining room table from a small 2 person table to a larger 6 place setting. So we simply rebuild the entire shotgun house with the new dining room and all is fine and dandy.

Except that anyone who wants to enter the house now needs to know it has a dining room table for 6 instead of 2. Doesn't matter if they are doing nothing more than opening the front door to check to see if there's a package waiting to be delivered in the living room and have no knowledge of the Dining Room nor care about the Dining Room, they need to know that the Dining Room now has a bigger table because the dining room is tightly coupled to the living room.

Yes, a whole suite of tests can be run against that shotgun house to make sure nothing broke, but those test are probably going to also know the dining room table expanded, even if they don't go into the living room. Because tightly bound TDD is like the матрёшка dolls in that everything has to fit together exactly so and everything is statefully bound. And that, to me, is a major problem.

There is an answer, but it's not one that most people are going to like, and that's Stateless programming. Instead of tightly binding everything together, make sure every piece of code stands alone and doesn't require knowledge of anything else to get the job done. Let me open the living room door and see if there's a pacakge to deliver without requiring any knowledge of the rest of the house because I don't care about the rest of the house. The state of a package in the dining room doesn't matter to me, I only care about the package when it's in the living room.

When I think of Stateless programming, I think of 3 descriptions that give it an advantage over TDD:

It's more flexible. I can change up my dining room and it may or may not affect folks using the dining room but I certainly don't need to affect anyone who is using some other functionality.
That flexibility makes it more Agile. I can implement my changes without it affecting anything else.
It's more demanding to code statelessly. I'm not gonna lie about this, but the extra demands in writing stateless code provide the flexibility that's missing from TDD. And that's an advantage because it's that minor inconvenience of writing more demanding code that creates the flexibility and agility of stateless code.

I'm sure that there are a lot of programmers and architects who have read that list and are ready to start telling me that I am mis-using the word Agile and that TDD provides very Agile paradigms. But they are using Agile in a specific discipline. And TDD is agile like a Ford Mondeo while stateless programming is agile like a Ferarri 599. To understand the difference, let's take a quick look at the dreaded F word that Microsoft is so fond of, Frameworks.

I think everything coming out of Redmond lately has a three letter acronym (TLA) ending in F. And there are times when a Framework makes a lot of sense, but not all the time. Using a Framework in your application can be very much like installing a full-blown transportation infrastructure, complete with subways and buses when all you want to build is a dirt track for you go-cart. Even if you're plan is to build a stock car track, you're not going to be using subways or buses or ferries. But a lot of projects use Frameworks and I think I know why.

Microsoft likes to say they have taken care of the plumbing that we programmers can focus on more challenging issues. Over the years, however, that plumbing has grown in complexity to the point that using the various Frameworks is less helpful than not; rather, it is more helpful to start but far less helpful as you go along.

Let's say that you're on a team that's starting out a new project. Using the various TLA's Microsoft has provided, you can quickly put together a prototype, kind of like going to a large rental company and asking for transportation and getting a fleet of Ford Mondeos. They are perfectly adequate cars for transportation, easy to get and everyone knows how to use them. But will they actually take your project where it needs to go?

There are companies in Minnesota that retrofit your cars to be more prepared for winter by replacing tires with tracks, something like this:

Retrofitting a vehicle can be far more expensive than designing it the way you need from the beginning. It can also be more time-consuming. And when managers see a demo whipped up in a couple of weeks they wonder why it takes so long to get a real project out the door and finished. This is one consequence of using a framework incorrectly and an expense in both dollars and time that applies to many projects.

How can you tell when are you using a framework incorrectly? When the framework constrains your programming, forcing you to contort your program into the shape of the framework:

A man goes into a tailor shop to buy a new suit. The tailor helps him try one on. It looks good. But then he sees that one leg is too long. The tailor suggests that he hitch it up. Then the arm is too long. He hitches that up, and so on. The man is confused but everyone has told him that this is the best tailor in town so he accepts the adjustments, he admires himself in the glass and purchases the suit. As he hobbles down the street holding his suit carefully in place, two men come along. One exclaims, “Look at that poor man!” The other replies, “Yes, but he's got a mighty fine tailor.”

When you start hobbling your program to fit the constraints of the framework, you're doing it wrong. If your framework makes you build out an entire transportation system to enable you to race cars around a track, you're doing it wrong. And if your framework requires everyone to know the size of the dining room table to check for a package in the living room, you're doing it wrong.

Finally, if your framework has tight binding and requires state knowledge outside of the piece you're working on, you're doing it wrong. All state should be self-contained. If it's not, you're increasing your scalability issues and hobbling your program.

I'll be talking a great deal more about state and non-framework programming over the next several weeks as I finally launch a new series on using Azure for building truly scalable projects.

And I'll be doing my best to avoid матрёшка doll development.

Josef Finsel

Azure-Architect.com

Technorati Tags: Azure,Test Driven Development,TDD,MDD,матрёшка Doll Development,Stateless vs Stateful,REST

Azure SQL Scripter

carpdeus@gmail.com — Tue, 10 Nov 2009 10:00:17 GMT

So, you've got an Azure SQL Database and have been doing some work in it/on it and then find out it is a CTP and you need to move the data and schema to another database.

Which isn't bad but there aren't any good tools for scripting an Azure SQL database, mostly because Azure SQL doesn't support using the DMO, which is what most people use.

So, I wrote my own.

It's not perfect, it's missing a few things (doesn't handle triggers, for instance) but it should be good enough to get you started.

It's simple enough. Download and run the code (or Install the app), enter the connection string to the server and a file name to store the script and click on Backup. The utility will walk the database, scripting tables, constraints, indices, views, stored procedures and functions.

Each object is wrapped in an IF EXISTS so you should be able to run it multiple times.

If there's something missing and you want to add it, add it and send me the code and I'll incorporate it.

If there's something missing and you don't know how to add it, leave feedback and I'll see if I can incorporate it.

Keep watching both this space and CloudQuotes. I've got some exciting new projects that will be pushing some boundaries, including a handler for AzureStorage apps.

Josef Finsel
AzureDBA

Technorati Tags: SQL Azure,Utilities

10,000 Lines of Code in 10 Minutes!

carpdeus@gmail.com — Thu, 13 Aug 2009 12:51:07 GMT

The title of this post comes from an ad for one of those code generators that scans your database and creates an application with little to no work on the part of the developer. Sounds impressive, but what would impress more is the right 10,000 lines of code. Mark Twain said it best:

The difference between the almost right word & the right word is really a large matter--it's the difference between the lightning bug and the lightning.
- Letter to George Bainton, 10/15/1888

The same goes for code. If you want a simple example, look at sorting algorithms. Bubble sort is the one that almost everyone comes across when first learning to sort data, and they quickly find that there are other ways to do it, some of which are more efficient in certain situations than others. The difference between the right sort routine and the wrong sort routine is the difference between a scalable program and one that has limitations.

Within limitations, I don't care if the code is 10,000 lines or 10 lines, I care that it does the job I want as efficiently and flexibly as possible. And it's that last part that causes the most consternation when using code generators. Creating a DataSet class in Visual Studio works fine for rough prototyping but I find it's almost as quick and easy to write the class from scratch so it does what I want it to do, not just come close the way a code generator does.

Just my .02

On Starting Over

carpdeus@gmail.com — Fri, 24 Jul 2009 12:39:58 GMT

Luciano E. Guerche was recently lamenting the costs of changing frameworks, especially within the Microsoft world. He said:

If you want Winforms app to turn a WPF app? Rewrite it from scratch? Come on M$...

To which I replied:

(re)Writing from scratch enables you to avoid the bugs in the original app and write brand new ones! (yes, bugs)

It was tongue in cheek and very reminiscent of an old saw about bugs in code:

Every program has at least one bug and can be shortened by at least one instruction from which, by induction, one can deduce that every program can be reduced to one instruction which doesn't work.

But it also had a couple of truths in it. Frameworks are wonderful things. Engineers and architects use frameworks all the time. The Statue of Liberty is copper over an internal framework of steel. But changing that framework isn't easy. And the same thing applies to writing code.

Microsoft has a litany of Frameworks, three letter acronyms that end in F (WCF, WPF, etc) and all designed to help programmers "not have to be plumbers". And I'm all for not having to re-do the internal plumbing every time, but that doesn't mean I need a framework. Frameworks are like jigsaw puzzle pieces and they only tend to go together one way and they build the exact same thing each time. What I, as a programmer, need is a set of tools like Legos or Tinker Toys or Lincoln Logs. And the beauty of all three of those items just mentioned is I can and have used them together to make some incredible things.

Not only that, if a part of it doesn't work for what I need any more, I can dismantle that one part and change it without having to rebuild the entire structure, which is what Luciano was complaining about.

Just my .02 for today

Pool Lessons

carpdeus@gmail.com — Fri, 10 Jul 2009 13:03:05 GMT

I used to work in an office that had a pool table, one of those really expensive, championship caliber ones. Since it didn't cost me anything to use it, I would use it a lot as a break from my net time. And I come to some conclusions that I wrote back then. Not quite a "Everything I need to know I learned from pool" but some things that are as applicable now as they were 7 years ago....

Finesse can be more important than power.
Oh how true. Like many things in life, I started out shooting pool by just trying to ram things through. I can be quite powerful and when I break with all my might, the balls scatter to the four corners of the table. But most of the time finesse is required over brute force. If you have the perfect shot lined up, particularly a straight shot, and power the ball into the pocket, odds are the cue ball will follow it in and you've wasted that shot.
Remember the goal of the game.
When I was in college, back in the mid-80s, I got hooked on 9-ball. The rules are simple. You have to hit the lowest numbered ball on the table. The object of the game is simple. Sink the 9-ball. As I was playing this weekend I had a nice shot lined up and hit the 4 which banked off and sank the 9, effectively ending the game. Since then, I've paid more attention to where the 9 is and whether it can be pocketed. Before that, my sole focus was on sinking the lowest numbered ball. The same applies to life in general. Getting caught up in the day to day minutuae can lead us to losing sight of our ultimate goal.
Hail Mary's may work but don't count on them.
This is an old sports term. You know, end of the game, your just a little behind and you throw a pass the length of the field or attempt to sink a basket from the other end of the court with little more than a prayer that it will work. I find myself doing that sometimes when shooting pool. Sometimes because I'm lazy or because I don't have a good shot I just wind up the cue and let loose. Earlier today I sank three balls on one shot that way. Mighty impressive sounding but I couldn't duplicate it so it really isn't all that great. I mean, if I did that on purpose when it counted, that would be one thing. But the shot was totally unplanned and a result of pure luck.
Know the difference between sinking a ball and sinking the right ball.
Sometimes I'll shoot straight pool and actually alternate between stripes and solids as though I were two players. And sometimes I just rack the balls and shoot whatever is available. The key is that you have to remember whether you are supposed to sink a specific ball or if you can sink any ball. It's that way with the decisions we make in life too.
Don't forget to look ahead.
Pool, like chess or any other game, requires more than just the shot you are attempting to make. It also requires that you think about where you want to leave the cue ball so you can make the next shot. And that requires practice, just like in real life.

A Parable Applied to Developers

carpdeus@gmail.com — Sat, 04 Jul 2009 19:55:35 GMT

One of my friends was recently musing on whether they should attend DevLink or not and, in their musings, said it all depended upon what the SQL Tracks were going to be. For some reason, that particular incident reminded me of the parable of the Beam:

Judge not, that ye be not judged.
For with what judgment ye judge, ye shall be judged: and with what measure ye mete, it shall be measured to you again.
And why beholdest thou the mote that is in thy brother's eye, but considerest not the beam that is in thine own eye?
Or how wilt thou say to thy brother, Let me pull out the mote out of thine eye; and, behold, a beam is in thine own eye?
Thou hypocrite, first cast out the beam out of thine own eye; and then shalt thou see clearly to cast out the mote out of thy brother's eye.
Matthew 7:1-5

Regardless of the original intent of the parable, I think it's very applicable to developers (myself included) when it comes to technology in general and programming languages in specific.

I would say that most developers who have been around for more than a couple of years have been forced to learn a new technology, even if it's only the new version of their current technology. And most of us are very good at some part of that technology. I can list a lot of developers who really rock at SQL Server or C# or VB(.NET) or, well, you get the idea. But that very focus can have a detrimental effect on us as developers if we reach a point where we filter everything through that technology, an effect that seems to manifest itself in two different ways.

The first is the "My technology is the greatest thing in the world and it's a true panacea." SQL Server is great, but I really wouldn't want to write a T-SQL stored procedure to handle a large amount of string processing, because that's something SQL Server does neither well nor efficiently. And yet I have known a developer or two whose answer to everything is, "We can do that in the database." Not many but still...

The second, and far more insidious effect is that of putting blinders on that filter out anything not related to our chosen technology. When .NET first came out and C# was introduced, there were a lot of VB programmers that took the "They can have my VB when they pry it out of my cold, dead hands" stance, some of whom even refused to look at the code. The real shame of putting on blinders that limit our technology focus to our chosen technology is that it artificially eliminates items that may not be totally relevant to our job but have an impact on how we do our work.

There have been times that attending sessions or reading about a technology totally unrelated to mine has made me rethink some of the ways I'm handling things in my day to day technology. Sometimes those have been directly beneficial. Sometimes they have not worked but led me down a path that was. And sometimes it ended up being just an interesting intellectual exercise following along. But in each of those cases, I was sharpening my saw, which is always a good thing.

Technorati Tags: Parable,Saw Sharpening,Blinders