Chapter 2 - SQL Server Setup
Before installing Microsoft SQL Server, it is recommended that you familiarize yourself with all of the "Installing SQL Server" documentation. In particular, read these topics before installing or upgrading.
Installing
Before running SQL Server Setup, read all of the documentation related to the areas of "Installing SQL Server" that interest you. Pay particular attention to these setup topics.
Users |
Topic |
Description |
---|---|---|
All users |
Before Installing: Checklist |
Steps to take before running SQL Server Setup |
New users |
Running SQL Server Setup |
What the Setup program does and the major options available |
Installation Type |
When to use a typical or custom installation |
Upgrading
When upgrading from SQL Server version 6.x to SQL Server 7.0, pay particular attention to these upgrading topics.
Users |
Topic |
Description |
---|---|---|
All users |
Before Upgrading: Checklist |
Steps to take before running the SQL Server Upgrade Wizard |
New users |
Upgrading from an Earlier Version of SQL Server |
What the SQL Server Upgrade Wizard does |
The information in "Installing SQL Server" helps you install and upgrade your SQL Server system.
Running SQL Server Setup
Use SQL Server Setup to install Microsoft SQL Server. The Setup program guides you through a series of options and then installs the components you have selected, such as the database engine, interactive management tools, and reference materials.
You can run the Setup program from the SQL Server compact disc or from a network. To run the Setup program from a network, copy the contents of the compact disc to a shared network directory.
Before Installing
Before running SQL Server Setup on a computer:
Make sure the computer meets the Microsoft SQL Server hardware and software requirements.
Set up one or more domain user accounts if you are installing SQL Server on a computer running Microsoft Windows NT, and you want SQL Server to communicate with other clients and servers.
Review the items in "Before Installing: Checklist" in this volume.
Hardware and Software Requirements for Installing SQL Server
To install Microsoft SQL Server software or the SQL Server client management tools and libraries, the computer must meet the following requirements.
Category |
Subcategory |
Requirements |
---|---|---|
Computer |
DEC Alpha and compatible systemsIntel® or compatible (Pentium 166 MHz or higher, Pentium PRO, or Pentium II) |
|
Memory (RAM) |
EnterpriseOther Editions |
64 MB minimum32 MB minimum |
Disk drive |
CD-ROM drive |
Hard disk space1 |
SQL Server OLAP ServicesEnglish Query |
180 MB (full)170 MB (typical)65 MB (minimum)90 MB (management tools only)50 MB12 MB |
Operating system |
EnterpriseStandard/Small BusinessDesktop/ManagementTools only |
Microsoft Windows NT Server Enterprise Edition version 4.0 or later with SP4 or later2Windows NT Server 4.0 or later with SP4 or later2, Microsoft BackOffice Small Business Server, or an operating system listed aboveWindows NT Workstation 4.0 or later2 with SP4 or later2, Microsoft Windows 95/98 or an operating system listed above |
Internet software |
Microsoft Internet Explorer version 4.01 with SP1 or later3 |
|
Network software |
Windows NT or Windows 95/98 built-in network software. Additional network software is not required unless you are using Banyan VINES or AppleTalk ADSP. Novell NetWare client support is provided by NWLink. |
|
Clients supported |
Windows 95/98, Windows NT Workstation, UNIX4, Apple Macintosh®4, and OS/24 |
1 These figures are the maximum hard disk space required. Setup installs a number of components that can be shared by other applications and may already exist on the computer.2 Windows NT SP4 is included on the SQL Server compact disc.3 Internet Explorer is required for Microsoft Management Console (MMC) and HTML Help. A minimal install is sufficient, and Internet Explorer does not need to be your default browser. Internet Explorer 4.01 with SP1 is included on the SQL Server compact disc.4 Requires ODBC client software from a third-party vendor. |
Creating SQL Server Services User Accounts
When running on Microsoft Windows NT, Microsoft SQL Server and SQL Server Agent are started and run as Windows NT services named MSSQLServer and SQLServerAgent. For these services to run, they must be assigned a Windows NT user account. Both services may be assigned the same user account.
Note Microsoft Windows 95/98 does not support Windows NT services; instead, SQL Server simulates the MSSQLServer and SQLServerAgent services. You do not need to create user accounts for these simulated services.
Three types of accounts can be assigned to SQL Server services:
Local system
Local user
Domain user
The local system and local user accounts do not have network access rights. Using a local account restricts SQL Server from interacting with other servers. Some server-to-server activities can be performed only with a domain user account. For example:
Remote procedure calls (RPCs)
Replication
Backing up to network drives
Heterogeneous joins that involve remote data sources
SQL Server Agent mail features and SQL Mail
This restriction applies if you are using Microsoft Exchange. Most other mail systems also require clients (MSSQLServer and SQLServerAgent) to be run on accounts with network access.
Note Another service, Microsoft Search, is installed with the full-text search custom installation option. Microsoft Search service is always assigned the local system account.
Domain User Account Requirements
All domain user accounts must have permission to:
Access and change the SQL Server directory (\MSSQL7).
Access and change the .mdf, .ndf, and .ldf database files.
Log in as a service.
Read and write registry keys at and under:
HKEY_LOCAL_MACHINE \Software \Microsoft \MSSQLServer.
HKEY_LOCAL_MACHINE \System \CurrentControlset \Services \MSSQLServer.
HKEY_LOCAL_MACHINE \Software \Microsoft \Windows NT\CurrentVersion\Perflib.
In addition, these permissions are required for certain functionality to work.
Service |
Permission |
Functionality |
---|---|---|
MSSQLServer |
Network write privileges |
Write to a mail slot using xp_sendmail |
MSSQLServer |
Act as part of operating system and replace process level token |
Run xp_cmdshell for a user other than a SQL Server administrator |
SQLServerAgent |
Member of the Administrators local group |
Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator |
SQLServerAgent |
Member of the Administrators local group |
Use the autorestart feature |
SQLServerAgent |
Member of the Administrators local group |
Use run-when-idle jobs |
It is recommended that you use a domain user account that is a member of the Administrators local group.
Note Several servers running SQL Server can share the same user account. When setting up replication, it is recommended that a Publisher and all its Subscribers share the same account.
Changing the Assigned User Account
After you have installed SQL Server, you can change the user account assigned to any of the SQL Server services by using the Services application in Control Panel. You can also change the MSSQLServer and SQLServerAgent services through SQL Server Enterprise Manager. Each service must be changed individually. The new user account will take effect the next time each service is started.
Important If full-text search is installed on the computer, do not change the MSSQLServer account information in the Services application in the Control Panel. While the Microsoft Search service is always assigned to the local system account, it keeps track of the MSSQLServer service account. You must change the MSSQLServer service account in SQL Server Enterprise Manager for the Microsoft Search service to stay in sync.
For information about creating Windows NT user accounts, granting advanced user rights, setting password expiration, and managing group memberships, see your Windows NT documentation or User Manager for Domains Help.
Before Installing: Checklist
Considerations Before Running SQL Server Setup
Back up your Microsoft SQL Server version 6.x installation if you are installing SQL Server 7.0 on the same computer.
Shut down all services dependent on SQL Server. This includes any service that is using ODBC, such as Microsoft Internet Information Services (IIS).
Shut down Microsoft Windows NT Event Viewer and Regedt32.exe.
Review the hardware and software requirements for installing SQL Server.
Create a domain user account to assign to the MSSQLServer, SQLServerAgent, and MSDTC services if you are installing SQL Server on Windows NT and plan to perform any server-to-server activities. For more information about domain accounts, see "SQL Services Logon Accounts" in this volume.
Log on to the system under a user account that has local administrative privileges.
Considerations Before Running a Custom Installation
Select a character set, sort order, and Unicode collation. If you need to change any of these later, you must rebuild the databases and reload the data. It is recommended that you develop a standard within your organization for these options. Many server-to-server activities may fail if the character set, sort order, and Unicode collation are not consistent across servers.
Review all other SQL Server installation options and be prepared to make the appropriate selections when you run the Setup program.
See Also
In This Volume
Character Set
Sort Order
Unicode Collation
Installation Options
SQL Server Setup provides several options when you install Microsoft SQL Server. Some of these installation choices determine what other options are available. Familiarize yourself with all of the options before you run the Setup program.
Name, Company, and Serial Number
During installation, SQL Server Setup prompts you to supply your name, company name, and serial number. All of these fields are required.
Be sure to supply the name of a user who is responsible for using or administering the server, as well as the serial number from the SQL Server compact disc box, which is required when calling for product support.
Upgrade SQL Server Beta
If you currently have the Beta 3 version of Microsoft SQL Server on your computer, you can upgrade the installation to SQL Server version 7.0 rather than uninstall the Beta version and install a clean copy of SQL Server.
The benefit of upgrading from the Beta 3 version is that SQL Server retains the system and user databases, saving you the time involved in re-creating system settings and reloading data.
The benefit of uninstalling SQL Server Beta 3 and installing a clean copy of SQL Server is the time the Setup program takes to complete. Uninstalling and reinstalling is faster than running the scripts involved in the upgrade, but that time is lost if you have to re-create or reload system or user data.
If you proceed with the upgrade, SQL Server Setup prompts you for the sa password. You can select a typical, minimum, or custom installation. If you choose a custom installation, you can select which components to install. All other custom installation options are retained from the Beta 3 installation.
Converting Existing SQL Server Data
If you have an existing Microsoft SQL Server version 6.x installation, select Yes, run the SQL Server Upgrade Wizard to run the wizard automatically after SQL Server Setup has installed SQL Server 7.0.
If you do not run the SQL Server Upgrade Wizard immediately after setup, you may run it later at your convenience.
Whether you run the SQL Server Upgrade Wizard immediately after setup or wait to run it at another time, read the documentation about upgrading from an earlier version of SQL Server and make preparations for the upgrade before you run SQL Server Setup.
See Also
In This Volume
Upgrading from an Earlier Version of SQL Server
Installation Type
If you are installing the Enterprise, Standard, or Desktop edition of Microsoft SQL Server, SQL Server Setup offers three installation types:
Typical
Installs all of SQL Server using the default installation options. This installation is recommended for most users.
Minimum
Installs the minimum configuration necessary to run SQL Server. This installation is recommended for users who have computers with minimum available disk space.
Custom
Installs SQL Server while allowing you to change any or all of the default options. This installation is recommended for expert users.
If you run SQL Server Setup for an edition of SQL Server that is not supported on your operating system (for example, installing SQL Server Standard Edition on Microsoft Windows 95/98), the Setup program does not offer the choice of installation types; instead, it proceeds directly to the SQL Server Components dialog box.
A typical installation and a custom installation use the same default options; however in a custom installation you can change the options. Here are the default values for the installation options.
Installation option |
Typical/Custom |
Minimum |
---|---|---|
Install database server |
Yes |
Yes |
Install upgrade tools |
Yes |
No |
Install replication support |
Yes |
Yes |
Install full-text search |
No |
No |
Install client management tools |
All |
None |
Install client connectivity |
Yes |
Yes |
Install online documentation |
Yes |
No |
Install development tools |
None |
None |
Install code samples |
None |
None |
Set character set |
1252 - ISO Character Set |
1252 - ISO Character Set |
Set sort order |
Dictionary order, case-insensitive |
Dictionary order, case-insensitive |
Set Unicode collation |
General, case-insensitive |
General, case-insensitive |
Configure network protocols (Microsoft Windows NT) |
Named Pipes, TCP/IP Sockets, and Multiprotocol |
Named Pipes, TCP/IP Sockets, and Multiprotocol |
Configure network protocols (Windows 95/98) |
TCP/IP Sockets and Multiprotocol |
TCP/IP Sockets, and Multiprotocol |
When you install SQL Server on Windows NT, these options are offered for all installation types:
Logon accounts for SQL Server and SQL Server Agent.
Whether to start SQL Server and SQL Server Agent automatically each time the computer is restarted.
You can back up and change your selections at any time before SQL Server Setup begins copying files.
Note When you click Back, the window to which you return reverts to the default options. The options you specified earlier are not retained.
File Locations
SQL Server Setup recognizes three file paths in allowing you to specify where the Microsoft SQL Server files are installed. You may specify distinct file paths for both the program and data file locations. The default directory for both the program and data files is \Mssql7. The Setup program also installs files in the system directory. The system file location cannot be changed.
Program File Location
The program file location is the root directory where the Setup program creates the folders that contain program files and files that typically do not change as you use SQL Server. Although these files are not read-only, the folders do not contain data, log, backup files, or replication data; therefore, the space requirements should not increase as SQL Server is used.
Note Program files cannot be installed on a removable disk drive.
Data File Location
The data file location is the root directory where the Setup program creates the folders that contain database and log files, as well as directories for the system log, backup, and replication data. The Setup program creates database and log files for the master, model, tempdb, msdb, pubs, and Northwind databases. The SQL Server data file path should be located on a drive that has space available for these files to grow.
Note Data files cannot be installed on a file system using compression.
SQL Server Components
When installing Microsoft SQL Server, you can choose which components to install from these categories:
SQL Server program files
Management tools
Client connectivity
Online documentation
Development tools
Code samples
You can run SQL Server Setup again later to install components that you did not install in the initial setup, or reinstall components. When run after the initial installation, the Setup program proceeds to the SQL Server Components dialog box. The typical installation options are selected by default. Select the components you want to install or reinstall and clear all others.
SQL Server Program Files
These components can be installed from the Microsoft SQL Server program files category in SQL Server Setup.
SQL Server
Installs the SQL Server relational database engine and other core tools. If any SQL Server program files are installed, the SQL Server component must be installed.Note When installing the SQL Server component, the Setup program also installs bcp, isql, osql, ODBC, OLE DB, and DB-Library.
Upgrade Tools
Installs the SQL Server Upgrade Wizard, which is used to upgrade SQL Server version 6.x databases to SQL Server 7.0. The SQL Server Upgrade Wizard can be installed only on a computer running Microsoft Windows NT.
Replication Support
Installs the scripts and binary files used for replication.
Full-Text Search
Installs the Microsoft full-text search engine, which extends the ability to search on character columns beyond the basic equality and LIKE operators. Full-text search is not available in the Desktop Edition.
Management Tools
These components can be installed from the management tools category in SQL Server Setup.
SQL Server Enterprise Manager
Used to perform server and enterprise administrative tasks.
SQL Server Profiler
Used to monitor, record, and support auditing of Microsoft SQL Server database activity.
SQL Server Query Analyzer
Used to enter Transact-SQL statements and procedures interactively. Also provides graphical query analysis in the form of graphical showplans.
MS DTC Client Support
Used to extend database transactions across multiple servers. Microsoft Distributed Transaction Coordinator (MS DTC) coordinates transactions across a network of systems running Microsoft Windows NT and Microsoft Windows 95/98.
Replication Conflict Viewer
Used to view and, if necessary, change the way synchronization conflicts are resolved.
Client Connectivity
The client connectivity component is used to allow communication between clients and servers. It includes network libraries for ODBC, OLE DB, SQL-DMO (SQL Distributed Management Objects), DB-Library, and the client network libraries.
Online Documentation
These components can be installed from the SQL Server Books Online category in SQL Server Setup.
Books Online on Disk
Installs the documentation set in the \MSSQL7\Books directory by default (program file location).
Books Online on CD
Creates a Start menu shortcut to run SQL Server Books Online from the SQL Server compact disc.
Development Tools
These components can be installed from the development tools category in SQL Server Setup.
Headers and Libraries
Installs the include (*.h) files and library (*.lib) files needed by a C developer to create programs that use OLE DB, ODBC, DB-Library, Open Data Services, SQL-DMO, Embedded SQL for C, and MS DTC. These files are installed in the \Mssql7\DevTools\Include and the \Mssql7\DevTools\Lib directories by default (program file location).
Backup/Restore API
Installs the header files, sample programs, and documentation needed to develop custom applications to back up and restore Microsoft SQL Server databases.
Debugger Interface
Installs an interface for stored procedure debugging.
Code Samples
The samples component installs programming sample files that can be used for reference when writing programs for Microsoft SQL Server. These files are installed in folders in the \Mssql7\DevTools\Samples directory by default (program file location). You can install any or all of these samples.
Option |
Name |
---|---|
DBLIB |
DB-Library |
DTS |
Data Transformation Services |
ESQLC |
Embedded SQL for C |
MS DTC |
Microsoft Distributed Transaction Coordinator |
ODBC |
Open Database Connectivity |
ODS |
Open Data Services |
OLEAut |
OLE Automation |
Repl |
Replication |
SQL-DMO |
SQL Distributed Management Objects |
SQL-NS |
SQL Namespace |
Character Set
A character set, also known as a code page, is a group of letters, digits, and symbols that make up the possible values for the Microsoft SQL Server character-based data types char, varchar, and text. Each character set contains 256 values. The printable characters of the first 128 values are the same for all character sets. The last 128 characters, referred to as extended characters, differ from set to set. The extended characters often represent language-specific letters and symbols.
Important It is critical that you select the correct character set when you install SQL Server. To change character sets after installing SQL Server, you must rebuild the databases and reload the data.
These character sets are available.
Code page |
Description |
---|---|
1252 |
ISO character set |
850 |
Multilingual |
437 |
U.S. English |
874 |
Thai |
932 |
Japanese |
936 |
Chinese (simplified) |
949 |
Korean |
950 |
Chinese (traditional) |
1250 |
Central European |
1251 |
Cyrillic |
1253 |
Greek |
1254 |
Turkish |
1255 |
Hebrew |
1256 |
Arabic |
1257 |
Baltic |
Code Page 1252 (ISO Character Set)
Code page 1252 (ISO character set) is the default character set. It is also known as the ISO 8859-1, Latin 1, or ANSI character set. It is compatible with the ANSI characters used by the Microsoft Windows NT and Microsoft Windows operating systems.
Code page 1252 might be appropriate if you intend to use clients running Windows NT or Windows 95/98 exclusively, or if you need to maintain exact compatibility with a Microsoft SQL Server environment for UNIX or VMS.
Code page 1252 (ISO 8859-1, Latin 1, or ANSI)
Code Page 850
Code page 850 is a multilingual character set that includes all the characters used by most of the languages of European, North American, and South American countries.
Code page 850 (Multilingual)
Code Page 437
Code page 437 is the most commonly used character set in the United States. Use this character set if you have character-based applications that depend on extended characters for graphics. Otherwise, use code page ISO 8859-1, which provides more compatibility with languages other than U.S. English.
Code page 437 (U.S. English)
Code Page 932
Code page 932 includes all characters defined in the shift-JIS code. This character set is supported by the Japanese versions of Microsoft Windows NT and Microsoft Windows 95/98.
Code Page 936
Code page 936 includes all characters defined in the GBK code. This character set is supported by the Simplified Chinese versions of Microsoft Windows NT and Microsoft Windows 95/98. It is supported by most popular Simplified Chinese systems and applications in China.
Code Page 949
Code page 949 includes all characters defined in the code page for the Korean versions of Microsoft Windows NT and Microsoft Windows 95/98.
Code Page 950
Code page 950 includes all characters defined in the BIG-5 code. This character set is supported by the Traditional Chinese versions of Microsoft Windows NT and Microsoft Windows 95/98. It is supported by most popular Traditional Chinese systems and applications in Taiwan and Hong Kong SAR, PRC.
Setting Client Code Pages
The code pages a client uses are determined by system settings.
Sort Order
A sort order is a set of rules that determines how Microsoft SQL Server collates and presents data in response to database queries. A sort order determines how character data is compared and in what sequence it is returned from a query.
At various points in a query, SQL Server compares characters to see if they are equal. A sort order determines what characters are considered equal when compared. A case-insensitive sort order, for instance, considers an uppercase letter to be the same as its lower-case equivalent. A case-sensitive sort order considers the two characters to be different. Another example is accent sensitivity: whether characters with accents are considered the same as their unaccented counterparts.
Important If you install a case-sensitive sort order, all entries are case-sensitive, including object names. This SELECT statement against a table named Customers is successful on a server with a case-insensitive sort order.
SELECT * FROM CUSTOMERS
The same statement fails on a server with a case-sensitive sort order.
A sort order also determines the sequence of rows in the result set of a query.
The sort orders available depend on the character set you chose. You cannot have different databases with different sort orders on the same server. In addition, you cannot back up and restore databases between servers configured for different sort orders.
Important It is critical that you select the correct sort order when you install SQL Server. If you need to change sort orders after installation, you must rebuild your databases and reload your data.
Here is an example of how the sort order affects comparisons and sorting.
Sort order |
Comparison examples |
Sorting example |
---|---|---|
Dictionary order, case-insensitive |
A = a, Ä = ä, Å = å, a ¹ à ¹ á ¹ â ¹ ä ¹ å, A ¹ Ä ¹ Å |
a, A, à, á, â, ä, Ä, Å, å 1 |
Binary2 |
A ¹ a, Ä ¹ ä, Å ¹ å, a ¹ à ¹ á ¹ â ¹ ä ¹ å, A ¹ Ä ¹ Å |
|
Dictionary order, case-sensitive |
A ¹ a, Ä ¹ ä, Å ¹ å, a ¹ à ¹ á ¹ â ¹ ä ¹ å, A ¹ Ä ¹ Å |
A, a, à, á, â, Ä, ä, Å, å |
Dictionary order, case-insensitive, uppercase preference |
A = a, Ä = ä, Å = å, a ¹ à ¹ á ¹ â ¹ ä ¹ å, A ¹ Ä ¹ Å |
A, a, à, á, â, Ä, ä, Å, å |
Dictionary order, case-insensitive, accent-insensitive |
A = a = à = á = â = Ä = ä = Å = å |
a, A, à, â, á, Ä, ä, Å, å |
1 This is one example of how the characters may be sorted. Because this sort order does not have uppercase preference, you cannot predict whether an uppercase character will come before or after its corresponding lowercase character.2 Sorting for this sort order is based on the numeric values of the characters in the installed character set. |
Other sort orders provide similar results.
Sort Order IDs
These tables show the sort order ID for each Microsoft SQL Server sort order. Each sort order is applicable to one character set only. Each table lists the available sort orders for the listed character set.
1252 ISO character set
ID |
Name |
Description |
---|---|---|
50 |
bin_iso_1 |
Binary order |
51 |
dictionary_iso |
Dictionary order, case-sensitive |
52 |
nocase_iso |
Dictionary order, case-insensitive |
53 |
nocasepref_iso |
Dictionary order, case-insensitive, uppercase preference |
54 |
noaccents_iso |
Dictionary order, case-insensitive, accent-insensitive |
71 |
dictionary_1252 |
Latin-1 case-sensitive |
72 |
nocase_1252 |
Latin-1 case-insensitive |
183 |
danno_nocasepref |
Danish/Norwegian dictionary order, case-insensitive, uppercase preference |
184 |
svfi1_nocasepref |
Swedish/Finnish (Standard) dictionary order, case-insensitive, uppercase preference |
185 |
svfi2_nocasepref |
Swedish/Finnish (Phone) dictionary order, case-insensitive, uppercase preference |
186 |
islan_nocasepref |
Icelandic dictionary order, case-insensitive, uppercase preference |
850 Multilingual
ID |
Name |
Description |
---|---|---|
40 |
bin_cp850 |
Binary order |
41 |
dictionary_850 |
Dictionary order, case-sensitive |
42 |
nocase_850 |
Dictionary order, case-insensitive |
43 |
nocasepref_850 |
Dictionary order, case-insensitive, uppercase preference |
44 |
noaccents_850 |
Dictionary order, case-insensitive, accent-insensitive |
49 |
caseless_34 |
Strict compatibility with version 1.x case-insensitive databases |
55 |
alt_dictionary |
Alternate dictionary order, case-sensitive |
56 |
alt_nocasepref |
Alternate dictionary order, case-insensitive, uppercase preference |
57 |
alt_noaccents |
Alternate dictionary order, case-insensitive, accent-insensitive |
58 |
scand_nocasepref |
Scandinavian dictionary order, case-insensitive, uppercase preference |
59 |
scand_dictionary |
Scandinavian dictionary order, case-sensitive |
60 |
scand_nocase |
Scandinavian dictionary order, case-insensitive |
61 |
alt_nocase |
Alternate dictionary order, case-insensitive |
437 U.S. English
ID |
Name |
Description |
---|---|---|
30 |
bin_cp437 |
Binary order |
31 |
dictionary_437 |
Dictionary order, case-sensitive |
32 |
nocase_437 |
Dictionary order, case-insensitive |
33 |
nocasepref_437 |
Dictionary order, case-insensitive, uppercase preference |
34 |
noaccents_437 |
Dictionary order, case-insensitive, accent-insensitive |
874 Thai
ID |
Name |
Description |
---|---|---|
204 |
bin_cp874 |
Binary order |
205 |
nls_cp874 |
Dictionary order, case-insensitive (Windows NT provided) |
206 |
nls_cp874_cs |
Dictionary order, case-sensitive |
932 Japanese
ID |
Name |
Description |
---|---|---|
192 |
bin_cp932 |
Binary order |
193 |
nls_cp932 |
Dictionary order, case-insensitive (Windows NT provided) |
200 |
nls_cp932_cs |
Dictionary order, case-sensitive |
936 Chinese (simplified)
ID |
Name |
Description |
---|---|---|
198 |
bin_cp936 |
Binary order |
199 |
nls_cp936 |
Dictionary order, case-insensitive (Windows NT provided) |
203 |
nls_cp936_cs |
Dictionary order, case-sensitive |
949 Korean
ID |
Name |
Description |
---|---|---|
194 |
bin_cp949 |
Binary order |
195 |
nls_cp949 |
Dictionary order, case-insensitive (Windows NT provided) |
201 |
nls_cp949_cs |
Dictionary order, case-sensitive |
950 Chinese (traditional)
ID |
Name |
Description |
---|---|---|
196 |
bin_cp950 |
Binary order |
197 |
nls_cp950 |
Dictionary order, case-insensitive (Windows NT provided) |
202 |
nls_cp950_cs |
Dictionary order, case-sensitive |
1250 Central European
ID |
Name |
Description |
---|---|---|
80 |
bin_cp1250 |
Binary order |
81 |
dictionary_1250 |
Dictionary order, case-sensitive |
82 |
nocase_1250 |
Dictionary order, case-insensitive |
83 |
csydic |
Czech dictionary order, case-sensitive |
84 |
csync |
Czech dictionary order, case-insensitive |
85 |
hundic |
Hungarian dictionary order, case-sensitive |
86 |
hunnc |
Hungarian dictionary order, case-insensitive |
87 |
plkdic |
Polish dictionary order, case-sensitive |
88 |
plknc |
Polish dictionary order, case-insensitive |
89 |
romdic |
Romanian dictionary order, case-sensitive |
90 |
romnc |
Romanian dictionary order, case-insensitive |
91 |
shldic |
Croatian dictionary order, case-sensitive |
92 |
shlnc |
Croatian dictionary order, case-insensitive |
93 |
skydic |
Slovak dictionary order, case-sensitive |
94 |
skync |
Slovak dictionary order, case-insensitive |
95 |
slvdic |
Slovenian dictionary order, case-sensitive |
96 |
slvnc |
Slovenian dictionary order, case-insensitive |
1251 Cyrillic
ID |
Name |
Description |
---|---|---|
104 |
bin_cp1251 |
Binary order |
105 |
dictionary_1251 |
Dictionary order, case-sensitive |
106 |
nocase_1251 |
Dictionary order, case-insensitive |
107 |
ukrdic |
Ukrainian dictionary order, case-sensitive |
108 |
ukrnc |
Ukrainian dictionary order, case-insensitive |
1253 Greek
ID |
Name |
Description |
---|---|---|
112 |
bin_cp1253 |
Binary order |
113 |
dictionary_1253 |
Dictionary order, case-sensitive |
114 |
nocase_1253 |
Dictionary order, case-insensitive |
120 |
greek_mixeddictionary |
Mixed dictionary order |
121 |
greek_altdictionary |
Dictionary order, case-sensitive, accent-sensitive |
124 |
greek_nocasedict |
Dictionary order, case-insensitive, accent-insensitive |
1254 Turkish
ID |
Name |
Description |
---|---|---|
128 |
bin_cp1254 |
Binary order |
129 |
dictionary_1254 |
Dictionary order, case-sensitive |
130 |
nocase_1254 |
Dictionary order, case-insensitive |
1255 Hebrew
ID |
Name |
Description |
---|---|---|
136 |
bin_cp1255 |
Binary order |
137 |
dictionary_1255 |
Dictionary order, case-sensitive |
138 |
nocase_1255 |
Dictionary order, case-insensitive |
1256 Arabic
ID |
Name |
Description |
---|---|---|
144 |
bin_cp1256 |
Binary order |
145 |
dictionary_1256 |
Dictionary order, case-sensitive |
146 |
nocase_1256 |
Dictionary order, case-insensitive |
1257 Baltic
ID |
Name |
Description |
---|---|---|
152 |
bin_cp1257 |
Binary order |
153 |
dictionary_1257 |
Dictionary order, case-sensitive |
154 |
nocase_1257 |
Dictionary order, case-insensitive |
155 |
etidic |
Estonian dictionary order, case-sensitive |
156 |
etinc |
Estonian dictionary order, case-insensitive |
157 |
lvidic |
Latvian dictionary order, case-sensitive |
158 |
lvinc |
Latvian dictionary order, case-insensitive |
159 |
lthdic |
Lithuanian dictionary order, case-sensitive |
160 |
lthnc |
Lithuanian dictionary order, case-insensitive |
Dictionary Order, Case-insensitive
This is the default sort order. With this sort order:
Uppercase and lowercase characters are treated as equivalent.
Characters with diacritical marks are treated as different from all other characters (except their corresponding uppercase or lowercase characters).
For example:
A = a Ä = ä Å = å a ¹ à ¹ á ¹ â ¹ ä ¹ å A ¹ Ä ¹ Å Jose = jose Jose ¹ Josè
With this sort order, characters are sorted according to the following rules:
Uppercase characters are not given sorting preference over their corresponding lowercase characters (they can appear before or after them).
Uppercase characters with diacritical marks are not given sorting preference over their corresponding lowercase characters (they can appear before or after them).
Characters with diacritical marks are sorted after characters without diacritical marks.
Characters with diacritical marks are sorted in the order in which they appear in the character set.
For example:
A, a, à, á, â, Ä, ä, Å, å a, A, à, á, â, Ä, ä, Å, å A, a, à, á, â, ä, Ä, Å, å
Binary Order
Binary order is the simplest and fastest sort order. The collating sequence for this sort order is based on the numeric value (from 0 through 255) of the characters in the installed character set. Using the binary sort order, ZYXWV sorts before abcde because the character Z precedes the character a in all character sets. Sorting for extended characters (such as characters with diacritical marks) depends on the installed character set.
Important With a case-sensitive sort order, object names are case-sensitive. Because the binary sort order differentiates all character values, including uppercase and lowercase equivalents, object names are case-sensitive as well.
If you want your data to sort in a more conventional order, choose one of the dictionary sort orders. However, dictionary sort orders come with a trade-off in performance.
Dictionary Order, Case-sensitive
With this sort order:
Uppercase and lowercase characters are treated as non-equivalent.
Characters with diacritical marks are treated as different from all other characters (including their corresponding uppercase or lowercase characters).
For example:
A ¹ a Ä ¹ ä Å ¹ å a ¹ à ¹ á ¹ â ¹ ä ¹ å A ¹ Ä ¹ Å Jose ¹ jose Jose ¹ José
With this sort order:
Uppercase characters are sorted before their corresponding lowercase characters.
Uppercase characters with diacritical marks are sorted before their corresponding lowercase characters.
Characters with diacritical marks are sorted after characters without diacritical marks.
Characters with diacritical marks are sorted by the order in which they appear in the character set.
For example:
A, a, à, á, â, Ä, ä, Å, å
Warning If you install a case-sensitive sort order, all entries are case-sensitive, including object names.
Dictionary Order, Case-insensitive, Uppercase Preference
With this sort order, characters with diacritical marks are treated as different from all other characters (except their corresponding uppercase or lowercase characters). For example:
A = a Ä = ä Å = å a ¹ à ¹ á ¹ â ¹ ä ¹ å A ¹ Ä ¹ Å Jose = jose Jose ¹ José
With this sort order:
Uppercase characters are sorted before their corresponding lowercase characters.
Uppercase characters with diacritical marks are sorted before their corresponding lowercase characters.
Characters with diacritical marks are sorted after characters without diacritical marks.
Characters with diacritical marks are sorted by the order in which they appear in the character set.
For example:
A, a, à, á, â, Ä, ä, Å, å
Dictionary Order, Case-insensitive, Accent-insensitive
With this sort order:
Uppercase and lowercase characters are treated as equivalent.
Characters with diacritical marks are treated as equal to their counterparts without diacritical marks.
For example:
A = a = à = á = â = Ä = ä = Å = å Jose = jose = José = josé
When sorting with this sort order, lowercase characters are paired with their corresponding uppercase characters (for example, a and A) and are sorted before any corresponding uppercase character with a diacritical mark (for example, Ä).
For example:
A, a, à, á, â, Ä, ä, Å, å
Version 1.x-compatible Sort Order
This sort order is provided for strict compatibility with Microsoft SQL Server version 1.x case-insensitive databases. It uses the dictionary sort order with case insensitivity and no uppercase preference for the first 128 characters of the character set. It uses the binary sort order for the remaining 128 characters. Using this sort order choice, a and A are sorted and grouped together, but ä and Ä are sorted as unique characters according to their binary values in the installed character set.
This option offers no advantage over other sort orders other than strict compatibility with earlier versions of SQL Server. Unless you have case-insensitive databases set up for earlier versions of SQL Server that use extended characters, and you require the upgraded version to have exactly the same, less functional sort order semantics, you should choose another sort order.
Alternate Sort Orders
The alternate sort orders are similar to their regular sort order counterparts, but have these differences:
For the case-sensitive option, all uppercase variants of a character are sorted before any equivalent lowercase variants. For example:
A, À, Á, Ä, a, à, á, ä
This is in contrast to the standard case-sensitive option in which the uppercase and lowercase pairings are maintained for each distinct character. The characters in the previous example are sorted as follows, using the standard dictionary order with case sensitivity:
A, a, À, à, Á, á, Ä, ä
Ø and ø sort at the end of the character set.
Ñ is sorted distinctly from N.
Scandinavian Sort Orders
The Scandinavian sort orders differ from the other sort orders because the extended characters (Ø, Å, Ä, and Ö) are sorted after Z.
Japanese Sort Orders
The Japanese dictionary sort orders sort characters by the Japanese NLS (National Language Support) provided by the Japanese versions of Microsoft Windows NT and Microsoft Windows 95/98. The dictionary sort orders are available in case-sensitive and case-insensitive formats.
NLS is sorted phonetically with the following rules:
Numerals are sorted in ascending order.
Symbols are sorted by code order.
For a representing character, no distinction is made between the single-character width and double-character width versions.
For katakana, alphabet, and special characters, the shift-JIS character set (code page 932) has two codes for each character, with one single-character width and one double-character width. For example, there is a single-character width A and a double-character width A.
A long vowel is regarded as representing its preceeding vowel.
A voiced constant and a Japanese voiceless sound. For example, ba and pa are considered the same as ha.
The binary sort order sorts characters by the code order of the shift-JIS character set.
Chinese (simplified) Sort Orders
The Chinese (simplified) dictionary sort orders sort characters by the Pin Yin (phonetic) and stroke count order provided by the simplified Chinese versions of Microsoft Windows NT and Microsoft Windows 95/98. The dictionary sort orders are available in case-sensitive and case-insensitive formats.
The binary sort order sorts characters by the code order of the GBK code.
Korean Sort Orders
The Korean dictionary sort orders sort characters by the Korean NLS (National Language Support) provided by the Korean versions of Microsoft Windows NT and Microsoft Windows 95/98. The dictionary sort orders are available in case-sensitive and case-insensitive formats.
Chinese (traditional) Sort Orders
The Chinese (traditional) dictionary sort orders sort characters by the Traditional Chinese NLS (National Language Support) provided by the Traditional Chinese versions of Microsoft Windows NT and Microsoft Windows 95/98. The dictionary sort orders are available in case-sensitive and case-insensitive formats.
Sort Order Performance
Binary sort order is the fastest sort order option. The overall performance difference between sort orders varies significantly depending on the types of operations and the type of data used. Sorting operations that depend on string comparisons in character fields (for example, GROUP BY and LIKE statements) take more time to process than other sorting operations and have the greatest effect on sorting speed.
With certain queries, if there is a nonunique clustered index on a character value, sort order options that define uppercase preference require the optimizer to perform a sort. Using a sort order with no uppercase preference, such as dictionary order, case-insensitive (no uppercase preference), improves performance because no sorting is necessary. Choosing dictionary order, case-insensitive (no uppercase preference), causes an uppercase A and a lowercase a to be treated equally in an ORDER BY statement.
Effect on Passwords of Changing Sort Orders
In SQL Server Authentication, when a login ID is added or a password is changed, the password encryption algorithm takes the supplied password and generates a numeric representation, which is stored in the syslogins table.
If the password is generated on a case-sensitive server, the original password text, as typed, is supplied to the password algorithm. When the password is subsequently checked, the user must supply the same characters in the appropriate case for authentication.
If the password is generated on a case-insensitive server, the password is converted to uppercase before generating and checking the encrypted form. When the password is subsequently checked, the user can supply the characters in upper-case or lowercase.
When changing sort orders, if a user transfers syslogins out of the server with one sort order and into a new server with another sort order, the encrypted passwords are moved as generated. This has the following effects:
If moving from case-insensitive to case-sensitive servers, users must type passwords in uppercase to be authenticated.
If moving from case-sensitive to case-insensitive servers, users cannot log in unless the password generated in the case-sensitive server was entirely in uppercase.
When changing sort orders, take the appropriate steps to ensure that users are able to log in to the server when the sort order conversion is complete. If the system administrator is not able to log in because of the effects described, he or she must restore the original sort order to correct the problem.
Unicode Collation
Unicode collation acts as a sort order for Unicode data. This is separate from the sort order for non-Unicode data.
A Unicode collation consists of a locale and several comparison styles. Locales are usually named after countries or cultural regions. They sort characters as is standard in that area. The Unicode collation still provides a sort order for all characters in the Unicode standard, but precedence is given to the locale specified. Here are the locales available in Microsoft SQL Server.
Locale ID |
Name |
---|---|
1033 |
General Unicode |
33280 |
Binary Order |
1027 |
Catalan |
197636 |
Chinese Bopomofo (Taiwan) |
2052 |
Chinese Punctuation |
133124 |
Chinese Stroke Count |
1028 |
Chinese Stroke Count (Taiwan) |
1050 |
Croatian |
1029 |
Czech |
1043 |
Dutch |
1061 |
Estonian |
1036 |
French |
66615 |
Georgian Modern |
1031 |
German |
66567 |
German Phone Book |
1038 |
Hungarian |
66574 |
Hungarian Technical |
1039 |
Icelandic |
1040 |
Italian |
1041 |
Japanese |
66577 |
Japanese Unicode |
1042 |
Korean |
66578 |
Korean Unicode |
1062 |
Latvian |
1063 |
Lithuanian |
1071 |
FYRO Macedonian |
1044 |
Norwegian/Danish |
1045 |
Polish |
1046 |
Portuguese |
1048 |
Romanian |
1051 |
Slovak |
1060 |
Slovenian |
1034 |
Spanish Traditional |
3082 |
Spanish Modern |
1053 |
Swedish/Finnish |
1054 |
Thai |
2057 |
UK English |
1058 |
Ukrainian |
1066 |
Vietnamese |
The general Unicode collation locale is compatible with many locales that are not available individually in SQL Server. Choose the general Unicode collation for any of these locales.
Afrikaans |
Faeroese |
Malay |
Albanian |
Farsi |
Russian |
Arabic |
Georgian |
Serbian |
Basque |
Greek |
Swahili |
Bulgarian |
Hebrew |
Urdu |
Belarusian |
Hindi |
|
English |
Indonesian |
The four comparison styles specify whether or not to ignore the differences among similar characters. If the case-insensitive comparison style is chosen, for example, the characters A and a are considered equal. The width-insensitive and Kana-insensitive comparison styles are relevant only to characters found in certain East Asian languages.
During installation, you must specify a Unicode collation. The default value is based on the character set and sort order already chosen. You may choose a collation other than the default, but exercise this option with care. When a different value is chosen:
Migrating data from non-Unicode to Unicode will be more difficult.
Unicode and non-Unicode data may sort differently.
Caution When upgrading an existing SQL Server version 6.x installation to SQL Server 7.0, always choose the default Unicode collation. The master database in SQL Server 7.0 contains Unicode columns that were non-Unicode in the 6.x installation. If a nondefault Unicode collation is chosen that sorts data differently than it was sorted in the 6.x installation, uniqueness constraints may be violated and conversion of SQL Server 6.x user objects to SQL Server 7.0 may fail.
See Also
In Other Volumes
"Unicode Data" in Microsoft SQL Server Database Developer's Companion
"Using Unicode Data" in Microsoft SQL Server Database Developer's Companion
Unicode Standard
Before the development of the Unicode standard, character data was limited to sets of 256 characters. This limitation came from the one-byte storage space used by a single character; one byte can represent only 256 different bit combinations. The Unicode standard expands the number of possible values for character data. By doubling the amount of storage space used for a single character, the Unicode standard exponentially increases the number of possible character values from 256 to 65,536. With this increased range, the Unicode standard includes letters, numbers, and symbols used in languages around the world, including all of the values from the previously existing character sets.
Difference Between Unicode and Character Sets
The Unicode standard and character sets are closely related but distinct concepts. Both represent a set of letters, numbers, and symbols. A character set has 256 possible values (28), and a number of character sets exist. The single Unicode standard has 65,536 possible values (216). This greater range of values is possible for Unicode data because of the increased storage space it uses. The Unicode standard and character sets fulfill the same purpose, to represent a set of character data, but they differ in how they fulfill that purpose.
These differences between Unicode and character set data result in different implementations, as well. Unicode and character set data cannot be stored in the same fields; a set of data types exist for each.
Character set |
Unicode |
---|---|
char |
nchar |
varchar |
nvarchar |
text |
ntext |
Microsoft SQL Server recognizes only one character set at a time. The char, varchar, and text data types can store data made up of the 256 possible values of that particular character set. To change the character set, you must rebuild the databases and reload the data.
SQL Server always recognizes the Unicode standard. The nchar, nvarchar, and ntext data types can store data made up of the 65,536 possible values of the Unicode standard.
Network Protocols
Microsoft SQL Server uses network libraries to pass network packets back and forth between clients and a server running SQL Server. The network libraries, implemented as dynamic-link libraries (DLLs), perform the network operations required to communicate by using specific interprocess communication (IPC) mechanisms.
A server can listen on multiple network libraries at one time. During installation, SQL Server Setup installs all of the supported Net-Libraries onto the computer and allows you to configure some or all of the Net-Libraries. If a particular Net-Library is not configured, the server cannot listen on that Net-Library. After installation, you can change these configurations using the Server Network utility.
Named Pipes
Named Pipes support is required on Microsoft Windows NT installations of SQL Server. Server-side Named Pipes is not supported on Microsoft Windows 95/98. By default, SQL Server listens on the standard pipe, \\.\pipe\sql\query, for Named Pipes Net-Library connections. After SQL Server is installed, you can change the pipe name. You can also drop named pipe support and set SQL Server to listen only on other Net-Libraries.
TCP/IP Sockets
This Net-Library allows SQL Server to communicate by using standard Windows Sockets as the IPC method across the TCP/IP protocol. By default, Windows 95/98 uses the TCP/IP Sockets Net-Library.
If you set SQL Server to listen on TCP/IP, type the TCP/IP port number in the Port number box. This is the port that SQL Server listens on when accepting connections from TCP/IP Sockets clients. The default is 1433, the official Internet Assigned Number Authority (IANA) socket number for SQL Server.
If you set SQL Server to listen on a proxy server using Microsoft Proxy Server over TCP/IP Sockets, type the proxy server address in the Remote WinSock proxy address box when you set up the TCP/IP Sockets Net-Library.
Multiprotocol
The Multiprotocol Net-Library uses the Windows remote procedure call (RPC) facility. With the Multiprotocol Net-Library, unlike other Net-Libraries, configuration parameters are not required.
The Multiprotocol Net-Library:
Communicates over most IPC mechanisms supported by Windows NT. (Only TCP/IP Sockets, NWLink IPX/SPX, and Named Pipes are considered tested and supported.)
Allows the use of Windows NT Authentication over all protocols that RPC supports.
Supports encryption for user password authentication as well as data.
Offers performance comparable to native IPC Net-Libraries for most applications.
To use Multiprotocol encryption for password authentication and data, select Enable Multiprotocol encryption. The Multiprotocol encryption is not supported on Windows 95/98.
NWLink IPX/SPX
This Net-Library allows SQL Server to communicate using the NWLink IPX/SPX protocol.
If you set up SQL Server to listen on NWLink IPX/SPX, the Setup program prompts you for the Novell Bindery service name in which to register SQL Server on the Novell network. The default service name is the computer name of the server computer. The Net-Library allows Novell SPX clients to connect.
AppleTalk ADSP
The server AppleTalk (ADSP) Net-Library allows Apple Macintosh clients to connect to SQL Server by using native AppleTalk (as opposed to TCP/IP Sockets).
If you set up SQL Server to listen on AppleTalk, the Setup program prompts you for the AppleTalk service object name. The AppleTalk service object name is assigned by your system administrator, who might want to use the computer name of the server for consistency; for example, ACCOUNTING1.
It is not necessary to enter an AppleTalk zone because the local zone is used when registering the service.
The AppleTalk Net-Library is not supported on Windows 95/98.
Banyan VINES
SQL Server supports Banyan VINES Sequenced Packet Protocol (SPP) as the IPC method across the Banyan VINES IP network protocol. Banyan VINES support for clients and servers running Windows NT is available for SQL Server on the Intel platform only; it is not currently available on Windows 95/98 or the Alpha AXP platform.
If you set up SQL Server to listen on Banyan VINES, the Setup program prompts you for a StreetTalk service name. This has the form servicename@group@org, where servicename is the StreetTalk computer-based service name used by SQL Server, group is the group, and org is the organization. The computer-based service name used by SQL Server must first be created by using the MSERVICE program included with your VINES software. Also, to start SQL Server, you must be logged in with administrative permissions.
All Net-Libraries are installed by the Setup program. These are the default server Net-Library settings by platform.
Windows NT |
Windows 95/98 |
---|---|
TCP/IP Sockets |
TCP/IP Sockets |
Named Pipes |
Shared Memory1 |
Multiprotocol |
Multiprotocol |
1 Shared Memory is a Net-Library used only for client/server connections on the same Windows 95/98 computer. You do not need to configure the Shared Memory Net-Library. |
Here are the default client Net-Library settings by platform.
Windows NT |
Windows 95/98 |
---|---|
Named Pipes |
TCP/IP Sockets |
SQL Server Services Logon Accounts
You must assign a logon account for each of the Microsoft SQL Server services. You may also specify whether the services should start automatically whenever you restart Microsoft Windows NT. The SQLServerAgent service is dependent on the MSSQLServer service. You can autostart the SQLServerAgent service only if you autostart the MSSQLServer service as well.
You may enter one logon account for all of the services, or specify an account for each. The default account is the domain user account currently logged into the computer.
Note If you perform a custom installation and install the full-text search component, SQL Server Setup assigns the local system account to the Microsoft Search service and sets it to autostart. To change these options, run the Control Panel Services application.
See Also
In This Volume
Creating SQL Server Services User Accounts
Unattended Installation
You can perform an unattended installation using SQL Server Setup to install Microsoft SQL Server without having to respond to prompts. Instead, the Setup program reads the installation settings from a setup initialization file. An unattended setup can be convenient if you want to install several instances of SQL Server with identical configurations. You can also use Microsoft Systems Management Server (SMS) to perform unattended installations on multiple Microsoft Windows NT Servers in your enterprise.
For installing SQL Server, the SQL Server compact disc contains two setup initialization files. The compact disc also includes two command files that detect the platform of the computer and run the appropriate version of the Setup program with a corresponding initialization file.
For removing SQL Server, the SQL Server compact disc contains an executable file that removes SQL Server.
Batch file |
Initialization file |
Action |
---|---|---|
sql70cli.bat |
sql70cli.iss |
Installs the SQL Server management tools |
sql70ins.bat |
sql70ins.iss |
Installs a typical installation of SQL Server1 |
sql70cst.bat |
sql70cst.iss |
Installs a custom installation of SQL Server1,2 |
sql70rem.bat |
Removes SQL Server |
1 These files assign the SQL Server services to the local system account. If you want the unattended installation to assign domain user accounts, create a custom setup initialization file.2 Installs all SQL Server components. |
You can also create custom setup initialization files to automate any set of installation options.
Creating a Setup Initialization File
A setup initialization file specifies all the options a user might select in SQL Server Setup, allowing the Setup program to run without prompting from the user. The Microsoft SQL Server compact disc comes with three setup initialization files. If you want initialization options other than these, create a new setup initialization file rather than edit an existing one.
A setup initialization file can be created using two methods.
Running SQL Server Setup interactively
By default, when you install SQL Server by running SQL Server Setup interactively, your setup options are recorded into a setup initialization file. This file, Setup.iss, is placed in the \Mssql7\Install directory.
SQL Server Setup can also generate an .iss file without actually installing SQL Server. In the \x86\Setup or \Alpha\Setup directory on the SQL Server compact disc, run this program from the command prompt:
setupsql.exe k=Rc
Running Setupsql.exe with the k=Rc option causes SQL Server Setup to write the Setup.iss file to the \Windows or \WinNT directory while you select setup options rather than waiting until after the files are copied. When the Setup program prompts you that it is ready to begin copying files, click Cancel to exit without installing SQL Server.
The Setup.iss file created with the k=Rc option is incomplete. Add the [SdStartCopy-0] and [SdFinish-0] sections detailed at the end of the setup initialization file format sections, below.
Using a text editor to create a setup initialization file
The file should be saved with the .iss file name and be compatible with the Windows initialization file format.
Format of the Setup Initialization File
The setup initialization file is a text file that uses standard Windows .ini file format. The sections, key names, and values are described in the following tables.
[InstallShield Silent]
This section is required for InstallShield. Do not change the values.
Entry |
Description |
Value |
---|---|---|
Version |
Version of the InstallShield Silent response file |
v5.00.000 |
File |
Response File |
[DlgOrder]
List each dialog box in the order it appears in an attended setup. The listing in this section must correspond to the other sections in the setup initialization file. An entry is required for each section name that ends in a number.
Entry 1 |
Description |
Value 2 |
---|---|---|
Dlg0 |
First dialog box |
SetupMethod-0 |
Count |
Number of dialog boxes listed in this section |
|
Dlg1 |
Second dialog box |
SdWelcome-0 |
Dlg2 |
Third dialog box |
SdLicense-0 |
1 This example includes only a partial listing. You must include an entry for each attended setup dialog box.2 If you click Back during an attended setup, multiple sections are recorded in the Setup.iss for each dialog box displayed more than one time. The section name of the first instance has a -0 ending. The second instance has a -1 ending. Only the last instance of a dialog box determines the setup options used. |
[SetupMethod-0]
Corresponds to the Select Install Method dialog box.
Entry |
Description |
Value |
---|---|---|
Component-type |
String |
|
Component-count |
1 |
|
Component-0 |
Specifies where SQL Server is being installed |
RemoteLocal |
Result |
Dialog box return value |
1 = Next |
[SkRemoteInfo]
Corresponds to the Remote Setup Information dialog box. This section is included for a remote installation only.
Entry |
Description |
Value |
---|---|---|
svEdit1 |
Domain user account to connect to target computer |
|
svEdit2 |
Domain user account password |
|
svEdit3 |
Domain name |
|
svEdit4 |
Target computer name |
\\computername |
svEdit5 |
Target computer path, including the computer name and volume |
\\computername\volume |
svEdit6 |
Path of the SQL Server source files |
|
Result |
Dialog box return value |
1 = Next |
[SdWelcome-0]
Corresponds to the Welcome dialog box.
Entry |
Description |
Value |
---|---|---|
Result |
Dialog box return value |
1 = Next |
[SdLicense-0]
Corresponds to the Software License Agreement dialog box.
Entry |
Description |
Value |
---|---|---|
Result |
Dialog box return value |
1 = Yes |
[SdRegisterUser-0]
Corresponds to the User Information dialog box.
Entry |
Description |
Value |
---|---|---|
szName |
User or owner |
|
szCompany |
Organization |
|
Result |
Dialog box return value |
1 = Next |
[CDKEYDialog-0]
Specify your product ID in this section, where xxx-xxxxxxx is the 10-digit CD key found on the yellow sticker of your compact disc liner notes or compact disc sleeve, and yyyy is a number of your choice, up to five digits.
Entry |
Description |
Value |
---|---|---|
svCDKey |
Product ID |
28174-xxx-xxxxxxx-yyyy |
Result |
Dialog box return value |
1 = Next |
[AskOptions-0]
Corresponds to the Convert Existing SQL Server Data dialog box, which asks the user if he or she wants to run the SQL Server Upgrade Wizard at the end of setup to convert existing SQL Server 6.x databases to SQL Server 7.0. This section applies only to computers that have a previous version of SQL Server already installed.
Entry |
Description |
Value |
---|---|---|
Sel-0 |
Check box for running the SQL Server Upgrade Wizard |
1 = Yes0 = No |
Result |
Dialog box return value |
1 = Next |
[SetupTypeSQL-0]
Corresponds to the Setup Type dialog box, which asks a user to select the type of installation and specify the file destination folders.
Entry |
Description |
Value |
---|---|---|
SzDir |
Directory where SQL Server program files are installed |
|
SzDir |
Directory where SQL Server data files are installed |
|
Result |
Dialog box return value |
301 = Typical302 = Minimum303 = Custom |
[SQLComponentMult-0]
Corresponds to the Select Components dialog box. This applies only when the setup type is Custom. The entries are an internal list of component names of the selected components. If you want to perform a custom setup, run SQL Server Setup interactively to create a list of components to install.
[DlgCpSortUnicode-0]
Corresponds to the Character Set/Sort Order/Unicode Collation dialog box. The character set is uniquely identified by the Sort Order ID and is not needed as an entry. Although this dialog box is shown only in a custom installation, this section is always included in the setup initialization file.
Entry |
Description |
Value |
---|---|---|
SortId |
Sort order ID |
For more information, see "Sort Order IDs" in this volume |
LCID |
Unicode locale ID |
For more information, see "Unicode Collation" in this volume |
CompStyle |
Bitmask that represents the Unicode comparison styles |
0x00001 = ignore case0x00002 = ignore accent0x10000 = ignore Kana0x20000 = ignore width |
Result |
Dialog box return value |
1 = Next |
[DlgServerNetwork-0]
Corresponds to the Network Libraries dialog box. Although this dialog box is shown only in a custom installation, this section is always included in the setup initialization file.
Entry |
Description |
Value |
---|---|---|
NetworkLibs |
Bitmask that represents the network libraries to configure |
NET_NMP = 0xF = Named PipesNET_TCP = 0xF0 = TCP/IP SocketsNET_RPC = 0xF00 = MultiprotocolNET_NWL = 0xF000 = NWLink IPX/SPXNET_APL = 0xF0000 = AppleTalkNET_BAN = 0xF00000 = Banyan VinesNET_ENC = 0xF0000000 = Multiprotocol encryption |
TCPPort |
TCP/IP port number, used when TCP/IP Sockets is configured |
Default = 1433 |
TCPPrxy |
Remote WinSock proxy address, used when TCP/IP is configured |
|
NMPPipeName |
Named pipe name, used when Named Pipes is configured |
\\.\pipe\sql\query |
NWLinkObj |
Novell Bindery service name |
|
BanyanObj |
StreetTalk service name |
|
ApplObj |
AppleTalk service object |
|
Result |
Dialog box return value |
1 = Next |
[DlgServices-0]
Corresponds to the Services Accounts dialog box. This section is not included for installations on Windows 95/98.
Entry |
Description |
Value |
---|---|---|
Local-Domain |
Bitmask that represents whether the service accounts are local system or a Windows NT domain account |
SQL_LOCAL = 0xFSQL_DOMAIN = 0xF0AGT_LOCAL = 0xF00AGT_DOMAIN = 0xF000 |
autoStart |
Bitmask that defines whether services are configured to autostart |
SQL_AUTO = 0xFAGT_AUTO = 0xF0 |
SQLDomain |
Domain of user account for MSSQLServer service |
|
SQLDomainAcct |
MSSQLServer domain account |
|
SQLDomainPwd |
MSSQLServer domain account password |
Encrypted password generated by setup. This can be obtained only by running setup and generating an .iss file. |
AgtDomain |
Domain of user account for SQLServerAgent service |
|
AgtDomainAcct |
SQLServerAgent domain account |
|
AgtDomainPwd |
SQLServerAgent domain account password |
Encrypted password generated by setup. This can be obtained only by running setup and generating an .iss file. |
Result |
Dialog box return value |
1 = Next |
[SdStartCopy-0]
Corresponds to the Start Copying Files dialog box.
Entry |
Description |
Value |
---|---|---|
Result |
Dialog box return value |
1 = Next |
[License]
Corresponds to the Choose Licensing Mode dialog box.
Entry |
Description |
Value |
---|---|---|
License Mode |
Dialog box return value |
PERSERVERPERSEAT |
License Limit |
Number of client access licenses for this installation of SQL Server (for PERSERVER license mode only) |
[SdFinish-0]
Corresponds to the Setup Complete dialog box.
Entry |
Description |
Value |
---|---|---|
bOpt1 |
Not used, placeholder as part of stock dialog box |
|
bOpt2 |
Not used, placeholder as part of stock dialog box |
|
Result |
Dialog box return value |
1 = Next |
Installing SQL Server Using SMS
You can use Microsoft Systems Management Server (SMS) version 1.2 or later to install Microsoft SQL Server automatically on multiple server computers running Microsoft Windows NT in your enterprise.
The SQL Server compact disc contains a Package Definition Format (PDF) file, Smssql70.pdf, that automates creating a SQL Server package in SMS. The SQL Server package can then be distributed and installed on SMS computers.
Smssql70.pdf includes instructions for running the three setup command files included on the SQL Server compact disc. To run a custom command file that you have created, make a copy of Smssql70.pdf and edit it to run your command file.
After Installing
After installation is complete, you can begin using Microsoft SQL Server immediately.
Components of Your SQL Server Installation
These are the main components of your Microsoft SQL Server installation.
Component |
Description |
---|---|
SQL Server program files |
Relational database engine and other core tools. |
Databases |
System and sample databases. |
Stored procedures |
Precompiled collection of Transact-SQL statements. |
SQL Server Upgrade Wizard |
Tool for upgrading SQL Server version 6.x databases to SQL Server 7.0. |
Management tools |
Group of interactive tools used for administering SQL Server. |
Online documentation |
SQL Server documentation set in online format. |
Setup initialization file |
File used in unattended installations. It contains all of the setup options you chose during this installation. Setup creates the initialization file in either the \Windows or \WinNT directory. |
Start menu options |
SQL Server 7.0 program group that is created on the Start menu with shortcuts to various SQL Server applications. If you have installed SQL Server on Microsoft Windows NT, a second program group is installed to provide for the SQL Server Upgrade Wizard. Additionally, if SQL Server 6.x is installed on the same computer, a switch program is installed to take you to the previous release. |
See Also
In This Volume
Overview of SQL Server Architecture
Unattended Installation
Upgrading from an Earlier Version of SQL Server
In Other Volumes
"System Stored Procedures" in Microsoft SQL Server Transact-SQL and Utilities Reference
System and Sample Databases
When Microsoft SQL Server is installed, SQL Server Setup creates these database and log files.
Database |
Database file |
Log file |
---|---|---|
master |
master.mdf |
mastlog.ldf |
model |
model.mdf |
modellog.ldf |
msdb |
msdbdata.mdf |
msdblog.ldf |
tempdb |
tempdb.mdf |
templog.ldf |
pubs |
pubs.mdf |
pubs_log.ldf |
Northwind |
northwnd.mdf |
northwnd.ldf |
master, model, msdb, and tempdb are system databases. Northwind and pubs are sample databases that are provided as learning tools. Most of the examples in the SQL Server documentation are based on these databases. The sample databases are not installed in an embedded installation.
Note The default location of the database and log files is \Mssql7\Data. The location may vary if the default location was changed when SQL Server was installed.
See Also
In This Volume
System Databases and Data
Directories and File Locations
These are the default locations of directories and files.
Directory |
Contents |
---|---|
\Mssql7\Backup |
Default location for backup files |
\Mssql7\Binn1 |
Microsoft Windows NT client and server executable files, online Help files, and DLL files for extended stored procedures |
\Mssql7\Books |
Microsoft SQL Server Books Online files |
\Mssql7\Data1 |
System and sample database files |
\Mssql7\Ftdata1 |
Full-text catalog files |
\Mssql7\DevTools\Include |
OLE DB include (*.h) files used to create programs using ODBC, DB-Library, Open Data Services, SQL-DMO, Embedded SQL for C, and MS DTC |
\Mssql7\DevTools\Lib |
OLE DB library (*.lib) files used to create programs using ODBC, DB-Library, Open Data Services, SQL-DMO, Embedded SQL for C, and MS DTC |
\Mssql7\DevTools\Samples |
Files and examples used by ODBC, DB-Library, Open Data Services, SQL-DMO, Embedded SQL for C, and MS DTC |
\Mssql7\Html 1,2 |
Microsoft Management Console (MMC) and SQL Server HTML files |
\Mssql7\Install |
Scripts run during Setup and the .out files that result from running the Setup scripts |
\Mssql7\Jobs |
Storage location for temporary job output files |
\Mssql7\Log |
Error log files |
\Mssql7\Repldata |
Working directory for replication tasks |
\Mssql7\Upgrade |
Files used for version upgrade from SQL Server version 6.x to SQL Server 7.0 |
1 Do not delete these directories or their contents. You may delete other directories, if necessary; however you may not be able to retrieve any lost functionality or data without uninstalling and reinstalling SQL Server.2 Do not delete or modify any of the .htm files in this directory. They are required for SQL Server Enterprise Manager and other tools to function properly. |
Note The path \Mssql7 is the default path. The path may vary if the default location was changed when SQL Server was installed.
See Also
In This Volume
File Locations
Start Menu Options
Microsoft SQL Server 7.0
SQL Server Setup creates the Microsoft SQL Server 7.0 program group on the Start menu in the Programs group. You may access these applications through the program group:
SQL Server Books Online
Client Network Utility
SQL Server Enterprise Manager
Import and Export Data
MSDTC Administrative Console
Performance Monitor
Profiler
Query Analyzer
readme.txt
Server Network Utility
Service Manager
Uninstall SQL Server 7.0
Microsoft SQL Server-Switch
If you install SQL Server version 7.0 alongside SQL Server 6.x on the same computer, the Setup program removes the Microsoft SQL Server 6.x program group. Because SQL Server 6.x and SQL Server 7.0 cannot be run at the same time, only the program group of the active version will be accessible on the Start menu. The Microsoft SQL Server-Switch program group contains these applications:
Microsoft SQL Server x.x
SQL Server Upgrade Wizard
Uninstall SQL Server 6.x
Microsoft SQL Server x.x is an application to switch to the nonactive version of SQL Server. The name of the application is always the name of the nonactive version of SQL Server.
See Also
In This Volume
Switching Between SQL Server 6.x and SQL Server 7.0
Changing the Character Set, Sort Order, or Unicode Collation
The character set, sort order, and Unicode collation are fundamental to the structure of all Microsoft SQL Server databases. In order to change one or more of these settings, you must rebuild the master and user databases.
Follow these steps to rebuild the databases and maintain their contents.
Back up the definitions of objects you want to preserve.
Use SQL Server Enterprise Manager to create scripts for all of the objects in the user databases and master database.
Export data from the user tables.
Use Microsoft Data Transformation Services (DTS) or the bcp bulk copy utility to unload the user data.
Rebuild the master database.
Run the Rebuildm.exe command prompt utility. You can select new character set, sort order, and Unicode values and rebuild the master database with these new settings.
Create new user databases.
Use SQL Server Enterprise Manager or the CREATE DATABASE statement to re-create the user databases.
Create objects using the scripts created earlier.
Use SQL Server Query Analyzer to run the scripts you created earlier.
Import data into the user tables.
Use DTS or BCP to load data back into the user tables.
See Also
In Other Volumes
"Creating a Database" in Microsoft SQL Server Database Developer's Companion
"Documenting and Scripting Databases" in Microsoft SQL Server Database Developer's Companion
"Importing and Exporting Data" in Microsoft SQL Server Administrator's Companion
"Overview of Data Transformation Services" in Microsoft SQL Server Distributed Data Operations and Replication
"Rebuild Master Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference
Removing SQL Server
You can remove Microsoft SQL Server from your computer using any of these methods:
Run the Add/Remove Programs application in Control Panel.
Run an unattended removal file.
For more information about Sql70rem.bat, see "Unattended Installation" in this volume.
Click Uninstall SQL Server 7.0 on the Microsoft SQL Server 7.0 Start menu.
Important Before removing SQL Server, shut down all applications, including the Windows NT Event Viewer, the Registry editor, all SQL Server applications, and all applications dependent on SQL Server.
Note You may need to delete the \Mssql7 directory manually after SQL Server is uninstalled.
See Also
In This Volume
Unattended Installation
Installing English Query
Microsoft English Query is a development tool designed to give you the ability to create applications that allow users to query a Microsoft SQL Server database in English. For example, users can ask, "How many widgets were sold in Washington last year?" instead of using the SQL statements:
SELECT sum(Orders.Quantity) from Orders, Parts WHERE Orders.State='WA' and Datepart(Orders.Purchase_Date,'Year')='1996' and Parts.PartName='widget' and Orders.Part_ID=Parts.Part_ID
When you install Microsoft English Query, a Start menu item named English Query is created in the Microsoft SQL Server 7.0 program group. It contains these shortcuts:
English Query Help
Microsoft English Query
Readme
For more information, see English Query Help.
Installing OLAP Services
Microsoft SQL Server OLAP Services is a new middle-tier server for online analytical processing (OLAP). OLAP Services includes a powerful server that constructs multidimensional cubes of data for analysis and provides rapid client access to cube information. PivotTable Service, the included OLE DB 2.0 compliant client, is used by Microsoft Excel and applications from other vendors to retrieve multidimensional data from the server and present it to the user.
OLAP Services organizes data from a data warehouse into multidimensional cubes with precalculated summary information to provide rapid answers to complex analytical queries.
Some key features of OLAP Services include:
Unparalleled ease of use provided by the user interface and wizards
A flexible, robust data model for cube definition and storage
Automated solution to the data explosion syndrome that plagues traditional OLAP technologies
Scalable architecture capable of supporting multiple OLAP and data warehouse servers
Intelligent client-server cooperative caching for rapid query response and reduction of network traffic
Widely supported, documented APIs and open architecture to support custom applications
When you install OLAP Services, a Start menu item named OLAP Services is created in the Microsoft SQL Server 7.0 program group. It contains these shortcuts:
MDX Sample Application
OLAP Manager
Product Documentation
Readme