July 16, 2008 at 3:52 am
NO REPLICATION ,NO CLUSTERING NOTHING NO OPTIONS
July 16, 2008 at 3:56 am
there is the IMPORT/EXPORT wizard in sql server..not sure what is error file in BCP:)
July 16, 2008 at 4:04 am
IMPORT/EXPORT is there in 2000 and 2005 .Error file in bcp is that the those data which are not able to get inserted into the new table will go to another file(error file) which we specify.
I just want to know whether there is any such option in SQL Server and when i migrate from 2000 to 2005 those datas which are not got inserted due to some error or any other reason have to go to any other file or table which we specify....:hehe:
July 17, 2008 at 5:42 am
Take care with the DTS. Try to test them in 2005 environment.
To edit DTS in SQL SERVER 2005 you need a microsoft tool named: SQLServer2005_DTS.msi , is available in the microsoft site.
Download and install it or you will not be able to edit the old dts in 2005.
July 17, 2008 at 7:28 am
ezekielen (7/17/2008)
Take care with the DTS. Try to test them in 2005 environment.To edit DTS in SQL SERVER 2005 you need a microsoft tool named: SQLServer2005_DTS.msi , is available in the microsoft site.
Download and install it or you will not be able to edit the old dts in 2005.
You can import DTS packages from SSIS (SSMS - Object explorer - Management - Legacy - Data Transformation Services)) and convert them to SSIS format. It is also possible to run DTS-packages encapsuled in a SSIS package.
July 17, 2008 at 7:55 am
If you migrate a database from SQL2000 to SQL 2005 via db restore, there are few steps you should remember:'
- Read about migrating from SQL2K to SQL2K5 in BOL
- Run MS Upgrade Advisor before upgrading the database. This will tell you if there are issues with upgrading database and DTS packages.
- If everything is ok restore the db backup to SQL2K5 server.
- Review all the db settings to verify their accuracy... pay special attention to the file growth settings
- Set the compatability level to 90
- Set the Page Verify option to CHECKSUM
- Run the following commands, see BOL for detail information on these:
DBCC UPDATEUSAGE (dbname)
DBCC CHECKDB (dbname) WITH DATA_PURITY
EXEC SP_UPDATESTATS
EXEC SP_VALIDATELOGINS
EXEC SP_CHANGE_USERS_LOGIN 'Report'
- Review all Full Text Catalogs
- Verify that Full Text indexes are set to Automatically check for updates
- Create job to optimize the full text catalog at some interval
- Migrate DTS packages via SQL Server Managemt Studio
- Re-write ones that have imbedded Execute DTS package task
- Rewrite ActiveX script tasks to Script tasks.
The Microsoft SQL Server 2000 DTS Designer Components is a free download from MS that will allow you to edit DTS package task from BIDS.
You can find this in the SQL Server Feature pack for MS SQL2K5:
- Set up/modify Maintenance plans as needed
Good luck with the migration
David
July 18, 2008 at 2:58 am
Thanks alot DAVID 😀
July 18, 2008 at 3:33 am
I have gone through all reply of this post and would like to suggest following two on top of all these:
1. You must Run DBCC CHECKCATALOG in sql server 2000 if there is error and you migrate to 2005 you will get error in DBCC CHECKDB in 2005 after upgrade. If anybody has edited system tables this will give error also DBCC CHECKDB won't able to catch this error as DBCC CHECKCATALOG is respect to system tables.
2. I suggest strongly do version change in development and test thoroughly. You might need to tune a few stored_procedures if required. As sometime it will use different optimiztion engine(logic).
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
July 21, 2008 at 4:24 am
Set the compatability level to 90
Can any 1 explain y do we need to change compatability level to 90
wat is an issue with compatability level 80.......:)
July 21, 2008 at 4:30 am
maruf.khan (7/21/2008)
Set the compatability level to 90Can any 1 explain y do we need to change compatability level to 90
wat is an issue with compatability level 80.......:)
Compatibility level in sql server 2005 is 90, some features will not work properly in sql server 2005 if the compatibility level is not changed to 90. in sql server 2000 it is 80.
July 21, 2008 at 4:37 am
can u giv 1 example which u hav seen is not working
thkz for the reply
July 21, 2008 at 4:49 am
maruf.khan (7/21/2008)
can u giv 1 example which u hav seen is not workingthkz for the reply
one example would be, one job that we had actually scripted out in sql server 2000 and then when had executed the same script in sql 2005 we found a message that the script isint compatibile with sql server 2005. then we had to change the compatibilty level in sqlserver 05, and then run the script.
July 21, 2008 at 5:11 am
wat abut performance my friend got an performance issue wen he changed comp level to 90
This is wat my friend got wen he upgrated from 2000 to 2005 and changed the Compatibility Level
After the install I tried to delete a row from my Item table which is a
foreign key to about 10 other tables. A simple
Delete Item Where ItemID = 888
In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
to let it run successfully, i stop it after 5mins.
After the upgrade I changed the Compatibility Level to SQL Server 2005
(90) under Database Properties -> Options
After I changed the Compatibiliity Level back to SQL Server 2000 (80)
the query ran in approx 17 secs.
I am little confused bcoz i have to migrate from 2000 to 2005 and there are around 25 live servers.
July 21, 2008 at 7:22 am
You should update statistics or rebuild indexes when you move to SQL2K5 as well. This would hurt performance if not done. ( this should also be a part of your regularly scheduled maintenance as well)
-- You can't be late until you show up.
July 21, 2008 at 7:37 am
tory it was updated
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply