Thursday, September 13, 2007

SQL Server 2005: Migration aftermath

I guess we can safely say that the migration was a success. It was quite a flat migration since we needed to guarantee a "backout" to SQL Server 2000, the only changes that were done is the replacement of SCSIPort with StorPort drivers (this was the only server left to migrate) and we decided to change the physical implementation of the database.

A couple of preliminary results since replication is still running and it obviously has impact on performance:

Long running transactions:
Before: 70.000/day > 100 msec - 1.000/day > 1000 msec
After: 14.000/day > 100 msec - 650/day > 1000 msec

Long running CUD transactions:
Before: 40.000/day > 100 msec - 250/day > 1000 msec
After: 4.000/day > 100 msec - 100/day > 1000 msec

Avg Disk Secs/Read:
Before: 12 msec
After: 9 msec

Avg Disk Secs/Write:
Before: 15 msec
After: 9 msec

Job duration dropped with over 50%.

Now it is time to start implementing new features like partitioning, service broker, vardecimal and hopefully many more. We have a lot of ideas floating around in our heads but unfortunately there are a couple of other priorities to take care of first Since we were no longer happy with our existing reindexing job we did decide to rewrite it from scratch and this gave us the opportunity to implement online reindexing.

I would like to thank all of my colleagues and the people from Microsoft who made this migration possible. I also apologize to all the colleagues I had to send away from my desk during the migration preparation ;-)