Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 – Upgrading from earlier versions of SQL Server

By Exforsys | on December 7, 2005 |
SQL Server 2005

SQL Server 2005 – Upgrading from earlier versions of SQL Server

In this tutorial you will learn about SQL Server 2005 – Upgrading from earlier versions of SQL Server. SQL Server 2005 permits the upgrading from version 7.0, 2000 (with service pack 3) and 2003. SQL Server 6.5 will have to be upgraded to SQL Server 7.0 with service pack 7.0 and then to SQL Server 2005.

The process of upgrading to these various versions requires a lot of careful planning. Data, Data Transformation Services packages, agent jobs, replication setup, failover clustering setup, Analysis Services objects, Full text search objects, Reporting Services configuration, Notification Services configuration etc will have to be upgraded. The Upgrade Advisor helps the user analyze the system and generates a report identifying issues that needs to be addressed pre and post upgrading.

Planning for an upgrade

Before beginning an upgrade check whether the required hardware and software are available. Read the backward compatibility notes and make the necessary changes to the application and scripts. The Upgrade advisor tool will have to be run and all issues highlighted for resolution will have to be addressed. All SQL Server database files will have to be backed up. Appropriate DBCC commands on databases to be upgraded will have to be run to ensure consistent state. SQL Server System databases will have to be configured with autogrow setting to ensure that they will have adequate disk space. All startup procedures will have to be disabled; else they will block the process of upgrading. Replication log should be emptied and Replication will have to be stopped.

Once all the above has been done, SQL Server 2005 setup has to be run and the instance installed. Thereafter, move user databases to the instance by using backup and restore or detach and attach functionalities in SQL Server 2005. Then register the server, repopulate full text catalogs, update the statistics and run Surface Area Configuration tool.

Another factor that needs to be taken into account while preparing for an upgrade, are the features that have been deprecated in SQL Server 2005. The features that are going to be deprecated are as under:

1. The DUMP and LOAD statements.
2. The SETUSER statement
3. sp_addtype.
4. sp_attach, Sp_attach_single_file_db, sp_rename, sp_dboption and sp_resetstatus.
5. sp_fulltext_catalog, sp_addlogin, sp_adduser.
6. System table are being replaced with backward compatibility views.

Some of the features of earlier releases are no longer supported. The features that are not being supported are as under:

1. English Query
2. isql.exe and rbuildm.exe
3. Northwind database
4. allow updates option of sp_configure.
5. Metadata component of SQL Server 2000

A number of breaking and behavior changes to the Database engine to support the new features that have been introduced in SQL Server 2005. The trigger_schema is added as the last column in the result set returned by the sp_helptrigger system stored procedure. WITH has been made mandatory for table hints. Qualified column names and aliases in the ORDER BY clause are resolved to columns of tables listed in the FROM clause unlike in SQL Server 2000.

SQL Server 2005 Upgrade Advisor

The Upgrade Advisor tool is used to analyze the system and installed SQL Server instance and identify issues that should be addressed before or after upgrading to SQL Server 2005. The Upgrade advisor has two components—the Analysis Wizard and the Report viewer. The former analyzes the system and saves the results into an XML file. The Report viewer reads the results and displays it. It allows sort and filter operations to be performed on the results. Each item in the Report viewer has a Tell Me More About This Issue and How to Resolve it links. The Upgrade Advisor documentation also provides information on various upgrade issues and steps required to resolve them.

The upgrade Advisor does not get installed automatically when SQL Server is installed. It has to be installed separately.

The process of upgrading each of the components is beyond the scope of this tutorial and will require a separate chapter for each process. Therefore, this section has been confined to a definition of the common issues relating to upgrading and touched upon the new features of SQL Server 2005 and the deprecated features have been listed to make the user aware of them. It has also briefly examined the features of the Upgrade Advisor.

« « SQL Server 2005 – Unattended Installations
SQL Server 2005 – Testing Troubleshooting » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • SQL Server Management Objects

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 Installation – Maintenance Plan Using Wizard

    November 29, 2005 - 0 Comment
  • SQL Server Architecture and Components

    September 13, 2005 - 0 Comment
  • SQL Server 2005 Configuration Manager

    December 18, 2005 - 0 Comment
  • Developing Client applications in SQL Server 2005

    November 1, 2005 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 Installation – Maintenance Plan Without Using Wizard

    December 2, 2005 - 0 Comment
  • Security Features in SQL Server 2005 for the Developer

    October 15, 2005 - 0 Comment
  • SQL Server 2005 – Populating the Database

    December 18, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT