Chapter 22

Running Microsoft SQL Server 6.5

Previous chapterNext chapterContents


This chapter shows you how to

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:

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.

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:

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.

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:

  1. Run Setup.exe from the SQL Server 6.5 folder for your processor type, usually \Sql65\I386, to start the Setup program.
  2. When the Welcome dialog appears, click Continue to open the Enter Name and Organization dialog.
  3. 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.

  4. 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.

  5. 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)

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 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)

  12. 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.

  13. 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.

  14. 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.

  15. Click Continue in the Installation Options dialog to open the SQL Executive Log On Account dialog.
  16. 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.

  17. 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:

  1. From the Start menu choose Programs, Microsoft SQL Server 6.5, and SQL Service Manager to open the SQL Service Manager window.
  2. 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).
  3. 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:

  1. 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.
  2. 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.

  3. 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.

  4. 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:

  1. 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.
  2. 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.
  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. Repeat steps 2, 3, and 5 to register any additional servers in your domain or trusting domain(s).
  8. 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:

  1. 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.
  2. 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.

  3. 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.

  4. 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.
  5. Right-click your tape backup device entry in Server Manager's window and select Restore 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.

  6. 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.

  7. 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.
  8. 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:

  1. Create a new database device of the appropriate size for the database.
  2. If the database is to be used for transaction processing, also create a log device.
  3. Add the table(s) that comprise the database and, if applicable, import existing data to the tables.
  4. 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:

  1. In SQL Enterprise Manager, select the server in the Manage Servers window, and then choose Database Devices 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.

  2. 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.
  3. 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.
  4. 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.

  5. 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.

  6. 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.

  7. 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.
  8. 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.

  9. 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:

  1. 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.

  2. 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.
  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.
  9. 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.

  10. 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:

  1. 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.
  2. 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.

  3. 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.

  4. To add an index to a column, such as RequiredDate, choose Indexes from the Manage menu to open the Manage Indexes dialog.
  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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:

  1. With the nwind database selected in Server Manager's window, choose Triggers from the Manage menu to open the Manage Triggers window. You edit existing triggers or create new triggers in this window.
  2. Select the Orders table from the Table list.
  3. 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.

  4. 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:

  1. 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.
  2. 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:

  1. Choose SQL Query Tool from the Tools menu to open the Query window.
  2. Select the database from the DB list.
  3. Type the query in the Queries page (see fig. 22.55).


    22.55

    A SELECT query against the nwind database.

  4. Choose Execute from the Query menu, or click the green arrow button at the right of the second toolbar.
  5. 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:

  1. Immediately after making a full backup of the database, the transaction log is deleted (dumped).
  2. In the event of a failure of the database file, the database is restored from the last backup.
  3. 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:

  1. In the Manage Devices window, create a new database device of the appropriate size. (Refer to the earlier section "Creating and Managing Database Devices.")
  2. Create another database device for the transaction log on a different local fixed-disk drive.
  3. 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.

  4. 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:

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:

  1. From the Start menu choose Programs, Microsoft SQL Server 6.5 Utilities, and SQL Security Manager to launch SQL Security Manager.
  2. 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.

  3. 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.

  4. From the View menu choose User Privilege. The No Accounts Have Been Granted User Authority message appears.
  5. Choose Grant New from the Security menu to open the Grant User Privilege dialog.
  6. Select the Groups on Default Domain option and select the database user group in the Grant Privilege list.
  7. 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 ($).

  8. 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.

  9. 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.

  10. 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.

  11. 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:

  1. 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.

  2. 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.

  3. 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:


Previous chapterNext chapterContents