Voice of the DBA

Tag: sql server

  • More Overloads

    I was attending the SQL Nexus and SQL Bits conferences recently in Europe. These were launch events for SQL Server 2016, and the keynotes given by Microsoft included the overload of yet another SQL Server term. This time ACID was taken to be: Algorithms, Computer/Cloud, IoT, and Data. While I appreciate someone trying to be memorable or interesting in a keynote, to me, this creates confusion when we try to discuss the importance and positioning of SQL Server. ACID is a core term for relational databases, having nothing to do with the future. While some marketing people probably enjoyed this, I would have preferred they spent a little more time coming up with a term that describes a creative look at the future.

    There have been plenty of “overloaded” terms from the SQL Server platform. DAC, is that a connection or part of a database schema package? Snapshots? We have a few of them. When we talk about logs, is this meaning transaction logs or logs for the SQL Server process? Or even Agent logs. Are clusters meant to be FCIs or traditional shared storage clusters? At least that last one is close to same meaning for both terms.

    There are others, which creates confusion and could result in problems if two of think we’re discussing the same topic, but we’re not. I’d hope we’d realize there are ambiguity in a few minutes, but what if we’re dealing with a crisis? Clear meaning and understanding are important.

    I don’t mean to be pedantic, but I don’t think it’s too much to ask that new features, new concepts be given new names. While marketing might think there’s a neat sound to an old acronym, for us technical people it’s annoying, and potentially a liability. If we will continue to have marketing people influence the ever growing platform, the least we can have is creative marketing individuals that can come up with new ways to describe the hard work completed by the SQL Server development team.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Half Baked Features

    I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

    The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

    However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

    Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

    There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Solving Tempdb Issues

    While reading Remus Rusanu’s piece on SQL Server 2016 development, there was an item that struck me. There’s a part of the piece where he notes that an engineer at Microsoft realized that there was a tempdb issue with Azure SQL Database. To solve it, a failover was needed, which could be problematic. The basic issue was that tempdb files were sized unequally, discovered after “months of investigation.”

    Now, on one hand this seems silly. After all, we’ve known since SQL Server 2005 that the guidance was for all files to be sized the same. Shouldn’t engineers at Microsoft be following all the practices  known for optimizing SQL Server performance? I think all Microsoft people should follow this, especially those working on other products (*cough* Sharepoint *cough*), but at the very least SQL Server engineers should have a huge list, perhaps with PBM or unit tests, that warn about non-compliance.

    On the other hand, since we’ve known this is an issue since SQL Server 2005, why does SQL Server allow this? I’d think some simple tooling and a few checks in code could eliminate this as an issue in any new install. Catch the issue during upgrades and grow (or shrink) files to be the same size, or at least warn the administrator. I know there are situations where you need to add a new file in an emergency, but shouldn’t this be an item we push administrators to correct quickly after the emergency is past? Or at least force them to consciously make an exception.

    There are plenty of limitations and restrictions in SQL Server systems that Microsoft forces on us. We have limits on where new features can be used, various historical limits on function parameters, and more. Why not also just enforce limits that prevent issues like this? I’m sure people will complain, but wouldn’t those complaints be outweighed by less issues overall from all customers?

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Cloud First

    SQL Server 2016 is the first “cloud first” release of SQL Server, as told to us by Remus Rusanu, former Microsoft SQL Server development team member. The features and enhancements in SQL Server 2016 have been running in the Azure SQL Database cloud for some time before they will be packaged up and released as the on-premise SQL Server product that most of us expect to work with.

    There are a number of items about this worth discussing, but one stands out to me. The idea of using large numbers of feature flags and deploying code to the “cloud” for use by customers and internal Microsoft people is interesting. On one hand, it’s really just a faster way of having beta software examined by users other than developers, with no install/uninstall/upgrade for users. Speed is good, but being on the bleeding edge and having systems break isn’t necessarily what any of us want. However the use of these flags to turn features off quickly means that disruptions can be minimized for individual customers.

    Some of the engineering process changes made to be cloud first were important for Microsoft to have one main branch of code for SQL Server. Having a separate branch for the cloud and on-premise versions had to be inefficient and problematic. However, that’s not something we, as customers, care about. We just want the database platform to work, wherever we run may run it.

    I do think that having features come out in Azure, whether private testing, public preview, or general availability is good. The people that can test these features give feedback quickly, and the rest of us aren’t affected by the problem code. More importantly, the developers at Microsoft get the chance to learn more about how the features will be used and tune them before a very wide set of on-premise customers get code. Personally I was glad to see things like Row Level Security (RLS) appear in Azure first (Mar 2015), and come later to the on-premise version (SQL Server 2016).

    I really expect in the future that almost every feature that is added to the SQL Server platform will be run in some form in Azure before it’s released for on-premise use. In fact, I’d be surprised if features are added to the on-premise without 6-12 months of testing in the cloud. That’s good, and I hope it leads to an increase in code quality for those of us that will run SQL Server on our own hardware.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and LibSyn.