CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008migration
Danthar
Danthar

Reputation: 1075

SQL Server migrating database performance what is better

Some background:

A customer has asked an Certified SQL Server Consultant for his opinion on migrating from sql server 2005 to sql server 2008.

One of his most important recommendations was not to use backup/restore but instead use the migration wizard to copy all the data into a new database.

He said that this would ensure that the inner structure of the database would be in an SQL 2008 format, and would ultimately result in better performance.

The Customer is skeptical about this because they cant find any writing, in white papers or otherwise to corroborate the consultants statement.

So they posed me this question:

Given an SQL Database, which originally started out on SQL Server 2000, and has migrated to newer versions of SQL Server using backup/restore. (and finally being on SQL Server 2005)

Would migrating to SQL Server 2008 using the Migration Wizard, and in effect copying all the raw data into a new database, result in better performance characteristics. Then if they would be using the Backup/Restore method again?

Upvotes: 2

Views: 965

Answers (2)

sensware
sensware

Reputation: 21

I agree with Denny. Backup/restore is the easiest way to upgrade. For no downtime upgrade you can use database mirorring to new server and fail over to new version

One important task that improves performance is refreshing all statistics when you upgrade to a new version

Upvotes: 0

mrdenny
mrdenny

Reputation: 5078

I'll repeat what I posted on Twitter, "your consultant is an idiot".

Doing a backup and restore will be much easier, and require a much shorter downtime. Also it will ensure that the data is consistent and that no objects are missed.

So long as you are doing index maintenance (rebuilding or reorging/defragging indexes) then any page splits which have happened are fixed and there will be no performance problems.

When the database is moved from one version to another the physical database file is updated to the new version. You'll notice when you restore the database that the compatibility level is set to the old version's number. This has nothing to do with the physical structure of the database file. You can change the compatibility level at any time to a lower or higher version. You can see this if you restore the database using T-SQL as after the database is restored you'll see the specific upgrade steps which are performed.

In response to qwerty13579's comment, when the indexes are rebuild the index is written to new physical database pages so exporting and importing the data in a SQL Server database isn't needed.

For the record, the migration wizard is about the worst possible option for moving data from database to database.

Upvotes: 12

Related Questions