This is the second post in a series on Upgrading to SQL Server 2012. Tips, tricks, hazards, pitfalls, and real world experiences.
Your Upgrade Strategy
A big part of the upgrade strategy should be how we do it. There are three basic approaches to the problem. There is no right answer, as it depends on where your company is at in terms of hardware refresh cycle, SAN infrastructure, OS version, and HA/DR posture.
In Place Upgrade
This is the simplest approach. When you execute this upgrade, you will replace an instance of SQL Server 2005,2008, or R2 with a running copy of SQL Server 2012. The instance can be default or named. Think of it as a Hail Mary pass. You get one shot at it and if it fails, rollback can be ugly. That’s why you always need to do your homework and plan carefully for each upgrade, with multiple fallback positions. The big pro is that it is a lot simpler than the other approaches. The big con is that you cannot upgrade cross platform. This means if you are running any version of 32bit SQL Server, you cannot do an in-place upgrade to SQL Server 2012 64bit.
Side by Side upgrade
This approach can be also referred to as Build-out-new or migration. I am using the term migration to move databases to a new instance of SQL Server, as opposed to migrating to or from another RDBMS platform like Oracle or DB2. The Side by Side approach can take on two flavors:
- A new instance of SQL Server on the same server.
- A new instance of SQL Server on a new server.
If you take the side by side strategy, you will then test and move your databases in small chunks or groups over time to the new instance. Note that if you install a 2nd instance on the same server, it will have to be a named instance (assuming of course, that the 1st instance was the default instance). Some applications don’t like / can’t handle a named instance of SQL Server. Check with your ISV.
So what are the pros and cons of the side by side approach? Obviously if your hardware is more than a few years old you are probably going to be migrating to new hardware. If you are already on newer hardware, you will want to go side by side on the same box. Personally, I prefer side by side on new hardware. Long term, it’s a cleaner strategy. Big pro of this side by side approach is that your DBA team can move at their own pace and move databases slowly via the backup/restore , detach/reattach or by using the copy database wizard. The big con of this approach is that it’s more labor intensive.
Do Your Homework
An upgrade is 75% project planning, and 25% execution. Take the time upfront and plan it out correctly and you won’t have any problems.