upgrade to downgrade and pull some hair out while you troubleshoot

I have a Monitoring system that houses RedGate SQL Monitor, as well as home grown monitoring solutions. It is called VOO1DBMGR1. When this system was stood up, a version of SQL Server 2012 was used that shouldn't have been. It was an Enterprise Eval version. It should have been the licensed Dev version we bought for this purpose, but without going back in time, its tough to change. 

But change it needed. I needed to upgrade it, or downgrade it, as it were. We wanted to go from Ent to Dev on sql server 2012. A testing VM was stood up to help with the process.

After several tests on testing VM that was configured for me to play with, I have installed and uninstalled and upgraded and removed SQL Server 2012 a bunch of times. After reading several blogs and articles, I found a path to upgrade (or downgrade in this case). Once the eval time expired, things stopped working like SQL Server Management Studio stopped working on box. It would error upon launch. One could continue to connect to it remotely via SSMS, but not locally. Also, when one shut of SQL Services on box, one was unable to start them again without setting the date back into the distant past and resetting the date once services started up again. This proved to be a problem.  

I discovered that once SQL Server 2012 was installed with an evaluation version, I could do a version upgrade with a properly keyed installation. We downloaded an iso from MSDN which embedded our key in it, and I was able to use this version to upgrade (downgrade actually) the version. Since it was already an Enterprise version, and we wanted Developer, it was a downgrade, but the same process could be used. So you select upgrade, it steps you thru several other steps of information before performing its operations. When done, the version has been changed. I even ran some powershell script the last time on the test system to determine that the service actually went down a couple times for a few seconds while it was upgrading.

So, it was time to perform this on VOO1DBMGR1.

I copied the same MSDN iso we used in the testing environment, and ran it. Since the key is embedded, when it comes to that screen, it’s there already, and not in ‘evaluation mode’ like the previous install was. I had the powershell script running elsewhere so I could watch the service status. As the upgrade proceeded, I saw that the service went down via the powershell script. But it wasn't for a few seconds. It simply kept reporting that it was down. When I looked at the upgrade process screen, it was simply working. Not locked. Not ‘Not Responding’. Just doing. Going. Working. But no real response from it.

I waited.

Then I looked at the windows application logs, and saw that an attempt to start the SQL Service had been attempted, and failed. The reason? ‘SQL Server evaluation period has expired.’ But that should have been taken care of with the upgrade, no? I believe so. That’s what happened in the test environment. But not here. 3 attempts so far. I looked into the ErrorLog of SQL Server itself, and it had a typical start-up sequence, but then the same error about the evaluation period expiring.

Hum.

In the past, when the service didn't start, I simply set it back to 3/1/2012. And viola, it would start up services, and then I was free to reset the date. It made me feel cheap, but I did it anyway. It’s for the greater good. So, while the upgrade process was obviously stuck in a loop, I gritted my teeth and changed the date.

I was prompted with some odd screen that said it couldn't complete an operation and would I like to retry. I said yes. It asked again. I said retry. It asked yet again, and in a fit of madness, I answered the same way. The next time it asked, I canceled the screen. At which time I was prompted with the upgrade utility, and all greens across the board, and it letting me know that it had successfully completed its activity. I beg to differ, but defer to its better judgment.

I looked for my SQL Service, and it was up and running. I logged in with SSMS (an act I was prohibited from doing for oh too long on this machine) and was successful. Once in SSMS, I was able to detect that the upgrade had actually performed its operation and the version was as expected.

I set the date back to today, to shake off those cobwebs of uncertainty. I  then turned off the SQL Service, without the date being set back to 2012. And found that the services were able to turn on and off at will now, without getting dirty.

All seemed well.

Then I tried to test something from my machine, and I encountered a slew of errors. Were these related? At first, would think so. But after some breathing exercises and several tests, along with a reboot of my machine, all was well again.

All is well.

Short version.

VOO1DBMGR1, our trusty monitoring server is now on a fully licensed and has a proper version. SSMS is once again able to run on box. Remote connections are functioning as expected. Monitoring systems are up and running. And SQL Services act well now, which will allow them to be shut down for random maintenance.

Indexes and Powershell

I ran across a blog before the Thanksgiving Holiday that caught my eye. Its something Ive been intending to get into recently, but just didn't find the time. So on the slow day of Wednesday before the Thanksgiving holiday I took the time to dig into it and play with it in my system. It was a blog by SQLFool (Michelle Ufford | blog | twitter ) on how she deals with indexes. She basically has a proc that shows her Missing Indexes. She also uses Kimberly Tripp's (blog | twitter) sp_HelpIndex2 proc to dig into the actual table that says its missing indexes. The results of these 2 sets of information are manually processed and decisions are made about indexes.

Simple enough. You need the knowledge of how your systems is being accessed, coupled with the information these two procs provide and a little time, and you too can make informed decisions about your indexing needs.

I then coupled this information with a script from Pinal Dave (blog | twitter) that looks at unused indexes. I tweeked both these scripts from Pinal and Michelle to match the results I was looking for. Small tweeks, mind you. The result were 2 queries I could use to look into a particular database, see in a glance all missing and unused indexes. Using Kimberly's proc to review the table for existing indexes and combining this with the knowledge I had of our systems, I made choices and either added or removed indexes at will. I felt powerful. The power of the DBA coursing thru my veins...

< side note >

I want to take this chance to thank these folks for their work, their sharing and their knowledge. It's nice to stand on their shoulders, grab solutions that work, implement them, tweek them to my needs and system and topology, and devise a workable solution that fits my needs.

Thank You!!

< /side note >

After digging into several databases in several servers, I ended up on a particular database last night, just before I went home. While I was looking at this db one of our database developers called me with a complaint about a slow running report he was working on. After looking into it, locating the spid and digging around it to see what I could determine, we determined that it was indeed processing, it was indeed working, just very slowly. It had been running for an hour and a half so far, when he called me. My first thought was that I had caused the slowness with the index work I was performing. After disclosing this to him, and determining that it was not the case, we kept looking. At one point I decided to give up, remembering that I was just about to dig into this database in particular and look at its missing and unused indexes. I informed him we should do that now, instead of looking more.

He killed the report and we watched it complete. He could tell that it had processes somewhat, but was not finished yet. I then looked for missing indexes, and with his help, identified likely candidates that would affect his query in particular. We added a few indexes to these tables along with 2 other tables. I think in total we added 6 indexes only to this database. We then removed several indexes that were unused. After this work I suggested he kick off the report. Almost immediately we received an alert from SQLMonitor indicating that we had high processor utilization. This lasted for 3 minutes and levels returned to normal. The report was finished in 4 minutes. This report, remember, had been approaching 2 hours previously, and now completed in 4 minutes. We executed it again, and monitored again. Same results. Yeah!! Database Developer is happy. I am happy. I go home.

The next day I realize that this is valuable information and could help me historically, if I could collect it. I set out to do so, and if you are still reading, do not be disappointed. I succeeded, or I wouldn't be writing this blog about it, I'd still be working on it...

I converted the 2 queries I was using into powershell variables, and then I execute this query against each database I want too, against each database server I choose too. I collect this data, and store it in a staging table in my DatabaseMonitoring database. After it has all been processed I call a proc that sticks this collected data from the staging table into a historical table, adding an Identity ID and a datetime stamp. Now I know when the execution occurred, along with the information about the missing and unused index, per server, per database, per table, etc.

I did not get fancy with the powershell and do any looping or anything like that. The DBs that I look into are hard coded in the script. Someday, maybe, I can get back to the script and make it purty. But now, I'm done. The code is put into a job that will fire off periodically and collect this information historically. Now I just have to look at it periodically as well.

I want to take some time soon to pull this accumulated data into a report and add it to my 'Monitor Everything' report that sends me daily email statuses for all these processes I have added to our monitoring topology. Soon. After a few projects are complete, I will return.

Yeah for SQL Monitor, Boo for SQL Monitor

Over the weekend, starting on Wednesday evening, I was on vacation. In Utah there is a school break in the Spring and Fall, sometimes with the kids having an entire week off. I took Thursday and Friday off, and we headed south to visit Zion National Park. We had a glorious time. Internet connectivity was via WiFi while we were in the campground only. Our cell phones stopped receiving cell signal about 40 minutes from the National Park, and remained eerily quiet for the duration of the weekend. This is a blessing and a curse. Knowing that things may go awry and I may need to pop into my databases always leaves me a bit uneasy. So, as often as I could, I connected to the WiFi and checked my work mail. A few times a day, usually in the morning and evenings.

On Sunday morning I was able to connect and to my surprise, SQL Monitor had captured a slew of error messages. 10 or 12 of them in a row, all occurring since midnight. My heart skipped a beat. What was going on... some digging ensued.

I feel so luck to have SQL monitor watching my systems, and emailing myself, along with 2 others in the IT department. If something awry occurred, it would let us know, and if I didn't jump on it, at least the other 2 humans would know via email something was up. So, something was up. I found it, and was investigating. Thanks SQL Monitor for watching my DBs while I climbed to Angels Landing in Zion National Park.

What was the error that SQL Monitor was complaining to me about? A nice generic one. 'SQL Server error log entry'. This means that something happened, was logged into the error log, and, well, that's it. Just that that think happened. If you are like me, your heart starts skipping a few more beats. As I looked at each alert down the chain, they were all the same. What I didnt notice at this juncture was that each alert was coming to me via email roughly and an hour interval. This is valuable to know, as it wasn't 12 alerts about 12 error log entries. It was an alert about an error log entry, and it was being repeated every hour. Much different story. So i dig deeper. To do this I need to drill into the alert itself and look at more details. Now I learn the following

The Database ID 6, Page (1:118112), slot 0 for LOB data type node does not exist.

The suggestion was to run dbcc CheckTable on the offending object. I need to know what database that is. Database ID 6. Is it one of my important databases? or a lesser important one? or a supplemental one. Which one is it... aaarrrggghhhh, I NEED TO KNOW!!! Since I had been out in the mountains, it seemed like my TSQL-fu was lacking and it did take me a minute to remember I could query sys.databases to see which one was Database ID 6. This was probably a few seconds, but in the panic of digging, it always seems longer. So, Database ID 6 turns out to be the database RedGateMonitor. Whew. Its only that db that is having an issue. A couple of quick DBCC CheckDB commands later I realize that all other DBs are in proper order. At least at the instance that I ran the CheckDB command. (hehe). So, I run on the RedGateMonitor and it encountered an object that has some issues. It is the object 'data.Cluster_SqlServer_SqlProcess_UnstableSamples'. I have no idea what this is, or if its needed, or what I should do with it. But, since its RedGateMonitor itself that has an issue, I figure I have some time.

Yeah for SQL Monitor for finding an issue on my DBs. Boo for SQLMonitor for its own object becoming funked up.

So, as you picture this, picture me on the top bunk of our camper, in my jammies (the desired PG rating refused to allow me to let you picture me in my undies), with a laptop on my lap, hooked into the RV campground WiFi, hunting down this issue. I am much more calm now that I know what db and what object is causing the issue. I quickly pen an email to my crew letting them know that I have discovered the issue, and a fix will come at some point, but until such time, we will continue to receive emails about every hour reminding us. Luckily its the weekend, and they can be ignored. My next step was to tweet my question about this object to #sqlmonitor and #kickasssupport. Knowing that someone will see it, and we'll get going on a solution soon. Maybe on Monday, maybe before.

Sure enough, I come in to work on Monday and see a tweet from @JowleyMonster with suggestions on how to remediate my issue. After a few attempts at recovering the data in the table via backups, I resorted to simply running a DBCC CheckDB with repair_allow_data_loss. I had to put the DB into SINGLE_USER mode first. Then run the DBCC CheckDB command. Then continue to verify via DBCC CheckTable on the particular object in question, followed by a larger DBCC CheckDB on the entire DB. When it all seemed OK, I returned the DB to MULTI_USER.

Now, you may be screaming at the screen now that I simply horked the connections to SQL Monitor, and did so in a graceful manner. I thought the same thing, and honestly wanted to try it out. I have been being so careful with it with other operations, I wanted to see what happened. In other instances I will actually stop monitoring everything, then have someone actually shut down the service, and then verify that all connections from SQLMonitor had terminated. But this time, I was curious.

It handled itself perfectly. Obviously the webpage that I had up throughout this ordeal was in a funked state. With a refresh, a few moments of uncertainty and stress on my part, it refreshed just fine. All was back to normal.

I am now free to pursue other tasks, as I leave the little gremlins that are SQLMonitor to do their job watching my DBs and letting me know when something is awry. It will happen. They will let me know. And I will dig in and fix them. I love having them around watching, especially when I am not.