Planning an efficient upgrade from SQL Server 2005: Step 1 of 3

Step 1: Discover what’s in your database environment

Upgrading a database can seem daunting. Fortunately, Microsoft offers exceptional tools and support to help you smoothly upgrade from SQL Server 2005 to SQL Server 2014 and Microsoft Azure SQL Database before the end-of-support deadline on April 12, 2016. In this series of three blog posts, we’ll review three steps for your that can help make this a manageable and simplified process.

To make your upgrade more efficient with less unintended downtime, it’s important to plan before getting started. The first step in your planning process is to discover what’s in your database environment: workloads, virtual or physical servers, what’s running on which versions and editions of software, and so on. For example, you’ll want to find out if SQL Server 2005 is running in your environment. Then, discover if any applications are running on SQL Server 2005 so you can protect critical data after the end-of-support date.

One way to gather this information is to run the Microsoft Assessment and Planning Toolkit, which automates the process of gathering data about your database environment. It can also help you find unidentified databases that might be impacted by end of support. Microsoft Services and Microsoft Partners also provide offerings to help you do a detailed assessment of your database environment and recommend next steps. Finding the right partner can greatly reduce work and worries on an upgrade.

If you are running an assessment on your own, check out the recommended tasks for the discovery step below.

Assess your database environment with an automated tool

Run the Microsoft Assessment and Planning (MAP) Toolkit. The MAP toolkit is an agentless, automated inventory, assessment and reporting tool that gives you a complete, network-wide inventory of database instances, identifies different software editions and assesses hardware used. The wide-ranging details of databases and server instances that MAP provides are vital information you can use to consolidate databases and better use hardware and database resources.

Document application workloads and dependencies

  • Identify in-house applications. When documenting and identifying which standalone, in-house applications to upgrade, it’s good to have a clear understanding of the application you’re targeting for upgrade.
  • Identify ISV applications. Migrating applications from independent software vendors requires you identify ISV licensing policies and issues, and develop upgrade strategies different from those for in-house applications.
  • Find documentation for non-default configurations. If you have a customized SQL Server instance, find out if anyone documented it before you begin the migration. Your database administrators will need this documentation in the new database environment.
  • Validate inter-application connectivity. A key dependency is to identify applications that connect to the SQL Server database you’re upgrading. You’ll need to keep those applications connected when you upgrade the database.

Understand usage and compatibility requirements

  • Understand application usage and outage windows. Because databases are more than just data and log files sitting on storage somewhere, you’ll need to fit together many pieces of the DB puzzle — application, storage, operating system, network, and so on — to minimize downtime.
  • Assess compatibility requirements. When planning for a SQL Server database upgrade, you’ll want to understand what features are deprecated, discontinued or changed in the new version. Being aware of these changes in advance helps you prevent performance problems and issues related to application availability.

In the next post in this series, we’ll talk about choosing a target destination for workloads that you’re upgrading. Leveraging the data you’ve gathered in this step, you’ll now have the opportunity to evaluate your current data infrastructure. You can then upgrade to solutions that streamline your investment by moving data to the cloud or by consolidating it.