1 chapter overview preparing to upgrade performing a version upgrade from microsoft sql server 7.0...
TRANSCRIPT
1
Chapter Overview
• 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
2
Multiple Versions on a Single Computer
3
SQL Server 6.5 Upgrade Options
• 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.
4
SQL Server 7.0 Upgrade Options
• 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.
5
SQL Server 6.5 Upgrade Requirements
6
SQL Server 7.0 Upgrade Requirements
7
Upgrade Preparations for All Versions
• 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.
8
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.
9
SQL Server 7.0 Version Upgrade
• 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.
10
Post-Upgrade Tasks
• 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.
11
Upgrading Metadata Services and the Repository
• 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.
12
Online Database Upgrade from SQL Server 7.0
• 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.
13
Post-Upgrade Tasks
• 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.
14
SQL Server 6.5 Version Upgrade
• 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.
15
Troubleshooting a SQL Server 6.5 Upgrade
• 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.
16
Backward-Compatibility Levels
• 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.
17
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.