SQL Server 2000 Consolidation: a business case

• Apply service packs and patches:
– Make sure all servers have same (most recent) operating system and SQL Server 2000 service
pack and security patch levels.
– Verify that virus protection is installed and has most recent virus definition files.
• Back up database, logs, master, and MSDB again.
• Test each database and server consolidation:
– Conduct performance monitoring of at least key CPU, memory, disk I/O and queuing, and
usage/workload measurements.
– Conduct SQL Profiler monitoring to identify problems within database.
– Carefully test each application for connectivity, security, performance, and function.
– Carefully test each database for connectivity, security, performance, and function.
• Test cutover:
– Conduct test with scheduled stop of SQL.
– Copy database to new server using database copy wizard (32-bit to 32-bit servers only) to
detach, copy, reattach, and script logins and errors.
– Examine wizard and event logs. Document and resolve any issues found.
– Reattach the original database on the original server and restore user access.
– Conduct connectivity, application, database, and performance testing against new, upgraded, or
consolidated server.
• Perform final cutover:
– Repeat backup and cutover steps above.
– Leave original database in offline status.
– Test new server, database, and applications. Make sure that cutover is scheduled for a longer
period to allow for thorough testing and problem resolution. Target holidays or weekends.
– Resolve all issues and accept new server, keeping old server offline, or resume operations on old
server (go/no go).
– Repeat backup.
– Document new server (suggest script).
• Monitor:
– Monitor performance of key set of counters for period covering normal to heavy usage.
– Log all problems and resolutions.
• Repeat test and final cutover procedure until acceptable performance thresholds are approached
(not crossed) or all servers and databases in group have been consolidated, whichever occurs first.
• Wait at least one week before starting actual consolidation of servers in next group. This is so that
administrators will have time to identify and resolve any issues with the previous group. Preparation
actions, such as determining hardware requirements and ordering servers or upgrades can be done
in parallel.
Monitoring the consolidation
Once the system goes live, the first three months are the most critical period. During this critical
period, it is strongly recommended that server and database monitoring systems be in place and
actively collecting live and historical performance data. This will help in early problem identification
and proactive resolution. During the critical period, database administrators, consolidation project
team-members, application teams, and help-desk personnel will need to be available and especially
alert to consolidation and cutover schedules and potential problems.
Once the monitoring framework has been set up, routine monitoring over periods ranging from days
to weeks to months allows for the establishment of system-performance baselines. Once the
28