70-457 Reviewer #04, Implement a Migration Strategy

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.

Migration Strategies

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

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!

Copy Database Wizard

Simply put

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”.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment