Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #66 – Monitoring SQL Server

    tsqltuesdayThis is a T-SQL Tuesday that not only makes me smile, but makes my boss happy. In fact, I’m sure that quite a few people at Redgate Software, my employer, are thrilled with this topic since we have a product that helps you here: SQL Monitor.

    T-SQL Tuesday #66 has the theme of monitoring, hosted by Catherine Wilhelmsen. Anything having to do with monitoring is fair game. Custom scripts, home grown applications, even third party software.

    If you’re a #SQLNewBlogger, this is a great chance to keep going with a new post. If you can’t write now, come back and publish something later. If you post something on May 12, 2015, you’ll get included in Catherine’s roundup. However if you can’t make it, you’re still a T-SQL Tuesday writer.

    Either way, follow #tsql2sday and learn a few things.

    Self Monitoring

    My post is going to be more of a philosophical one, with a little code.

    Most of the time I’ve worked with SQL Server, I’d had some responsibility for a production instance. Even when I was writing code and building software, I’d still find myself as the backup, if not the primary, accidental DBA. I learned early on that I needed to have some way to track what was happening on an instance.

    One of the ways in which I did this was by ensuring each instance could monitor itself. I’d found far too many times that having a process connect remotely to the instance and gather data was a way to lose data. Too often something would get missed. The exact time that a process connected was the time things didn’t work.

    As a result, I decided to keep some monitoring on the local instance. Even though I usually had a central server roll up information, if that instance dropped off the network (or my monitoring server died), I’d still know what was going on. This was especially important when I worked in the financial industry or was bound by ISO 9000 standards.

    Note: These days I don’t bother to manage monitoring software of my own on an instance. I use SQL Monitor because I work for Red Gate, but no matter who my employer might be, I’d buy software because it’s worth the cost. I don’t have the time to worry about maintaining my own software for most monitoring tasks.

    DBAAdmin

    I had a setup routine that I used to use on every instance. It did a few things, the first of which was create a DBAAdmin database. This was the place I could put anything I needed to monitor my instance. I tried to keep all the code the same between instances, and tried to write idempotent code so that re-running the install wouldn’t cause issues.

    Once I had the database, I’d add tables and procedures for various purposes. For example, one of the things that needed to be tracked each day was the backups for the databases. So I created a table called DBBackups.

    CREATE TABLE DBBackups
( BackupID INT IDENTITY(1,1)
, Backupdate DATETIME
, DatabaseName VARCHAR(2000)
, BackupType CHAR(1)
, Filename VARCHAR(2000)
, sizeGB NUMERIC(15,2)
)
;

    This table had a corresponding procedure that was designed to scan the filesystem and report back on the latest backups written to the file system. The details of each file are recorded here.

    Why? If there’s a problem with monitoring, the local machine still has the information about backups. What’s more, I can usually get more details here than I’m capturing in a monitoring system that’s looking at the last backup date or just getting full backups. If msdb history is wiped, I have a second copy here.

    However I also need to remove data over time, so this solution usually has three parts.

    • Table for data storage
    • Procedure(s) for gathering data
    • Procedure and job to remove data older than xx days

    That’s simple, but it’s a decent amount of work. However I only use this for certain areas. I used to really care about backups, but SQL Monitor captures that for me. However I might care about sp_configure settings. While SQL Monitor can alert me if there are changes, how do I know what changed? I’ve used a process like this to keep the last week’s worth of sp_configure information, captured every day to let me look back.

    As I noted above, I wouldn’t rebuild a monitoring solution for overall checking of a database, but I might capture specific information using this process, with the table and procedures tailored to the specific information I care about.

  • Opening Up Data

    Tim O’Reilly has been an advocate of open data access and standards for some time, especially from governments. He’s pushed for more interoperability and certainly more accessability from all sorts of groups. He gave an interview earlier this year to LinuxVoice where he talked about a variety of things, but data was foremost on his mind.

    There are some good thoughts, but I was pleased to see him looking for more software to adapt how it works with data rather than asking data to match the application. An interesting thought he had was in the area of control systems. Does every device or sensor need a separate application and way of interacting or should we have some guiding design principles that let similar applications work in similar ways with different data? That almost sounds like good data modeling and normalization principles in action, backing a data driven application.

    I also liked his acknowledgment of the fact that so much of our data isn’t very portable. Between social networks and proprietary storage, it becomes hard to move data around. The pattern of downloading data, perhaps editing, perhaps not, and then uploading elsewhere works great with ETL tools, but it’s cumbersome for many users and applications to deal with. Building ways for us to interact with disparate data, allowing for queries to remote sources, sometimes transforming and copying data, all of this needs to be easier to implement and integrate inside software.

    In some ways, I think the 3.0 model of our Internet interaction will take place around data. I think SSIS will continue to be one of the most valuable tools in SQL Server (along with lots of demand for work), but it still needs improvement and enhancement to catch up to other ETL tools. I really hope Microsoft believes this and continues to invest in the tool.

    I also think that the data professionals that really stand out in the next decade will be those that learn to make the choices about when to use R, JSON, XML, HADOOP, or whatever non-RDBMS tool to meet a need. But also when not to use these tools. The better data professionals will make good decisions about when to query data, and when to move it to another system.

    It’s an exciting time to work with data as the opportunities and rewards continue to expand and grow. I look forward to what the future will bring us.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Service Pack Fiasco

    I remember getting a Service Pack years ago that caused a blue screen of death on Windows 2000 servers when it was installed. In fact, a quick search shows that quite a few operating systems (and SQL Server 2005) have had issues with SP2 over the years. Perhaps we shouldn’t be waiting for the first service pack to upgrade, but upgrading and then skipping SP2 for various products.

    Microsoft has responded fairly well to problems with service packs by removing them quickly from their download sites and re-releasing them. This occurred recently for SQL Server 2014 SP1, where an issue was discovered with the SSIS Catalog. This is disconcerting to me, not because of the bugs, but because these mistakes and problems seem to lead Microsoft in the direction of abandoning Service Packs. The idea floated in the past, and still pushed by many people, is that the Cumulative Updates are good enough for patching SQL Server.

    That seems crazy to me. If we have problems with a service pack, wouldn’t we still have problems with a CU? Or is Microsoft hoping that the smaller group of people that are impacted with CU issues are good beta testers and limit the impact of issues to the community? I’m assuming the testing is similar for all patches, if not the same. However as we all know, testing can’t cover every possible scenario. Microsoft notes this as their latest documentation includes these quotes:

    I really prefer that a service pack is released every year for each product. It can contain all the CUs up to that point, with no additional patches, thought hopefully a bit more testing. It should be a line in the sand that helps administrators manage their systems to keep up with patching and reduce support requirements. I’d actually even suggest Microsoft require vendors that want to certify their products on SQL Server and use any logo, validate their software on an SP within four months of release. In most cases, there isn’t any work for vendors, merely the effort to re-run all their tests.

    We pay a large licensing cost for SQL Server, and the lifecycle support policy under which many of us purchased our software notes that we’ll get ten years of support and the term “service pack” is embedded throughout the SQL Server support pages. We should receive a service pack every year to provide continuing support for our platforms.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A Week to SQL Saturday #393 – Redmond

    Only my second SQL Saturday of the year (wow), but SQL Saturday #393 is next week in Redmond, WA. This is my first time at a SQL Saturday in Washington, though I’ve been to the area many times. I’m looking forward to the trip up with a couple days in the Seattle area and getting the chance to catch up with some friends.

    I’ll be talking tSQLt and testing at the event, and the full schedule has a lot of great sessions to choose from. If you’re in the area on May 16, 2015, I’d urge you to register and come by for the day.

    If you can’t make it, hopefully I’ll see you at another event soon.