November 18, 2007 at 11:50 am
Hi I'm taking over the migration of a clustered SS2000 32 bit server that will
be migrated to SS2005 64 bit cluster.
Are there any glaring pitfalls that are luming for me? It is extremely difficult to
take over a migration that has not started but everything is in place already.
I'm not sure wether the DBA that was at the helm did his due deligence, and now
I'm stuck with his migration.
Thanks!:angry::angry:
November 18, 2007 at 11:17 pm
There are quite a few possible gotchas in the migration from SQL 2000 to SQL 2005, and a few more when you go from 32 to 64 bit.
The SQL 2005 Upgrade Advisor will help you to determine if there are any fundamental issues with your SQL 2000 databases - it will check your database and identify any "features" in your SQL 2000 databases that are not compatible with SQL 2005. Be sure to take a look at any DTS packages, etc. that need to move with your database... nothing like finding out that the database moves just fine but that you've got to update 500 DTS packages...
As for the differences between 32 and 64 bit there are a couple of gotchas, be sure to check for linked servers, etc. in use on your 32 bit machine - one noted issue is that 64 bit, particularly X64, versions of SQL Server 2005 may need both 32 and 64 bit drivers installed for external data sources (e.g. oracle). On X64 you also need to get used to the fact that different components of SQL 2005 run 32 bit (e.g. Visual Studio/BI Studio) and others 64 bit (e.g. the core database engine) which can cause some frustrations - particularly if you're developing on a 32-bit machine with driver X and then find out that driver X is not available in a 64 bit version...
Joe
November 19, 2007 at 8:42 am
the big part is sql 2000 to 2005. the 32 /64bit part is no biggie.
you can take databases from 64 bit and mount them on 32 bit servers and back again as often as you want
November 19, 2007 at 9:13 am
Indeed within a sqlserver version (sql2005) you can move datafiles from 64bit to 32bit and visa versa.
keep in mind, once mounted on sql2005 you cannot detatch and attach
on sql2000 ! (implicit upgrade scripts).
Also keep in mind if activating CLR modules, compile them to 64bit ! (so you'll not have them running in 32-bit mode.
As already stated, SQL2005 upgrade advisor is a MUST START WITH !
Also keep in mind DTS <> SSIS.
Your biggest challange will be geting used to the new UserInterface(s), SSMS, BI-devstudio and finding out where the "old" functionality has gone (new places) and what and what not to do using the new UI.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2007 at 9:24 am
Follow the above advice and Re-test everything! Especially beforehand on the 64-bit server if you can. Check alerts, mail, other things that might be set up on the SQL 2000 server. Be sure they will work as they have in the past.
Don't assume any diligence was done.
November 19, 2007 at 3:29 pm
Thank you all for your responses. We do have linked servers so this might be a problem.
Thanks Again!
November 19, 2007 at 4:00 pm
What kind of linked servers? Other SQL Servers, Oracle?
January 28, 2008 at 11:38 am
Hi Joe,
I'll be doing this exact scenario and my existing 32bit servers have Oracle linked servers. Can you give me the blow by blow on getting those set up again on 64 bit?
I'm also trying to figure out the best way to migrate all of my logins, jobs, and DTS packages as I can obviously not do an in-place upgrade. I did find the Microsoft article on scripting logins which I assume I could use but I'm not sure about the other two. I have read that I can restore user databases directly from the 32 bit backups. Then I'll change the compatibility mode and update statistics.
I'd appreciate any other tips people might have.
Thanks,
Dan
January 28, 2008 at 1:01 pm
I can't answer your question about Oracle linked servers, but regarding migrating logins, jobs and DTS packages, scripting is the way to go. MS's KB article about transferring logins works great. Jobs can be scripted from Enterprise Manager. DTS packages should be saved as files then opened in SQL 2005 Management Studio and saved as legacy DTS packages. If you install the DTS runtime components on the SQL 2005 server, you'll be able to continue running them as DTS packages until you are ready to migrate them to SSIS.
DTSBackup 2000 (free at http://www.sqldts.com/) makes saving and transferring a lot of DTS packages easy.
Greg
January 28, 2008 at 1:10 pm
Thanks Greg,
So I can script a job on 2000 and it should be created fine on 2005?
Also thanks for the tips on DTS, I know that will be a pain point. The silver lining there is that my developers are using VS2005 now so the pain should be reduced somewhat.
I'm figuring that there is a 64bit Oracle client or that I can run the 32bit client in WOW.
Dan
January 28, 2008 at 1:11 pm
forgot to tell you all, sql mail doesn't work on 64 bit. you'll have to change everything to db mail
January 28, 2008 at 1:51 pm
Thanks again. I'm sitting here during a break in the 2780 class and I thought up another scenario. What if I do an inplace upgrade of my SQL 2000 running on 32bit windows/sql to SQL 2005, backup all databases ( including master, msdb etc), smoke the box, reinstall 64bit Windows/SQL, then restore master, and then the rest of the DB's.
Would that give me a cleaner install because I won't have to worry about migrating logons, jobs, DTS packages (I'm assuming it must do some kind of conversion for those)?
Can you acutally do a master database restore if it was backed up in 32bit? I figure you just have to make sure you are restoring 2005 to 2005...
January 29, 2008 at 10:02 am
As far as I know, a backup of 32-bit databases will restore on 64-bit as long as the SQL version is the same, but I haven't tried restoring master. Hopefully, someone else here can share experience with that.
We've shied away from in-place upgrades from SQL 2000 to SQL 2005 because of the difficulty of reverting to 2000 if something goes wrong. We've even had an MS support engineer recommend not doing an in-place upgrade.
Greg
Greg
January 29, 2008 at 10:06 am
Thanks for the input. I plan on building several test boxes to try out both my in place scheme as well as migration from 32bit 2000 to 64bit 2005 on a separate box. I'll post my results back here in case they are helpful for anyone else.
January 30, 2008 at 1:12 am
I am. also, in the same boat. I have to migrate from SQL Srv 2000 32 bit to SQL 2005 64 bit. I migrate the databases, the logins and part of replication. Here I am in stuck. I have 4 transactional replication publishers and 2 merge repl. publishers. I am in stuck with merge replication because on 2000 sql server the 2 merge publishers publish the data from the same database to 2 diffreent databases subscriptions. I have also 2 rows filters on publisher. I got the following error:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403
The subscription does not exist. (Source: MSSQLServer, Error number: 14055)
Get help: http://help/14055
Cannot generate merge replication stored procedures for article 'partizispeciali'. Stored procedures are generated on the Publisher when the Snapshot Agent runs or when a data definition language action is performed; they are generated on the Subscriber when the snapshot is applied by the Merge Agent. Verify that the agents have the appropriate permissions to create procedures, and that the procedures do not already exist. (Source: MSSQLServer, Error number: 20636)
Get help: http://help/20636
Merge replication upgrade of SQL Server 2005 metadata and triggers on the subscriber failed. (Source: MSSQLServer, Error number: 20691)
Get help: http://help/20691
It is funny that one of merge replication is working and syncronizing and the other is blowing this error.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply