Voice of the DBA

Tag: administration

  • Consolidation Matters

    Throughout my career I’ve been looking to consolidate SQL Servers when I find them. The typical employer I’ve had usually grows their IT infrastructure over time and many projects look like this:

    • Build or buy a software package
    • Buy a new server
    • Install SQL Server and one database for the application

    Over time this means I find lots of individual servers running at much less than full capacity. That’s something that DBAs like, because it means that we can handle the inevitable spikes in resource usage that our workloads will encounter. However that’s not what the rest of the business, especially the financial management, wants. Underused resources mean money that isn’t spent well.

    As a result, I’ve often looked to consolidate instances where possible. Often I let an instance run by its own hardware for a period of months, perhaps even a year, during which I can get a good idea of what level of resources the database and application require. Once I have that, I try to match up the needs with an existing SQL Server that might be underutilized by at least that amount of resources. Typically I’m looking at RAM and CPU since disk resources can often be transferred to a new piece of hardware. It’s not quite as simple as it sounds as I also need to look at workload patterns and potentially match up instances whose workload peaks occur at different times.

    I’ve successfully consolidated many instances this way, often reducing the amount of physical hardware in data centers substantially. As hardware cycles turn over and newer machines are purchased, I can usually repeat the process again and again. The advent of virtualization has made this even easier as bad guesses can usually be reversed or corrected by moving the database to a different instance.

    I suspect that virtualization will become more important in the future, especially as licensing changes in SQL Server make it much more expensive to add the ad hoc instance on its own hardware. I’d encourage you to plan on consolidating new databases from the beginning to ensure that your organization gets the most performance out of the hardware that it has purchased.

    Steve Jones

    Podcast: http://traffic.libsyn.com/voiceofthedba/consolidation_56_v1061.mp3

  • Your First Week as a DBA

    Summary: There are many accidental DBAs as well as people new to SQL Server who are assigned the responsibility of managing a database system. Learn about the most important things for you to examine on your first week.

    Abstract: Developers, system administrators, junior DBAs, and even managers often find themselves responsible for a new SQL Server system without knowing what things they need to worry about. Since a SQL Server instance can run for months without issues, these accidental DBAs don’t realize that they are neglecting maintenance and other operations that will ensure their systems are protected and running smoothly. This session will teach you the most important things you should do on your first week with a new instance.

    Length: 60 minutes

    Slides: (coming soon)

    Code: (coming soon)

  • T-SQL Tuesday #52 – Arguments

    tsqltuesdayIt’s time for T-SQL Tuesday, this month hosted by Michael J Swart, our artistic DBA in the community. I love Michael’s pictures, and enjoy the time I get to chat with him at events.

    His invitation this month for #52 is to argue against a popular opinion. He asking us to find a "never ever" issue and argue for it. Or perhaps an "always do this" advice and argue against that. It’s a great topic.

    T-SQL Tuesday is the monthly blog party, invented by Adam Machanic. The second Tuesday of the month is the time to publish a post on your own blog about the topic and join the other people that write about the same topic.

    Never Turn on Auto Shrink

    I think that if you turn on the auto-shrink setting, Paul Randal rips an LED off his interactive panels. Even if he doesn’t, why would you ever enable auto shrink? It causes fragmentation, it’s somewhat insane as your files will just regrow if the space is needed for a workload such as a maintenance operation.

    The recommendation is to never turn on Auto Shrink on your databases. There are numerous references, though I’ll point to Brent Ozar’s nice summary. I’m going to pick a fight with this advice, though only in a narrow, limited sense.

    Auto shrink is, in general, a very bad idea. However I think there are a few places where I’d use the setting, understanding the implications.

    The Road Warrior

    There was a salesperson that had a laptop, traveling around and working his a piece of SQL Server (Express) based software. Let’s call him Joe, since that’s a nice generic name for this story.

    When Joe arrived at a client site, he’d get data from the clients in an Excel spreadsheet. He’d use the import wizard, load a bunch of data into a new table, point his software at it, and do some analysis of the data. He’d then make recommendations to the client and sell something based on his recommendations. When he was done, he’d click a button in the software and drop the table. The amount of data from clients varied, and it sometimes caused his database (and log files) to grow. He’d also forget to the drop the table sometimes and would wind up with 4-5 clients’ worth of data before he removed the tables.

    Over time, this also caused his laptop to run low on space. This wasn’t a big deal, and the help desk could respond to his concerns by remotely running a shrink of the files for him. However the time spent dealing with questions, and the concern that Joe experienced weren’t worth the effort. Auto-shrink worked fine for Joe and some of his colleagues.

    Performance wasn’t a big concern, as a single user accessed the database. The data didn’t live long enough to worry about fragmentation or maintenance and the reduction in irritation from Joe and the help desk was worth it.

    The Dusty Archives

    I once worked in a company that had an archive database. We had a process that copied data from previous years to a new database that was taken offline and a copy burned to a few optical discs for long term archival. We actually build this process to use a separate instance for the copy as the source SQL Servers were located in various parts of the US, and the data was remotely transferred to a central location for archival, and because we didn’t want to ship optical drives to multiple locations and explain to warehouse personnel how to use them.

    I, and my cohorts in IT, also didn’t really want to travel to small, inconvenient warehouses located in small cities without direct flights.

    One of the requirements was that we make these databases as small as possible to save space. We typically built a few archive databases before we shrank them and took them offline. In hindsight, the process of copying the data should have minimized the size of data and log files with its own shrink, but that wasn’t the case. An administrator needed to perform the shrink and sometimes they’d forget. Actually, most of the time they’d forget.

    The simplest solution was to enable auto shrink on this instance. That way once database files were sitting on the instance for any length of time, the databases would shrink themselves. No need for anyone to worry.

    Performance wasn’t an issue in these cases as recovery of the data would have meant a transfer back to a source system, where fragmentation and indexing would have been a concern.

    Narrow Cases

    In both cases, the reason auto shrink worked was performance wasn’t an issue, and administration was. Performance didn’t matter, and it wasn’t a heavily used system. I think these, and perhaps a few other narrowly defined cases are acceptable uses of Auto Shrink.

    However I also think there are many more abuses of the setting and I’d prefer it removed from SQL Server. Scheduling this process with Powershell would be trivial if you really needed it.

    And if you don’t know how to use Powershell (or SQLCMD), perhaps you shouldn’t be shrinking database files.

  • Zombie Data

    Most of us that work with data are concerned about losing any of the bits or bytes we are responsible for. Many of us practice restores when we can, we complain about the low disaster recovery budgets we’re allowed, and we regularly check our systems for corruption. Even those of us that aren’t extremely diligent in our daily data protection are often worried about losing our jobs if a disaster causes data loss. Many of us don’t even trust our users, preferring to implement logical deletes in applications rather than physically running a DELETE statement.

    However there are times that we do want to remove data from our systems. We may find data quality is poor and want to erase the results of an ETL load. We may find ourselves bound by regulations that require the removal of data from our systems. We may upgrade old software and end up with copies of obsolete databases whose contents have been copied and reformatted by a new version of an application.

    This article talks about the data-pocolypse, and in somewhat of a jesting way, but it has a few good points that we may want to consider when we do need to remove data permanently. We should understand that deletes are not always deletes, and if a permanent solution is needed, we should use a utility to wipe the drive or physically destroy the hardware. However there are other places we should worry about old copies of data. Backups should be deleted from remote storage that might not have cleanup jobs running anymore if the servers are decommissioned. We should be wary about taking databases offline, or detaching them without physically removing the files. Development machines, laptops, etc. should have data removed if we are sure we don’t need it again.

    We should be especially careful about security access to servers that we are not using. Users might easily have links or pointers to old servers, and mistakenly connect. Make sure you remove access when you decommission the instance. Be careful about keeping old file exports, such as reports or feeds that were generated in the past. It might not be practical to wipe backup tapes, but be sure you’ve changed documentation and surfaced the information to all administrators that copes of databases restored from tapes after xxx date are not useable in DR situations.

    Of course the first thing you should do is make sure you have a good backup of the most current version of your data before you start deleting older copies. Delete this last, after you’re absolutely sure that users are no longer going to request a restore. I wouldn’t even ask users about this, however, because they may not be sure themselves. Instead I’d set a reminder a few months in the future to go back and delete this one, last, most current backup.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.3MB) 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.