In this chapter, you develop plans and strategies to help you correctly install or upgrade SQL Server. Why bother with a planning stage? Why not just skip right to the installation or upgrade? SQL Server installation is a simple process but by planning ahead, you can make the correct decisions before the installation that affect the performance and operation of SQL Server. In the case of an upgrade, you can never make too many plans to limit server downtime and protect your database against problems encountered during the upgrade. Start by examining installation strategies and plans.
Developing an installation plan starts with the assessment of the requirements of your business or users, includes the selection and purchase of the hardware, and finishes with making decisions for specific SQL Server options. You begin the process by collecting user and system requirements. Then you examine possible hardware configurations and SQL Server options. You then create a checklist to use during system installation. Finally, you install SQL Server.
How do you determine the hardware system requirements and user requirements for SQL Server? How else: You ask questions and do some homework. Start with the user requirements or business requirements. Based on the requirements of the users or business, you can determine the size and type of hardware system you need to meet the requirements. Start with the following questions:
NOTE: One decision you won't have to make is which operating system to use. Microsoft SQL Server 6.x is supported only on Windows NT Server and Windows NT Workstation. If you have decided to use Microsoft SQL Server, the operating system war is already over!
The following sections expand on each of the preceding questions to help you determine
the type of system you need.
What Is the Purpose or Goal of the System? The first questions you might ask
yourself are, "What is the system for?" and "Is the system for a single
department with 10 users or for a very large database with several hundred users?"
In most cases, a system supporting more users requires more memory, disk space, and
processing power. Is the system a dedicated SQL Server system or does it perform
other activities like file and printing services? Is the system replacing another
system as a result of downsizing or right-sizing? If it is replacing an existing
system, you already have a lot information available to you (such as the current
load on the system and the current system's shortcomings). Is the system a production
system or a development/test system? You will want more fault tolerance and more
storage capability on a production server than you typically need on a development
server.
What Are the Database Requirements? What are the database requirements for
the system? Will the SQL Server primarily be used for decision support systems or
transaction systems? How heavy is the expected transaction load? If the system is
transaction driven, try to determine the number of expected transactions per day
and how the transactions are processed. For example, is the server idle for eight
hours and does it then process all the transactions during a few hours, or does it
process the transactions evenly throughout the day? What is the expected size of
the database? Are you moving databases from another system to SQL Server because
of downsizing or right-sizing? If so, you should be able to obtain information such
as the current database size, expected database size, and the transaction load of
the system from the current system.
TIP: If you have the means, dedicate a machine for SQL Server. Then you can tune the hardware to give the best SQL Server performance.
What Are the User or Business Requirements? It is always important to understand the requirements and expectations of the individuals who use SQL Server. What type of query response time do the users expect? How many users will be logged on to SQL Server at one time? What are the backup and storage requirements of the users or business? How Much Money Will it Cost? Maybe this question should be listed first! In the real world, the difference between the system you need and the system you get is the amount of money you have available to spend on the system. (Enough said!)
After you obtain the answers and information to the questions described in "Step 1: Determine System and User Requirements," in the first part of this chapter, you are ready to select the hardware platform for your SQL Server. For this discussion, the hardware platform is divided into four areas:
The following sections examine each area and the type of decisions you need to
make for each area. Hardware When determining which type of hardware platform to
use, the first and last place to check is the Windows NT Hardware Compatibility List
to make sure that the brand and model of the machine you are considering is on the
list. If the brand and model you are interested in is not on the compatibility list,
download the latest list from an electronic bulletin board. If the machine is still
not listed, check with the manufacturer or Microsoft.
TIP: Save yourself a lot of problems and potential headaches: Use only the machines approved for Microsoft Windows NT. Although you may get other machines to work, I have seen the difficulty involved and the potential to not get the machine up and running when using non-approved platforms and configurations.
At the time this book goes to press, Windows NT is supported on the following microprocessors:
NOTE: Remember to check the compatibility list; support for new systems is an ongoing process.
So, how do you determine the correct hardware platform for your business or organization? Start with cost and examine hardware platforms within your budget. There is no point wasting your time researching hardware platforms you can't afford.
The next step is to use the information you gathered earlier--such as the expected number of transactions during a given time period--and talk to the hardware manufacturers or integrators to see whether the platform you are considering can meet those goals and requirements. Check for SQL Server benchmarks on the particular platform and ask to speak to other clients currently using the platform. Consider other factors such as manufacture reliability, service, and maintenance. These three factors are extremely important if the machine runs into a hardware problem and you are faced with downtime. Consider expandability; for example, will you require multiple processors in the future? If so, can the current platform be expanded to accept more processors?
Do I Need SMP (Symmetric Multiple Processors)?
Right out of the box, Windows NT 3.51 supports up to four processors; SQL Server can take advantage of these processors without any special add-ons or configuration changes. In theory, a perfect scaleable SMP machine would scale 100 percent, meaning that if your SQL Server performed 20 transactions per second and you added a second processor, you would increase the number of transactions to 40 per second. The scalability of systems varies widely and can range from nearly 100 percent to below 60 percent. Check with the manufacturer. What does it mean to you and SQL Server? If you are performing heavy transaction database processing, you can expect your transaction performance to increase with the scalability of the system. If you perform 10 transactions per second and add a second processor on a system that provides 80 percent scalability, you can expect roughly 18 transactions per second. SMP works very well for transaction-based systems. What if you do primarily decision support (such as database queries)? Adding a second processor may not be the best way to improve your system performance. In decision support systems, the queries are I/O bound and not processor bound, so adding additional processors does not provide the same substantial performance gain you get with transaction-based systems.
Memory A common theme in this book is give SQL Server enough memory. Not because SQL Server is an inefficient memory hog, but because SQL Server uses memory very intelligently. Extra memory can provide you with some very cost-effective performance enhancements. The minimum memory requirement for an Intel-based Windows NT Server with SQL Server is 16M (RISC machines like the Alpha AXP and the MIPS require slightly more memory). If you want to use replication, the minimum requirement jumps to 32M of memory with at least 16M assigned to SQL Server.
TIP: Although a Windows NT server with 16M of memory is the minimum requirement, I recommend starting with 32M, allocating 16M for Windows NT and 16M for SQL Server. You can tune up from there.
The setup program allocates up to but does not exceed 8M of memory to SQL Server. Once SQL Server is up and running, you can increase the amount of memory allocated to SQL Server. How does SQL Server get memory? When SQL Server starts, requests are made to the operating system to obtain the configured amount of memory. Memory is allocated for the SQL Server executable code, static memory, data structures, and miscellaneous overhead. SQL Server divides the remaining memory into the procedure cache and data cache (as discussed in Chapter 19, "Which Knobs Do I Turn?").
TIP: When SQL Server starts, it requests the amount of memory in the configuration parameter. The operating system then allocates as much physical memory as possible to SQL Server; if needed, it uses virtual memory to meet the memory configuration requirement. Avoid setting the memory configuration option higher than the amount of physical memory available to SQL Server (subtract the memory required for Windows NT from your machine's total memory; the remainder can be allocated to SQL Server). Using virtual memory can slow performance. Never set the memory parameter too high; if SQL Server cannot get the required amount of memory from physical and virtual memory, the server does not start.
In later chapters, you learn to tune your server for the correct amount of memory.
At installation time, however, how much memory should you use? Microsoft has published
the following suggestions as rough estimates for SQL Server and Windows NT memory
configurations. These figures are from SQL Server's online documentation:
Machine Memory (Megabytes) | Approximate SQL Server Memory Allocation (Megabytes) |
16 | 4 |
24 | 8 |
32 | 16 |
48 | 28 |
64 | 40 |
128 | 100 |
256 | 216 |
512 | 464 |
Configuration & Tuning of Microsoft SQL Server for Windows NT on Compaq Servers (February 1994, Database Engineering, Compaq Computer Corporation) suggests the following formula to use as a rough estimate for memory allocation:
SQL Server Memory = 5MB for Kernel and Data Structures
+ (2% Total Data and Index Space) + (50 KB * Number of Users)
Regardless of the amount of memory you start with, once SQL Server is up and running, you can monitor SQL Server to more accurately determine your memory requirements. Disk Drives One of the most important system decisions you can make is the type of disk drives and disk controllers you select. Selecting the proper disk system has a big impact on the overall performance of the SQL Server system and the type of data fault tolerance used to protect the databases.
CAUTION: Take special care in selecting your disk system. Disk I/O is the typical bottleneck found in database systems.
Before you get into the specifics, you want to select fast disk drives and smart controller cards to take advantage of Windows NT multitasking and asynchronous read-ahead features. When buying disk drives for a database server, consider using more, smaller physical drives rather than one large physical drive. Doing so allows you to spread your databases and transaction logs over several different physical devices. If you are considering buying one 2G hard drive, for example, reconsider and purchase two 1G hard drives or four 500M hard drives.
TIP: The asynchronous read-ahead technology in SQL Server 6.x is beneficial only with multiple disk configurations and smart disk controllers that have asynchronous capabilities.
Just as important as the speed of your hard disk system is the fault tolerance offered in modern disk drive systems. You want the best protection for your databases with optimum performance. One option available to you is the use of RAID (Redundant Array of Inexpensive Disks) disk drive configurations. RAID disk configurations use several disk drives to build a single logical striped drive. Logically, a striped drive is a single drive; physically, the logical drive spans many different disk drives. Striping the drives allows files and devices to span multiple physical devices. By spreading the data over several physical drives, RAID configurations offer excellent performance. Another benefit of RAID configurations is fault tolerance and recovery. A RAID 5 configuration can lose a single disk drive and recover all the data on the lost drive. When a new drive is added, the RAID configuration rebuilds the lost drive on the new drive. A RAID 5 system offers good protection and performance for your databases. RAID configurations can be hardware-based solutions or Windows NT software-based solutions. Hardware-based RAID solutions are typically faster than software-based RAID solutions. File System Should you use NTFS (New Technology File System) or FAT (File Allocation Table)? From a performance standpoint, it does not really matter (the performance difference between the two file systems is negligible). In general, NTFS performs faster in read operations and FAT performs faster in write operations. If you use the NTFS file system, you can take advantage of Windows NT security. If you are required to have a dual boot computer, you should use a FAT partition.
TIP: I typically recommend NTFS, which can take advantage of NT security and auditing features.
The Right Platform What is the right platform for SQL Server? The best system you can afford that will do the SQL Server processing you require! A good configuration for a SQL Server system is shown in Figure 5.1: a computer configured with one or many processors and starting with 32M of memory. Use a RAID 5 stripe set disk configuration for the databases and the operating system and place SQL Server on a nonstriped drive. How could this system be enhanced? Add additional stripe sets or more memory. Additional stripe sets can give you additional logical drives so that you can place a table on one logical drive and its index on another. For the memory requirements, monitor your SQL Server and determine the correct amount of memory for your Server. After all, SQL Server can run with as little as 4M of memory and as much as 2G of memory.
Figure 5.1.
A typical SQL Server hardware configuration.
When you begin a new SQL Server installation, you are asked several questions such as your name, company name, and the type of license agreements for the SQL Server. You should be able to answer these questions with no problem--but you are also asked to answer other questions that affect SQL Server performance, maintenance, and behavior. Examine the following installation topics in more detail to help you make the correct choices for your SQL Server:
The master Device During installation, you are asked to give the drive, path, and filename of the master device. The master device is the most important database device and is described in more detail in Chapter 8, "Managing Devices." The master device contains the master database, which houses all the SQL Server information required to manage and maintain the server's databases, users, and devices (basically, all the information required to maintain and run SQL Server). The master device also contains the model and tempdb databases, as well as the optional pubs database. The default name for the master device is master.dat and the default path is the drive and root directory selected for the SQL Server installation in the directory \DATA. The default/minimum size for the master device is 25M.
NOTE: The default size for SQL Server version 4.21 is 15M. The minimum installation size for the master device in version 6.x is 25M. You should use a size of 35M to 40M to give you room for expansion. A larger master device size gives you more room to create SQL Server objects and enables you to expand the temporary database (tempdb) beyond the default of 2M (if tempdb is not placed in RAM).
Character Set The character set is the set of valid characters in your SQL Server database. A character set consists of 256 uppercase and lowercase numbers, symbols, and letters; the first 128 characters in a character set are the same for all the different character sets.
NOTE: If you plan to use SQL Server replication, you must select the same character set for all the SQL Servers participating in replication.
Following are some common character sets you can choose from at installation time:
Code page 850 (Multilingual) | Code page 850 includes all the characters for North American, South American, and European countries. |
Code page 850 is the default character set for SQL Server 4.21 installations. | |
ISO 8859-1 (Latin 1 or ANSI) | This character set is compatible with the ANSI characters used by Microsoft Windows NT and Microsoft Windows. |
ISO 8859-1 is the default sort order for SQL Server version 6.x. | |
Code Page 437 (US English) | The common character set used in the United States. Code Page 437 also contains many graphical characters that are typically not stored in databases. |
Other available character sets are as follows:
Code Page 932 | Japanese |
Code Page 936 | Chinese (simplified) |
Code Page 949 | Korean |
Code Page 950 | Chinese (traditional) |
Code Page 1250 | Central European |
Code Page 1251 | Cyrillic |
Code Page 1253 | Greek |
Code Page 1254 | Turkish |
Code Page 1255 | Hebrew |
Code Page 1256 | Arabic |
Code Page 1257 | Baltic |
CAUTION: Deciding on the correct character set is important because the character set cannot be changed easily. Changing the character set requires rebuilding and reloading all your databases.
Sort Order The sort order you select for your SQL Server determines how the data is presented in response to SQL queries that use the GROUP BY, ORDER BY, and DISTINCT clauses. The sort order also determines how certain queries are resolved, such as those involving WHERE clauses. For example, if you choose a sort order that is case sensitive and you have a table called MyTable, the query to select all the rows from MyTable must have the following format:
Select * from MyTable
If the sort order selected is case insensitive, the preceding query could be written in following manners:
Select * from MyTable
Select * from mytable
Select * from MYTABLE
SQL Server offers many different sort orders, each with its own set of rules. The following sections briefly examine some of the possible sort order choices. Not all sort orders are available for every character set. Dictionary Order, Case Insensitive Dictionary order means that the characters, when sorted, appear in the order you find them in a dictionary. Dictionary order, case insensitive, uses the following rules to compare characters:
NOTE: Dictionary order, case insensitive, is the default sort order for SQL Server 6.x.
Binary Sort Order The binary sort order uses numeric values to collate the data. Each charter is compared to its numeric representation of 0 to 255. Data does not always come back in dictionary order; for example, UUU returns before aaa in ascending order. Dictionary Order, Case Sensitive Dictionary order, case sensitive, uses the following rules:
TIP: If you have two servers available and you want to change the sort order or character set, use the Transfer Manager built into the Enterprise Manager to rebuild the database and transfer the data.
The sort order affects the speed and performance of SQL Server. Binary sort order is the fastest of the sort orders; the other sort orders are 20 to 35 percent slower than the binary sort order. The default sort order (dictionary order, case insensitive) is about 20 percent slower than the binary sort order.
CAUTION: Selecting the correct sort order is important because changing the sort order--just as changing the character set--requires rebuilding your databases and reloading the data.
TIP: If you have several SQL Servers in your organization, you should use the same character set and sort order for each of the servers, especially if you want to share databases using the DUMP and LOAD commands. You can't load a database that was dumped with a different character set and sort order.
Network Because SQL Server supports many different network options simultaneously, clients running TCP/IP can connect to SQL Server along with clients using IPX/SPX--all at the same time. SQL Server installs different network libraries during installation to handle network communication with other servers and client workstations. SQL Server always installs the named-pipes protocol. You have the option during installation (and after) to install one or more network libraries. Keep in mind that the type of network support you select determines the security mode you can use for SQL Server. Before you examine the network libraries available to you, look at the three different security modes (for detailed information, see Chap-ter 10, "Managing Users"):
The following sections describe the network options available for SQL Server 6.x. Named-Pipes Protocol The named-pipes protocol is the default protocol installed with SQL Server. Named-pipes allows for interprocess communication locally or over networks and is used in NT networks using the NetBUI protocol. Multi-Protocol Multi-protocol is new for SQL Server version 6.x. The multi-protocol uses Windows NT Remote Procedure Call (RPC) mechanisms for communication and requires no setup parameters. Multi-protocol currently supports IPX/SPX and TCP/IP, enabling users of those protocols to take advantage of SQL Server integrated security features.
NOTE: Before SQL Server version 6.0, integrated security was supported only by named-pipes protocol.
NWLink IPX/SPX Protocol IPX/SPX is the familiar network protocol used for Novell networks; it is the default network protocol for Windows NT 3.5x servers. If you select NWLink IPX/SPX during installation, you are prompted for the Novell Bindery service name to register SQL Server. TCP/IP Protocol TCP/IP is a popular communications protocol used in many UNIX networks. If you select TCP/IP, you are asked to provide a TCP/IP port number for SQL Server to use for client connections. The default port number and the official Internet Assigned Number Authority socket number for Microsoft SQL Server is 1433. Banyan Vines Banyan Vines is another popular PC-based network system. Support for Banyan Vines is included only on Intel-based SQL Server systems. If you install Banyan Vines, you are prompted for a valid street talk name that must first be created using the Vines program MSERVICE. AppleTalk ADSP Protocol AppleTalk ADSP allows Apple Macintosh clients to connect to SQL Server using AppleTalk. If you select AppleTalk, you are prompted for the AppleTalk service object name. Decnet Protocol Decnet is a popular network protocol found on many Digital networks running VMS and Pathworks. If you select Decnet, you are prompted for a Decnet object ID.
NOTE: For performance, the named-pipes protocol is the fastest of the network protocols. Running TCP/IP and IPX/SPX is faster than using the multi-protocol but limits your security mode options.
SQL Executive User Account The SQL Executive was first introduced in SQL Server 6.0. The SQL Executive is the service responsible for managing SQL Server tasks such as replication, events, alerts, and task scheduling. During system installation and upgrade, you are required to assign an NT system user account for the SQL Executive. You can use the local system account (in which case, you do not have to create a new NT user account) but you will not be able to perform tasks with other servers like replication or task scheduling. It is recommended that you set up a Windows NT domain user account for the SQL Server Executive. Then you can access files on other servers, such as a Novell NetWare server or Microsoft LAN Manager and perform server-to-server replication and scheduling.
NOTE: SQL Server 6.5 has added a new Windows NT user account, installed during installation/upgrade, called SQLExecutiveCmdExec which de-faults to a member of the NT local users group. The new user account allows nonsystem administrators (nonSA) to run the CmdExec task in the security context of the SQLExecutiveCmdExec user.
The next step is to read the next chapter and begin the installation process. Use the following worksheet to help you prepare for the installation; use the worksheet later as a reference:
The plan and strategy for an upgrade is different from the plan and strategy for a new installation. In an upgrade, you have already decided on a platform and are currently running SQL Server. You may have many large production databases and hundreds of users who depend on the databases, or you may have small development databases with a few users. In many ways, upgrading an existing SQL Server is more critical than installing a new SQL Server. The existing SQL Server contains data being used and depended on by your organization.
Now you know why it is important to develop a plan that enables you to upgrade to the new release; if the upgrade is not successful, your plan should allow you to return the system to its pre-upgrade state.
Mark and Orryn's First Rule of Upgrading
Never under estimate the difficulty of an upgrade. Remember Mark and Orryn's first rule of upgrading: expect something to go wrong; when it does, make sure that you can get the system back and running to its previous state. Creating an upgrade plan is essential. I was once involved with what was to be a simple upgrade for a banking organization that gave me a six-hour window to get their high-powered multi-processor SQL Server upgraded from SQL Server 4.21 to SQL Server 4.21a. No problem, right? After all, the upgrade was not even a major revision number--just a revision letter. Nothing could go wrong...NOT! Five hours later, when the SQL Server was still not working correctly and tech support was trying to resolve the problem, we opted to restore the system to the pre-upgrade state. Once the SQL Server was restored, it did not work either! It appears that the problem had to do with the SQL Server registry entries. This was not a problem because our upgrade plan called for backing up the system registry. Once the registry was restored, the SQL Server was up and running with no problems, and the upgrade was pushed off to another day, awaiting information from tech support. The moral of this story is never underestimate the potential problems that may be encountered during an upgrade, and be overly cautious. It's better to have too many files backed up and ready to restore than not enough.
Once you perform a SQL Server 6.5 upgrade on a 6.0 or 4.2x database, there is no turning back. The upgrade process to version 6.5 makes modifications to the databases such as new system tables and datatypes that are not supported in SQL Server versions 6.0 or 4.2x.
NOTE: Upgrades from SQL Server 1.x are not supported with SQL Server 6.x. OS/2 SQL Server version 4.2x systems can be upgraded, but you must first upgrade the operating system to Windows NT 3.51.
When upgrading an existing SQL Server to SQL Server 6.5, you have two options available:
You upgrade SQL Server to the next release by running the setup program and selecting the Upgrade SQL Server option. The upgrade option installs the new SQL Server software and upgrades the databases with new system tables and datatypes.
Before running a SQL Server upgrade, you must make sure that you have adequate disk space on the drive where SQL Server is located. Upgrading from SQL Server 6.0 to SQL Server 6.5 requires an additional 20M of free disk space and 2M of free space in the master database. Upgrading SQL Server 4.2x to version 6.5 requires the following:
The installation program automatically increases the size of the master database. You must make sure that the SQL Server Open Databases configuration parameter is equal to or greater than the number of databases on your server (including master, pubs, model, and tempdb). If the parameter is less than the total number of databases on your system, use the SQL Enterprise Manager or the system stored procedure sp_configure to increase the value. SQL Server 6.5 added several new keywords and is now fully ANSI-92 compliant. If you are upgrading from SQL Server 6.0 or 4.2x to SQL Server 6.5, run the utility program CHKUPG65 before upgrading to SQL Server 6.5. CHKUPG65 checks to make sure that the database status is fine, that all required comments are in the SQL Server system table syscomments, and that there are no keyword conflicts in your databases. In SQL Server 6.0, the CHKUPG65 utility was called CHKUPG. If you are upgrading from SQL Server 6.0 and have not used any of the published reserved words, you will not have any problems with keyword conflicts.
TIP: Fix them now or fix them later! Although keyword conflicts found in your databases do not prevent the successful upgrade of SQL Server from 4.2x to 6.5, you will have to make the corrections on SQL Server and your applications that reference the keywords before or after the upgrade.
The syntax for the CHKUPG65 utility is as follows:
CHKUPG65 /Usa /Ppassword /Sservername /ofilename
In this syntax, password is the password for the sa user, servername is the SQL Server being upgraded, and filename is the output file to print the CKKUPG65 report. The filename parameter must be fully qualified with drive, path, and filename. The following is a sample output from the CHKUPG65 utility:
===================================================================
Database: master
Status: 8
(No problem)
Missing objects in Syscomments
None
Keyword conflicts
Column name: MSscheduled_backups.DAY [SQL-92 keyword]
=====================================================================
Database: pubs
Status: 0
(No problem)
Missing objects in Syscomments
None
Keyword conflicts
Column name: sales.DATE [SQL-92 keyword]
If you have problems with syscomments entries, drop and re-create the objects. Databases with the read only option set to TRUE must be changed so that the read only option is set to FALSE.
TIP: A full list of the new keywords and future keywords can be found in the SQL Server documentation. The following keywords have given me trouble during several 4.2x upgrades: CURRENT_TIME, CURRENT_USER, KEY, CURRENT_DATE, and USER.
CAUTION: This caution does not apply to users upgrading from SQL Server 6.0 to SQL Server 6.5. If you are moving from SQL Server 4.2x to SQL Server 6.5, be prepared after the upgrade to rewrite and recompile some stored procedures. SQL Server 6.5 is not SQL ANSI-92 compliant. You may get this error message after upgrading when you try to execute a stored procedure: You must drop and re-create the stored procedure <stored procedure name>. What's the problem? Transact SQL treatment of some subqueries and SQL statements such as GROUP BY were not ANSI-92 compliant. When you upgrade and try to execute these stored procedures that break ANSI-92 SQL rules, you get the preceding error message. For example, the following pubs database query works and can be compiled as a stored procedure with SQL Server version 4.2x:
Select au_id, au_lname
From Authors
Group By (au_id)With SQL Server 6.5, however, you get an error. To correct the SQL statement for use with SQL Server 6.5, change the query as follows:
Select au_id, au_lname
from authors
Group By (au_id), (au_lname)The CHKUPG65 utility does not report ANSI-92 SQL violations in stored procedures.
The Upgrade Plan Before you begin to upgrade an existing SQL Server installation, it is important to create an upgrade plan. The following sections provide you with an example of an upgrade plan to upgrade an existing SQL Server installation to SQL Server 6.5:
On the day of the upgrade, follow these steps:
The Fall Back Plan A SQL Server upgrade is a straightforward process, but because you are usually dealing with valuable data and systems that can be down only for a limited amount of time, upgrades should be treated with extreme caution and care. Just as important as a good upgrade plan is a good fall back plan in case the upgrade does not go as smoothly as you hoped. Here are some suggestions on how to protect yourself. Above all, make sure that you have the backups (tapes, and so on) to return your SQL Server to its earlier state if necessary.
CAUTION: Always make sure that you have a valid backup of the Windows NT system registry before starting any upgrade.
Suggestion 1: Complete System Backup Recovery Plan If possible, shut down the SQL Server before the upgrade and perform a backup of the SQL Server directories and all the data devices. You must shut down SQL Server to back up files that SQL Server is using, such as devices. If the upgrade fails for some reason, you can restore the SQL Server directories, devices, and the NT registry, returning your system to its earlier setup. Suggestion 2: Complete Database Backups--Reinstall Previous Version Perform SQL Server database backups on the databases, including the master. Make sure that you have all the valid SQL Server configuration information such as the server name, character set, sort order, network configuration, and device and database layouts. If you cannot get the SQL Server 6.5 upgrade to work correctly, having the database dumps and the required SQL Server information enables you to reinstall your previous SQL Server system and reload your databases if necessary. Suggestion 3: Complete System Backup and Database Backups Perform suggestions 1 and 2. You can never be too careful!
The bottom line is that the information and data completely recover your system if the upgrade fails. Play it safe. Have a backup plan to use if the backup plan fails! The Upgrade Checklist Use the checklist on the next page to help prepare for a SQL Server upgrade. Check off each item on the list as it is completed. Perform each step in order from top to bottom.
The option to install a new server does not qualify as an upgrade to an existing system, but it is mentioned here for two special cases. The first case is that SQL Server 6.5 can be installed alongside SQL Server 4.2x SQL Server on the same machine.
CAUTION: You cannot install SQL Server 6.5 alongside SQL Server 6.0 on the same machine. Because the two products share the same registry entries, installing SQL Server 6.5 on a machine with SQL Server 6.0 results in an upgrade.
SQL Server 6.x uses a different directory structure and registry entries than does SQL Server 4.2x. You can run the two SQL Servers simultaneously and migrate the databases from SQL Server 4.2x to the new SQL Server 6.5 installation. This enables you to test each database with SQL Server 6.5 and migrate all the databases without worrying about unexpected problems because the 4.2x installation is still operating and functional. This option is not for everyone because it requires enough disk space and memory to support both SQL Servers and your existing databases.
The second case is mentioned for the situation in which an existing SQL Server's machine is being upgraded to a new machine. In this scenario, you can install SQL Server on the new machine and migrate the existing databases from the old machine.
If you decide to use either method, you should perform all the normal upgrade steps and follow the installation procedure for a new SQL Server. Use the Enterprise Manager interface (Transfer Manager in SQL Server 6.0 and 4.2x) to transfer the databases and data or the DUMP and LOAD commands.
TIP: When installing version 6.5 alongside a SQL Server 4.2x installation, remember to change the named-pipes name used for the SQL Server 6.x installation; otherwise, the SQL Server 6.x will not run correctly. The named-pipes used by SQL Server 6.x and SQL Server 4.2 are the same: \\.\pipe\sql\query. To change the named-pipes name for SQL Server 6.x, use the setup program and select the Change Network option. Make sure that the Named-Pipe checkbox is selected and click OK. A dialog box displaying the default named-pipe appears. Change the name of the named-pipe and click the Continue button. Although you can also use the registry editor, regedt32, this method is not recommended.
This chapter has helped you prepare for a SQL Server upgrade or installation.
In the next chapter, you walk through the installation and upgrade process.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.