–Hurdles Start by Robert Voors –

With less than a year before SQL Server 2005 extended support ends, customers can start planning on upgrading their databases to SQL Server 2014 or even SQL Server 2016 when it is released. In a previous blog post, I talked about how to integrate Microsoft Product Lifecycle and Support Policies into IT Operations which concluded with planning upgrades and migration based on the version of SQL Server that you are running. In yet another blog post, I talked about how you can use database mirroring to upgrade and migrate a SQL Server 2005 database to a later version.

But regardless of the strategies and approaches you take when upgrading and migrating your databases (or any of your IT infrastructure, for that matter,) there are four major challenges that you need to address in order to guarantee a successful project. The strategies and approaches will change over the years depending on the availability of technologies and innovations. But these four challenges remain the same. If you can address them in your planning phase, rest assured that the upgrade and migration will be successful.

1. Destination. This should answer the question, “Where will you be migrating to?” More than a decade ago, it was just a question of, “which hardware are we migrating into?” Then, there was, “which location are we migrating into?” This was when organizations started outsourcing the hosting of their IT infrastructure to hosting providers. Then came, “are we moving to physical or virtual?” Now, it’s “are we staying on-premise or moving to the cloud?” The destination will keep changing depending on the technologies available and the cost associated with the migration. But why would this be a challenge? Because each destination poses a different challenge in and of itself. If you are moving from a physical machine to another physical machine within your own data center, you need to worry about your hardware refresh cycles, warranty coverage and the gruesome calculation of license cost in terms of per-core allocation. SQL Server MVP Glen Berry (Twitter | blog) wrote about how your choice of the different Intel and AMD processors affect licensing cost. Licensing cost is one of the main reasons why a lot of customers are moving from physical to virtual machines. But, then you need to worry about which virtualization platform you’re moving in to – is it going to be VMWare, Hyper-V, Citrix, etc? Same thing when you’re moving to the cloud. Will you migrate to a platform-as-a-service offering or an infrastructure-as-a-service offering. The choice of your destination will determine how you plan for the migration. Oh, and let’s not forget about doing either an in-place upgrade or side-by-side upgrade. I’m not a big fan of in-place upgrade because of the risks of downtime. But this should be considered in the list of destinations that you want to migrate in to.
2. Content. This should answer the question, “what is being migrated?” Understand that whatever version of the application you use – let’s say SQL Server – will be dependent on the supported version of the operating system you intend to run it on. SQL Server 2016 is no longer supported to run on Windows Server 2008/R2. However, the end-of-life support for the application and the underlying operating system is totally different. For example, Windows Server 2003 end-of-life support was last week while SQL Server 2005 will be next year. When migrating your databases, will you just be migrating SQL Server or do you want to move to the latest version of the Windows Server operating system? My rule of thumb for new deployments and migration is to be on the latest version of both application and operating system if at all possible, even though I don’t get commissions for selling Microsoft licenses. Most of my customers think that this is just me trying to upsell them to upgrade to the latest and greatest. But the real reason why I recommend the latest versions is because they are going to do it again anyway after maybe 5 years or more. It will be cheaper from an operations point of view to just go with the latest and greatest right now so they can have a longer breathing room before the next end-of-life for both the application and operating system.  And choosing whether to just migrate the databases or include the operating system as well will determine how you will plan for the migration.
3. Identification. This answers the question, “will my applications recognize my database after the migration?” This really should be an application developer challenge of how to properly connect to the database after migration. I’ve seen different approaches throughout my career – embedding connection strings in the application, a configuration file, a configuration database, etc. And in almost all of those cases, the connection string uses the server hostname to connect tot he database. When you’re migrating your databases, there is no guarantee that the server hostname will stay as it is, unless you’re doing an in-place upgrade which I totally do not recommend. Now, imagine dealing with thousands of client applications like point-of-sale terminals that will connect to the database server. How do we deal with that? I sure don’t want to be manually changing all of the connection strings on all of the applications. Even when I can write a PowerShell script to do so, I’m still going to do it again in a future migration project. This is why I am a big fan of using abstraction for the connection string in the form of a DNS alias. This way, all I need to do is change the IP address on the DNS alias to point to the new database server and not have to change anything on the application connection string. The real challenge here is to  get the application developers to abstract the connection string as part of their development practice.
4. Availability. This answers the question, “how much downtime can I afford?” Applications evolve overtime. I’m sure you’ve seen one of those MS Access applications that ended up being a mission-critical application. When more users depend on the application, availability becomes a serious matter. The main reason why I wrote about using database mirroring to upgrade to SQL Server 2014 is because I wanted to achieve as minimal downtime as I possibly can during the migration process. This is also the main reason why I’m not a big fan of in-place upgrade. One of my most challenging migration project involved upgrading a multi-tiered SharePoint 2010 farm in less than 10 hours. And even if we did the cutover during a weekend, we only have a limited maintenance window since users are coming from all over the world. I had to automate and script most of the tasks to make sure that we meet the availability goals during the migration process.

These are the four major challenges that you need to address in every migration and upgrade project. Knowing the appropriate answers to the questions posed above will help you prepare, plan, document and test a migration project that increases your chances of success.

But, seriously, there really is no such thing as 100% success rate. Just like any high availability and disaster recovery solution, we cannot account for everything that will happen. That’s why we need to prepare and plan way ahead of time and define our success criteria so we’ll know what to achieve for. And if you’ve met those success criteria, then you can call it a successful project.