SQL Server 2000 Consolidation: a business case

• SQL Server Agent jobs—Care should be taken when moving SQL Server Agent jobs to the 64-bit

servers. Generating scripts for the jobs would be an easy way to create jobs on the new 64-bit

server. Remember that certain jobs may fail to run on the new server. The time required for testing

and fixing such jobs must be included in the testing phase.

Before applying the scripts on the new consolidated server, make sure that there are no name

duplications for jobs, alerts, and operators.

• DTS packages—Microsoft SQL Server 2000, 64-bit does not support the execution of DTS

packages. Microsoft SQL Server, 64-bit can store DTS packages, but they must be run from 32-bit

SQL Server. When the databases are moved to a new server, the DTS packages may need

modifications to continue working.

For example, if you use a DTS package to populate an Analysis Services cube, and the Analysis

Services has been moved to 64-bit SQL Server, you can use DTS from a 32-bit machine to read

data from the database and populate the cube. The package itself may need modifications to point

to new server names.

If metadata services are used to manage DTS packages, then they must continue to be stored on

32-bit servers only.

Microsoft SQL Server 2005 (code-named Yukon), to be introduced in 2005, will support full native-

supported DTS functionality on Intel Itanium 2–based systems.

Note: Any User Defined Functions (UDFs) in the Analysis server must be recompiled for the 64-bit

platform.

If the DTS packages involve other database products (such as Oracle9i, Oracle 10g, or IBM DB2),

make sure that you have proper 64-bit drivers for those databases.

• Extended stored procedures—Extended stored procedures share the memory space with SQL

Server. When migrating extended stored procedures on a consolidated server, the extended stored

procedures must be thoroughly tested for problems, including memory leaks. When consolidating

multiple servers on one server, a memory leak in any one extended stored procedure may put all

other applications at risk.

Note: Any extended stored procedures must be recompiled for the 64-bit platform.

• Linked servers—If, prior to the consolidation, servers were linked to enable connectivity and queries

between servers, that connection may no longer exist after the consolidation. If domains are

changed, this may also affect linked servers. Some of the questions that need to be answered

during migration are:

– If you consolidate, how will consolidation affect the application?

– Will queries need to be rewritten? How will you migrate your linked server settings?

• Shared resources—Certain resources, such as Microsoft Distributed Transaction Coordinator (MS

DTC) and the underlying Microsoft Search full-text search service, are shared among all instances

and even with other applications. Resource-sharing will be a concern in a consolidated environment

as you add more databases and resources that utilize shared resources.

• Upgrade and migration rules—The same upgrade rules and migration rules for general applications

and databases continue to apply in a consolidation effort. Remember to identify and resolve any

database issues prior to moving the database, leveraging available features like DBCC commands

and error logs. In addition, update statistics and resolve database fragmentation issues prior to

moving the database. Also remember that, depending on the post-consolidation physical layout

(locations of the data and log files for your consolidated SQL Server), what you do to one database

may affect others.