SQL Server Migration and Upgrade – Success

Over the past few months the IT Team have been preparing for one of our most ambitious upgrades of our infrastructure, not because of what we were trying to achieve, but the work needed to get us there.

Our newly retired database server happened to also be a file server, DNS, DHCP and WINS server as well as a Domain Controller. For most small businesses, this was reasonably normal to have a server fulfilling multiple roles – it was a bleak outlook for the team however, as because of the multi-role nature of the server, we’d have to rebuild and redeploy all of our in-house software to point to a new database. This was near enough 100 projects, services and websites needing attention.

Back in late 2011 when the server was first commissioned, we estimated it would be good for 70 – 100 users, allowing comfortably for a 100% growth of the business from the 38 users at the time. From a software development point of view, the concept of having to go through a migration to a new server is not something that was ever discussed. We were content to have our connection strings within our applications name the server directly.

Hindsight. Well… you know the saying.

Fast forward to the present, 202 active users were hitting our database, file services, authentication on a daily basis, and I had to spend a long time babysitting the server into reasonably smooth running – watching closely for long running processes and coming up with clever, slicker ways of running the queries to squeeze out that last extra bit of performance – deadlocks, blocked processes and slower responses were becoming the norm and action was urgently needed.

Our new server was racked and ready to be used some months ago, it runs SQL Server 2014, has 50% more cores, 50% more RAM and we hope will see us through another 3 years.

Our first job, learning from our mistakes of the past, was to DNS alias the server, for use with our database connections. With our alias in check, if we have to go through this exercise again, we’ll be ready.

With all of our software prepped and tested against the new database (which was pointing at our current production database), we were ready.

The documented approach to a database server migration is to detach the databases from production and reattach¬†on the new server. For the purposes of our exercise, we didn’t exactly follow convention – that process would have been a one-way street as our databases were running on SQL Server 2008 R2 at SQL Server 2000 compatibility level, and would have been upgraded as part of the deployment.

We opted for running a full set of backups, shutting down SQL Services and deploying these backups to the new server – with the help of a few scripts I’d created for deploying development database instances, this was painless. The other handy script in my tool belt was ‘sp_help_revlogin’ which we used to script out the logins and security settings.

We switched the DNS alias to the new server, flushed the DNS caches and fired up our first application – with our fingers tightly crossed, it started and could connect!

No Comments

Post a reply

Copyright © James Coleman-Powell, 2016