Detailed specification of the SQL Server migration planning – Part 3

In this series of blog posts, you will learn in detail what goes in to SQL Server migration process. You can access the first part of "Detailed specification of the SQL Server migration planning" here.

In my previous blogpost I wrote about the SQL Server High Availability planning. In this post the topic is phasing the project.

Migration project phasing

The key point of phasing is that all the important parts of migration will be covered and in right order.

First Phase: Gather Information

1. List all the servers (physical or virtual) and instances involved in migration and document
   1. Windows Server version and SP-level
   2. Server Configuration (memory, processors, page file, Etc.)
   3. High Availability Configurations
   4. Disk system
2. List all the SQL Server and instances involved in migration and script:
   1. Versions of SQL Servers
   2. Logins with hashed passwords an SIDs
   3. Jobs with owner accounts (including SIDs)
   4. Server configuration settings
   5. Alias-names
   6. All other Instance level objects
      1. Linked Servers
      2. Policies
      3. Database Mail Profiles and accounts
      4. Maintenance plans
      5. Audits and Server Audit Specifications
      6. Credentials
      7. Backup Devices
      8. Server Proxy Account
   7. Etc.
3. List all the databases to be migrated and script:
   1. Database and Log sizes
   2. Filegroup settings and File locations
   3. Is Filestream or In-Memory used
   4. Database options (properties)
   5. Security settings (users, Database Audit Specifications)
   6. All other special setting/objects
      1. Database Master Keys
      2. Certificates and keys
      3. Database encryption keys
      4. Always Encryption settings and keys
      5. Full Text Catalogs
      6. Service Broker usage and settings
      7. Assemblies
      8. Etc.
4. List all other SQL Server services running on these servers/instances
   1. Reporting Services
   2. Analysis Services
   3. Integration Services
   4. Master Data Services
   5. Data Quality Services
   6. Etc.
5. List all other programs running on these servers
   1. Program name and purpose
   2. Average resource consumption

Second Phase: migration planning of applications or application servers

1. What applications/application servers are involved
2. Shut down procedures and orders
3. Name resolutions (alias, DNS, Etc.)

Third Phase: Planning the migration according to previous findings

1. Reviewing (new) Server Architecture and High Availability Architecture
2. Software installations and versions
3. Instance definitions
4. Database definitions
5. What is the migration method
   1. In-place
   2. Side-By-Side
6. Etc.

Fourth Phase: Project timing

1. Testing the migration
2. When will the actual migration take place
3. Estimated time for migration based on testing or experience
4. Recovery Procedures
5. Allocation of Resources
6. Informing

Fifth Phase: Migration

1. Doing the migration according to plan

Sixth Phase: Operations after SQL Server and database migrations

1. Testing that databases work
2. Start up applications/application Servers
3. Test that applications work
4. Rollback on failure
5. On SQL Server:
   1. Review/Set Server/instance Configurations
   2. Update Statistics
   3. (Rebuild indexes)
   4. Create possible missing Server level objects (Maintenance plans etc.)
   5. Configure Compatibility levels
6. Open client connections

Access the first part of "Detailed specification of the SQL Server migration planning" here.

You should also check out our online guide on Mastering SQL Server Consolidation planning.

Pekka Korhonen
Lead Architect, MCM
DB Pro Oy