There are two upgrade paths for upgrading Microsoft SQL Server 2008 (R2) to SQL 2012, broadly speaking they are an inline upgrade or a migration. My preferred method for upgrading to SQL Server 2012 is with a migration because it’s smoother to plan and execute. It does however, require more effort redirecting applications to new database locations. Migrating takes longer to upgrade to SQL Server 2012, but the actual amount of time each database is down is shorter, so this can be preferable from a service point of view.
Important SQL Server 2012 Pre-Upgrade Steps
To upgrade SQL Server 2008 (R2) to SQL 2012, follow these initial steps:
- Do some research to ensure that the applications connecting to the SQL server support MS SQL 2012
- Install the Microsoft SQL Upgrade Advisor from the SQL Server 2012 installation media and run a report against the SQL 2008 (R2) server that you wish to upgrade. Note that the Upgrade Advisor does not have to be installed on the SQL server itself, it can be run remotely
- Fix any issues highlighted by the SQL Upgrade Advisor
Now you have to decide whether you want to do an inline upgrade or a SQL migration. As mentioned before, there are advantages of each method.
How To Perform An Inline Upgrade Of SQL Server 2008 R2 To SQL 2012
An inline upgrade of SQL to SQL Server 2012 carries more risk than a migration, but most of this risk can be mitigated using procedure and application research and proofing. To upgrade to SQL Server 2012:
- Agree an outage time to all services provided by the databases hosted on the SQL server you are upgrading
At this point, you may wish to take a backup of the SQL server and test the upgrade on a backup first
- Disconnect the SQL server from the network so users cannot use the applications connecting to the SQL server
- Insert the SQL Server media and run an Upgrade Installation from the installation menu. Follow the wizard though until the installation is complete. This will also upgrade your SQL databases to SQL 2012
- Test the applications pointing to the databases
How To Upgrade SQL Server to 2012 Using A SQL Migration
This method is safer because it gives you the opportunity to migrate each database individually and have a seperate roll back plan for each application relying on a database. Because it involves migrating to a new SQL Server instance, extra time must be taken to move the databases and change application settings to point to the new database. You may wish to test the following steps in a preproduction environment first, but to migrate SQL Server to 2012:
- Build a new SQL Server 2012 server and run Windows Updates
- Arrange an outage on a SQL database
- Backup that database
- Detach the SQL database and attach it to SQL Server 2012 (This process upgrades the database to a SQL 2012 database)
- Point the application using that database to the SQL Server 2012 server that you are migrating to (How you do this will depend on the individual application – you’ll need to do some research before hand)
- Test the application
- Repeat steps 2-6 for each database, before decommissioning the old SQL server
If the databases involved are very large, you may wish to consider an inline upgrade or some sort of clever SQL Replication, perhaps?