November 17, 2008 at 2:58 pm
Dear All, I'm a newbie here. I apologize if this is not the right place to post this topic.
We are looking to migrate our 2000 databases to a new server with 2005 installed.
For my limited knowledge in SQL, could somebody give me a detail procedure on how this is done?
Also, will it make any difference if the target is a 64 bit server??
I really appreciate this.
KF
November 17, 2008 at 3:31 pm
Ok, first you need to run SQL Server Upgrade Advisor, to find out what problems you might face during migration and fix those problems. you can download it from Microsoft's website. Then remove all orphaned users from your SQL 2k database.
Run DBCC CHECKDB command for all Databases once migration is done.
Run SP_UpdateStatistics for all the tables and indexes.
Change compatibility to 90.
Create Datafiles for the tempdb database. for example if your server is quad core then create 2 datafiles for the tempdb.
Reindex all the tables.
Run DBCC UPDATEUSAGE.
This are the steps we followed i don't remember the exact order rite now.
November 17, 2008 at 8:22 pm
wow, that sounds complicated. I read in a few articles to just do a backup from the 2000, and then restore it onto the 2005. will that work??
Again, I'm not a SQL person, a bit more details would really be appreciated.
thx.
November 17, 2008 at 9:16 pm
You can always try to restore 2k database to 2k5 in your test environment then see if everything works well as required then you are all set, but after you do that atleast you have to update statistics , run dbcc checkdb and rebuild indexes on the table.
An trust me it's not that complicated. It would be better if you run SQL Server Upgrade Advisor, will help you a lot with your migration and then you won't have to worry about features or data types which are available in 2k and not in 2k5.
For orphaned users you can find script online.
As Query Optimizer uses both indexes and statistics to select best path it would be in your best interest if you run reindex and update statistics.
If you want to take advantage of the features of 2k5 then you have to change compatibility level to 90 from 80.
After you are done with this you have to take care of your tempdb and create extra data files for them to get better performance.
November 18, 2008 at 4:35 am
If these databases are critical, hire a consultant. Not getting personal, but it seems you're not really knowing what you're doing. You just can't do a database upgrade by reading some papers during the weekend, or rely on answers on a forum. If something goes wrong during the big bang, who you're gonna call?
Maybe this is a challenge for you; make sure your boss understands the importance of a database admin an take a course. Good for your CV!
Wilfred
The best things in life are the simple things
November 18, 2008 at 5:04 am
If you have a testing server, try in that first and so you will know the importance of the points mentioned above, then there are lots to look once migrated to 2005, since some commands will not work on 2005,
I would say ( as all suggested) run upgrade advisor, try to know what your database is exactly working with, test that , then migrate
Cheers
🙂
November 18, 2008 at 6:46 am
ok, thank you all for the input. will try this out. thx.
November 19, 2008 at 3:54 am
I'd just like to mention one caveat that I experienced upgrading form SQL2000 (32) to SQL2005 (64).
Everything went ok but some time down the line the 2005 (64) bit server periodically stopped responding. It transpired that it was being paged out of memory.
There are fixes for this but they are a bit of a pain and not entirely guaranteed, in addition most of them do not apply to the SQL2005 (64) standard edition only the enterprise edition.
So - make sure you have oodles of memory on the server where you will be hosting the SQL2005 64 bit addition.
November 19, 2008 at 4:19 am
You probably mentioned the "... a significant amout of memory has been paged out ..." errors you'll see in the errorlog.
The solution is simple:
1) make sure the Os has enough memory available by defining a min and max memory setting for SQL
2) enable "lock pages in memory"
3) enabling AWE is not necessary in a 64bit environment, this option is ignored (but confusing)
Wilfred
The best things in life are the simple things
November 19, 2008 at 4:31 am
That is correct, but even doing this does not necessarily ensure paging out in the standard edition.
btw: lock pages in memory does not actually do anything in standard edition - see the MS KB regarding this (don't have the link). You can enable it but apparantly it doesn't actually do anything in the standard edition.
November 19, 2008 at 4:36 am
Wilfred van Dijk (11/19/2008)
You probably mentioned the "... a significant amout of memory has been paged out ..." errors you'll see in the errorlog.The solution is simple:
1) make sure the Os has enough memory available by defining a min and max memory setting for SQL
2) enable "lock pages in memory"
3) enabling AWE is not necessary in a 64bit environment, this option is ignored (but confusing)
I've been in the same boat - 32bit MSSQL 2000 to 64bit 2005 Standard Edition and got the memory paging issue. Lock pages in Memory isn't supported for 64bit Standard Edition though so other than throwing tons of ram at it, are there any more elegant solutions?
November 19, 2008 at 4:56 am
Make sure there's enough memory available for the OS.
Make sure this is a dedicated server, remove all unnecessary programs/services which could eat your memory. Our memory problems were caused by an Antivirus product.
Wilfred
The best things in life are the simple things
November 19, 2008 at 5:07 am
I was afraid you were going to say that 😀
There's nothing else running on the box, max server memory is set to leave 6gb for the OS and nic and hba drivers are up to date (in case they were leaking). The 6gb figure came about by slowly decreasing max server memory until the 17890 errors became rare but it feels like a poor workaround. Performance monitoring has revealed nothing of interest. I hadn't considered a possible connection between moving databases from 32bit 2000 but I'll entertain any possibilities at this stage 🙂
November 19, 2008 at 5:16 am
Ted Stryker (11/19/2008)
I was afraid you were going to say that 😀There's nothing else running on the box, max server memory is set to leave 6gb for the OS and nic and hba drivers are up to date (in case they were leaking). The 6gb figure came about by slowly decreasing max server memory until the 17890 errors became rare but it feels like a poor workaround. Performance monitoring has revealed nothing of interest. I hadn't considered a possible connection between moving databases from 32bit 2000 but I'll entertain any possibilities at this stage 🙂
what do you have min memory set too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 19, 2008 at 6:17 am
The 64 bit memory issue is a real issue for SQL2005 Standard edition, that is why I mentioned it as a caveat when upgrading from SQL2000 32 bit.
However I seem to have moved this forum off topic from the original question so I'll just post this then leave it alone.
The most useful/interesting sequence of fixes/workarounds I found is here:
http://www.eggheadcafe.com/software/aspnet/32821566/memory-issues-with-64bit.aspx
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply