Chapter 22
Running Microsoft SQL Server 6.5


This chapter shows you how to
- Install SQL Server 6.5
- Install SQL Server utilities on a client PC
- Use SQL Enterprise Manager to create database objects
- Use Access 95 and the Upsizing Wizard to import table structures and
data
- Add indexes to tables on an immediate or scheduled basis
- Set up database security
Unlike the other members of the BackOffice Server suite, Microsoft SQL
Server is a mature product with a long development history. Sybase Corp.
released in 1987 the first version of SQL Server, a client/server relational
database management system (RDBMS). In 1988, Sybase, Microsoft, and Ashton-Tate
(the developer of dBASE) codeveloped a version of SQL Server for OS/2,
the operating system of Microsoft's LAN Manager and IBM's LAN Server. Ashton-Tate,
which later was acquired by Borland International, dropped out of the triumvirate,
and Microsoft alone marketed the OS/2 version as Microsoft SQL Server.
Sybase SQL Server for UNIX minicomputers and Microsoft SQL Server for PC-based
systems remained almost indistinguishable through the last OS/2 version,
4.21.
Microsoft and Sybase went their separate ways in 1993, primarily as
a result of Microsoft's port of SQL Server 4.21 to Windows NT as version
4.21a. In 1995, Microsoft introduced SQL Server 6.0 and, in April 1996,
released the current 6.5 version. SQL Server 6.0 and 6.5 are optimized
for Windows NT and include a comprehensive graphical management tool, SQL
Enterprise Manager, for administering SQL Server databases. Like other
members of the BackOffice family, SQL Server runs as a service and uses
Windows NT's security system to control access to SQL Server objects, such
as databases, tables, views, and stored procedures.
This chapter assumes basic familiarity with the terminology of client/server
RDBMSs and their table objects, such as columns (fields), indexes, key
fields, foreign keys, and constraints, as well as basic SQL syntax. The
primary emphasis of the chapter is on installing SQL Server 6.5, installing
and using SQL Enterprise Manager, and establishing basic database security.
Positioning SQL Server in the RDBMS Market
Microsoft's version of SQL Server traditionally has been relegated to
the departmental database category, whereas UNIX RDBMSs (such as
Oracle, Sybase, and Informix) and mainframe products (primarily IBM's DB2)
have enterprise status. The generally accepted definitions of these
two categories is as follows:
- Enterprise RDBMSs support large-scale transaction processing
applications, such as airline reservation systems running on mainframe
and very large UNIX "boxes" (minicomputers). Enterprise RDBMSs
often involve database sizes in the terabyte (1,000G) range. Licensing
costs for enterprise RDBMSs start in the $100,000 range and often are in
the million-dollar class. Users pay substantial amounts for yearly software
maintenance.
- Departmental RDBMSs are dedicated to specific applications (such
as inventory control or manufacturing requirements planning) or functions
(such as finance or marketing). Departmental RDBMSs support database sizes
between about 100G and a terabyte. Departmental RDBMSs can serve the needs
of most smaller firms with sales of under $25 million or so.

Microsoft's aggressive pricing of SQL Server has brought the average
licensing costs for departmental RDBMSs into the $10,000 and less range.
Smaller firms now can start with a single SQL Server 6.5 installation and
10 client PCs with an up-front cost of less than $2,000 for licenses.

Microsoft's goal is to reposition SQL Server from its current departmental
niche to enterprise status while maintaining its rock-bottom price point.
Microsoft is about halfway to its target with SQL Server 6.5. In the meantime,
Microsoft is counting heavily on small- to mid-sized firms to increase
SQL Server's market share. Integration of SQL Server 6.5 with Internet
Information Server 2.0 through the Internet Database Connector, SQL Server's
Web Assistant, and easy connectivity to SQL Server with the members of
Microsoft Office and Visual Basic also contribute to SQL Server's acceptance
for a broad range of database applications.

See "Using the Internet Database Connector" (Ch
20)
See "Using SQL Server Web Assistant to Distribute Activity Reports,"
(Ch 20)


Microsoft RDBMS competitors also are jumping on the Windows NT bandwagon.
Oracle announced in late August 1996 at the Windows NT Internet Solutions
show that the firm is porting Oracle7, Oracle WebServer, application development
tools, and other Oracle RDBMS applications to Windows NT. Oracle says it
also plans to ship two-node and four-node cluster versions of its Parallel
Server by early 1997. Most of the other major players in the client/server
RDBMS market offer downsized Windows NT versions of their UNIX-based products,
often called workgroup servers.

Data warehousing and data marts are two of today's hot
database topics. Data warehousing involves extracting data stored in a
variety of formats within enterprise RDBMSs, as well as legacy network
and hierarchical databases (typified by IBM's VSAM), into separate databases
that provide fast response to user queries. The objective is to separate
ad hoc decision-support activities (queries) from transaction-processing
operations and format the data into a consistent relational structure.
Rollups of mainframe transaction databases are the traditional method
of providing this separation. Rollups create a second database that summarizes
daily, weekly, and/or monthly transactions. A data warehouse stores the
rolled-up data for an entire corporation in a separate set of relational
databases, usually on a UNIX box.
There are several suppliers of RDBMSs that are designed specifically
for data warehousing; Red Brick Systems was one of the first firms to enter
the data warehouse RDBMS business. Oracle offers its Oracle Express Server
for data warehousing. The size of a data warehouse easily can grow into
the terabyte range.
Data marts are smaller-scale versions of data warehouses devoted to
a single department or function, such as sales, finance, or marketing.
Properly designed data marts can be combined to create a distributed data
warehouse. Microsoft is only beginning to stick its toe in the data mart
water. SQL Server 6.5 has added ROLLUP and CUBE statements to Transact-SQL
(SQL Server's flavor of SQL) for summarizing (aggregating) data. Microsoft
recommends SQL Server for use with data marts less than 200G in size having
fewer than 5,000 simultaneous users. These limits, although insufficient
for major-scale data warehouses, are adequate for most of today's data
marts.
Installing SQL Server 6.5
One of the primary advantages of SQL Server over its competitors is
the ease of installation and startup. You can install SQL Server 6.5 in
less than 30 minutes. Before you install SQL Server 6.5 for the first time
or as an upgrade to an earlier version, be sure to do the following:
- Read the Readme.txt file on the distribution CD-ROM for your server's
processor, typically in the \Sql65\I386 folder. The Readme.txt file contains
late-breaking information on SQL Server 6.5 that isn't in the Books Online
documentation. The Readme.txt file also contains details on disk space
requirements for installation and actions you must take when upgrading
from prior versions.
- Provide sufficient free disk space for the installation. Although a
new installation requires about 80M of free space, you should have at least
110M free on the logical drive to which you install SQL Server, so you
can install a master database device of 50M or larger. You can use other
local logical drives to accommodate user databases and transaction logs.

Less free space is required when upgrading SQL Server 4.21a or 6.0 to
version 6.5. You must have free space available in your Master.dat device.
Check the Readme.txt file for the exact disk and device free space requirements
for upgrading.

- Set up your local tape backup drive, if you haven't already done so.
If you're using third-party network backup software, make sure that the
tape drive is accessible from the Windows NT server on which you're installing
SQL Server. (SQL Server's built-in tape backup function won't back up to
a remote tape drive.)

See "Using the Windows NT Server 4.0 Backup Application,"
(Ch 8)

Installing Files from the Distribution CD-ROM
To install SQL Server 6.5 from the distribution CD-ROM, follow these
steps:
- Run Setup.exe from the SQL Server 6.5 folder for your processor type,
usually \Sql65\I386, to start the Setup program.
- When the Welcome dialog appears, click Continue to open the Enter Name
and Organization dialog.
- Complete the Name, Company, and, optionally, the Product ID text boxes
(see fig. 22.1), and then click Continue to open the Verify Name and Company
dialog.
22.1
Entering user and organization names.
- Confirm your entries and click Continue to open the Microsoft SQL Server
6.5 - Options dialog. If this is a new installation, select Install SQL
Server and Utilities (see fig. 22.2). Otherwise, select Upgrade SQL Server.
Click Continue to open the Choose Licensing Mode dialog.
22.2
Specifying a new installation of SQL Server 6.5 and its utility applications.
- Select the Licensing Mode for your installation (see fig. 22.3). The
default is Per Server, which you can change later to Per Seat. Click Add
Licenses to open the New Client Access License dialog.
22.3
Setting the licensing mode for SQL Server.

See "Per-Seat vs. Per-Server Licensing," (Ch
21)

- Type the number of Client Access Licenses you own in the Quantity box,
or use the spin buttons to set the number of licenses (see fig. 22.4).
If you selected Per Seat in step 5, you can enter a note in the Comment
text box that appears when you use Windows NT 4.0's License Manager application.
Click OK to display the Per Server (or Per Seat) Licensing dialog.
22.4
Setting the initial number of Client Access Licenses.

See "Using Windows NT Server 4.0's License Manager," (Ch
21)


If you're installing from the BackOffice Test Platform CD-ROM of the
Microsoft Developer Network Enterprise Subscription, you're entitled to
five simultaneous connections to SQL Server.

- Mark the I Agree That check box (see fig. 22.5) and click OK to add
the Client Access Licenses. Click Continue in the Choose Licensing Mode
dialog to open the SQL Server Installation Path dialog. (You might need
to reconfirm the number of Client Access Licenses at this point.)
22.5
Confirming that you've purchased the number of Client Access Licenses
you added.
- Select the local volume on which to install SQL Server and accept the
default installation folder, unless you have a reason for doing otherwise
(see fig. 22.6). Click Continue to open the MASTER Device Creation dialog.
22.6
Specifying the local disk volume and the installation folder for
SQL Server.
- You can place the master device on a different volume and/or in a different
folder, if you want. It's recommended that you specify a size of at least
50M for the master device (Master.dat file) of a production SQL Server
installation (see fig. 22.7). Click Continue to open the SQL Server Books
Online dialog.
22.7
Setting the volume and folder for the master device.
- Microsoft provides SQL Server's documentation in a searchable Books
Online format, similar to a help file. Unless you have another SQL Server
installation with Books Online installed, select the Install on Hard Disk
option (see fig. 22.8). Click Continue to display the Installation Options
dialog.
22.8
Options for installing the SQL Server 6.5 online documentation.
- Mark the Auto Start SQL Server at Boot Time and Auto Start SQL Executive
at Boot Time check boxes (see fig. 22.9). Autostarting SQL Server is especially
important if you use SQL Server for logging Internet Information Sever
2.0 activity.
22.9
Setting options to start SQL Server and SQL Executive during Windows
NT's boot process.

See "Logging to an ODBC Data Source," (Ch
20)

- The default character set (code page) for SQL Server 6.5 is the ISO
character set. If you want to change the code page, click the Sets button
in the Installation Options dialog to open the Select Character Set dialog
(see fig. 22.10). Choose the code page to use from the Select Character
Set list and click OK to close the dialog.
22.10
Selecting a character set for your databases.

Make sure that you understand the ramifications of using a code page
other than the default ISO character set. If you later must change the
character set, you must re-create all your databases from backups. The
ISO character set has been the standard for SQL Server since version 4.21a.

- The default sort order for SQL Server 6.5 is dictionary order, case-insensitive.
Early versions of SQL Server used case-sensitive sort order. If you must
change the sort order, click the Sorts button in the Installation Options
dialog to open the Select Sort Order dialog (see fig. 22.11). The same
warning that applies to changing code pages also applies to sort order
changes. Select the sort order you want and click OK to close the dialog.
22.11
Selecting a sort order.
- The default network protocol for SQL Server is named pipes, which is
sufficient for most Windows NT Server installations. To change or add network
protocols, click the Networks button in the Installation Options dialog
to open the Select Network Protocols dialog. You can add multiple protocols
by marking the protocol name in the Install/Uninstall Networks list (see
fig. 22.12). After making your selection, click OK to close the dialog.
22.12
Changing or specifying additional network protocols.
- Click Continue in the Installation Options dialog to open the SQL Executive
Log On Account dialog.
- SQL Executive, a service for automating execution of SQL Server processes,
needs a login account to start during the boot process. The default is
the domain Administrator account (see fig. 22.13), which must previously
have been granted Log On as a Service rights. If you don't want to assign
a user account for SQL Executive at this point, select the Install to Log
On as a Local System Account option. Click Continue to begin copying files.
22.13
Specifying the user account for the SQL Executive service.
- Setup copies files to the destination folder you selected earlier in
this process, and then creates the master database and other devices and
objects. When Setup completes, you see the Microsoft Server SQL 6.5 - Completed
dialog. Click Exit to Windows NT.
The Setup process adds a SQL Server 6.5 program group menu to the Programs
menu with the choices shown in figure 22.14. At this point, SQL Server
is installed, but neither SQL Server or SQL Executive is running.
22.14
Microsoft SQL Server 6.5's program group menu choices.
Starting SQL Server and SQL Executive
Before you can configure SQL Enterprise manager for your newly installed
SQL Server system, you must start SQL Server 6.5. To start SQL Server and
SQL Executive after installation without rebooting Windows NT, do the following:
- From the Start menu choose Programs, Microsoft SQL Server 6.5, and
SQL Service Manager to open the SQL Service Manager window.
- The Server drop-down list box displays the name of the local SQL Server.
Select MSSQLServer from the Services drop-down list and double-click Start/Continue
to start SQL Server 6.5 (see fig. 22.15, left).
- Select SQLExecutive from the Services list and double-click Start/Continue
to start SQL Executive (see fig. 22.15, right).
22.15
Starting SQL Server 6.5 and SQL Executive with the SQL Service Manager.

You don't need to start SQL Executive to use SQL Enterprise Manager,
but it's a good idea to start the service at this point to verify that
the service is operable.

Using SQL Enterprise Manager
SQL Enterprise Manager, called Starfighter during its development,
is a graphical management tool for SQL Server 6.x. SQL Enterprise Manager
replaces the SQL Administrator and SQL Object Manager of SQL Server 4.21a.
With very few exceptions, SQL Enterprise Manager lets you perform any administrative
operation on SQL Server 6.5 that can be performed by a Transact-SQL script
(query). You can run SQL Enterprise Manager locally on the server or from
a Windows 95 or Windows NT client. Only ISQL/w, the graphical query manager,
is available for 16-bit Windows clients. Most servers are located in restricted-access
areas, so managing SQL Server 6.5 from a 32-bit client is the most common
practice.
Installing SQL Enterprise Manager on a 32-Bit Client
To install SQL Enterprise Manager on a client running Windows 95 or
Windows NT Workstation, follow these steps:
- Run SQL Server 6.5's Setup.exe from the distribution CD-ROM, either
from the client's CD-ROM drive or from a shared CD-ROM drive. The Setup
program automatically recognizes that it's running on a client.
- After Setup starts, click Continue to open the Install/Remove Client
Utilities dialog (see fig. 22.16). With the Install Client Utilities option
selected, click Continue to open the Install Client Utilities dialog.
22.16
Starting installation of the SQL Server utilities on a client PC.
- Specify the local Drive and Directory in which to install the utilities,
and then mark the Utilities to Be Installed check boxes to specify the
applications you want (see fig. 22.17). In most cases, ISQL/w, SQL Enterprise
Manager, and SQL Security Manager suffice for remote administration. Click
Continue to install the client files.
22.17
Selecting the SQL Server client utilities to install.
- After Setup copies the files to your local drive, the Microsoft SQL
Server 6.5 - Completed dialog appears. Setup makes changes to the path,
so you must reboot the client to make full use of all the utilities.
The examples in this chapter use SQL Enterprise Manager and SQL Security
Manager running on a Windows 95 client.
Registering Servers
Before you can use SQL Enterprise Manager with your newly installed
server and any other SQL Server 6.x server(s) previously installed, you
must register the server(s) with SQL Enterprise Manager by following these
steps:
- From the Start menu choose Programs, Microsoft SQL Server 6.5 Utilities,
and SQL Enterprise Manager. Click OK to close the Tip of the Day dialog,
if it appears.
- If you're running SQL Enterprise Manager for the first time, the Register
Server dialog appears automatically. Otherwise, choose
Register
Server from the Server menu to open the Register Server
dialog.
- In the Server combo box, type the name of the Windows NT server on
which the SQL Server you want to register resides. If you installed SQL
Server for integrated or mixed security (Trusted Connection), accept the
default Use Trusted Connection option (see fig. 22.18). Otherwise, select
Use Standard Security and type your administrator account (usually sa,
the default account) in the Login ID text box and your password (empty
if you haven't yet set a password for the sa account) in the Password text
box.
22.18
Registering a SQL 6.5 server with SQL Enterprise Manager.
- If you want to create a server group for management purposes and add
the new server to the group, click Groups to display the Manage Server
Groups dialog. Type the group name in the Name text box, and then select
whether you want a Top Level Group or a Sub-Group of the default SQL 6.5
group (see fig. 22.19). Click Add, and then click Close to return to the
Register Server dialog.
22.19
Creating a subgroup of the SQL 6.5 server group.
- Click Register, and then click Close to return to the SQL Enterprise
Manager with your new server added to the Server Manager window (see fig.
22.20).
22.20
SQL Enterprise Manager with the new server registered.
- Click the + icon next to the entry for your new server, to display
the SQL Server objects and object collections installed during the SQL
Server Setup process (see fig. 22.21). You can expand any entry that displays
the + icon.
22.21
Server Manager's window, with the server and Database Devices entries
expanded.
- Repeat steps 2, 3, and 5 to register any additional servers in your
domain or trusting domain(s).
- To view or change the configuration of a server, right-click the server
entry and choose
Configuration from the popup menu to
open the default Server Options page of the Server Configuration/Options
property sheet (see fig 22.22). You use the Configuration page to establish
default values and to tune SQL Server parameters for optimum performance.
If you plan to use SQL Server replication and this server will act as a
publishing and/or distribution server, set the memory value to a minimum
of 8,192 pages (see fig. 22.23). Click Cancel to close the property sheet.
22.22
The Server Options page of the Server Configuration/Options property
sheet.
22.23
The Configuration page, displaying default values and tuning parameters
for SQL Server 6.5.

You can configure SQL Mail and SQL Executive by right-clicking the entries
in the Server Manager window and choosing Configure to display a Configuration
dialog or property sheet.

Specifying and Testing Backup Tape Devices
Only the diskdump backup device is installed by Setup. To enable and
test your Windows NT Server backup tape drive for use with SQL Server 6.5's
backup feature, follow these steps:
- Right-click the Backup Devices item in the Server Manager window and
choose
New Backup Device from the popup menu to open the
New Backup Device dialog.
- Type a description of the drive in the Name text box and select Tape
Backup Device. Accept the default \\.\TAPE0 entry in the Location text
box; TAPE0 is the device name Windows NT assigns to the first tape drive
of a server (see fig. 22.24). If you use a separate tape drive to back
up SQL Server, type the device designator (such as \\.\TAPE1) in the Location
text box. Mark the Skip Headers check box if you don't want to assign an
ANSI header number to the backup tape. Click Create to add the new backup
device.
22.24
Adding a tape backup device to SQL Server 6.5.
- Expand the Backup Devices entry in the Server Manager window to display
the new tape backup device (see fig. 22.25).
22.25
The new tape backup device added to Server Manager's Backup Devices
list.
- Insert an inactive backup tape in your tape drive and use Windows NT's
Backup tool to erase the tape. You must run the Backup tool from the server,
unless you have third-party backup software.
- Right-click your tape backup device entry in Server Manager's window
and select Re
store from the popup menu. In the Database
Backup/Restore dialog, click the Backup tab, if necessary. Select the pubs
demonstration database from the Database Backup drop-down list and your
tape backup device from the Backup Devices list. Mark the Initialize Device
check box and accept the No Expiration Date option (see fig. 22.26). Click
Backup Now to open the Backup Volume Labels dialog.
22.26
Setting backup parameters for a test backup of the pubs sample database.
- Accept the default volume label, or type a six-character Volume number
in the Backup Volume Labels dialog (see fig. 22.27). Then click OK to back
up the database.
22.27
Assigning an ANSI volume label to the backup tape.
- A Backup Progress dialog confirms the backup operation, and then a
message box indicates that backup is complete. Click OK to return to the
Database Backup/Restore dialog.
- Click the Restore tab and select pubs from the Database list. After
the drive searches the tape and finds the pubs database backup, the Restore
page looks like figure 22.28. Click the Restore Now button to restore the
database. A Restore Progress dialog and message box confirm success of
the restore operation. Click OK twice to close the message box and the
Database Backup/Restore dialog.
22.28
The Restore page displaying the parameters for restoring the pubs
database from the backup tape.
Creating and Managing Database Devices
Setup installs default database devices (master, MSDBData, and MSDBLog)
and databases (msdb, pubs, and tempdb). As a general rule, you don't use
existing database devices for user databases. A database device is
a file with the .dat extension in which SQL Server stores databases and/or
log data.
Adding user databases involves the following basic steps:
- Create a new database device of the appropriate size for the database.
- If the database is to be used for transaction processing, also create
a log device.
- Add the table(s) that comprise the database and, if applicable, import
existing data to the tables.
- Assign appropriate permissions to users of the database.
The following sections describe how to use SQL Enterprise Manager to
create a new database device and database, and then use Access 95's Upsizing
Wizard to create and populate the tables with existing data from a copy
of Access 95's Northwind.mdb sample database. The advantage of using Access
95's Upsizing Wizard is that the wizard automatically creates the tables,
indexes, and triggers to maintain referential integrity automatically.
Creating a New Database Device
To create a new SQL Server 6.5 database device, follow these steps:
- In SQL Enterprise Manager, select the server in the Manage Servers
window, and then choose Database De
vices from the Manage
menu to open the Devices window. The default database devices created by
the Setup program appear as shown in figure 22.29.
22.29
The default database devices created by SQL Server 6.5's Setup program.
- Click the New Device button (at the left on the second toolbar), or
choose
New Device from the File menu
to open the New Database Device dialog.
- Type the name of the device in the Name text box-in this example, Northwind.
As you type the name, the default path and file name appear in the Location
text box. Select the logical drive on which to create Northwind.dat from
the Location drop-down list. This is an example database, so don't mark
the Default Device check box.
- Type the initial size of the device in the Size text box, or use the
slider to set the size (see fig. 22.30). 8M is more than adequate for Northwind
data and the temporary files created during the export process. Click Create
Now to create the device file.
22.30
Setting the location and size of a new database device.
- Click OK when a message box informs you that the device is created.
Your new device appears in the Devices window. The brown bar (the darker
area in fig. 22.31) indicates that the entire device is available to contain
the database.
22.31
The new database device added to the Devices window.
- From the
Manage menu choose Databases
to open the Databases window, which displays the default databases installed
by Setup (see fig. 22.32).
22.32
Four of the databases installed by SQL Server 6.5's Setup program.
- Click the New Database button (at the left on the second toolbar),
or choose
New Database from the File
menu to open the New Database dialog.
- Type the name of the database-nwind for this example-in the Name text
box and select Northwind in the Data Device drop-down list. (Traditionally,
SQL Server databases use lowercase names.) Don't mark the Create for Load
check box, which is used when loading a database from a backup. For the
moment, you also don't want to specify a log device. By default, the size
of the database is equal to the size of the database device; set the database
size to 5M (see fig. 22.33).
22.33
Setting the properties of a new database.
- Click the Create Now button to create the database. The new nwind database
appears in the Databases window (see fig. 22.34).
22.34
A new database, nwind, added to the database device.
Importing Table Structures and Data
You must have Access 95 and the Access 95 Upsizing Wizard to import
the data from a copy of Northwind.mdb into the SQL Server database. You
can download a free copy of the Upsizing Wizard (2.4M, which expands to
7.8M) from http://www.microsoft.com/accessdev/accinfo/accinfo.htm.
Use Access 95's Add-In Manager to add the Upsizing Wizard to Access's Tools,
Add-Ins menu. Compact Northwind.mdb into another database,
such as Upsize Northwind.mdb, before starting the upsizing process.
To upsize the copy of Northwind.mdb to SQL Server, follow these steps:
- Launch Access 95 (if necessary), open the Upsize Northwind database,
and from the
Tools menu choose Add-ins
and Upsize to SQL Server to open the Upsizing Wizard (see
fig. 22.35).
22.35
Selecting use of the newly created database in the first dialog of
the Access Upsizing Wizard.
- With the Use Existing Database option selected, click Next to display
the SQL Data Sources dialog, and then click the New button to open the
Add Data Source dialog.
- Select SQL Server from the Installed ODBC Drivers list (see fig. 22.36)
and click OK to open the ODBC SQL Server Setup dialog. Click the Options
button to expand the dialog.
22.36
Selecting the SQL Server ODBC 2.5 driver for a new ODBC data source.
- Type the name of the ODBC data source in the Data Source Name text
box, provide an option description of the data source, type the name of
the SQL Server in the Server combo box, fill in the Database Name text
box, and clear the Convert OEM to ANSI Characters check box (see fig. 22.37).
Click OK to close the dialog and return to the SQL Data Sources dialog.
22.37
Setting the property values of the new ODBC data source.
- Select the new data source in Select Data Source list (see fig. 22.38)
and click OK to open the SQL Server Login dialog.
22.38
Selecting the new ODBC data source for the upsizing operation.
- Type your SQL Server login ID-usually sa with no password, at this
point. Click the Options button to expand the dialog (see fig. 22.39).
Click OK to open the next Upsizing Wizard dialog.
22.39
Entering the login ID and password for the data source.
- Click the >> button to copy all the entries from the Available
Tables list to the Export to SQL Server list (see fig. 22.40). Click Next
to continue.
22.40
Selecting all the tables in the Access database for export to SQL
Server.
- Mark the Indexes, Validation Rules, Defaults, and Table Relationships
check boxes; then select the Use triggers option to maintain referential
integrity. If you choose the Use DRI (Declarative Referential Integrity)
option, you lose cascading deletions.
- Select Yes, Let Wizard Decide whether to add timestamp fields to tables,
and mark the Attach Newly Created SQL Tables (to your Access database)
and Save Password and User ID with Linked Tables check boxes (see fig.
22.41). Click Next if you want the option of having the Wizard prepare
an Access upsizing report; otherwise click Finish (see fig. 22.42).
22.41
Specifying parameters for the upsizing operation.
22.42
Requesting the wizard to prepare an upsizing report.
- The wizard provides a progress bar that reports the wizard's steps.
When upsizing is complete, the attached tables appear as shown in figure
22.43. Tables attached by ODBC are identified by an arrow and a globe icon;
the Access source tables are renamed to TableName_local. Click OK
to terminate the wizard.
22.43
New references to SQL Server tables attached by the Upsize Northwind
ODBC data source.
You can test the performance of the attached tables by opening any of
the Access forms in the database, such as the Customer Orders form.

If you don't have Access 95 or the Upsizing Wizard, you can use the
pubs demonstration database in the examples of the sections that follow.
Choose one of the primary pubs tables, such as authors. The pubs database
doesn't include demonstration triggers.

Working with SQL Tables, Indexes, Tasks, and Triggers
Importing table structures and data from an existing Access database
provides tables that you can use to gain familiarity with the table-related
features of SQL Enterprise Manager. To work with the tables you imported
in the preceding section, follow these steps:
- Select your new database, nwind, in the Server Manager window; then
choose
Tables from the Manage menu to
open the Manage Tables window. The default <new> entry in the Table
list lets you add a new table to the database and specify the properties
of each column.
- Select one of the imported tables, such as Orders, from the Table list.
The properties of each column (field) of the table appears in the list
(see fig. 22.44).
22.44
Properties of the first 12 columns (fields) of the Northwind Orders
table.
- Click the Advanced Features button (with the + icon) of the second
toolbar to open a set of properties pages that show Primary Key/Identity,
Foreign Keys, Unique Constraints, and Check Constraints, if any. For the
Orders table, only a Primary Key and Identity Column are defined (see fig.
22.45). Click the Advanced Features button again to return to the column
list.
22.45
Displaying Primary Key/Identity properties with the Advanced Features
option of the Manage Tables window.
- To add an index to a column, such as RequiredDate, choose
Indexes
from the Manage menu to open the Manage Indexes dialog.
- Select (New Index) in the in the Index combo box, and then type an
index name, such as RequiredDate, in the Index text box. Select the RequiredDate
column in the Available Columns in Table list and click the Add button
to add the column to the Columns in Index (Key) list (see fig. 22.46).
Click the Build button to start the indexing process.
22.46
Setting the properties of a new index on an existing table.
- In the Index Build message box (see fig. 22.47), click the Schedule
As Task button to open the Schedule Index Build dialog.

If you have large-scale, low-priority tasks that might affect the performance
of your database while in use, scheduling the task for the middle of the
night or on a weekend is a good strategy.

22.47
Choosing whether to execute an indexing task immediately or at a
later time.
- You have the option of running the task Immediately, One Time, or as
a Recurring task (see fig. 22.48). For this example, select Immediately
and click OK. Click OK when the Run Task message box appears (see fig.
22.49).
22.48
Selecting scheduling options for the indexing task.
22.49
Confirmation of the scheduling of your task.
- To view the Task History dialog described in the Run Task message box,
choose Scheduled
Tasks from the Server
menu to open the Task List page of the Manage Scheduled Tasks window (see
fig. 22.50). This page displays all scheduled tasks, regardless of status.
22.50
Viewing scheduled tasks on the Task List page of the Manage Scheduled
Tasks window.
- Double-click the task to open the Edit Task dialog, which displays
the task properties (see fig. 22.51). Click the History button to display
the Task History dialog (see fig. 22.52). Click Close in the Task History
dialog and then click Cancel in the Edit Task dialog to return to SQL Enterprise
Manager.
22.51
The Edit Task dialog, an intermediary on the way to viewing task
history.
22.52
Checking the execution of the task in the Task History dialog.
Viewing Triggers
The traditional method of enforcing referential integrity of SQL Server
databases is by the use of triggers in transaction processing operations.
Triggers are stored procedures that execute when a client application
requests an INSERT, UPDATE, or DELETE operation on a table. The Transact-SQL
code of the trigger performs one or more tests, usually on one or more
related tables. If the test succeeds, the operation executes; otherwise,
the trigger cancels the operation and posts an error message.
To view a typical pair of triggers that the Access Upsizing Wizard creates
to maintain referential integrity, follow these steps:
- With the nwind database selected in Server Manager's window, choose
Tri
ggers from the Manage menu to open
the Manage Triggers window. You edit existing triggers or create new triggers
in this window.
- Select the Orders table from the Table list.
- Click the icon to open the Trigger list and select Orders_ITrig (INSERT
trigger on the Orders table). The Transact-SQL code for the trigger appears
in the window (see fig. 22.53). The CREATE TRIGGER name ON tablename FOR
INSERT AS statement specifies an INSERT trigger.
22.53
Part of the Transact-SQL code for an INSERT trigger.
- Open the Trigger list and select Order_UTrig (UPDATE trigger on the
Orders table). The CREATE TRIGGER name ON tablename FOR UPDATE AS statement
specifies an UPDATE trigger.

A single table supports only three triggers, one each for INSERT, UPDATE,
and DELETE operations. Triggers are self-contained stored procedures that
don't accept or return parameter values. The only output from a trigger
is an error message posted by a RAISERROR statement.

Viewing Standard Stored Procedures
Many of the functions performed by SQL Enterprise Manager use stored
procedures, which are precompiled queries that accept and return parameter
values. The master database holds stored procedures that can be executed
against any database on the server. Stored procedures specific to a database
are stored with the tables of the database. Traditionally, SQL Server names
stored procedures with an sp_ prefix, as in sp_addserver.
To view example stored procedures in the master database, proceed as
follows:
- Select the master database in the Server Manager window, and then choose
Stored
Procedures from the Manage menu
to open the Manage Stored Procedures window.
- Select one of the stored procedures in the Procedures list, such as
sp_addserver, to display the Transact-SQL code for the procedure (see fig.
22.54).
22.54
Part of the Transact-SQL code for the sp_addserver stored procedure.
Parameters of stored procedures are identified by an ampersand (@) prefix,
as in @server, followed by the SQL data type and size, if applicable, such
as varchar(30). You also can create local variables with the declare @varname
datatype statement.
Executing Queries
SQL Enterprise Manager includes the equivalent of the ISQL/w graphical
query tool. You can write your own queries or execute Transact-SQL scripts
(queries) stored as *.sql files. To execute a query, follow these steps:
- Choose SQL
Query Tool from the Tools
menu to open the Query window.
- Select the database from the DB list.
- Type the query in the Queries page (see fig. 22.55).
22.55
A SELECT query against the nwind database.
- Choose
Execute from the Query menu,
or click the green arrow button at the right of the second toolbar.
- Select the Results page to display the result of your query (see fig.
22.56). SELECT queries return the query result set; INSERT, UPDATE, and
DELETE queries return the number of rows affected.
22.56
The result set of the query shown in figure 22.55.
Setting Up Transaction Logging
Transaction logs provide a history of all changes made to the tables
of the database by INSERT, UPDATE, and DELETE operations. The purpose of
a transaction log is to provide the means to reconstruct a database to
its state immediately before a catastrophic failure that requires restoring
data from the last backup. The basic backup and restore sequence with a
transaction log is as follows:
- Immediately after making a full backup of the database, the transaction
log is deleted (dumped).
- In the event of a failure of the database file, the database is restored
from the last backup.
- The transaction log is executed against the database to bring the content
of the database tables to their state immediately before the failure.
You establish transaction logging by specifying a log device for the
database when you create the new database. Transaction logging is enabled
by default, and the transaction log shares space with the data in the database.
A transaction log in the same database or stored on the same physical device
provides no protection against failure of the drive that stores the file
for the database device. You can't change the log device after the database
is created.
To determine the log device for a database, double-click the database
entry in Server Manager to display the Database page of the Edit Database
property sheet. The nwind database, created earlier in the "Importing
Table Structures and Data" section, stores its log with the data (see
fig. 22.57). In this case, there's no advantage to maintaining a transaction
log, so you can click Truncate to recover the log space used. To prevent
further log entries, display the Options page and mark the Truncate Log
on Checkpoint check box (see fig. 22.58). Click OK to close the property
sheet.
22.57
The Database page of the Edit Database property sheet for the nwind
database.
22.58
Preventing transaction logging with the Truncate Log on Checkpoint
option.
To create a database with a transaction log, follow these steps:
- In the Manage Devices window, create a new database device of the appropriate
size. (Refer to the earlier section "Creating and Managing Database
Devices.")
- Create another database device for the transaction log on a different
local fixed-disk drive.
- Create a new database, selecting the database device in the Data Device
combo list and the database device for the transaction log in the Log Device
combo list (see fig. 22.59).
22.59
Specifying a transaction log device for a new database.
- Click Create Now to create the database and the transaction log.
Establishing Database Permissions
SQL Server offers the three following types of user login security:
- Standard security requires SQL Server users to specify a login
ID and a password to gain access to the server. You set up individual SQL
Server accounts for each server user in the Manage Logins window of SQL
Enterprise Manager.
- Integrated security uses Windows NT security to authenticate
SQL Server users with their Windows NT login IDs and passwords. Integrated
security is limited to clients that connect with the default named pipes
protocol. You use SQL Security Manager to assign group logins to the server.
- Mixed security lets SQL Server installations that are set up
to accept multiple network protocols take advantage of integrated security
for clients by using named pipes. Users of clients connecting by a protocol
other than named pipes must supply a login ID and password.
Integrated security is the most common choice for SQL Server 6.5 with
clients running Windows 95 or Windows NT Workstation. Regardless of the
type of security you choose, the default system administrator account,
sa, has all permissions for all databases and is the default owner (dbo,
database owner) for all SQL Server objects.

In the examples of this chapter, the sa account uses the default empty
password. SQL Server's sa account and Microsoft Access's default Admin
account have similar authority. Be sure to assign a password to the sa
account before using SQL Server for production applications. To change
the sa password in SQL Enterprise Manager, choose Logins
from the Manage menu, select sa in the Login Name list,
type a password in the Password text box, and click the Modify button.

Using SQL Security Manager to Assign Group Accounts
You can assign all users in a specified group access to one or more
SQL Server databases with the SQL Security Manager. Ordinarily, you employ
User Manager for Domains to create a specific Windows NT group for access
to each database, and then add to the group the users who need database
access. In the following example, the Domain Users group is used for simplicity.

See "Using the Group Management Wizard," (Ch
12)

To set up a SQL Server user group from Domain Users, follow these steps:
- From the Start menu choose Programs, Microsoft SQL Server 6.5 Utilities,
and SQL Security Manager to launch SQL Security Manager.
- In the Connect Server dialog, type the name of the server, your login
ID, and password, if necessary (see fig. 22.60). Click Connect to connect
to the server.
22.60
Completing the Connect Server dialog to open SQL Security Manager.
- To view administrators with sa (system administrator) status, choose
Sa Privilege from the View menu. Double-click
the group names to expand the list and display login IDs with sa privileges
(see fig. 22.61).
22.61
Displaying users with system administrator (sa) privileges.
- From the
View menu choose User Privilege.
The No Accounts Have Been Granted User Authority message appears.
- Choose
Grant New from the Security
menu to open the Grant User Privilege dialog.
- Select the Groups on Default Domain option and select the database
user group in the Grant Privilege list.
- Mark the Add Login IDs for Group Members and Add Users to Database
check boxes. Select the database for the group from the drop-down list
(see fig. 22.62). Click the Grant button to add the users.
22.62
Adding Domain Users as the Domain$Users group of SQL Server.

SQL Server doesn't permit spaces in names of objects, including names
of groups. SQL Server automatically replaces spaces in group names with
dollar signs ($).

- As the users are added, the number of Login IDs, Users, and Groups
appear in the Adding SQL Server Login IDs/Users dialog (see fig. 22.63).
22.63
A summary of users and groups added to the Domain$Users group.
- If errors occur during the login process, click Error Detail to expand
the dialog and check the source of the errors (see fig. 22.64).
22.64
Displaying errors during the addition of SQL Server users.
- Click Done to close the dialog and display the added user accounts
(see fig. 22.65).
22.65
The new user accounts displayed in the SQL Security Manager window.
- Double-click one of the user accounts to display the Account Detail
dialog. If you want to add another database for the user, select the database
in the Available Databases list and click Add. Select the default database
for the user in the Databases Currently Defined In list and click the Set
Default database (see fig. 22.66).
22.66
The Account Detail dialog for a newly added user.
Viewing Logins and Setting Permissions in SQL Enterprise Manager
SQL Enterprise Manager includes complete facilities for managing SQL
Server user accounts (logins) and database permissions for each account.
To view the logins you added from the Windows NT Domain Users group in
the preceding section and set specific user permissions for database objects,
follow these steps:
- Close and relaunch SQL Enterprise Manager, and then expand the Logins
item of Server Manager's window for the server to which the users were
added. Each user login, together with default users added by SQL Server,
appears as shown in figure 22.67.
22.67
Logins added to the OAKLEAF3 server from the Domain$Users group.
- Double-click the user item to display the Manage Logins dialog for
the user (see fig. 22.68). You can add or remove permissions for databases
by clicking cells of the Permit column, and set one default database by
clicking a cell in the Default column. Click Close to close the dialog.
22.68
Displaying the details of the users account in the Manage Logins
dialog.
- Double-click the database in Server Manager to display the Edit Database
dialog, and then display the Permissions page. By default, users in the
Domain$Users group don't have permissions to modify the database. If you
want to grant the Domain$Users group permission to create a view, click
the Create View button (see fig. 22.69). When you click OK, the permission
is granted to all members of the group.
22.69
Adding Create View permission for the Domain$Users group.
To drop a user group from a database, select the database item in Server
Manger, and then choose Groups from the Manage
menu to open the Manage Groups dialog. Select the group you want to drop
in the Group drop-down list (see fig. 22.70), and then click the Drop button.
Confirm in the message box that you want to drop the group.
22.70
Selecting a user group to drop in the Manage Groups dialog.
From Here...
This chapter covered the basic operations involved in installing SQL
Server 6.5 under Windows NT Server 4.0. Instructions for installing the
SQL Server 6.5 Utilities on clients running Windows 95 and Windows NT Workstation
4.0 also were given. The chapter provided examples of creating new database
devices, databases, and importing table structures and data to SQL Server
databases. The chapter closed with a brief description of SQL Server's
integrated security features.
The following chapters contain material related to the content of this
chapter:
- Chapter 12, "Managing User and Group
Accounts," describes how to use Windows NT Server 4.0's User Manager
for Domains, take advantage of the new Add User Accounts and Group Management
wizards, and utilize the built-in user groups of Windows NT.
- Chapter 20, "Administering Intranet and
World Wide Web Sites," describes how to use a SQL Server 6.5 database
for logging the activity on your intranet or Internet site.
- Chapter 24, "Administering Clients with
System Management Server," covers the basics of planning, administration,
and management for Microsoft SMS 1.x. SMS 1.x uses SQL Server 6.5 to store
all of its client management information.


DISCLAIMER
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.