Voice of the DBA

Category: Editorial

  • A New New Microsoft

    Microsoft is changing again. Microsoft is reorganizing and the head of Windows, Terry Myerson, is leaving. The reorganization isn’t that surprising, as like most large companies, Microsoft usually does something each year. What is interesting is that they are moving away from their major divisions focusing on products. We’ve had divisions for Windows, Servers, Devices, etc. Now we move to Experiences and Devices as one major group. The other will be Cloud + AI Platform, which seems more product oriented, but still is rather amorphous. Gaming is still separate and there’s still a large Research engineering group.

    Why are they doing this? There’s some thoughts at ZDNet and Geekwire. It seems that the cloud is becoming the most profitable part of Microsoft, and I’d expect that we will continue to see more push for cloud and subscription type software. Along with the advances in AI, Microsoft seems to be hoping that more of us will start to do work using their infrastructure. That makes sense for some, but not for others.

    For those of us using the data platform, I do think that we ought to be thinking cloud first unless we already have substantial infrastructure and automation capabilities to quickly stand up new instances. As development and other departments look to get work done, especially around data analysis, we need to meet their needs or many people will start to use the easy to provision and use cloud services, especially those for machine learning and AI. Companies are entranced by new technology, even if we aren’t, and the media hype around ML and AI will put pressure on us to do better. We don’t need to learn R or Python, but we should be able to run those scripts against data if our business demands the capability.

    The GDPR and other security initiatives might seem to slow cloud deployment, but in many cases the cloud isn’t more or less secure than our own systems. Certainly we may have issues with privacy with the new US CLOUD Act, but for most of our businesses, that won’t really matter. The conflicts there are more around illegal activities, and if you are in that space, US based companies might not be your choice for hosting data.

    For many of us, we do need to understand how to build and enforce more secure applications, and I’d argue that Azure makes this as easy any any other system. Our recent move at SQLServerCentral to AWS was essentially the same as if we’d have moved to a VM at Azure, Google, or even back to the Redgate office. The systems look the same, it’s the cost, the firewall and networking, and things beyond the data platform that are just different. And that’s the key, they’re not necessarily better or worse, but different.

    As Microsoft changes, I think their code quality improves, though certainly their rapid pace means that people getting updates too quickly are on the bleeding edge. Their DevOps deployment, however, means that the bleeding edge usually doesn’t last tool long if the issues are severe.

    Steve Jones

     

  • dbatools is Growing Up

    The dbatools.io project is growing up rapidly. I went to check something the other day and I was amazed at the command list. This used to fit on one page. Now it’s not close. Here’s the first page:

    In fact, it doesn’t fit on two pages. Here’s the part below the fold.

    It’s crazy how quickly this project has grown, and how many cmdlets are available to help you not only migrate objects and settings between instances, but perform useful operations on your systems and test or change their configuration. I’m starting to think that this needs to be required for most people that manage systems. The ways that you can easily manage your systems in a programmatic manner has dramatically changed with this module.

    I was surprised the other day when I saw a blog post for a cmdlet I didn’t know existed. I went to try it on my system and it wasn’t available. I’d updated the module the previous week, but apparently there are new changes on a regular basis. I might need to update my module more often, and there’s a cmdlet to help me do that!

    Those of us that have a lot of experience with SQL Server might find some of these tasks easier in T-SQL. I certainly spend more time inside the platform than out of it, but there are definitely advantages to using PowerShell to enforce consistency and easily work with multiple machines is nice. These utilities are often like scripts or procedures I’ve written to handle simple tasks in a wrapper-like fashion. dbatools do that from PoSh, and allow me to easily string together disparate comands. I won’t give up T-SQL anytime soon, but I’ll do more with dbatools than I might do with PoSh otherwise.

    If you haven’t tried this project, you ought to. This will make it easier to learn PoSh and work with SQL Server. Maybe you’ll enjoy it enough to built your own module and contribute to the project. There are still holes, especially in the SSIS area, though I expect new cmdlets to appear any day.

    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.

  • The Nightmare Letter

    I’m not sure if this imaginary GDPR letter is a nightmare, but I do know that in most of the organizations where I’ve worked, this type of request would result in a crash project for me. I’d be working long hours, contacting lots of people and trying to manage a complex spreadsheet of information about an individual. I’d like to think that I’d compile this information in a general sense to understand our data better and anticipate future requests, building a process that I could repeat, but I know that under pressure that might not always happen. I’m sure I’d grab some data without capturing and saving the metadata or query. I’d probably have to perform duplicate work when the next request came in.

    GDPR enforcement begins in a couple months, and organizations receiving this type of letter will have 30 days to respond. Companies can also charge a reasonable fee based on administrative costs for information requested. The fee that’s reasonable for getting a few of these letters a month might not be sufficient if hundreds or thousands of individuals start requesting this information, and I’m sure companies and authorities will be arguing about the rates.

    With the focus on privacy in the media, and the mishandling of data regularly by companies, I wouldn’t be surprised if there are going to be large numbers of requests by individuals. In fact, I’m wouldn’t be surprised if there are scripts or applications being built now to facilitate the ability for lots of individuals to ask for this information from companies about their data processing.

    Really all of this information should be documented and any decisions made about securing sensitive data should always be followed. Any organization should know how they handle data, where it’s stored, and how it’s secured. This is just practical and good administrative practice. The items about how data is processed and used are good business knowledge points. After all, should we be processing data without some justification for the resources involved? I think too often a company decides to implement some process without evaluating if it makes sense in the context of their mission. If it does, we should know why it does and be able to measure that. If it doesn’t, we ought to stop.

    If you do business in the EU or with EU citizens, you might wish to start ensuring you have a way to export the information requested in this letter. Being prepared for some of these items might make it much easier to respond to any or all of these requests.

    Whether you think this might happen to your organization or not, you might want to just save a copy of this letter. I know I will, with the idea that I might send this off to companies that store my data. Knowledge can help me protect myself by being aware of what’s being done with information related to me. If there are issues, having this information might help ensure my rights are protected. I’ll also be sure that I have a form letter to ask for removal of information. I’ve felt this wasn’t possible in the past, but at least in the EU, where I regularly travel, I can exert some control over my data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Small Changes and Big Impacts

    I’ve been doing some work with RLS lately (Row-Level Security). I think this is one of the most useful features added in SQL Server 2016, and I wish it had been available earlier in my career. I’ve had the need to secure multi-tenant databases, which is a chore. Updates and new functionality constantly brought stress on developers and DBAs that a small mistake would expose one client’s data to another.

    RLS isn’t perfect, and while it simplifies the way in which you can apply security to your tables, it does require strong T-SQL understanding and careful thought. While we often can link a user to a customer, we may decide that we also have administrators or super users that need to access all, or many pieces of data. What might seem like a small change, just adding a catch-all clause, might cause problems for performance. This article at MSDN shows the impact, which could be quite large for million (or billion) row tables.

    I  ran across a developer that was testing access and found performance to be much worse. They were querying AD with OR clauses to allow different groups access, and this ended up causing issues. There are a few ways that this could be solved, but everyone should understand that adding in functions to your code  requires more processing of data. This processing can dramatically impact your system, just like any other code, if you haven’t written efficient operations.

    That’s ultimately the litmus test for most code. Is you code written to work efficiently in your environment and with ever growing data sizes? There are certainly places where a trade off might make sense. If you have a system that will never pass many rows through the FORMAT() function, perhaps that’s acceptable. If you can’t be sure of the data size and it could be large, then you shouldn’t ever be using FORMAT().

    There are some great features in SQL Server, many of which can help you write better applications. You just need to use them appropriately and in the places where they will shine. RLS is one of these, but there can be a performance impact if you aren’t careful. Ensure that you learn to write better code, picking the patterns that work well. No matter what, also ensure that before you deploy your change, you run a full scale performance test to be sure your clients won’t be spending most of their time staring at a screen while some query runs in the background.

    Steve Jones

    The Voice of the DBA Podcast

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