File versioning is another thing that I assume most people understand
but I still run into a lot of confusion about.
This came up in one of the 24 Hours of PASS sessions I watched, The question was something like “does the
format of the data in a SQL Server file change when you do an upgrade or does
SQL Server understand both the new and old format”?
While there might be exceptions I’m not aware of, there is
only one file format that a particular version of SQL Server understands. This means that when you upgrade a SQL Server
file – upgrade in place by installing a new version, restore to a new version,
attach to a newer version – the format of the data in the file is changed to
match the new SQL Server version. This
is enforced to avoid implementing code that behaves differently depending on
the version that a page of data was created on.
If the data in the file wasn’t upgraded when the database engine is
upgraded then SQL Server 2012 would have to be able to handle at least 5 different
page formats – SQL 7, SQL 2000, SQL 2005, SQL 2008, and SQL 2012. This would make the code very fragile and
difficult to test. You would also see
bugs that would depend on which version of the database a page was originally
written by. You could also run into
issues where performance was different in SQL 2012 for a database created in
SQL 2000 and a database created in SQL 2012.
So for all these reasons, not changing the format of the data files when
SQL Server is upgrade would be a very bad thing.
This leads to several behaviors we’re all familiar with:
·
You can restore a backup made on an older
version to a newer version of SQL Server but you can’t restore a newer backup
to an older version. SQL Server knows
how to convert a page from the older format to the newer format but not the
opposite. Note that the compatibility
level of a database does NOT affect the format of the database pages. All database pages in a SQL Server 2012 instance
use the 2012 format.
·
Log shipping and Database Mirroring can transfer
data from an older database version to a newer version but not the other way
round. Again, the page upgrade is a one
way transformation.
·
You can attach a SQL 2008 database to a SQL 2012
instance but once you do, you can’t go back.
·
You can only upgrade a database by two versions –
2005 to 2012 works but 2000 to 2012 doesn’t.
This was a choice Microsoft made to limit the number of upgrade routines
they have to develop, maintain, and test.
Isn’t this pretty arbitrary?
Sure, but think about whether you would want the SQL Dev and Test teams
working on the upgrade from SQL 7 to SQL 2014 or working on new features for
SQL 2014.
The one-way upgrade is the reason rollback planning is such
an important part of upgrade planning.
If you attach your database files to an instance of a newer version and
something happens, you can’t go back just by attaching the files to the old
level or backing up the new level database and restoring it to the old level. You also can’t use log shipping or mirroring
from the new instance to keep the databases in the older instance current.
No comments:
Post a Comment