Upgrading an instance is a process in which you upgrade an existing instance of SQL Server to SQL Server 2012. Before proceeding to implement a migration strategy, you will need to ask yourself the following and evaluate
From which edition do you need to upgrade or migrate? Your current SQL edition will tell you if you will succeed in migrating to SQL Server 2012. Some points to take note of;
- Itanuim architecture supported by 64 bit in previous versions in SQL Server is not supported in SQL Server 2012.
- Cross architecture upgrade is also not supported in SQL Server 2012. So you cannot upgrade from x86 instance to x64 instance.
- DTS migration is not supported in SQL Server 2012. These packages must be converted to SSIS packages first before you can deploy.
- You will need to apply SP2 to your SQL Server 2008 before you can upgrade to SQL Server 2012.
- You will need to apply SP1 to your SQL Server 2008 R2 before you can upgrade to SQL Server 2012.
From which processor architecture are you upgrading? Note that only Windows Server 2008 or later can support an installation of SQL Server 2012.
You have 2 options when planning for your migration. Upgrade your current instance in place or move to a new instance of SQL Server 2012. Upgrade your current instance means you are retaining your current server and doing a fresh install of SQL Server 2012. Yes, it is possible to install an instance of SQL Server 2012 side by side with SQL Server 2008 or SQL Server 2005.
Migrating or moving your database means you have a new server on standby already installed with SQL Server 2012. Both options has its pros and cons.
- Upgrade option is less time consuming and less error prone because you are just updating the SQL system files.
- Migrating or moving to a new server with a new instance of SQL Server 2012 may require some downtime depending on how large you database files are.
Upgrade Advisor is a tool included in SQL Server 2012 media installer. Upgrade Advisor helps you identify what issues you will have during your upgrade. The tool can help you check your Database Engine, Analysis Services, Reporting Services and Integration Services.
Upgrade Advisor can be found in the Servers\redist\Upgrade Advisor folder of your SQL Server installation disk or you can download a copy of the Upgrade Advisor tool here.
When it’s time to move
When you do opt to migrate your database you have some options on which method to go about this. The 3 methods below is what is included for the review.
Detach and Attach
Detach and attach involves the process of detaching the database from your old SQL Server, dropping all user connections, copying that database file to the new server, and from the new server, attach that database file to your new SQL Server 2012. On how fast this method is and long your downtime will be largely depends on how fast you can copy your database file from your target server to your destination server.
One thing to be wary about this method though is the case of orphaned users. Orphaned users are users that exist in the database level because it comes with mdf file but does not exist in server level. Microsoft already has prepared a script for creating your logins. All you have to do select the logins you need to move to the new server and run the script and you’re good to go. You can find the script here.
Backup and Restore
If you do need your database online during your migration process, you might want to do a full backup on your database and restore it on the new SQL Server 2012. Then just restore also succeeding transaction log backups from the time you restored your full back up to keep your data up to date.
Again, you will need to execute the sp_help_revlogin here to check for orphaned users especially SQL authenticated logins.
Copy Database Wizard
This is my favorite way to go when migrating a database. It has simplified wizard under Tasks menu when your right click on a database. Also you can limit your database connectivity downtime depending on the method on how you will migrate, move or copy. And my favorite feature of this wizard is that you can also automatically migrate the system objects that is related to that database you are migrating. See the screen below. No need to prepare a separate script for creating logins or jobs. You can select it all from the wizard. Neat!
Your migration strategy or plan should start with evaluating the following
- Old SQL Server version and any applied service packs.
- New host server. Remember SQL Server 2012 is only supported on Windows Server 2008 or later.
- What is the downtime allowed? You might need to schedule your migration on weekends or off peak hours.
- Detach/Attach or Backup/Restore or Copy Database Wizard? Note that you cannot detach if your current database is involved in any replication or is mirrored or has a snapshot.
- Preparation of your scripts on moving metadata objects.
As they say “If you fail to plan, you plan to fail”.