INF: Frequently Asked Questions - Conversion

• Microsoft SQL Server version 7.0

SUMMARY

This article is meant to address frequently asked questions regarding the conversion of a SQL Server 6.5 installation to SQL Server 7.0.

MORE INFORMATION

1. Q. How long will the conversion process take?
   
   A. Many factors will affect the amount of time needed to convert SQL Server 6.x databases to SQL Server 7.0. Each object in the SQL Server 6.x database must be rebuilt in the SQL Server 7.0 database, and every row must be transferred. Depending on the complexity of each database, the length of time needed to convert two 10-GB databases that have differing numbers of rows and objects may vary widely. Also, the hardware platform, number of processors, disk subsystem, and amount of RAM will play a significant part in the amount of time required for the conversion. Selecting "data validation" during setup will increase the amount of time needed to perform the upgrade by a factor of two. Some typical times for the upgrade process are:
   
   

   Size of database	Estimated time required to convert
   400 MB	Less than 20 minutes
   1 GB	Less than 1 hour
   10 GB	Less than 4 hours
   50 GB	Less than 12 hours
   100 GB	Less than 24 hours




2. Q. How much additional disk space does SQL Server 7.0 use than SQL Server 6.5?
   
   A. With one computer, converting from SQL Server 6.x to SQL Server 7.0 requires about 1.5 times the space your SQL Server 6.x data currently uses. In most cases, the actual space used will be less than your SQL Server 6.x data currently uses. If you do not have a sufficient amount of disk space, you may want to consider using either the one-computer tape conversion or the two-computer conversion method. Each of these options is described below.

   One-Computer Tape Conversion Method

   With this method, your SQL Server 6.x data files are backed up to a SQL dump. Optionally, you can specify that a file backup of the physical files is also performed. After this step is accomplished, the data devices are deleted from the drives and the SQL dump is used during the loading of the new SQL Server 7.0 databases. If you have any difficulties, you can reinstall SQL Server 6.x and restore the original data files from the physical backup of the data files.

   Two-Computer Conversion Method

   With this method, the new computer will require roughly 1.5 times as much disk space as your SQL Server 6.x databases currently use. The final amount of space used may be less, depending on the data types used and the actual amount of free space in the SQL Server 6.x databases. When converting a database, SQL Server 7.0 will suggest a data file size and use the previous log file size for the first log file. Note that this behavior is different when creating a new database in SQL Server 7.0. In that case, SQL Server 7.0 creates the new database with a default size of 2 MB.




3. Q. Can users be connected to the SQL Server 6.x computer while the conversion process is running?
   
   A. No. During the upgrade process, the SQL Server 6.x computer is stopped and started while objects are scripted and data is extracted. Also, when the data transfer is started, only SQL Server 7.0 is running, and it is not possible to access SQL Server 6.x.



4. Q. How should I configure my server before performing the upgrade?
   
   A. If you will be upgrading your existing SQL Server 6.x to a new computer running SQL Server 7.0, both computers should be configured to use a domain user name and password for the MSSQLServer service. The domain user account should also belong to the Administrators group on both computers. A local system account is sufficient for a one-computer upgrade. If you are upgrading across different domains, you must have a trust relationship set up between the domains before starting the upgrade.
   
   NOTE: If you use the local system account with the one-computer upgrade process and you also use a local account, you will be unable to upgrade integrated logins (because setuser will fail). Therefore, you should only use the local system account with a one-computer upgrade process if you are using standard security, not integrated security.



5. Q. Can a SQL Server 4.21 installation be converted directly to SQL Server 7.0?
   
   A. No. Because of the many database changes between SQL Server 4.21 and the structure of the SQL Server 6.x databases, the conversion process will not run using SQL Server 4.21 databases. To upgrade a server running SQL Server 4.21 to SQL Server 7.0, you must first upgrade the server to either SQL Server 6.0 or SQL Server 6.5 before converting to SQL Server 7.0. If you are running SQL Server 4.21 and you want to upgrade to SQL Server 7.0 but you do not have SQL Server 6.x, you can obtain a copy of SQL Server 6.5 for the purpose of upgrading to SQL Server 7.0 from the Microsoft Fulfillment Center.
   
   NOTE: Before you convert your installation from SQL Server 4.21 to SQL Server 6.x, be sure to run Chkupg65.exe or Chkupg60.exe, to verify that the structure and contents of your SQL Server 4.21 databases have no conflicts with the new ANSI 92 requirements that are implemented in the SQL Server 6.x versions.



6. Q. Can I load my SQL dumps onto a new computer and upgrade the new computer to SQL Server 7.0?
   
   A. Yes. However, be sure to also copy the master database to the new computer. If databases from a different computer are loaded onto a new computer, the SQL Server login IDs will not be present in the master database. The upgrade will fail to create objects for users who do not have login IDs in the master database. Also, if integrated security is used and no local group exists for the users of a database that is loaded into SQL Server, logins may fail.



7. Q. Can I consolidate databases from two or more 6.x SQL Servers on to one version 7.0 SQL Server?
   
   A. No. The upgrade process will keep track of the server being upgraded and will only allow databases from one 6.x SQL Server to be upgraded. Consolidating databases from different servers may cause problems in user login IDs, user accounts, and object permissions. If you want to consolidate several databases from different 6.x SQL Servers, move all databases to be consolidated to a 6.x SQL Server and verify that your applications work correctly before upgrading to SQL Server 7.0.



8. Q. Do I need to run the database consistency checker (DBCC) statements on the server before upgrading my databases?
   
   A. Although it is not necessary to run DBCC statements before upgrading, doing so is recommended. Depending on the extent of any logical inconsistencies existing in the database, the upgrade process may not complete successfully. In a situation where there may not be sufficient time in a maintenance window to complete the upgrade and DBCC checks, consider running the DBCC checks on a backup or secondary server, using dumps of the databases to be upgraded.



9. Q. Can I upgrade only one or a few of my databases to SQL Server 7.0?
   
   A. Yes. You can upgrade one, some, or all of your SQL Server 6.x databases to SQL Server 7.0. You can even convert individual databases as a test or as practice before upgrading all of the databases on the server. However, note that it is recommended that you convert all production databases on a server at the same time; this will minimize potential problems. If you only want to convert a subset of your existing SQL Server 6.x databases, you should convert them all at the same time.
   
   If you will not be upgrading all of the databases at the same time, there are some issues to be aware of. Any objects that rely on the contents of other databases, including views, stored procedures, and triggers, will fail to be created if the object or the dependent database does not exist.
   
   If the SQL Server 6.x model database has been modified to include additional objects, it should be converted either at the same time as all of the other SQL Server 6.x databases, or last. Any objects that were created in SQL Server 6.x databases as a result of non-default objects being added into the SQL Server 6.x model database will be scripted during the upgrade process.
   
   When other SQL Server 6.x databases are upgraded after the model database has already been converted, they will contain non-default objects based on the SQL Server 6.x model database. Because the objects will be added to the new SQL Server 7.0 databases when they are initially created by the SQL Server 7.0 model database, the creation script will fail to create any objects that already exist in the database. Therefore, by converting the model database last, any changes in the database structure will only be applied to new SQL Server 7.0 databases. All of the non-default objects in the SQL Server 6.x converted databases will have been created by scripts during the conversion process of those databases.



10. Q. Can I run SQL Server 6.x and SQL Server 7.0 at the same time on a single computer?
    
    A. No. You can only run one of these versions of SQL Server any given time. Both versions share common Windows NT resources and registry information, and only one version can be started at a time. However, you can switch between SQL Server 7.0 and SQL Server 6.x by using the Switch utility. The Switch utility is intended for the conversion process and testing; it is not meant for hosting two versions of SQL Server on one computer in a production environment.
    
    If you have converted your databases to SQL Server 7.0 on the same computer as the existing SQL Server 6.x installation, it is important to realize that these two sets of databases are independent; they are not synchronized, and any modifications to the SQL Server 6.x data while SQL Server 6.x is running will not be reflected in the SQL Server 7.0 databases. Conversely, any changes to the SQL Server 7.0 data will not be reflected in the SQL Server 6.x databases.
    
    CAUTION: If you are installing SQL Server 7.0 alongside SQL Server version 6.x on the same computer, do not install SQL Server 7.0 in the same directory as SQL Server 6.x. They must be installed in separate directories.



11. Q. I'm getting "@@servername not valid" errors during conversion. Why?
    
    A. This error message can occur either if the version 6.x SQL Server you are upgrading has not been named. To resolve these problems, perform the following steps:
    
    
    1. In ISQL or ISQL/w, use the SELECT @@Servername statement to make sure the server has a name.
    
    
    
    2. If the server does not have a name, use the following stored procedure to add a name:
       

       
       sp_addserver <server_name>, local 

    
    
    



12. Q. What causes the "Cannot open default database" and "Error querying @@servername" errors?
    
    A. If the default database for the system administrator (SA) has not recovered yet, or if it is marked as suspect, the Upgrade Wizard will produce either of these error messages. Resolve the problem with the default database and run the Upgrade Wizard again.



13. Q. The conversion process seems to stop responding and fails. Why?
    
    A. If applications or services have open ODBC connections to the SQL Server 6.x server during the conversion process, they may not allow the SQL Server to shut down completely. The conversion process will not proceed on to the next step if it does not receive verification that the SQL Server 6.x server has been completely stopped. The conversion process will appear to stop responding and eventually fail in this situation. To resolve this situation, close all applications and services that may have ODBC connections or may be using SQL Server before performing the upgrade. If SQL Profiler or SQL Trace is connected to the version 6.x SQL Server, you will see a similar problem in that while the server will not actually stop responding, tasks that once happened quickly use so much CPU time that they become exponentially slower.



14. Q. Where can I look to see a record of any errors that I may have encountered during the conversion process?
    
    A. During the conversion process, detailed logs are generated and stored in your SQL directory. If any errors occur during the conversion process, you will see a dialog box at the end of the process. This dialog box will display the contents of the error files. This output file is located in the MSSQL\Upgrade\servername_date_time directory. Each database will have its own subdirectory with output and error files that were generated during the conversion process.



15. Q. Some of my stored procedures either did not convert properly or did not convert at all; what went wrong?
    
    A. The following are some possible reasons why your stored procedures may not have been converted correctly:
    
    
    • The text for the stored procedure must start with CREATE PROCEDURE; if your procedure begins with BEGIN TRANSACTION, followed by the CREATE PROCEDURE text, the stored procedure will not be created.
    
    
    
    • Stored procedures that are based on system tables will not be created, because of changes in the column names and a change in the structure of the system catalogs.
    
    
    
    • If the name of a stored procedure was changed by using the sp_rename stored procedure, the original name for the stored procedure in the syscomments system table was not changed. In this case, the stored procedure will be created with the original name. Then if you have another existing stored procedure with the same name or one that was created before the original, the second stored procedure will not be created because an object by that name will already exist.
    
    
    
    • Stored procedures that are created by other stored procedures will not be created because they have no entry in the syscomments table.
    
    
    

Additional query words: hang hangs hung dimmed gray grayed grey greyed out type dataype datatypes localsystem

Keywords          : SSrvInst 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbinfo 

Last Reviewed: March 13, 1999