Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Are You Running the x86 Version of SQL Server 2005/2008?

If so why?  Honestly, I am curious why people may still be running 32-bit versions of SQL Server 2005, 2008, or 2008 R2 in a production environment.  I have been advocating that people use 64-bit versions of SQL Server 2005 and above for some time now. NewsGator’s production SQL Server environment has been 100% 64-bit since April 2006, but I am lucky in that I don’t have to worry about any 3rd party databases, and all of the applications that use my databases use the ADO.NET Provider (since we are a 100% Microsoft shop).

The main advantage of running x64 instead of x86 is that you can more fully and easily take advantage of all of your installed memory beyond 4GB. In the x86 world, you have to add the /PAE switch to boot.ini, set “awe enabled” to 1 with sp_configure, and enable “Lock Pages in Memory” for the SQL Server Service account in order to use AWE. You also have to decide whether to add the /3GB switch in boot.ini to let applications use 3GB of RAM instead of 2GB of RAM (which you would not want to do if you had more than 16GB of RAM, since the OS needs more memory to manage AWE). Depending on the Build of SQL Server that you are on, you may need to have Enterprise Edition in order to use “Locked Pages in Memory”. Its all pretty complicated.

After you do all of this, only the buffer pool can use the AWE memory. Things are much simpler and better with x64. With x64 or IA64, SQL Server 2005 and above can use all of the installed RAM for pretty much any purpose, with none of the 32-bit limits. Nearly any Intel or AMD server CPU sold in the last 5-6 years should be x64 capable (its easy to check with a tool like CPU-Z). Server RAM is pretty inexpensive, and you can never have too much RAM for SQL Server. The more RAM the better!

You may have heard that Windows Server 2008 R2 is 64-bit only, and there is a small possibility that SQL11 will be 64-bit only (not that I have any inside information). What do you think?

When I asked this question on Twitter a couple of days ago, the main answers I got were:

3rd party, ISV databases that require x86

3rd party, ISV databases had not been “certified” on x64

3rd party applications using older data access technology that is x86 only

I would love to hear any other reasons that may be blocking people from moving to 64-bit versions of SQL Server.

Comments

Posted by Steve Jones on 29 January 2010

I can think of one more: hardware. In 2006, 64 bit hardware wasn't necessarily a commodity for many places. There were people that upgraded from 2000 using older, 32 bit hardware.

Of course a number of people, especially those with AMD CPUS, might not realize they have a 64 bit system.

One big reason I heard for SS2K8 R2 not being 64 bit only was older 32 bit drivers, like JET drivers.

Posted by Glenn Berry on 29 January 2010

Good point Steve. Still, I would argue that any hardware that is too old to run x64 is pretty ancient now. It would certainly be out of warranty, which is not good for a database server.

People may be able to make a case for new hardware and server consolidation or virtualization at the same time if they are still running very old x86 hardware.

Posted by PLR on 3 February 2010

For products I work on there is no reason for our clients not to use x64 version of SQL Server. Similar to the article we are mainly a MS shop and certainly everything I've worked on has been MS top-to-bottom.

The main issue for us is our clients are government sector and hence have looooonnng life-cycles - e.g. they STILL use IE 6 8(

Posted by stephen.parry on 3 February 2010

We are thinking of moving to x64 SQL 2008. Our main database is a 64-bit Oracle 10g setup. The stumbling block is that we have heard of many people having problems running SSIS packages - mainly due to issues with the Oracle driver. Something to do with it not being able to handle the longer path name (x64). We haven't done any testing in-house so only going on what i've heard.

Posted by Wrk-1146259 on 3 February 2010

We are still running SQL 2000, although a project is now underway to upgrade to SQL 2008.  We are considering moving to 64 bit, but have to persuade people that this is a sound move.

We would also need to replicate this with 64 bit versions in the Dev and Test environments which is going to be expensive.

Posted by chris webster on 3 February 2010

We currently use Oracle 10g drivers to connect to Oracle 9 databases from a 64 bit Polyserve SQL 2005 cluster. Our main issue in removing the old x86 systems is the time it takes to migrate some internally developed systems to our clusters. Some of them require extensive toilet training (redevelopment) before I'll let them play with everyone else.......

Posted by Lynn Pettis on 3 February 2010

Hardware.  Currently all our LOB applications are running on x86 (32 bit) hardware.  We are currently migrating our PeopleSoft Finance and HR systems to x64 (64 bit) blade servers.  The new tools release we are installing is all x64 based, so this is our first major move.

We will be looking at moving other systems to x64 hardware in the future.

Posted by David Bird on 3 February 2010

Besides having some servers that dont support x64, the migration to x64 is not complete by Microsoft and other vendors.

For examples:

Oracle client installs on x64 but requires some manual customization,

Microsoft's driver "OLE DB Provider for Visual FoxPro 9.0" is only available as 32 so no 64 bit application "SQL Server" can use it.

SQL Server is not all x64, some components are still 32 and do not gain the expected benefits.

For new servers, we use x64 and deal with any issues.

Posted by richardd on 3 February 2010

Other than hardware and OS restrictions, the only problem I've run into was with an application that occasionally used some data from an Access database. At the time, moving the data into SQL wasn't an option.

Since there's no 64-bit Jet OLEDB provider, I was left with two options:

1. Stick to 32-bit SQL, and have the Access database as a linked server;

2. Use 64-bit SQL, with a 32-bit Express instance as a linked server on the 64-bit instance, and the Access database as a linked server on the 32-bit instance;

Posted by julian on 3 February 2010

We use Express Edition on some webservers where cost-to-customer has priority over performance (or where performance would not significantly benefit from more RAM). Express is restricted to 1GB RAM - no point being 64bit!

Surely there are plenty of such (smaller scale) circumstances where the power of SQL Server's features (over MySQL or Access databases) are the driving motivation for its use?

Posted by EricEyster on 3 February 2010

We have a few legacy applications that require 32-bit Windows due to poor coding of DLL's.

Posted by talltop on 3 February 2010

The biggest reason I have seen in most shops I have been in is becasue the company is still supporting legacy third party applications....

Posted by JohnD on 3 February 2010

Most of our systems are running 64 bit. However I had a request to install SQL Server 2008 on 32 bit hardware just the other day. I questioned the logic of this and was informed that we're taking ~2 year old hardware from the current system for use in the new system.  This is the last of our 32 bit hardware purchased - I hope!  The database server admittedly does not get hit very hard so that is good.  More surprisingly to me was the fact that the software vendor (major vendor of commonly used software) required the Max Degree of Parallelism to be set to 1.  I questioned the vendor about this and they did say this is being addressed in the next version, of course.

Posted by Tom Winter on 3 February 2010

We just moved from SQL 2005 32-bit to SQL 2005 64-bit and has a few surprised, mostly from linking to 32-bit servers or using 32-bit drivers. I have heard that the next version of Office will include 64-bit Access/Excel drivers. We'll see.

Posted by SQL Noob on 3 February 2010

out of 30 some SQL servers we have a few 32bit ones left. one was upgraded from sql 2000 and it's running windows 2000. supports a critical app and no one wants to come in on the weekend to do it. a few others are subscriber database servers no one really cares about. one is for compatibility reasons. when we upgraded a server to x64 a few years ago we had some issues with BEA Weblogic jdbc drivers and set up a x86 server just to have a "legacy" installation in case something else came up.

Posted by rodney on 3 February 2010

SSIS is the largest hang-up for us.  Because we use it to migrate large amounts of data from other DBMS using ODBC-like providers, 32 bit compatible packages are created to run in our 64-bit environment.  Thus far, the process of installing and managing these packages is far from perfect and in the end, they don't run in true 64-bit mode. Also, 64-bit SQL Server lacks 64-bit JET drivers required to merge Access data within our SSIS packages.

The better question is why doesn't Microsoft go ahead and bring these aged, but still used providers up to 64-bit such that we can incorporate them into a production deployement of SQL Server 64-bit vs. scaling SQL Server 64-bit down to accomodate older providers?

Posted by David Data on 3 February 2010

My development PC only has 2.5G of RAM and I'm developing in 32-bit; 64-bit uses more RAM so would be no benefit on this small system.  Our new server (due on Friday) will have 12G RAM and will have 64-bit SQL Server installed.  However I'll have to run my SSIS application in 32-bit mode because the SSIS JET driver (which we need to import from Excel) are still 32-bit only, as far as I know.  I'm hoping 32-bit SSIS plus 64-bit SQL Server 2008 + 64-bit Windows Server 2008 will all 'just work' ... (and yes I know to set Run64BitRuntime = False.)   Will it?

But isn't it about time Microsoft provided 64-bit JET drivers?

Posted by Brnbngls on 3 February 2010

I guess I fall somewhere in between "If it ain't broke, don't fix it" and "Stagnation leads to being obsolete".  We have 32 bit SQL Server 2005 implementations.  Our production HR and Payroll environment is not certified for either the 64 OS or SQL installs.  Heck, even in our 2005 instances, they're setup to an 80 compatibility level (which drives me insane).  The newest release is supposedly 64 bit compliant, but still only 90 compatibility.

Other instances we have haven't been upgraded either due to licensing costs or time constraints.  Right now, I'm sole "DBA" for a company of over 5000 employees and probably close to 20 different SQL instances.  I put DBA in quotes because I wouldn't consider myself a true DBA (yet) and I still have other job roles to fulfill.  

I don't think it's fair to make a blanket statement that you should move to a 64 bit environment.  If the time, money, talent, etc. just aren't there you can't force it.

Posted by Jason Shadonix on 3 February 2010

We still 20+ 32-bit SQL 2K5 instances that we purposely kept at 32-bit when we upgraded to 2K5, due to the lack of JET driver for 64-bit.  Also, our in-house applications that hit these servers don't hit it hard enough that we would benefit from additional RAM.

Posted by nathan 7372 on 3 February 2010

You make some valid points for upgrading to 64-bit but forgot one thing.  You only gain a benifit if you are going to use more than 4 gb of memory.  Right now all our servers don't have enough utilization to need more ram than that so we stick with the 32 bit version rather than spend money on new hardware we don't need just to have the fancy 64-bit version.

Posted by RalphWilson on 3 February 2010

I can think of one major reason: Management.

We are still in the process of migrating from SS2K to SS2K5 . . . SS2k8 is not really even being talked about.  As a result, we have legacy 32-bit apps, legacy servers, and legacy databases and all of our development/desktop computers are 32-bit ("for compatability with other developers' desktops").  Our management doesn't consider moving to SS2K5 a real priority, much less moving to SS2K8.  Therefore, nobody even seems to be aware of 64-bit processors being out there.

Posted by Glenn Berry on 3 February 2010

This driver may be helpful to some people

64-Bit OLEDB Provider for ODBC (MSDASQL)

www.microsoft.com/.../details.aspx

There is an open Connect item about having Microsoft provide a 64-bit JET driver.  You cam go there and vote on it.

connect.microsoft.com/.../win-xp-x64-jet-v4-0

I have also heard that there is a 64-bit JET driver in Office 2010. I am trying to confirm this.

Posted by Pam Brisjar on 3 February 2010

For those talking about Jet and 64-bit, have a look at this:

blogs.msdn.com/.../how-to-get-a-x64-version-of-jet.aspx

As far as our customers are concerned, it's (mostly) a hardware issue, though there is also the whole getting enough downtime, etc. to actually make the conversion.

Posted by Glenn Berry on 3 February 2010

Pam,

Thanks for the link to the beta Office 2010 x64 JET driver.

Posted by Revenant on 3 February 2010

You may be forced to use SQLS 32 bits if you are developing medical apps.  Your platform, both hardware and software, has to be FDA approved, and they take literally years.  On my recent project I was forced to use SQLS under WinXP Embedded because Win2008 was not on the "approved" list.

Posted by Glenn Wilson on 3 February 2010

We are in the government sector and the main reason for us is the providers of the applications we use do not support the hardware, and in some cases the software. We still have one application (Large DB with a large client base) that the vendor has not updated past v6.5 but at least they allow me to run it on a sql 2000 engine in compatibility mode.

But on the side note all systems we have brought on in the last year or so one of the requirements is to support the latest hardware and software... which is good for us.

Posted by Scott Coleman on 5 February 2010

My biggest issues are third-party software.  We have a Progress-based accounting system and there are no 64-bit ODBC drivers available.  Hopefully it will be upgraded soon.  We have another critical application that could probably run in 64-bit but requires compatibility mode 80 because it uses old-style outer joins.

We have one orphaned SQL 2000 server that is used by multiple departments for legacy apps and nobody wants to take resposibility for migrated or updating the mess.  Most of the original developers have moved on.

Leave a Comment

Please register or log in to leave a comment.