My current project with Coeo is for a hosting company and their major sports brand customer. Of course, they don't actually intend moving the data centre - just the data, but this still presents some interesting challenges!
The Brief
The customer has 300+ websites and related databases with a hosting provider in mainland Europe and the project is to relocate the application, databases and network infrastructure to the UK with minimal downtime and no data loss. The servers are in separate domains, with no trust relationship and we've got a 2Mb leased line between data centres.
The Plan
The largest full database backups are around 12GB in size and testing has shown we can move a 1GB file in ~1.5 hours over the WAN. We've carried out a proof-of-concept using a custom log shipping technique to move the databases between locations. Log shipping essentially comprises three steps - backup, copy, restore and our custom log shipping incorporates compressing the files before the copy and uncompressing afterwards via a series of scripts and to allow completion within our migration window.
There are many other considerations such as security, DTS, Full Text catalogs, jobs, maintenance plans including operators and alerts - each of which should be reviewed and migrated if required.
The Availability Challenge
There's often a lack of precision around cutover times and availability when DNS is involved, even if TTLs are reduced - it can still be a challenge to pinpoint propagation times and ensure a consistent experience for all users from a network perspective. We're currently going through a number of dry-run iterations of the backup/ compress/ copy/ uncompress/ restore cycle to ensure the process is robust and timings for cutover are predictable.
In any migration project there has to be a trade off during cutover between data loss and data availability. Using the log shipping technique outlined above, we bring the database online in the new data centre by restoring a tail-log backup using the WITH RECOVERY option. However, what the status of the original database from the point the tail-log backup was taken?
There are three options: take database off-line, put database into read-only mode or continue to allow access (no change). The first two options would ensure data is consistent between the two sites since no further changes will be allowed to the original database - the impact to users largely depends on whether the application can cope with read-only access to a database. The third option is more interesting in a web hosting scenario because most of these sites are effectively online catalogs (not ecommerce), therefore the sites benefit from 100% uptime as they can afford some minimal data loss during the cutover. Most online marketing or e-catalog oriented websites write to a database for usage statistics, logging search terms, trend analysis and enquiries etc. Essentially, no money is lost if transactions are written that are subsequently not present post-cutover.
The amount of data lost can be quantified as the time between the tail-log backup being taken, and the DNS changes taking effect to redirect visitors to the new servers. Providing this is known and managed, this is acceptable in most scenarios where uptime is more important than data loss. Given sufficient application and schema knowledge, the data could be exported and loaded into the new server after cutover if required (either via bcp or DTS), this option isn't practical in this scenario because of the number of databases and timeframes for cutover.
The Conclusion
There are a number of feasbile solutions satisfying the business requirements and it's important to clearly communicate the pros and cons of each solution to enable the business to make the right decision.
We're still working on dry-run testing, but intend to use a combination of continued write access to databases for catalog sites and one of the other methods for sites where transactional consistency is required (choosing an appropriate approach in each case).
The migration plan may consist of a combination of solutions - each fit for purpose and selected for their characteristics. Building a thorough migration plan and testing it meticulously will reduce risk and allow predictable timings.
Justin Langford
Principal Consultant
http://coeo.com - The SQL Server Experts