March 3, 2012 at 1:40 pm
We want to move some databases from "Server A" running SQL Server 2000 to "Server B" running SQL Server 2008 R2. I know I can basically do a detach/attach, backup/restore, or a schema-rebuild/data-import.
My question is specifically targeted to the internals of the database files. Do we gain any internal file structure/page structure benefit by doing a schema-rebuild/data-import vs the other methods? I'm asking because I assume that the way sql server stores data internally with all of the various data pages and structures has probably changed since SQL Server 2000 and doing a "fresh" populate vis a schema-rebuild/data-import would use the newest structures for all the user data pages. Anyone know for sure one way or another?
March 3, 2012 at 3:47 pm
Kennedy.Parker (3/3/2012)
Do we gain any internal file structure/page structure benefit by doing a schema-rebuild/data-import vs the other methods?
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2012 at 3:01 am
Kennedy.Parker (3/3/2012)
We want to move some databases from "Server A" running SQL Server 2000 to "Server B" running SQL Server 2008 R2. I know I can basically do a detach/attach, backup/restore, or a schema-rebuild/data-
When moving from 2000 to 2005 onwards, You will want to run
DBCC UPDATEUSAGE
Followed by
DBCC CHECKDB WITH DATA_PURITY
It would also be a good idea to rebuild all your indexes or st the very least check your stats are up to date
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 4, 2012 at 4:52 pm
Gail, I appreciate the reply but "No" leaves me wondering...
"No" because there have been no advances in data page storage structures/inernals over the last 10 years (for the sql 2000 datatypes anyway)?
-Or -
"No" because there is some kind of conversion of data pages when attaching or restoring to a more current release?
-Or-
Am I missing something else?
p.s.
Thanks Perry, I'll look into it....
March 5, 2012 at 3:01 am
Kennedy.Parker (3/4/2012)
"No" because there is some kind of conversion of data pages when attaching or restoring to a more current release?
This
A database attached to a SQL 2008 instance is a SQL 2008 database in every way, regardless of how it got to the SQL 2008 instance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2012 at 4:59 am
Kennedy.Parker (3/4/2012)
"No" because there have been no advances in data page storage structures/inernals over the last 10 years (for the sql 2000 datatypes anyway)?
There have been changes in data storage structures within SQL Server, mainly from 2005 onwards. The new system catalogs
sys.allocation_units
sys.partitions
are detailed in Books Online. The data files still use the same 8kb page\64kb extent allocations but with allocation units the database engine can now handle larger data sizes such as VARCHAR(8000) and VARCHAR(max).
Kennedy.Parker (3/4/2012)
"No" because there is some kind of conversion of data pages when attaching or restoring to a more current release?
When you attach a database from a previous version of SQL Server to a higher version, a database internal structure upgrade will be performed
Kennedy.Parker (3/4/2012)
"Am I missing something else?"
Read Books Online, it's all detailed there 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply