Book Image

Microsoft SQL Server 2008 R2 Administration Cookbook

By : Satya Shyam K Jayanty
Book Image

Microsoft SQL Server 2008 R2 Administration Cookbook

By: Satya Shyam K Jayanty

Overview of this book

Table of Contents (19 chapters)
Microsoft SQL Server 2008 R2 Administration Cookbook
About the Author
About the Reviewers
More DBA Manageability Best Practices

There's more.

The following Upgrade checklist will be a useful resource while upgrading the SQL Server platform:

Planning for Upgrade


  1. 1. Obtain all the necessary leads pertaining to the SQL Server data platform (hardware), web and application environment (software) to document high-level and detailed information.

  2. 2. Highlight all the important resources required for pre-upgrade, during-upgrade, and post-upgrade tasks.

  3. 3. Highlight all the required application testing and business escalation resources.

  4. 4. Document implementation steps including the back-out plan tasks.


  1. 1. Hardware and Software requirements for SQL Server 2008 R2.

  2. 2. Hardware considerations—existing and new.

  3. 3. Application compatibility—essential for third-party applications.

  4. 4. Ready with required environment for thorough testing at pre-upgrade and post-upgrade implementation.

Strategic Decision

  1. 1. Determine whether In-place or Side-by-side upgrade method is being used.

  2. 2. In case of failover clustering and/or database mirroring environment:

    • Establish backup and recovery plan.

    • Establish roll-back and roll-forward plan.



  1. 1. In case of side-by-side upgrade, ensure that the required databases, jobs, logins, packages, and linked-server information are restored.

  2. 2. Ensure no errors are reported on environment (OS, SQL Server, and application).

  3. 3. Download and execute SQL Server 2008 R2 Upgrade Advisor.

  4. 4. Download and execute SQL Server Best Practice Analyzer tool for 2000, 2005, and 2008 R2 versions.

  5. 5. Perform basic connectivity testing from the application.

The Upgrade


  1. 1. Ensure to list out all the steps that will be implemented, a screenshot of each screen is an ideal concept to perform the upgrade by any user.

  2. 2. Make sure to include all the checkpoints that will control the GO or NO-GO decision.

  3. 3. Every checkpoint should insist on the point of referring to relevant log files including SQL Server error logs.

  4. 4. Action a roll-forward plan by troubleshooting the issue wherever possible.

  5. 5. In case of serious issue of environment, invoke the no-go step to implement the back-out plan.


  1. 1. Obtain necessary passwords from safe.

  2. 2. Perform a health check on OS and SQL Server.

  3. 3. Perform the upgrade using SQL Server installation center tool.

  4. 4. Perform the system and user database backups, once the upgrade is completed successfully.



  1. 1. Determine the application acceptance by performing end-to-end testing.

  2. 2. Integrate the new instance to connect the live application.

  3. 3. Apply required security patches and hotfixes.

  4. 4. Review security settings and execute Best Practice Analyzer tool for SQL Server 2008 R2.

  5. 5. Review event viewer and SQL server error logs.

  6. 6. Perform


  1. 1. Document any new steps and modify the existing steps wherever necessary.

  2. 2. Perform failover and failback tasks to ensure Disaster Recovery provision is not compromised.