Voice of the DBA

Tag: administration

  • DBA Support

    There was a time when I managed two production databases on SQL Server. Two. I had a development version of one database where we paused development for testing, and only two production databases to manage. Since I had to also handle development, application support and hardware repair/replacement, that seemed like plenty to me. I was the accidental DBA, with database administration being the lowest priority of my day.

    After that I moved on to administer databases in a number of jobs, sometimes as a priority, sometimes not, but in each case, I learned to work more efficiently and effectively. My goal was to automate as much as possible of the routine work so that I could spend my days adding value to the company. I learned to use scripts, alerts, jobs, and more to keep systems running while I was doing other work.

    I’m sure many of you work in a similar manner, or at least I hope you do. This Friday I wanted to ask you at what scale do you need to become efficient, based on the size of your organization. The question this week is:

    How many databases does each DBA in your organization manage?

    I know some of you manage lots of databases in raw numbers, but also let us know if you need to do much with these databases. Is maintenance automated, or is there much active management you need to do in order to ensure these databases are running on a weekly basis. Let us know the size of your load as well, perhaps the amount of data is a better way of measuring the DBA load.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Two Steps Ahead

     

    think ahead
    Are you thinking ahead? Using the data from your systems to be proactive?

    Exceptional DBAs do more than respond to events and issues in their environments. In many cases, I think they even go beyond using metrics that detect problematic activity on their systems before users notify them. I think the best DBAs will actually mine the information they have about their systems to anticipate problems in advance.

    In the past I’ve had monitoring systems that would respond to issues, and I had alerts setup on the system to notify of unusual events, like an unexpected data growth. What I had started to do before I became a manager was start to write system checks that anticipated future problems and allowed me as much lead time as possible to prepare for issues. An example of this was a set of queries I wrote that calculated data growth for all databases on an instance and then used that to calculate how many days would elapse before I ran out of space on the data drives.

    You can write similar queries to look for other trends. Tracking the execution times of often-run queries, or those queries which are important to the application can allow a DBA to find potential issues. If the execution times are growing, the DBA can anticipate a problem occurring in the near future and begin taking action to rewrite, tune, change indexing, or some other measure. A broad spectrum of queries taking longer might be an indication that hardware needs to be upgraded. There’s even a site devoted to metrics.

    Instrumentation is important in understanding, analyzing, and predicting system performance. More and more tools are being released to gather detailed metrics on .NET code, in browsers, and more, but there is a wealth of information inside SQL Server on the performance of the platform. A little work can help you track and monitor the performance of your system and proactively maintain performance before your users complain.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Important Alerts for SQL Server

    One of the things that I think is extremely important for DBAs and really anyone that has to administer a SQL Server instance is a set of alerts on various items that might occur.

    I ran across Glenn Berry’s article on Provisioning SQL Server and there’s a section in there that is titled “Setting Up SQL Server Agent Alerts”. It assumes you have Agent running (and you should), and includes a series of alerts that you can set to inform you when certain things occur.

    Glenn has alerts for severity errors 19-25, which are important, and then includes an alert for Error 825.  This error is indicative of possible corruption and at least some I/O issue with your storage. It’s important to have this error trapped, but in addition, I’d also recommend setting alerts for 823 and 824 errors.

    I would recommend modifying Glenn’s code to include this T-SQL as well.

    -- Error 823: Read Write Request Failure
DECLARE @Error823AlertName SYSNAME = N'I/O Alert - Error 823: Read or Write request failure';

EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName,
              @message_id=823,
              @Severity=0,
              @enabled=1,
              @delay_between_responses=900,
              @include_event_description_in=1,
              @category_name=N'[Uncategorized]',
              @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO
-- Error 824: Read Write Request Failure
DECLARE @Error824AlertName SYSNAME = N'I/O Alert - Error 824: Logical Consistency I/O Error';

EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName,
              @message_id=824,
              @Severity=0,
              @enabled=1,
              @delay_between_responses=900,
              @include_event_description_in=1,
              @category_name=N'[Uncategorized]',
              @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO

  • Manage By Delegation

    powershell
    Powershell might be a great skill if you need to manage lots of instances.

    More and more SQL Server instances are being deployed all the time. In fact, with the ease with which we can build a new virtual machine (VM) through snapshotting and cloning, it seems that many administrators are finding that the number of servers for which they are responsible might be doubling or tripling.

    Even moving to the cloud doesn’t completely remove the need for some administration of your data and databases, though it does require you to rework the type of administration that you perform. I foresee more hybrid solutions over time, which will require DBAs to not only manage data, but help analyze the financial impacts of moving data (and analysis) to, or back from, the cloud.

    In SQL Server 2008 we had the chance to begin managing our servers through a set of declared rules with  Policy Based Management (PBM). I haven’t seen that feature take off, and it seems relatively few people are using PBM to manage their servers. I think it’s a great platform for ensuring that your instances are conforming to certain rules, though I think there is a bit of creativity needed to ensure that this system works well for you.

    Powershell is becoming integrated into all Microsoft products. Virtually everything in SQL Server, perhaps even every thing by now, can be managed through Powershell scripts that access the SMO objects. I hear various people say that Powershell is a critical skill for DBAs of the future. I’m not sure of that, but I do think it will be used more and more if you have the need to perform repeated actions on multiple servers. Whether you use it now or not, it doesn’t hurt to learn how it works and what it can do for you.

    It just might be the tool to make your job easier as you get more and more instances to manage, something that seems to happen more and more.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.