March 29, 2007 at 5:18 pm
We're migrating from SS 2000 to SS 2005 by EOY. They are ordering a new server and I've heard it is going to be 64 bit. I don't think we'll need to use more than 8G of RAM.
Anyway, what do I need to consider in order to port this to 64 bit? The O/S side is handled by another group. But what Sql Server considerations are there? Nothing changes on the app side, right? Are there any config changes other than Awe Enabled?
March 29, 2007 at 9:49 pm
You don't need to enable AWE but you may need configure "Lock in pages memory"..
Migration of databases is just backup restore....
MohammedU
Microsoft SQL Server MVP
March 30, 2007 at 12:09 pm
So it's that easy to go to 64 bit?
Btw, does 64 bit give you any extra processing speed? It doesn't, right? All it gives you is the potential for more memory, correct?
April 1, 2007 at 5:55 pm
Lock pages in memory only works for the Enterprise version of SQL Server 2005.
64bit processors will be faster for given clock rates because they move larger amounts of data/instructions through the CPUs for each clock cycle.
The movement to Sql 2005 IS pretty straight forward, but read up on upgrade mechanisms and issues. It can be problematic depending on your code/structures/ancillary processes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 2, 2007 at 4:48 am
We moved to 64-bit SQL 2005 standard edition a little while ago and it was pretty seamless. We're ramping up additional activity on the server so we've not pushed the performance gain yet.
The biggest consideration in my opinion are whether there are other applications on the server that could be impacted using 64-bit instead of 32-bit.
April 2, 2007 at 6:31 am
64-bit can potentially give you a big performance boost, depending on your workload. 32-bit systems with AWE can use memory beyond 4GB for additional buffer pool space, while other structures such as the procedure cache are still limited. 64-bit systems can use all their memory for whatever it needs. Also, AWE doesn't really give the system direct access to all memory over 4GB, it is a paging scheme that maps a window to the part of the higher memory that it needs at the moment. This has some overhead and is much less efficient than letting each process directly address a large memory space. There are other improvements in SQL Server 2005 that also may contribute to making your system run better.
Given the price of memory, if you think you might need 8GB then why not double that to 16GB? You might be amazed. I went from a SQL 2000 Enterprise 32-bit system with 4GB to a SQL 2005 Enterprise 64-bit system with 24GB and immediately saw about a factor of 10 improvement in most large workloads. Part of that was due to dual-core processors and higher GHz, but I think at least half of the improvement was because of the RAM.
April 2, 2007 at 6:42 am
I should clarify that my last post was just a response to "does 64 bit give you any extra processing speed?". You still have to look at everything you are doing with your server to check for 64-bit compatibility. Make sure you can get 64-bit versions of any ODBC drivers or any third-party software you use.
One issue some people complain about is that there is no 64-bit Jet driver, so you have problems with Access and Excel. Your 64-bit server will have a 32-bit Jet driver (and 32-bit copies of other software), but they are not available in every context. If you use the Import/Export wizard you'll only see 64-bit sources, but if you create an Integration Services package that is flagged to run as 32-bit it will be able to use 32-bit sources and it will work with Access, Excel, DBF, etc.
April 2, 2007 at 8:42 am
Will the processors be Intel or AMD? Do you get to choose? The AMD runs both 32 bit and 64 bit seemlessly. Itanium runs a 64 bit architecture, so 32 bit is software/firmware emulated.
April 2, 2007 at 11:58 am
To follow up on Scott's comment regarding 32-bit/64-bit - if you create packages that require 32-bit providers (Excel, Access) and are going to execute these packages as jobs via SQL Agent, then you'll need to execute them via command line dtexec as an Operating System job step instead of an SSIS package job step.
If you are connecting to Oracle, then there's a bit more fun in store due to the (x86) path problem!
We are just finalizing a migration from 2000/32-bit to 2005/64-bit and we seem to have everything working now (knocking on wood with my fingers crossed!) but it was quite a journey!!
April 2, 2007 at 9:40 pm
That explains (I think) what I read about potential improvement in context switching.
Yes, SSIS will be number one. But I meant are there are any issues going from 32 to 64 along, i.e. not any issues going from 2000 to 2005 (although I realize the two go hand-in-hand).
April 2, 2007 at 9:41 pm
Sounds good. This server has poor Page Life Expectancy performance so it's an especially good candidate I would think...
April 2, 2007 at 9:43 pm
Thx for the advice, but I think we'll be okay: it'll be almost entirely accessed from .Net apps.
April 5, 2007 at 12:46 pm
Data wise, nothing to worry about. The install can get a little tricky, first time I did it I didn't even realize I had installed the 32 bit version.
Detach your databases from the old server, copy them, and attach (having first copied syslogins, of course, using the usual hack). It doesn't care about 32/64 at the disk file level.
Processing speed is dramatically faster in many cases, just from the 64 bitness.
However, if you are running multiple CPUs, you potentially can further improve the speed by choice of CPU. I'm not a vendor partisan, by the way, just reporting the state of the art as I see it.
We got a huge boost from using an AMD Opteron based box. Benchmarked against the best Intel dual-cores about a year ago, the Opterons were amazing due to the different connection between the CPU and the I/O channels. Both were running 64-bit Enterprise for OS and SQL, same number of dual core CPUs at similar clock rates, against multi terabyte iSCSI arrays, with 16 GB memory.
The AMDs have seperate IO channels per CPU, whereas the more traditional front bus means all your CPUs are contending for the same IO channels.
Roger L Reid
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply