My is IP Address  •
Detail of TCP/IP  •
Internet IP Addressing  •
IP Subnetting  •
IPv6 Technologies  •
OSI Model  •
Network Security  •
Hacking Explained  •
Types of Hacking  •
Firewall & IDS  •
VPN (Virtual Private Network)  •
IPSec (IP Security)  •
Data Encryption  •
DES & Key Exchange  •
Ethernet Technologies  •
History of Ethernet  •
LAN Switching History  •
LAN Switching  •
Virtual LAN  •
Bridge & Switch  •
Router Technologies  •
Routing Technologies  •
WAN Technologies  •
Fiber-optic Networking  •
Frame Relay Technologies  •
ATM (Asynchronous Transfer Mode)  •
MPLS (Multiprotocol Label Switching)  •
Detail of MPLS  •
Wireless Networks  •
WLAN (Wireless LAN)  •
Wireless Roaming and Bridges  •
Broadband Technologies  •
DSL Technologies  •
Cable Broadband  •
ISDN Technologies  •
IP Telephony  •
VoIP (Voice over IP)  •
IP Convergence  •
IP Multicast  •
Videoconference  •
Network Maintenance  •
Disaster Recovery  •
QoS (Quality of Service)  •
Routing Protocols  •
High Availability  •
STP (Spanning Tree Protocol)  •
Data Center  •
SAN (Storage Area Networks)  •
SAN Technologies  •
SNA (Systems Network Architecture)  •
Desktop Protocols  •
E-Learning  •
E-Commerce  •
Windows & Linux Comparison  •
SQL Server  •

SQL Server Overview

SQL Server Overview

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

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 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 SQL Server machines have exceeded 100 gigabytes. The largest current project in development using SQL Server exceeds 1 terabyte.

SUMMARY
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. This book will help you make the most of your database choice.




Copyright © 2006 myipaddressinfo.com. All rights reserved.

This website and the materials and information you find on this website are provided "as is", without warranty of any kind, either express or implied, including without limitation any warranty for information, services, or products provided through or in connection with the service and any implied warranties of merchantability, fitness for a particular purpose, expectation of privacy or non-infringement.