Archive for the ‘SQL2008’ Category

Changing Gears again.. it’s the life of a DBA

August 10, 2012 Leave a comment

When I last wrote my big project was to upgrade our primary business systems to SQL 2008 while we sourced a new application to replace our legacy one.  At the same time I’m a resource for the new application sourcing.

As I began planning upgrading my existing windows 2003/sql 2005 cluster I realized that the resources I needed for my upgrade were already hip deep into the sourcing project.  This kind of resources contention is never a good thing, especially because we are all still on the hook to support the environment and take care of the day to day business needs.

Being a cautious DBA I was planning a side by side upgrade instead of an in-place upgrade and this meant that all of our client workstations, reports, Access routines (gasp!  I know, we have them and rely on them), SSIS packages, etc would need to be pointed to the new cluster and tested before we could cut over.  It sounds like a short list and my first reaction was that it would be easy – we know where all the reports, access routines, etc live so no problem…

Once we dug in and detailed out all the different places that the instance name would have to be changed it became quite daunting very quickly.  Especially considering these systems, reports, packages, and Access routines would all be scrapped in just about 12 months with the new software we are sourcing.
I made a quick couple of lists to help me decide if we should press on – it’s what I do as a first step when making a decision like this.
Pro’s to upgrading to 2008

* we’re back on a platform that  is supported under mainstream support

* I get to use page/row compression

Con’s to upgrading to 2008

* risk that we will miss something in testing and cause an outage for the business

* over allocation of people resources

* we have a stable system now, and upgrades introduce instability

I know there are more benefits to upgrading from 2005 to 2008 than I’ve listed, but to be honest, those are the two that we would have taken advantage of immediately.  After thinking a bit and talking to my team we made the decision to scrap the 2008 upgrade for these business systems.

So, it’s time to switch gears.  I’m focusing on gearing up with SQL 2012, evaluating Always On for HA, planning a new architecture for the new business systems, and keeping my old 2005 cluster running for the next 12 months.

My plan right now, is to make notes of what I’m learning as I go along here on my blog for my own reference – hopefully it will help you as well.

Categories: SQL2008

It’s never easy is it?

We recently made a business decision that upgrading some of our business systems to current versions didn’t make much sense – they don’t really meet the needs of the business and the upgrade project would take almost as many resources as replacing them.  The replacement project has kicked off and it looks like we will have new systems in place within 18 months or so.

This is all well and good – migrations are always a more interesting project for me than upgrades, but it left me in a bit of a quandary about what to do with the SQL install that supports these systems.  I’m still running these databases on SQL 2005 (I know! I know!) and could really take advantage of page/row compression for some of them.  Of course, to do that I need to be running at least SQL 2008 so it is time to start planning and testing my upgrade paths.  After doing some research on what versions of SQL the applications supported with our current version of software I realized that as much as I wanted to I couldn’t upgrade to SQL 2008 R2, and instead was limited to SQL 2008.  It’s not great, but better than supporting  these systems on 2005 for another 18 months.

I have a big old honking test system that holds five named instances of SQL for test/dev/qa.  Because we were going to upgrade the software to current it was a mixed bag as far as my SQL versions.

My current reality looked like this:

Instance1 – 2005 sp2

Instance2 – 2005 sp2

Instance3 – 2005 sp2

Instance4 – 2008 R2

Instance5 – 2008 R2


I needed it to get to here:


Instance1 – 2008 sp3

Instance2 – 2008 sp3

Instance3 – 2008 R2

(Instances 4 and 5 could be removed – they were installed for the application upgrade, and not needed any more).

Easy-peasy right?  Not so much!

Initially I (naively) assumed that because I was dealing with named instances I could upgrade the 2005 instances to 2008 with ease even though I had a those two 2008R2 instances living out there.. I mean, I don’t care what version of SSMS is running, I just cared about the engine version for each instance.

That was my first poor assumption.  After attempting the upgrade and it failing for Instance2 I realized that I needed to remove the 2008 R2 bits from the server.

No problem I thought – I’ll remove 2008 R2 via the control panel since I don’t really need it currently and go on with my upgrades.  If only it were that easy!

I went round and round with this, and eventually wound having to use Aaron Bertrand’s guidance in this blog post to remove all of the 2008 bits completely.

Once that was complete I was able to perform in place upgrades to SQL 2008 SP3 for Instance2 and Instance3 and only once those were complete did I dare to attempt the upgrade on Instance1 to 2008 R2.

All in all I spent entirely too many hours on this, but it would have been even longer had Aaron not blogged about removing the eval bits of 2008R2 back in 2010.


For me it was just another reminder that you should ALWAYS test before attempting upgrades or changes in production.   Certainly not my favorite way to spend a Sunday afternoon, but it would have been much worse had it been in production and I had a steadily decreasing maintenance window.

Categories: SQL, SQL2008

Fighting with the SQL Agent

January 26, 2011 Leave a comment

I am currently struggling with getting new servers built out for upcoming projects, supporting our existing servers, and rapidly deploying new servers to meet increased demand from our customers (a very good problem to have in my opinion), and moving to a new cubicle at work to make room for our new team members.

Tonight the top thing on my list was to get a new Windows/SQL 2008 R2 server built up and databases restored to it for an upgrade project from SQL 2000.  Needless to say, just as I was setting my service accounts I was hit up with a production problem and became distracted.  In my rush to get the install going I swapped the Agent and Engine service accounts in the setup wizard.  It wasn’t until after the build was complete that I realized my error and corrected the accounts via Configuration Manager.  It really wasn’t anything more than housekeeping on my part.  The two accounts have identical domain privileges, and aside from the Agent account having agt in the AD properties it makes no difference to SQL server which account runs which service.  

So, fast forward a few hours when I am ready to start my restores and I realize that the SQL agent isn’t started, and Agent XPs are disabled. 

A quick script execution to enable Agent XPs and back to Configuration Manager I go to start the service, only to have it start then stop and to get this oh so helpful error on my screen:

The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped.  Some Services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

So I start digging into event logs.  For whatever reason I started with the Windows Application Event log (guess it’s the sys admin in me), only to find the last Agent event was hours ago and showed a normal shutdown request.  Next up is the SQL Server error log where I see the reconfigure events, but nothing from the attempted start of the Agent service.   Finally I dig into the Agent error log where I notice that the file hasn’t been modified for a few hours, and to get a security error when I try to open it with notepad.   Huh, that’s a bit strange….

Next step is to look at the file permissions on the agent error log itself – turns out that my agent service account had no permissions set at all, and my engine service account had full permissions. 

I added the Agent service account and gave it full permissions to the sqlagent.out file and restarted the service again via Configuration Manager and will wonders never cease it started with no issues this time.

Hopefully this will help someone else out there in similar circumstances, and save you a bit of frustration.

Categories: SQL, SQL2008