Friday, July 6, 2012

SQL 2012 Upgrade Part 7: Upgrade Plan

Alright! All the testing is done and we're ready to get this upgrade done! We had a rough upgrade plan to start with but we learned a lot during the testing process. As a result, the plan changed and became more detailed. Our weeks of testing and analysis paid off and left us in a vary comfortable place.

Pre-Upgrade Steps

Several things needed to be done before we could run the SQL Server 2012 installer to perform the upgrade to make sure that we didn't get part-way through the process and get a failure.

Update database backup paths

  1. Make a note of which servers have a network path for the default backup location and record the network path in a safe place. (I use OneNote.)
  2. Change the default backup path to a local directory, creating a new folder somewhere on the server if necessary.
See SQL 2012 Upgrade Gotcha: Network Backup Location for more details.

Disable scheduled jobs

  1. Make a note of all jobs that are going to be disabled.
  2. I disable all SQL Server Agent jobs.
  3. Coordinate with the operations team that manages the corporate scheduling solution to make sure no jobs are executed during the upgrade period.

Upgrade third-party SSIS components

After testing the SSIS components we determined that they could be updated at any point in the process. We decided to go ahead and do it before running the setup app. In theory, we could have upgraded them well ahead of the upgrade but the developers didn't have time to upgrade their workstations beforehand so we stuck with upgrading with everything else.

SQL Server Upgrade

A couple of our servers were still running SQL Server 2008 R2 RTM. Those needed to have Service Pack 1 applied but while that was running we planned to start the 2012 upgrade on the servers that were already properly prepared. Other than that, the SQL Server 2012 upgrade process was pretty simple.

Post-Upgrade Cleanup

There were still some things to be done after the upgrade process was complete. Not all of which was immediately obvious but came out during testing.

Update Statistics on Data Engine databases

I didn't think of doing this myself and nothing in the testing indicated that it was necessary but a co-worker suggested it. Further research found that Microsoft does indeed recommend updating statistics after the upgrade per Upgrade to SQL Server 2012 Using the Installation Wizard (Setup) (down at the bottom under "Next Steps"). The T-SQL statement below will update the statistics in each database on server (the PRINT statements are used to show the breaks between each database).

sp_msforeachdb 'USE [?]; PRINT ''***?***''; EXEC sp_updatestats; PRINT ''***************************'';'

Put the database backup paths back

The default backup locations need to be changed back to what they were before we did that pre-upgrade step. We don't want to run out of disk space on the server or cause backups to fail!

Change SQL 2000 data sources in SSAS

We had a couple SSAS databases that still referenced SQL Server 2000 application databases so they needed to be changed to use the .NET provider.

See SQL 2012 Upgrade Gotcha: SQL 2000 Not Supported in Native Client for more information.

Check SharePoint list views

This step is a quick sanity-check of the SharePoint Listview adapter that we use in one of our databases. It's a CLR components that allows us to create views that reference SharePoint lists. It's pretty handy for reports that need to reference these lists where the end-users can't wait for an ETL process to load the data into a datamart.

Process all cubes

Well, maybe not all the cubes. There were several analysis databases that we wanted to check so we planned to process those manually. The rest would be processed on their normal schedule. Since the upgrade was planned for a Saturday morning, we had plenty of time to make any corrections before the users would notice anything out of the ordinary.

We were pretty confident that this would go well as most of the foreseeable problems occurred during testing and we took what we learned and updated the upgrade checklist accordingly.

Enable scheduled jobs

The last thing to do is to turn all the scheduled jobs back on. Once we're confident that everything went properly we can resume normal operations.

The End

That's the plan. Now we only need to see how well it meets the enemy. Tune in next time to see just how well this plan worked out...

No comments:

Post a Comment