Voice of the DBA

Tag: sql server

  • Very Hot Patches

    “At best it would crash” is not a phrase I’d like to have to use as a data professional. That’s a quote from an article that the Azure team wrote about hot patching SQL Server. While this sounds very scary, it’s actually something being used now to patch the SQL Server code running Azure SQL Database.

    Years ago I read a book where the hero was a programmer that had to alter and hack into live code on a mainframe, making changes to thwart the villains. It was a neat concept, and certainly daunting. As someone that had to write assembly code at one point, I had trouble keeping track of instructions when I could map them out on paper. Doing this on live code would be very scary.

    The SQL Server code is not being changed live by a human, but code is being patched without stopping the sqlsrvr.exe process in Azure. There is a blog on the hot patching process, which I appreciate, though I’m not completely sure I get the minute technical details. Still, it’s an impressive feat of engineering to me, and this does make me wonder to what extent platform engineers might structure their code to allow more of this in the future.

    Deploying changes is already a challenge for many of us with database code. Making changes, evolving our schema and adding functionality without downtime or excessive blocking is a challenge. Many customers that look to move to a database DevOps software development process often assume that our tools will just do this for them. They won’t, because any DevOps tools that help with automation don’t magically get around the limitations and restrictions that Microsoft has built into the platform.

    Making changes in real time, without interrupting workloads involve some engineering challenges, but whether at the SQL Server platform level or the database code level, they are possible. It takes some work, some flexibility, and more importantly, some understanding of how changes can be made and the patterns that enable uninterrupted changes. There is often a space and time trade-off, and certainly no magic, though to our customers, it might appear that way if we do our jobs well.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Fantasy SQL Server

    This past week was the 118th T-SQL Tuesday (hosted by Kevin Chant) and it was a great one. Lots of people participated, with some really interesting entries. Kevin asked people to post their fantast T-SQL (or SQL Server) feature that they wish Microsoft would build. From better defaults and hints to a performance rating to CCI improvements to better partitioning, there are lots of creative solutions. Look for the recap this coming week.

    I didn’t write mine, mostly because I was out of the country and busy with SQL in the City Streamed and then some customer visits, as well as a mini-vacation in London with my wife. As a result, this slipped my mind, but my feature would be two phase authentication for a batch. I’d like a user to be able to submit a batch, have that held in a queue inside SQL Server until another user approved this feature somehow. The implementation doesn’t matter, but requiring two admins (or users) to run something would be fantastic for limiting rogue admins.

    What’s more, I’d use it to schedule something for the future that needed to be done, but I wasn’t sure when, like cleanup of some deployment. I’d write the trigger delete or other cleanup, leave it in queue and then have a job that reminds me of work that’s out there. When I’m ready, another account approves something.

    There were some good features submitted. I like Brent Ozar’s, restoring a single table, which is based on this suggestion with lots of votes. Not likely to happen because the backup process doesn’t know what’s on the pages, it just restores them. However, I’d think this could be added somehow with a scan of system tables inside the backup. Another simple one is better logging of job results. We’ve needed that for a long time, and that seems doable.

    If you didn’t participate, you can still write something, and even submit a suggestion to Microsoft. Doing a T-SQL Tuesday post is a great way to think and exercise your mind a bit. You might even have a great idea that someone notices and Microsoft picks up. You can still write your post and leave a comment on the invitation post.

    Steve Jones

  • Do You Deal with UTF-8?

    Microsoft is adding UTF-8 support in Azure SQL Database, and it will be coming in SQL Server 2019. If you don’t know what this is, perhaps you want to read a bit about it, as it can save space if you have the need to use Unicode characters. This format uses a variable number of bytes to encode characters, and this is often used on the web and email. My question today is:

    Are you looking to store data in UTF-8?

    The way this works with SQL Server can be complex. In fact, not everyone thinks this is really done well, as there are some bugs in the initial versions. As I’ve watched some people try to work with this, it is a very confusing and complex topic. I thought this might be a simple “SQL Server handles everything” collation, but it doesn’t appear that this will be the case. Calculating space needed for data isn’t as simple as I might expect. Not having to prefix strings with N is nice, but I’m not sure that this will actually work in practice.

    I’ve seen some discussions of how to work with this, and it’s complicated. In fact, it’s not easy to tell how much storage you might need for characters. The storage differences can be confusing, depending on the code range you work with. Since most of us know that our users will try to add data we would never expect to our database, and we might run into issues with not enough space. For those of us specifying the size for our columns, we now need to know how many bytes are in use, not characters.

    Likely this is easy for those of us that work in the English world and stick with varchar, but maybe not. I’m curious today how many of you will attempt to work with UTF-8 (or are waiting for it). It would also be good to know about any challenges or issues you’ve had working with the encoding in other systems or languages.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Evergreen SQL

    Colorado is a beautiful state, one that my family and I have enjoyed many times. We like the summer and winter outdoors, including lots of days skiing. We have plenty of trees on the slopes, which is both great to view and a series of obstacles to avoid. Fortunately, the pines and spruces keep their needles all year round and are easy to spot.

    Recently I caught a press release from Microsoft about the Azure SQL Server platform. It was written as support for SQL Server 2008 and R2 comes to an end. Microsoft certainly wants to pressure those customers to upgrade, as there are lots of them out there and this would be quite a bit of revenue. It’s been nearly a decade since R2 and over that for SQL Server 2008. There are a few options Microsoft has for you, though the move to an IaaS system in Azure with 3 years of support might be the only feasible one if you need support for some business reason.

    There are other options, and the post calls out some of these as evergreen SQL. Both the Managed Instance and Azure SQL Database are listed here, because there’s no need to patch or upgrade these platforms. Microsoft handles this for you, though that’s not necessarily as simple as you might expect. I don’t know how vendors will deal with Microsoft upgrading code, but certainly your in-house applications that might be built with workarounds for the various bugs that people stumble upon need to be prepared to change code if the bugs fix and behavior changes.

    I do like the idea of not needing to patch SQL Server and having the code improve and grow. I also like the idea of my code working and not breaking. While Microsoft has noted they don’t plan on removing functionality (deprecated just means there’s a better way you should use), what about the features that have bugs and the current behavior needs to change? That can be challenging for in-house development teams, but also a hassle for ISVs.

    Perhaps this will get ISVs to write code that handles patches and upgrades. Perhaps this means that we won’t get stuck on RTM or SP 1 of some old version of SQL Server because a vendor doesn’t want to test and certify their system on patched code. Perhaps it also means they’ll write the most basic, generic SQL that uses limited features and will work everywhere without them spending any resources verifying their code. I worry the latter more than the former will be the result of evergreen SQL Server.

    Do you want a version of SQL Server, as an instance, a database, or some hosted service that you never patch, but Microsoft does? I wonder how many of you look forward to evergreen SQL for your code.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.