Category Archives: Monitoring
MAP Toolkit in Action
I have written in the past about the MAP Toolkit (Microsoft Assessment and Planning) and how it helps with licensing issues including core counts. With that being said, it is time for us to renew our Software Assurance maintenance agreement and this was the original reason that I installed the MAP Toolkit a couple of months ago.
This week I rescanned all of my instances to figure out how many cores we had licensed and for which version. I wanted to get all of my documentation ready to go to our contract manager when I noticed a new server that had 24 Enterprise cores that I had never heard of before. My supervisor had never heard of the box either.
When I logged into the box, sure enough it had SQL Server installed. The server was for a monitoring tool and when the administrators installed the software on the box instead of asking me where they could install the database they found a disk and installed SQL Server themselves. Unlicensed. On the same box as the software. Without telling anyone.
When the contract manager told them how much the 24 cores would cost their department they quickly called me and asked me to kindly move their 13 GB database to one of my other servers. It just happened recently because I scan the network every few months and had never seen this before. Not only did they put us in jeopardy with our licensing, they had no backups setup for the database. When confronted they said yeah that was taking up too much space on the disk.
The lesson here today boys and girls is to scan your instances and look for unknown installations regularly. Carry on and enjoy!
Red Gate Backup Restore of the Master Database (Part 8)
One of the errors I encountered with the Red Gate Backup Pro tools discussed extensively this month, was in running the DBCC CHECKDB command on a temporarily restored backup. It gave errors about the consistency which did not appear in the actual master database. The following are some interesting blog articles in response to this issue:
- Response from Red Gate.
- Response from Paul Randal and Kimberly Tripp on SQL Server Pro magazine.
Basically you will need to do the master database separately. I run an integrity check weekly against the system databases. It is a good idea to follow the recommendations from Red Gate listed in the link above. Enjoy!
Red Gate Backup Pro Continued (Part 7)
If you have been keeping score at home, I have been trying to work out the issues of implementing the Red Gate SQL Backup Pro and Virtual Restore. This series may sound like a bunch of complaints, they are not. I am just working through the issues and hoping to help others struggling and hopefully improve the product if possible. If some of these items are easily fixed, I could not find the information easily on their website otherwise I would not have posted it. Or it could be quite possibly that I am just an idiot.
I was able to resolve one issue from yesterday post, the weekly regular SQL Server copy-only backup. By tweaking the HyperBac Configuration Manager settings for the BAK extension and disabling HyperBac compression (see Figure 1) we were able to get default SQL Server backups running once a week in the midst of using SQL Server Backup Pro for our daily backups.
Figure 1 – HyperBac Configuration Manager
We also noticed a weird error in our SQL Monitor tool when using the Virtual Restore (see Figure 2). The drive is only 300 GB where we store our Virtual Restores. It looks like the Virtual Restores raise a drive space error based upon the “actual” size of the restore instead of the virtual size.
Figure 2 – Drive Space Error in SQL Monitor
Just in case you are wondering, I compiled my long list of quirks and submitted it to Red Gate support. I am confident that they will be able to help as they have been excellent in the past. Personally, I just like to figure things out before I talk with support, which many probably find as a character flaw. Maybe it is male ego or pride but I like to solve problems without help if at all possible. On the other hand, I am never afraid to ask for help, I just want to make sure I have tried everything before hand. Enjoy.
Deadlock Graphs
Last night I learned about graphical deadlock graphs from Fargham Butt of Microsoft. I had simply used the traceflag and then read the error logs to find the information. Thus today I thought I would write a blog about the subject and decided to do some research first when I found an excellent blog post by Jonathan Kehayias (B|T) covering Handling Deadlocks in SQL Server. Jonathon is a Microsoft Certified Master and does a much better job than I can do explaining the whole subject, so I thought it more prudent to just refer you to his blog. Give it a whirl and enjoy!
SQL Tool Evaluation, List of Tools
Thanks everyone for the feedback. Below are the items submitted thus far for me to evaluate, let me know within the next few days if you have any others to add to the list. Keep in mind that I am an Enterprise DBA, therefore I will not be evaluating any development tools.
- DBA xPress from Pragmatic Works
- SQL DBA Toolbelt from Redgate (we currently own)
- Ignite from Confio
- Performance Advisor for SQL Server from SQL Sentry
- SQL Server Index Analysis & Defrag from SQL Sentry
- SQL Diagnostic Manager from Idera
- SQL Admin Toolset from Idera
- Toad for SQL Server from Quest (we currently own)
- Foglight for SQL Server from Quest
- Spotlight on SQL Server from Quest (we currently own for Oracle)
- SQLCop from Less Than Dot.
Stay tuned for further details. I will have to process all of the EULAs and other paperwork in order to setup my test environment, but we have about five months left in order to complete this project. Enjoy!
SQL Evaluation Project Upcoming
I have been tasked with an exciting goal for this new fiscal year. Determine whether our existing SQL Server tools, Redgate DBA/Backup Bundle and Toad for SQL Server, are the best tools for our needs. In other words, should we continue to pay for these tools or should we purchase different tools.
Before working for this employer, I have never been able to buy additional tools so I personally do not have any experience with any other tools. Therefore, frankly, I love the Redgate tools, but I do not know how well they stack up against tools from other vendors. Any input from my #SQLFamily would be greatly appreciated. Enjoy!
Using the Right Tool
My week has been spent deep diving with the OpNet AppInternals Xpert and AppResponse console to learn some more valuable troubleshooting skills for our servers and applications that we administer including SQL Server and Oracle.
Using OpNet, I have been able to resolve three major headaches for my team this week working with their engineer. I am reminded of the simple fact that sometimes the ability to do your job well consists of having the proper tools to perform the job. With the cost of these tools sometimes you have to be able to justify the need but this one pays for itself many times over and that says a lot because it is pricey.
Beginning OpNet Monitoring
Today’s blog will be brief as I am in the middle of a weeklong process of learning the OpNet AppInternals xPert Monitoring and Transaction Trace Warehouse. I am excited to see how well this tool can interact with our SQL Servers as well as Oracle servers, dotNet application servers, and our Java based enterprise content management system.
We are in the configuration side today with the rest of the week set for troubleshooting various issues we have in our production environment. I will keep you posted as I learn this product. So far, it seems pretty powerful. Enjoy!
Outlook Rules Are Your Friend
If you are like me, you receive quite a bit of automated e-mails from all of your SQL Server jobs and maintenance plans. This can take a fair amount of time to read these e-mails daily. Until now!
Outlook rules and alerts are a great way to manage this workload. First of all, and most importantly, use a unique description in the Notify Operator Task for maintenance plan notifications, such as Figure 1. Be consistent! Use this same string in all of your backup tasks in your maintenance plans.
Figure 1
Then create a rule in Outlook (Tools -> Rules and Alerts -> New Rule) to handle these messages. Personally, I set them to move to another folder to keep them organized, look for specific words in the body (the phrase we put consistently in our notification tasks), and make sure they come from the e-mail account that I setup to alert me from all of my servers (as shown in Figure 2).
Figure 2
From here, let the fun begin as I then do the following (see Figure 3):
- Mark them as high importance
- Flag the message for follow-up today
- Move it to the specified folder (I know we did this on the last step, but it is highlighted on this step as well, silly Outlook)
- Display a specific message, Database Backup has FAILED, in the New Item Alert window to ensure that I see it as it happens (granted most jobs are at night, but it will be there waiting for me in the morning)
- Display a Desktop Alert (can you tell that this is a big deal?)
Figure 3
From here you can name the rule and finish the task. I also like to setup a rule for successful jobs that searches for a successful string that I have designated, but in that case I mark the e-mail as read and move it to the folder in case I need to search for it later.
For SQL Server Agent jobs, you have to do things just a little bit different since you cannot set a custom string for the alert message. In that case, search for “STATUS: Succeeded” (or failed, if that is the case) in the body of the e-mail and setup your other options in the same manner.
Outlook Rules are your friend, use them to be more productive. Enjoy!
Master Maintenance Plan, Part One
For the last two days, in between other tasks, I have been kicking around ideas to organize or reorganize scheduled jobs and maintenance plans into a grander scheme solution that sends me ONE e-mail a day. Sounds like a minor operation, but I have 32 SQL servers including four clusters. Currently, many of the 300 databases send me nightly e-mails to let me know whether the individual operations succeeded or failed. There are still some maintenance plans that I have not replaced yet. I have also implemented Ola Hallengren’s backup solution on many of the servers which greatly reduced the number of e-mails that I receive. The plan is to install that solution on every server. This solution has saved me hours upon hours of work, check it out if you have not already.
Sometime ago, I wrote or found a script (no, I cannot remember) that would send me an e-mail letting me know the backup type and date/time of the last backup. So here we are, and I am trying to take this to another level by exploring solutions to greatly reduce e-mail traffic and reduce the amount of time it takes me to assess my server’s overnight maintenance thus allowing me to spend more time being proactive. With all that being said, I found the following article, Use PowerShell to Report SQL Server Backup Status, written on the Hey, Scripting Guy! Blog.
As this plan develops, I will post new blog posts. Enjoy or let me know how you handle it. Let’s share ideas!
SQL Swampland 