Migration query from 2005 -> 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Migration query from 2005 -> 2008

Author

Topic

ram.marella
Starting Member

15 Posts

Posted - 2012-02-16 : 12:16:08

Hi,
We are migrating the data base from 2005 to 2008; got a question here.
1. When we put the compatibility mode 100 there were osme errors but when we set the compatibility mode 90 applicaitons started working fine. basically we encountered ANSI joins errors (whihc has *= or =* in JOINS) when compatibility mode set to 100. Do you guys think its always better to fix the ANSI join errors though they are working fine with compatibility mode 90? I heard that Microsoft not really caring the features that are not supported in 2008 but in 2005 as part of thier pathces/upgrades which causing the issues in applciations later stage though they are fine at begining.

any suggestion are MOST welcome.

Thanks
-----------------
Ram MCP

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:33:31

I suggest

1. You script out all of the objects int their own files
Table
Constraints, indexes, Triggers and keys
Views
Logins and Users
Functions
Stored Procedures

2. You review all of the code and eliminate bad coding practices (like = *)

3. bcp out all of the data into |~| delimited form

4. Recreate the tables without constrainsts

5. Load that data into the tables

6. Add the contraints

7. Add the Views

8. Add the Functions

9. Compile the sprocs (Create a regression test script or each)

10. Have the app team test the new database

11. Have many cocktails while they confirm conversion

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:33:57

Or just do a dump and restore

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ram.marella
Starting Member

15 Posts

Posted - 2012-02-16 : 12:51:04

Thanks Brett for quick response.
Actually we have tested all the apps using restoring the data base after setting up compatibility mode 90. did not see any complaints from the testing. But, after hearing what i said having second thought of fixing bad code (JOINS). but do you agree that Microsfot pathes/upgrades may not support discontinnued futures anymore in 2008? in this case JOINS.

/Ram

Thanks
-----------------
Ram MCP

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 13:53:33

Yes..they will be depricated, if not already in 2k8R2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources