Voice of the DBA

Category: Editorial

  • 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.

  • A True Lift and Shift

    Microsoft bought a data center. That’s not news, after all, they’ve been building and buying data centers for some time as they look to expand their Azure footprint, as well as continue to power their own internal systems. As they’ve grown, they’ve also learned quite a bit and regularly redesigned their data centers, moving from racks to shipping containers, from large warehouses to outdoor facilities. They are always trying to become more efficient in how they not only power and cool the systems, but also manage the deployment of servers. They don’t want to rack individual servers, nor do they even want to have systems with unnecessary wiring, choosing to order custom motherboards and other hardware. In fact, they are looking to move beyond containers to ITPACs, whatever they are.

    I’m getting off track here, since I think data centers are really cool and well designed. I used to evaluate and rent space in them, so I get intrigued. Back to my point. Microsoft bought a data center.

    Is this news? I find it interesting because in this case, Microsoft bought a data center from Chevron for a facility valued at US$80mm. That’s not a lot for a data center, but it comes on the heels of a deal between the companies to use cloud services for Chevron and make Microsoft the primary provider. It seems like Chevron wants to get out of the data center business and just rent the services it needs from Azure. Since they work in the oil and gas industry, with lots of data analysis and predictive requirements, perhaps this makes sense.

    That’s a good sales tactic from Microsoft. Use our stuff, which can save you money, and by the way, we’ll buy your old DC so you don’t have to lose that investment. An argument like that might sway quite a few large customers, allow Azure to continue to grow, and also ensure a strong base for Azure revenues for some time to come. That kind of moves makes me think I ought to buy more Microsoft stock.

    This won’t make a difference for the smaller companies, but face it, many of them likely have a limited number of servers, dozens perhaps. There are still potential cost savings for them if they can move to a managed solution in the cloud, and I know quite a few Redgate customers that are considering a variety of moves, often a combination of IaaS and PaaS. Not everyone wants to move, but there are plenty of advantages for some companies to consider cloud type solutions.

    Microsoft Azure is growing quite a bit and I continue to be impressed with the capabilities and options available. From Managed Instances to Machine Learning on demand to Cognitive Services, I think there is tremendous opportunity for companies to use technologies that might have been cost-prohibitive just a few years ago. Of course, if you’re a basic, just need a RDBMS for an app, you probably are just as happy to run (or rent) space in a smaller data center, where you have more control over resources, options, and costs.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Ad Hoc Logs

    A long time ago I started working in a team of people as a general systems administrator. I worked in a team of six people managing a large, 1000+ node network with a number of servers. In my first exposure to SQL Server, we had a very unstable application that forced us to provide 24×7 support. With a couple of contractors, we had to ensure someone was on-site all the time, often working with Microsoft and our corporate developers to stabilize the applications. We were constantly trying new techniques to fix our application, and with staff stretched thin, we struggled to understand what might have happened in the previous 12 hours when we reported for work. At the time, I suggested leaving a text document on each server’s desktop, updated with a note for each change.

    That worked well and I brought that technique with me to future positions. In another job, we constantly remotely connected to systems, and having a standard file on the desktop was helpful. As we became more security conscious, and stopped using shared logins, we moved our logging to Exchange public folders. Every action taken by an admin needed to be cut and pasted into a new post. That wasn’t a perfect system, but we built habits over time and we had an audit trail that helped us in understanding the changes we’ve made and assisted in troubleshooting.

    Today I’m curious. I want to ask the question about the data corrections, those quick changes, those fixes that get production working. Do you log everything? Is there some system in place to ensure you know what’s happening?

    I’ve been wondering about this and thinking hard as the date for GDPR enforcement approaches. One of the items that I’d glean from the law’s text is that any change to correct data, any quick fix made, needs to have an audit trail. We need to prove that we know who, when, and why this change occurred. This is especially important if a data subject requested some correction. You’ll need to prove you actually performed the action.

    I’ve never worked anywhere that some admin (including myself) completely avoided connecting to a production machine and making some change. Sometimes we’ve had great auditing, often not, but ad hoc fixes and changes, especially in the heat of an issue, are a fact of life. I’ve learned to deal with it and try to build lightweight habits to help me capture those changes.

    Let me know today. How bulletproof is your auditing?

    Steve Jones

    The Voice of the DBA Podcast

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

  • More SQL Server Updates

    This was a busy week for updates. I finished adding new builds to the build lists for 2017, 2016, and 2014, the first time I’ve done all three platforms on the same day. To be fair, two updates were on the 19th and one on the 20th, but that’s pretty close to the same day. In the past I’ve had updates for different versions during different months.

    That will not be the case in the future. SQL Server 2017 is on the CU model where we get monthly updates to the product. No more service packs for this version. If you hadn’t heard that, be ready for the new servicing model that consists of just the monthly updates. I wasn’t sure if I liked this at first, but I do appreciate that we don’t get confused with trying to determine which CU1 is needed as there were different patches for RTM, SP1, SP2, etc. Now I’ve gotten used to expecting an update each month and applying them after a few days. So far, Microsoft has mostly been sticking to monthly releases, though the December release got delayed into January. With holidays, that’s expected.

    This week also saw the update of SSMS to v 17.6. You can freely use this version and download the full install or just the update package if you are running 17.x. We are seeing updates on a fairly regular basis to the tool, every couple of months. I’m hoping we continue to see more fixes and improvements aside from new functionality as SSMS has been neglected for too long. I don’t know how stable this version is, but 17.4 was on my machines for a long time without an ill effects, and I think this seems fine for now.

    Updates have always been a part of software, but often they are fairly rare, with interruptions occurring just a few times a year. That’s changing. Redgate releases software every week, with almost every product updating once or twice a month. For someone that uses relatively few pieces of software, all of a sudden it seems like I have a regular set of tools to update across my SQL Server development platform between SQL Server, SSMS, VS, Redgate tools and more.

    That’s good, as I get fixes and increased functionality, but across a set of tools, I might find myself updating something every day. While the software often updates itself if I click OK, I have to make a decision and the process is disruptive and time consuming, especially when I run into required updates, which happens more than I’d like. I like the idea of DevOps, but one of the unintended consequences is that if everyone does it, there are a lot of changes and updates for users to deal with.

    I don’t have a solution, but I do find myself delaying updates more and more, often moving to only take every 3 or 4 updates. Maybe if everyone had an easy way to apply patches with PoSh across systems, or smoother built-in mechanisms I’d feel differently, but for now I probably click “skip” more often than “apply”.

    Steve Jones

    The Voice of the DBA Podcast

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