SQL Server 2005 Database Upgrade essentials and tips

server| Skills | data | Database SQL Server is not a product that Microsoft is constantly updating, so when Microsoft wants to release an update, it's worth paying attention to. Therefore, before you upgrade from a previous SQL Server 2000 or SQL Server 7 to the most recently published SQL Server 2005, you need to be aware of some of these updates.

One caveat: SQL Server 2005 is able to support the largest data warehouse by developing from a basic database to a truly enterprise-class product. Although the old version of SQL Server is also good and scalable, SQL Server 2005 has pushed the previous version to its highest peak. So we have reason to understand the update and change.

　　Changes in management

You may have heard all sorts of talk about "junk" Enterprise Manager and Query Analyzer in Microsoft SQL Server 2005, but in fact, the management tools for SQL Server 2005 have been well changed. The new SQL Server Management Studio (Figure A) is more complex than its predecessors, but from a management standpoint it is more advantageous to the management of various tasks. Now, with a simple tool, you can make adjustments to the structure of the database, running complex queries that can run other tasks at the same time, including establishing business Intelligence and analysis Service.

Figure A

SQL Server Management Studio

Even users who use SQL Server Express Edition (equivalent to previous MSDE) can enjoy the GUI to manage the free version of SQL Server. Although the Express version has its own limitations (for example, only the maximum 4GB database size can be used), it can be used to test and build small programs. In addition to the GUI, this product, called SQL Express Management Studio (currently in beta), also makes it a more popular choice in the Windows environment.

A little reminder

If you read the various SQL Server forums carefully, you'll find that a lot of people complain about the stability of Management Studio. I can't give my own answer, but I can offer a little bit of advice: Install SQL Server 2005 in your lab and start testing, and now you can run all the possible problems and test all the programs, and this process will help you familiarize yourself with this new management tool.

SQL Server Upgrade Advisor

While it is not a substitute for real-world program debugging, Microsoft's SQL Server Upgrade Advisor (available from a SQL Server 2005 vendor) can help you quickly complete your program debugging. The main function of this tool is to help you find and identify potential problems that you may encounter when you update the database server and related programs. Upgrade Advisor can run in SQL Server 7 and servers.

Now, I'll review the installation and basic usage of SQL Server Upgrade Advisor. I recommend downloading this tool instead of using the vendor version, as this will ensure that you are using the latest version.

Upgrade Advisor requires some noteworthy prerequisites:

• Windows Service Pack 4; Windows Server 2003 Service Pack 1; Windows XP Service Pack 2.
• Microsoft. NET Framework 2.0.
• If you are using Analysis Services, you must install SQL Server Decision Support Objects (DSO).
• If you need to analyze Data transformation Services, you must install the SQL Server 2000 client component.
• The processor speed must be more than a few MHz.
• The disk you can use must be greater than MB.

I'm not going to use an interface to describe the installation process of Upgrade Advisor in detail, as it is one of the most typical Windows software installations, no different from other software installations. For the installation of this tool, I have installed the server to a Windows Server 2003 R2.

Through Start | All Programs | SQL Server 2005 | SQL Server Upgrade Advisor starts Upgrade Advisor. Figure B shows the introduction interface for Upgrade Advisor.

Figure B

Introduction to SQL Server Upgrade Advisor.

As shown on the interface, you can choose between two options:

• Upgrade Advisor Analysis Wizard: Perform a database service profiling to find any links that hinder SQL Server 2005 upgrades.
• Upgrade Advisor Viewer: Displays analysis results.

Obviously, you need to perform an analysis before you get a report. This analysis process can be completed in 5 steps:

1. Identify the 腟 QL Server 7 or 2000 component you want to analyze.
2. Provides authentication that upgrade Advisor can use for SQL Server.
3. Select the database and provide additional necessary parameters.
4. Perform the analysis.
5. View all results and documents that can help you modify any potential problem.

Component Selection

As a first step, you select the component you want to analyze (Figure C). This step is easier because all the work can be done by Upgrade Advisor. All you need to do is enter the name of the server running in SQL Server 7 or 2000, and then click the Detect button, and Upgrade Advisor will handle all the remaining tasks. For other reasons, if you have a selection error, you can make changes and click the Next button. In my lab server, Upgrade Advisor can detect that I am running SQL Server and the Data Transformation Services service.

Figure C

Select the SQL Server component you want to upgrade Advisor analysis

Certified SQL Server

To perform this step, Upgrade Advisor must obtain detailed information about SQL Server. To implement this procedure, the following operations must be entered into the server.

The next interface in the Advisor Wizard (Figure D) provides authentication methods (Windows authentication or SQL Server Authentication) and a SQL Server instance that selects the Upgrade Advisor report. If you are running SQL Server 7, you will not be able to select another instance, because this version only supports a single instance. If necessary, you can provide a username and password so that Windows or SQL Server users have access to SQL Server.

If you have difficulty connecting to the server, make sure that the SQL Server Agent is running and try again.

Figure D

Provides the appropriate authentication method, causes upgrade Advisor to carry on.

Select Databases and Options

You can limit the Upgrade Advisor to only analyzing specific database components, or you can include tracing and SQL batch files in the analysis. For the example shown in Figure E, I will allow this tool to analyze all the databases in the experimental system, but does not include any trace or batch files.

Figure E

Select all parsed databases and determine whether to include trace or SQL batch files.

In the next wizard interface (Figure F), select Data Transformation Services that handles SQL Server. Data Transformation Services is the foundation of SQL Server 2005 's Business Intelligence feature, which is the complete error correction transformation in SQL Server 2005.

Figure F

Select DTS Transition Options

Perform the profiling process

Upgrade Advisor has begun to deal with the analysis process through the already completed selection. Before executing, this tool provides a summary of all your selections, as shown in Figure G. Click the Run button to continue the analysis process. Figure H and I are the processes of the analysis.

Figure G

Select the Run button to start profiling

Figure h

The analysis process of Upgrade Advisor

Figure I

2 warnings appear when profiling completes

 

[1]  [ 2]  next page