After you have determined optimal placement of user databases on the consolidated SQL Server stacks, migrate user databases one by one from their current production environment to the new consolidated topology. After you migrate each user database, monitor the performance impact to ensure that the consolidated system remains stable with the introduction of these new user databases.
There is a multitude of ways to migrate user databases to the consolidated environment. One popular method is to detach each database from its current environment, copy it to the consolidated environment and attach it there.
However, replication will prevent you from detaching published databases. If your database is published, it is best to drop replication, move the databases and then rebuild the publications and subscriptions. On SQL Server 2000 databases with full-text catalogs you will have to manually copy the database and the full-text catalogs to the consolidated environment as outlined by Microsoft support. It is possible to use the Copy Database wizard, but it is simply not scalable for anything beyond small databases.
Before opening consolidated databases to the business owners, run warm-up scripts to ensure the first user experience in the new consolidated environment will not be a sluggish one.
The above tip is excerpted from Chapter 2, '
Planning your SQL Server consolidation,' of our original expert e-book, "Consolidate SQL Servers for availability, scalability and cost savings." This chapter explains six steps to consolidation and other key consolidation considerations.
How to consolidate SQL Servers
Step 1: Create a SQL Server consolidation methodology
Step 2: Analyze candidate databases, servers and more
Step 3: Test your consolidation
Step 4: Deploy consolidated SQL Servers
Step 5: Monitor and stabilize consolidated SQL Servers
|ABOUT THE AUTHOR:|
Hilary Cotter has been involved in IT for more than 20 years a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and currently working on books on merge replication and Microsoft search technologies.
Copyright 2006 TechTarget
This was first published in June 2006