Voice of the DBA

Tag: administration

  • Provisioning

    This editorial was originally published on May 24, 2013. It is being re-run as Steve is on holiday.

    At a talk recently, the presenter talked about the time lag for an on-premises server to be installed and configured to be on the order of months. Not that many, but it could easily be six months.

    That seems crazy, but in many companies I’ve worked in, that wasn’t an unusual time frame. From the time someone decided that a purchase was allowed, it could be days for the accounting department to approve the funds. A day or two to place an order, assuming that IT had already provided the specifications. Vendors respond quickly, but it could still take a week or two, possibly even more, for a server to arrive at our company.

    One of the advantages of cloud computing services is that new virtual machines can be bought and enabled in minutes. That might be true, but I wanted to ask many of you if you know how longs it actually takes for you to begin working on a new SQL Server.

    How long does it take to provision a new server?

    Imagine that you were to ask for a database server today, and you management agreed to grant the approval, think about how long would it be before you could sign into a SQL Server. I’m sure some of you have virtual infrastructures available, and that should reduce the time it takes, but let us know if that’s your setup and how long it takes.

    The ultimate service would be a cloud like application inside your organization that allowed you to select a SQL Server template, and send you a server name and login with minutes after an automated build took place. I don’t know many companies that have that yet, but I think many of us would appreciate that capability. Especially if we could choose the hardware we required.

    Steve Jones

     

  • Monitor All the Changes

    Can you monitor every configuration change and setting on all your SQL Server instances? Can you get an alert every time code changes, or even if an option for an object changes, such as the changing of an EXECUTE AS or the rebuild options for an index? Do you want to know about every security change (new logins, grants, revokes, etc.). Can you keep up with every alteration of a SQL Agent job?

    You certainly can, but across any busy enterprise, all these alerts might result in a constant stream of items to review each day. In fact, in some companies, the volume might be high enough that this becomes a full-time job for someone. I’m not sure many of us think that it’s worth an employee’s time to actually review every change.

    Even if you decided it’s worth reviewing every change, is it possible to do a good job actually doing so? Too many alerts usually result in an individual starting to treat all of them as though they are the same priority. It becomes hard to differentiate what’s important to review and what’s not over time if you are always looking at a stream of changes across disparate systems. This is one reason why I never want to get success message, but even failure or change messages can be problematic when the volume is high.

    What can you do in a situation like this? Certainly there are alerts that are critical and need to be addressed right away, aren’t there? There are, but they are probably few. Making the decision about which items are important enough to review daily can be hard. I’ve typically only wanted critical alerts for backup tasks (after some retry) and privileged security alterations (add/change/remove sysadmin/securityadmin/processadmin/serveradmin). Those are items I need to take action on. Most other items, such as failed index rebuilds, job schedule changes, configuration alterations, I just want to capture and log.

    In one of the SQLskills newsletters recently, Paul wrote about an issue where replication settings had changed. The distributor had gone from 72 hours to 72 days, resulting in memory pressure for the workload. While I know this is a problem, is this the type of alert you’d define as critical? I’m not sure I would. In fact, this is the type of alert I’d want logged as a set of changes on this system so that I could review it when it seems that the system is not running as smoothly as it otherwise might be. In fact, this is the type of problem I’d hope I’d catch through performance monitoring, which might pro-actively catch performance degradation that would lead a DBA to review changes and metrics before a user reported the situation.

    Ultimately I want monitoring systems to help me find issues, and only notify me when I might need to take some action. To do this, the system might need to capture everything, but I want most of the items filtered until such time as the information might actually help me solve an issue. This takes some time to setup and tune as you discover holes in your monitoring, or you find that too much data is being passed on. Don’t be more afraid to add more data to capture, but be ruthless about removing extraneous notifications from the system. This is a case when too much information sent to the administrator is as bad as too little.

    Steve Jones

  • A New Recovery Tool for Your Toolbelt

    Those of us that administer databases and are responsible for ensuring the integrity and availability of data know that being able to restore systems is a core skill. In the SQL Server world, this should mean more than just being able to run a RESTORE DATABASE Sales FROM DISK = ‘Sales.bak’. Many of us would easily recognize that we should be able to use the WITH MOVE option. We should also understand when to use (and not use) NORECOVERY and STANDBY. Perhaps more importantly, we need to know the default option is RECOVERY.

    If we delve further, perhaps we could debate and discuss when a tail log backup is needed. Many might mention how to programmatically use FILELISTONLY and HEADERONLY to generate scripts.  A few of you would note that restoring databases means restoring logs as well and an understanding of the STOPAT option is important. Perhaps learning the ins and outs of restoring a filegroup is important in your environment. There are many nuances and options with restoring a database that a system administrator could learn.

    Apart from performing a restore, a careful DBA might think that we should try to avoid restoring databases if possible. Perhaps a DR system using Log Shipping can be used to recover data quicker than a database restore. Maybe periodic snapshots are valuable in recovering from those “whoops” mistakes. As our data sizes grow larger, we may need to develop more creative ways of recovering from user mistakes.

    Recently I saw the SQLCAT team had written a post on using Temporal Tables to recover from an “oops” mistake. If you haven’t looked at these structures, they are new in SQL Server 2016. Temporal tables, or system versioned tables, allow a user to view a row as of a certain timestamp, which can be especially useful for DML mistakes. Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files.

    Certainly the majority of us don’t yet have temporal tables, and we might not even set up the option on many tables even if we were running SQL Server 2016. Over time, however, some of us will use these system-versioned objects, and perhaps we will run into a situation where recovery using temporal queries is more efficient than restoring an entire database. If that were to happen, wouldn’t you want to have some skills in this area?

    As SQL Server continues to evolve, many of the techniques and skills we’ve built across time will continue to work. There will be new ways of accomplishing work, based on features and enhancements that we ought to consider, especially when they might be more efficient than our past techniques. As you work with SQL Server, I hope you keep learning about the “new” ways that you might accomplish the tasks for which you’ve developed solutions in the past. Sometimes the old way might prove to be a better choice, but you won’t know that unless you learn, practice, and measure the effects of a new method.

    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.

  • What’s Your Backup Speed?

    I ran across a thread recently where someone was looking to estimate the time it too to perform a 600GB backup. I’ve seen other threads with similar questions. The general advice is that you need to run a backup and see how long it takes. After all, your hardware, your workload, the compression settings, and more will affect your backup.

    In researching speeds, I didn’t find much info out there, so I thought I’d start a poll. This might be fun for you, so I’m hoping quite a few of you will answer.

    How long does it take you to back up 1TB?

    I know some of you don’t have a 1TB database, and probably a few of you have much larger ones. However, look at your backup and extrapolate (or interpolate) the time it would take to complete a 1TB backup. Assume that your hardware would scale appropriately to allow you to run a 1TB backup if you actually had that much data.

    If you can share your setup, or your configuration, that would be very interesting. I know some of you use striped backups, some compress, some use third party tools to help, and maybe you even have another way to actually get data from a live system into a backup file.

    Over the years I’ve seen some amazing improvements in the backup and restore functionality that SQL Server provides. I think it’s one of the most solid subsystems in SQL Server, and I’m looking forward to seeing just how fast some of you can create backup files.

    Steve Jones

    The Voice of the DBA Podcast

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