1 / 32

Introduction to Database Servers and SQL Middleware

Learn about database servers, SQL fundamentals, middleware solutions, SQL standards, API usage, and dealing with multiple vendor solutions. Explore the importance and impact of database servers in today's technology landscape.

lehmanm
Download Presentation

Introduction to Database Servers and SQL Middleware

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Servers Kittiphan Techakittiroj engktc@au.ac.th

  2. Agenda • Introduction to database servers • SQL: the language for database • SQL Middleware • Database Server Architectures

  3. Introduction to Database Servers • Send one command to server: SQL Request • Server processes the request • Information sent back from server

  4. Introduction to Database Servers Current Situation • Huge Market --> $$ Money $$ • SQL is most common language • SQL is standard, but its extensions is not

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

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

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

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

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

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

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

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

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

  14. SQL Middleware Multi-vendor Solution • Facing Problems: • Different SQL APIs • Multiple database drivers

  15. SQL Middleware Multi-vendor Solution cont. • Facing Problems: • Multiple FAPs and no interoperability • Multipble administartion tools

  16. SQL Middleware Middleware Solution • Try to eliminate the need of “multiple” • Three strategies: • Common SQL Interface • Open SQL Gateway • Standard SQL Systems

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

  18. SQL Middleware: Middleware Solution Open SQL Gateway • Standardize more portion on the client • Need the gateway server

  19. SQL Middleware: Middleware Solution Standard SQL Systems • All Vendors have the same things • Big Politic issues

  20. SQL Middleware API • Application Programming Interface • Help developing programs • Embedded SQL & SQL Call-Level Interface

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

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

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

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

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

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

  27. Database Servers: Database Server Architecture Process-Per-Client Architectures Each user has there own process

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

  29. Database Servers: Database Server Architecture Multithreaded Architectures Sharing same environment space by using the concept of multithread for multitask

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

  31. Database Servers: Database Server Architecture Hybrid Architectures • combination of previous two

  32. Reference Books Client/server survival guide 3rd edition by Robert Orfali, Dan Harkey, Jeri Edwards (John Wiley & Sons: 1999)

More Related