Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should guarantee that they are compilable and executable.

There are many stored procedures and triggers to check. To do it manually, I would need maybe 1 month. So, I want to take another approach.

What kind of checking procedure would you recommend me, so that I can say surely that the stored procedures and triggers will also work in SQL Server 2008?

Thanks

share|improve this question
add comment

migrated from stackoverflow.com Nov 28 '12 at 12:37

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote accepted

Generally speaking, if you just reapply of the stored procedures and no errors show up, you should be good to go (based on my experience from migrating 2 databases (2000->2005) (2005->2008). On a side note, this is why we started writing unit tests for our database code to handle problems like these.

share|improve this answer
    
Do you mean that applying the sps and getting no syntax errors would also mean that the execution will be ok? (All the original SPs are assumed to be working fine in sql2005) I somehow need to write a report as a conclusion. Do you have any sources that I can reference to in that report? –  EngelbertCoder Nov 28 '12 at 10:20
    
@EngelbertCoder Yeah, that is exactly what I mean. It's not fool proof but I do not think a fool proof solution exists. Additionally, it's worth noting that if your just upgrade the server from 2005 to 2008, the database doesn't upgrade with it. That requires you to change the compatibility mode on the database itself by going to it's properties. As for the sources, this all based on my personal experience. Sorry I don't have a more concrete answer. –  mikerobertking Nov 28 '12 at 10:33
    
The 'compatibility level' confuses me a bit. So, if the compatibility level on SQLSERVER2008 DBs are 'SQL Server 2005 (90)', does it mean that 2008 should be compatible with 2005 but we will not be able to use the new features introduced by 2008Server? –  EngelbertCoder Nov 28 '12 at 11:32
    
Are you really suggesting that as long as your code compiles it will absolutely work fine when executed? –  Aaron Bertrand Nov 28 '12 at 14:56
add comment

Is the 2005 instance still your production instance? If so, you could use a server side trace to capture a workload and replay it on the 2008 instance. You should try to capture your normal workloads. You have to decided what is normal in your system. This wouldn't guarantee that all stored procs will work, but you day to day activity should be fine.

As far as compatibility levels please review the BOL link: http://msdn.microsoft.com/en-us/library/bb510680.aspx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.