File Format Versioning

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.