December 5, 2011 at 8:14 am
Here's the background:
We recently upgraded our DB Servers to SQL 2008 R2, HOWEVER... The COMPATIBILITY_LEVEL = 80 (SQL 2000) on all of the DBs. As you know, this is analogous to driving a Bugatti Veyron, with your lawnmower engine under the hood! It severely restricts use of all of the new, cool features in 2008 R2! (MERGE, PIVOT, et al) But the second we flip the switch and set COMPATIBILITY_LEVEL = {90|100} (2005 or 2008) the phone starts ringing with application errors from users, and the Director comes storming into my office shouting "EVERYTHING'S BROKEN!!!" or "THE SKY IS FALLING!!!" or other such nonsense because many of our stored procedures fail at various points in their execution. (GROUP BY errors, etc.)
I ran the SQL Upgrade Advisor, and it tells me that everything is ok, which I know to be false. Is there a tool, or something that I could run to check our 2800+ tables, and 3500+ stored procedures to find what needs to be changed in them, divvy up the changes between our developers and be able to finally run SQL 2008 AS SQL 2008 instead of 2000?
I have searched far and wide for a solution to this for several weeks now. Any help or guidance would be greatly appreciated.
KK
December 5, 2011 at 8:31 am
Are you doing it on PROD? It's not recommended.
Identify a test server and copy sample data (FULL is always better) on it. Create a test instance of your application & point to this test database. Change the Server settings and observe the errors.
Take corresponding actions on the errors.
THE SKY IS FALLING because you broke it (by doing configuration changes in PROD server). You are risking your job as well.
December 5, 2011 at 8:49 am
I did not create the "FALLING SKY" LOL, and "Chicken Little" (aka the person who created this nightmare) already lost their job! I am here to try to rectify it.
So basically, what you are telling me is that there is nothing, to your knowledge, that will automate this process, correct? We can't really pull everybody off to do "pretend work" in a test environment while we observe and document all the errors. This is a system wide issue, affecting all of our web and desktop applications! I personally could never test every app and website in our vast catalog to ensure that I got every error. If I could fix maybe 60% of the potential errors up front, and the rest as they were observed, that would probably be acceptable to management in the interim.
I considered creating a query to find specific text in the stored procedures that we know to be erroneous, but that too seems insurmountable to me without a bit of guidance. I've read the BOL on the changes in 2008 R2 and have a pretty good understanding of what is causing the majority of these errors. Now I just need an easier way to ferret them out and fix them.
Thanks for the quick response!
Happy Holidays!
KK
December 5, 2011 at 9:01 am
So basically, what you are telling me is that there is nothing, to your knowledge, that will automate this process, correct?
I am not sure. There are few guys who use third party tools excessively, they might guide you better. I never get any liberty to buy one. 🙁
We can't really pull everybody off to do "pretend work" in a test environment while we observe and document all the errors. This is a system wide issue, affecting all of our web and desktop applications! I personally could never test every app and website in our vast catalog to ensure that I got every error. If I could fix maybe 60% of the potential errors up front, and the rest as they were observed, that would probably be acceptable to management in the interim.
Don't you have your System Baseline ready (usually done in Performance Testing e.g. Load Runner)? You just need to run it on your test instance. It will log all errors for better analysis.
I considered creating a query to find specific text in the stored procedures that we know to be erroneous, but that too seems insurmountable to me without a bit of guidance. I've read the BOL on the changes in 2008 R2 and have a pretty good understanding of what is causing the majority of these errors.
That would be my last suggestion (and the only option available to me)
December 5, 2011 at 11:24 am
krushkoder (12/5/2011)
We can't really pull everybody off to do "pretend work" in a test environment while we observe and document all the errors.
I'm sorry, but this is quite a silly statement. When you upgrade versions you have to test before doing so in production. We don't just upgrade production and fix what stops working. It is not "pretend work," it is real work, the kind that a good DBA does.
Jared
Jared
CE - Microsoft
December 5, 2011 at 11:32 am
krushkoder (12/5/2011)
So basically, what you are telling me is that there is nothing, to your knowledge, that will automate this process, correct?
There are automated test suites around, they're not cheap and they're not miracle tools, someone has to configure the tests
We can't really pull everybody off to do "pretend work" in a test environment while we observe and document all the errors. This is a system wide issue, affecting all of our web and desktop applications!
What about your testing staff? You know, the people that test changes and new development?
If you don't have testing staff, then you're probably better off leaving the DB in compat mode 80 for the time being and investigating either buying a testing package or setting up comprehensive tests (have a look at SQLTest from RedGate) or testing the app bit by bit. There's no miracle tool here that can run every part of a system without any work needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2011 at 12:57 pm
if an application is building SQL statements and executing them, then your database and it's objects could be version 9/10 compatible, but the commands create by the application may not be...that's where the real issue exists, i'm thinking.
do you have access to the source code of the application that causes the sky is falling scenarios?
some simple things like search for old style joins (find "*=" or "=*") would get things rolling in the right direction.
there's an easy to find list of the breaking syntax issues,a nd doing the search of the source code and recompiling with the updated syntaxes would be where i'd start.
(oh and i would be testing this on anything that;s not production, as stated before)
also, do your procs feature a lot of dynamic sql, and not normal sql statements? that's another place Version 80 code could be hiding fromt eh upgrade advisor.
Lowell
December 5, 2011 at 1:47 pm
Personally I would leave the compatibility level at 80 for now and like other people have mentioned in this thread, create a test environment and then thou rally test your upgrade, surely the person who broke the sky had a implementation plan RFC?.
With the DB set to a compatibility level of 80 you may not be able to use some of the other cool functionality like pivot tables but, there are ways to code round that .
Also if I was upgrading my compatibility level id make sure to set the DB to single user prior and then update DB stats etc before allowing access.
***The first step is always the hardest *******
December 5, 2011 at 2:01 pm
When you switch to the engine level compatibility (100):
- did you update all statistics
- did you perform dbcc updateusage
- did you perform full db maintenance
- did you run DBCC CHECKDB (n) WITH ALL_ERRORMSGS, DATA_PURITY;
- did you clear the proccache for that db (maybe that is done automatically, I always perform it manually)
- What exact error is it producing (except for slow stuff or stuff hitting timeouts)
- what was the @@version info of the sql2000 you upgraded ?
- does the engine report missing indexes or statistics for that db and did you investigate their relevance ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 5, 2011 at 2:09 pm
ALZDBA (12/5/2011)
When you switch to the engine level compatibility (100):- did you update all statistics
- did you perform dbcc updateusage
- did you perform full db maintenance
- did you run DBCC CHECKDB (n) WITH ALL_ERRORMSGS, DATA_PURITY;
- did you clear the proccache for that db (maybe that is done automatically, I always perform it manually)
Those need to be done when upgrading the server from 2000 to 2008, not when changing the compatibility level.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2011 at 4:56 am
GilaMonster (12/5/2011)
ALZDBA (12/5/2011)
When you switch to the engine level compatibility (100):- did you update all statistics
- did you perform dbcc updateusage
- did you perform full db maintenance
- did you run DBCC CHECKDB (n) WITH ALL_ERRORMSGS, DATA_PURITY;
- did you clear the proccache for that db (maybe that is done automatically, I always perform it manually)
Those need to be done when upgrading the server from 2000 to 2008, not when changing the compatibility level.
Indeed .... but often forgotten.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 6, 2011 at 8:01 am
Thank you all for your insightful feedback! Based on this, as well as our internal discussions, we have concluded that the best way to "eat this elephant" is to put our test environment into 2008 compatibility level and every time something breaks in SQL when we are implementing new changes and testing, we will document the issue, and fix it at the same time. Once we get to a point that we feel that most of the issues have been fixed, we will migrate all the new procs over to PROD and toggle 2008 level there. Also we have decided to evaluate the RedGate product SQLTest since we already use and get good value from another of their products.
Again, thank you all for your input. This continues to be one of the best SQL Server Forums on the net!
Happy Holidays to All!
KK
December 6, 2011 at 8:20 am
ALZDBA (12/5/2011)
When you switch to the engine level compatibility (100):- did you update all statistics
- did you perform dbcc updateusage
- did you perform full db maintenance
- did you run DBCC CHECKDB (n) WITH ALL_ERRORMSGS, DATA_PURITY;
- did you clear the proccache for that db (maybe that is done automatically, I always perform it manually)
- What exact error is it producing (except for slow stuff or stuff hitting timeouts)
- what was the @@version info of the sql2000 you upgraded ?
- does the engine report missing indexes or statistics for that db and did you investigate their relevance ?
script monkey that I am, I decided to build a script that did all the items you identified...just for a bit of clarification, when you say "- did you perform full db maintenance ", what are you refering to there? index rebuilds? backups?
Lowell
December 7, 2011 at 7:20 am
Lowell (12/6/2011)
ALZDBA (12/5/2011)
When you switch to the engine level compatibility (100):- did you update all statistics
- did you perform dbcc updateusage
- did you perform full db maintenance
- did you run DBCC CHECKDB (n) WITH ALL_ERRORMSGS, DATA_PURITY;
- did you clear the proccache for that db (maybe that is done automatically, I always perform it manually)
- What exact error is it producing (except for slow stuff or stuff hitting timeouts)
- what was the @@version info of the sql2000 you upgraded ?
- does the engine report missing indexes or statistics for that db and did you investigate their relevance ?
script monkey that I am, I decided to build a script that did all the items you identified...just for a bit of clarification, when you say "- did you perform full db maintenance ", what are you refering to there? index rebuilds? backups?
Indeed, rebuild every index !
In some cases I evaluate the auto created statistics (_WA_Sys*) with regards if an index would make sense (during migration planning time).
In many cases I drop these auto created statistics, although some may argue this attitude.
Of course Backups should always be part of any db implementation/migration sequence. :satisfied:
Every db on every instance should have a DRP. (formal or not)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 7, 2011 at 12:53 pm
I've been running a trace for the deprecated announcement events.
http://msdn.microsoft.com/en-us/library/ms186302.aspx
Something else I did for the older 80 compatibility databases was trace T-SQL for signs of non-ansi joins like *= and =*. That usually turned up plenty of code to be turned up.
But even those two methods won't find all the things that break from 80 to 90 (or 100). I've seen some older datetime() usage that broke upon upgrading. I'm betting there are a lot more coding tidbits that were deprecated in 2005 that I haven't run into yet.
Bumping up your test environment and developing off that for a while is probably a great step as long as you are careful not to use any new functionality that won't be available in production yet.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply