- Shopping Bag ( 0 items )
Ships from: Fort Worth, TX
Usually ships in 1-2 business days
Ships from: Appleton, WI
Usually ships in 1-2 business days
Ships from: Chatham, NJ
Usually ships in 1-2 business days
Ships from: acton, MA
Usually ships in 1-2 business days
Part One of this book provides the reader with a roadmap for exploring the Microsoft SQL Server features that support real-world application development. This includes examining the business context of the product, the SQL Server environment, distributed application development capabilities, and enhanced functionality in SQL Server 7 over earlier versions of the product.
Chapter 1 introduces the reader to the SQL Server environment and provides a quick start for readers learning SQL Server or who want to learn about some of the inherent improvements in this powerful product. The reader is also assisted in installing SQL Server and taking a first tour of the product and its new features.
Chapter 2 examines 2-tier, 3-tier, and n-tier application architecture. COM, COM+, DCOM, ActiveX, and the Microsoft Transaction Server (MTS) are also discussed in this chapter. This technology forms the infrastructure of n-tier applications that are capable of scaling to thousands of concurrent users.
Chapter 3 is a primer that provides a quick overview of common SQL Server functions and shows how to use them to build a sample application. The reader is introduced to database creation and manipulation commands within the SQL Server environment that are accessible through Transact-SQL, wizards, and GUI-based utilities.
Chapter 4 is the conclusion of the primer that began in the previous chapter. It explores more complex database objects such as stored procedures, triggers, views, and table joins. System help procedures are also covered.
Chapter 5 examines methodologies, techniques, standards, and approaches for building n-tier database-oriented applications using SQL Server and COM.
Microsoft SQL Server is a database server that supports applications in a variety of environments, including n-tier client/ server, data warehousing, Internet, and object-oriented environments. SQL Server offers data services to local and remote client platforms. As shown in Figure 1.1, clients residing in several locations issue service requests to SQL Server and then receive responses in return. Essentially, applications on client platforms issue requests to database servers through an internal or external network. The database server inspects the security access of the requester, inspects the syntax of the request, parses it, optionally optimizes the execution of the request (if an execution plan does not already exist), and then proceeds to execute it. The database server returns a response to the client platform either in the form of a message indicating that some work was performed or in some cases not performed to any data that satisfies the request. Responses from database servers are referred to as result sets. As the figure shows, the SQL Server product and the related databases generally reside on the same platform. Client platforms communicate with SQL Server over a local area network (LAN) or a wide area network (WAN). It is also possible to install several SQL Server engines on the same platform or have them installed on different geographically distributed platforms. Applications on a client platform can communicate with any of the database servers in the configuration. Database servers can also pass requests to one another using "remote procedure calls" or messaging middleware products. Each SQL Server is identified by a specific name that is selected by the user( s) at installation time.
The Road Map
For the purposes of classification, it is useful to divide the substantial features incorporated into Microsoft SQL Server and the surrounding environment into categories that include the operating environment and storage architecture, development environment, engine architecture, enhanced services, features and add-ons, and client platform services. These categories are described in Table 1.1.
What You Get
After installing Microsoft SQL Server you have access to the tools shown in Table 1.2 through the main Windows menu under Start-Programs. These are graphical tools that are far simpler and more efficient to use than traditional text-based tools for accessing the database server environment.
What's New in SQL Server 7
Microsoft SQL Server release 7 supports larger databases, integrates with more Microsoft products aimed at supporting the enterprise, such as Microsoft Transaction Server (MTS) and OLE DB, and supports n-tier application development. The internal architecture of SQL Server 7 is object oriented and component based. SQL Server 6.5 was a significant enhancement over SQL Server 6, which was a tremendous step up from SQL Server 4. Until the release of SQL Server 6.5, Microsoft SQL Server shared the same core engine with Sybase SQL Server. However, the functionality of version 6.5 diverged noticeably away from Sybase SQL Server because of Microsoft's stronger focus on functional enhancements, such as limited row-level locking, direct data warehousing support, and data replication. SQL Server 7 is arguably an important milestone in SQL Server's history. It positions Microsoft as a serious database server player that can support the enterprise as a whole in terms of the key criteria that are discussed in this section.
Microsoft SQL Server has been an NT-based database product. Since NT has historically been a workgroup or departmental operating system, Microsoft SQL Server capacity has been more or less sufficient to handle the requirements of those size environments. Capacity can be measured in terms of database size, throughput, and concurrent user connections. Concurrency is a key attribute-- as load is measured not by the number of users that can use the application, but by the number of users who are simultaneously using the application. In terms of capacity, SQL Server can now support over 1 TB of real data. The throughput of the database in terms of TCP-C numbers and other client information demonstrates impressive growth over version 6.5 and against the competition. SQL Server can now also support concurrent user connections that number in the thousands.
Microsoft SQL Server is positioned to scale from a stand-alone database server to an enterprise-wide SQL Server capable of supporting over 1 TB of data and hundreds and thousands of concurrent users. This degree of scalability is supported without a corresponding strain on the hardware resources. Adding user connections and increasing storage requires nominal increases of other supporting resources. Due to the scalability improvements in NT, SQL Server is now also able to leverage an increasing number of engines, from 8 to 32 depending on the hardware capability.
By its very nature, Microsoft SQL Server is restricted to Microsoft operating systems, at the exclusion of others, such as UNIX, MVS, OS/ 2, and MAC OS. However, the industry momentum is clearly behind Microsoft and many organizations are committing to Microsoft Enterprise solutions. This is a huge market in itself. Within this family, SQL Server is highly interoperable between the NT family, Windows 95, and Windows 98. SQL Server's interoperability is tightly integrated with these operating systems. NT, specifically, is successfully supporting a range of hardware platforms, notably the DEC Alpha 64-bit series. This is an especially exciting enhancement in terms of mobile, desktop, and large environment support. SQL Server can replace applications of the access database where it is a requirement to support databases in the desktop and in large clustered environments as well. This is also supported by new physical formats (e. g., for pages, rows, extents, data files, and transaction logs) for the data architecture. Also watch for NT's and Windows 95/ 98's continued penetration into computer platforms that support other types of electronic products.
An important benefit of SQL Server is its tight integration with other Microsoft Enterprise solutions. COM+ (formerly COM and MTS), ActiveX, Microsoft SQL Server, Visual Basic, Visual C++, SourceSave, NT, SMS, OLE DB, Internet Information Server (IIS), Internet Studio, and the Web tools provide the infrastructure for enterprise strength solutions. There is also tight integration with modeling tools such as the Visual Data Modeler. Integration with OLE DB supports access to heterogeneous data, including relational tables and flat files, as shown in Figure 1.2.
Ease of Use
Microsoft SQL Server is possibly one of the easiest database servers to use and administer. The product offers a high level of self-tuning, automation, wizards, and graphical tools. These are enabled during the entire project life cycle. Many activities can be performed in several ways, including the use of the visual tools, scripts, or traditional text-based interfaces. The visual tools also allow generation of traditional text-based scripts. The following features support SQL Server's ease of use:
Dynamic space management allows databases to dynamically size themselves without DBA involvement. SQL Server blurs many of the tasks between DBAs and developers, freeing up resources to do other work on the project.
Data files grow and shrink automatically. This feature can be turned off or severely limited when files are created in order to protect disk space.
There are many wizards to assist in completing major activities. These include administration, performance, security, and object management.
Graphical tools simplify interaction with the database (e. g., graphical show plan, command syntax is color coded in the query analyzer).
The query analyzer suggests indexes for best access based on a costing algorithm. Automated configuration of memory disk space. Resources are requested by SQL Server as they are required.
Until this latest release, many of my colleagues did not consider Microsoft SQL Server to offer exceptionally high performance in larger database environments. Oracle, DB2, and Sybase were perceived as better performers in high-load environments as long as enough hardware was allocated to the solution. However, Microsoft SQL Server offers dramatic performance improvements in its utilities, database engine, integration with NT, and data structures, including the following:
Microsoft SQL Server is based on a highly componentized architecture. Architecture enhancements to this release include
Full row-level locking or "dynamic" locking support on data and index pages. Version 6.5 supported limited row-level locking capability. This is a much requested feature that will go a long way toward enhancing the popularity of this product. Locking automatically scales between row, page, and table locks based on contention for data. Row locks require the most resources to maintain so they are the least desirable from a performance perspective. However, row locks reduce data contention, so they are useful under heavy transaction loads to improve system performance. Full dynamic row-level locking allows SQL Server to support many enterprise resource planning (ERP)-type packages, such as PeopleSoft, FileNet, and SAP. Locks can now be applied at the row, page, or table level. Row-level locking is available at the data row and index level. The choice of lock is automatically selected by SQL Server but can be overridden by the DBA.
Internal SQL Server architecture is now based on OLE DB-DCOM components. This is a major enhancement for extensibility of SQL Server, and integration with Microsoft's enterprise development tool suite. OLE DB provides access to both SQL Server and non-SQL Server (e. g., Excel) data.
An increase in addressable main memory space. This is important because database objects such as tempdb can be moved directly into main memory for improved performance. Data and procedure cache sizes can also be increased. There is also support for 1 GB of RAM for improved response time and calculations.
Dynamic memory optimizes memory allocation. Large memory support for more than 4 GB is available for NT 5.0 systems.
SQL Server 7 is compliant with ANSI/ ISO information schema standard.
Support for both 3-tier and n-tier architecture.
Some system tables have been removed. These include sysprocedures, sysrestoredetail, syssegments, systasks, sysusages, syslocks, syskeys, and syshistory.
The full path to database objects consistently contains four components: server. database. owner. object.
New system procedures and system tables in both the master and the model database.
New disk format and storage subsystem for more efficient data storage and access capabilities. The new data structures offer a better opportunity to scale.
Microsoft SQL Server contains many administrative enhancements including
Microsoft SQL Server supports enhancements to its core functionality including
The physical layout of the SQL Server files has changed from the previous version. This requires an upgrade of files to version 7. The upgrade actually occurs very quickly.
Data Warehousing, Replication, and eCommerce
SQL Server 7 continues to integrate data warehousing, replication, and eCommerce into the core database product. Enhancements in this area include
Another benefit offered by SQL Server is its return on investment or its cost/ performance ratio. SQL Server offers a winning combination of functionality compared with the price of a working solution. However, it is important to watch this ratio, as it has eroded in the past two years. This erosion may continue into the future.
Generally Painless Installation
This section describes the installation process for Microsoft SQL Server 7. SQL Server can be installed on a stand-alone platform or in a LAN environment. The former approach is useful for readers who are learning the product for the first time. The latter approach is the preferred method for work environments.
It is important to note the term "generally" painless installation. In most cases, installation is straightforward. However, the occasional system can experience difficulties for a variety of reasons, including the following:
Operating system problem.
Internet Explorer version is older than the operating system. Service pack missing.
Configuration issues, such as not enough RAM, insufficient disk space, or insufficient processing power.
Make a bootable disk of your computer before starting the install process. This can be done through the Start-Setting-Control Panel-Add/ Remove Programs option.
Follow the Prompts
As a prelude to installing SQL Server, the following steps should be followed:
Installing SQL Server generally consists of a straightforward set of activities that can be painlessly completed by the installer. The product is installed under a Windows NT Server platform, or in a small number of cases under Windows NT Client. It is also supported by Windows 95 and Windows 98. The first step is to insert the CD ROM containing SQL Server in the CD ROM drive and select Run. Enter the drive letter that corresponds to the CD ROM drive (e. g., d:), and select OK. The system responds with a list of folders for different environments, such as Alpha, Clients, I386, Mips, Odbc_ sdk, Ppc, Ptk, and Sqlbks65. For example, open the I386 folder by double clicking on it to install SQL Server under an Intel platform. Find the setup icon and click on it. Click on the Continue button to get past the welcome window that appears. On the first installation of SQL Server, you will be prompted to enter your name and organization information. The product ID is typically found on a small label attached to the back of the CD ROM case. There are three versions of SQL Server: desktop, standard, and enterprise.
Follow the instructions that appear in the active window. The following options are available:
Reinstallation of SQL Server can then be done in one of two ways: on top of the existing SQL Server programs or from scratch into a new environment.
Click on the text corresponding to the desired radio button option (e. g., click anywhere on Install SQL Server and Utilities) and click on the Continue button to display the corresponding window to proceed with the option.
Table 1.3 explains options that can be selected at SQL Server installation during the execution of the setup program. Generally the default options are acceptable for most installations. However, in some cases, for performance reasons, customized options can provide much needed improvements in application performance. Many of the options can be modified after installation, however, this generally requires the master database and user databases to be rebuilt. Use the SETUP program to change the configuration after SQL Server is installed.
Client and Server on the Same Platform
Microsoft SQL Server can be installed on a stand-alone platform. Select the Install SQL Server and Utilities button and click on the Continue button. A basic installation that uses the NFS file format with no external clients is the simplest way to get started using the product quickly and is sufficient for the examples in this book.
Client and Server on Different Platforms
The primary difference between implementing SQL Server on a single platform versus a distributed environment is the need to prepare the client platforms in the latter case. The client platform will typically have a front-end application development tool installed, such as Visual Basic or Visual C++. Clients communicate with SQL Server through the network using one of the protocols shown in Table 1.3.
NT Server Is not the Same as NT Client
The Windows SQL Server engine should be installed under NT Server if it is intended to support client platforms within the application environment. It is also possible to install SQL Server under NT Client in a stand-alone configuration. SQL Server can also be installed under Windows 95 and Windows 98. Client platforms that issue service requests to SQL Server can run a more diverse range of operating systems including NT Server, NT Client, Windows 95, Windows 98, and Windows 3. x.
After Successful Installation
A successful installation updates the Windows NT Registry with the following key: HKEY_ LOCAL_ MACHINE\ software\ Microsoft\ server_ nm. A successful installation creates the following applications in a Windows environment. These can be seen either as a folder or as an option in the Start menu. The relevant components available in the SQL Server menu structure include Books Online, Client Network Utility, Enterprise Manager, MSDTC Administrative Console, Profiler, Query Analyzer, Server Network Utility, and Service Manager. These applications are explored in more detail later in this chapter. Table 1.4 shows the databases that are created by the installation. Each database has a physical data file (. mdf extension) and a corresponding log file (. ldf extension). The minimum install size combines the size of the database file and the log file.
Client/Server installations can occur on a single machine. In such an event, the client and server separation is logical rather than geographical. This is entirely consistent with the client/ server model.
A successful SQL Server installation on a Server platform creates a physical Mssql7 (you can override this name) directory under the root. Table 1.5 shows the important level 1 subdirectories that are created under \Mssql7.
Note that the executable files in the binn subdirectory correspond to the applications that are described later in this chapter. For example, Sqlmangr is the executable for SQL Server Service Manager and Sqlservr is SQL Server. Some of these applications can also be invoked by double clicking on their names in the Windows NT Explorer. Some dependent applications will not start using this technique; however, the operating system displays an appropriate error message if you make such an attempt.
Upgrading from a Previous Version of SQL Server
Most SQL Server features are available automatically, thus allowing the process of upgrading to be done incrementally. SQL Server databases prior to version 6 should first be upgraded to SQL Server 6.5. To upgrade SQL Server 6.5 to SQL Server 7, unload the data from your current databases and reload the data into the SQL Server 7 databases. This is required because of the new row-level locking capability in SQL Server 7. This can take a great deal of time and may have to be done on a weekend or at night. However, tests have shown that the upgrade process is very efficient, so do not be surprised if it takes a fraction of the allocated time. SQL Server 7 also offers a better storage architecture in terms of efficiency, so your converted database may require up to 30 percent less space. A general rule of thumb is to allocate about 1 hour for every 1 GB of data being converted. You can expect to get better performance by improving the hardware on the system. SQL Server is packaged with a Version Upgrade Wizard that should be used to upgrade from SQL Server 6.5 to SQL Server 7. The upgrade wizard performs a checksum calculation to ensure that the data is transferred correctly.
When upgrading from version 6.5 to version 7, keep a DB6.5 compatibility level for user-defined databases. System databases should be set to version 7's compatibility levels with the new system procedure sp_ dbcmptlevel 65. Most SQL Server 7 commands are enabled for user databases even if they are left at a version 6.5 compatibility level.
The following steps should be followed to upgrade a version 6.5 server to version 7:
Use chkupg. exe from the operating system or from graphical tools to inspect the capability of a data server environment. Display the results from running this command.
Installing Microsoft Visual Database Tools
After installing SQL Server, it is useful to install supporting tools or utilities such as the Microsoft Visual Database Tools. This tool set is packaged with some versions of SQL Server and with other Microsoft Visual Suites. It requires Microsoft Internet Explorer 3 to install correctly. Internet Explorer can be found and downloaded from the Microsoft Web site (www. microsoft. com) and must be installed on the platform before Visual Database Tools can be successfully installed. A successful installation of this product will update the Start menu options, allowing the tool to be selected graphically.
To use the Microsoft Visual Database Tools application, it is necessary to create a connection to SQL Server. This is done by selecting New-Project from a pulldown menu. Ensure that the Projects tab is selected and enter a Project Name. Enter any valid name and select a location on the disk drive for physical storage. Press OK to proceed to the next dialogue.
Press the New button beside the DSN name and select the SQL Server driver. Enter a name for the source and write down the location of the driver so that it can be selected for other projects. Select the Next button until a dialogue that prompts for the Server name appears on the screen. Enter the name of the active SQL Server, which in this example is Server-01. Any description can be entered in the Description field. Press the Enter key to proceed to the next dialogue. For simplicity, trusted security can be selected to simplify the logon process. Press the Next button to proceed to the next dialogue box where you can modify the default database. Click on the Change the default database to checkbox and select Master as the default database. Leave the other values alone and press the Next button until the ODBC Microsoft SQL Server Setup dialogue appears. Click on the Test Data Source button to ensure that a valid connection can be established. Press OK to complete the operation, which will create a data source in the Select Data Source dialogue box. Select the data source that was just created so that it appears in the DSN Name field. Press the OK button. Notice that the project that was just created appears in the hierarchy on the left of the Microsoft Developer Studio.
Starting Microsoft SQL Server
From the Windows NT Server environment, SQL Server can be started through one of the components available through the SQL Server menu options. SQL Server start-up and stop operations are controlled through an easy to understand traffic light metaphor. To start the engine, select the SQL Service Manager option to display the window shown in Figure 1.3.
Use your mouse to click on the Start/ Continue option to start SQL Server. If the environment is sound, and SQL Server was successfully installed, the green light will go bright showing that the server is active. It is possible to select specific SQL Servers in a multi-server environment by selecting a service from the pulldown menu. (See Figure 1.3.)
SQL Server can also be configured to start automatically when the operating system is booted. This is done through an SQL Server installation option or through operating system commands. Microsoft SQL Server can be started in several basic modes: automatic, manual, and single-user. The first two are used most often, the difference being how they are started. The single-user mode is used for some administrative purposes, for example, when recovering a master database that is corrupted.
Stopping Microsoft SQL Server
SQL Server is stopped by selecting the SQL Service Manager option and double clicking on the Stop option with the name of the database server selected. Another method of shutting down the server is to use the Shutdown command from the Query Analyzer (formerly ISQL/ w) dialogue. Shutting the computer down without a standard shutdown causes SQL Server to undergo exception processing when it is restarted, including rolling the transaction logs forward.
Inspecting the Errorlog File
A text-based session/ errorlog file is maintained by SQL Server throughout a session. Different session logs are separated by sequential numbers. These files are stored in the \mssql\ log directory by default. The log files have a root name of errorlog. Older versions of Errorlog have a name of the form errorlog. x, where x starts at 1 and increases sequentially. The current errorlog file does not have a suffix and is locked while SQL Server is running. Figure 1.4 is a sample errorlog. It is a good practice to review this file because it contains valuable information. SQL Server tests each database and flags errors if they are encountered. The date and time stamp provides statistical information concerning the length of time the actions require.
It is only possible to sign on to Microsoft SQL Server if the engine has been started and is running in the operating system environment. Several methods are available to sign on and issue requests to SQL Server, as shown in Table 1.6.
Microsoft SQL Server and Sybase SQL Server historically share the same engine, but there have always been differences. For example, Sybase SQL Server uses the syntax isql-Uuserid-Ppassword to sign on. The minor difference in the syntax between the two products is the use of the "-" in Sybase and the "/" in Microsoft SQL Server.
Administrative ApplicationsThis section describes some of the more useful user-friendly visual tools that are installed as part of the SQL Server setup process. The applications provide a graphical and user-friendly method of interacting with the SQL Server. An interactive alternate method usually exists to work directly with SQL Server without going through the user-friendly tool. However, the first method is easier to learn, more intuitive, and generally faster when it is mastered. Each of these important SQL Server Administrative tools are discussed later in this book.
Microsoft SQL Server Service Manager
The SQL Server Service Manager is used to start, pause, and stop SQL Server applications, namely SQL Server Agent, SQL Servers, and MSDTC. This is an intuitive interface that uses a traffic light metaphor. By default the application itself is found under Start-Programs- Microsoft SQL Server-SQL Server Service Manager. Selecting this option displays the dialogue box shown in Figure 1.6. The Server list box displays the names of the servers that are available on the system. The Services list box displays a list of services that can be selected. In this example, the name of SQL Server was established as Server-01 at installation time. This is selected in the figure. The service to start the server is MSSQLSERVER, which is also selected in this example. Double click on Start/ Continue (green light) to start the server. Under normal circumstances, the green light will be selected and SQL Server will start to run.
The hypertext at the bottom of the dialogue box reflects the successful launch of SQL Server; in this example it reads
Once SQL Server is running, the SQL Server Service Manager dialogue box can be closed by clicking the "x" symbol in the top right corner of the box. The traffic light metaphor is still visible in the Windows status line. A server can also be paused by clicking on the Pause option (yellow light) or shut down by clicking on the Stop option (red light).
SQL Server Query Analyze
The SQL Server Query Analyzer is a graphical form of the character-based ISQL utility with a few more integrated tools. In previous versions of SQL Server it was called ISQL/ w. This application supports execution of any command statements that can be issued directly through the character-based ISQL command. Under the default installation, this application is available under Start-Programs-Microsoft SQL Server-SQL Server Query Analyzer. Selecting this option displays the Connect to SQL Server dialogue which is used to connect to an active server, as shown in Figure 1.7. As shown in this example, Server-01, which was started using the SQL Service Manager, is selected in the pulldown list. Selecting the Use Windows NT Authentication will activate Windows trusted security. Selecting Use SQL Server Authentication requires entry of a login name and password. This example uses the installation defaults for both of these fields. Login name is sa (system administrator). The password is empty by default. The List Servers option displays the available servers. Clicking on the Connect button creates the link between the application and the active SQL Server.
The SQL Server Query Analyzer application, as shown in Figure 1.8, appears after a successful logon. This application consists of a large dialogue window and a nested dialogue that has three tabs. There is also a pulldown menu and a toolbar. The default toolbar options allow a query to be cleared, a script to be loaded from disk, a query to be saved, query options to be established, a database to be selected, the current query to be executed, a result set to be cleared, a query to be executed, an SQL Statement Execution Plan to be displayed, and the execution of a running query to be halted.
The three tabs on the Query Analyzer are Query, Results, and Plan. The Query tab is used to issue requests to SQL Server. SQL Server displays the results to the active query in the Results tab. The third tab, Plan, displays the SQL Statement Execution Plan. The latter displays the graphical execution plan in the Plan tab. Clicking on the graphics drills down into further details. This command can be invoked through the Query-Execute With Graphical Showplan option from the pulldown menus.
The Query pulldown option also contains several other useful commands when the Query tab is selected (on top of the others). The Object Help option searches the default database for the object that is selected. For example, typing sysobjects, selecting it, and choosing Query-Object Help displays information about the sysobjects system table, as well as its fields, datatypes, lengths, and null acceptability. The other command, Query-Set Options, displays a dialogue that allows options to inspect statistics about queries to be established.
The options in this dialogue fall into two groups: query flags and format options. Query options include No Count Display, No Execute, Parse Query Only, Show Query Plan, and Show Stats Time. The No Execute option is useful in evaluating the cost and plan of a query without the risk of executing it (and possibly bringing down the server). Show Query Plan and Show Stats Time are useful in evaluating query plans. Another option that can be set using this dialogue is rowcount. When the value is 0, all the rows in response to a query are returned. A value greater than 0 places a limit on the number of rows that are returned by a query. This is useful in many instances. Some queries can literally return hundreds or thousands of rows. In other cases, it is useful to only return the first 10 or 20 rows of sorted data. A final example is a scrolling application that can only accept a screen full of information at a time. Format Options allows customization of the results through such options as Result Output Format, Right Align Numerics, Print Headers, and Output Query. Result Output Format establishes the format of the printed data, allowing column alignment and delimiters. Selecting Print Headers causes column headings to print in the output. Selecting Output Query prints the original SQL query in the output.
You can use the Query Analyzer as a text editor. The text can be coded directly into the top window and saved using the File-Save As option from the pulldown menu. If you only want to execute a portion of the code that is displayed in the window, highlight the text and select the execute button or choose Query-Execute Query from the main pulldown menu.
Sometimes it is necessary to repeatedly issue different SQL commands during a development cycle. An example of this might be inserting data into a table and issuing another command to select the number of rows contained in the table. Of course it is possible to type one command, execute it, clear the screen type in a second command, clear the screen, and reenter the first command. A better method is to type both sets of commands into the save screen. Using the mouse, only the commands that are to be executed should be highlighted, and the Query-Execute (or click on the Execute button) option should be selected. Executing the second command then requires highlighting the appropriate commands and repeating the execution sequence. The first set of commands can be reissued without any rekeying by changing the commands that are highlighted.
The query analyzer uses colors to identify syntactical errors in the code. As you code Transact-SQL statements in this tool, watch for colors that flag for potential syntactical mismatches.
SQL Server Enterprise Manager
This application is used to administer SQL Server and is a snap on to the Microsoft Management Console (MMC). Figure 1.9 shows the functions that can be administered with this dialogue. This includes creating and maintaining database objects such as databases, tables, stored procedures, and triggers. The figure shows an expanded version of the contents of an SQL Server in the left hierarchy. The options shown on the right side of the screen can be selected. This application is also used to register the server and maintain user profiles. Although the majority of the functions offered by this utility can be performed with a combination of the Query Analyzer (SQL/ w) and batch commands in the operating system, the Enterprise Manager simplifies the interface and offers DBAs a consistent method for completing their job functions. It is more object-oriented in its operation than the previous versions of the tool. Some of the version 6.5 functionality has moved to the Visual Database Tools application.
Selecting one of the database objects in the left hierarchy shows an additional level of detail for that type of object. For example, selecting the database level displays the databases that are available in the environment. This is shown in Figure 1.10. This example contains several databases that were created with the Create Database Wizard. Selecting one of the databases displays the dialogue box shown in Figure 1.11. This is a very useful feature that can be used instead of several system procedures. The panels in this folder provide a view of such things as the tables contained within the database, the size of the database, the amount of free space available, and details about the transaction log. This is a huge improvement over earlier versions of SQL Server that required the use of multiple commands to produce the same information.
SQL Server Client Network Utility
This dialogue consists of three tabs, DB Library, Net Library, and Advanced, as shown in Figure 1.12. The DB Library tab supports identification of the version of the database middleware that links an application to the Net Library. The Net Library tab supports identification of the Net Library software. It also supports identification of the default network (e. g., Named Pipes, TCP/ IP Sockets, and Banyan Vines). The Advanced Tab supports identification of servers, DLLS, and a connection string.
SQL Performance Monitor
As shown in Figure 1.13, this application reports performance statistics about SQL Server in a graphical format. This is another tool for database administrators to use in analyzing the SQL Server environment. The pulldown menu supports alerts, logs, and reports. Data for the chart can be displayed from the current server or from a log file. Charts can also be exported to files.
SQL Server Profiler
As shown in Figure 1.14, this application can be used to trace events in a database server. This application was called SQL Trace in SQL Server 6.5. The General tab must be used to start using this application. It is necessary to enter a mnemonic name and a source to establish a trace. This will prompt you to register a database server name (e. g., Server-01 with an sa logon ID). The results of a trace can be displayed on the screen or saved into a file. The Events tab is used to identify the events that are captured by this application as they occur in the registered database server. Some examples of these events are Connect, Disconnect, ExistingConnection, RPC: Starting, ErrorLog, Insert, and Delete. The Trace Criteria tab is used to identify the event criteria, including an Application Name, ConnectionID, CPU, Database ID, Duration, Event Text, Host Name, Host Process ID, NT Domain Name, Index ID, Object ID, Owner ID, Reads, Writes, Severity, SPID, and SQL User Name. The last tab, Output Data, is used to select data from the previous list.
Getting Things Done
A variety of methods are available to issue commands or requests in the Microsoft SQL Server environment. A few examples are discussed here, however, the two-part primer starting in Chapter 3 and extending into Chapter 4 provides a more detailed discussion of this topic.
Transact-SQL: Database Programming Dialect
Transact-SQL is the programming language built into the SQL Server engine. It consists of a data definition language (DDL), data manipulation language (DML), data control language (DCL), and control-of-flow language. The control-of-flow language consists of programming constructs such as if, while, case, and built-in functions. Transact-SQL also supports database objects such as stored procedures, triggers, views, cursors, datatypes, and user-defined datatypes. Transact-SQL is accompanied by system procedures that interact with the SQL Server environment and the system tables.
Transact-SQL was introduced with the first commercially available version of SQL Server in 1987. It has been consistently enhanced with new releases of the product. For example, the first version of SQL Server did not support interstored procedure communication through updated parameter values. In fact, stored procedures could not update parameter values from the calling stored procedure. This required creative programming through the creation of temporary tables to hold values that different stored procedures were required to access. This required a lot more table reads and writes.
The Transact-SQL syntax is based on the ANSI SQL '92 dialect. This dialect supports extensions to the standard but is based primarily on Sequel. Application partitioning has always been a problem with client/ server applications and will continue to be a lesser problem with network applications. The reason for this is that the client dialogue is traditionally not the same as the database server programming dialect. This means that once a module is designed and programmed to reside on the client or the database server, it is not possible to move the module between these two tiers because of the programming language inconsistencies. There is a chance that Microsoft may choose VBA-Script as the Transact-SQL standard syntax to act as a host to standard Sequel DDL, DML, and DCL dialects and for the extensions. While this will not allow stored procedures to be more portable between database products, such as SQL Server and Oracle, the change will allow greater code reuse between the client and the server tiers within a Microsoft environment.
Finding Out Who's Using the System
In a multiuser environment, it is necessary to identify users who are consuming SQL Server resources and to communicate with them in the event that the environment must be modified. A system procedure, sp_ who, locates this information in the system tables and displays it to the requester. System procedures are a set of utilities or programs that interact with the Microsoft SQL Server environment and system tables to perform useful housekeeping functions and report on SQL Server internals. To test this system procedure you must log into an active SQL Server engine. This command can be executed from the SQL Server Query dialogue. The command sp_ who is typed in the Query tab, the command is highlighted with the mouse, and the execute option is selected from the toolbar. The results of these actions are shown in Figure 1.15.
Finding Your Way around the Microsoft SQL Server Environment
Commands issued in the SQL Server environment operate on the default database( s). It is also possible to qualify a command or destination with a path that consists of server. database. owner. object. For example, pubs. dbo. stores references the stores table that is owned by the dbo in the pubs database. The database and owner are not required if you are the owner of a database object, and if the database is in the path. For example, suppose that the default database is Master. The command Select * from stores displays the following message: Invalid object name stores. This is because the stores table cannot be found by SQL Server, even though it exists in the pubs database. The table is found by providing a complete path, as follows: Select * from pubs. dbo. stores. We could also locate the table with the command Select * from pubs.. stores.
Limitations and Ceilings
Microsoft SQL Server is capable of satisfying the requirements of the business enterprise as a whole. Many of the limits in version 6.5 have been eliminated or their capacity has been greatly increased. Table 1.7 shows the limits SQL Server places on various features of the product.
The new version of Microsoft SQL Server offers a plethora of helpful wizards to guide you in completing various activities. As shown in Figure 1.16, wizards are divided into broad categories that include database, data transformation services, management, and replication. Wizards are a good place to start, but eventually the graphical tools support a more power-user-oriented approach for conducting the same activities. Here is a list of the wizards that are available to you: Create Backup, Create Alert, Create Database, Create Diagram, Create Index, Create Job, Create New Data Service, Create SQL Server Login, Create Stored Procedures, Create Trace, Create View, Database Maintenance Plan, DTS Export, DTS Import, Full_ text Indexing, Index Tuning, Make Master Server, Make Target Server, Register Servers, Upgrade, and Web Assistant. Chapter 3 provides examples of using some of the basic wizards. These are the wizards that are available from the primary wizards dialogue, which will probably be expanded by Microsoft over time. Other wizards are also available throughout the SQL Server environment.
It is necessary to select a server context before using the wizards. This can be done by selecting a server in the dialogue box on the right of the screen. Wizards can be invoked from the pull down menu or the menus icons on the Enterprise Manager dialogue box.
The hardware requirements for running earlier versions of Microsoft SQL Server are the following: Intel 486+, DEC Alpha, MIPS chips, 16 MB RAM minimum, >32 MB RAM recommended; 100 MB disk space. It is possible to save some disk space by running SQL Server books from the CD ROM, NTFS, or FAT file system. SQL Server 6.5 requires Windows NT Server 3.51+. A workstation version of SQL Server can run on Windows NT Workstation 3.51+. SQL Server 7 is more demanding of resources. It requires Windows NT 4. x or higher, Windows 95 or higher, and Internet Explorer 4 or higher. My testing shows that running SQL Server 7 on anything less than a 133 MHz Pentium with 24 MB RAM is highly unproductive. Even 24 MB is underpowered and should be 64 MB or more.
Research for this book was completed on a variety of Intel-based platforms, including both low-end and high-end systems. Name brands of the hardware are not included in Table 1.8, as I believe that the limitations encountered during testing were more a function of the power of the hardware as opposed to the vendor.
The low-end, 75 MHz platform was capable of running Microsoft SQL Server under Windows NT 4 with one connection. Upgrading to NT 5 Beta or Windows 98 brought the performance down significantly. This required a transfer to a faster machine. Upgrading to a 133 MHz platform made a significant difference in performance. The 350 MHz system with 128 MB RAM provided a great improvement in performance that was immediately evident during the implementation process, startup, and issuing of complex queries.
After suffering through the poor performance on a low-end Pentium, it was necessary to upgrade the machine. I chose a 350 MHz Pentium II with a 16 GB hard disk to ensure there was more than an adequate supply of power and capacity. Installing SQL Server 7 was painless in that environment. However, an interesting concern was raised when trying to install older Windows-based software that was running well under Windows 95. A graphics package that installs and works fine under Windows 95 could not install on a disk that had 14 GB free space because it could not recognize the 14 GB of free space and instead seemed to read it as 14 MB. A possible workaround that I did not try was to install a lot of software until 999 MB of disk space was free, at which point the package could be installed.
An Ethernet LAN supported the database server and client platforms. SQL Server 6.5 and SQL Server 7 Beta were used to test the code provided in this book. The database server was executed on Windows NT Server. SQL Server was tested under Windows NT 4, Windows NT 5, Windows 95, and Windows 98.
In this chapter you were introduced to the new Microsoft SQL Server environment. Enhancements made to SQL Server 6.5 were discussed in the context of portability, interoperability, scalability, ease of use, administration, architecture, and new functionality. This chapter also examined upgrading SQL Server 6.5 or implementing SQL Server 7. The reader was also provided with a walk-through of the GUI tools, wizards, and SQL Server environment. This chapter also examined limits and ceilings on SQL Server components. The chapter concluded by describing experiences under a diverse set of test environments.