June 6, 2013 at 10:59 pm
Hi guys,
I was just wondering if it is safe to permanently run a production database in a newer version of SQL Server without changing the compatibility level of the database (say a SQL 2005 Database in SQL 2008R2 or SQL 2012)?
We would like to spend a minimal amount of time and currently don't have the time nor the capacity to work on update stored procedures etc to work with the latest versions
We have a few production servers running different versions from SQL 2005 / SQL 2008 R2, Instead of installing multiple instances on the same machine or having seperate machines for each version of SQL Server, I was just wondering if this was a safe approach.
Some of the databases are from 3rd party software products.
June 6, 2013 at 11:41 pm
Sure, no problem.
"Under the hood", your database is already upgraded - that happens automatically the first time you bring it online. Leaving it in a compatibility level lower than your current SQL Server version simply means that it's still presented as an older version. It's perfectly safe and acceptable to do this, but some newer functionality and reports will not be available for that database.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
June 7, 2013 at 2:21 am
You should plan to test and do this at some point though. If you plan on upgrading again in the future that compatibility mode may not allow you to until it is upgraded to a supported mode.
Joie Andrew
"Since 1982"
June 7, 2013 at 4:42 am
True.
You do need to be at a supported level, ie. if not at the level of the current SQL Server version, then one of the two previous versions before that. For example: SQL Server 2008 supports compatibility level 90 (2005) and 80 (2000) in addition to its own "native" level 100.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
June 7, 2013 at 10:48 am
First, contact the 3rd party vendors and ask if the versions of the app you're using supports 2008R2 or 2012.
Next, run the Upgrade Advisor for the SQL version you'd like to upgrade to on the homegrown databases and see if any issues are reported.
If all looks well there, install a test environment for all the apps on the newer SQL version, set the higher compatibility level and have a group of users run through tasks they do.
June 7, 2013 at 11:33 pm
Thank you all for your feedback, Your responses have made things clearer. Where possible I will upgrade the database compatibility in the future, in the short term I just need to consolidate servers.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply