The upgrade is the most important aspect of the SQL Server 2008 R2 platform management. To prepare for an upgrade, begin by collecting information about the effects of the upgrade and the risks it might involve. When you identify the risks upfront, you can determine how to lessen and manage them throughout the upgrade process.
Upgrade scenarios will be as complex as your underlying applications and instances of SQL Server. Some scenarios within your environment might be simple, other scenarios may prove complex. For instance, the existing data platform is hosted with high availability components such as failover clustering, database mirroring, and replication. Start to plan by analyzing upgrade requirements, including reviewing upgrade strategies, understanding SQL Server 2008 R2 hardware and software requirements, and discovering any blocking problems caused by backward-compatibility issues.
This recipe introduces you to the methods that need to be followed when you design an SQL Server upgrade process. It will also present the best practices scenario for pre-upgrade, during the upgrade, and post-upgrade tasks that are involved within the upgrade of current SQL Server instance to SQL Server 2008 R2 version.
The upgrade is not restricted to databases. It is important for the upgrade project to consider the various tools, components, and services of SQL Server 2008 R2 and non-database components, such as:
Business Intelligence Development Studio (BIDS)
SQL Server Reporting Services (SSRS)
SQL Server Analysis Services (SSAS)
Data Mining
Linked Server configuration
Log Shipping servers
Database Mirroring pair
SQL Server Replication
SQL Server Agent jobs
DTS Packages
SQL Server Integration Services (SSIS)
Microsoft Desktop Engine (MSDE) or SQL Server Express edition
Further to the list of tools, components, and services, you need to include technical issues (if any) and decisions that are involved in an upgrade to SQL Server 2008 R2, in addition to recommendations for planning and deploying an upgrade. The upgrade processes include upgrade strategies (pre/during/post), test, and rollback considerations and upgrade tools.
The windows architecture upgrade is also essential such as an upgrade from a 32-bit to a 64-bit platform. Additionally, the upgrade from a Standalone server to Microsoft Clustering services is required:
Upgrading from SQL Server 2005: Run the upgrade from the command prompt on each failover cluster node, or by using the Setup UI to upgrade each cluster node. If Full-text search and Replication features do not exist on the instance being upgraded, then they will be installed automatically with no option to omit them.
Upgrading from SQL Server 2000: This is similar to upgrading from SQL Server 2005. You can run the upgrade from the command prompt on each failover cluster node, or by using the Setup UI to upgrade each cluster node. It's supported for 32-bit scenarios only. Failover cluster upgrades from SQL Server 2000 (64-bit) are not supported.
Before you install SQL Server 2008 R2, we should also run the SQL Server Best Practices Analyzer (BPA) against your current legacy instances of SQL Server. If bad or questionable practices exist, you can then address them before the upgrade, moving the fixes through test and into production. The BPA tool installation is a straight-forward process and this diagnostic tool performs the following functions:
Gathers information about a server and installed SQL Server instances
Determines if the configurations are set in accordance with the best recommended practices
Produces a report on all configurations that indicates settings that differ from recommendations
The report consists of any potential problems in the installed instance of SQL Server and recommends solutions to potential problems
No configuration settings or data is changed when you execute this tool.
The following steps are classified as the best practices approach in preparing the environment for an upgrade:
1. To obtain the relevant Best Practices Analyzer (BPA) tool that suits your legacy instances of SQL Server, refer to the following links:
For SQL Server 2000 version download from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b352eb1f-d3ca-44ee-893e-9e07339c1f22&displaylang=en.
For SQL Server 2005 version download from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=DA0531E4-E94C-4991-82FA-F0E3FBD05E63.
For SQL Server 2008 and SQL Server 2008 R2 version download from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591.
2. To install SQL Server 2008 R2 BPA tool the following tools are required:
PowerShell V2.0: review the requirements and download it from http://support.microsoft.com/kb/968929.
Microsoft Baseline Configuration Analyzer (MBCA) V2.0: review the requirements and download from http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=1b6e9026-f505-403e-84c3-a5dea704ec67.
3. Start the installation process for BPA tool by double-clicking on the downloaded file.
4. Upgrade strategies include two fundamental methods, they are: 'side-by-side' and 'in-place'. It's worth mentioning about additional conventional methods of upgrade, such as using Copy Database Wizard and manual Schema rebuild method with scripts.
Using the side-by-side upgrade requires the user to move all or some data from an instance of SQL server 2000, or SQL Server 2005, or SQL Server 2008 to a separate instance of SQL Server 2008 R2. The variations in this strategy include, one-server—the new instance exists on the same server as target instance); and two-servers—the new instance exists on a different server than the target instance.
Using the in-place upgrade will involve a direct upgrade of the previous version of SQL Server to SQL Server 2008 R2, where the older instance is replaced.
Using Copy Database Wizard to upgrade an SQL Server 2000 or SLQ Server 2005 database, offers the advantage for the database to be available immediately, which is then upgraded automatically.
Using the Schema rebuild method with scripts is a manual operation, which requires individual script files to create database, tables, logins, users, and scheduled jobs. Additionally, these external components also require scripting, such as SSIS packages, Linked Server information, and database maintenance plans.
5. Documentation is the key to a successful upgrade. Everyone should work as part of a team. The planning process will begin with a document that stands as a communication to involve all the stakeholders and teams to complete the data platform upgrade process.
6. In case of any specific requirement from Business teams within the upgrade process, that information must be documented in an Upgrade document along with their contact information.
7. Using the documentation acts as a base to execute the upgrade during the deployment phase. The plan should be as detailed as possible, and you should store the resulting document or documents by using some form of change control, such as a source control system. In the rest of this section, we will detail these steps.
8. Finally, within that planning documentation, include the upgrade requirements in addition to the rationale for choosing an upgrade strategy (refer step 3) for each instance or class of instances. Use the rest of the plan to detail remaining issues.
9. Detail the steps required for taking the systems offline for a period of time and bringing them back online.
10. Upgrade the Advisor tool:
Upgrade Advisor is available in the
\X86(orx64, ia64)\redist\Upgrade Advisorfolder of the SQL Server installation media.The tool is also available from Microsoft SQL Server 2008 R2 Feature pack page: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en.
Within this feature pack page choose the appropriate file that suits to the environment,
X86, X64, orIA64.11. Run the SQL Server 2008 Upgrade Advisor to determine potential blocking issues:
Deprecated features
Discontinued features
Breaking changes
Behavior changes
It analyzes objects and code within legacy instances to produce reports that detail upgrade issues, if there are any, organized by SQL Server component.
The resulting reports show detected issues and provide guidance about how to fix the issues, or work around them. The reports are stored on disk, and we can review them by using Upgrade Advisor or export them to Microsoft Excel for further analysis.
In addition to analyzing data and database objects, Upgrade Advisor can analyze Transact-SQL scripts and SQL Server Profiler/SQL Trace traces. Upgrade Advisor examines SQL code for syntax that is no longer valid in SQL Server 2008 R2.
Whether you choose an 'in-place' upgrade or a 'side-by-side' upgrade, you can still run Upgrade Advisor on your legacy systems. We can run Upgrade Advisor from a local or remote server. To execute from a Command Prompt window, we require a configuration file name as an input parameter as follows:
C:\Program Files\Microsoft SQL Server 2008 R2 Upgrade Advisor\UpgradeAdvisorWizardCmd.exe" -ConfigFile "C:\Documents and Settings\SSQA.net\My Documents\SQLServer 2008 R2 Upgrade Advisor Reports\MyServer\Config.xml"
As we discussed, the best practices approach for a server instance or database upgrade process, it is essential to understand how the process works when you adopt any two upgrade choices that are available in SQL Server 2008 R2 and their characteristics.
In-place upgrade: Using the SQL Server 2008 Setup program to directly upgrade an instance of SQL Server 2000, or SQL Server 2005 to SQL Server 2008 results in the older instance of SQL Server being replaced. The number of servers used in 'In-place' upgrade is
1, which implies that all the steps within an upgrade are performed on the same server.Side-by-side upgrade: Using steps to move all or some data from an instance of SQL Server 2000 or SQL Server 2005 to a separate instance of SQL Server 2008. Inside the side-by-side upgrade strategy, we have two variations of how upgrade is processed:
Characteristics of an In-Place Upgrade vs. a Side-by-Side Upgrade are as follows:
|
Process |
In-Place Upgrade |
Side-by-Side Upgrade |
|---|---|---|
|
Number of resulting instances |
One only |
Two |
|
Number of physical servers involved |
One |
One or more |
|
Data file transfer |
Automatic |
Manual |
|
SQL Server instance configuration |
Automatic |
Manual |
|
Supporting tool |
SQL Server Setup |
Several data transfer methods |
The important process of the upgrade is a collection of database objects required from the previous versions of SQL Server. During the upgrade process, Transact-SQL code objects are essentially passive. Whether the process of an 'in-place' upgrade or a 'side-by-side' upgrade is chosen, the end result will be the same as far as your Transact-SQL code is concerned.
Note
If in case any external scripts required on the user database are not associated within Stored Procedures or Functions, then they will remain unchanged by a direct upgrade process. You must apply these scripts manually to ensure that the post-upgrade tasks for the user databases can be classified as completed.
Based upon the new server or existing server upgrade, it is a best practice to move any Transact-SQL external scripts to a new server, or correct references within your database to those scripts.
The Post Upgrade process is also equally important and it is easy to analyze how the new SQL Server 2008 R2 instance performs compared with your original SQL Server 2000, SQL Server 2005, or SQL Server 2008 instance. Download the RML Utilities for SQL Server from http://go.microsoft.com/fwlink/?LinkId=133157 under the Microsoft download site. These utilities stand as a suite of tools for load testing, workload replay, and performance analysis.
As soon as you have completed the upgrade tasks, you need to perform two important steps to ensure that the initial process of SQL Server upgrade is accepted. The two steps are:
1. Integrate the new SQL Server instance into the application and database server environment.
2. Application testing process, such as: Change connectivity settings to the new server, if a side-by-side upgrade is chosen.
3. Change authentication mode of upgraded SQL Server instance.
Linked servers: The current system might depend on linked server relationships and definitions that must be applied for an upgrade. The application might fail, if those linked servers are not defined and tested correctly.
Logins: All the required logins and users with relevant privileges on the database must be applied for an upgrade of databases. By using
Transfer Loginstask from SSIS, the logins can be transferred between a source and a destination SQL Server instance.Scheduled jobs: The routine administrative tasks are coupled as jobs that are executed using SQL Server Agent service, which are stored in
msdbsystem database, which may not be part of the usual upgrade method. In such cases, it is essential to script all the scheduled jobs on the source server and execute them on the destination server.Imports and exports: The legacy database system might receive data imports and can become the source of data exports. These imports and exports might use DTS, converted to SSIS, or use other tools. You have to isolate these requirements and make sure of the resulting upgraded instance's correct participation.
Components referring to older SQL Server versions: If the user is selectively transitioning legacy SQL Server instances, make sure that the resulting instance of SQL Server 2008 has components that can still connect successfully to the older SQL Server versions.
Drivers required for changing to a 64-bit version of SQL Server: These required drivers might include drivers for accessing other database systems and mainframes from a 64-bit server.
Patches, hotfixes, and cumulative updates: After you upgrade to SQL Server 2008 from another edition of SQL Server, you must reapply any hotfix or service pack updates to the upgraded SQL Server instance. The process of Slipstream helps here to avoid spending time installing hotfixes or service pack patch upgrades.
4. Determine whether the upgrade was successful by using the following methods:
Upgrade to support SQL Server 2008.
Change connectivity settings.
Change authentication mode.
Accept the upgrade, and how it will make the "go/no-go" decision:
5. Verify tests to ensure applications using the upgraded database servers run as expected and required.
6. If available, enlist the support of the QA team to develop appropriate acceptance tests.
7. Determine exactly when and how a rollback to the legacy SQL Server might be required.
8. Test the rollback plan.
If the application connectivity tests are successful, then the next step is to test the reporting section. For instance, if the installation includes custom report items, assemblies, or extensions, you must re-deploy the custom components. If you deployed and used any custom extensions, or custom assemblies for reports with SSRS 2000 or SSRS 2005, you need to redeploy the extensions or assemblies for use with SSRS 2008 R2.
These two steps need not necessarily be sequential. For example, you might apply some acceptance criteria immediately to obtain a go/no-go decision. This could then be followed by integrating the new instance and applying the remaining set of acceptance tests.
Update statistics include:
Backward compatibility with earlier versions of SQL Server was a high priority from SQL Server 2008 version onwards. So in most cases, applications will behave as in the past. There are several features from SQL Server 2008 version onwards that are marked for removal in the next version of SQL Server.
After you upgrade, you should remove the usage of these features from existing applications and avoid them in new development work. Several features from earlier versions of the SQL Server Database Engine are not supported in SQL Server 2008 and SQL Server 2008 R2, so you must use replacement features for these.
With an in-place upgrade, the upgrade process handles all aspects of the upgrade, automatically by upgrading the metadata for each database found in SSAS 2005. However, for SQL Server 2000, the upgrade process will not automatically reprocess the upgraded databases. Each database must be fully processed after the upgrade to ensure users can access the data that is contained in each database.
If there is a frequent upgrade failure, then the easiest resolution is to reinstall SSAS 2000. Restore the installation to the state before the upgrade process was started. This ensures that all the data and configuration information that is needed to restore the existing installation is available
The manual transfer of database objects needs to be performed with utmost care due to the nature of all database objects (inclusive within database and exclusive of database). The outer objects of SQL Server are also important for databases, such as the objects that you must transfer, which include the following: