SQL Server Performance Forum – Threads Archive
SQL 2005 dbcompatibility level
Hi all,I have just upgraded my test server to SQL Server 2005 Dev. Edition. Everything went fine. But I was surprised to see that master database is stil compatibility level 80, the other system databases 90. I knew that all my user db level will be 80. I need to make some changes to get some stored proc code work (there are some older style joins used and some column aliases needs to be changed) once I make the changes I will change the compatiblity level to 90. My question is should change the master db compatibility level now or wait till all my user db compatibility level is set to 90?
Few things fyi:
-New features may work under older compatibility levels but beware of SET options.
-An upgraded database retains its compatibility level. So in this case whatever changes you are willing to perform wait until they are finished and then change it to 90 for complete utilisation of features. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I would change the master db to 90 straight away. I can see no reason to leave it on 80.
If you have user procedures or user extended procedure in master database then you may need to worry otherwise no reason to keep in 80 mode… Did you run the upgrade advisor before upgrade? If there is an option of side by side upgrade, better to do it…otherwise use inplace upgrade…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Mohammed I did run the upgrade advisor and made sure that the items were addressed prior to upgrade. I did in place upgrade. I have xp_smtp_sendmail proc that It needed to have full path for registration which I did that prior to upgrade. Production SQL server is back end to a single website(coldfusion) that reads/writes to 15 different databases(there are procs in each database that reads/writes data from other databases) so I we thought it simply is less work to do inplace upgrade. The current server I did the upgrade is a virtual server that we tried to duplicate as our prod.
Anything else I should be aware. Thanks everyone for all the input.
See I have seen similar problem in working with a legacy application that has been designed to work until SQL 2k and for the few workouts on the basis of system objects in master database we have kept it in 80 until we have managed to rewritten the required using .NET. Keeping my experience in mind I have suggsted to keep it 80 until you have managed to upgrade your components. Anyway have to attempted to use the CF application in connecting with 90 compatibility? fyi, Log in to your ColdFusion Administrator and create a new datasource with the same name as the database you want to connect to. Make the datasource type ‘other#%92. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Only problem with inplace upgrade is, if anything happens middle of the restore you can’t recover back or you can upgrade it again….
You have to use backup to restore the databases…
In case if you want to rollback to sql 2000 you need to restore them from the backup…
Also side by side upgrade minimize the down time… You don’t need to use xp_smtp_sendmail procedure anymore because sql 2005 introduced dbmail which uses SMTP mail and SQL mail deprecated…
With xp_smtp_sendmail you can’t configure the sql agent alerts and job notifications where dbmail does…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>