The Complete SQL Server 2000 Software

• Familiarity with SQL Server tools, utilities, interface, and procedures.

Many tools and utilities are available for the SQL Server developer. After SQL Server is installed, you should know how to use these useful utilities to get the most out of SQL Server. After reading the following section, you should have a fair picture of what portion of the product performs the tasks that you want to do. For example, if you need to modify or insert data, you should know that the Query Analyzer and the Enterprise Manager are among your choices.

So far, this chapter has taken a quick look at the SQL Server Query Analyzer and OSQL, but many more useful programs and utilities are provided with the product that should be of great interest to you:

• Books Online

• Client Network utility

• Server Network utility

• Service Manager

• Enterprise Manager

• Profiler

• BCP

• English Query

• Analysis Services

• XML Configuration Tool

These tools each have specific uses and provide intuitive means of accessing the SQL Server environment. A brief explanation of each tool provides you with at least a point of initial discovery into its use.

SQL Server Databases

So, what exactly is a database? A database is similar to a work file folder, which contains information pertaining to related topics. In the same way, a database is a group of files used to store data pertaining to a single business process. Databases are organized in all database servers with components called fields, records, and tables.

A field is a single characteristic, attribute, or property that provides information about an object; for instance, salary or age provides information about an employee. A record is a complete set of all the fields/attributes combined together for a particular object. A record might include the salary, age, phone number, address, and name for a particular employee. A table is a group of all the records. A table might be set up for the employees holding hundreds of records, one record for each employee. A database is a group of all the tables. To continue with this example, a database might be set up for all the tables in the company, including employees, products, customers, and so on.

SQL Server is a relational database management system. A relational database contained within SQL Server is a collection of objects where data and other information are stored in multiple tables. The numerous tables are related in some way, either directly or indirectly via other tables. A relational database contains all database objects, structures, and the raw data pertaining to that database.

Default Databases

Some databases are created automatically when you install SQL Server. The default system databases should not be deleted because they may cause failure to your instance of SQL Server. The two sample databases are the Pubs and Northwind databases and may be removed, because they are there only to show you how a finished database functions.

Master

The Master database is provided to keep your instance of SQL Server functioning. This database records all the system information for the SQL Server instance. The Master database contains all information that is global to the server, including logins, error messages, system configuration, and the list of other databases that exist on the server. The Master database helps in tracking the location of primary files in order to view other user databases.

NOTE

Master of the Server The Master database is crucial to the operations of SQL Server and should be considered as important as the most mission-critical database stored on the server. If it becomes damaged, the server ceases to function.

Msdb

The SQL Server Agent uses the Msdb database to store information about the scheduling of alerts, the definition of jobs, and the recording of the server operators to be contacted when a particular event occurs on the server.

Tempdb

The Tempdb contains all temporarily created stored procedures and tables and is generally used as a work area by SQL Server. Tempdb is where tasks that require memory are performed, such as join and sort operations. The temporary tables and objects created in a SQL Server session are dropped after SQL Server is shut down. Tempdb never saves information permanently. By default, the size of the Tempdb database automatically increases when needed and is restored to its default size (2.5MB) each time SQL Server is started.

Model

The Model database stores a complete template for creating new databases. When you create a new database, SQL Server copies the whole contents of the Model database into the new database you create.

Pubs

The Pubs database is a real-world example of a database serving as a learning tool. The Pubs database is a fictitious publisher's database containing publisher-specific tables and information such as authors and titles. The Pubs database may be dropped, because doing so does not affect the SQL Server environment whatsoever.

Northwind

The Northwind database is the second example database. It is preferred by many Microsoft Access users who are new to SQL Server because it is the same sample database that was provided for Microsoft Access.

WARNING

Northwind Configuration The Northwind database is a poor example of the options to use in a live database. Don't use it as a measure for the options you need for your own database. Before selecting any option, know the impact of the option on the database and client connections as well as any applications-required settings.

Contents of a Database

There are certain objects that are present within a database after it has been created. Many database objects are necessary for a database to function. For example, a table object is the basic object needed to store data; without a table, your database wouldn't get very far. An outline of the different database objects that you can use follows :

• Table . A table is the first thing you create in the database so that data may be stored. Tables may have many fields and records. A table is a data structure that may contain information of interest to users, table creator, or company. Tables, like spreadsheets, are made up of rows and columns .

• View . A view is an object used in displaying a subset of data from a table in different formats. This can be used to ensure security or reduce data redundancy. A view is a stored SQL query. You can assign permissions to a view to enable an administrator to forgo more granular permission assignments at the column level of a table.

• User-Defined Function . A user-defined function is a group of T-SQL statements that may be reused. It is similar to predefined functions, such as ABS() , which returns the absolute value of a number specified. User-defined functions can be created by a database developer and stored as database objects. Functions are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all the function logic.

• Stored Procedure . Stored procedures are collections of T-SQL statements that execute as a single unit. Stored procedures are stored on the server and can execute faster than queries at the client, without any extra overhead.

• Triggers . A trigger is a stored procedure that automatically executes at the invocation of an INSERT , DELETE , or UPDATE . Triggers can be used to validate the data being entered and to enforce data integrity.

• Database roles . Users can be assigned to various database roles that determine what access they have to which database objects. A role is a collection of users and permissions. Members of a role inherit the permissions associated with the role. A user may be a member of many roles, and the permissions are cumulative.

• Database diagrams . Database diagrams are graphically created outlines of how your database is structured. Database diagrams show how tables, and the fields that compose the table, are related.

• Constraints . A constraint is an attribute a column or table can take to restrict what users enter into your database. For example, you may want only unique values to be entered into a certain column. To do this, use a UNIQUE constraint.

• Indexes . An index is a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The Primary Key constraint of a table is automatically indexed.

Each database object has its own properties that make it an important piece of the entire picture. This book looks deep into the SQL Server object structure and looks independently at each part of SQL Server from multiple perspectives.

Client Network Utility

The Client Network utility is used to manage the client net-libraries and define server alias names . It can also be used to set the default options used by DB-Library applications. This tool is not very difficult to use. It can impact the connectivity of client computers accessing SQL Server. For the client and server to run on the same computer, they must be running the same network library. The Client Network utility is used to configure and even possibly change the network library in use on the client.

NOTE

The Client Network Utility This utility is installed in SQL Server by default. It can be installed on other computers if you execute a Network Libraries Only installation, which is done by selecting Connectivity from the Installation Type selection screen.

In some cases, an instance of SQL Server may be configured to listen on alternate network addresses. If this is done, client applications connecting to that instance must explicitly specify the alternate address. Although applications could specify the alternate addresses on each connection request, it is easier to use the Client Network utility to set up an alias specifying the alternate addresses. Applications can then specify the alias name in place of the server network name in their connection requests . The starting screen of the Client Network utility looks like Figure 1.3.

Figure 1.3. The Client Network utility.

When you first run the Client Network utility by going to the Start menu and selecting Programs and Microsoft SQL Server, you will see four tabs: General, Alias, DB-Library, and Network Libraries. Each one is discussed in the following sections.

The General Tab

The General tab includes two boxes labeled Enabled Protocols and Disabled Protocols. The Client may communicate with SQL Server databases over any of the Network Libraries switched on in the Enabled box. If you select a library that is not supported by the server, the Specified SQL Server Not Found error will be reported .

The network libraries or net-libraries all have different properties that can be changed so that the client can communicate effectively.

You also have two arrows below the Enabled box. These boxes set the precedence of the network library. SQL Server tries to talk over the first mentioned net-library and works itself down to the bottom. For instance, if you want to make SQL Server communicate over the Named Pipes library, then you select it and press the Up arrow.

Alias Tab

In many organizations, several SQL Servers run simultaneously and each server is configured with different settings. For instance, one server may be running by having the TCP/IP net-library listen to port 1433. Another may be configured to listen to port 37337. Other servers have different settings for their client connections. In this case, you need to set server aliases. With an alias you have a method of assigning server-specific library configurations. The most common use of a server alias is to specify a particular network library to use to communicate with a specific server. In addition, an alias may be used to communicate with a SQL server that listens on a part other than the default.

The DB-Library Tab

SQL Server can be set up so that data can be retrieved using different tools. You can write T-SQL code by using the OSQL utility or the Query Analyzer, or you can also execute your custom-made programs. DB-Library is an interface dll to SQL Server. You can define applications using VB or C++ to call and use the functions inside the dll. The dll is an API that front-end programmers can access. The DB-Library API has not been enhanced beyond the level of SQL Server version 6.5. All DB-Library applications can work with SQL Server 2000, but only as 6.5-level clients . Features introduced in SQL Server 2000 and SQL Server version 7.0 are not supported for DB-Library applications.

WARNING

Front End Development SQL Server 2000 does not include a programming environment for DB-Library for Microsoft Visual Basic. The existing DB-Library for Visual Basic applications can run against SQL Server 2000, but must be maintained using the software development tools from SQL Server version 6.5. All development of new Visual Basic applications that access SQL Server should use the Visual Basic data APIs such as ActiveX Data Objects (ADOs) and Remote Data Objects (RDOs).

As SQL Server changes, the DB-Library will also be changed, and therefore you will need to upgrade your DB-Library at times. To upgrade, you need to know information regarding the current DB-Library (version, size, date), which can be viewed from the DB-Library tab.

NOTE

Future Implementation DB-Library is going to be de-implemented. OLE-DB and ODBC, in that order, are the preferred interfaces.

Network Libraries Tab

This tab displays the date and version number of all the network library files installed on your local system. Whenever files are outdated , you can upgrade then by installing the most recent service pack. You can check the readme file Microsoft provides with the service pack and compare it with the version numbers on this tab.

Server Network Utility

The Server Network utility works in a way similar to the Client Network utility because it is also used to configure net-libraries on which the server listens. Unlike the Client Network utility, which controls how applications connect to SQL Server, the Server Network utility tells on which network libraries SQL Server is listening.

This utility also contains Winsock Proxy information listed on the bottom of the general tab. By changing these proxy settings, SQL Server can listen for client calls through Microsoft Proxy Server. To do this, you need to check the Enable Winsock proxy check box and then supply the name of the proxy server as well as the port on which to listen.

Service Manager

This tool, shown in Figure 1.4, is quite simple in contrast to the others. The Service Manager is there only to stop, pause, start, and monitor SQL Server services. The easiest way to run the Service Manager is to double-click on its icon in the system tray.

Figure 1.4. The Service Manager, which is used to stop, start, and pause SQL Server services.

The Services drop-down list displays the services that can be controlled. These services include the MSSQLServer service, the Microsoft Search service, the MSDTC service, and the SQL Server Agent service.

You can find some "hidden" functionality in the Service Manager. Click the icon on the top left corner of the form and notice a selection named Options and Connect.

The Verify Service Control Action check box is checked by default. This controls whether an "Are You Sure?" dialog box appears when you stop a service.

The poll interval determines how frequently Service Manager looks for the status of the services that are monitored . The default interval is 5 seconds.

Profiler

After designing and deploying your databases, and giving access to users to modify data, you need to monitor how your server is functioning and make sure it's working the way it's supposed to work.

You can set up traces that monitor anything that happens to the server. These include knowing whether a login failed or succeeded, whether a query was executed, and other such events. Traces can be customized to monitor different angles of SQL Server at different times. The Profiler is shown in Figure 1.5.

Figure 1.5. The SQL Server Profiler, used to monitor the SQL Server environment

Enterprise Manager

The Enterprise Manager is the primary graphical tool that performs many of the administrative tasks needed in SQL Server. With this tool, databases can be created as well as objects contained in them, such as views, tables, and so on. The Enterprise Manager is shown in Figure 1.6.

Figure 1.6. The easy-to-use SQL Server Enterprise Manager.

You use the Enterprise Manager to perform regular backups and restorations of databases. Server and database management and security can be maintained from here. Figure 1.6 shows the first screen of the Enterprise Manager. The Enterprise Manager is really a snap-in to the Microsoft Management Console (MMC). The MMC is a utility that Microsoft uses as an administrative interface for their associated products.

You can see two panes in the Enterprise Manager. The tree pane on the left is where the types of objects are displayed, and the contents pane is where the respective objects of the type selected are displayed.

The Enterprise Manager enables you to perform many of the tasks that require the use of T-SQL code at the click of a button. The Enterprise Manager enables you to easily manage and create databases, tables, indexes, and all other SQL Server objects. The Enterprise Manager enables you to delete and insert records. Using the SQL Server Enterprise Manager is one of the fastest and easiest ways to maintain and create databases and database objects. It is the primary tool used by SQL Server database administrators.

Registering Servers

The Enterprise Manager is an MMC snap-in that enables you to administer multiple SQL Servers from a single interface. Computers running SQL Server can be registered using the NetBIOS computer name, DNS Internet Host Name, or by TCP/IP address. Step by Step 1.1 walks you through the process of registering a server. Registering a server in the Enterprise Manager gives you access to the particular server and all the capabilities that were given to the login ID that was used in the registration process. This enables you to remotely monitor and manage multiple machines throughout the environment.

STEP BY STEP

1.1 Registering a Server Using the Enterprise Manager

1. Open the Enterprise Manager and click on a group. (Right-click if you want to create a new group.)

2. Right-click and choose New SQL Server Registration from the drop-down menu.

3. At the introductory screen, select Next to begin the registration process.

4. Select from the available list of servers. (You may also type in an IP address or other recognizable name not displayed.)

5. Select the type of security account you would like to use for this registration. The security account is used to determine the privileges of the administrating user.

6. Select or create a group to organize the interface and then click Next. The grouping has little practical purpose other than to place the servers to make them easier to manage.

7. Select Finish to complete the process and register the server.

After you have registered, you can change registration properties through the Enterprise Management Console (EMC).

Components of EMC

The Enterprise Management Console (EMC) is a full server and database management snap-in for the MMC. MMC is a common console framework for server and network management applications known as snap-ins. Snap-ins enable administrators to more effectively manage network resources. With several snap-ins placed into a single console it is possible to administer many applications from a single interface.

The EMC provides for administration over the SQL Server security environment, server and database configuration, statistical analysis and management, and provides a complete set of administrative tools. Essentially it is the only tool needed for day-to-day operations of a SQL Server environment.

Console Settings

As you work more with the Enterprise Manager and MMC in general, you will want to customize your administrative toolset and create your own consoles. In the Tools Menu of the console, Options will enable you to configure EMC and maintain your own personal settings. This is quite handy if multiple administrators share the same computer. Use Step by Step 1.2 to explore those options.

STEP BY STEP

1.2 Customizing the Enterprise Management Console

1. From the Tools menu in the EMC, select Options. (If you want, you can set service-by-service polling intervals and configure console security.)

2. To configure user-by-user consoles, you must select the default Read/Store User Independent check box. This enables each administrator to have different console settings.

3. Select OK to close the window.

As you can see on the interface, it is also possible to set up a central server to store all registration information.

Bulk Copy Program

When databases are created in SQL Server, they need to be populated with data. You can certainly populate a database by using the Enterprise Manager or Query Analyzer, but why not use the Bulk Copy program? The main feature of the Bulk Copy program is to import or export data from text files to or from tables into other tables. As the name suggests, the "bulk" copy program is primarily used to shift large amounts of data from one place to another, and so it is used only in this case. The rate at which the bulk copy program transfers data from one place to another is about 2000 rows per second. The BCP.exe is stored in the C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder and is run through the command prompt.