| Author |
Topic |
|
srikanth12
Starting Member
22 Posts |
 Posted - 2010-02-14 : 20:22:32
|
| Hi All Experts, I am new to SQL Server and i got a situation here where i need to upgrade the server from 2000 to 2005. The sql server has 500GB size of database and it has around 200 stored procedures. Could any one of you please let me know how i can do this? I have already read all the pre_migration steps and post migration steps. I would really appreciate if any one can say the way to check whether all the SP's will be working fine in 2005.Thanks,Sri |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-14 : 20:25:12
|
| There are lots of posts regarding Migrations. You can search it and find here.Also if you are using *= or =* for Outer joins, then it won't work in 90 compatibility but works in 80 comp. |
 |
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-14 : 21:11:51
|
| Yeah!!! you are right...i see so many postings but i don't see any postings regarding the SP's in SQL Server 2000 and 2005. Could you please let me know if you have any idea about SP's in 2000 and 2005. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-15 : 01:09:56
|
| run the upgrade advisor against your databases.stored procs will upgrade fine unless you're using discontinued syntax (such as what sodeep pointed out). advisor should point out any SPs that wont uprade smoothly |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 02:18:09
|
| Agree about using Upgrade Advisor.Here is a post about upgrading to SQL 2008. There is an earlier post about upgrading to SQL 2005 (linked from this post), but the SQL 2008 post is more fully described, and most of it applies to upgrading to SQL 2005 too.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-15 : 22:57:20
|
| I already ran the upgrade advisor and it doesnt show any warnings regarding the SP. Is there any way that we can test the stored procedure with out directly upgrading. I am asking this because after the migration, if there are any issues w.r.t the database, i dont want to fix the SP at that time. Any kind of answers will be highly appreciated. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-15 : 23:32:19
|
| In general, it will be fine, until/unless you flip the compatibility mode.create a 2008 instance and restore a backup to it and start testing. |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-15 : 23:44:15
|
| So you say that i need to check all those 200 stored procedures to run manually in 2005. IS this correct? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 01:53:53
|
| "So you say that i need to check all those 200 stored procedures to run manually in 2005. IS this correct?"Absolutely! IMHO ...Otherwise stick with SQL 2000.You should have a regression test that you can run - so you can prove that any changes you make (whether changed to your application, or moving to a new version / platform) do not have adverse effects on your application.On that basis you could restore your SQL 2000 database on SQL 2005, change compatibility mode to 90, and run your regression test to prove it all works OK, then migrate the live system.Then you can take advantage of the new features in SQL 2005.Personally I would want to go straight to SQL 2008 at this point in time. Otherwise you are going to be doing the same migration again in a couple of years time ... whereas, right now, you have the chance to leapfrog a version. |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-16 : 23:00:48
|
| I think the regression test which you were talking about should work(TSQLUnit)..but the problem here is its going to kill the time since we have to check each and every SP manually:((is there anyway that we can check all the SP at a time?). Anyways what would be the best suggestion for a 500 GB DB Migration(IN-PLACE OR SIDE by SIDE)? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 02:51:02
|
| "but the problem here is its going to kill the time since we have to check each and every SP manually"If you HAVE a regression test already it will check ALL processes in the database / application - or am I missing something?If you don't have a test then you have to test it manually.Otherwise you just migrate then cross your fingers and hope - and probably get emergency requests to sort things out for days / weeks / months / years ... who knows how long the piece of string is?? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-17 : 08:10:27
|
| whenever possible, i'll choose side-by-side migration.gives you more time to deploy and test AND make for very easy rollback if need be. |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-17 : 20:53:14
|
quote:
Originally posted by Kristen
"but the problem here is its going to kill the time since we have to check each and every SP manually"If you HAVE a regression test already it will check ALL processes in the database / application - or am I missing something?If you don't have a test then you have to test it manually.Otherwise you just migrate then cross your fingers and hope - and probably get emergency requests to sort things out for days / weeks / months / years ... who knows how long the piece of string is??
Sorry for making this complicate...could you please let me know what regression test you are talking about? I read an article in internet regarding the regression tests....http://www.agiledata.org/essays/databaseTesting.htmlI see some unit testing tools like TSQLUNIT, SQLUNIT and some other stuff. So is this what you are talking about? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-17 : 23:08:32
|
| If the Upgrade Advisor doesn't show any issue with SP then you are fine for it. There is nothing to worried about. We have done migration from 2000 to 2005 for numerous servers. I would say just have 2005 QA Production Server and Test all your Products and Make sure it works and then flip it to Production Server with Migrations. Have QA involved for Testing for it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-18 : 03:19:52
|
| "Sorry for making this complicate...could you please let me know what regression test you are talking about"OK, in that case you don't have one.A regression test is something you build with the software so that you can run an automated test, at any time, to compare whether the output from your application provides "expected" answers.For example, we have a QA database for a web site. We use Ruby Watir scripts to run the Browser automatically - visiting specific URLs, filling in forms with specific values, and so on - and automatically taking a screen snapshot of each page.The snapshots are compared against known-good images (from an earlier run) and any that are different are listed for human review.We run this every time we make any changes to our system, and then have high degree of confidence that whatever we have changed has not changed the way the program operates.We also use Unit Tests - these are specific to each individual SProc / component of the system. We use these to test that each component gives an expected output for a given input.If you don't have any such tests, already built, then you have no choice but to either test it manually (which is expensive and time-consuming ... which is why we use an automated test), or cross your fingers and hope ... |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-23 : 20:58:48
|
quote:
Originally posted by sodeep
If the Upgrade Advisor doesn't show any issue with SP then you are fine for it. There is nothing to worried about. We have done migration from 2000 to 2005 for numerous servers. I would say just have 2005 QA Production Server and Test all your Products and Make sure it works and then flip it to Production Server with Migrations. Have QA involved for Testing for it.
Ofcourse Upgrade Advisor report shows some points which are like sql server 2000 has sysxlogins and 2005 doesn't have it and many more statements like that(which i need someones help excessively to understand(I cannot post those over here :().....i am also worried about the stored procedures. Many of the stored procedures are getting or inserting or updating data from other servers(LINKED SERVERS), so i am not sure whether these stored procedures work in the same way as they are working before with LINKED SERVERS. |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-23 : 21:05:39
|
quote:
Originally posted by Kristen
"Sorry for making this complicate...could you please let me know what regression test you are talking about"OK, in that case you don't have one.A regression test is something you build with the software so that you can run an automated test, at any time, to compare whether the output from your application provides "expected" answers.For example, we have a QA database for a web site. We use Ruby Watir scripts to run the Browser automatically - visiting specific URLs, filling in forms with specific values, and so on - and automatically taking a screen snapshot of each page.The snapshots are compared against known-good images (from an earlier run) and any that are different are listed for human review.We run this every time we make any changes to our system, and then have high degree of confidence that whatever we have changed has not changed the way the program operates.We also use Unit Tests - these are specific to each individual SProc / component of the system. We use these to test that each component gives an expected output for a given input.If you don't have any such tests, already built, then you have no choice but to either test it manually (which is expensive and time-consuming ... which is why we use an automated test), or cross your fingers and hope ...
hmm...Anyways thanks for your input on this. I will keep on posting my questions in this post if i have anything else other than these SP. |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-02-28 : 17:40:18
|
quote:
Originally posted by srikanth12
quote:
Originally posted by sodeep
If the Upgrade Advisor doesn't show any issue with SP then you are fine for it. There is nothing to worried about. We have done migration from 2000 to 2005 for numerous servers. I would say just have 2005 QA Production Server and Test all your Products and Make sure it works and then flip it to Production Server with Migrations. Have QA involved for Testing for it.
Ofcourse Upgrade Advisor report shows some points which are like sql server 2000 has sysxlogins and 2005 doesn't have it and many more statements like that(which i need someones help excessively to understand(I cannot post those over here :().....i am also worried about the stored procedures. Many of the stored procedures are getting or inserting or updating data from other servers(LINKED SERVERS), so i am not sure whether these stored procedures work in the same way as they are working before with LINKED SERVERS.
Hi Sodeep, Can i get your e-mail address so that i would like to discuss with you regarding some of the points in Upgrade Advisor. |
|
|
|
srikanth12
Starting Member
22 Posts |
Posted - 2010-03-08 : 20:04:03
|
| Can anyone who has good knowledge in checking SQL server Upgrade Advisor reports give me their e-mail address so that i would like to discuss about it with them? Apreciate any of your help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-09 : 03:05:51
|
| Post your questions here, its an open forum. People conversing offline deprives the forum of the collective benefit. If you want a consultant then hire one! |
|
|
|
|
Help needed in Migration form SQL 2000 to 2005