Voice of the DBA

Tag: administration

  • Deploying SQL Server Automatically

    I’ve had to install SQL Server many times over the years. Often it has felt that most of these installs were one-offs, a dev server, a new QA instance, a production server for a brand new application. A few times I’ve had to recover from disaster, including restoring master, but often, I just installed SQL Server manually because that was quick and guaranteed. I knew what needed to be done for most of the instances.

    However, I couldn’t be sure. In the past there were relatively few settings that were ever changed outside of the master database, but today there are more, and perhaps more importantly, the tolerance for making mistakes with any security missteps, is low. I’ve spent a bit of time learning to do unattended installs, and I’ve tried systems like FineBuild for installing SQL Server. In any size organization that might want certain standards set up, using one of these systems is important. Perhaps one of the better reasons to do this is ensure that your dev and test systems are configured the same as production to prevent any silly mis-configuration problems.

    This week I saw a post on using Ansible to install SQL Server on Linux, with all the various configuration. While I do think that it’s easier to install SQL Server on Linux programmatically, there are a number of items to set and configure. Following along the install, it’s interesting how much more this feels like something a developer would do, which is how many modern Operations groups approach installs and upgrades. Everything needs to be done without a human directly involved, often because of scale. There are so many systems we manage, often a mix of VMs, cloud, and local systems, which mean that a consistent, programmatic way of installing instances is needed.

    That’s likely one of the most important skills for a modern system administrator. We need to learn to use tools to get our work done. Whether these are free, open-source, or purchased, using a tool to work at scale is more important today than ever before. Even if you are part of a development group, learning to manage systems with code can help ensure you can scale and grow quickly, and more importantly, hand off this job easily to someone else when you reach that point.

    I think there are plenty of homemade scripts and tools that can help here, but almost every time I’ve seen one of these, it’s not very portable to other staff, especially if the author isn’t available. Too often these tools take shortcuts or are specifically tailored to the current environment and not the future one. These days, for many functions we tackle there are extremely well built tools available at no, low, or modest costs. I’d encourage anyone that is handling these tasks to learn about the tools available and pick one. I don’t know that I think any of these are necessarily easier to use, but they are all well documented and capable of handling most of your install, update, and configuration chores.

    Steve Jones

  • Essential Operations

    It would seem obvious, but IT Operations staff are often essential to ensuring that systems and business can keep running. From websites to email to VPNs, many of the companies in which I’ve worked don’t have systems that will run without someone keeping an eye on them. Sometimes many eyes on a daily basis if we want to ensure that clients can log into applications and use them.

    The pandemic of the last year has shown many organizations that workers can get their jobs done from any location. Very few of us actually touch the physical hardware that we might manage, but we do need access to those systems across networks, in a secure way. That might not have been as big a challenge as getting the rest of a company online, but it was a lot of work.

    I don’t know how many people prioritize or consider IT operations an essential worker. This article makes a case that anyone working in IT Ops ought to be classified in the same way that health-case workers, teachers, and more. These workers were needed to keep the world moving forward, so why not recognize the importance of IT Ops along with these other types of workers.

    I do agree that Operations staff need to be treated as important pieces in an organization. Even if you use a lot of DevOps automation, GitOps, Infrastructure-as-Code, and cloud resources, you need staff to track, configure, manage, monitor, and adjust the way things work. There likely isn’t any organization that has an infrastructure they can run without any humans involved on a weekly basis. Likely something would break in that time and you need some staff.

    While you might consider the operations staff to be more replaceable and less valuable than developers or others, keep in mind that bringing someone up to speed on existing systems and ensuring they can run without service interruption isn’t as simple as just grabbing a new worker from some temp agency.

    If you use computer services from your company, remember that someone is ensuring they continue to run smoothly for you, even if you don’t realize what they are doing. Remember there is someone that is behind the scenes, often during nights and weekends, patching, upgrading, and monitoring equipment. Thank them, send some appreciation, and acknowledge their effort the next time you have the opportunity. I’ve done that job, and I know it can be thankless. I’m going to take a moment and drop a note to my own staff, who really make my life much easier.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Security Updates for SQL Server Jan 2021–CVE-2021-1636

    There was a security bulletin (CVE-2021-1636) for SQL Server, an elevation of Privilege vulnerability that could be exploited when an Extended Event session is running.

    SQL Server has released a number of patches. You can see them on the Release blog, but there are KB links below. If you have any servers that potentially can be accessed by unauthorized traffic, consider patching them.

    SQL Server 2019

    SQL Server 2017

    SQL Server 2016

    SQL Server 2014

    SQL Server 2012

    For SQL Server 2016 and earlier, make sure you are at the Service Pack levels listed. If you aren’t, you cannot patch these instances.

  • Reassess Your Configuration

    It’s the start of a new year, and while it might feel like COVID-19 has us stuck back in March 2020, I hope that will start to fade as we move into the new year. Certainly our employers want to move forward, and I suspect that there is some hope that organizations will start to thrive in 2021.

    I saw a post from Kenneth Fisher recently that talked about an end of year, new year checklist. It’s a basic set of things that you might do as a type of review and cleanup of some of the instances and databases that you manage. Checking things like end of year processes and performing some review of potential items that might no longer be needed.

    In the past, I didn’t do this type of work at the end of the year, or even that often, but when there were slow times, I did have a list of things to periodically review. We had canned queries and reports to help us evaluate resources. Many were automated to ensure the data about systems was available if I had time.

    One area that I was check once or twice a year was the configuration, trying to determine if any hardware had changed, the workload had altered, or there were proactive configuration changes we could make. This might include removing unused security logins or turning off services that weren’t being used.

    Certainly I was always cognizant of the load that managing lots of databases can have on a DBA. Every additional database is a potential customer that can call or file a ticket. I couldn’t often remove any, but I did find ways to remove unused objects and archive data at times. Reducing resource usage often pays itself back over time, especially when the need for more storage arises, which was more of a battle than it needed to be.

    I might add one thing to Ken’s list. I would ensure that you should check on cloud resources that might not be used. One thing I see too often is that cloud resources are easy to spin up and leave up, even if they become unused. Finding a few of these might earn you some points with the people paying the bills.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.