SQL Server 2000 Consolidation: a business case

Table 10. Consolidation framework
Criteria Considerations
OLTP vs. data warehousing Mix the types of applications to avoid contention
SQL Server transactions/sec to BatchRequest/sec ratio High volume of transactions can lead to I/O bottlenecks
Number of concurrent active users Look for “active” user account in Sysprocesses
Degree of deviation between resource utilization over a
period of time
Helpful in planning hardware upgrades
Degree of Parallelism (DOP) for batch process and complex
queries
Allows for better utilization of hardware
Uptime requirements Allows for proper use of WSRM configuration
Response-time requirements User requirements must be evaluated for each application that
is consolidated
Ownership and management Requires a skilled database administrator
Network requirements Will benefit linked servers and replication
Security First, evaluate security requirements
Departmental chargebacks WSRM allow for studying the resource utilization
Potential SQL Server 6.5 and SQL Server 7.0 migration issues need to be completely isolated—from
Transact-SQL incompatibilities to system settings and everything in between. A priority list for
application migration, followed by user migration and data migration, needs to be built. Conflicts
need to be identified and resolved in object names and location.
Most importantly, special care must be taken to NOT introduce any change in the original
environment such as adding new functionality or enhancements to applications or data schemas.
Problems will be identified during migration planning, but as long as they will not break in the
consolidated environment, this is not the time to address them. Once the consolidation has been
successfully completed, these types of enhancements can be considered. Some migration
considerations are listed below.
• Data migration—Data can be moved between 32-bit and 64-bit editions of SQL Server 2000. The
on-disk data structures are the same for both editions. Moving a database from 32-bit SQL Server to
64-bit SQL Server can be accomplished using an sp_detach from the source server, copying the
database and transaction log files to the 64-bit SQL Server and performing an sp_attach to the
database on the 64-bit SQL Server. System-level objects, such as logins, database access,
permissions, roles, jobs, stored procedures, and linked servers will require script generation and
recreation on the 64-bit server. Data Transformation Services (DTS) packages require a 32-bit server
to run, but can access data on the 64-bit server.
Migration can be accomplished in a very similar manner to that described above, using database
backup and restore, and the GUI database detach/reattach wizard.
To migrate Analysis Services cubes, simply archive the cubes on the 32-bit server and restore the
images on the 64-bit server. Bear in mind that the metadata in the relational database also needs to
be backed up. The best practice is to run a SQL Server maintenance job that backs up the OLAP
repository SQL Server database into a file at the root of the Analysis Services data folder, and then
use a file system backup from there. This procedure ensures that the metadata backup is
synchronized with the cube data backup.
You cannot use the copy database wizard to copy from a 64-bit server or to target a 64-bit server.
All logins have to be scripted and applied on the 64-bit server.
24