SQL Saturday #279 in Phoenix – March 29, 2014

This weekend, SQL Saturday #279 is in Phoenix. I am lucky enough to have been selected to present 2 sessions. I speak at 9:45 AM in DBA - Room: Room 4 on some tips that I have used to help me with Release Management. I also speak at 4:00 PM in that same room on Documentation. I have given these sessions before and really enjoy the topics, as well as the discussions that come from them.

Since we always will have to make changes to our production systems, coming up with a descent plan of attack that can be repeated to allow controlled and graceful change to occur seems like a no brainer to me. I have spent a lot of time in different organizations fine tuning processes to allow for this to occur, and it is always changing.

I believe that every person in this and many other fields of work would tell you that we lack documentation. Maybe authors don’t complain about that. But I bet a lot of others will agree. What to document, when, how, and so on. I hope in this session to give you a slew of examples, templates and ideas on how to document the things that matter most to you.

In both of these sessions, I hope to really share with you my experience, examples and tricks, and hope that you will be inspired to improve your own shop, make a change for the better, and come out of these sessions with something gained, be it directly related to what I talk and show or not, it matters not to me. Just that you are inspired, and become better for that inspiration.

It has been a long time since I was in the Phoenix area, and with the winter we have had, I look forward to a tease of warm weather for a couple days.

If you can make it out to this free event, please do. Come and learn and network and experience the #SQLFamily as we share and grow together. 

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.