Voice of the DBA

Tag: administration

  • Management at Scale

    Would you like to manage 20,000 databases by yourself? What about 20,000 instances? It’s not quite the same thing, but Facebook recently announced that each of their data center operations staff manages 20,000 servers. That’s an impressive number, and it comes about because of lots of standardization, specialized design, and lots of automation.

    If you watch the video, you’ll see that a lot of effort in the Facebook data centers is being paid to gathering and analyzing information. They seem to truly understand that having data about not only their systems, but their processes, is valuable. Maybe more importantly, they understand how to modify the way they work based on data to improve their efficiencies.

    We should do the same thing as DBAs. Perhaps even as developers. We should be monitoring our workflow and looking for ways to improve our effectiveness. We should take advantage of the tools that let us manage systems at larger scales. PBM, third party tools for monitoring and alerting, BIML or SSIS patterns and practices, and more. There are a variety of ways in which we can work more efficiently.

    I thought the best quote in the article was this one: The emphasis on automation is not because Facebook is interested in unmanned data centers, or having robots operate facilities. It’s because Facebook values its workers”. The next quote is “We want to hang onto our talent,” she said. “The way you do that is to give them the opportunity to work on high-value tasks.”

    Those are powerful quotes, and I wish that was how more businesses viewed their workers. To be fair, most workers haven’t proven themselves to be willing to tackle and want the “high value tasks”. All too often I find there are too many workers that want to get their job done, without providing more value than they cost. If they did, perhaps we’d have many more companies that would value their employees, and they could manage systems at something closer to Facebook’s scale rather than the much lower numbers I’ve seen in my career.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • Common Checks

    One of the things that I recommend to DBAs is that as they write code to solve problems, they write some sort of check to catch future potential occurrences of the issue. For example, I worked with a product years ago that had an identity column as an int, but this product scanned thousands of our systems every 5 minutes, logging each scan. We found that after 8-12 months, we’d run out of identity ranges.

    While we considered changing to a bigint, this was a third party product and they did not want to allow us to alter the schema. Since there were only a few million rows in the table at any one time, we decided to write a check that would alert us when the table came close to running out of integer values. A DBA could then easily reseed the identity property to prevent issues.

    Across my career I’ve found many other instances where we could write checks to find issues before users were impacted. At SQLServerCentral we have checks looking for discrepancies in points awarded, in incorrect status values, and more. These often don’t impact the users of the site, but they are alerts that let administrators know about potential problems before they become issues.

    This week I’m wondering:

    What types of common issues have you found that could be valuable as checks for other DBAs?

    Perhaps looking for delays in replication or Service Broker? Jobs that never stop or never execute for some reason? Memory or space alerts? Do you look for localsystem as a service account? Let us know what checks you’ve written, or which ones you wish were available for you to download and use on your systems.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • A Full Backup Includes Everything (with a caveat)

    Full database backups in SQL Server include all of the data, objects, tables, rows, functions, stored procedures, etc. If something is in the database when the data reading portion of the backup concludes, it’s in there.

    Note that “in there” means committed in a transaction.

    If someone tells you the backup missed a row, or a procedure, or something else, they’re almost always wrong. 99.9999% of the time they are wrong, and you should stand by that.

    The issue is that things must be committed in the backup. If they aren’t committed, they aren’t included. And that means that there’s a small chance that something added to the database while the backup is running isn’t in the backup.

    If you remember how backups work, there’s a data reading portion of the backup and a log writing portion of the backup. The log writing portion of the backup takes a physical amount of time. If someone were to finish a transaction during this time, the data or objects would not be in a restored database. This is because the transaction didn’t exist or wasn’t committed when the data reading portion of the backup completed.

    If the data writing portion of the backup takes a few minutes, and a change was in the last minute or so of the process, someone might think something completed before the timestamp on the backup file is included. It wouldn’t be.

    It’s a small chance, and it’s not likely to come down to this point, but it could happen. Can you figure out the transactionally consistent time of the backup? Perhaps, but I don’t know how. You’d need to get the time for the last LSN written in the backup and map that to a time. If you know how to do that, let me know.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

  • The Future of Auditing

    I was reading Captain’s Share the other day and enjoying a quiet afternoon at home. It’s a science fiction book about one man’s journey in the future as a captain of a space freighter. It’s an interesting series from Nathan Lowell that I’ve enjoyed and recommended to other science fiction fans. In the book, there’s a scene where the main character is leaving his old ship as first mate and moving to a new ship as the captain. However he notes that the formal process is to deactivate his records on the old ship and ensuring they will be read only forever. The book notes they can’t be deleted because they are a part of the ship’s records, log entries, etc.

    That seems to be a far cry from the way auditing takes place in current computer systems. Auditing of systems is under the control of the sysadmins (who are sometimes hackers) and can be altered, changed, etc. We, as software designers, haven’t done a good job of ensuring the integrity and longevity of log records. In some sense, it seems to be a fundamental flaw in OS and software design to not have separated out the auditing and recording of actions from the administration and rights of the rest of the system.

    I’d hope that we would recognize that auditing actions and preserving this data is something that ought to be tightly linked to, but separate from, the rest of system operation. I’d like to think that fundamental changes and actions taken on the system should be written separately to an area that is easily marked as readable by non-sysadmins that are designated to review the information. I know we have the challenges of managing the space and the problems of spurious actions being generated to fill (or rollover) logs, but I’d think after 50+ years of computing we would have considered some sort of event log that isn’t under the control of the people whose actions it is recording.

    SQL Server has improved its auditing features and capabilities, but far too much is still linked invariably to the sysadmin, often the same person the auditing should be watching. This is certainly one area that I hope matures in future versions as the need grows to track and review actions taken by privileged accounts.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 19.5MB) feed

    MP4 iPod Video ( 23.7MB) feed

    MP3 Audio ( 4.6MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center