Microsoft Programmer's Library 1.3

home *** CD-ROM | disk | FTP | other *** search

/ Microsoft Programmer's Library 1.3 / Microsoft_Programmers_Library.7z / MPL / net / sqlins.txt < prev next >

Wrap

Text File | 2013-11-08 | 62.8 KB | 1,807 lines

Microsoft SQL Server - Installation Guide ──────────────────────────────────────────────────────────────────────────── Microsoft(R) SQL Server - Installation Guide The SYBASE(R) SQL Server database for PC networks VERSION 1.1 ──────────────────────────────────────────────────────────────────────────── for the MS(R) OS/2 Operating System Microsoft Corporation Information in this document is subject to change without notice and does not represent a commitment on the part of Microsoft Corporation. The software described in this document is furnished under a license agreement or nondisclosure agreement. The software may be used or copied only in accordance with the terms of the agreement. It is against the law to copy the software on any medium except as specifically allowed in the license or nondisclosure agreement. No part of this manual may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying and recording, for any purpose without the express written permission of Microsoft Corporation. (C) 1990 Microsoft Corporation and SYBASE, Inc. All rights reserved. Printed in the USA. Microsoft, MS, MS-DOS, and the Microsoft logo are registered trademarks of Microsoft Corporation. Windows is a trademark of Microsoft Corporation. IBM is a registered trademark of International Business Machines Corporation. SYBASE is a registered trademark of SYBASE, Inc. TRANSACT-SQL and DB-LIBRARY are trademarks of SYBASE, Inc. 3Com is a registered trademark of 3Com Corporation. Document Number: SY10228-0290 OEM-D/0788-1Z Table of Contents ──────────────────────────────────────────────────────────────────────────── Before You Begin Manual Overview How to Use This Guide Check the Package Contents Make Backup Copies Register the Product Notational Conventions Finding Further Information Chapter 1 Installing SQL Server Introduction The Hardware and Software You Need For a Server For a Workstation Using the setup Program Before You Run setup Gather Information Check Your Network (if necessary) Shut Down SQL Server Running setup Under OS/2 Systems Installing SQL Server on a Remote Server Running setup Under MS-DOS Starting SQL Server Network Server Stand-alone Server Testing SQL Server Viewing the Error Log Permitting Workstations to Use SQL Server Running MS-DOS Applications Where to Go Next For System Administrators For Application Developers For Other Users Chapter 2 Configuring SQL Server Introduction What the setup Program Does: OS/2 System Network Server Stand-alone Server What the setup Program Does: MS-DOS System Performance Issues Adding Physical Memory Disabling Write Caching Disabling the MS-DOS Compatibility Box OS/2-System Swapping LAN Manager/LAN Server Options Disabling LAN Manager Disk Caching Changing LAN Manager/LAN Server Parameters SQL Server Configuration Options The memory Option The user connections Option The procedure cache Option SQL Server Configuration Guidelines Running SQL Server on a Dedicated Computer Running SQL Server on a File or Print Server Running SQL Server on a User Workstation Common Installation Problems Problem 1 Problem 2 Problem 3 Appendix A Upgrading from Version 1.0 Introduction Changes to SQL Server, Version 1.0 Changes to the Server Changes to DB-LIBRARY Changes to SAF Changes in Running MS-DOS Application Upgrading from SQL Server, Version 1.0 Updating the Error Message Table Upgrading from the Network Developer's Kit Before You Begin ──────────────────────────────────────────────────────────────────────────── Manual Overview This manual describes how to install the SQL Server software on your OS/2, MS-DOS(R), or Microsoft(R) Windows(tm) system. It also includes information that describes how to upgrade from SQL Server, Version 1.0. How to Use This Guide The following topics are covered in this manual: ──────────────────────────────────────────────────────────────────────────── Chapter 1 How to install SQL Server Chapter 2 How to maximize performance Appendix A How to upgrade from version 1.0 ──────────────────────────────────────────────────────────────────────────── Check the Package Contents Before you attempt to install SQL Server, Version 1.1, make sure you have received the following items: ■ Three 5-1/4 inch high density (1.2 megabyte) disks: ■ Setup for OS/2 systems ■ Disk 2 for OS/2 systems ■ Disk 3 for OS/2 systems ■ Four 5-1/4 inch low density (360 kilobyte) disks: ■ Setup for MS-DOS systems ■ Disk 2 for MS-DOS systems ■ Disk 3 for MS-DOS systems ■ Disk 4 for MS-DOS systems ■ Three 3-1/2 inch high density (1.44 megabyte) disks: ■ Setup for OS/2 systems ■ Disk 2 for OS/2 systems ■ Disk 3 for OS/2 systems ■ Two 3-1/2 inch low density (720 kilobyte) disks: ■ Setup for MS-DOS systems ■ Disk 2 for MS-DOS systems ■ The SQL Server manual set, in addition to this manual: ■ SQL Server System Administrator's Guide ■ SQL Server Learning TRANSACT-SQL ■ SQL Server Language Reference ■ SQL Server Programmer's Reference ■ SQL Server Quick Reference ■ SQL Server Product Registration Card ■ Support Services Agreement and Registration Card ■ Value Pack ■ Third Party Product Coupons If you are missing any of these items, contact Microsoft Customer Service at (206) 882-8088. Make Backup Copies Before you attempt to install SQL Server, you should make backup copies of the product's master disks. Use the MS-DOS or OS/2-system diskcopy command to copy each original disk to a backup. Consult the appropriate documentation if you do not know how to use this command. After you have made backup copies, store the SQL Server master disks in a safe place. Register the Product Be sure to mail in the Product Registration Card, to register your purchase of SQL Server, Version 1.1. Sending this card also registers you for product support and allows us to keep you informed of future releases of SQL Server. Notational Conventions Throughout this manual, the following conventions are used to distinguish elements of text: ╓┌─────────────────────────────────┌───────────────────────────────────────── Convention Purpose ──────────────────────────────────────────────────────────────────────────── UPPERCASE Represents statement and clause names, functions, macros, and any other portions of syntax that must appear exactly as shown. SMALL CAPS Represent keynames such as CTRL. bold Represents stored procedures, system Convention Purpose ──────────────────────────────────────────────────────────────────────────── bold Represents stored procedures, system procedures, triggers, defaults, rules, utility programs, and commands. italic Represents database names, table names, view names, column names, datatypes, index names, pathnames, filenames, and variables that appear in text. monospace Represents examples, screen output, program code, and error messages. [brackets] Enclose optional items. Type only the information within the brackets, not the brackets themselves. {braces} Enclose required items. Type only the information within the braces, not the braces themselves. Convention Purpose ──────────────────────────────────────────────────────────────────────────── braces themselves. | (vertical bar) Separates items inside a set of braces or brackets. The vertical bar means you must choose one and only one item. ... (ellipsis) Means that you can repeat the previous item as many times as you like. <execute> Executes one or more SQL statements. (In the SQL Server Administration Facility, SQL statements are executed by pressing the CTRL+E keys. In the isql program, SQL statements are executed with the go command.) ──────────────────────────────────────────────────────────────────────────── Finding Further Information The following manuals describe SQL Server and are included in the standard documentation set: ──────────────────────────────────────────────────────────────────────────── SQL Server Learning TRANSACT-SQL A guide to learning and using TRANSACT-SQL(tm) SQL Server System Administrator's Guide A guide to managing SQL Server and to using the SQL Server Administration Facility (SAF) for database queries SQL Server Language Reference A reference to the syntax of all TRANSACT-SQL statements, commands, procedures, and utilities SQL Server Programmer's Reference A reference to DB-LIBRARY(tm), which is a set of C routines and macros that allow your application to interact with SQL Server SQL Server Quick Reference A quick reference guide to TRANSACT-SQL ──────────────────────────────────────────────────────────────────────────── Chapter 1 Installing SQL Server ──────────────────────────────────────────────────────────────────────────── Introduction This chapter describes: ■ The hardware and software you need to install SQL Server on your computer ■ How to install SQL Server with the setup program ■ How to start SQL Server The Hardware and Software You Need To install SQL Server on a server or workstation, you need the SQL Server distribution diskettes and the hardware and software described on the next page. For a Server SQL Server requires an OS/2 system, Version 1.1 or later, to run. To install SQL Server on a server, you need at least 20 megabytes of free space on your hard disk and the requirements in the table on the next page: ╓┌─────────────────────────────────┌───────────────────────────────────────── You Need Your Choices Are ──────────────────────────────────────────────────────────────────────────── Computer A personal computer with an 80286 or 80386 microprocessor running OS/2 system, Version 1.1 or later. An 80386 is recommended. Minimum memory 8 megabytes. Disk drives A minimum 40-megabyte hard disk drive and 5-1/4 inch or 3-1/2 inch disk drive. Operating system(1) OS/2 system, Version 1.1 or later. Network software(1) Microsoft LAN Manager-based network; IBM(R) OS/2 LAN Server. Network adapter The appropriate network adapter card.(1) ──────────────────────────────────────────────────────────────────────────── You Need Your Choices Are ──────────────────────────────────────────────────────────────────────────── (1) See your network software manuals for more information. For a Workstation SQL Server workstations can run under either MS-DOS or OS/2 systems. To install SQL Server utility programs and programming tools on a workstation, you need at least 1 megabyte of free space on your hard disk and the following: ╓┌─────────────────┌──────────────────────────────┌────────────────────────── You Need MS-DOS Workstation OS/2-System Workstation ──────────────────────────────────────────────────────────────────────────── Computer An IBM personal computer, or A personal computer with an compatible, with a hard disk. 80286 or 80386 You Need MS-DOS Workstation OS/2-System Workstation ──────────────────────────────────────────────────────────────────────────── compatible, with a hard disk. 80286 or 80386 microprocessor. Minimum memory 640 kilobytes (K). 3 megabytes. Operating system MS-DOS, Version 3.2 or later. OS/2 system, Version 1.1 or later. Network software IBM DOS LAN Requestor. IBM LAN Server. MS-DOS LAN Manager-based Microsoft LAN Manager-based networks. networks. ──────────────────────────────────────────────────────────────────────────── Using the setup Program Use the setup program to install SQL Server software on your server or workstation. Depending on your choices, the setup program copies all or part of the SQL Server software. The three most common installations are as follows: ╓┌───────────────────────────────────────┌─────────────────────────────────── Type of Installation Software Installed ──────────────────────────────────────────────────────────────────────────── An OS/2-system server. Server software and utility programs. An MS-DOS workstation or an Utility programs. OS/2-system workstation used mainly for accessing data in SQL Server databases or for the remote administration of SQL Server databases. An MS-DOS workstation or an Utility programs and programming OS/2-system workstation used mainly tools. for developing SQL Server Type of Installation Software Installed ──────────────────────────────────────────────────────────────────────────── for developing SQL Server applications. ──────────────────────────────────────────────────────────────────────────── The setup program makes it easy to install only those SQL Server components you need. Before You Run setup Before you run setup, you must: 1. Gather the configuration information you'll need. 2. Make sure your network is set up and running if you are installing over the network. The network need not be running if you are installing SQL Server on the local machine. 3. Shut down SQL Server if it is already running. Gather Information The SQL Server setup program prompts you for the following information when you install the SQL Server software. You can fill in the appropriate details under "Your Computer" and refer to this information when running setup. ╓┌───────────────────────────────────────┌───────────────────────────────────╖ Type of Information Your Computer ──────────────────────────────────────────────────────────────────────────── Amount of memory in computer(1) ______ Kilobytes Amount of space on the server's hard ______ Megabytes disk to reserve for the master database device(2) Type of Information Your Computer ──────────────────────────────────────────────────────────────────────────── database device(2) Case sensitivity of character data and ______ Case sensitive object names(3) ______ Non-case-sensitive ──────────────────────────────────────────────────────────────────────────── (1) To find the amount of memory in the computer, turn the power switch off and then on. The amount of memory available is displayed at the upper-left corner of the screen. The last number that appears is the number of kilobytes of memory available in your computer. (2) This amount must be at least 10 megabytes. See the SQL Server System Administrator's Guide for a description of the master database device. (3) With case-sensitive data, character data retrieved from a database must contain the same uppercase and lowercase letters it contained when it was entered into the database. If you entered "Bill Smith," you must retrieve "Bill Smith" and not "BILL SMITH." Object names must also contain the same uppercase and lowercase letters used when the object was created. With non-case-sensitive data, if you entered "Bill Smith," you could retrieve "BILL SMITH," "bill smith," or "bill Smith." Check the application manual to find out whether your application requires case-sensitive data. Check Your Network (if necessary) If you intend to install as a network server, your server must have both network server software and network workstation software installed. Before installing SQL Server, make sure your server and workstation are set up correctly: 1. Start your network server and workstation, if they have not been started. 2. Make sure you are logged on as a valid network user with administrative privileges on your server. 3. Make sure that your server is sharing the IPC$ resource. (If your server has user-level security, the IPC$ resource is shared automatically, but if your server has share-level security, use the appropriate network command to share IPC$.) Refer to your network documentation for details. Shut Down SQL Server If SQL Server has been installed previously and is currently running, shut it down from a SQL Server session, (such as the isql or SAF utility program) by typing shutdown You may shutdown SQL Server running on a network by typing net stop sqlserver; however, no checkpoints are performed in the databases with this method until SQL Server is started again. Running setup Under OS/2 Systems This section describes how to run the OS/2-system version of setup. With this version of setup, you can ■ Install the server software on a local or remote computer ■ Install the workstation software on a local or remote computer ■ Remove all server and workstation software from a local or remote computer The setup program is a menu-driven utility that gives you step-by-step instructions. To run setup: 1. Insert the SQL Server setup disk in drive A. 2. At the C> prompt, type a:setup [/remote] Specify the /remote switch if you want to install or delete software on a remote computer system. 3. Press ENTER. The setup program displays a menu. Select the option you desire. Whichever option you choose, the setup program prompts you for any information it requires to carry out its work. At the completion of each option, setup returns you to the menu and prompts you for another choice. You can start SQL Server automatically every time you start your computer by selecting the default menu item when prompted to allow setup to modify the startup.cmd file for you. If you have problems and need additional information, see Chapter 2, "Configuring SQL Server." Installing SQL Server on a Remote Server Generally, the SQL Server setup program is run on the same computer that will run SQL Server. However, some network servers do not have a floppy drive, keyboard, or monitor (specifically, 3Com(R) servers such as the 3S/400). The SQL Server setup program has been designed with a remote option that simplifies installation on these servers. To install SQL Server on a remote server, the remote server must have ADMIN$ shared and you need an OS/2-system workstation. The workstation must be logged on with a logon ID/password which has administrative privileges on the remote server. You must then make a connection from the workstation to ADMIN$ on the remote server. Once you've made this connection, you can install SQL Server: 1. Insert the SQL Server setup disk for OS/2 systems in drive A of the workstation. 2. Type a:setup /remote 3. Select option 1. The setup program asks for the remote server name. 4. Type the server name, preceded by two backslashes. For example: \\mysqlserver The rest of the setup procedure is unchanged from the standard SQL Server setup. Running setup Under MS-DOS This section describes how to run the MS-DOS version of setup. With this version of setup, you can ■ Install the workstation software on the local computer ■ Remove all server and workstation software from the local computer The setup program is a menu-driven utility that gives you step-by-step instructions. To run setup: 1. Insert the SQL Server setup disk in drive A. 2. At the C> prompt, type a:setup 3. Press ENTER. The setup program displays a menu. Select the option you desire. Whichever option you choose, the setup program prompts you for any information it requires to carry out its work. At the completion of each option, setup returns you to the menu and prompts you to make another choice. If you have problems and need additional information, see Chapter 2, "Configuring SQL Server." Starting SQL Server You should reboot your system after installing SQL Server, because changes may have been made to your config.sys file and your network. Network Server After running the setup program, start SQL Server from the computer on which you installed the server software: 1. If your network server is not running, type net start server 2. To start SQL Server, type net start sqlserver If you have problems starting SQL Server, see the "Common Installation Problems" section in Chapter 2, "Configuring SQL Server." Stand-alone Server If you have installed SQL Server on a stand-alone workstation (that is, with no network installed), use the following command to start the server: cd \sql\data sqlservr -d master.dat You then need to change screen groups by pressing ALT+ESC. If you have problems starting SQL Server, see the "Common Installation Problems" section in Chapter 2, "Configuring SQL Server." Testing SQL Server To make sure SQL Server is working correctly, start the isql program and execute a sample query: 1. Start isql by typing the following commands: isql /Usa /P /Sservername If you installed SQL Server stand-alone, type isql /Usa /P 2. When the number prompt (1>) appears, type the following statements: 1>select * from sysdatabases 2>go 3. Press ENTER. You will see a report similar to the following: name dbid suid mode status version crdate -------- ---- ---- ---- ------ ------- ------------------- master 1 1 0 0 1 Jan 1 1990 12:00AM model 3 1 0 0 1 Jan 1 1990 12:00AM pubs 4 1 0 0 1 Feb 22 1990 4:50PM tempdb 2 1 0 4 1 Jan 1 1990 12:00AM (4 rows affected) 4. To leave isql, type 1>exit 5. Press ENTER. Viewing the Error Log SQL Server writes all messages to the sql\log\errorlog file. Each message, as it occurs, is appended to this file. You can review this file at any time to check error messages. Permitting Workstations to Use SQL Server Use your network software to permit workstations to use SQL Server: 1. If you are running user-level security, give each user a username and password on the network server. 2. Assign each username to a valid group that has access to \pipe. See your network manual for details. ──────────────────────────────────────────────────────────────────────────── NOTE The only SQL Server account available is "sa," until users are given login IDs to SQL Server. ──────────────────────────────────────────────────────────────────────────── Running MS-DOS Applications If your workstation is running MS-DOS, the terminate and stay resident (TSR) program, DBNMPIPE, must be loaded for your application to communicate with the server. If you did not choose the option to include this in your autoexec.bat file, you may load the TSR by simply typing dbnmpipe at the prompt. You may want to unload the TSR to free up more memory for other applications. To do this, type enddblib.exe at the prompt. Where to Go Next For System Administrators ╓┌───────────────────────────────────────┌─────────────────────────────────── To Find This Consult This Manual To Find This Consult This Manual ──────────────────────────────────────────────────────────────────────────── Instructions on how to manage SQL SQL Server System Administrator's Server databases Guide Instructions on how to use the System SQL Server System Administrator's Administrator's Facility (SAF) to Guide access databases Tutorials in the TRANSACT-SQL SQL Server Learning TRANSACT-SQL programming language A complete description of TRANSACT-SQL SQL Server Language Reference syntax For Application Developers ╓┌──────────────────────────────────────────┌──────────────────────────────── To Find This Consult This Manual ──────────────────────────────────────────────────────────────────────────── A complete description of the functions SQL Server Programmer's Reference available in DB-LIBRARY, the programmatic interface to SQL Server Tutorials in the TRANSACT-SQL programming SQL Server Learning TRANSACT-SQL language A complete description of TRANSACT-SQL SQL Server Language Reference syntax For Other Users Read the first three chapters of the SQL Server System Administrator's Guide to learn how to use the SAF. Then, study SQL Server Learning TRANSACT-SQL for a tutorial about TRANSACT-SQL. Chapter 2 Configuring SQL Server ──────────────────────────────────────────────────────────────────────────── Introduction This chapter discusses the setup program and common problems encountered when installing SQL Server. What the setup Program Does: OS/2 System When installing SQL Server on an OS/2 system, you can set it up as either a network server or a stand-alone server. Network Server When installing SQL Server on a network server, the setup program does the following: 1. Checks that named-pipe interprocess communication (IPC) exists on the server. 2. Loads executable and data files from the setup disks. 3. Updates the following files: ╓┌──────────────────────────┌────────────────────────────────────────────────╖ To This File setup Adds ──────────────────────────────────────────────────────────────────────────── config.sys c:\sql\dll to libpath, and copies dynamic link libraries (DLLs) to that directory and also adds c:\sql\binp to path lanman.ini or sqlserver in the services section and an ibmlan.ini sqlserver section containing full pathnames for To This File setup Adds ──────────────────────────────────────────────────────────────────────────── ibmlan.ini sqlserver section containing full pathnames for master.dat, errorlog, and sqlservr.exe ──────────────────────────────────────────────────────────────────────────── 4. Creates the master database to your specifications. 5. Starts SQL Server. 6. Runs four scripts: ╓┌─────────────────────────────────┌───────────────────────────────────────── This Script Does This ──────────────────────────────────────────────────────────────────────────── instmstr.sql Loads system procedures This Script Does This ──────────────────────────────────────────────────────────────────────────── instmodl.sql Loads system procedures instpubs.sql Loads the pubs database if specified config.sql Adjusts configuration parameters based on your replies to memory size and master.dat location ──────────────────────────────────────────────────────────────────────────── After installation, the computer must be restarted to make the change to libpath effective. Stand-alone Server When installing SQL Server as a stand-alone server, the setup program does the following: 1. Checks that named-pipe interprocess communication (IPC) exists on the server. 2. Loads executable and data files from the setup disks. 3. Adds c:\sql\binp to path in the config.sys file. 4. Creates the master database to your specifications. 5. Starts SQL Server. 6. Runs four scripts: ╓┌─────────────────────────────────┌───────────────────────────────────────── This Script Does This ──────────────────────────────────────────────────────────────────────────── instmstr.sql Loads system procedures This Script Does This ──────────────────────────────────────────────────────────────────────────── instmstr.sql Loads system procedures instmodl.sql Loads system procedures instpubs.sql Loads the pubs database if specified config.sql Adjusts configuration parameters based on your replies to memory size and master.dat location ──────────────────────────────────────────────────────────────────────────── After installation, the computer must be restarted to make the change to libpath effective. What the setup Program Does: MS-DOS System When installing SQL Server on an MS-DOS workstation, the setup program does the following: 1. Loads executable and data files from the setup disks. 2. Adds c:\sql\bin to path in the config.sys file. 3. Updates the autoexec.bat file by adding dbnmpipe. Performance Issues The following recommendations concern your system hardware or operating system. Adding Physical Memory SQL Server requires a computer with at least 8 megabytes of system memory installed. In this minimum configuration, the computer system should be dedicated to running SQL Server. Adding additional memory above 8 megabytes can significantly improve system throughput. SQL Server caches frequently accessed database information in physical memory. In systems with a large amount of memory (9-16 megabytes), SQL Server may be able to locate previously accessed information without reading from the hard disk. This can significantly increase performance. Disabling Write Caching OS/2 systems and LAN Manager 1.X networks support a sophisticated read/write disk cache to improve file server performance. The read cache buffers data in memory, which allows read requests to be satisfied without accessing the disk. Similarly, the write cache delays writes to the disk until they are required. Disk writes buffered through a write cache are called lazy writes. SQL Server will run if a read cache is installed, but you can get better performance if you give the memory to SQL Server's own buffer instead of to the OS/2 or LAN cache. ──────────────────────────────────────────────────────────────────────────── WARNING Data written using lazy writes is at risk until it is physically written to disk. If the system were to crash before buffered data is written, it would be lost. This would cause database corruption. ──────────────────────────────────────────────────────────────────────────── When you issue the command net start sqlserver, SQL Server automatically disables lazy writes if they are enabled on the server. However, if you start the server directly with the sqlservr program, SQL Server does not disable lazy writes. You can determine the state of write caching by issuing the following command at the operating system prompt: cache /options If the cache program displays the following message: Drive C is enabled with lazy write selected then disable the write cache with the following command: cache /lazy:c:off You should also remove the following command from the startup.cmd file, if it is present: cache /lazy:c:on This ensures that the write cache is disabled each time you reboot your machine. Other versions of LAN Manager may handle the write cache operations differently, as may other network software. Consult your network software manual(s) for more information if necessary. ──────────────────────────────────────────────────────────────────────────── NOTE You must disable write caching on any File Allocation Table (FAT) drive where you intend to place SQL Server data. ──────────────────────────────────────────────────────────────────────────── Disabling the MS-DOS Compatibility Box OS/2 systems provide a special screen session known as the compatibility box, where you can run MS-DOS programs. For performance reasons, you should disable this feature when running SQL Server. Running the compatibility box on the same machine with SQL Server will severely impair system performance. To disable the compatibility box, edit the operating system configuration file (config.sys or config.os2). Set the protectonly option by typing protectonly = yes You should also disable any real-mode device drivers such as dos.sys. For this change to take effect, you must reboot your machine. OS/2-System Swapping OS/2 systems retain only the most important data and programs in physical memory. Only when OS/2 systems need more physical memory are segments that have not been used recently discarded or transferred to the hard disk. This conserves physical memory. When a segment is needed, it is transferred back into memory from the disk. This action can cause another segment to be swapped or discarded in order to make room. OS/2 systems perform swapping without user intervention. If configured correctly, the OS/2-system swapping mechanism can improve system performance by removing infrequently used portions of data to disk. This allows dedicated programs like SQL Server to use as much memory as possible. Only modified data segments are swapped. Others are discarded and reread from original sources (.EXEs and .DLLs). OS/2-system swap files can become very large (greater than 4 megabytes) when running SQL Server. In particular, when the computer is dedicated to SQL Server, much of the OS/2 system and LAN Manager will be swapped to disk. This is normal and will improve system performance. You can control the amount of memory dedicated to SQL Server through the SQL Server memory configuration option, discussed later in this chapter. If you are running MS OS/2 1.1, you can also free up memory by disabling the Presentation Manager spooler. LAN Manager/LAN Server Options The following recommendations apply to your network software. Disabling LAN Manager Disk Caching LAN Manager 1.X networks include a sophisticated disk cache that improves performance when LAN Manager is used as a file server. This cache minimizes physical reads (and, optionally, writes) during file sharing. SQL Server does its own caching. Because SQL Server's cache is optimized for database operations, running the LAN Manager cache concurrently does not improve performance. However, disabling the LAN Manager cache and releasing the cache memory to SQL Server can improve overall system performance. To disable the LAN Manager cache, edit the OS/2-system configuration file (either config.sys or config.os2) by converting the diskcache command into a remark. Type the following: rem diskcache=x where x is the amount of memory dedicated to the disk cache. For this change to take effect, you must reboot your machine. If you are using the hpfs386.ifs installable file system driver, it is advised that you set the cache to 64K. Modify the line in the config.sys to look like this: IFS=c:\Lanman\netprog\HPFS386.IFS /I:c:\lanman -c:64 If you are running SQL Server on a network file server, you can retain a small disk cache without significantly affecting SQL Server performance. On an 8 megabyte system, allow no more than a 512K cache. On a 10 megabyte system, allow no more than 1024K for the cache. Changing LAN Manager/LAN Server Parameters The setting of LAN Manager and LAN Server parameters can also affect SQL Server performance. The SQL Server setup program adjusts LAN Manager parameters based on its estimate of the number of user connections. (See "The user connections Option" section, later in this chapter.) If you change the number of user connections, you may need to adjust the settings of the LAN Manager/LAN Server parameters. These parameters are specified in the following files: ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ File System ──────────────────────────────────────────────────────────────────────────── \lanman\lanman.ini LAN Manager \ibmlan\ibmlan.ini LAN Server You can change the LAN Manager/LAN Server parameters with a text editor. For the changes to take effect, you must stop and restart the network. See your network manual for details. The maxopens Parameter This parameter determines the total number of network connections available. Each SQL Server user connection counts as one network connection. In addition, other network activity also consumes connections. You should always set maxopens to a number greater than the SQL Server user connections option. For a dedicated SQL Server machine, you should set maxopens to the number of SQL Server user connections plus 25. For a file and print server, maxopens should be increased. Many SQL applications use several connections for each user. Check with your application's vendor for more details. The numbigbuf,numreqbuf, andsizereqbuf Parameters These parameters specify the number and size of the network server buffers. They generally do not need to be changed. Although SQL Server is a network application, it does not impose the network overhead commonly encountered with file and print sharing. The LAN Manager/ LAN Server setup program sets the values of the parameters based on the available memory. For maximum throughput on dedicated SQL Servers, it is usually best to keep the number of network buffers small and give the memory to SQL Server. If you add more users to SQL Server, set the numreqbuf value to the number of users times 2. The maxpipes and maxthreads Parameters These parameters specify the number of named-pipe IPC connections that can be established on a workstation. If you run the SQL Server setup program, it will increase these parameters from their default value (10) up to 20. A value of 20 should be sufficient for any normal SQL Server application. Check with your application's vendor for more details. SQL Server Configuration Options The following SQL Server configuration options have an effect on overall system performance. Use the SAF menus or the sp_configure stored procedure to change these options. See the SQL Server System Administrator's Guide for instructions on using SAF and stored procedures. The memory Option The memory configuration option is the most important option because it determines the amount of system memory available for use by SQL Server. The number specified includes all memory requirements of SQL Server. Setting the memory option correctly is the most important single factor in improving SQL Server performance. ──────────────────────────────────────────────────────────────────────────── NOTE When using SAF, specify the memory option in kilobytes; when using sp_configure, specify it in 2K blocks. ──────────────────────────────────────────────────────────────────────────── Set the memory option to the largest possible value, while still leaving memory for the operating system and network code. If you set this parameter too high, the system will spend an inordinate amount of time swapping frequently used code and data segments in and out. This condition is known as thrashing. For best performance, set the memory option to no less than 6000K. In cases where there is very little physical memory and the system is also used as a file or print server, you can set memory to as little as 4000K, but SQL Server performance will suffer in this environment. On a computer dedicated to SQL Server, the optimal setting for memory is roughly 75-80 percent of available memory, with a minimum of 6000K. So, if your computer has 8 megabytes available, set memory to 6000K. For a 10 megabyte machine, set memory to 7500K, and so on. It's possible to set memory so high that you will be unable to start SQL Server. If this happens, see the bldmaster utility in the SQL Server Language Reference for information about how to restore initial configurations without altering the database(s) themselves. If your computer is not dedicated to SQL Server, reduce the value for memory. Finding the best value for memory in this case is tricky, because different services require different amounts of memory. A reasonable guideline for nondedicated computers is to have at least 8 megabytes of memory available. Then, set the memory option to 50 or 60 percent of available memory. The user connections Option After memory, the next most important configuration option is the number of user connections. You must specify enough connections to allow your user community to access the server. If you run out of connections, some users will be denied access. On the other hand, each connection consumes 42K of SQL Server memory space. Thus, you should set the user connections option just high enough to allow the maximum number of concurrent users you want to support. While most application programs require only one user connection for each actual user, some applications use more than that. Consult your application manual to determine the number of user connections required for each actual user. If you are developing applications with SQL Server, remember that each open DBPROCESS requires one user connection. Consult the SQL Server System Administrator's Guide for more information. The procedure cache Option This configuration option specifies the percentage of SQL Server memory dedicated to the procedure cache, after all other memory needs are satisfied. The procedure cache is used for both SQL compilation and the storage of any active stored procedures. The default procedure cache setting is 20 percent. This is a reasonable number for most applications. If you are developing applications for SQL Server, increasing this number can improve system throughput. If you are running applications that use a limited number of stored procedures, reducing the procedure cache value may improve performance. SQL Server Configuration Guidelines The SQL Server setup program configures your computer, using reasonable values based on the amount of available memory. You should not need to change the configuration values established by setup until you are familiar with SQL Server and are actively using it. Because of the many different ways in which you can use SQL Server on your network, there is no single configuration that will provide optimal performance in all cases. Use the guidelines below as a starting point in your efforts to tune SQL Server for your application. Running SQL Server on a Dedicated Computer In this configuration, the machine is dedicated to running SQL Server; it is not used for file or print sharing, or as a user workstation. ╓┌────────────────────────────┌────────────────────────────┌──────┌──────┌─── System Memory (in megabytes) 8 10 12 16 ──────────────────────────────────────────────────────────────────────────── SQL Server memory 6000K 7500K 9000K 12500 SQL Server user connections Configure as needed System Memory (in megabytes) 8 10 12 16 SQL Server procedure cache 20% 20% 20% 20% LAN Manager disk cache 0 0 0 0 LAN Manager maxopen user connections + 25 LAN Manager numreqbuf user connections * 2 LAN Manager numbigbuf 0 0 0 0 LAN Manager sizereqbuf 1024 1024 1024 1024 ──────────────────────────────────────────────────────────────────────────── Running SQL Server on a File or Print Server You can run SQL Server on a machine that is also acting as a LAN Manager or LAN Server file or print server. This configuration can provide good performance for installations with moderate server demands. In this configuration, machine resources (primarily memory) are divided between the network file or print services and SQL Server. ╓┌────────────────────────────┌────────────────────────────┌──────┌──────┌─── System Memory (in megabytes) 8 10 12 16 ──────────────────────────────────────────────────────────────────────────── SQL Server memory 4000K 5000K 6000K 8000K SQL Server user connections Configure as needed SQL Server procedure cache 20% 20% 20% 20% LAN Manager disk cache 512K 1024K 2048K 4096K System Memory (in megabytes) 8 10 12 16 LAN Manager maxopen user connections + 64 LAN Manager numreqbuf user connections * 2 ──────────────────────────────────────────────────────────────────────────── Running SQL Server on a User Workstation In this configuration, the machine is running both SQL Server and the client application. This is the least optimal configuration. ╓┌────────────────────────────┌────────────────────────────┌──────┌──────┌─── System Memory (in megabytes) System Memory (in megabytes) 8 10 12 16 ──────────────────────────────────────────────────────────────────────────── SQL Server memory 4000K 5000K 6000K 8000K SQL Server user connections Configure as needed SQL Server procedure cache 20% 20% 20% 20% LAN Manager disk cache 0 0 0 0 LAN Manager maxopen user connections + 25 LAN Manager numreqbuf user connections * 2 ──────────────────────────────────────────────────────────────────────────── Common Installation Problems This section describes two of the most common installation problems. Problem 1 The computer is not properly configured. Message This computer is not properly configured... Description When this error message appears, it is often followed by specific instructions on how to fix the error. In other cases, the message is followed by an error code and another message to call customer support. This error message means that the setup program has failed to create and use a named pipe or that you do not have user permission. Solution Either fix the error by following the instructions or record the error code and call customer support. Make sure that IPC$ is shared and that you are logged to the network as a user who has privileges to access named pipes. If this still doesn't work, refer to your network documentation for testing pipe access. Problem 2 You have insufficient disk space for the OS/2-system swap file or insufficient memory. This problem can result in an incomplete installation. Message Check the error log file for the following message: Unable to create timer thread, insufficient core Description While installing SQL Server, if you see the message "Now Starting SQL Server" but not "SQL Server Shutdown," it can mean that you do not have enough disk space for the OS/2-system swap file or enough memory. The amount of disk space required for the swap file depends on the amount of memory in the computer and how much of it is being used by other processes. The OS/2-system swap file is called swapper.dat and can be found by looking at the swappath parameter in the config.sys file. It usually starts out at approximately 0.6 megabytes and grows in response to the memory requirements of the processes that are running. Solution To increase available swap space, change the swappath to point to a disk with more space or remove files from the swap drive. Check the size of the sqlserver.out file by typing dir c:\sql\log\sqlserver.out If the file has some data in it (a size other than 0), take a look at the messages in the file. Based on these messages, try to fix the problems or call customer support. If the sqlserver.out file has 0 size, start SQL Server from the sqlservr.exe file by typing cd \sql\data sqlservr -d master.dat Note any error messages. If the message "Recovery complete" appears, SQL Server has started successfully. Press ALT+ESC to switch to another screen group and test SQL Server (as described in Chapter 1 in the "Testing SQL Server" section). If the computer has at least 8 megabytes of memory, if the network software buffers were not increased significantly, and if nothing else is running, then the swap file should not exceed 8 megabytes during installation. Problem 3 The setup program can't find the server while installing on a remote workstation. Message The remote server could not be found or is not configured for remote administration. Verify that the remote server exists, and that this workstation has administrative privileges on the server. Description This error message appears if you are installing SQL Server on a remote workstation and setup can't find the server. It will also occur if you don't have administrative privileges. Solution Make sure the remote server has ADMIN$ shared. Verify that you are logged on as a valid user with administrative privileges on the remote server. Make sure the workstation has a connection to ADMIN$. Appendix A Upgrading from Version 1.0 ──────────────────────────────────────────────────────────────────────────── Introduction This appendix discusses how to upgrade from SQL Server, Version 1.0. Specifically, it describes ■ Changes made to the product since SQL Server, Version 1.0 ■ How to upgrade from SQL Server, Version 1.0 ■ How to upgrade from the SQL Server Network Developer's Kit (NDK) Changes to SQL Server, Version 1.0 This section discusses changes made to SQL Server, Version 1.0, that have gone into this release. It describes ■ Changes to the Server ■ Changes to DB-LIBRARY ■ Changes to the System Administration Facility (SAF) Changes to the Server Known bugs in the server have been fixed. Changes to DB-LIBRARY DB-LIBRARY has been completely rewritten for this release. The new library is both smaller and faster than previous versions. In addition, the following changes were made: ■ Support for the Minimal Subset has been dropped. Memory requirements for various combinations of library functions are now documented in the SQL Server Programmer's Reference. ■ Support for the dbsetbusy, dbsetidle, and dbsetinterrupt functions has been dropped. ■ The dbinit and dbwinexit functions have been added. Calling dbinit is optional in all environments except Windows. Under Windows, your application must call dbinit before calling any other DB-LIBRARY function, and it must call dbwinexit after the last call to any DB-LIBRARY routines and before exiting. ■ All macros have been rewritten. Macros are those DB-LIBRARY routines that are named in uppercase letters in the SQL Server Programmer's Reference. In this release of DB-LIBRARY, all macros resolve directly into calls to functions of the same name, written with lowercase letters. Source compatibility is maintained. ■ The definition for the DBPROCESS data structure is no longer visible to application programmers. It is no longer possible for application programs to directly access data within the DBPROCESS structure. DB-LIBRARY provides tools to accomplish these needs while retaining compatibility with future releases. ■ All application programs must now include the header files sqlfront.h and sqldb.h; sqldbtok.h, sqllogin.h, and sqlerror.h are no longer supported. ■ All application programs must define the environment under which they run before including sqlfront.h. The following table defines the appropriate constants: ╓┌─────────────────────────────────┌─────────────────────────────────────────╖ Constant Environment ──────────────────────────────────────────────────────────────────────────── DBMSDOS MS-DOS DBMSOS2 OS/2 systems DBMSWIN Windows ──────────────────────────────────────────────────────────────────────────── Constant Environment ──────────────────────────────────────────────────────────────────────────── ──────────────────────────────────────────────────────────────────────────── ■ dbnextrow () must be called repeatedly until NO_MORE_ROWS is returned. Version 1.0 of dbnextrow allowed this function to be called only once if a query returned only 1 row. This was not documented behavior, and was not presented in examples. However, if your application depended on this behavior, a slight modification of your application will be necessary. ■ The names of the SQL Server libraries have been changed, as documented in the following table. This will avoid name collisions in environments that must use a combination of both new and old versions for a period of time. ╓┌────────────┌────────────┌───────────────────────────────────────────────── Old Name New Name Environment ──────────────────────────────────────────────────────────────────────────── Old Name New Name Environment ──────────────────────────────────────────────────────────────────────────── ldblibr.lib rldblib.lib MS-DOS, large model mdblibr.lib rmdblib.lib MS-DOS, medium model dblibp.dll pdblib.dll OS/2 systems, dynamic link library dblibp.lib pdblib.lib OS/2 systems, import definitions dblibw.exe wdblib.exe Windows (Version 2.1), dynamic link library dblibw.lib wdblib.lib Windows (Version 2.1), import definitions none dbnmpp.dll Named pipe dynamic link library ──────────────────────────────────────────────────────────────────────────── ■ Applications no longer need to link with dosnet.lib, unless they access the network directly. NOTE Old applications using the previous version of DB-LIBRARY will work with the new server. However, we strongly recommend that you recompile and relink to get the benefit of the new libraries. For more information see Appendix E, "Building Applications," in the SQL Server Programmer's Reference. Changes to SAF The SAF has been enhanced for this release. In several instances, the interface was changed to provide more useful information on screen. Changes in Running MS-DOS Application If your workstation is running MS-DOS, the TSR program, DBNMPIPE, must be loaded for your application to communicate with the server. If you did not choose the option to include this in your autoexec.bat file, you may load the TSR by simply typing dbnmpipe at the prompt. You may want to unload the TSR to free up more memory for other applications. To do this, type enddblib.exe at the prompt. Upgrading from SQL Server, Version 1.0 If you are upgrading from SQL Server, Version 1.0, you simply need to install the new software over the old version. ──────────────────────────────────────────────────────────────────────────── NOTE All existing databases are retained but as a precaution, back up your databases before you update SQL Server. ──────────────────────────────────────────────────────────────────────────── To upgrade: 1. Run setup from an OS/2-system server. 2. Select option 1, Install Server and Utilities. Install the server and utilities on all server machines. 3. Run setup on all MS-DOS and OS/2-system client workstations. 4. Select option 1 (MS-DOS) or option 2 (OS/2 systems) to install the utility programs. 5. Select option 2 (MS-DOS) or option 3 (OS/2 systems) to install the programming tools. 6. If you have developed SQL Server applications and wish to use the new and improved version of DB-LIBRARY, you must recompile and relink your code with the new DB-LIBRARY modules. See "Changes to DB-LIBRARY," earlier in this appendix, for information about changes you need to make to your programs for version 1.1. Updating the Error Message Table The next step in upgrading is to add one new error message to the sysmessages system table. To help update the sysmessages table while upgrading from version 1.0 to version 1.1, a script (update.sql) has been included for the System Administrator (SA) to run. To run this script: 1. Move to the directory containing the script by typing cd \sql\install 2. Redirect the file update.sql into isql by typing ISQL -Usa -Sservername -Ppassword -i update.sql where servername and password are variables. The system exits from isql when the update is complete. The following message has now been added to the error message file: Message 216: Cannot update table because it's an inner member of an outer join. The update.sql script temporarily allows the SA to update system tables. When it has finished running, however, the system is configured to prevent any further updates to the system tables. Upgrading from the Network Developer's Kit The NDK was a prerelease (Beta) edition of SQL Server. If you have the NDK version of SQL Server and are upgrading to SQL Server, Version 1.1, you must remove the NDK version from your hard disk before you can upgrade. To save any NDK databases that you want to preserve: 1. Change the Database Owner of each database to sa. See the SQL Server System Administrator's Guide. 2. Delete all other usernames from each database. 3. Back up each database. You can use either SAF to create disk dump devices and then back up the databases to those devices, or use the isql and console utility programs to back up the databases to disk. If you use disks, back up each database twice to minimize the possibility of data loss due to media failure. You need not back up the master, model, or tempdb databases. If you have added datatypes or made other changes to the master database, you should reapply those changes after you have installed the software for SQL Server, Version 1.1. Do not load an NDK backup of the master, model, or tempdb databases onto software for SQL Server, Version 1.1. 4. Use the setup program to remove all existing files from the SQL Server directory tree. (Select option 4 under the MS-DOS version of setup, option 5 under the OS/2-system version.) ──────────────────────────────────────────────────────────────────────────── WARNING This removes all files in the SQL directory. Therefore, if you've saved any batch files, queries, or other data, be sure to move them before executing this option. ──────────────────────────────────────────────────────────────────────────── 1. Install SQL Server, Version 1.1, using setup option 1. 2. Restore your databases, create login IDs, create usernames, and reapply changes to the model database as necessary.