Moving from SQL2000 (32bit) to SQL2005(64bit)

Question

Post reply

Moving from SQL2000 (32bit) to SQL2005(64bit)

Rich Hansell

SSCrazy

Points: 2346
More actions
February 8, 2007 at 11:45 am

#175905

Hey Guys/Gals..
We are planning to migrate to SQL2005 however a concern has been raised regarding the move from 32bit to 64.. I have been doing a lot of web searching for any issues that people have experienced but have come up with a short list.. Anyone here been through a similar migration? Any "gotchas" to look out for? Any advice would be greatly appreciated!
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Upgrade from 32bit 64 bit is not a problem... you can do backup restore or detach attach...

I have not seen any problem but it always better testing specially applications.....

test..test..test... move...

http://support.microsoft.com/kb/906892

http://msdn2.microsoft.com/en-us/library/ms141766.aspx

http://msdn2.microsoft.com/en-us/library/ms143506.aspx

MohammedU
Microsoft SQL Server MVP

Richard Fairgrieve Grasshopper Points: 16 More actions · Answer 2

Bear in mind that if you have any extended stored procedures that you will have to create/compile a 64bit version of these.

Charles Barnhart-229428 SSC-Addicted Points: 435 More actions · Answer 3

If you rely on linked servers for distributed queries, get ready for a potentially bumpy ride. Especially if you want to use Windows-only (Kerberos) security. Also, the Microsoft Oracle provider does not exist for X64 version and, so far, I have not yet been successful with Oracle's provider but I'm still working on it.

cjb

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

I installed our first SQL 2005 which was 64 bit. Yes, the tools are slower on 64 bit. I have had issues with Integration Services, called MSFT and eventually fixed it. I cannot say I have found any plus or minus for 64 bit yet as we have not put any large dbs on it yet.

Christopher Bellizzi SSC-Addicted Points: 423 More actions · Answer 5

And just to add to the fun. IBM does not have a driver for accessing the DB2 platform. Although they have a 64 bit driver it does not work for a 64 bit program duh? I worked with the IBM technical team for 2 hours beforer they called the developers and that was the answer I got. The way around the issue is to use the Microsoft DB2 driver which works pretty well. cjb-

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

We moved from SQL2000 32-bit to SQL2005 64-bit and have not had any major issues. We don't have any custom external stored procedures, and we don't link to Oracle or IBM systems. We went from a dual processor server with 4GB RAM and SQL 2000 Standard 32-bit to a dual-core dual processor x64 server with 24GB RAM and SQL 2005 Enterprise x64. Most of our processes were roughly 10 times faster on the new system. Better disk structure and faster processors helped, but mostly I credit the RAM.

It's not completely painless but overall we're extremely happy. The main driver issue we had was running SSIS packages that used the Jet driver (Excel spreadsheets, DBF files, Access databases) because there's no 64-bit version. All you have to do is configure the package to run as 32-bit on the x64, and suddenly the Jet driver appears. I don't know if the Oracle or DB2 drivers work under WOW, but if I needed to use them I'd try it.

If you have some other process that has to run on 32-bit SQL 2000, one option would be to keep the old server around to run that process until you can migrate it.

Christopher West Ten Centuries Points: 1209 More actions · Answer 7

Hi Scott,

I'm running into the same issue with the Jet Engine. I've been able to execute Packages that import EXCEL files by changing the Run64bitRuntime parm on the SSIS Project to False but when I try to run the package using SQL Agent it fails. DO you know of a way around this?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

I don't think I've tried that. Have you tried setting the agent job step up both as a Integration Services step and as a CmdExec step that uses a dtexec command? The cmdexec step introduces an extra step that might make a difference in having it run as 32 bit.

Christopher West Ten Centuries Points: 1209 More actions · Answer 9

Thanks Scott, that worked. FYI - I found 2 different dtexec.exe files on my computer. I needed to specify the one located in the Program Files (x86) directory... I guess (x86) means 32-bit.... Why does Microsoft make you jump through these hoops? Afterall, aren't SQL Server and EXCEL both Microsoft products? Didn't the SSIS developers at Microsoft test trying to load a spreadsheet into SQL Server in a 64-bit environment? Why couldn't SSIS have been designed to use the 32-bit version of DTExec when loading spreadsheets? Am I missing something?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 10

The alternatives to having separate paths for 32-bit and 64-bit code are not very attractive. One is to have a 64-bit architecture like Itanium that doesn't run 32-bit code at all, or run the 32-bit code unchanged and not take advantage of 64-bit features. Or somehow have every code file include both 64-bit and 32-bit versions and have the operating system autodetect which one to use when the file is loaded, which is too gruesome to contemplate.

Their focus is primarily getting server products like SQL Server and Exchange to work on 64-bit, client tools like Office products are a lower priority. If you don't have a 64-bit version of Excel and Access, there's no compelling reason for a 64-bit Jet engine to read them.

I agree that it is an unpleasant (if understandable) surprise that there are issues with importing spreadsheets and other Jet-related data sources in SQL 2005 x64, but I would prefer that Microsoft not worry about that issue until they can take care of other issues like getting service packs right the first time.

wyliec2 SSC-Addicted Points: 438 More actions · Answer 11

I don't know that I fully agree/understand the problem with building SSIS packages and executing them as a SQL job on a 64-bit platform.

I wish there had been a disclaimer when ordering the 64-bit platform/version that trying to do integration with other systems becomes much more difficult!!

SSIS is about integration and most integration is not a one-shot deal so it is done repetitively and consequently put into a scheduled SQL job. Yet if you're trying to integrate with Excel or Access then you have to jump through the command line call exercise to run the 32-bit dtexec.

I've gotten past that, but still haven't successfully connected to Oracle from the 64-bit platform or from a SQL job. Oracle 64-bit provider Net Manager tests succeed. But I can't see the Oracle provider from BIDS (running BIDS on the 64-bit platform).

Does anyone have the steps necessary to connect from a 64-bit platform (ie. in an SSIS package running as a SQL job) to an Oracle database??

Many of us have come to take the simple connectivity capabilities for granted and it seems like going to 64-bit takes us back to the stone age... It should not be that difficult to foresee that SSIS packages are typically going to be built on 32-bit workstations to run on 64-bit platforms often as scheduled jobs. /* Rant Complete */

PS - if there's an easier way on any of this and I'm just clueless, I'll gladly have two servings of crow!!!