SQL Server 64-bit Migration Benefits
Limitations of the 32-Bit Platform.
The x86 platform has been the mainstay processor for PC architecture
from the inception of the Personal Computer. However, the 32-bit has limitations,
mainly, the 32-bit processor is limited to a maximum of 4GB of addressable memory.
For many of today’s memory hungry applications, 4GB is simply not
enough.
Database applications such as SQL Server derive definite benefits from
larger memory address spaces.
The way the 32-bit Windows OS allocates memory actually exacerbates the
addressable-memory limitation.
Under 32-bit Windows, 2GB is reserved for the OS which leaves only 2GB
to run all the applications.
Microsoft developed a technology for Windows 2000 Server - Address
Windowing Extensions (AWE) - designed to help circumvent this problem by
letting AWE compliant applications access up to 8GB of RAM on Win2K Advanced
Server (Win2K AS) and 64GB of RAM on Win2K Datacenter Server.
However, AWE has limitations as well. Applications must be specially
written to take advantage of AWE.
Also, AWE’s paged memory access scheme imposes overhead without providing
any benefits to the OS.
How does 64-bit
Help.
The 64-bit platform solves these problems.
The maximum addressable memory using 64-bit architecture is increased to
1TB.
And, unlike the AWE implementation, both the OS and the applications can
use this memory.
In addition, in 64-bit editions of SQL Server, not only the buffer cache
but also the procedure cache and other SQL Server mechanisms can use the memory.
The larger 64-bit address space lets the system move and process
essentially twice as much data per CPU cycle as the older 32-bit design.
Effectively doubling the raw data processing power can have a
significant impact on data-centric applications such as SQL Server.
Microsoft Support
for the 64-bit Architecture.
The first official Microsoft support for the 64-bit platform came with
the 2005 release of the 64-bit editions of Windows 2003 Standard, Enterprise
Internally, Microsoft had begun a trial deployment of the 64-bit
platform using a pre release version of Windows 2003 64-bit for the Web servers
that comprise its www.microsoft.com site.
Microsoft reported several significant performance benefits resulting
from the migration to the 64-bit systems.
According to Microsoft’s internal performance measurements, the CPU load
on the servers decreased by 50 percent.
In addition, page response times for some applications were reported to
be more than 15 times faster.
For more information about the Microsoft 64-bit deployment, go to
Microsoft also used the improved scalability that the 64-bit
architecture provided in the Microsoft Treasury project. The Microsoft Treasury
manages the financial assets and foreign currency exposure for Microsoft.
In this instance, the Microsoft Treasury reduced the time required to
run its risk-analysis software from 40 hours on the 32-bit platform to 13 hours
on the 64-bit platform, a 325 percent increase in overall system performance.
This improvement required no code upgrades and resulted from the 32-bit
applications’ greater memory use and the more efficient system architecture.
For more information about the Microsoft 64-bit Treasury project, go to
A number of Microsoft partner projects also demonstrate the scalability
and performance improvements that the 64-bit architecture enables.
CROSSMARK, a point-of-sale (POS) data warehouse, implemented its 3TB
data warehouse on the 64-bit platform using SQL Server 2005 and achieved a 12-fold
increase in data-loading capability with 64-bit Integration Services.
Bank Leu, a leading Swiss private bank replaced its management account
system with a solution based on the SQL Server 2005 Enterprise Edition 64-bit
and achieved better system responsiveness that resulted in a 40 percent
improvement in management account productivity.
Windows 2003
Standard, Enterprise
As you might expect, the 64-bit editions of Windows 2003 can take
advantage of the increased system capabilities that the 64-bit platform
provides.
Windows 2003 32-Bit vs. 64-bit Editions
|
||||||||||||
|
By increasing the available system memory, the 64-bit platform solves
the virtual memory limitations that have restricted the scalability of the
32-bit versions of Windows Server.
The 32-bit version of Windows Server is limited to 4GB of addressable
memory split evenly between the OS and applications.
However, this change also reduces the amount of memory available to the
OS, which can result in performance degradation for kernel-intensive
implementations.
Memory Availability in Windows 2003 32-Bit and
x64 Editions
|
|||||||||||||||||||||
|
The 64-bit x64 architecture increases the amount of virtual memory that
Windows can address from 4GB to 16TB.
As is true of the 32-bit version of
Windows, the virtual address space is split evenly between the OS and the
applications (i.e., between kernel mode and user mode).
The net result is that
native 64-bit x64 applications have 8TB of available virtual memory address
space.
SQL Server 2005
Standard and Enterprise Editions.
SQL Server 2005 32-Bit and 64-Bit Compatibility
|
|||||||||||||||
|
Both the SQL Server 2005 Standard Edition and the SQL Server 2005
Enterprise Edition provide native support for the x64 architecture, which means
that they must run on the 64-bit Windows 2003 x64 OS.
The SQL Server 2005 Express Edition and the SQL Server 2005 Workgroup
Edition are 32-bit only.
However, they can use the WOW64 subsystem to run on the 64-bit Windows
2003 x64 edition.
SQL Server 2005
64-Bit Advantages.
The architectural advantages that 64-bit computing offers can be
particularly beneficial to several application workloads.
The server applications that probably derive the most benefit from
running on a 64-bit platform (i.e., a 64-bit server running a 64-bit OS) are
relational database and business intelligence (BI) workloads.
The servers that run these workloads often process very large data sets
and benefit greatly from theadded memory capacity and improved I/O bandwidth
that the 64-bit platform offers.
To take full advantage of the 64-bit hardware to run one of the 64-bit
editions of SQL Server 2005, you must have a 64-bit OS installed.
64-Bit Relational Database Benefits.
A primary reason for the improved scalability is an increase in
available physical and virtual memory.
Relational databases such as SQL Server 2005 must often execute queries
and other SQL statements that work with very large amounts of data. The larger
memory address space that 64-bit processors provide lets SQL Server process
larger datasets in memory—thereby avoiding expensive and slow I/O operations.
Disk access is always the slowest operation that relational database servers
perform. (RAM access is hundreds of times faster.)
Increasing the server’s available memory lets it process a greater
portion of its workload in memory, which significantly reduces the number of
times it needs to perform disk I/O
The 32-bit architecture is limited to a maximum of 4GB of memory.
Although that amount might be adequate for most desktop applications or
smaller scale databases, 4GB isn't adequate to support larger relational databases
applications.
Also, running on 32-bit Windows, the actual addressable memory for
applications (e.g., SQL Server) is only 2GB; the other 2GB is reserved for the
OS.
Microsoft developed a technology called Address Windowing Extensions
(AWE) that lets AWE-enabled applications access up to 64GB on Windows
Datacenter Server. Many SQL Server installations have adopted AWE because of
the increased memory support that it provides to SQL Server.
However, AWE is restricted because it:-
- has no OS benefits - Only applications can access AWE memory. It does nothing to let the OS access more memory.
- requires specially written applications - Only AWE-enabled applications can take advantage of the increased memory. Other applications get no benefits.
- incurs performance overhead - AWE uses a paged memory access scheme whose performance is inferior to that of typical flat memory access.
- During checkpoint intervals, databases take up processor time; any overhead to the processor (e.g., AWE) will only slow down system performance
- offers limited SQL Server benefits - Even though SQL Server supports AWE, only specific areas (e.g., the buffer cache) can use the increased memory AWE provides.
Other areas (e.g., the procedure cache) receive no benefits from AWE.
Likewise, other subsystems (e.g., Analysis Services, Integration
Services, Reporting Services) aren’t AWE-enabled and receive no benefits from
AWE.
Running on Windows 2003 Datacenter, Enterprise ,
or Standard editions, the 64-bit processors let SQL Server 2005 Enterprise and Standard
editions break through the 4GB memory ceiling that the 32-bit version of
Windows imposes.
The increased memory capacity
lets SQL Server 2005 handle larger databases and more complex application requirements
Database Performance Enhancements.
Queries submitted to the database engine derive substantial benefits
from the increased memory available for in-memory buffer caching.
Extended plan caching benefits statement execution.
Index creation is improved by allowing larger in-memory data sorting.
The increased memory also lets various types of sort conditions execute
more efficiently.
Supports a higher maximum number of concurrent users.
Processor parallelism can better support multi threaded workloads.
Faster Query Performance.
64-bit computing offers a larger addressable memory space, which SQL
Server 2005 can use to increase the size of its buffer cache.
The increased size of the buffer cache is probably the single area that
benefits SQL Server most. (The need for buffer cache space is the reason that
32-bit AWE support focused on increasing the available buffer cache size.)
The SQL Server relational database engine’s buffer cache contains
recently used data as well as read-ahead data that the SQL Server engine
anticipates using.
When the SQL Server relational database engine executes a query, it
first looks in the buffer cache for data to satisfy that query.
The larger buffer cache lets SQL Server store more data in and pull more
data from the buffer cache, which helps eliminate the need for disk I/O.
The more memory the buffer cache has available, the more effectively the
SQL Server caching mechanism works.
The net result is less disk I/O and faster query performance.
Faster Statement Execution and Improved Engine Efficiency.
With the 32-bit platform, AWE support was limited to the buffer cache.
With the 64-bit platform, many system components can benefit from the
added memory that the platform makes available. One of the most important of
those components is the SQL Server procedure cache.
Whereas the buffer cache contains data, the procedure cache contains
query plans for recently executed T-SQL statements and stored procedures.
Because of the dynamic nature of database requests, old execution plans
must be continually deleted from the procedure cache.
The greater memory capacity of the 64-bit architecture lets SQL Server
create a larger procedure cache and hold more execution plans. The increased
number of execution plans enables faster statement execution for cached plans
because no additional time is required to recreate the query plan.
Finding more execution plans in the cache reduces demand on the CPU
because fewer new execution plans need to be compiled.
Faster Indexing.
SQL Server supports clustered, non clustered, and full-text indexes.
The creation process for all of three types of indexes benefits from the
expanded addressable memory that 64-bit processors.
When an index is created, the column data that comprises the index is
brought into memory and sorted.
The increased amount of available memory lets more of the sort be
performed in memory without the need to write intermediate results to disk.
This increase in available memory is even more important for full-text
indexes because they typically use much more data.
Microsoft customer deployments of full-text searches on the 64-bit
platform have shown tremendous gains when rebuilding full-text indexes.
Faster SQL Operations.
Hash joins, aggregates, sorting, and server-side cursors are all SQL
operations that can derive significant benefits from the 64-bit platform’s
added memory.
Hash joins are fast and highly memory-intensive. More available memory
lets the system execute hash joins more freely rather than being forced into
slower but less memory-demanding execution plans.
Aggregates and sorting perform significantly better with more working
memory because they often use temporary storage to process large data sets.
Because temporary storage in turn requires disk I/O, you can drastically
increases the time and system requirements needed to satisfy the operation.
Server-side cursors are memory and system resource intensive operations
that can benefit from a move to a 64-bit platform.
Support for More Concurrent Users.
SQL Server must also allocate system memory for each open database and
each user connected to the system.
Because the number of databases is finite and typically a known
quantity, database allocations are seldom an issue.
However, that’s not always the case with user connections especially for
systems that service Web applications for which the number of concurrent user
connections can be enormous and isn’t predictable.
Each user connection requires the system to create a user context that’s
stored in memory.
The increased memory capacity of the 64-bit processors lets the system
create more simultaneous users contexts thereby increasing the number of
concurrently connected users the server can support.
Greater System Throughput.
The enhanced 64-bit architecture lets more data move through the system
in shorter periods of time, which increases system scalability.
The performance increase improves the system’s ability to perform
parallel processing, which is used effectively in simultaneous data loading
from multiple data sources as well as in parallel database backup and recovery
operations.
Improved Availability.
When implemented as part of a high-availability Microsoft Windows
Clustering Services solution, the x64 platform can also improve availability.
The greater 64-bit system performance reduces the time to complete a fail-over operation, making the system available again sooner.
64-Bit Business Intelligence Advantages.
BI technologies such as OLAP, extraction, transformation, and loading
(ETL), reporting, and data mining must be able to efficiently process,
aggregate, and query very large data sets.
Servers running these BI workloads must process large amounts of data
and can benefit tremendously from the higher processing power, increased memory
capacities, and improved I/O bandwidth that the x64 platform offers.
SQL Server 2005 x64 editions and BI features
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Only Enterprise
and Standard Editions are native 64-bit applications. The Work-group and the
Express Editions are 32-bit applications that run in the Windows on Windows64
(WOW64) subsystem that the Windows Server 2003 x64 editions offers to provide
64-bit support.
Reflecting their small business orientation, Workgroup and Express
editions offer limited support for CPUs and RAM.
Because the BI features that SQL Server 2005 provides are primarily
oriented toward mid-size and large businesses, they’re available in the Enterprise and Standard
Editions.
In fact, Workgroup and Express Editions contain just two BI features:
the SQL Server Integration Services Import/Export Wizard and Reporting
Services.
Analysis Services, the core SQL Server Integration Services designed for
ETL transfers, and Data Mining only comes with Enterprise and Standard Editions.
Enterprise Edition also ships with several advanced BI functions.
These functions include proactive caching, advanced data management
(which supports partitioning cubes), and the ability to scale out Reporting
Services servers.
The BI features in the x64 editions of SQL Server 2005 are native 64-bit
applications fully able to take advantage of the enhanced x64 platform.
64-Bit BI Benefits.
The 64-bit platform lets SQL Server 2005’s BI subsystems in the Enterprise and Standard
editions achieve higher levels of scalability than was possible with the
previous 32-bit editions of these features.
The increased scalability is possible primarily because of the boost in
computing power and the increase in available memory (up to 1TB).
Below is a summary of the 32 bit Limitations, followed by some of the
areas in which these BI features specifically benefit from the 64-bit computing
architecture.
32-Bit BI Limitations.
Unlike the 32-bit SQL Server relational database engine, which uses
(AWE) to boost the internal capacity of its buffer cache and thereby relieve
some of the 32-bit memory constraint.
Analysis Services and the other BI subsystems ( Integration Services
& Reporting Services) were never AWE-enabled.
Therefore, the 32-bit SQL Server BI components ran under the 32-bit
Windows 4GB memory ceiling.
By default, this 4GB of memory is split evenly between the Windows OS
and the applications.
Realizing the constraints that the memory ceiling could place on
applications such as the SQL Server BI stack, Microsoft developed a way to tune
Windows’ allocation of the available 4GB of memory address space.
For certain versions of the Windows Server OS, Microsoft added a /3GB start-up parameter that let users shift the balance of RAM in favour of the
applications.
Using this switch with Windows start-up lets Windows devote 3GB to the
applications, leaving 1GB for the Windows Server OS. Although this switch can
benefit the applications, it’s a doubled edged sword—giving memory to the
applications but taking it from the OS.
Workloads that stress the Windows
kernel face the danger of becoming memory constrained, which can severely
degrade overall system performance.
Even in situations in which this workaround succeeds in improving
overall system performance, it still only adds 1GB of memory for application
use.
Analysis Services Performance Enhancements.
The 64-bit editions of Analysis Services 2005 provide significantly
enhanced performance for aggregation and processing of large sets of OLAP data.
The ability to process much larger amounts of data in memory without the
need to perform the much slower I/O required to use temporary disk files
significantly improves performance over the 32-bit editions of Analysis
Services.
As the data sets increase in size, the benefits of 64-bit computing grow
substantially. The increased memory that the x64 editions of Analysis Services
provides can support larger cubes, improve performance for processing large
dimensions, and process cubes faster.
The greater processing power of the 64-bit Architecture provides better
support for parallel processing operations both for simultaneously processing
cubes and for accessing partitioned cubes.
The 64-bit architecture’s increased memory support also enables a higher
maximum number of concurrent users. And the more powerful and scalable 64-bit
systems allow us to implement server consolidation to an extent that isn't possible with 32-bit Analysis Services.
Below is a list of how the 64-bit architecture benefits each of these
areas in more detail.
Support for Very Large Cubes.
The extended data cache reduces the need for physical I/O to satisfy
queries.
The increased memory capacity is especially useful when you have cubes
with many fact tables, partitions, and large numbers of aggregates.
Huge Dimensions Supported in Memory.
The memory capacity of the 64-bit platform allows Analysis Services to
better handle very large dimensions, which might include millions of members.
The increased memory is especially crucial when you use custom security
in Analysis Services.
Custom security caches a separate copy of the dimension for each custom
security role assigned.
(For 32-bit implementations, the memory required is an extremely
significant concern.)
The increased memory that 64-bit architecture offers lets it effectively
handle large dimensions and dimensions that use custom security roles.
Faster Query Processing.
Much as the SQL Server relational database engine uses its buffer cache
to store recently used query results, Analysis Services’ data cache stores
recently used query results.
Because of the dynamic nature of the data cache, older query results are
continually being expelled from the cache. When Analysis Services resolves user
queries, it first attempts to use the data in its data cache to respond to the
query.
It either retrieves the specific data set from the cache or attempts to
create the requested data set from other data already present in the cache.
If
the query can be addressed using cached data, it’s referred to as a warm cache query. If the query can’t
be satisfied by the cache and I/O is required to resolve the query, it’s
referred to as a cold cache query.
The 64-bit architecture’s expanded memory capacity lets Analysis
Services maintain a much larger data cache; the result is a higher percent of
warm cache queries that don’t resort to physical I/O.
Faster Cube Processing.
The additional memory capacity of 64-bit architecture also enables faster
cube processing.
Analysis Services cubes consist of data aggregations; these aggregations
don’t take place in real time. Instead, the cube data must be periodically
refreshed by “processing the cube.
The greater memory available
reduces the need to write temporary files to the disk subsystem while processing
the cube data.
With the reduced need to perform disk I/O, cube processing can be
performed much faster and therefore more often as well.
Faster Parallel Processing and Querying.
The Direct Connect Architecture also offers faster simultaneous cube
processing and data querying.
The typical data mart or data warehouse contains numerous cubes that
must be reprocessed periodically. In many cases, some cubes must be refreshed
while other cubes remain in active use.
The increased memory capabilities of the 64-bit architecture allow the system
to more effectively process cubes while simultaneously satisfying ongoing
end-user query requests.
Larger Number of Concurrent Users
Like the relational database engine, the 64-bit architecture’s larger
memory capability lets the server support more simultaneous user contexts and a
higher number of connections.
Each user connection requires a set of data structures that maintain the
state of each connection to the server. The larger addressable memory space increases
the number of active user connections that a server can support.
Increased Performance Using SQL Shared Memory Provider.
Although the 64-bit platform’s larger address space provides clear
benefits for several internal areas of the SQL Server relational database
engine and for Analysis Services, one area in which the benefits are not so
obvious is server consolidation.
Co-hosting the SQL Server relational database engine and Analysis
Services on the same server is not usually recommended because either entity
can require most if not all of the available memory on a 32-bit server.
However, the massive increase in headroom provided by the 64-bit
platform make it possible to consolidate your SQL Server relational database
instance with Analysis Services.
|
||||||
|
Running the SQL Server relational database engine and Analysis Services
on the same system lets you use the Shared Memory provider to connect Analysis
Services cubes to the SQL Server relational databases.
Unlike the TCP/IP or Named Pipes client protocols, Shared Memory is an
in-memory provider that doesn’t need to drop down into the system networking
layers to make the connection between Analysis Services and SQL Server.
This in-memory capability supports the fastest possible connection between
Analysis Services and SQL Server because they run on the same system.
The connection is many times faster than the networked connection
required when Analysis Services and the SQL Server relational database engine
reside on separate systems.
Integration Services Performance Enhancements.
SQL Server Integration Services (SSIS) performs ETL tasks for SQL Server
2005.
Like the 32-bit Analysis Services, the 32-bit SSIS can’t take advantage
of AWE and is confined to the 2GB or 3GB address space that the 32-bit editions
of Windows provide.
The increased memory that the 64-bit platform offers improves overall
SSIS performance significantly.
The access to increased memory capacities vastly improves the
performance of memory-intensive tasks such as row-by-row calculations, lookups,
aggregation, and sorting.
The additional working memory lets SSIS do more work in memory, reducing
the need to perform intermediate disk reads and writes.
Additionally, SSIS’s multi-threaded parallel data loading ability lets it
use the greater system throughput.
The ability to work with larger data sets in memory not only speeds up
the specific task but also helps free up server resources to be applied to
other workloads.
Improved Performance for Row-by-Row Calculations.
The 64-bit platform provides improved performance for SSIS tasks that
execute operations row by row.
Examples of such tasks include performing lookups, creating calculated
columns, and executing data and character conversions.
The enhanced power of the 64-bit platform allows performing these
operations more rapidly, and the increased memory capacity lets SSIS bring more
rows into working memory, enhancing the query performance for applications that
use these row by row functions.
Improved Performance for Aggregations.
The increased memory that the 64-bit platform provides vastly improves
performance for tasks that perform aggregation and sorting.
Aggregation and sorting require manipulating large amounts of data at
the same time.
Such operations perform significantly better with more memory because
the data sets involved can consist of thousands or millions of rows and are
usually too large to fit into memory.
To process these tasks, SSIS must use temporary storage to store
intermediate results.
Using temporary storage requires disk I/O, which increases the time
needed to perform the task.
More memory lets more data be manipulated at one time, reducing the need
to perform disk I/O to store intermediate results.
Faster Loading of Multiple Data Sources.
SSIS is a multithreaded application capable of performing parallel
processing by simultaneously loading data from multiple data sources and
writing data to multiple targets.
The 64-bit architecture
can support much higher system throughput, which SSIS parallel operations use
effectively.
Reporting Services Performance Enhancements.
Reporting Services also benefits from 64-bit computing.
Like Analysis Services and Integration Services, Reporting Services
can’t use AWE support.
However, the 64-bit SQL Server 2005 Reporting Services can take
advantage of the increased memory available on the 64-bit architecture to
process and render complex reports.
Faster Report Rendering.
Using the CLR’s dynamic memory management, the 64-bit edition of
Reporting Services can access all physical memory available to the CLR,
resulting in performance improvements for rendering large and complex reports.
64-Bit Database Application Development.
SQL Server 2005 x64
T-SQL Application Development.
DBA’s and T-SQL Database Developers perform common development tasks
include creating and maintaining database objects (e.g., tables, views, stored
procedures, triggers, user-defined functions). T-SQL scripts perform the vast
majority of these tasks.
This type of development T-SQL for 32-bit SQL Server is 100 percent
compatible with the native 64-bit platform.
Existing T-SQL scripts function on the 64-bit editions of SQL Server
2005 exactly as they do on the 32-bit editions.
Microsoft redesigned SQL Server in 1998 knowing that 64-bit processors
would be an option for future releases of SQL Server. The company designed all
of SQL Server’s on-disk structures to accommodate 64-bits. This design ensured
that SQL Server would be completely 64-bit compatible, and allows us to freely
move SQL Server database objects between 32-bit and 64-bit systems.
In addition, the tool-set you use to develop and run T-SQL scripts on the
32-bit editions and the 64-bit editions of SQL Server 2005 is exactly the same.
In both cases, you use SQL Server Management Studio to manage the server
system and the Query Editor to create and execute T-SQL scripts.
Note that the SQL Server 2005 tool-set is completely platform agnostic.
You can use the 32-bit SQL Server Management Studio and Query Editor to
manage and develop T-SQL scripts for both 32-bit and 64-bit editions of SQL
Server 2005.
Likewise, you can use the native 64-bit version of the SQL Server Management
Studio and Query Editor to manage and develop T-SQL scripts for both 32-bit and
64-bit editions.
The capabilities and features in each edition are identical.
32-bit and 64-bit .NET Framework Compatibility.
This compatibility ensures that .NET Framework developers can realize
the benefits of 64-bit computing by leveraging their investments in existing
32-bit .NET Framework code.
In most cases, source-code compatibility between the 32-bit and the
64-bit editions of the .NET Framework is close to 100 percent.
However, the following areas are among the few in which the 32-bit and
64-bit editions of the .NET Framework differ:
Floating Point calculations.
The IEEE 754 standard for floating-point arithmetic allows for different
results for certain floating-point operations on different platforms (e.g.,
32-bit and 64-bit platforms).
Pointer size.
Thirty-two-bit platforms have 32-bit pointers (4 bytes) whereas 64-bit
platforms have 64-bit pointers (8 bytes).
Memory alignment.
Types are aligned on boundaries of the natural lengths of the data type.
32-bit applications use 1-, 2-, 4-, and 8-byte alignments;
64-bit applications use 1-, 2-,4-, 8-, 10-, and 16-byte alignments.
If items are longer than 8 bytes, the 64-bit version of the.NET
Framework aligns them on the next greater boundary.
Cross 32-bit/64-bit application development involves some additional
considerations.
For example,
64-bit .NET processes can’t load 32-bit DLLs or 32-bit COM in-processing
(Inproc) servers.
32-bit .NET processes can’t load 64-bit DLLs or 64-bit COM Inproc
servers.
However, remote procedure calls (RPCs) between 32-bit and 64-bit
processes on the same system are supported.
Migrating Databases
to 64-Bit SQL Server.
Migrating from a 32-bit SQL Server relational database installation to a
new 64-bit installation is a direct and clear-cut process.
When Microsoft redesigned SQL Server in 1998 (7.0), the company
developed the system with 64-bit capabilities in mind.
All the on-disk structures the 32-bit version of SQL Server uses are identical
to the on-disk structures the 64-bit editions of SQL Server uses.
The identical structures make moving your database from the 32-bit
version to the 64-bit version easy.
After installation of the new 64-bit hardware platform, a new 64-bit
edition of the Windows OS, and the SQL Server 2005 there are two primary
options for migrating databases.
Detach/Attach.
By using the sp_detach_db and sp_attach_db stored procedures, you can
move one or more databases from a 32-bit SQL Server system to a 64-bit SQL
Server system.
An overview of the detach/attach process.
|
 |
In this scenario, you first run the sp_detach_db stored procedure to
detach one or more databases from the 32-bit SQL Server system. Doing so takes
the database offline, which lets you move the database data files.
After the files are detached, you can copy those files across the
network to the new 64-bit SQL Server .
After the databases have been copied to the 64-bit system, you can run
the sp_attach_db stored procedure to bring the databases on-line.
No changes are required for the database files.
This procedure should be followed for each user database that you wish
to migrate to the 64-bit platform.
Backup and Restore.
The process of using backup and restore to move databases from a 32-bit
server to a 64-bit server is quite similar to the detach/attach process.
On the 32-bit SQL Server system, you can back up the databases by using
the T-SQL BACKUP command. Alternatively, you can use the graphical SQL Server
Management Studio’s backup capabilities.
After you have copied the .bak file to the 64-bit server, you can
restore it by using either the T-SQL
RESTORE command or SQL Server Management Studio.
Migrating to 64-Bit
Analysis Services.
The process of moving from 32-bit Analysis Services to 64-bit Analysis
Services is similar to the backup and restore scenario for moving SQL Server
relational databases.
Just like the SQL Server relational database files, Analysis Services
on-disk structures use exactly the same format for 32-bit Analysis Services
that they use for 64-bit Analysis Services.
You have two primary options for migrating your Analysis Services
databases from the 32-bit server to the 64-bit server.
Backup and Restore.
To move cubes from the 32-bit version of Analysis Services by using the
backup and restore methodology, you first back up the 32-bit database to an
Analysis Backup File, then restore it to the 64-bit system.
Analysis Services backup and restore
migration process.
|
 |
To migrate Analysis Services databases from a 32-bit Analysis Services
system to a 64-bit Analysis Services system with the backup and restore method,
you can use either the graphical interface the SQL Server Management Studio
provides or XML for Analysis (XMLA) backup and restore statements.
After the file has been copied to the target server, you can then use
the XMLA restore command or the SQL Server Management Studio to restore to the
64-bit Analysis Services.
Analysis Services Deployment Wizard.
You can also use the Analysis Services Deployment Wizard to migrate
Analysis Services 2005 databases from 32-bit systems to 64-bit systems.
The wizard can take the output from an Analysis Services project and use
it to create and process an Analysis Services database on the target server.
However, to use the Analysis Services Deployment Wizard, you must have
network connectivity between the 32-bit source system and the 64-bit target
system.
Product Licensing.
Microsoft’s licensing costs for the 64-bit editions of SQL Server 2005
are identical to those for the 32-bit editions.
No unexpected pricing hurdles bar the adoption of 64-bit technology.
Microsoft also lets Volume Licensing and Software Assurance customers
upgrade from an existing 32-bit SQL Server 2005 license to the 64-bit edition
license at no cost.
In addition, there is no extra charge for using multi core processors.
Under SQL Server’s per-processor licensing model, you need only license
each physical socket.
Neither the number of processor cores used in that socket nor the number
of logical processors changes the licensing cost.
For example, if you have a 2-processor (2-P) system running two dual
core processors, you have two CPU sockets on the motherboard but four real CPUs
in the system. In this scenario, if you chose to use SQL Server’s per-CPU
licensing model, you would need only a two-CPU license -one for each CPU socket
on the motherboard.
Likewise, if you were upgrading to a 4-P system that used four dual-core
processors you would need only a four-CPU license even though the system
actually has eight processors.
Because dual-core processors are on the same die, they’re licensed as a
single processor.
Revisiting the
Benefits.
The 64-bit technology provides a number of essential advantages to all
SQL Server 2005 installations in terms of scalability, manageability, cost effectiveness,
and application compatibility.
Scalability.
The 64-bit platform provides great performance, and several world-record
Transaction Processing Performance Council (TPC)-C and SAP Sales and
Distribution (SD) Users benchmarks offer clear-cut proof of the platform’s
performance advantages.
The large memory addressability and nearly unlimited virtual memory
result in vastly improved performance and reduced need for system I/O, provide reduced system latencies, better
support for parallel processing, and higher data throughput.
Manageability.
The management tools that the 64-bit editions of SQL Server 2005 and
Analysis Services provide are completely “feature compatible” with their 32-bit
counterparts.
In addition, T-SQL code is 100 percent compatible.
.NET Framework application code supports seamless 32-bit to 64-bit
compilation.
Identical on-disk formats make it easy to migrate to the 64-bit
platform.
The setup process is the same for the 32-bit and 64-bit versions of SQL
Server.
Cost effectiveness.
The licensing cost of the 32-bit and 64-bit versions of SQL Server 2005
are the same.
There are no additional licensing charges if you want to take advantage
of the higher performance multiple-core processor technologies.
The 64-bit platform’s scalability lets you use it effectively in server
consolidation scenarios to
reduce hardware, infrastructure, and management requirements.
The performance allows you deploy greater computing power in less
physical space.
Application compatibility.
In addition to its higher performance and vastly increased memory capabilities,
one of the strongest arguments for the 64-bit architecture is its ability to
simultaneously run both 32-bit and 64-bit applications at full speed.
The 100-percent binary compatibility of the 64-bit architecture lets
organizations move to native 64-bit applications at their own pace while
continuing to run all of their existing 32-bit applications at performance
levels that often exceed the performance that the native 32-bit platform provides.
The 64-Bit Future.
“As we move forward . . . I really think of 32-bit as legacy . . . the
future is all 64-bit.”
No comments:
Post a Comment