Anatomy Of A Modern Clientserverdb

Anatomy of a modern Client-Server Database Modern Client-Server (CS) Databases, such as Oracle and MS SQL Server, share various design elements. At the core of it all, Databases manage large amount of data stored in one or more disk files. They also facilitate ad hoc querying of such data. The following are the common design elements of a modern Client-Server Database. Data Files CS Databases use one or more physical data files on disk to store information in a manner optimized for updates and lookups. For example, a database may store raw data in one disk file location and an index to the data into another file location to optimize access. The raw data may be stored sequentially whereas the indexed data may be stored in a sorted fashion to optimize access speed. CS Databases also utilize low-level, raw disk IO instead of OS provided IO functions to further enhance performance. Oracle stores its data in files with an extension .DBF whereas MS SQL Server uses .MDF and .LDF extensions for its data files. Databases also use log files to provide transactional capabilities. Managing Process Oracle refers to the Manage Process as the Service or the Instance. Oracle also identifies this process with a unique identifier called the System Identifier (SID). MS SQL Server refers to the Managing Process as an Instance. We think of this Managing Process as the Database Server. CS Databases usually run on a machine other than the client machine. It follows then a separate, stand-alone process must manage the data files. A separate process provides isolation for database operations immunizing it from a misbehaving Client Application and vice versa. On Windows platform, the Managing Process is implemented as a Windows Service. Specific to a Managing Process are in-memory data structures used to cache recently or commonly used data. Oracle calls this memory area, System Global Area (SGA). An important sub-subsystem of the Managing Process is the SQL Query Processor and Optimizer. The objective of this subsystem is to understand and process data definition and data manipulation requests by the Client Application in an efficient manner. The Managing Process also enforces ACID qualities of a database: Atomicity - Either all or none of the tasks in a transaction are performed Consistency - The database remains in a consistent regardless of successful or unsuccessful transactions Isolation - Results of operations during a transaction must remain invisible to other transactions and queries Durability - Once a transaction is completed, the resultant changes persist Communication Channel

The Client Application communicates with a CS database over a network. The CS Database provides a communication channel over which it can exchange data with a Client Application. A Client Application needs the following information to connect to an MS SQL database: Name of the Host (IP address or Name of the Server hosting the Managing Process) Network Protocol used by the Database for communication such as TCP/IP Port number used by the Managing Process (defaults to 1433 in case of TCP/IP) Instance name. An Initial Catalog may be specified. A Catalog can be thought of a Logical Database or Schema. A Catalog is a container for SQL Database objects such as Tables, Views and Stored Procedures. If not specified, the Master database is the default Initial Catalog. The Catalog dictates what Database objects are initially visible to the Client Application. Database objects in other Catalogs may be accessed, subject to object permissions available to the Client Application. A Client Application needs the following information to connect to an Oracle database: Name of the Host (IP address or Name of the Server hosting the Managing Process) Network Protocol used by the Database for communication such as TCP/IP Port number used by the Managing Process (defaults to 1521 in case of TCP/IP) Service Name or SID (System ID). In Oracle there is no Initial Catalog per se as is the case with the MS SQL Server. In Oracle, the set of SQL Database objects visible to a Client Application correspond to the SQL objects owned by the authenticated User of the Client Application. In other words, for Oracle, User and Schema are synonymous. Oracle Schemas loosely correspond to MS SQL Catalogs. In case of both databases, however, user permissions determine what is accessible to the authenticated user. Authentication The Database either uses the authentication services provided by the Operating System (Windows Authentication) or maintains its own list of usernames (login)/passwords in its System tables for authentication purposes. The latter is called SQL Server Authentication mode in MS SQL Server and OS Authentication in Oracle. Before the Database allows a Client Application to connect to it, it authenticates the identity of the party using the Client Application. The Client Application presents username (login) and/or password to the Database to prove its identity. Alternatively, depending upon the Database authentication scheme in effect, the identity of the Client Application is deciphered from the Network identity of the party using the Client Application (Windows Authentication). Authorization Before a database responds to any data queries or updates, the Database determines the permissions granted to the party using the Client Application. The Database stores such

permissions its System tables. An example of permission is a permission to SELECT rows from a certain TABLE. A Database allows permissions to be aggregated into Roles that can be granted to Users of a Database. The Managing Process is responsible for limiting operations of the Client Application to permissions granted to the authenticated User of the Client Application.