The final countdown: How to prepare for migration from Microsoft SQL Server 2005

For many, is Black Friday an unpleasant memory: slow-moving lines out the door for the hottest products and best deals, long waits at the check-out counter, and traffic jams in parking lots make for frustrated shoppers.

If a store does not adequately prepare for this retail frenzy, the day becomes an even bigger nightmare, with angry customers, harried employees and potential revenue loss.

Flash forward to April 12, 2016. A business hasn’t migrated from Microsoft SQL Server 2005 – or the migration has failed – and extended support has ended. That business has lost access to critical security updates. It will begin to incur higher costs to maintain its legacy IT products, and may even be running something so outdated that hardware can’t be upgraded.

Even more serious, it may face compliance issues because its obsolete infrastructure no longer meets regulatory standards, and if a crisis were to happen and infrastructure goes down, there is no one to call for help. That business is now the IT equivalent of that unprepared Black Friday retailer.

>See also: How to decide whether to update a system that is ending support

Migration trepidation

With these risks on the table, why haven’t some businesses migrated? For many, SQL Server 2005 insures their critical applications or data – it’s embedded in their day-to-day operations, so they continue to use it. The sum of total of users still running SQL Server 2005 is larger than any previous migration, with IDC estimating that about 800,000 servers globally still rely on it.

Leaning heavily on database infrastructure can actually foster procrastination. In a survey by Vision Solutions, nearly two-thirds of organisations said they delayed an important data migration for fear of downtime or lack of resources – two primary reasons users may still be on SQL Server 2005, despite the looming end-of-support deadline.

Other fears associated with migration involve the data’s integrity if the data does not synchronise correctly, the database hasn’t replicated, and even if it replicates core data, ancillary data – like user credentials and passwords – need to move over as well and behave the same way after migration. If this doesn’t happen, the migration has failed.

Some businesses need to migrate decades of data – multiple terabytes at one time – and are hesitant to even begin the process for fear that some data won’t migrate properly. Others may have attempted a migration that failed, and they are afraid to try again. Still others, such as a new business, may have never had to deal with a data migration, and fear of the unknown is keeping them from getting started.

Cost is another stumbling block. Some businesses may feel like they need to choose expensive migration software or risk a different kind of cost in the form of data loss or unplanned downtime.

As the clock ticks closer to the end of support for SQL Server 2005, keeping in mind the following tips can ease migration fears.

Due diligence is key. Many businesses already have an annual review process in place, but diving into the business’s infrastructure may show a much more complex picture than expected.

For example, an application may be using SQL Server 2005 behind the scenes, but have tertiary applications or front-end portals that will also need to be migrated at the same time.

Likewise, if a business is running older hardware supported by the SQL Server 2005 environment, then that hardware needs an upgrade.

Determining all infrastructure that needs to be updated, and all data/databases and servers that need to move, and figuring out a way to do everything all at once, may make the best use of the business resources devoted to migration.

After evaluating the infrastructure, it’s time to look at migration options. There are five factors businesses should consider when choosing the right migration solution:

1. Downtime and resilience

In Vision Solutions’ 2015 report, nearly 75% of respondents had not calculated the hourly cost of downtime for their business. Knowing what is and is not affordable in terms of downtime is of paramount importance when looking at migration options.

If a business is in a complex environment with SQL databases supporting multiple critical applications, they need to keep operations running on a 24/7 basis and cannot afford downtime – this is the case with many businesses that run externally facing applications such as portals or email.

An online commerce business, for example, would need all applications to function while terabytes of data are being migrated, so its customers could shop and check out without any issues.

2. Cost

Migrations are a necessary expense for IT infrastructure developments, and costs will vary across the board depending on what kind of migration option a business selects.

When looking at a solution and its overall cost, the entire context of the migration needs to be considered. This can include licensing costs; additional hardware, if a business is running older versions; resource time required; cost of any downtime; and any migration software.

Some free software does not get zero or near-zero downtime, and so the cost of downtime may trump any savings on that software if a business cannot absorb that cost.

3. Testing

A migration solution ideally will allow real-time testing. The ability to test the upgrade without impacting the production database will simulate the actual migration experience without affecting operations, and is invaluable to easing fears about downtime and data loss and any uncertainty about the migration process itself.

Completing a dry run of the actual migration and thoroughly testing the new version of the database is a must. If a migration solution does not allow the option to see a comparison on a test server, as well as provide minute-to-minute backup, then the test becomes less reassuring because the data are not current.

4. Data loss and functionality

For many businesses, data is at the heart of their operations, so maintaining data integrity while keeping the lights on 24/7 is the most important consideration when looking at any migration software.

Most businesses simply cannot afford a data loss during migration to a new database. It also is essential that data is synchronised as well as transferred, so applications function the way they did prior to the migration.

>See also: SQL Server 2005 end-of-life: how to plan for the future

5. Staff resources

Determine what learning curve is associated with the migration option. Staff may need to learn a new process, which will add to the total migration time, as does incorporating other services to manage the migration.

A migration solution that is intuitive and straightforward, with little to no learning curve associated with it, might be a better option for a business that does not have a lot of time built in for migration.

The clock is ticking on SQL Server 2005 support. Though not all businesses will migrate the same way, careful consideration of the factors above will ensure a business is making the right move and give all parties peace of mind.


Sourced from Suchita Nagale, VP of IT, Vision Solutions

Avatar photo

Ben Rossi

Ben was Vitesse Media's editorial director, leading content creation and editorial strategy across all Vitesse products, including its market-leading B2B and consumer magazines, websites, research and...

Related Topics