Datacenter’s and companies everywhere generally rely on a core piece of software to store their data and metrics, SQL Server. It’s nice to keep it up to date, but sometimes, and a lot of the time, if it’s not broken, don’t fix it. SQL Server has been around for quite a while, there are many databases hosted by SQL Server. Microsoft has released many upgrade advisors over the years, but recently the SQL Server team has overhauled their traditional SQL Server Upgrade Advisor and have created a new product – SQL Server Data Migration Assistant (DMA), which is different than SQL Server Migration Assistant (SSMA). To pull off a successful migration or upgrade, you need to do your detective work.

The Challenge

Years roll by almost as fast as the gas prices changes. This means that there always seems to be a new version of SQL Server out on the market. Whether your database is homegrown or via a software product, you may have to look at migrating it to a newer version of SQL Server. Most people think, how hard is it to migrate from one version to another. Detach the database, attach to the new database and it’ll automatically upgrade. This is true. But it’s like putting together a piece of furniture and having left over pieces, or not enough pieces. After your migration, you want to end up with a consistent database where all the functions, stored procedures, and access works as designed.

  • Some of the store procedures or function calls that you’ve been using may have finally been removed
  • Some of the store procedures or function calls that you’ve been using may cause poor performance
  • Some of the data types have been removed
  • SQL Mail, Database Mail?

Things change over time, usually for the better! Things that use to be best practice are no longer best practice.

This is where SQL Server Data Migration Assistant comes in!

The Solution

SQL Server DMA is a battery of assessments. If you’ve heard of Microsoft Best Practices Analyzer (BPA), think of this for SQL Server, but from a perspective of how the data is store and accessed. When you run DMA, you tell it which type of assessments you want to run.

After the assessments are run, you’re not just given back the best practices for SQL Server, but you’re given a report of what works and doesn’t work for ALL SQL Server versions back to SQL Server 2005! Essentially DMA will show you how well your database will perform across the different database versions. Although getting to the latest version of SQL Server for example from a version 8 compatible database might take a little work. But with the output of DMA, it’s a lot less work!

Summary

SQL DMA is a great tool for in house IT Technicians and especially consultants. Databases are everywhere within an IT organization and it’s hard to keep on top of all of them from a design perspective and tracking each stored procedure. Use SQL Data Migration Assistant to figure all of this out. In my next blog post, I’ll show you the tool.