Microsoft SQL Server© Application Development

IT Solutions Group, Inc. has extensive experience developing Applications with Microsoft SQL Server.  Below you will find a brief overview of this robust and scalable development platform.

WHAT IS SQL SERVER?

Microsoft SQL Server is a database management system. It is portable, reliable, flexible, fast, and easy to manage. SQL Server has been developed specifically to take advantage of the best features Microsoft Windows NT has to offer. It is optimized for the multithreaded, preemptive multiprocessing kernel of Windows NT. The many features and options in SQL Server are geared toward multiple users who need concurrent access to high volumes of data. In this chapter, we discuss the primary features that differentiate SQL Server from other database management software.


PORTABILITY

SQL Server is portable across all platforms on which Windows NT runs, including Alpha AXP, Intel x86, MIPS, and PowerPC architectures. The Windows NT hardware abstract layer (HAL) and other kernel processes create layers between the hardware and services like SQL Server.

Besides being portable across platforms, SQL Server and Windows NT support many different types of hardware devices. The recent and ever-changing improvements in hardware devices include support for symmetric multiprocessing (multiple CPUs), high-bandwidth network cards, striped backups, and RAID disk striping. RAID, an acronym for Redundant Array of Inexpensive Disks, can provide performance and/or reliability improvements. SQL Server will also support multiple tape drives for striped backups, which are useful for very large databases; striped backups write to multiple backup devices simultaneously.

More on NT Integration
This section is a preview of Chapter 4, which is devoted to a discussion of the close integration of SQL Server with Windows NT.

SQL Server runs as a service under Windows NT. Services in NT can start when the machine restarts and can broadcast their service names and types over the network. For example, SQL Server will broadcast its type of service as SQL Server as well as the name of the server. This feature allows other machines, whether clients or servers, to query a network to find out which servers are providing database services. Two other services come with SQL Server — the SQL Executive and the Distributed Transaction Coordinator. These services are separate programs that run under Windows NT and can be started and stopped from the Services applet in the Control Panel. Other processes, such as replication, run as different threads under the SQL Server process.

Errors in SQL Server appear in the Windows NT event log and can be seen in the Application section of the Event Viewer program. The errors also appear in the SQL Server error log. Programmers can set their application errors to appear in the NT event log.

The NT registry controls the basic SQL Server installation parameters. It is used to save the default login name, the standard named pipe configuration, the SQL Server startup parameters, and more. It also saves parameters for SQL Executive, as well as for tools like SQLTrace.

The Windows NT Performance Monitor has a special addition for SQL Server. It includes pre-defined counters that give an in-depth look at what is happening behind the scenes. In addition, programmers and administrators can define new counters to track application-based performance. The Performance Monitor shows more detail than most people will use, but the detail is very beneficial when your system is showing signs of performance problems.

SQL Server can take advantage of Windows NT’s security models. The three choices — integrated, mixed, and standard security — give SQL Server the flexibility to fit into any security scheme. The SQL Security Manager program makes it easier to administer large groups of users because SQL user and login information is integrated with Windows NT user information.

Batch jobs use Windows NT command-line utilities, such as ISQL (Interactive SQL) and BCP (Bulk Copy Program). Windows NT is not considered to have the most powerful scripting language for command-line batch programs, but it is usually adequate for background database jobs. Other popular scripting languages, such as PERL, are available for Windows NT if the standard language is not sufficient. Combining the scheduler (new in 6.0) with the power of the batch scripting languages makes it easier to program and manage background jobs for all Windows NT jobs, not just those related to SQL Server.


RELIABILITY

SQL Server is reliable because of the enforced transaction logging in all versions of SQL Server, complete with roll-forward and roll-backward recovery. Recovery from application and system errors and from hardware problems has always been a solid part of the Windows NT/SQL Server cohesiveness.

Another new feature in the hardware/software combination arena is the fallback support in SQL Server. This option lets two servers share one disk tower. If one server fails, the other can be brought up very quickly.


FLEXIBILITY

Because SQL Server is a Win32 application, it is network independent and supports all major networks, including TCP/IP, NetBEUI, IPX/SPX, DECNet Sockets, Banyan Vines, and AppleTalk (or Apple Datastream Protocol). The Win32 API lets services like SQL Server take advantage of all networks that work with Windows NT. The network library (new in SQL 6.0) called MPNL, or Multiprotocol Network Library, integrates common network services and remote procedure calls (RPCs) to offer additional features. The Windows NT RPC allows two-way encryption so that SQL Server can encrypt both the queries and the results. The MPNL option is found in the SQL Server installation process.

Client/Server Orientation
SQL Server has all the right pieces to be a very good element of a client/server environment. Support for the major network protocols, standard database links with ODBC, new OLE automation, and multiple choices for client-side data management make it very versatile. DB-Library, ODBC, and Microsoft Jet Engine are choices for getting data from SQL Server to client machines and back. SQL Server is also one of the best databases for the back end of an Internet application. The new tools, such as ISAPI (Internet Services Application Programming Interface), ADO (Active Data Objects), OLE-DB, and ASP (Active Server Pages), make it relatively easy to set up and manage Web servers.


EFFICIENCY

The heart of SQL Server’s good performance lies in its ability to manage memory pages and process threads efficiently. These concepts apply to SQL Server in general and not necessarily to a specific version.

Let’s look at the thread management first. Windows NT splits very large programs into smaller segments, called threads, to manage many tasks happening on the server at the same time. If you have only one CPU, then the tasks only appear to be happening at the same time, because each thread gets a small slice of time to do its work on the CPU. However, if your setup has more than one CPU, different threads can be executing simultaneously.

Because SQL Server manages its threads efficiently, it has very good response time. The Read-Ahead Manager for parallel data scanning runs on a separate thread, so it can operate independently from the user session it is working for. Other SQL Server processes, such as the checkpoint process and any backup processes started by the administrator, work on separate threads behind the scenes. You can configure thread usage for SQL Server from the standard SQL Server configuration screen. Refer to Chapter 16, “Performance Tuning,” for more information about configuring SQL Server.

Now let’s look at SQL’s memory management. SQL Server does everything in 2K blocks called pages. There are many different page types, each with its own purpose and format. Most pages handled by SQL Server are data and index pages, so most of the optimization techniques internal to SQL Server focus on these page types.

SQL Server reads a 2K page from the physical disk into memory and leaves it there as long as it can. Optimizing the task of getting data from disk to memory includes processes such as the Read-Ahead Manager. To be technically correct, the Read-Ahead Manager is a thread within the SQL Server process. The memory manager keeps track of available memory blocks to know where to put the pages coming from disk. If no pages are available, the memory manager knows which pages haven’t been used for a while and will discard them. This type of memory management is based on the least recently used (LRU) algorithm. Of course, before discarding the page, the memory manager writes it back to disk if the page has been modified. Because all the pages are the same size, the memory manager does not care about the type of page it is because the page is handed off to other threads for the actual processing.

Query Optimizer
SQL Server uses a cost-based optimizer to determine the best method of processing a query. The basis of the optimizer is the distribution page, which keeps a statistical distribution of the values in the first field of an index. Every index gets a distribution page. From this, the optimizer can determine the selectivity of the index and determine if it is the best one to use.

Developers and administrators can use tools that show what the optimizer has chosen and the steps it goes through to get to a final decision. The SET SHOWPLAN ON option is one of the most helpful techniques for programmers to learn. Several DBCC TRACEON options show the join order and index selections made by the optimizer.


MANAGEABILITY

In SQL Server, Microsoft has provided some of the best administrative and operations tools for managing your SQL Server environment. For example, the Enterprise Manager program allows administration of all your servers from one central location. SQL Server uses its own services to manage a server. System databases and system stored procedures perform various administrative functions.

System Databases
SQL Server uses its own databases to manage the user databases, which means that the same tools and techniques can be used to manage SQL Server as well as all the databases on the system.

The main database is the Master database. It is used to control devices, other databases, logins, remote connections, and configurations. Another system database, Tempdb, is used for temporary workspace, such as large sorts and work tables for complex queries. The Model database is used as a template when creating application databases — any objects added to the Model database are added to any new application databases created after the object was added. The Msdb database (new in SQL 6.0) keeps track of scheduled jobs and job history. One other system database, Distribution, is created only when replication is installed.


ABILITY TO HANDLE VERY LARGE DATABASES (VLDB)

SQL Server’s architecture, its integration with Windows NT, and its expandable hardware bring Microsoft SQL Server into the big leagues. The combination of operating system and hardware features allows for very large databases at reasonable prices. The largest production databases on Microsoft SQL Server machines have exceeded 100 gigabytes. The largest current project in development using SQL Server exceeds 1 terabyte.


SUMMARY

Microsoft SQL Server is portable, reliable, flexible, efficient, and easy to manage. It has been written to take advantage of the architecture of Windows NT. Other database management systems are written to be very portable between operating systems, which limits their opportunities for specific performance enhancements. In contrast, SQL Server is portable only to the platforms supported by Windows NT, and this tight integration lets SQL Server become more powerful with each enhancement to Windows NT and the underlying hardware.

SQL Server is reliable. SQL Server is oriented toward client/server applications. SQL Server is fast and has good performance tuning options. SQL Server has some of the best administrative tools on the market. The Software Development Life Cycle methodology will help you make the most of your database choice.

Home Page