The main difference betwen 32 bit and 64 bit server is Memory, you may be aware about 'Lock Page in memory' privilege to be granted to the SQL Server service accout within 32 bit  based servers.

This is a operating system based privilege that is required by SQL Server service account to allow locking of physical memory preventing OS paging of the locked memory. Within 32 bit architecture systems irrespective of edition i.e., standard, enterprise and developer editions, this privilege needs to be granted to the sql server service account in order to use the AWE mechanism.

Before performing such a huge changes to your server configuration ensure to check whether it is enabled or not by using following process:

--To show advanced options of your SQL configuration 

sp_configure 'show advanced options', 1

go

reconfigure with override

go

--To check whether AWE enabled or not

sp_configure 'awe enabled', 1

go

reconfigure with override

go

Here comes the trouble, if the lock pages in memory privilege has not been granted to the service account, then you will get following error message:

Msg 5845, Level 16, State 1, Line 1

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

Further down I will refer about how to enable this privilege within the Operating System, before that it is good to go through the requirement within 64 bit systems too. The above approach will be different in 64 bit machines, where you can get brief information by executing  'dbcc memorystatus'. Based on the results if you see the value within 'AWE Allocated memory' is 0 then lock pages in memory privilege has not been granted to the service account or you haven't attempted to restart SQL Server services after this has been granted in order to affect this change.

To check the account used for SQL Server services you can make use of SQL Server Configuration manager, by using Start --> run --> type 'sqlservermanager.msc' that will open Sql Server configuration manager API. Click Sql Server 2005 services and it will list all the services on the right side window. Double click the service named 'Sql Server(MSSQLSERVER)' and go to logon tab and it will display the service account under which Sql Server is running.

Further to grant the privilege of lock pages in memory for SQL Server service account refer to this article ‘How to: Enable the Lock Pages in Memory Option (Windows)’ and don't forget to restart the SQL Server services and sometimes a server reboot will also help.

It has been a while in posting X64 related information here, so that comes now about configuration gotchas you may need, for a starter though.

X64 computing platforms have become common installations now a days due to the hardware reduced costing and availability, at the same time the DBA will have big job to tune the platform in terms of manageability & performance. In this regard running ERP application such SAP product is supportable on SQL Server Enterprise edition within 32-bit and 64-bit platforms. Being SAP is another demanding application to implement in large-scale deployment by using Windows Server 2003 or even latest Windows Server 2008 can cope up the largest workloads to be processed by offering a sizeable amount of memory including SAP BW with Unicode implementations.

In this scenario running such large workloads require high-end hardware resources such as Memory, that may not be possible in handing within 32-bit platforms having the issues like frequent out-of-memory errors can occur when running large batch jobs SAP application instances. There is no alternative that you cannot optimize the code programatically for the SAP processes that are complicated in terms of ETL procesess. This is where a bit clarification on memory limit configuration is essential to understand within  32 bit platform such as 32-bit virtual address space that can be addressed to 4 GB. So taking the defaults of  2 GB or 3 GB are directly addressable by an application process whereas 1GB or 2 GB are only addressable by the operating system. This will limit the growth of taking 4GB virtual address memory cieling in 32-bit that will have a degrade process on overall performance, and increase costs since each server has a limited processing capability. Also the future of SAP deployment will be on 64-bit only, that means the upgrade of SAP platform is compulsory!

Taking into the consideration of 64-bit within SQL Server will have support of IA64 and x64 computing platforms and makes no distinction between these platforms in regard to their limitations. That will provide an increased linear address space, without requiring the use of an additional layer such as AWE. This means such query process can be assigned up to 16 GB of memory to perform joins, sorts, and grouping, leaving only a very small number of queries needing tempdb. Most queries can be performed fully in memory using the data buffers available to platform specific defaults, greatly improving performance. No doubt that pushing up the memory in a dramatic manner can reduce the I/O rate, which improves the response time by a factor of 2 to 4 and lowers investment costs for I/O hardware. 

The configuration factor within 64bit computing is 'lock pages in memory', such I refer related blog posts [http://sqlserver-qa.net/blogs/perftune/archive/2007/07/24/1132.aspx  & http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/05/03/using-64-bit-sql-server-2005-lock-pages-in-memory.aspx]. This configuration parameter step should not be used within 32-bit editions (SQL Server) in order to avoid SQL Server buffer pool pages being paged out into the Windows page file(s) one needs to allow the privilege to ‘lock pages in memory’ for the user context which starts SQL Server Services.  Also one of the Technet document refers about CPU & memory limitations between 32 & 64 bit platforms, as below:

General Memory Limits	32-bit	64-bit
Total virtual address space	4 GB	16 TB
Virtual address space per 32-bit process	2 GB (3 GB if the system is booted with the /3gb switch) 4 GB if compiled with /LARGEADDRESSAWARE 2 GB otherwise	Not applicable
Virtual address space per 64-bit process	Not applicable	8 TB
Paged pool	470 MB	128 GB
Non-paged pool	256 MB	128 GB
System cache	1 GB	1 TB

Physical Memory and CPU Limits	32-bit	64-bit
Windows Server 2003 Standard Edition	4 GB/1 to 4 CPUs	32 GB/1 to 4 CPUs
Windows Server 2003 Enterprise Edition	64 GB/1 to 8 CPUs	1 TB/1 to 8 CPUs
Windows Server 2003 Datacenter Edition	64 GB/1 to 32 CPUs	1 TB/1 to 64 CPUs

(Source Technet)

As there are more tools defaulted to 32 bit it is bit confusing for the software applications that will support X64 based installations. IN the same manner it is a common question out in the newsgroups referring to which types and versions of SQL Server can be installed. Say for instance on X86 based system SQL Server 2000 or 2005 can be installed which is by default a 32-bit application. So for x64/EMT64, and IA64 servers it is bit confusing and also with evolution of SQL Server 2005 that has different types of installations than SQL Server 2000, it is more complex for beginners in SQL world!

You may be aware the binary type of installation within SQL Server 2000 is 32-bit and 64-bit, keeping apart the edition you need to deploy. By default this version of SQL Server can be installed on x86 and even on on x64/EMT64 with an exception fo running as a 32-bit application. Ony the possibility of X64 SQL Server 2000 installation is possible on IA64 based platform.

Coming to the latest version SQL Server 2005, keeps out 3 types of installations. You can install it on x86 or x64/EMT64 as a 32-bit application, on x64/EMT64 as a 64-bit application if you’re running an x64/EMT64 Windows operating system, and on IA64 as a 64-bit application.

We have most of our database systems on 64-bit environment, but one of the application user asked the question what is the advantage of a 64-bit environment in relation to SQL Server application. 

I would say with the evolution of SQL Server 2005 it has opened many more differentiation between 32- and 64-bit editions and bit hard for the application to deploy the 64-bit edition among those who would benefit most from it. Basically if your database system such as Data Mining or Data Warehousing application will take utmost advantage from a 64-bit platform. For me nothing is different between 32 or 64 bit systems, as in X64 it is only memory advantage. The Development team within Microsoft compiled and optimized the 64-bit edition specifically to run on a 64-bit Windows Server which is default requirement of having 64-bit processor. So having a system such as SQL Server 2005 (and Windows Server 2003 or going to Windows Server 2008 in next year) both run on the 64-bit x (Intel/AMD) and the Itanium (Intel-only) platforms. So here the important part is played by the hardware to take such an advantage of performance with huge-pool of resources and as said itis the first main advantage 64-bit provides for SQL Server is the ability to directly deal with larger amounts of memory.

In relation to the advantages list I would like to refer the documentation about the advantages of 64-bit environments for SQL Server which relates to the  AP's Advance Planning and Optimization with SQL Server. As referred above Analysis Services (OLAP based applications) can be one of the most memory-hungry applications around, especially if you're working with databases several gigabytes in size or processing data cubes with many dimensions. Now the consolidation of servers is an important bit within the Organisations, so having such restricted compatability using 64-bit server will reduce the memory barriers that existed before are gone, these apps can run side by side on the same machine and still show off a boost in performance that they couldn't have before, don't take it granted and always test your applications in real-time.

As 64-bit is huge-pool of memory having large number of concurrent users to the application is no more a problem, with this more physical memory that can be addressed, the greater the number of concurrent user connections can be sustained without breaking a sweat. So if you have been asked to migrate to 64-bit do not hesitate to say 'YES' as such a migration of environment to the 64-bit SQL Server will still require a good deal of planning and work. Not to mention about ETL process completion, using the legendary DTS methodolog system from SQL Server 2000 and before has not been ported to 64-bit environments. SSIS is a replacement for DTS and will work with SQL engine within a 64 bit application that will be significantly more powerful and flexible. So it is better to get more practice with SSIS so these packages can be converted or rewritten before the 64-bit leap.

I had tough time working with Integration Services (SSIS) on one of the 64 bit machine that will be used for a 24/7 based application. So here goes my ranting and things you need to take care when having installation of SQL Server 2005 on 64 bit platform, specially within ETL processes completion.

So at first, when you install SQL Server 2005 and select both Integration Services and Business Intelligence Development Studio, as this it to ensure to have both 32 & 64 bit type of components installed and bear in mind there are no 64 bit tools available and you can make use of available 32 bit tools against 64 bit server instance. So with above it will install all available 32-bit and 64-bit design-time and run-time Integration Services features are installed, together with support for SQL Server 2000 DTS packages. By default all the binaries for SQL Server will be installed under the Program Files directory, and for 32-bit features are installed separately under the Program Files (x86) directory.

So talking about BIDS (Development Studio) which is a 32 bit application for SSIS/SSAS/SSRS type of programs is not supported on a Itanium 64 bit platform and will not be installed altogether. So you have to install a seperate component for DTS aka IS tools that have 64-bit versions are dtexec.exe, dtutil.exe, and the SQL Server Import and Export Wizard, DTSWizard.exe from Microsoft Downloads site.

Just a bit of advice on developing or testing 32 bit application on a 64 bit computer, always remember that this 32-bit tool is running packages in 32-bit mode and in order to complete your 64 bit text you should have 64-bit mode by using the 64-bit version of dtexec.exe before you deploy or schedule them on a production server. This brings up another issue of having SQL Server Message Queue task, Books Online refers that : if you have installed 64-bit SQL Server on a 64-bit computer, you can use the Message Queue task only in packages running in 64-bit mode; you cannot use the Message Queue task in packages running in 32-bit mode. So this will leave you an option with having 32 bit support on Message task and not with 64 bit.

Still I would like to quote the reference from Updated Books Online about limitations on the packages during design & run time. As usual you will not be able to design packages within BI Development Studio on Itanium-based operating systems, as said above. This still applies to the debug of scripts using the Scripts task in SSIS on a 64 bit platform. If you have got 64-bit provider then ensure to configure the connection manager where 32 bit tools are installed, this will not affect execution of the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True.

For RunTime gotchas, by default you will not be able to execute the package that will have Script Tasks or Script components. They must be recompiled under the 64 bit platform of SQL instance as the value of the PreCompile property is True for both the Script task and the Script component. Make sure you have downloaded the 64 bit .NET framework data providers including the OLEDB providers that are specific to 64 bit only. By default again the SQL Server 2000 DTS runtime is not available within 64-bit version.

Very frustating isn't it, this is where Microsoft compels the users to go towards SSIS when you have magic of 64 bit and 32 bit platform within your enterprise. So piece of advice, do not mix the database between 32 & 64 bit platforms as ETL processes such as DTS & SSIS will not work seamless unless you take care of such compatible version of tools.

I have had problem when installing SQL Server 2005 on one of the 64-bit server, the error was:

There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information.

So as per the message I have looked in to the specified directory for the logs which is "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files" and only error I could see is:

Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation. Source File Name: packageengine\installpackageaction.cpp

Eventually found that the media which is used to install SQL Server server components is corrupted, the Operations support is trying to install the server components using tools media. This problem occurs because the folders that contain the files that are required during the installation of SQL Server 2005 do not have a correct layout.

Further I recommend to download and install the "Microsoft .NET Framework Version 2.0 Redistributable Package (x64)" before you proceed to install SQL Server software. ToDownlad the .NET version fo X64.

We have had a request from one of the solutions company stating they have to mirror 15 SQL Servers that are dispersed across the European locations to a central location. For this they have deployed the X64 environment for operating system and SQL Server application too. So now the question is will there be any limit on the number of databases to involve in this type of setup. Each database will not grow beyond 25GB in next 3 years and few of them are 24/7 type of applications within minimum database maintenance window.

As usual having number of databases within a Database Mirroring session depends on the database size and their usage, storage size and features to use those databases for reporting purpose. The general rule of thumb by Microsoft set as 10, but this is more or less applicable for a 32 bit environment due to the amount of memory and resources availability. Based on the resource (memory and threads) utilization by database mirroring, it is a best practices recommendation to mirror not more than 10 databases in one instance. The recommended limit of 10 is only approximate and not an exact number. The limit can vary depending upon your application and workload, this is where it differs on the X64 environment. Also within this DB Mirroring session ensure to have a setup of same kind of operating system including the SQL Server version in addition to the service packs & hotfixes. 

Within my experience there are few additional considerations in this regard:

• What kind of high availability option you need to have for the database that are spread across the geographical locations.
• Check the recommendations of the product that owns these databases. If you are using any third party tools other than in-house developed applications, in case to support failover of servers in case of any issues.
• If there is any dependancy to few databases or interrelated databases, be aware that database mirroring works at the database level and does not ensure that a group of related databases will all failover at the same time.
• When working with multiple related databases you probably want to be in control of the failover, failing over all databases at the same time in some manual (or at least not the built-in automatic) way. Ensuring transactional consistency across those databases across failovers is beyond database mirroring's built-in guarantees.
• Further reference on the DBMirroring BestPractices whitepaper.

Can we get install SQL Server Express 2005 edition on X64 environment? Yes and No.

Yes for SQL Server Express on X64  and EMT64 that too in Windows on Windows (WOW), but No it is not supported on IA64 systems. This enhancement is enabled since the service pack2 release of SQL Server Express 2005 edition.

So on the above enabled X64 platforms if you are developing any applications then looking to submit the bugs then refer to Connect Feedback Center and above all don't forget to RegisterHere your copy of SQL Server.

Is 64 bit means more memory? Yes, to be precise.

Microsoft has been investing heavily on X64 bit and within couple of years there will be no more development on 32-bit applications, as per the recent road map.

So within 32-bit application it is one of the reason that memory has been a constraint on scaling-up server capacity. Once you consider that on a Windows system or any application, half of this is reserved for the system address space (aka PAE  with /3GB switch is used in boot.ini) the remaining private address space for each process will get hit even it is a small one to compare, having load on virtual memory. So to answer to this problem of memory addressing is 64-bit computing, which allows much more memory to be addressed. On the other hand a downside is that new servers and a switch to a 64-bit operating systems are required (if not now in future).  As you may have seen 64 bit investment within Windows operating system, SQL Server, Visual Studio applications and so on. A simple table to compare the 32 vs 64 bit resources:

So with this enhancements there is a fight between 2 major chip-makers (not food based) such as Intel & AMD. You may be aware that AMD beat Intel to market last year with 64-bit x86 chips and with dual-core chips, with this Intel has come out with a roll out of 45-nanometer technology, which promises to use less power and generate less heat while delivering greater performance.

That means having 64 bit system means all your problems are solve?

Having higher resources is always helpful and due to the nature of Windows Server that is a complex system and performance is constrained by the component with the lowest performance. So if the problem persists best option is to identify the bottleneck at first going through an issue of memory bandwidth. As memory plays major role in how fast data can be moved to and from memory and written to disks. Then comes to the latency (speed) to cater those data needs, that leads to I/O performance about how much data must be moved by inserting or updating.  

As usual for every performance issue there are many considerations that need to be evaluated when determining how to investigate the root cause for slower performance of many SQL Server databases. I would suggest that an important consideration is whether to use a 64-bit platform or a 32-bit platform to get the best performance results, though 64-bit platform offers some advantages over the 32-bit platform in this regard.

When it comes to hosting databases on 64-bit usage of heterogenous workload is quite common with different I/O characteristics, the general recommendation is not to join such multiple workloads (non-streamlined) within the same environment without a testing that too concentrating on how share I/O subsystem caters the system resources. Taking into consideration the following systems:

One advantage with 64-bit is you need not to set any settings such as AWE as compared to 32 bit. But an important measure is CPU utilization, and getting the best performance ROI as well as increased throughput. The context switching is a vital to the overall performance of your SQL Server, as it takes care the operating system or applicaiton by forcing to change the executing thread on one processor to another thread executed on another processor. The higher the CPU usage is, or I/O contention, the higher the context switching, and ultimately lower throughput. The number of context switches should be as small as possible. The worst thing that can occur is when you have your system resources dedicated to excessive context switching, than doing the actual work required by SQL Server, that leads to parallelism due to the limitation of single process for SQL Server.

Further there is a KBA (906892) states that :

When you run these tools on 64-bit operating systems, the tools run in the Windows on Windows (WOW) environment. Under certain conditions, you may experience slow performance when you run these tools.

To improve performance, run these 32-bit tools on a computer that is running a 32-bit operating system. Then, connect to a 64-bit server that is running SQL Server.

Microsoft SQL Server 2005 Business Intelligence Development Studio is not designed to run on the IA-64 64-bit architecture and is not installed on IA-64-based servers. SQL Server 2005 Business Intelligence Development Studio is the 32-bit development environment for business intelligence solutions. If you have 64-bit (x64) management tools, SQL Server Integration Services, and SQL Server Notification Services installed as part of a 64-bit instance of SQL Server 2005, you cannot install 32-bit versions of the same components in a side-by-side configuration.

To get more insight for such performance soon I will be performing few tests within the 64-bit (pre-production) environment, meanwhile refer to the latest updates from 64BitOverview.

Format: asp
Duration: --:--

What advantages we took when deployed the 64-bit computing within SQL Server

As you aware 64 bit computing with 64-bit processors have become the standard for systems ranging from the most scalable servers to desktop PCs. The way to take full advantage of these systems is with 64-bit editions of Microsoft Windows products.  Same way SQL Server has evolved in using 64 bit computing from SQL Server 2000 version onwards, so what improvements you can take advantage with this technology. For me it is with ETL to extract, transform, and load processes, they have coped with large data integration by conforming and loading the data into the target server in multiple phases.

Though you can say this was well manage with usual 32 bit editions, but there is a stage where the import of data is huge in terms of data warehousing database performs all the aggregation, sorting, and other operations, potentially contending with other equally important queries on the database server that use the common resources. As data volume grows with increasingly complex aggregations, memory pressure on the database server rises, causing data to be swapped out to disk when large data sets cannot fully fit in the memory.

So with 64 bit technology Integration Services (SSIS) has been evolved to confront all the issues and also be able to do all the aggregation and sorting of the entire data set in the memory, eliminating the penalty of having to swap out to disk. Being able to load larger data sets in direct addressable memory not only optimizes throughput, but frees up the database engine to serve other equally important queries and workloads. Similarly the performance speed during the aggregation and processing the large amounts of OLAP data is very much helpful in using with 64 bit editions, so with large scale query increases the requirement of calculations and processing data more than 3GB in size with concurrent users accessibiliy.

Similarly contention and deadlocks has improved better with SQL Server 2005 snapshot isolation eliminates these challenges and radically improves performance by allowing larger quantities of data to be processed by the query in memory. SQL Server 2005 (64-bit) servers run extremely large queries concurrently with optimal results, which is an important benefit for us to manage data warehousing. Same as with snapshot isolation, partitioned tables partitions can then be accessed independently, decreasing the performance impact arising from I/O bottlenecks when accessing large amounts of data (especially data sets larger than 3 gigabytes) from these tables. Because of memory-addressing limitations of 32-bit database instances, extremely large partitions had to spread across multiple instances of SQL Server. With 64-bit technologies, the memory addressability limitation within a SQL Server instance is eliminated, providing demanding workloads to keep the partitions within the same server or instance, and processing the data in memory without the limiting the impact of I/O.