Opinion: Don't reinvent the (database) wheel

There is an old saying about not reinventing the wheel yet this is something that I see happening at client sites every day. I see two main reasons why this happens:


Image by Nathan Dumlao

There are so many tools and frameworks in this industry, that you can't be expected to know them all. I remember when I worked a lot with the .NET framework. I'd go into client sites and see them designing and building classes that were already in the framework. Worse, the framework classes were usually very well designed and tested.

The challenge was that with thousands and thousands of classes (at release I think .NET was over 6,000 classes), it's hard to know what's in there.

Image by Rafael de Nadai

I see developers working with SQL Server and creating tables to hold queues. A table seems the natural way to store queue data but the problem is that it doesn't have many of the semantics that are needed for a great queue.

When I describe Service Broker, the developers are often very surprised to learn that SQL Server already contains a fully transactional queue, already there, right in the database. Often it has all the capabilities that they are trying to implement themselves.

More importantly, Service Broker includes support for things they hadn't even considered. Building a queue sounds simple, but it really isn't.

For example, if you take an entry off the queue, try to apply it, and the transaction rolls back, what happens next? Does your application just stop? Does it try to take the entry off the queue and apply it, only to go bang again?

Service Broker includes poison message support. It lets you control what occurs.

The biggest problem I've seen with Service Broker is that Microsoft promoted it to database people, as it was part of the database. That's completely the wrong target market. It should have been promoted to developer leads and technical architects. I think the marketing for this was completely misdirected.

Not Invented Here

Image by Roman Mager

The other common reason that I come across is the "Not Invented Here" syndrome. There are clients who simply won't ever use code that someone else has created. They are usually concerned about one of these:

  • Takes too long to learn
  • Won't do exactly what I want
  • Don't want a dependency on it

I can't always dismiss this but I do note that the same people won't write their own database management systems or operating systems. (Although I think many of them would prefer to if they had time)

The problem with this is that the outcome for their own clients is usually substandard, and worse, their products are likely to become noncompetitive.

For example, you can write your own reporting system and/or dashboard system, but you'll probably get a better outcome if you use Reporting Services and/or Power BI. I've seen some of my own clients write their own reporting systems, but they are extremely low-functioning, and are generally unable to be integrated with any other tools that their customers use.

When you are starting to create new functionality, at least please consider that what you're after might already exist, and in a better form than you would ever have time or experience to create.

Opinion: To find good staff, invest in communications, not buildings

Many of my customers are software houses (ISVs). In almost all of them, I hear people complaining that they can't find enough good staff. I think they are trying to tackle the wrong problem.

Most of the staff members they are trying to find are developers, and there are so many great developers out there, but you have to accept that they might not be in the location that you hope they're in.

I've seen companies struggling to hire the last remaining developers for various technologies, from those available in that city. This often even leads to crazy situations like hiring staff that they have previously rejected. They should not be drilling further and further down the talent pool in their location.

Worse, the more that companies hire poorly qualified staff, the more their experienced staff will be spending their times fixing issues caused by staff that should never have been hired in the first place. This is a formula for losing your best staff.

Some temporary respite might come from convincing people to move to the same city, but that's a very limited group of people who can do that or will be willing to do that.

So many jobs are now able to be performed from distributed locations, and development jobs are prime examples of these.

Why hire poorly qualified locals at ever-increasing costs when there are many outstanding people in other locations?

Instead of trying to fight for the last remaining staff in a city, and paying ever increasing salaries to ever less capable staff, companies should make a serious commitment to enabling remote work, and investing in really high quality communications infrastructure and probably some software. It's important to get out of the mindset of needing to have all your staff in your building.

Image by Richard Jaimes

It's time to look outside. It's a big world out there, full of amazing people who could be doing great work for you.

Opinion: Constant churn breaks community learning for software applications

A current trend that I can't say that I love is constant churn within software applications. I have no interest to go back to the days where we got a new version of SQL Server or Power BI, etc. every few years.

It's also not a case of who moved my cheese?

In fact, I thrive on change. However, I've now become really concerned about how anyone:

  • Learns to use a complex application
  • Remembers how to use a complex application when they don't use it daily

I first really struck this issue with Azure. If I was teaching a class that used Azure, I could check every single lab on Sunday night, then Monday morning, the students would find it had all changed. That's OK for an experienced person, but not OK for a learner.

I love the rate of change for Power BI. We're endlessly getting wonderful new things. But I have to say that every class that I teach on this is like a new experience. I've got another one this coming Tuesday. I used to look forward to them but now I have a major hesitation every time, as I wonder what parts of the labs will have broken.

This is now an ongoing challenge for all this type of software though. I helped create some labs for VSTS very recently, and when I look at the product now, it barely resembles the one that I built the labs on.

Is it better? Probably yes.

But even though it might have been a few months ago, it feels like just the other week, and yet, not only has the UI changed, entire concepts have been added or removed, and the order that things are done in has changed substantially.

I don't know the answer to this but the current rate of churn is a substantial issue.

I gather the plan with the DevOps guys is to put a set of labs on GitHub, and let people who are doing the labs point out the issues day by day as they strike them. Again, for experienced users that might work. But for newcomers, I really wonder if that's what they'll think.

Will they realize the app must have changed, and it's all different, or will they just think the product is too hard to use. Either way, they'll be very frustrated.

Image by JeShoots

And while initial learning the product is one thing, I'm worried about it longer-term. A product like VSTS lets you set up automation and you hope you won't need to change it constantly. But if every time you go to make a change, you're struggling to use it like you're a newbie again, that's a problem.

Finally, I'm really concerned about ongoing support.

The vast majority of support of software applications today happens from community resources like blogs, webcasts, etc.

Will they continue to be created at the same pace if the authors know they'll be irrelevant or wrong within a very short time? How will end-users learn to do things when none of the online examples they find still work?

I wish I knew the answer to this.

Opinion: There's a plague we need to stop

I've concluded that many software vendors (particularly large ones) don't understand how much support users of their software provide to each other, and how critical that support is.

The SQL and data communities are a good example of this. When someone has a problem and are wondering how to solve it, they don't call Microsoft or Google or Oracle (or whichever vendor) first. If they're lucky, they ask a colleague for help. But most will simply make a Google search (or yes a Bing search) to try to find an answer.

No matter how obscure an error message might be, if someone else has struggled with it before, at least there's a chance that on an online forum, someone will have spelled out what caused it for them.

Even cryptic values like app IDs in Windows that look like this:


can be matched to an error or an application that's causing the error.

Most of this happens without the vendor even being involved.

So one of my pet hates (which Microsoft have heard loud and clear on internal mailing lists) is applications that break this pattern.

Every time I have an error that says:

and nothing else, I want to scream. Even that's enough to get an answer sometimes. "Every time I click on XXX and drag the YYY, I get an error saying Oops. Something went wrong!" might lead to a posting that solves the issue but it's so much tougher when there's no other info.

A plea to developers:

At the time the error occurs, even if you don't know exactly what happened, you must know something about what you expected and what happened. Tell us something. No matter how cryptic.

Another related trend is where there is an error message but it's a GUID:


And we think: "Great. We have something to work with" only to find that the GUID changes every time the error occurs and is only meaningful to the support team at the vendor organization.

Please don't do this either.

Give us something repeatable that we can use to help each other.

Opinion: You have to live and breathe the technology to be good at it

Digital Transformation and Cloud Transformation are phrases that I hear bandied around at nearly every large organization that I currently doing consulting work for.

Yet, in so many cases, I can't see the organization achieving the changes required. This is for two core reasons:

  • The first is that the culture within the organizations is a major hurdle. There just isn't enough flexibility to think outside the box about alternative ways to work.
  • Worse (and probably more concerning), I see these companies taking advice on how to make these transformations from companies who don't themselves "get it".

An organization that is cloud-antagonistic internally, and stuck in an endless IT management quagmire, isn't likely to make a good cloud transformation, and they're certainly not going to be a successful partner to be able to help you to make a successful cloud migration or to implement a cloud transformation within your company.

An organization that doesn't use business intelligence (BI) or analytics internally isn't going to be able to help you make that transition either.

If the organization is claiming to be proficient in an area of technology, ask them about the use that they are making themselves of those same technologies. As a simple example, ask them about their internal analytics that they can see on their own phones.

To be any good at any of these areas of technology, companies need to live and breathe them daily. If they don't, find someone to help you who does.

Opinion: Vendors who prevent patching should be liable for issues

When many SQL Server customers are asked why they haven't kept up to date with either SQL Server versions, or more importantly, patches to SQL Server, the answers usually boil down to two reasons:

  • They are just slack
  • Their vendors won't support the later version or patch level

Many SQL Server application vendors don't keep up to date with testing of their applications on released versions or patches for SQL Server.

While I can understand a hesitation to quickly support later versions of the product, refusing to support later patches of supported versions is particularly concerning. Worse, actively telling customers to avoid installing security patches is deeply troubling.

Preventing clients from installing security patches is simply not reasonable.

If there is a proven issue with a patch, that's understandable. But if the main reason is that the vendor just hasn't done the work to test the patch, I believe that vendors who do this need to bear liability for any ensuing issues that occur, regardless of their license agreement that might try to exclude consequential damages from use or inability to use their products.


Opinion: Case Sensitivity is a Pox on Computing

Case sensitivity in comparisons is an aspect of computing that I'm surprised is still so widespread. I can't say it more clearly than this:

It's a pox on computing and needs to be eradicated.


I've recently been working at a site at present where a new case-sensitive SQL Server system is being implemented. I cannot begin to describe what a poor idea I think this is.

In the end, all that a case sensitive system allows you to do is to have:

  • Multiple identifiers exist
  • They exist in the same scope
  • The names of the identifiers differ only by case

You'd have a hard time convincing me that that would ever be a good idea.

At least not for a system used by typical humans. No sensible person is ever going to be comfortable with "John Smith" being a different name to "john smith". And similarly, do you really want a single database table with a CustomerID column, a CustomerId column, and a customerID column?

Well I certainly don't.

In the new system that I mentioned, there are columns ending in ID, Id, etc. They haven't even been consistent in the naming of their case-sensitive objects.

And yes, I hear the "but what about private variables vs properties in languages like C#?" complaint:

  • Age is the object's property
  • age is where the object stores the value assigned to the property

Surely we can come up with a better naming convention than that. I've lost count of how many times I've seen people using the property when they meant the private variable or vice versa. It's just not sensible.

Now before I hear complaints that case matters, be clear that I'm not talking about case preservation; that's an entirely different thing. Yes, if I defined a column as CustomerName, I don't care if I query it by customername, Customername, etc. I want it coming back as CustomerName ie: however I defined it. Case preservation is a virtue; it's case sensitivity that I see as an almost always painful and unnecessary thing.

Worse, if you've ever tested an application against a case-sensitive server, you'll understand the challenges involved. It's hard to get case-sensitive code correct.

I gather that SQL Server Management Studio has a current bug that arises when you remove and re-add a database from an availability group on a case-sensitive server. Why? It appears that they aliased a table with A in one place, and used the alias as a in another place. It's really nonsense to have a situation where that matters but it highlights the other big issue. It makes for fragile applications.

Image by Michał Parzuchowski

Do you really want to be the one who's testing all your applications and 3rd party utilities to find out if they've tested case-sensitivity properly? Do the tools that you use have a better testing regime than SSMS? I'll bet that most don't. And what that means is that you get to spend your life wading through obscure tooling issues.

No thanks. Life is too short.

Opinion: Treat Staff like Adults

There's a nasty trend that I've seen at a number of sites in recent years. It's the tendency to try to block and or censor anything that the company thinks might be an issue. Some companies are so concerned about their IP (intellectual property) that they even try to stop any potential leak of that property.

While on the surface, that all might seem to make sense, it's not sensible. It's unproductive.

  • When staff can't look up the syntax of things like BULK INSERT because the system flags it as a shopping site (which is forbidden), you've stopped people working effectively.
  • When staff can't look up how to work efficiently with threads because the discussion they want to read happens to be about threading in games (and gaming sites are blocked), you've stopped people working effectively.
  • When staff can't look at content from a presentation because it's shared on a site like SlideShare (and sharing sites are blocked), you've stopped people working effectively.
  • When staff systems work at one third of the normal speed because of all the "protective" code that you've added (I have a personal dislike for the impact of tools like CyberArk), you've stopped people working effectively.
  • When staff can't download a presentation or code sample because it's on a Google Drive, Azure Storage Account, etc. (because they could be used for sharing files), you've stopped people working effectively.

And so on, and so on.

Automated censorship has never worked effectively yet. (Machine Learning and AI might change that).  And this is no different.

Rather than try to guess and block all the things you fear staff might look at or use, please consider treating them as adults. A better set of rules for technical staff like developers is:

  • We recognize that you are a professional staff member.
  • We don't block what you do.
  • We will, however, monitor all your usage.
  • You might be required to justify your usage.
  • We've detailed the types of things that are never allowed (that don't fit with the company ethos or the law) and some do attract significant penalties.

These auto-censoring and blocking tools that try to keep you safe end up just causing angst and frustration, don't work very effectively, and from what I've seen, almost always end up with staff finding ways to circumvent them.

Worst of all, treating the staff like children won't make them love your company. Neither will anything that keeps blocking them from doing their jobs.

SQL: Best Way to Scale SQL Server Database Performance

I see so much written about how to scale SQL Server systems, and this generally starts with needing to improve SQL Server database performance. When I read articles from the SQL Server field support teams with titles like Top 10 Performance Problems for SQL Server, I often just smile.

The problem is one of perspective. If you are looking at the performance problems that are brought to the support teams to solve, you get a very, very skewed view of what's typical.

That's because most performance problems are dealt with by database developers, DBAs, application developers, etc. long before they'd go anywhere near product support. So the product support team never sees the most common problems.

If I had to rate performance problems by cause, I'd suggest the following:

  • 40% of issues are related to poor application design
  • 25% of issues are related to poor indexing
  • 20% of issues are related to how the database framework that the application has used talks to the database (either by design or by how it's been configured)
  • 10% of issues are related to poor concurrency design (blocking issues)
  • The remaining 5% are odd things, and generally the ones that go to product support.

But if I had to provide my #1 tip for improving database performance and increasing scale, it's this:

Stop talking to the database!

It's that simple. So many applications that I work with are very, very chatty.

A year back, I stepped through the login process of a web application. A single login ended up causing over 400 calls to the database. That should probably be one or two.

I've seen processes that send 55 million calls to the database and run in half an hour. But the same process runs in 2 minutes when the number of database calls is cut to 4000.

I've seen windows apps that execute 90,000 remote procedure calls before the first screen comes up. (Loading 90,000 customers one row by agonizing row at a time). It's a tribute to SQL Server that the application started in about 30 seconds, but that should have been one call, not 90,000.

I've recently seen frameworks that have been misconfigured. For every select of a single row of data, the framework executed this:

  • Create a cursor for the command
  • Open the cursor
  • Fetch from the cursor
  • Fetch again from the cursor (this one failed)
  • Close the cursor
  • Re-open the cursor
  • Query for the parameter metadata
  • Close the cursor

And that was done every single time that a single SELECT should have been sent. The problem is that the developers using frameworks like this are blissfully aware of the commands being generated for them in the background.

I've seen caching errors where an application ran the same query with the same parameters over 12,000 times per minute. The developers were unaware of this. (They had a typo in the caching code but it "looked like it worked").

The bottom line is that you need to spend time looking at how your applications interact with the database. If you execute 400 commands every time someone logs on, you will never get any real scale.

I'll say it again. The best way to scale a SQL Server database (and to get better performance from it) is to stop talking to it incessantly.


DevOps: Microsoft Professional Program for DevOps

In the second half of 2016, I enrolled in the Microsoft Professional Program for Data Science, and completed it in early 2017. I have to say that I really enjoyed it overall. It was a bit challenging at times but I don't regret doing it.

If you want to get the certification, you need to enroll in the verified option for each course. Nowadays, that's pretty much $99 USD per course. You can do it for free, and if you're tight on funds, perhaps that's what you should do. I like to support the concept, and like to support both Microsoft and edX for creating these options. They are doing amazing work, so while I hear people say to just do the courses and not contribute to them, I can't say that I agree.

edX and their partners offer an incredible range of world-class courses that you can take for free, but if you want them to continue, you should consider contributing. And that applies to the non-Microsoft ones too.

I think that programs like these are more likely to be the real future for Microsoft certification in general.

Earlier this year, Microsoft created a Professional Program for DevOps. I've had an interest in DevOps for a long time, and I got the opportunity to help create one of the courses DevOps for Databases with the inimitable Steve Jones from Redgate Software. Databases are a specifically-challenging area for DevOps.

A few months back I decided to start pursuing this professional program as well. I've got one course to go (the container one) before the final capstone project. I can finish that container course in the next three months, but unfortunately the capstone project won't be available until April.

Here's the overall program:

Over the last few weeks, I've been involved in enhancing the existing Monitoring and Testing courses, and am looking forward to seeing how people find the updated versions.

To support my continuing interest in DevOps, in the upcoming weeks, you'll see DevOps-related posts from me.