Chapter 4: Upgrading to SQL Server 2000

1
Chapter 4 Upgrading to SQL Server 2000
2
Chapter Overview
P95

• Preparing to Upgrade
• Performing a Version Upgrade from Microsoft SQL
  Server 7.0
• Performing an Online Database Upgrade from SQL
  Server 7.0
• Performing a Version Upgrade from SQL Server 6.5

3
Multiple Versions on a Single Computer
P96-97
4
SQL Server 6.5 Upgrade Options
P97-98

• You can
• perform a version upgrade to a default instance
  using the SQL Server Upgrade wizard (and version
  switch between each version).
• You can perform a single-computer (using either a
  hard drive or tape) or two-computer upgrade.
• You can choose to upgrade some or all databases.
• You can also migrate data using DTS, Bcp, or
  Transact-SQL.

5
SQL Server 7.0 Upgrade Options
P98-99

• You can
• perform a version upgrade to a default instance
  of SQL Server 2000 using the SQL Server Setup
  program (thereby replacing the SQL Server 7.0
  instance).
• You can perform an online database upgrade to any
  SQL Server 2000 instance using the Copy Database
  wizard.

6
SQL Server 6.5 Upgrade Requirements
P99
7
SQL Server 7.0 Upgrade Requirements
P100
8
Upgrade Preparations for All Versions
P100

• Terminate all user activity in the database.
• Back up all system and user databases.
• Perform DBCC database consistency checks.
• Disable all jobs.
• Close all open applications.

9
Upgrade Preparations Specific to SQL Server 6.5

• Set the tempdb system database size to at least
  10 MB.
• Verify that the master database has at least 3 MB
  of free space.
• Verify that the master database contains logon
  information for all users.
• Disable any startup stored procedures.
• Ensure that there is enough hard disk space
  available to perform the upgrade.

10
SQL Server 7.0 Version Upgrade
P101

• Run Setup, and then click Upgrade Your Existing
  Installation.
• Choose authentication method to connect to the
  SQL Server 7.0 instance.
• The system databases are upgraded using a series
  of scripts.
• The Windows registry is upgraded.
• The client connectivity components and client
  tools are upgraded.

11
Post-Upgrade Tasks
P103

• Review the SQL Server error logs and the
  Sqlstp.log file if troubleshooting is necessary.
• Repopulate all full-text catalogs if full-text
  search is being used.
• Update statistics.
• Register servers in SQL Server Enterprise Manager.

12
Upgrading Metadata Services and the Repository
P103

• Neither Metadata Services nor the repository
  database are upgraded automatically.
• An upgrade of Metadata Services is required to
  save and retrieve DTS packages from Metadata
  Services.
• Upgrade the repository database to take advantage
  of new repository engine.
• Refer to the DTS Information Model and
  Upgrading and Migrating a Repository Database
  sections of Books Online for precise commands.

13
Online Database Upgrade from SQL Server 7.0
P104-109

• Use the Copy Database wizard in SQL Server
  Enterprise Manager.
• Connect to a source server and a destination
  server with system administrator privileges.
• If a remote server is involved, the SQL Server
  service account on the destination server must be
  a domain user account.
• Select the database, and specify move or copy.
• Modify defaults if necessary.

14
Post-Upgrade Tasks
P110

• Repopulate all full-text catalogs if full-text
  search is being used.
• Update statistics.
• Delete the underlying data and log files after
  verification of the upgraded database.

15
SQL Server 6.5 Version Upgrade
P111-117

• Run the SQL Server Upgrade wizard on a computer
  with a default instance of SQL Server 2000 that
  is using Mixed Mode authentication.
• Determine verification level.
• Specify database creation parameters for the data
  and log files being created for the user
  databases being upgraded.
• Specify system objects for transfer, including
  server, replication, and SQL Executive settings.

16
Troubleshooting a SQL Server 6.5 Upgrade
P118

• Text is missing from the syscomments table or
  objects were renamed.
• Stored procedures were embedded within other
  stored procedures, reference a nonexistent system
  table, or modify a system table.
• Table and views have NULL column names.
• Tables owned by a user who does not have CREATE
  permissions.
• NetBIOS server name does not match server name in
  SQL Server 6.5.

17
Backward-Compatibility Levels
P119

• A database-level setting that allows some
  Transact-SQL statements to retain their behavior
  from earlier versions of SQL Server.
• Used as a temporary measure until applications
  can be upgraded.
• Primarily used when upgrading from SQL Server
  6.5.
• Valid levels are 60, 65, 70 and 80.

18
Chapter Summary

• SQL Server 6.5 can version switch with SQL Server
  2000 or 7.0.
• With a SQL Server 2000 named instance, default
  instance can remain as either SQL Server 7.0 or
  6.5.
• SQL Server 7.0 can be upgraded to the default
  instance of SQL Server 2000.
• SQL Server 7.0 databases can be upgraded online
  to any SQL Server 2000 instance.
• SQL Server 6.5 can be upgraded to any default
  instance of SQL Server 2000.

19
Lab

• None