How To Assess Impact Of Changing Database Compatibility Level
Aug 28, 2006
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
I have a problem where I have a software application that needed to be updated to a newer version. In order for the new version to work I needed to upgrade my SQL Server 7 database to SQL Server 2000. The upgrade went fine for SQL Server 2000 and also with the application. The only problem now is that the compatibility level for the databases stays at 70. Even when I change the compatibility level to 80 it will automatically go back to 80. What is wrong? Please help.
Dear All, what are the advantages of changing compatibility level from 80 to 90? are there any disadvantages doing this on production machine? will it take any downtime? i've searched the google but i didnt get the correct info.
Arnav Even you learn 1%, Learn it with 100% confidence.
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
Hi! After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65". I checked that by executing sp_dbcmptlevel <database_name>. I can change it to "70" but my question is how it's going to affect the application and do I have to change it?
When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
We have a reasonably large (several TB) database that was recently migrated from 2008 to a new box running 2014. Before giving it back to the users we forgot to change the compatibility level of the DB to bring it up to date with it's new environment.
We want to do some testing with backup compression so want to change the compatibility level, but we are unsure whether making the change on such a large database would cause slowness or downtime for our users.
Does the process of changing the compatibility level simply allow options that are not available in the older version or does it make structural changes to the database that would cause the users to notice slowness or downtime?
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
I would like to know if I put the Compatibility Level in a SQL Server 2005 installation to 70 I can make afirmation that I have a full SQL Server 7.0. If the answer is "Yes" where I can find a documentation or a FAQ that explained this topic.
I restored the database from SQL server 2000 to 2005.The database was restored with 80 compatibility.Can i change it to 90 and what are the effects? Coz my applications are pulling data from SQL server 2000. Does 80 work for database mirroring?
We found interesting issue which is basically the app is being tested ok on SQL2005 by software vendor. Then we tested in our environment and we found it's not truly true. There are some compatibility issue on SQL syntax. Anyway ... the plan set the compatibility level back to 80 instead 90. Cause this thing for sure is working.
Now my question is do you know any other impacts that you know of if we are doing this setting (running SQLServer 2005 but the database set as 80)? I know that some inbuilt reporting only run 90 level but I can get around this. Performance or something? Is there any thing that I should to know?
Having moved over to SQL 2k5, from SQL 7.0 we have now realised that the database's need to be set to comp level 9.0 before they are found in the maintence plan wizard, we currently still access the database using an Access 2000 front end, by changing the comp level will this cause us issues writing data, I'm sure it won't but want to make sure, I'm sure that the comp level just sets what options are available to use.
We upgraded from SQL 6.5 to 2000 (.384) and just look at this T-SQL and maybe somebody can tell me what's wrong.
sp_dbcmptlevel appetserv1,80
update articleentrepot set usagermodification = 'MANON' where idarticle = 1
Here is the result: Cannot use the column prefix 'E'. This must match the object in the UPDATE clause 'ArticleEntrepot'.
When I put the compatibility level to 65, it's works. But my problem is that I need to have the compatibility level to 80 because of the DataMirror replication software and if I do that, my house application doesn't work. I really don't know what to do, I'm in a deadlock. Can you help me! Manon Tremblay
I installed SQL 7.0 on a test server a few days ago. I want to test an application that currently runs on SQL 6.5. I have run a test database through the upgrade wizard without errors. Is there any way to see the compatibility level that the db is running on. I know I can use the sp_dbcmptlevel procedure to make it run at the 6.5 level. I want to see if it is running at that level currently, or if it is at the 7.0 level.
Problem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS ( SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase' ) BEGIN ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110 END
Does anyone have any insight as to whether the new data encryption features of SQL Server 2005 work when the database is set to compatibility level 80?
I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL?? any help is greatly appreciated.
I'd heard that upgrading SQL 05 to SP2 forced any databases at SQL 2000 compatibility (80) to SQL 2005 compatibility, but I can't find any documentation that says one way or the other.
Can anyone provide insight as to whether or not this is true?
I can set the compatibility level by using :sp_dbcmptlevel But before doing this - How can i view my current DB's compatibility level? Any help appreciated
Hi All, We were upgraded our databases from 6.5 to 7.0 .After upgrading the Compatibility level was 65 .We changed to 70 for all databases using sp_dbcmptlevel system stored proc.We were having some problems with Ansi nulls if the compatablity level is 70,We changed back to 65 that works fine--OK with ANSI NULLS.While Upgading ANSI NULLS OFF after upgrading ANSI NULLS OFF.But from the front end side ODBC settings ANSI NULLS are ON.Is that some thing to do with compatablity level or with ANSI NULLS . If you guys have any great ideas Please let me know.
I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work
This may be very obvious and when I get to the first test server I will find out, but I only have a new installation of SQL2K5 so far to test with. I have several databases that need to be upgraded to run on 2K5, but the application requires them to run in 8.0 compatibility. Does this mean that an in-place upgrade is not an option?
I apologize if this is a silly question, but I have not yet found the answer specifically.
We have upgraded our database from SQL 2000 to SQL 2005 keeping the compatibility level 80. Now the question is - Would microsoft support compatibility level 80 in SQL Server 2005 even after the extended support date for SQL Server 2000?
OR
If Microsoft dropped the support for SQL 2000, then would Microsoft support compatibility level 80 in SQL Server 2005?
We have recently migrated our DDBB from SQL 2000 to SQL 2005 in several Servers. We have 2 DDBB per Server and the size of mdf files are between 10 and 40 GB.
We put Compatibility Level in 90 in SQL 2005 but when we arrive at work we see that our Maintenace Plans failed because the Compatibility Level of one of Data Bases changed to 70.
We have a Trace executing the whole day registrying the execution of stored procedure 'sp_dbcmptlevel' but in despite of Compatibility Level changes, the Trace does not registry anything.
Has anyone passed before me for this situation? Thank you in advance and greetings,
Hello All, I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.
The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features. From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing. Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine. One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported
It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.
I cannot find this info anywhere. We have Hyperion 8.x running against SQL 2000. We will upgrade side-by-side in the coming weeks. It appears that Hyperion 8.x is assured to continue to work without issue with a 2K5 database in 80 compatibility level.
I have seen the BOL table on differences between 80 and 90 (specifically affects us - WITH on Index hints), but what I wonder are about 3 specific features.
1. Do you still get DMV capabilities in 80 mode? (I ran a test on sys.dm_db_index_usage_stats for an 80 compat DB, creating index and using query to hit index, and yes - it does look as if this DMV does get populated).
2. Can you still Table Partition in 80 mode? (Ran a test in DEV, and yes - this does look possible - any gotchas?)
3. Does the optimizer behave quite differently in 80 mode?
I have tested in DEV moving a DB from 80 to 90 and back, and this seems to work with no problems.
I have a 2005 database sitting on a 2012 server, we're looking to change its compatibility mode from 90 to 110 so we can avail of what 2012 offers.I did a migration project a couple years ago for SQL Server 2000 databases changing to 2008 R2 and we ran into loads of problems where we left most databases in compatibility mode 80 as these were application databases that the owners couldn't stand over in terms of deprecated code / features no longer in use in 2008 R2.
From what I can see with changing from 90 to 110, there doesn't seem to be as many issues but I just want to double check if there's a way to confirm this. I know upgrade advisor is a handy tool but will it pick up database specific issues as opposed to database server compatibility issues? URLs...I know UA won't cover all the bases but it would look good when attached to the report I'm submitting recommending the change.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
