Thank this author by sharing:
By Arshad Ali, 2011/05/20 (first published: 2009/03/02)
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:
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.
There are two methods to upgrade from SQL Server 2000(SP4)/ SQL Server 2005(SP2) to SQL Server 2008.
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.
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.
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.
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.
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:
o Disadvantages:
o Advantages:
In-place Upgrade Disadvantages:
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:
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:
Note:
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.
(Once installed SSUA can be found at START -> Programs -> Microsoft SQL Server 2008 -> SQL Server 2008 Upgrade Advisor)
When the analysis completes, click the Launch Report button to view the results of the analysis.
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
http://technet.microsoft.com/en-us/library/bb677622(SQL.100).aspx
Using Upgrade Advisor to Prepare for Upgrades
http://technet.microsoft.com/en-us/library/ms144256(SQL.100).aspx
How to: Upgrade to SQL Server 2008 (Setup)
http://msdn.microsoft.com/en-us/library/ms144267(SQL.100).aspx
HELP=Sql instance installation on Application Server
Fresh install no database server
Error: Installing SQL Server Cluster Instance
If you have not had the chance to work with SQL Server 2005, or even if you have, Robert Pearl bring...
Need help immediately= Installing seperate instance on server
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.
Join us!
Steve Jones Editor, SQLServerCentral.com
