15 January 2013

Blog

  • MySQL has this feature that SQL Server needs

    In the grand scheme of things, MySQL and SQL Server operate in different realms. It’s difficult to compare them because MySQL is predominantly used for websites as part of the free LAMP stack (Linux, Apache, MySQL and PHP), while SQL Server’s range is much broader than that.

    There’s one neat feature that MySQL has, which is missing from SQL Server, and, while it’s not the end of the world and plenty of workarounds exist, I think it would be a good addition to the database engine.

    Let’s say you design a web page, and on that page you need to capture a value represented by a dropdown list or radio button.

    In MySQL, if I have a value from a handful of options that I want to persist in a table, I can use the ENUM (short for enumerator) data type for that column.

    The ENUM data type provides a list of possible values that can be stored in that column. For example, “YES” or “NO”, the digits 1 through 5, that sort of thing.

    If I want to do the same thing in SQL Server, I need to create a separate lookup table and add a foreign key constraint. If I’m sure it will be only two values, I can use a BIT value and use business logic to assign a value to the 0 or 1 that can be stored there.

    Those workarounds are fine, but they seem like overkill for the case where you only have two or three values.

    In the more than two decades I’ve been working with databases, this has admittedly been only a minor inconvenience, but I think SQL Server would benefit from an enumerator data type.

    Let me know what you think on Twitter at @bornsql.

    Photo by Felipe Elioenay on Unsplash.com.

  • SQL Server 2017 Administration Inside Out

    For the last five months or so, I have been helping some really smart people put words on paper, both the physical and electronic kind, which is hopefully going to culminate in an actual technical book that I can point to and say “Yes, that’s the name I invented for myself when we moved to Canada”.

    My own contribution is somewhere in the region of 64,000 words (including the chapters I took over from other authors who had to leave the project), which sounds like a lot until you realize most of the words are “NOTE” or “CAUTION!”. I think William wrote double that amount.

    SQL Server is a massive product and there are lots of moving parts. No single person can know everything, though I did accidentally become the nascent expert in SQL Server on Linux, albeit briefly.

    The book is due to be released early in 2018. I’ve started and never finished enough projects to know that putting a release date on publication is a fool’s errand, so I’ll leave it to Pearson and Microsoft Press to do the hard work.

    You can go and preorder it from Amazon if you’d like. That would be super, because ever since I got paid $25 (Canadian) to be in a TV show, I’ve heard stories that authors of technical books get paid less than actors, and I need the proof in the form of a royalty cheque!

    Until then, I encourage you to download and install a trial version of SQL Server 2017. The installer is really easy to use for both Windows and Linux.

    If you find any mistakes, those are the fault of the other authors. Except chapter 2. That was the first one that I wrote, and it’s probably terrible.

    Throw tomatoes at me on Twitter, at @bornsql.

    Photo by rawpixel.com on Unsplash

  • T-SQL Tuesday: The Contributing Factor

    Ewald Cress writes:

    Find a person or several people to pick on, and tell us a shareable story or two about how they have made a positive contribution in your life.

    I’ve known Ewald for 27 years, though we only met two years ago. It’s wonderful that he is hosting this T-SQL Tuesday.

    My first job was working at an ISP help desk, which lasted a surprisingly long five months. After that I found a job at a consulting firm which worked with PeopleSoft (before the Oracle buyout).

    I was exposed to a number of database platforms, but I didn’t get stuck in there like I should have. I still thank Stuart and Ronnie for teaching me the value of offsite backups, though!

    This post, however, is about my third job, and the man who made a dramatic impact on my life as a data professional. My shoutout for this T-SQL Tuesday is to a very dear friend of mine, Ian van Schalkwyk, who died several years ago.

    Ian convinced me to work with him at the Learning Channel, and together we ran the back-end for a website that for a time was the single best educational resource in South Africa for high school learners, learn.co.za.

    Along with the website, our New Media studio moved into other markets, where I designed my first SQL Server database and wrote my first stored procedure, for a registration system that supported the original launch of the Sunday Times project known as “It’s My Business”.

    After looking back at that code recently, I am surprised nothing broke. There were race conditions. There were scope problems. There were table variables!

    We had tens of thousands of registrations and used the database to send mail to each registered user. That SQL Server 2000 database didn’t go down once.

    I owe so much to Ian, for a number of reasons, and I don’t want to forget his talent and encouragement while I stepped out of my comfort zone into this new world of relational databases.

    He taught me everything he knew about SQL Server and classic ASP. Later, he taught me about how to be classy even in the face of death.

    Thank you, Ian. Thank you, Ewald.

  • Is Transparent Data Encryption just security theatre?

    I love theatre. In six months I am putting on two one-act plays for a local festival, because I don’t already have enough on my plate.

    Security theatre, on the other hand, I don’t like. It is security for the sake of appearances, that offers little to no solution to the problem it claims to solve.

    Arguments have been made that airport security qualifies as security theatre. This is not that argument.

    I am here to argue that Transparent Data Encryption (TDE), an Enterprise-level feature in SQL Server (and available in all service tiers on Azure SQL Database), is not security theatre.

    What is Transparent Data Encryption?

    The short version is that our data, log and backup files are encrypted at rest (i.e., on the storage layer, see Perry’s comment below), so that an attacker cannot simply copy and attach the data and log files, or restore a backup, without having access to the master key. If backup tapes or drives are stolen, the data on those devices cannot be recovered.

    We can also use what is known as a Hardware Security Module (HSM) to provide keys to secure the database. This is a dedicated physical or virtual device, separate from SQL Server, that generates keys for various services in an organization.

    When securing a database with TDE, the documentation makes it clear that the keys should be backed up immediately, and then copied securely offsite.

    One of the biggest arguments against TDE is that it doesn’t protect data in motion, which is a primary attack vector. It is thus considered nothing more than a checkbox item for the compliance officer’s security audit.

    At the risk of sounding flippant, obviously it doesn’t protect data in motion. That’s not what it’s for.

    Defence in depth

    Taken in isolation, there are many ways to thwart practically every security measure used to protect data.

    What a modern security strategy requires is multiple layers of defence, with sensible alerting systems in place at each layer, so that if an attacker manages to break through a particular layer, an organization is aware of the attack and can take action against it.

    The important question when designing a good strategy to defend our data, is to ask who our enemy is.

    If we just want to prevent the developers from having access to the production system, that’s fairly trivial to implement. Certainly a lot of breaches are the result of an inside job.

    But if we need to protect our massive, highly-available online system from a nation-state, the security profile is going to look a whole lot different. TDE definitely forms a part of that larger picture, even if it’s just a small part.

    Consider the rogue administrator, fired or made redundant, who decides to steal a copy of the database before heading out to the competition. With TDE as well as Always Encrypted and several other features included in the box with SQL Server, we’ve just made that person’s life a whole lot more difficult.

    Summary

    Transparent Data Encryption was designed to do one thing, and it does that one thing really well. Instead of calling it a compliance checkbox item, implement it as part of a broader defence strategy.

    Photo by John Salvino on Unsplash.

  • Gimmicks that work: XE Profiler in SQL Server Management Studio

    I have a favourite new feature of SQL Server Management Studio 17.3 (SSMS), and that’s XE Profiler, which allows you to monitor your instance in real time using Extended Events from inside SSMS with just the click of a mouse.

    So is it Extended Events or Profiler?

    Let’s talk about the name XE Profiler quickly. It seems carelessly thought out because Microsoft is blurring the lines between Extended Events (an asynchronous, low-overhead advanced monitoring feature introduced in SQL Server 2008) and SQL Server Profiler (a resource-intensive, synchronous monitoring tool, introduced in the Ancient Times).

    Profiler’s main problem is that if you decide to run it against a live instance of SQL Server, the way most people do, it writes every action into a scrolling window, which adds a lot of overhead, thus being the bane of database administrators. This performance overhead can be mitigated to some degree by using a server-side trace, where the profiler rules are written to a file on the server’s hard drive instead of a scrolling window.

    Not a lot of accidental DBAs know about server-side trace, so Profiler continues to dominate as a performance monitoring tool that ironically makes performance worse.

    I submit that XE Profiler is exactly meant to blur the lines between Extended Events (XE) and SQL Server Profiler (Profiler).

    Its existence in the main user interface that most people use to administer SQL Server is inspired. Calling it XE Profiler means that it has enough similarity in name to an existing (but much slower) feature. People are more likely to click there than open up a brand new application from a menu.

    No love for XE Profiler

    Some people are less convinced about XE Profiler. For example, my friend Dave Mason writes on Twitter:

    He goes on to call it a gimmick. Dave’s argument seems to be that Extended Events (XEvents) has been in the product since SQL Server 2008, and therefore XE Profiler should not be heralded as something wonderful and new because we have all been running Extended Events for almost ten years already, and we all have scripts ready to go at a moment’s notice. Right? Right? Bueller?

    Dave does, maybe. The vast majority of people looking after a SQL Server instance? Not so much.

    It is a gimmick, which is a good thing

    Andy Mallon (far more eloquently than I) proceeded to explain in that thread that this is an excellent feature for people who don’t use Extended Events already, or for people who think Profiler is faster than Extended Events. And by my estimation, there are a lot of them out there. Its very nature as a gimmick—as a quick and easy interface to an existing feature—makes it simpler to use.

    Even better, there are only two sessions you can run on XE Profiler.

    To run a session, scroll down to XE Profiler, expand the node to show the two sessions, right click on one of them and click Launch Session (session-ception).

    Launching an XE Profiler session

    These happen to coincide with the two most common profiler traces that people use on SQL Server Profiler: Standard and TSQL, and, even better, they show the same stuff inside Management Studio.

    Standard and TSQL are the top two choices in SQL Server Profiler

    The output of a Standard XE Profiler session looks like this. In the top pane are the events. In the bottom pane are the details of these events.

    XE Profiler display in SSMS

    SQL Server Profiler is dead. Long live XE Profiler.

%d bloggers like this: