SQL Server 2008 offers a number of new features and improvements that make upgrading desirable. You can upgrade instances of SQL Server 2000 and SQL Server 2005 to SQL Server 2008 or SQL Server 2008 R2, as well as upgrade SQL Server 2008 to SQL Server 2008 R2. Whether you’re a gung-ho developer or the most conservative of administrators, there’s an upgrade path to suit your comfort level. This chapter provides best practices and recommendations for upgrading to SQL Server 2008 with minimal issues.
What’s New in Upgrading SQL Server
SQL Server 2008 provides a new installer program for performing installations and upgrades. The new features of the SQL Server 2008 Installer include
Also new in SQL Server 2008 is a refined Upgrade Advisor. The Upgrade Advisor tool allows a DBA to fully analyze existing SQL Server 2005 and SQL Server 2000 installations for issues that may surface when upgrading to SQL Server 2008 or SQL Server 2008 R2. Addressing these issues before conducting the upgrade should lead to a smoother experience when transitioning to SQL Server 2008. With the release of Service Pack 1, SQL Server 2008 also now supports Slipstream installations. Slipstreaming is a method of integrating a SQL Server 2008 update (such as a service pack or cumulative update) with the original installation media so that the original media and update are installed at the same time. This can be a huge timesaver over having to manually apply service packs or cumulative updates after performing a full installation or upgrade.
The focus of this chapter is on upgrade options and best practices rather than a screen-by-screen walkthrough of the upgrade process. An upgrade installation is not much different from a new installation. See, “Installing SQL Server 2008,” for a detailed walkthrough and description of the installer screens and options.
Using the SQL Server Upgrade Advisor (UA)
It would be a daunting task indeed to try to test every stored procedure and function, every table and view, every online analytical processing (OLAP) cube, every Data Transformation Services (DTS) or SQL Server Integration Services (SSIS) package, and so on that your team has built to make sure they still work after you migrate them to SQL Server 2008. With the availability of the SQL Server Upgrade Advisor (UA), you can relax a bit and let the combined experience and testing of early adopters and the SQL Server development team go to work for you.
Even though the UA is a great tool, if you have the resources to do so, it is a good idea to set up an additional test environment just for SQL Server 2008or later version. Also, you should thoroughly test your upgraded objects and code after the upgrade on a dry run, just to be sure you don’t miss anything. Remember to make full backups!
The UA advises on which aspects of your current setup should or need to be changed to become compatible with SQL Server 2008. Let’s look at how it works.
Getting Started with the UA
Before running the Upgrade Advisor, you must first install it. The easiest way to install the Upgrade Advisor is to start the SQL Server 2008 Installer. On the Installer Landing page is an option to install the Upgrade Advisor pic below. Alternatively, the Upgrade Advisor is available in the Servers\redist\Upgrade Advisor folder of the SQL Server installation media, or from the Microsoft Download Center. The Upgrade Advisor has the following system requirements:
If not installed already, the .NET Framework 2.0 is available on the SQL Server 2008 product media, and from the SDK, redistributable, and service pack download website. To install the .NET Framework 2.0 from the SQL Server 2008 media, locate the root of the disk drive. Then double-click the \redist folder, double-click the \2.0 folder, and run Dotnetfx.exe(for 32-bit) or Dotnetfx64.exe(for 64-bit), depending on your operating system.
If you run the SQL Server Installer, it installs the Windows Installer and .NET Framework requirements automatically if they are not detected. If upgrading from SQL Server 2000 Analysis Services, you need to install the SQL Server 2000 Decision Support Objects (DSOs) on the system where UA will be run to scan upgrade issues in Analysis Services. To install DSOs, run the SQL Server 2000 Setup program and click Install SQL Server 2000 Components.
Click Analysis Services to start the Analysis Services Setup program. In Select Components, make sure that the Decision Support Objects component is selected. Additionally, if you are upgrading SQL Server 2000 DTS packages, the SQL Server 2000 client components are required to scan SQL Server 2000 DTS packages. The SQL Server 2000 client components can be installed from the SQL Server 2000 installation disk. If you are upgrading from SQL Server 2005 DTS packages that were migrated from SQL Server 2000, you need to install the SQL Server 2005 backward-compatibility components to scan SQL Server 2005 DTS. Use the SQL Server 2005 installation disk to install backward-compatibility components.
The location where you can install SQL Server Upgrade Advisor depends on what you will be analyzing. Upgrade Advisor supports remote analysis of all supported components except Reporting Services. If you are not scanning instances of Reporting Services, you can install Upgrade Advisor on any computer that can connect to your instance of SQL Server and that meets the Upgrade Advisor prerequisites. If you are scanning instances of Reporting Services, you must install Upgrade Advisor on the Report Server.
As described in the following sections, the UA has two main functional areas: the Analysis Wizard and Report Viewer. The first time you use Upgrade Advisor, run the Upgrade Advisor Analysis Wizard to analyze SQL Server components. When the wizard finishes the analysis, you can view the resulting reports in the Upgrade Advisor Report Viewer.
The Analysis Wizard
You’ll be glad to know that the analysis process does not modify any code or data; that is left for you to do (or not do) at a later time. As an example, let’s run the UA’s Analysis Wizard against all the SQL Server components of a locally installed SQL Server 2005 instance. The Analysis Wizard examines objects that can be accessed, such as scripts, stored procedures, triggers, and trace files. Upgrade Advisor cannot analyze desktop applications or encrypted stored procedures. To start the process, click the Launch Upgrade Advisor Analysis Wizard hyperlink at the bottom of the Welcome page (see pic below). When the Analysis Wizard’s Welcome page appears, click Next. When you reach the SQL Server Components screen, choose all the components to be analyzed by checking their corresponding check boxes (see picture below).
You can also use this screen to ask the UA to analyze one or more SQL Profiler trace (.trc) files. This feature is useful for analyzing the T-SQL statements submitted from one or more applications for deprecated or discontinued features. You would want to set up and run a trace in SQL Profiler ahead of time to capture a representative sample of the T-SQL executed against the server. You can also scan T-SQL batch files (maintenance scripts, procedures, functions, triggers, and so on) to check for deprecated or discontinued features used in the SQL scripts. For this example, create a SQL batch file that contains the following T-SQL commands, most of which are deprecated in SQL Server 2008, just to test the UA:
use bigpubs2008 go
EXEC sp_configure ‘set working set size
SELECT * FROM master..syslockinfo
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(pr_info) FROM pub_info WHERE pub_id = ‘6380’
SELECT * FROM Stores s, Stores s2 WHERE s.Stor_Id *= s2.Stor_Id AND s.Stor_name <> s2.Stor_name
READTEXT pub_info.pr_info @ptr 0 25
When you’re ready, click Next, and the Upgrade Advisor presents screens for each of the SQL Server components you selected previously (the pic shown above) asking for login information or to select packages to analyze. Note that if you selected a component, but that component isn’t installed on the server you are upgrading, the Upgrade Advisor reports that no instances of that component could be found on the server and you cannot proceed until you go back and deselect the component. If you selected to analyze DTS or SSIS packages, the DTS and SSIS Parameters screens (shown in the pic below) give you the option to analyze all the packages stored in the target instance or to specify one or more package files to be analyzed.
Free online sql Server dba learning
Be sure to select only components that are actually installed on the server being upgraded; otherwise, the Upgrade Advisor stalls at the appropriate feature screen with an error message that the feature could not be found on the specified server.
When the Connection Parameters screen appears, choose the target server, select an authentication method, and if using SQL Server authentication, enter your username and password so that the UA can connect to your instance. Click Next, and the SQL Server Parameters screen, shown in below, appears. Choose which (if any) databases to analyze.
Note that the DTS Parameters screen advises that you must install the Legacy Components feature when installing SQL Server 2008; otherwise, SQL Server 2008 will not be able to run your DTS packages (unless they are upgraded to the new SSIS format). To upgrade your DTS packages, you need to use the DTS Migration Wizard, which is installed with SSIS and discussed later in this website, in the section “Migrating DTS Packages.”
When you’re all set with your DTS and SSIS selections, click Next to reach the Confirm Upgrade Advisor Settings screen. Make sure that all the SQL Server services you are analyzing are running and (if you’re happy with your selections) click the Run button to begin the analysis. As you can see from the Upgrade Advisor Progress screen that appears (see in the picture below), the wizard performs a task-based study of each component, providing per-step reporting, similar to the installer and the System Configuration Checker (both discussed later).
When the analysis is complete, the UA Progress screen presents a Launch Report button. The output of the UA Analysis Wizard is an XML report that you can view via the second major component of the UA, the Report Viewer, described in the next section.
You can view your last-generated report by using the Report Viewer; you can find the link to launch it on the main screen. If you run the UA more than once against the same SQL Server instance, however, you must save your previously generated reports to a directory other than the default output directory; otherwise, the previously generated report will be overwritten.
UA reports are saved by default to the folder My Documents\SQL Server 2008 R2 Upgrade Advisor Reports\Servername, and then they are broken down into separate XML files by component (for example, AS.xml for Analysis Services, DE.xml for the Database Engine).
You can launch the Report Viewer to figure out what to do about the issues the UA may have uncovered. Click the Launch Report button to proceed.
The Report Viewer
The Report Viewer is one of the most important tools in the upgrade process because it provides per-issue messaging, resolution tracking, and (in many cases) hyperlinks to the compiled help documentation distributed with the UA. Issues are organized in the Report Viewer on a per-server and then per-component basis. They can be filtered by type (that is, all issues, all upgrade issues, pre-upgrade issues, all migration issues, and resolved issues), and you can track your resolution progress by checking the Issue Has Been Resolved check boxes. Below picture shows the main user interface of the Report Viewer of SQL Server 2014 the feature is the same with 2008 R2
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute providing SQL Server and T-SQL (SQL Server) for Developer Training, SQL Data base SQL DBA Training and strong core of Business Intelligence (MSBI / SQL BI) core Training and we will guide you for database administrator training and placement, sql developer training and placement and we will prepare you for microsoft sql server database certifications
We have been working and providing SQL DBA classes partnered with numerous consultancies in USA. We carryout training on SQL Server 2008 to 2017 versions. We ensure that every one of our sessions are especially intelligent and very much organized with questions illuminations during and after each course.
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE