A new beginning: migrating database systems to new environments

The rule of thumb in IT is that ’hardware used in production becomes obsolete every 3 years and needs to be replaced and upgraded’.

So far so good. The boss approves, the financial department approves, the IT department writes the specs and the vendor eventually delivers the hardware and installs it.

Now what?

The old ‘obsolete’ hardware is still in place, the system is still running on it and no one seems to know the exact sequence of steps to be performed in order to get most out of the new hardware.

Let’s make this case specific to SQL Server instances and databases. (I am sure that the general idea behind this could be applied any technology, though.)

A step back:

Some of the questions which (hopefully) preceded the decision of buying the new hardware are:

  • How much performance gain can we get from the new hardware
  • What is the ROI over the next 3 years (when the new hardware will become obsolete)
  • How would we utilize the old hardware after we migrate to the new one
  • Who, when and how will migrate
  • How much will the migration cost in downtime, maintenance, workforce
  • Do we really need the upgrade?

 

There are plenty of other questions, but they are specific to each company, so I will leave them aside. What I would like to highlight here is, that so far I have seen numerous cases of companies buying hardware before asking any of the above questions; and in all cases it turned out that it is much more expensive to ask the questions post factum.

OK, got the hardware, now what:

There are several stages to go though before even getting close to the migration.

  1. Choice of OS – this is important, since the different OS offer variety of features, and so do the different editions (Standard, Enterprise, R2)
  2. Testing the hardware
    1. connections, disks, memory, CPUs;
    2. in case of clustering – testing the failover, testing the SAN

 

Testing the hardware is essential; it is much better to discover a hardware flaw before having anything migrated than to discover it later. (Once I even got a 10 day-old server to crash due to a vigorous testing and some admitted fault by the vendor – luckily someone was performing the testing, right?!)

  1. Choice of SQL Server version and edition.

 

Think of the features which are crucial for the databases and the applications; weigh the benefits vs. the costs and be realistic.

I have said it before, and will say it again: depending on the budget and the intentions one can either view the Enterprise edition as a super expensive extravagant asset OR one can view the Standard edition as a chap knock-off of the Enterprise edition.

This topic is very business specific, and since no two businesses are alike, I will leave it up to the readers to decide for themselves.

  1. Benchmarking – know what to expect, avoid surprises.

 

Performance measurement of an application means nothing without the context of the baselined capabilities of the server.

If you do not measure initially how much the server can handle, you will never have some data set to compare to the application performance. Hence, it will be very hard to know if the application is performing as it should or if there is a degradation of the performance.

  1. Compose a convention (set of rules) for migrating the databases to your new hardware
    1. Collect information about the application owner, developer, testers (contact info)
    2. Collect information about the users (contact info for some main users, if applicable)
    3. Collect information about the database and the application: what is the expected growth in 1 month, 6 months, 1 year, 3 years
    4. What are the specific requirements of the system: specific settings which cane ‘make or break’ the application (for example, database compatibility mode, autoupdate statistics and so on)
    5. Make a list of explicit rules for the new environment. For example: no database shrinking, no index rebuild in peak hours, no triggers etc.
  2. Set up a monitoring environment for your system
    1. Policy based management
    2. DMVs
    3. Other tools
    4. Compare the benchmarks of the performance of the old system and the new system
    5. Compare the benchmarks of the new system in even time slices and make sure no alarming trends are underway.

 

Keeping performance at a certain level is a complex job, and it requires preparation even before the new hardware enters the door and the first person logs on to it.

Hope this list gives some useful guidelines for successful system upgrades which do not bring a lot of extra costs / frustrations to the end user.

 

Comments are closed.