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 or an increasing amount of data being digitized for storage. SQL Server 2008 R2 has emerged as data platform 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 R2 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.
Our goal is to Migrate 100 SQL server 20005 Enterprise Edition to SQL server 2008 R2 and consolidate the existing dozen servers to In Virtual environment a few servers for easy manage and maintenance. So there are 2 things that we want to accomplish:
1. Upgrade to SQL server 200R R2
2. Consolidate existing servers In VMware environment
We need to define the criteria that determine the success of our database 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 are follow proper 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.
We are following below two methods to upgrade the server 2008 and 2008 R2 environment.
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 R2 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.
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.
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 auto grow 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 UPDATEUSAGE on all databases following the upgrade.
- 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.