Voice of the DBA

Category: Editorial

  • Are Indexes Actually Changes to the System?

    I haven’t thought about this in some time, but Brent Ozar raises an interesting question: should index changes require change control? I’ve worked in an organization that didn’t consider an index change to be an item that was presented as an official change. They were seen somewhat like adding new users or changing a firewall rule. These were ticket requests that could be triaged and made by an individual group without notifying the rest of IT.

    I’ve also worked in organisations where an index change was viewed as a deployment and subject to change control procedures. Not that the network or security people cared about the details of the index, though the help desk might. These just wanted to be aware as any deployment change could affect their departments.

    In today’s world where many companies seek to adapt faster to changing customer demands or market opportunities, I think the idea of meeting every week to discuss any upgrades or changes to applications is somewhat silly. Certainly large changes, like major application upgrades to ERP systems, new security hardware, and alteration of core infrastructure ought to be debated in a larger group. Deploying changes to an application isn’t one I’d think deserves the debate. At least not if your organization is trying to become more efficient.

    I do think a way to avoid issues is to work more closely with the development group if you are in operations or vice versa. I’d be sure to let other app teams know about index changes? Certainly Brent lists a number of potential problems, many of which could occur. A new feature might break and prevent inserts, exports could use more disk space, imports might eat up data and backup space, queries could cause slowdowns or blocking. These are all possible, maybe even probable.

    I agree with Brent in that any changes need to be tested. I don’t know about any sort of formal change control, but there ought to be a process that evaluates the changes and mimics them in an environment that allows you to determine the production impact. Using a known, automated process with testing and well documented deployment actions will reduce the risk, but perhaps not eliminate it. However, when you find issues, if you have an automated process, it’s easy to add tests or alter the process to ensure that it will succeed in the future. Especially if you examine the effects of these changes and learn to avoid problematic development or deployment patterns in the future.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Give Up on Natural Primary Keys

    There is plenty of debate over how to design your database. At SQLServerCentral we have a Stairway Series as well as a few articles that cover design topics. I think it’s important for anyone that builds tables to spend some time learning what others have done and understand the pros and cons of making different choices. It does often become hard to change designs once they are in use, so trying to choose a good entity design early is important.

    One of the things I think is important in modeling your particular entity is including a primary key (PK). In my DevOps talk I stress this, as I’d rather most attendees come away thinking a PK is important as their first takeaway from the session. There are exceptions, but they are rare, and I would prefer that most tables just have some PK included from the beginning.

    A PK ought to be stable as well, and there are plenty of written words about how to pick the PK for your particular problem domain. Often I have received the advice that natural keys are preferred over surrogate keys, and it is worth the effort to try and identify a suitable column (or set of columns) that will guarantee uniqueness. I think that’s good advice, and it’s also advice I tend to ignore.

    There’s an interesting article about keys and the GDPR. The first part is a rather basic description of what PKs are, but the second part talks about keys and some of the rights that data subjects have under the GDPR. I think these are worth considering, especially as it’s likely similar legislation will make its way into other jurisdictions, as already seen in California. The short part of the argument is that the right to be forgotten or to have your data deleted is incompatible with the use of natural keys.

    It’s an argument, though I’m not completely sure if I think it would be solid. There are valid reasons to keep some information about a user, and I suspect keeping a list of emails to delete from a database restore as a separate list would be a valid use. Even if the user asked that their information was removed. It would be, but there would also be a need to ensure that the correct data was removed,  hence a list of emails.

    The bigger problem for me is that if I needed to redact or alter this key data, which I would likely do in order to keep some integrity in my database, I’d need to alter this data in lots of tables. That makes for a much more complex set of scripts, including ensuring that I am correctly building a map of the new values I would use for a key. It’s much easier to have a surrogate key that doesn’t change and just redact the other information.

    I’m sure there are arguments both ways, but as we move towards the era of not only seeing data as valuable, but also as an asset we can’t completely control, I think surrogate keys make more sense now than ever. Let me know if you agree.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Ever Expanding Data Platform

    This past week was the 2018 Ignite conference from Microsoft, where we had a number of announcements about the data platform. You can rewatch some of the sessions from the event, and I might recommend the keynotes to see some of the demos and positioning of the data platform. That’s the direction that Microsoft is moving their database products, as a complete platform that not only includes SQL Server, but CosmosDB, Managed Instances, Data Lakes, and  more.

    If you’re a SQL Server DBA, it’s time to stop thinking yourself as a SQL Server DBA or developer. Instead, you need to be a data professional, especially on the Microsoft stack. While you might concentrate on SQL Server and live in SSMS, you ought to be aware of the growing options for working on the Microsoft stack. Azure Data Studio, which Grant wrote about this week, ought to be a tool you investigate. You also ought to be looking at the latest version of SSMS, which had it’s v18 move into a public preview this week. With these tools being free, companies ought to be moving away from the older versions that shipped with SQL Server 2014 and earlier. Instead you should at least be on a v17 version of SSMS. Talk to your IT group and give it a try today. It works fine with all your SQL Server versions, from 2005 through 2017.

    Microsoft is certainly hoping you’ll run more workloads in Azure, and that’s where the data platform is growing. CosmosDB, which I think has a lot of promise for various types problem domains, or even as a companion to SQL Server for certain types of data. There is an increase in their SLA to 5 9s, which is both impressive and ambitious. I know very few on-premises instances that get by with 5 9s across multiple years, leaving aside the ability to get 10ms write performance in the SLA. The is also multi master replication and support for the Cassandra API. While I haven’t done much with CosmosDB, it is on my radar to experiment with as a data store option.

    Managed Instances will be generally available on Oct 1, just a couple days away. While I wasn’t sure that this product would catch on, I’m not surprised that some companies would like to get away from managing most of the stuff around the database and stick with the data. To me, this, more than anything else, can mean that DBAs at larger companies need to be managing data, security, and more, without worrying too much about the basics of backups and HA. Even threat detection, something few of us are good at, is handled by Azure. The restore demo in the keynote is truly impressive. I’m not sure many of us would want to, or be able to, architect those speeds. At least not as easy as provisioning an Azure Managed Instance.

    There are lots of other announcements, which you can read. The one really interesting thing for me was the Data Box announcement. I’ve had more than a few people be concerned about the initial loads of data into an Azure database or data lake. I’ve had that concern, and actually been part of a company that FedEx shipped a rack of disks as part of a SAN to a DR site because of bandwidth constraints. The Data Box is a device that you can order and fill, shipping this back to Azure for loading. It comes in 40TB, 100TB, abd 1PB sizes. That is truly stunning to me. Drop ship 1PB if you have the need. You can even see a picture of it from Argenis Fernandez for some idea of size.

    It’s an exciting time to be a data professional, and Microsoft’s data platform continues to grow. I don’t know that any of us will know more than a tiny bit about most of the platform, but I certainly plan on increasing my knowledge in a few areas to become better aware of how they work and what they are capable of. I might not be able to use them well, but I can at least have enough knowledge to have a conversation about the technology and have an idea of whether it might solve a problem that I run into at work.

    Steve Jones

  • An Open Thank You to the Microsoft SQL Server Team

    Thank you, Microsoft SQL Server Development team. I really am happy with your work.

    I was going through emails and noticed the someone had posted a surprise in the SQL Server 2019 CTP. The post was slightly cryptic, but since I saw the dreaded string-or-binary-data-truncated message, I was intrigued. Digging further, with some help from fellow MVPs, I found that one of the most voted on feature requests was actually implemented. I blogged about this briefly, but in case you wonder, this is the message in SQL Server 2017 and below:

    Msg 8152, Level 16, State 14, Line 8
    String or binary data would be truncated.

    In SQL Server 2019, I get this from the same repro:

    Msg 2628, Level 16, State 1, Line 8
    String or binary data would be truncated in table 'Sandbox.dbo.Customer', column 'CustomerName'. Truncated value: 'Is this th'.

    You do need to enable trace flag 460, but this works and should help you find that problematic data. I’m really glad that Microsoft has implemented the first stages of a fix, and I do appreciate their efforts here. This is a nice step forward.

    I do hope that future versions, or even continued development on SQL Server 2019 will enhance this. Right now I just get the first occurrence, which is good, but I’d love it if we could get a result set back, or maybe an indication of more problems. I know I’m asking for more here, but there is room for improvement.

    If this is all we get for SQL Server 2019, that’s fine. I think this is going to be very helpful for those problematic imports, and at least help us find bad data more quickly in those poorly structured input strings.

    Steve Jones

    The Voice of the DBA Podcast

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