It's widely recognized that database sizes are growing significantly, and that the growth is being forced by many factors, such as companies requiring more data to be available online for longer (e.g. to comply with government regulations) or an increasing amount of data being digitized for storage.
SQL Server 2008 has emerged as data platform not only for small and medium scale industries but for large scale industries which stores and manages several terabytes of data efficiently in a variety of different formats including XML, e-mail, time/calendar, file, document, geospatial, and so on. SQL Server 2008 is very much capable in handling data explosion while providing a rich set of services to interact with the data: search, query, data analysis, reporting, data integration, and robust synchronization.
The objective of this article is to lay down a step-by-step guide for a SQL Server 2008 upgrade from SQL Server 2000(SP4) and SQL Server 2005(SP2). In this document, I will talk of Upgrade Advisor tools used for planning the SQL 2008 upgrade, different upgrade processes (In-place upgrade and Side-By-Side), and the rollback strategy.
SQL Server 2008 delivers a powerful set of capabilities to solve the growing needs of managing data in the enterprise, on desktops, and on mobile devices, it also builds on the strong momentum in the business intelligence market by providing a scalable infrastructure that enables information technology to drive business intelligence throughout the organization and deliver intelligence where users want it. SQL Server 2008 also delivers improved performance in many areas, including data warehousing, reporting, and analytics. Some of the few compelling features of SQL Server 2008 are as follows:
- T-SQL Enhancements
- Change Data Capture
- Resource Governor
- Integration Services Enhancements
- Reporting Services Enhancements
- Analysis Services Enhancements
- Data and Backup compression
- Policy-Based Management Framework
- Transparent Data Encryption
- External/Extensible Key Management
- SQL Server Audit
- Database Mirroring Enhancements
- Performance Data Collection
For details on these features refer to the links below:
Note: I am writing separate articles on these above features, where I will talk more/granular details on it.
Methods to Upgrade
There are two methods to upgrade from SQL Server 2000(SP4)/ SQL Server 2005(SP2) to SQL Server 2008.
- In-place Upgrade – Organizations that do not have resources available to host multiple database environments commonly use an in-place upgrade. An in-place upgrade overwrites a previous installation of SQL Server 2000 or 2005 with an installation of SQL Server 2008. In other words, SQL Server 2008 gives us ability to automatically upgrade an instance of SQL Server 2000 or 2005 to SQL Server 2008. The reason it is called in-place upgrade is because a target instance of SQL Server 2000 or 2005 is actually replaced with a SQL Server 2008 instance. We do not have to worry about coping data from the old instance to new instance as the old data files are automatically converted to new format. This upgrade method is the easiest way to upgrade the database to newer version.
It's because the files are automatically upgraded without any manual intervention. The number of instances and server involved in this type of upgrade is always one. This upgrade method cannot be used if we want to upgrade only a single database.
Note: Database backups required
Before we perform an in-place upgrade, we must back up all SQL Server databases and other objects associated with our previous SQL Server instances. In addition, we should be aware that the previous version of SQL Server Books Online will remain intact on the machine after the upgrade.
- Side by Side Upgrade – Database environments that have additional server resources can perform a side-by-side migration of their SQL Server 2000 or 2005 installations to SQL Server 2008. In this upgrade method, a new instance is created on the same server or in a new server. In this upgrade method the old database instance runs in parallel to the old legacy database. So as the old instance is untouched during this type of upgrade, the old legacy database is still available and online for the application. Having the old environment still active during the upgrade process allows for the continuous operation of the original database environment while we can install and test the upgraded environment. Side-by-side migrations can often minimize the amount of downtime for the SQL Server.
A side-by-side migration does not overwrite the SQL Server files on our current installation, nor does it move the databases to new SQL Server 2008 installation. We/DBAs need to manually move databases to the new SQL Server 2008 installation and other supporting objects (Jobs, DTS/SSIS packages etc.) after a side-by-side installation by using one of the upgrade methods discussed below.
- Detach/Attach – When disk storage and source database availability are not a consideration, one common method of moving a database from one server to another is to detach and then attach the database. This process requires that users not be accessing the database, but it has the safety advantage that if an unforeseen problem arises, the DBA can always reattach a copy of the database file to the original SQL Server instance. We can move a large database from one instance to another on the same server, which requires less disk space than some other methods because it reduces the number of database files that we must create for the upgrade process. However, this method can be unsafe because it doesn't follow the recommendation of creating a copy of the database files.
- Backup/Restore – To avoid possible loss of the database files, making a database backup to use in the upgrade is a secure alternative method for moving a database from one SQL Server instance to another. This process does not interfere with the continuation of activity on the original database environment, nor does it jeopardize the usefulness of the source database files to the original SQL Server version.
Another advantage of using backup and restore is that the backup files are usually smaller than the original database files because the backup process captures only database data, not reserved, unused database space. The decrease in file size usually makes any file transfer faster than transferring the original database file. However, we must take into account the disk space needed for the original database files, the backup files, and the new database files during the upgrade. We can use SQL LiteSpeed tool to take compressed backup.
- Copy Database Wizard – If we want to automate the task of moving a database from one server to another during the upgrade process, we can use SQL Server's Copy Database Wizard. The Copy Database Wizard gives us a way to move one or more SQL Server 2000 or 2005 databases, with their associated objects, while the source database is either online or available for use or offline to SQL Server 2008. This direct copy makes efficient use of disk space while preserving database uptime.
- Manual Schema Rebuild and Data Export/Import – A method not commonly used for database upgrades is the manual method of scripting out the database, scripting the logins associated with that database, scripting all objects associated with that database, and scripting out any other supporting SQL Server objects associated with that database. After executing the script or scripts in the new instance, then we must manually move the data from the original database to the new database using Transact-SQL scripts, SQL Server Integration Services (SSIS), BCP, or other methods available for moving data from one database to another.
Note: Make a copy of the files
It is a best practice to create a copy of the database file for recovery purposes before attaching it to a new instance. After we attach a database file to SQL Server 2008, we can no longer use that file in an earlier version of SQL Server.
Choosing upgrade method
The two upgrade methods discussed above have several different advantages and disadvantages with it as given below, we have to evaluate it in terms of our requirements and current circumstances before moving for upgrade.
In-place Upgrade Advantages:
- It's easier and faster, especially in small systems.
- It's mostly an automated process.
- The instance will be offline for a minimum amount of time.
- The resulting instance after upgrade will have the same name as the original, as the new setup will replace the older version.
- No additional hardware is required in many cases.
- It's very complex to rollback.
- Not applicable in scenarios where we want to upgrade a part of system like upgrading just one single databases.
- We cannot run an upgrade comparison after doing the upgrade.
- Side by Side Upgrade
- More control over the upgrade, as we can upgrade the components, which we want to.
- We can keep our application running even when we are installing SQL 2008 as the old instance or server will be available.
- Easy to do a rollback as the original database server is untouched.
In-place Upgrade Disadvantages:
- We might need additional hardware resources in terms of disk space, CPU and RAM.
- Manual intervention is required to migrate databases, Jobs, logins etc.
- There will be change in configuration settings, which are used by the application to connect to the database.
- More time is required while moving VLDB to the new version of database.
Risk and Mitigation plan
We need to define the criteria that determine the success of our database upgrade. The success test might be as simple as manually verifying the existence of an object through the graphical user interface (GUI); or it might be a very complex procedure that executes a set of predetermined queries and scripts to verify that all objects exist, that data has successfully imported, that surrounding database objects such as backup jobs operate normally, and that users have full access to the database. However simple or complex the success criteria, we must define the criteria before upgrading to help ensure a successful upgrade.
To prepare the success criteria, we should review each phase and step of the overall database upgrade plan and ask ourselves several questions. The following questions will help us understand what we need to do to declare that the phase or step is successful:
- How can I measure whether this step is successful?
- How can I test that measurement?
- How can I compare my test results against what they would have been in the old database?
Although creating an upgrade plan reduces the likelihood of problems occurring during the upgrade process, problems do arise that can prevent the upgrade process from completing. Most organizations rely heavily on the data contained in their databases, and having that data unavailable due to an upgrade might cause problems in business operations and even have financial implications. We should create a plan to recover from each phase and step of the upgrade process to help minimize data loss and reduce the time that data might be unavailable. This recovery plan might involve backing up a set of files, creating scripts to move database connections from the upgraded instance back to the original instance, or anything else we feel is necessary to get the old database instance back up and meet business uptime needs while you fix the upgrade problems.
The following tips can help us perform a secure and successful upgrade:
- Create a series of checklists: DBAs and developers should prepare a series of checklists that need to be performed before, during, and after a database upgrade.
- Back up all important files: Back up all SQL Server database files from the instance to be upgraded, as well as any application files, script files, extract files, and so on so that you can completely restore them if necessary.
- Ensure database consistency: Run DBCC CHECKDB on databases to be upgraded to ensure that they are in a consistent state before performing the upgrade.
- Reserve enough disk space:Estimate the disk space required to upgrade SQL Server components, user databases, and any database files that might need to be created during the upgrade process. We might need two to four times the amount of disk space during the upgrade process as we will need after the upgrade is finished.
- Ensure space for system databases: Configure system databases (master, model, msdb, and tempdb) to autogrow during the upgrade process, and make sure that they have enough disk space for this growth.
- Transfer login information: Ensure that all database servers have login information in the master database before upgrading the database. This step is important for restoring a database because system login information resides in the master database and must be re-created in the new instance.
- Disable all startup stored procedures: The upgrade process will usually stop and start services multiple times on the SQL Server instance being upgraded. Stored procedures set to execute on startup might block the upgrade process.
- Stop replication:Stop replication and make sure that the replication log is empty for starting the upgrade process.
- Quit all applications:Certain applications, including all services with SQL Server dependencies, might cause the upgrade process to fail if local applications are connected to the instance being upgraded.
- Register your servers after the upgrade: The upgrade process removes registry settings for the previous SQL Server instance. After upgrading, we must reregister our servers.
- Repopulate full-text catalogs: The upgrade process marks your databases as full-text disabled. Catalogs must be repopulated, but Setup doesn't run this operation automatically because it can be time-consuming. Because this operation enhances the performance of your SQL Server 2008 installation, we should plan to repopulate full-text catalogs at a convenient time after the upgrade.(Full Text Search has been integrated in SQL Server 2008, please refer to the section given above for more details)
- Update statistics:To help optimize query performance, update statistics on all databases following the upgrade.
- Update usage counters:In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGEon all databases following the upgrade.
- Before we perform an in-place upgrade, we must back up all SQL Server databases and other objects associated with our previous SQL Server instances. In addition, we should be aware that the previous version of SQL Server Books Online will remain intact on the machine after the upgrade.
- It is a best practice to create a copy of the database file for recovery purposes before attaching it to a new instance. After we attach a database file to SQL Server 2008, we can no longer use that file in an earlier version of SQL Server.
Things to Do
- Participants must have real-world SQL Server 2000/2005 administration experience
- Participants must have basic operational familiarity with SQL Server Management Studio and SQL Server Profiler
- Participants must have basic operational familiarity with Windows administration and Networking
- Participants are encouraged to have real-world experience installing and supporting SQL Server applications
Preparing for Upgrade – SSUA
The SQL Server Upgrade Advisor (SSUA) is a tool available to find out database compatibility and blocking issues while doing the migration. Upgrade advisor smoothes the transition from an older version to the newer version by anticipating issues/blocking with legacy databases. The Upgrade Advisor generates reports explaining the upgrade issues and also guidance on how to resolve them.
In addition to analyzing (analysis of both local and remote instances) databases and objects, the Upgrade advisor can also analyze T-SQL queries, by using SQL trace files and BI components excluding Reporting Services (Though I noticed it was not also analyzing Integration Service as well remotely and giving error, “Upgrade Advisor cannot locate the SQL Server 2005 Integration Services files that it requires to analyze packages. The installation of Integration Services on this computer has already been upgraded to SQL Server 2008. To upgrade existing Integration Services packages without analyzing them, use the SSIS Package Upgrade Wizard”, same was working fine on local machine). Reporting Services instances will require the SSUA to be installed on the report server itself. The SSUA utility supports analyzing both SQL Server 2000(SP4) and SQL Server 2005(SP2) installations (which are the supported platforms for upgrade to SQL Server 2008).
The SSUA utility provides both analysis and reports. SSUA reports provide the alerts/warnings that we should consider or take action on prior to upgrading our SQL Server environments to the 2008 platform. A nice feature of the SSUA utility is its ability to provide the user with links directly to its alert documentation.
The output is a report that tells us how severe the problem is and when it should be fixed. It can be run from any client with .NET 2.0 framework installed and does not affect the targeted databases when it is run. It can take a while to run as it has to check each object in the database so the more there are the longer we have to wait.
When we start upgrade advisor, we see the welcome screen as below.
(Once installed SSUA can be found at START -> Programs -> Microsoft SQL Server 2008 -> SQL Server 2008 Upgrade Advisor)
If SSUA doesn't automatically identify the server we wish to analyze, we provide the name in the "Server Name" textbox.
Click the Detect button to identify all SQL Server services running on the selected server. We may modify the components that we wish to validate by toggling the checkboxes. When complete, click the Next button to continue.
If we have multiple SQL Server instances running on the selected server, choose the affected instance from the Instance Name drop-down box. We may only check one instance at a time.
Select the authentication type from the Authentication drop-down menu. If we chose SQL Server Authentication, we need to provide a user name and password. Click the Next button to continue.
SSUA will then allow us to select the parameters we wish to analyze. First, we use the checkboxes to select the database(s) we wish to analyze on the selected instance.
We may also provide SSUA with specific SQL Trace and/or SQL batch files to test particular workloads. When complete, click the Next button to continue.
At this point we will be allowed to choose databases, which we want to run against Upgrade Advisor. If we choose to analyze all the databases, wizard will also analyze SQL Server configurations settings. It's always a good practice to choose all the databases in case we are planning to do an in-place upgrade.
Confirm the settings displayed on the next screen and click the Run button to begin the analysis. Depending upon the size and number of databases and the selected workloads, this may take up an hour or longer to complete.
Once confirmed by clicking on "Run" button, Upgrade Advisor analyzes each selected database and components and when done, it saves the report on the default path of logged in user. The reports are saved in XML format, for each component. We can launch the Report viewer to view the reports generated.
While running it will show us status of run as given below.
When the analysis completes, click the Launch Report button to view the results of the analysis.
Clicking on (+) sign will drill down as follows, Take any actions necessary to correct issues prior to our actual upgrade.
Each report has got its own importance level. Red X symbol means that this error needs to be fixed before upgrading. A yellow triangle indicates that additional action needs to be taken once upgrade is done.
The "When to Fix" column indicates when we should address the error. Any error having "Before" as the value in "When to fix" need to be addressed immediately as this error can be blocker for the upgrade.
We can expand each report as shown below screen shot to check the objects causing the error and how to resolve it. This can be done by clicking on link, "Show Affected objects"
Review de-supported (removed) features (for example Notification Services, backup with TRUNCATE ONLY etc. are no more available in SQL Server 2008).
Document existing system.
Develop validation tests / performance benchmarks and capture baseline data
Formulate and test rollback plan
As discussed above, we should run upgrade advisor for all databases (including system databases) and fix any issue/blocker for upgrade.
Take proper down time before starting the upgrade. The outage required would depend upon the size of the databases and the environment in which database servers are hosted.
Once all the issues and blockers are solved, and all applications are closed, take complete backup of all the databases (including master, model and msdb).
Also take transactional backup for all user databases and the disable all the jobs including jobs for taking full backups, jobs to defrag and other maintenance plans.
It is also recommended to take an image of your database server; this can be very handy in case you will have to rollback.
If the operating system of the server is Windows 2003, make sure that Windows service pack 1 is installed. This needs to be done on all the nodes of cluster in case the database server is on Failover Cluster environment.
We can save upgrade time by installing a few prerequisites in the database server. They are .Net 2.0 framework and SQL Native Client, in case if we have servers on cluster, install these setups on each node.
Get ready with the new SQL 2008 DVD and the edition you want to install.
Make sure that there is enough space in the drive where SQL Server 2000/2005 is installed in case of in-place upgrade.
Full Text indexes applied on tables in SQL 2000/2005 are not compatible with SQL Server 2008. Refer to Full Text Integration section in this document for more details.
Make sure, all the points are taken care from "Before upgrade section".
Start installing by clicking SetUp.exe from SQL Server 2008 DVD.
The setup program is designed in such a way that it automatically updates the database binaries. It altogether creates a different folder "100" to contain its new binaries. The “100” folder specifies the compatibility of the database. It also upgrades the other SQL Servers on clusters automatically but only the database engine and not other components like reporting services or analysis services. This is very important. Therefore you will have to install other components separately in each node.
You can see the progress of installation on installation screen, in case installation is done on cluster, setup will populate all the nodes in a drop down list.
Once all the nodes are upgraded with the database engine and all components are installed individually on servers, install the latest SQL Server service pack(if there is any). This need to be done on all the cluster nodes.
A reboot may be required to complete the installation due to open file locks during the installation process. If the installation log does not contain any 3010 error, then a reboot is not required.
Review all logs
Revisit Upgrade Advisor recommendations
Update statistics to ensure performance
Sample for very large tables.
Reconfigure Log Shipping
Verify Agent jobs and maintenance tasks
Verify security settings, especially cross server and/or cross-domain access privileges
Recreate all the full text indexes removed from the databases.
Re-populate Full-Text catalogs
Update the statistics for all the databases. This step may take time depending on the size of database.
Update usage counters In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGE on all databases following the upgrade.
After initially restoring SQL Server 2000/2005 databases to SQL Server 2008, the compatibility level will still be 80/90, which corresponds to SQL Server 2000/2005 respectively. To take advantage of all the new features of SQL Server 2008, we must change the compatibility level to 100, which refer to SQL Server 2008.
Once it is done, it's very important to test the application with the new database server 2008.
At first glance, upgrading to SQL Server 2008 looks complex but can be made quite straight forward by doing lot of testing and study. This article talks about different methods and strategies to achieve a smooth and trouble free transition, I also discussed of Risk/Mitigation plan if it is all required. I would recommend you do the rigorous testing to get confidence and get a fair idea about the entire processes of upgrading or migrating and for that we must devote ample good time in planning and testing of the upgrade.
Upgrading to SQL Server 2008
Using Upgrade Advisor to Prepare for Upgrades
How to: Upgrade to SQL Server 2008 (Setup)