Wednesday, January 29, 2014

SQL Monitoring

I use a variety of methods for monitoring my database systems. Some are home grown. Others are 3rd party tools. Some look at more than SQL Server, reaching out to services and network and beyond. Some are very specific; monitoring a collection of file to ensure that imports are occurring on a regular basis.

One of the tools that I love is RedGate's SQL Monitor. I consider it my junior DBA. It is always on and always watching my systems. It lets me know when things are going awry.

With the custom metrics, I have been able to create and monitor some things that are near and dear to my heart, but are not generic enough that an alert already exists. For example, I have one custom metric that collects data about replication. Its not perfect, but the goal is to let me know when we are experiencing a particularly heavy replication period, as i may need to stay on my feet and be vigilant. Most times these periods simply pass without incident, but on occasion, in retrospect, something has gone terribly awry and one of the early indicators was heavier than normal replication business. So, thus the custom alert.

All this is to share with you some valuable information before i share the funny that I found. I was tweeking one of the alerts, and went back in time a day to see the data it had collected in the Analysis portion of the SQL Monitor tool. And the graphic representation of the data seemed to be mimicking the icon of the application itself.

 


This made me giggle and i had to share it with you.

Monday, January 27, 2014

what does a DBA do?



What I feel like I do      





  




What I really do





Just keeping the lights on

Wednesday, January 08, 2014

Full Backups, Log Backups and catching developers doing things

We all have our favorite ways to ensure that our databases are backed up. We may even have our favorite scripts, tools, and so on. Talking to someone that believes differently on this topic than you can be akin to discussion between religions or politics, with heated arguments seemingly making sense on both sides.

This is not what I want to discuss. I'm just going to share with you why I like what I like and why.

I love having individual backups for each job. This is more work for me, and is not automated. But I can create a single job for a single database and schedule it at an appropriate time. When this job fires off, i can ensure that it starts, performs its task, and completes, in a timely manner, without interruption from anything else. This takes a bit of doing and scheduling, but once done, I feel that my backups are the only thing going on at that moment, and can complete successfully. If an issue arises with a single backup, it can notify that it failed, and I know by the job name which database is having issues. Most likely the other databases are all fine, and not impeded by this single failure. This is harder to accomplish if you have shared jobs or maintenance plans, in my opinion.

So I spend the time creating individual jobs, determine the appropriate time, space these times out so that each can start and complete successfully. I also configure them to notify if a failure occurs. This coupled with other monitoring ensures that if a job fails, I know about it, soon. Knowing which one failed helps speed up the recovery.

So, that's how I like it for full backups. Single, measurable, simple. But for transaction logs, this is where I get lazy. I will often let all dbs fall to the same schedule (if possible) for tlog backups, and I will run a single job that cycles thru 'all' databases and performs the transaction log backup. I will schedule this appropriately on a db server, and let it fly.

On of the side effects to this method is that the global 'all' database approach to the tlog will fail if a certain database has not had its full backup performed already. A transaction log backup cannot happen until a full backup has occurred, and it will error. Since it cycles thru 'all' databases, this forces me to have taken all databases into account in the other method.

If I do my job, then all is well. For example, a new database needs to be stood up. I get the space needed for it, create it, size it, and so on. I then create a full backup job and find the time when it should execute. I then do nothing for tlogs, as they will automatically be backed up. Good to go.

But what about the time when someone else creates a database? Shouldn't they let the DBA know? Yes, they should, but sometimes, they do not. This is when the above scenario inadvertently helps me out. Developer X creates a new db, but doesn't perform any of the steps I usually do. No backup is created, no backup job is created, and so on. So, when the tlog job kicks off, it cycles thru 'all' databases, and encounters this one with no full backup, and freaks out, and fails and emails me.

This is when I know that a DB was created, but properly configured. In a way, its like the Database Server is tattling on the developers that created the DB without my knowledge. This lets me quietly go in, size the DB, configure it properly, create the appropriate maintenance jobs and processes for it, and get it going. I usually do this without fanfare, and simply get it setup correctly, and go back to my previous tasks. But now I know that this newly minted DB has had some TLC given to it, and will fit into my topology well.