How to fit 16 hours of upgrade work into 2?

How to fit 16 hours of upgrade work into 2?

A client of mine is trying to upgrade their SQL Server box. They are on SQL 2000 now and the new box will be SQL 2000 as well. Before you ask, they don’t want to go to SQL 2005 for licensing issues (i.e. cost).

The problem I’m having is that in addition to moving the database they need to add an update field (smalldatetime) with an index. Adding the new field and index to each table is taking around 16 hours on the new box. This client is an online retail shop and they are completely dependant on SQL being up. The have given me a 2-3 hour window to make the move. So how do I get the database moved and updated in 2-3 hours?

This is what I have done so far:

Argued for a longer install window. Being down for 16 hours is a no go, not even an option.

Restored a copy to the new server, add the new field and index. Created a temp database on the old server and triggers that log the PK any time a change is made to the current prod db. Then use a script to pull the changed data into the new prod. This looked like it was going to work but it when it gets to the larger and more heavily used tables it takes way to long (25+ minutes per table). That shoots the 2-3 hour window.

Next plan is to move the current database to the new box. Then each night update as many of the tables as I can until it’s complete. I estimate this will take six to eight days. They are not very excited about being down every night for eight days.

So my question to all of you is; have you had to deal with a situation like this before and if so how did you resolve it? If not do you have any ideas as to how I can get the database moved and updated while keeping my client happy? I would be most appreciative of any advice or suggestions you may have.

Doug

With SQL 2005 you can add an index and allow the table to be in use.

so, maybe you could alter the field, then add the index.

Also, is the table only being read from?

If it is, you could create a copy and then rename.

How many rows in the table? 16 hours sounds like a long time.

Steve

|||

They are using SQL 2000 so an online index won't work. I like the idea of a copy and rename, just have to work out how to get any new fields into the new table but I think that could work.

There are 1.3 billion rows total. The top 14 tables make up about 1.2 billion rows. Anything and everything takes a long time on these tables.

Doug

|||

The best option that you have is really to backup the existing database and restore it to the new server. Then create the new table with the indexes that you need under a different name. Then add a trigger behind the original table to move any data over that has changed. Once everything is built and synchronized, take one brief outage and do a table rename.

When you are dealing with a customer that does not want to upgrade to SQL Server 2005 that has tables with billions of rows that wants to add indexes and get everything done in a very short time window you are pretty much stuck with doing some very major workarounds. They are quickly going to find out that the amount of downtime they will incur for stuff like this is going to cost them a whole lot more than simply upgrading to SQL Server 2005. With the new hardware that is available, the license costs really aren't that much different. When they bought SQL Server 2000 on a quad processor machine, they paid for 4 processor licenses. You can now by a single quad core processor that would have about the same processing capacity as the older quad proc machines we used to buy a couple of years ago and you are only having to pay for 1 processor license for SQL Server.

|||

Thanks to everyone for your help and advice. I was able to get the cut over down to just over six hours.

We broke it down into two sections; moving the database to the new server and adding the new fields and index. The actual cut over took just under two hours, mostly due to the file copy. Adding the new field and index was done in just over four hours. I have outlined the steps below for everyone’s future reference.

The actual move of the database did not really change but adding the fields was decreased by almost eight hours. This is mainly do to changing the recovery to bulk copy and using a select into (non logged) then creating the index on this new table. It also has the advantage of compressing and reorganizing the table, defragging at a page level.

Thanks again.

Doug

Stage 1: Initial cut over

Run DBCC INDEXDEFRAG to clean up and compress the tables

Run DBCC SHRINKDB to shrink the db

Run a full backup

Detach the database from the original server

Copy the mdf file to the new server

Run sp_attach_single_file_db specifying only the mdf file. This will create the log file which allowed me to skip the log file copy.

Run sp_change_users_login to fix the orphaned users.

Stage 2: Add the logging fields and indecies.

Run a full backup

Change the database recovery to bulk logged

For the majority of the tables simply use a alter table statement to add the new fields and create index to add the new index

For the 14 tables that make up the bulk of the database create an SELECT INTO statement to load a new table

As part of the SELECT INTO add CAST(GETDATE() AS smalldatetime) AS UpdateDT to the select list. This adds the new fields while creating the copy table.

Recreate the primary key on the new table

Add an index on the new field

Add any indecies, constraints and foreign keys on the new table

Rename the original table and its primary and foreign keys

Rename the new table and its prmimary and foreign keys to the original table names

Rebuild any foreign keys that reference the original table to point to the new table

Run a second full backup