390 likes | 669 Views
ASE136 Providing a Single Database View of a Heavily Distributed Database Architecture using Open Server. Russ Wheaton DBA Advisor rwheaton@fedex.com August 2003. Agenda at a Glance. The System The Problem Possible Solutions
E N D
ASE136 Providing a Single Database View of a Heavily Distributed Database Architecture using Open Server Russ WheatonDBA Advisorrwheaton@fedex.comAugust 2003
Agenda at a Glance • The System • The Problem • Possible Solutions • It’s an ugly baby but it still needs a name. Introducing Abstraction Gateway Server • Open Server for Dummies • Abstraction Gateway Server Architecture • Abstraction Gateway Server WalkThrough • Abstraction Gateway Server Features • Benchmark Findings
The System The Picture Described • Massively distributed database architecture • 12x UNIX hosts • 10 active • 2 fail over • 24x 80 gig distributed databases of schema A • 8x 40 gig distributed databases of schema B • Several central databases schemas (not represented on picture) • Physical distribution based on customer account number. • No other way to a specific database other than via the customer account number. • Cross reference exists in a central “architecture support” database.
The System Why Create such a complex database architecture? • Large Database • Need to reduce size for manageability and performance • Performance • Eliminate bottlenecks by utilizing more resources • Scalability • Need to be able to scale horizontally to meet new requirements and business growth • Fault Tolerance • Protect from single points of failure • Cost • Take advantage of more, smaller, cheaper hardware • Administration • Smaller databases easier to administer than larger ones.
The “New” Requirement • Provide the ability to get data anywhere from the system via: • Tracking number • Customer name • Invoice number • Customer number (which we are already indexed on) • Where there is qualifying data across databases: • Aggregate the data into one common result set. • Sort the data across all databases by a set of predefined sort orders. • Accumulate some financial columns across all rows. • Where the result set across databases is very large: • Provide the ability to limit the number of rows returned to the calling client. • Optionally buffer the data, or truncate it.
Possible Solutions Index on Additional Data • In the routing architecture: • Add the ability to route not only based on customer number, but on any of the other three columns • Complications with this approach: • Would need to build the index data from all existing data. • As data gets archived, the index would need to be kept up to date. • Volume of data for some of the index columns would be close to a billion. • Complex modifications to the routing architecture and calling applications would be required. • Inflexible moving forward. What if we need to index on a new data item? • Complex application architecture required to sort, merge etc when multiple source databases have qualifying result sets.
Possible Solutions Build an Index Server • Using IQ: • Replicate the most commonly sought after fields from ASE to IQ. • Use IQs extraordinary ability to index on any column to return the summary data required, and the source of the data in the ASE environment. • Source database id required if additional data is needed. • Complications with this approach: • Not real time. Data would need to be batch loaded into IQ every X interval. • Second copy of the data to deal with and all those complications: synchronization, archive, maintenance, etc • Additional HW costs required to support the IQ implementation. • Additional SW costs required to support the IQ implementation. • Inflexible moving forward. What if we need to index on a new data item?
Possible Solutions Look Through Database • Build a look through database in ASE with CIS • A look through database is: • A database that has proxy tables to all objects in the environment. • For example, if we have 24 Wheaton tables - one in each of schema A, we will have Wheaton001, Wheaton002 … Wheaton024 in the look through database. • Also, over top of each proxy table set is a UNION view. So the Wheaton0xx set would be abstracted with a union view called Wheaton. • Given this approach: • There is a single view to all data in the system - all clients are in the context of one database only. • Developers use the same tables they are used to in the distributed schemas. • Complications with this approach: • The number of objects in the look through database becomes unwieldy - our system would realize over 3000 proxy tables with hundreds of UNION views.
Possible Solutions Look Through Database - continued • Complications with this approach: • Some queries join several union views. This results in a Cartesian Product. • Union views are serial. So if 32 or 64 or 128 queries are required to satisfy query using the UNION view, all are executed one at at time. • Materialization - Union views may have to materialize data to the calling server to satisfy the query. With large result sets, this can cause problems. • If one table in the union view was not available, bad things happened. • Bottom line with this approach is: • we really liked it • it was simpler and cheaper to do than all the others • but the performance just was not there
Possible Solutions Other solutions we looked at: • Tibco based Request Reply using Business Works 1.0 • Tuxedo based service fan out.
The Last Solution Open Server • The last solution we looked at is an idea I’ve been toying with for years but just have not had the nerve to sit down and try.
The Solution is Born • Abstraction Gateway Server is Born! • Lovingly called AGS for short. I mean lets face it, it ain’t a system if there is no acronym for it!
Open Server for Dummies From The Book • Open Server • Open Server provides the tools and interfaces needed to create custom server applications. • Broadly speaking, Open Server contains two components: a programming interface, in the form of libraries of functions, and network services. • The libraries that make up the Open Server programming interface are: • Server-Library, a collection of routines for use in writing server applications. Server-Library includes routines that: • Listen for commands from clients • Return results to clients • Set application attributes • Handle error conditions • Schedule interactions with clients • Provide a variety of information about client connections
Open Server for Dummies From The Book - Open Server Configurations • Standalone Open Server Application • A client can connect directly to a standalone Open Server application. • The client submits requests to the server using: • Remote procedure calls (RPCs) - these calls allow you to execute registered procedures on an Open Server application. Registered procedures are defined pieces of Open Server code stored by the Open Server application. They can be user-defined or system-defined procedures. • A cursor command. • Any other kind of client command. • The Open Server application programmer supplies code to process client commands. • The standalone Open Server application makes no external requests to respond to a client request.
Open Server for Dummies From The Book - Open Server Configurations • Auxiliary Open Server Application • An auxiliary Open Server application can support Adaptive Server Enterprise by processing RPCs. • The client connects directly to ASE and uses Transact-SQL for its language requests. To execute a registered procedure on the Open Server application, the client prefixes the procedure name with the name of the Open Server application in the Transact-SQL statement, which causes ASE to initiate an RPC. For example: exec OpnSrv211...print_calls • An RPC is the only type of client command that can be sent to an Open Server application directly from an Adaptive Server Enterprise. You can initiate the RPC calls by using stored procedures, triggers, or threshold management in Adaptive Server Enterprise. RPCs give you access to: • Operating system functionality, such as sending e-mail and printing. • Whatever functions you have defined in your Open Server application code. • The Open Server application can return information to the ASE, or back to the client via ASE.
Open Server for Dummies From The Book - Open Server Configurations • Gateway Open Server application • A gateway server enables a client to access a server that may or may not be able to accept the client connection directly. The gateway does not have to connect to an ASE or, for that matter, to any DBMS server. It could connect to a file system or an application program that can act as a server. • An Open Server application that accesses an ASE or another Open Server application includes both Client-Library and Server-Library routines. • It assumes both client and server roles. • In the server role, it uses Open Server to interface with clients. • In the client role, it uses Client-Library routines to send requests to, and receive results from, an ASE or another Open Server.
Open Server for Dummies For Free From Open Server • Complete Server Architecture • No network programming required. • Signals handled by Open Server. • Multi-Threading handled by Open Server. • Structures like Queues readily available. • Support infrastructure in place (for example you can run an sp_who, sp_ps, etc to monitor and support an Open Server application) • Open Server handles TDS for you! • Open Server can be configured to handle security for you. • Common services like logging are built in. • Mutex APIs provided for you.
Walkthrough Step 1 - Client Connects WebLogic Server
Walkthrough Step 2 - WebLogic Connects to AGS WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE. If there isn’t already a pooled connection open and available to AGS, WebLogic will establish one.
Walkthrough Step 3 - AGS Spawns connections to each data source. WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Walkthrough Step 4 - GUI Sends SQL WebLogic Server SQL Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Walkthrough Step 4 - GUI Sends SQL WebLogic Server SQL Abstraction Gateway Server Looks, Feels, and Smells like an ASE database.
Walkthrough Step 4 - GUI Sends SQL WebLogic Server Abstraction Gateway Server SQL Looks, Feels, and Smells like an ASE database.
Walkthrough Step 4 - GUI Sends SQL WebLogic Server Abstraction Gateway Server SQL Looks, Feels, and Smells like an ASE database.
Walkthrough Step 5 - AGS Processes the SQL in Parallel Against all target databases WebLogic Server SQL SQL Abstraction Gateway Server SQL Looks, Feels, and Smells like an ASE. SQL SQL SQL SQL SQL
Results Results Results Results Results Results Results Results Walkthrough Step 6 - Results are generated and sent back to AGS in Parallel WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Results Results Results Results Results Results Results Results Walkthrough Step 6 - Results are generated and sent back to AGS in Parallel WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Results Walkthrough Step 7 - AGS Merges the Results WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Results Walkthrough Step 8 - AGS Sends the Results back to the Client WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Results Walkthrough Step 8 - AGS Sends the Results back to the Client WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
Results Walkthrough Step 8 - AGS Sends the Results back to the Client WebLogic Server Abstraction Gateway Server Looks, Feels, and Smells like an ASE.
AGS Features • Can optionally sort the result set • In this case, it will stage the data in AGS and use the STL sort() function before sending the data back to the client. • When sort is turned off, AGS goes into “Pass Through” mode and sends the data back to the client as it receives it, terminating the whole mess when the last thread reports in. • Can handle stored procedures or language queries. • Database driven interface for configuration of data sources. • Database table to audit all activity.
AGS Features • Developers can develop against a normal ASE database, and seamlessly deploy to AGS. • All interfaces are the same as ASE: • SQL or Stored procedures for data access • Connect with JDBC, ODBC, ct-library • AGS is “Business Rule” free - it is 100% an architecture component. • Makes it transportable between business requirements. • May use it for a GUI upon initial deployment, then have back end jobs use it as an index server. • You can also add custom business logic to AGS if you need to. • Security can be custom or you can have ASE authenticate login via srv_getloginfo, ct_setloginfo, ct_getloginfo, srv_setloginfo • Secure connections are also available.
AGS Features • Multiple Abstraction Gateway Servers can be deployed to service disparate business requirements. • Because it is build on Open Server and Open Client, you can run AGS anywhere you deploy those libraries to.
Benchmark Findings Seconds Rows in Result Set