We're starting to think about migrating a 2000 32-bit sql/server to 64-bit (probably 2005). Performance gains are tempting when it can easily be bought, almost a management mantra . I've been doing some reading about the pro's and con's, most of what I've found is pro, very little on the con.
My impression is that the 64-bit version(s) is faster in most case, if not all. It would seem that most of the performance improvement is gained by the heavier equipment and overcome memory limitations. Too me, it looks like the integration of hw/os/sql is made very tight on 64-bit, more than 32-bit, which might be the main reason for the improvements.
Call me skeptical but I'm looking for some more con's and a little less on the "great, fantastic, improved and hoorah" that I've been reading. Anyone out there returned to 32-bit after a 64-bit disaster?
Yes, I know of folks that have returned to 32 bit code after a venture into 64 bit code.
There are downsides to running 64 bit code, but in my experience those downsides are few and highly predictable.
Specific 32 bit add-ons don't work in the 64 bit enviornment. Nearly all of those that I know of are propriatary, and very "utility" or "vertical market" in nature. Just be aware that not everything you can hang on a 32 bit SQL instance will stick to a 64 bit instance, especially things like SAN customizations and third-party add-ons.
At present, 64 bit code is not as common as 32 bit code. This means that borderline cases often don't get the attention in the 64 bit world that they get in the 32 bit world. If you've got a "screen door widget" that you desperately need to control via SQL Server, be very sure that you carefully check the widget out before you put it into production.
There is always the "golden bullet" problem, where people think that somehow using the 64 bit version of any application will fix underlying problems in their own code. That is the most subtle, and probably deadliest problem I've heard about.
All in all the conversion is a very good thing. I would STRONGLY recommend that anyone running a machine with more than 2 GB of RAM try it, especially if that machine has more than two CPUs. I'm not saying that they should automatically convert, just that they should try it to see what it will do for them.