SQL Server: Migrating from 2000 EE X86 to 2008 R2 SE X64

Today we had a production migration and brought over one of our legacy applications from a data center located up north to one located in South Florida.

The challenges:

1. The locations are disconnected and far from eachother
2. We are going up 3 releases. From 2000 to 2008 R2 (8.0 to 10.5)
3. We are Downgrading from Enterprise Edition to Standard Edition
4. We are going from a 32-bit architecture server to a 64-bit one (X86 to X64)

The solution. We separated this into 2 pieces. A pre-migration and production migration process.

Pre-Migration Process:

First, ensure that there are no enterprise edition features used. We were lucky this time as there were really none in the source database. But as an example, we got another server running 2005 EE which will be migrated to 2008 R2 SE and had partitioned tables. That is something to be taken care of before migration.

Second, ensure that the database has no corruption. A DBCC CheckDB needed to be performed on the source before backing it up.

Third, prepare for a test with a latest full backup. We FTP’d over the last full backup during a weekend and restored onto the target server running 2008 R2. We gzipped the files before transferring, but it was painfully slow still.

Fourth, restore and ensure that the databases open with no issues whatsoever. Update statistics and reindex any important table that is really defragmented. This is a challenge in Standard Edition as reindexing cannot be performed online.

Fifth, test and retest. This requires a lot of help from the Application Developers and QA Team. Ensure that functionality is intact and most importantly, that the database is always in a healthy condition (no excessive I/O reads, no high CPU utilization, etc).

Sixth, let the QA team sign-off.

Production Migration Process:

First. Plan the work. Create checklists with detailed steps to be performed before, during and after migration.

Second. Perform a full backup on the source server. We chose Saturday night. Disable the full backup job on the source server. Ensure that transaction log backups are still running at the source.

Third. Compress the backups, FTP to Florida, decompress and start restoring onto the target server.

Fourth. Bring over the transaction logs and continuously apply to the target server.

Fifth. In our environment we were able to disable outbound services and show a maintenance page on the web apps. This way the source database was accepting data but not processing it.

Sixth. Take a last transaction log backup, apply it to the target and open the databases.

Seventh. Update statistics, reindex for required objects and perform sanity check tests.

Eighth. Let the Infrastructure team make appropiate changes (like DNS) and enable services.

Ninth. Sync up any missing data. In our scenario, we were able to code an application that extracted missing transactions (just a few thousand) from the source server and apply to the target server. Let QA Test and Sign-Off.

Tenth. Perform a full backup and enable all maintenance jobs with appropriate schedules.


Each migration will have its own steps and some will be more difficult than others. I have specified very high level bullet points of what needed to happen for this particular migration.

What if the unplanned happens:

First, every checklist should have a rollback plan. In our case, we had steps to follow in case we needed to stop the migration. On the DB side was pretty simple as we just stop restoring onto the target server, go back to the source and enable maintenance jobs. Of course there are other steps that needed to be followed by development and infrastructure members.

But today we had an interesting scenario. While recreating indexes on a 25 Million row table, the DBA stopped the SQL Agent, which was part of the process outlined on the checklist. The problem, it was stopped through the Services Panel instead of the SQL Configuration Manager. So what happened? The Clustered service freaked out and decided to perform a fail-over.

The instance started but the database status was “in recovery”. As per the SQL Log, it was going to take like 2 hours to rollback all the changes. We had 2 options; 1 to just execute the rollback plan, or 2 drop the database and restore it once more.

We decided to execute option 2 as the transaction logs were small. The bulk was restoring the full backup, but fortunately it was done in under 30 minutes; not bad for a 120 GB database.

Now how did we drop the “in recovery” database? We opened the SQL Configuration Manager, offlined the database instance, deleted the datafiles from the “in recovery” database and re-started the instance. Of course the SQL log was complaining about missing files, so we went ahead and dropped the database.

We are now live on a new 2008 R2 instance, and are current with backups. Interesting to see that with database compression a 120 GB database backup uses just 25 GB of disk space.

This was our last SQL Server 2000 instance.

SQL Server: Can I upgrade to 2008 R2 from 2008, 2005 or 2000? #TSQL2sDay

It is known that you can go up 2 major releases. Let’s consider 2008 R2 as a “half release” as we can “floor” 10.5 to 10. That is because we can go from 8.0 (2000) to 10.5 (2008 R2). Microsoft is smart as it is allowing several costumers running SQL Server 2000 to upgrade to 2008 R2.

But there is a caveat, you cannot upgrade from SQL Server 2008 SP2 to 2008 R2 RTM if you have the option to support 15,000 partitions enabled on 2008 SP2. So you are pretty much stuck until Microsoft releases a CU or SP to address it in R2.

Make sure you test before upgrading a production environment.

In my DJ Days

This is a recording I performed back in early 2002 when I use to DJ as a hobby.

Style is Progressive House.


Sphere And Accorsi – Set The Mood
Jas – Nasty – Cure Recordings
Sleepfreaks – Chemical Shift (Substructure)
Pako & Frederik – Beatus Possessor (Original)
Medway vs Pete Gawtry – Geno Sequence
Mo Shic & Zidan – Nightstalker (Remix)
Teimoso Feat Shelly Preston – Riding
16B Feat Morel – Escape (Omid’s Instrumental)
Two Right Wrongans – Sorry Mate (Steve Porter`s Back On Track Mix)
circulation presents abstract funk theory – subtech – coast
orbital – Illuminate charlie may
Chonga – The Quantum Gospel
Why R U Here – Lexicon Ave
Roland Klinkenberg – Cellophane

Donwload Link


Participate in #TSQL2sDay

Sankar Reddy has blogged about what will be discussed as next topic: Misconceptions in SQL Server.

What is SQL Tuesday? As he explains, it is the opportunity for the community to improve based on a topic chosen about SQL Server. It happens every 2nd Tuesday of each month; an original idea from Adam Machanic.

These are the rules:

1. Your post should go live between 10/12/2010 00:00:00.000 GMT (2nd Tuesday) and 10/13/2010 00:00:00.000 GMT.
2. Your blog post has to link back to the hosting blog, and the link must be anchored from the logo (found above) which must also appear at the top of the post.
3. If trackbacks won’t work properly then please leave a comment below so that your post is accounted for in the roundup.

Get involved.

SQL Trace causing error “The client was unable to reuse a session with SPID XXX”

We will open traces whenever a slow performing process needs to be pinpointed. We never experienced a high overhead until today.

A couple of our production sites and services started to time out and SQL Server started login this errror:

From: SQLAlerts [mailto:[email protected]]
Sent: Monday, October 04, 2010 10:13 AM
To: [email protected]
Subject: SQL Server Alert System: ‘Severity 020’ occurred on \\XYZ

DATE/TIME:        10/4/2010 10:12:41 AM

DESCRIPTION:   The client was unable to reuse a session with SPID 345, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

COMMENT:        (None)

JOB RUN:             (None)

We stopped the trace and everything went back to normal. The trace was being stored to a file.

Is the overhead that high to cause these kind of errors? We’ll try to figure it out.