Voice of the DBA

Category: Editorial

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

  • Interviews: Covering index

    Performance seems to be a part of every DBAs job. It should be a part of every developer’s job, but I understand the focus to build software is different and many developers aren’t sure how much a feature or option they work on will be used. They don’t think too much about performance because at small scales, perhaps performance doesn’t matter a lot these days with multiple cores and many GB of RAM. I’d disagree with that, but I understand the perspective.

    What’s interesting to me is that in many interviews I’ve had over the years, I’ve not often been quizzed on specific scenarios that might help improve performance for queries. It seems many interviewers like to ask trivia questions such as the difference between a clustered and non-clustered index or what configuration knobs should I change. Code questions might be how to find duplicates in a table or deal with NULLs in aggregates.

    I hope that’s changing and more interviewers are giving candidates scenarios, and looking for ways to ensure that candidates think about performance. I hope someone asks how a candidate looks at an execution plan or structures code to remove RBAR concerns. I wondered about this since I chatted with someone recently that had been asked about covering indexes in an interview and was struggling to understand how a covering index might be different than a clustered, nonclustered, filtered, or other type of index.

    This might seem like a trick question, and perhaps it is for some interviewers. After all, if you don’t know, you might correctly guess what this means, but will you know this isn’t a special type of index? Perhaps you’ll try to bluff your way through the answer and hope you get close.

    Answering interview questions should be a discussion, as your answer can reveal as much about how you work as it can about your knowledge. Do you probe to understand the boundaries of the question or confirm that you know what’s being asked? Do you admit when you don’t know something or perhaps reason out loud? Do you give details that make sense, or do you launch into a lecture?

    In this case, you should understand that this index covers a query. This isn’t a type of index, but rather a characteristic of an index against a particular query (or set of queries). To learn more, you might want to read a bit about adding included columns or how to evaluate your choice. If you don’t know much about indexing, get started with an introduction or go through our Stairway to Indexing.

    Future versions of SQL Server will include automatic index tuning, but I think we’ll always need to balance the number of indexes we have. We’ll also often want to build some indexes that will ensure the system works fairly well when it’s first installed. That means data professionals (DBAs ans developers) need to understand how indexes work and ensure they can choose a good index most of the time.

    Steve Jones

    The Voice of the DBA Podcast

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