Avoiding Pitfalls in your SQL Server Migration

Recently, most of my blog entries have been about SQL Server migrations. With extended support for Microsoft SQL Server 2005 having ended in April, it’s a timely discussion. In this blog, I’d like to share with you how we’ve applied the “lessons learned” during engagements over the last several years to a set of key migration best practices.

It all starts with understanding where you are. Discovery is the key to success. You need identify your full inventory of all servers running SQL Server. This would include deployment architectures, version levels, associated availability requirements, as well as specific performance-level characteristics.

Next you’ll have to identify the interdependencies. Those include more than just databases. There are a lot of additional dependencies such as SQL security users/logins, agent jobs, linked servers, and other configurations that have to be accounted for. You’ll have to take legacy and third-party applications into consideration as well, and understand how your new environment will support those services.

Know your costs—you need to look at licensing, business, and support impact on your cost structure and do what’s necessary to optimize your post-migration cost structure.

Minimize Impact to Business. Accounting for application outage windows and migration time frames will also be of critical importance to ensuring a smooth transition. The level of criticality defined here—based on business need—should align with the availability/recovery solutions planned in the future-state architecture.

Consider the entire data platform supporting data integration, data warehousing, analytics, and reporting. And don’t underestimate the time and effort to migrate and test related BI solutions. Plan for testing and leave enough time to prepare prior to moving to a full production environment.

The process of planning and executing a SQL Server migration is often complex. But the right approach and best practices can help you avoid the pitfalls of migration. To read about our recommended best practices in greater detail, take a look at our EMC Perspective: Five Best Practices for Ensuring a Successful SQL Server Migration.

In my next blog, I’ll be talking about application transformation.

About the Author: Joe Terrell