330 likes | 375 Views
Database Servers. Kittiphan Techakittiroj engktc@au.ac.th. Agenda. Introduction to database servers SQL: the language for database SQL Middleware Database Server Architectures. Introduction to Database Servers. Send one command to server: SQL Request Server processes the request
E N D
Database Servers Kittiphan Techakittiroj engktc@au.ac.th
Agenda • Introduction to database servers • SQL: the language for database • SQL Middleware • Database Server Architectures
Introduction to Database Servers • Send one command to server: SQL Request • Server processes the request • Information sent back from server
Introduction to Database Servers Current Situation • Huge Market --> $$ Money $$ • SQL is most common language • SQL is standard, but its extensions is not
SQL • Structured Query Language • “English-Like” language to manipulate Database • You tells the server what you need by using SQL as the communication language • Server interprets SQL command and find the way to get the data (how to get the data)
SQL cont. • Create by Mathematician • Well-defined language based on set-theory • Powerful & Flexible commands • Store, Retrieve and Process • Process = Query, Combining, Calculation (summation) • ISO SQL, ANSI SQL-89, ANSI SQL-92
SQL Characteristics of SQL • Interactive query langauge for general database queries: for end-user, specially new GUI systems • Database programming language: embedded in many languages • Data definition language and data administration Language: contain both define database and manipulate database • Language for networked database servers and multiuser environment
SQL SQL Standard • Jointly by ANSI and ISO • SQL-89: starting point of standard SQL • SQL-92: serious defined of standard SQL • SQL-3: future trend but may be too much
SQL: SQL Standard Details of SQL Standard • SQL-89: defined according to existing products to make it widely acceptable • SQL-92: current implementation. At first, difficult to implement so it contains three levels of compliance (entry, intermediate and full) • SQL-3: contain a lot of feature. Not-yet-standard and will use time to build products.
SQL: SQL Standard SQL Extensions • Vendors try to add more functionality • Standard becomes a minimum requirement • Using extensions of SQL: • higher performance • easier in programming • lose compatibility
SQL Middleware • API (Application Programming Interface) • Help developers for • communicate with the server easier • create front-end client easier • supporting multiple client platforms • supporting multiple server platforms • make the differences disappear by becoming a transparent middleware
SQL Middleware Single Vendor Solution • The complete systems contain • Proprietary SQL API for multiple client-platforms • Proprietary SQL driver to interface with SQL servers • Supporting for multiple protocol stacks (e.g. TCP/IP)
SQL Middleware Single Vendor Solution cont. • Extra functionality • Gateways to other vendor databases: usually multi-tier • Client/Server database administration tools • GUI application developments: I recommend third-party
SQL Middleware Multi-vendor Solution • Facing Problems: • Different SQL APIs • Multiple database drivers
SQL Middleware Multi-vendor Solution cont. • Facing Problems: • Multiple FAPs and no interoperability • Multipble administartion tools
SQL Middleware Middleware Solution • Try to eliminate the need of “multiple” • Three strategies: • Common SQL Interface • Open SQL Gateway • Standard SQL Systems
SQL Middleware: Middleware Solution Common SQL Interface • Commonly use: nothing to do with server • Need the common SQL API: there are many! • Need multiple drivers • Required multiple managing stations FAPs
SQL Middleware: Middleware Solution Open SQL Gateway • Standardize more portion on the client • Need the gateway server
SQL Middleware: Middleware Solution Standard SQL Systems • All Vendors have the same things • Big Politic issues
SQL Middleware API • Application Programming Interface • Help developing programs • Embedded SQL & SQL Call-Level Interface
SQL Middleware: API Embedded SQL • SQL within COBOL, FORTRAN, C, … • Defined by ISO SQL-92 • SQL becomes part of the language • After compilation become specific to a single database vendor
SQL Middleware: API SQL Call-Level Interfaces • SQL is not embedded into part of the program • Interface to the “CLI library” is included in the program • Allow users to change database vendors by changing “CLI library”
SQL Middleware: API SQL Call-Level Interfaces cont. • Common Standard is SAG CLI • Contains the SQL style procedure calls • ODBC is grown from SAG CLI • from Microsoft • ODBC 1.0 was slow and buggy • ODBC 2.0 contains 3 portions (core, Level 1, Level 2) • ODBC 3.0 introduce more functions and supporting for Unicode
SQL Middleware: API Combination and Tools • Most tool combine both methods • Visual tools are available • Delphi (PASCAL variant) • PowerBuilder (C++ variant) • Visual Basic (BASIC variant)
Database Servers • Receive request from clients • mixing between standard SQL and proprietary SQL (to enhance performance) • Process the request • create, retrieve, modify, calculate • containing protection for sharing data • Send the result back
Database Servers Database Server Architecture • Process-per-client architectures: servers build one process for one client • Multithreaded architectures: use the concept of multithread instead of multiprocess • Hybrid architectures: combination of both architecture
Database Servers: Database Server Architecture Process-Per-Client Architectures Each user has there own process
Database Servers: Database Server Architecture Process-Per-Client Architectures cont. • Each client has its own environment space • one process dead, other can still work • Good for multiprocessor system • Consume more resource • more memory and CPU resources • slow due to process context switches and interprocess communications • Examples: Informix, Oracle
Database Servers: Database Server Architecture Multithreaded Architectures Sharing same environment space by using the concept of multithread for multitask
Database Servers: Database Server Architecture Multithreaded Architecture cont. • best performance because of running on the same environment address • not rely much on the OS multitasking: cross-platform applications • save memory and CPU resources • one connection down the other gone • Example: MS SQL Server
Database Servers: Database Server Architecture Hybrid Architectures • combination of previous two
Reference Books Client/server survival guide 3rd edition by Robert Orfali, Dan Harkey, Jeri Edwards (John Wiley & Sons: 1999)