What Does "Enterprise Database" Mean?

Back in February I answered this question in "SQL Anywhere vs ASE":

Q: Is SQL Anywhere an enterprise database?

A: For 90% of the world's enterprises the answer is "most definitely yes!" SQL Anywhere is much easier to administer and develop for than other products, and advances in computer technology mean that performance is no longer the serious concern it used to be: maintenance and development costs are. You can buy a 3Ghz computer with 2G of RAM and a 500G disk drive for less than U$1000, and that's enough power to run an enterprise database in many companies.

For the other 10% of the world's enterprises, performance problems still require huge sums of money to be spent on equipment, software and especially support. New features in SQL Anywhere are pushing the envelope, however; for example, version 10 introduces intra-query parallelism and hot failover using database mirroring.

SQL Anywhere is now being marketed as an enterprise database solution, as well as a solution for mid-level and embedded applications. Millions of rows, thousands of busy users, hundreds of gigabytes, no problem.

Here's the real question:

Exactly what is an "enterprise-level" database?

For OLTP systems, the answer might be a simple measurement:

The size of the workload that can be successfully handled by the database, expressed as the the number of simultaneous busy users, where databases handling workloads above that can be called "enterprise" and those below that are called something else, like department-level.

What do you think that threshold is? Here's my answer:

The threshold for "enterprise database" is a number in the range of 100 to 10,000 simultaneous busy users. Anything larger than 10,000 and you're excluding most of the world's enterprises. Anything smaller than 100 and you're not acknowledging that large systems really are qualitatively different.

Let's consider some government statistics... in paticular, let's look at the US Census Bureau figures for 2005 showing the number of companies with...

fewer than 20    20-99     100-499     500+
  employees    employees  employees  employees     
-------------  ---------  ---------  ---------
  5,357,887     520,897    87,285     17,477

Let's leave out all the small companies, and only look at those with 100 or more employees:

83% of companies with 100 or more employees have fewer than 500 employees.

Let's have another look at those big companies, the ones that have 500 or more employees: they actually employ over 57 million people, but the average is only 3,300 employees per company.

Here's another list: Fortune 500 2006 Top Companies by Employees shows that the top 50 employee counts range from 1.9 million for first place (Walmart) down to 122,200 for 50th place (Northrop Grumman).

Now, "number of busy users" is not the same as "number of employees". Sometimes, a company's database will have more busy users than they have employees; e.g., an airline reservation system has many busy users outside the organization. On the other hand, many internal enterprise databases are used by a small minority of the company's employees, and an even smaller number are busy at the same time.

But the real problem is this: You're not going to find any public ranking of enterprises by numbers of database users.

So you're going to have to take a guess at the threshold for "enterprise database", and I'm going with the range of 100 to 10,000 busy users. For many systems, that corresponds to a range of 1,000 to 100,000 actual users since not all users are busy all the time.

Techwave 2008 Notes

It's the last day of Techwave 2008; here are some random notes...

The free SQL Anywhere 11 Web Edition has a new FAQ here.

So far, Eric Farrar's presentation "Offline Wikipedia with SQL Anywhere" has been my favorite, not just because of the application but because of the many problems he solved and the tips and techniques he included. Like shrinking a database by copying tables and swapping dbspaces instead of running unload/reload.

I learned that using -gn 1 or 2 IS a good idea if you want to dedicate a SQL Anywhere database engine to running a single process... like building a full text index on a huge table.

Lotsa new buzzwords floating around: zero touch, bare metal, management stack, granular control, tasked with, enable credibility, platform innovator, snap in, conduit DMZ...

I want to write an article "Top 10 Reasons Full Text Search Is Cool". The number one reason is probably going to be "It's built right in to the SQL Anywhere query engine"... followed by "It's not using Verity" in case anyone didn't get the first point.

IvanAnywhere didn't make it to the conference. His videos were playing, though... a kind of remote telepresence for a remote telepresence.

More to come, eventually...

Comparing Database Schemas

[ An improved version of this article may be found here. It shows how to use this technique on all database versions 5.5 through 11. ]

---

Question: How do I compare the schemas of two SQL Anywhere databases?

Answer, from Bruce Hay at Sybase iAnywhere: Check out the new "-no" option for dbunload in SQL Anywhere 11 (unload objects in name order, no data)? It's intended for this purpose.

---

Here's how it works:

• Step 1: Run dbunload against the first SQL Anywhere database to create a text file containing CREATE TABLE and other schema definitions. The new -no option tells dbunload to unload just the schema, not the data, and do it in name order so it will be easier to compare with another text file sorted in the same order.

• Step 2: Run dbunload again, on the second SQL Anywhere database, to produce another text file.
  
  These dbunload steps only work with the SQL Anywhere Version 11 copy of dbunload.exe, and they only work on Version 10 and 11 databases running on the Version 11 database engine.

• Step 3: Run a text comparison utility like Compare It! to show the differences between the two text files.

Here's an example of Windows commands that compare the Version 10 and 11 demo databases:

"%SQLANY11%\Bin32\dbunload.exe" 
   -c "ENG=demo10;DBN=demo;UID=dba;PWD=sql" 
   -no -r c:\temp\demo10.sql

"%SQLANY11%\Bin32\dbunload.exe" 
   -c "ENG=demo11;DBN=demo;UID=dba;PWD=sql" 
   -no -r c:\temp\demo11.sql

"C:\Program Files\Compare It!\wincmp3.exe" 
   c:\temp\demo10.sql c:\temp\demo11.sql

Here's what Compare It! shows for one difference:



Compare It! can also produce a report in HTML format:



Here's why using dbunload is a good approach:

• The dbunload utility doesn't miss anything; it unloads every single schema object in the database... tables, views, procedures, triggers, everything.

• The dbunload utility is always going to be more up-to-date than any other software except the SQL Anywhere engine itself. In other words, even PowerDesigner falls behind dbunload in terms of supporting SQL Anywhere syntax for new features.

• Running dbunload on actual databases guarantees you will be comparing the current database schemas, not out-of-date information stored is separate model files.

• The text file comparison view presents differences in terms of actual database syntax, not some funky abstract logical view used by data modelling tools.

SQL Anywhere Is Free!

The free "Web Edition" of SQL Anywhere 11 is a pretty good deal:

• no charges

• no limits on server functionality

• no limits on the number of users

• MobiLink is included, and so is UltraLite
  

It's called the "Web Edition" for a reason, however: all end user interactions must originate from a web browser. In other words, it's not a "free client server edition". And it's not a "free embedded OEM edition" either.

You can download the Web Edition from here.

When you install it, you will see not one, but two license agreements, the second one containing all the fine print specific to the Web Edition. Section 3 contains the interesting bits, and I've pasted it below so you can see what you're getting in to.

3. Use of Program.

3.1 Web Server Database. Each instance of this SQL Anywhere Web Edition permits you to access the SQL Anywhere Server component from an unlimited number of Seats, and may include internal users within the Customer's organization and external users outside the Customer's organization, provided that (a) except as specifically authorized below, all access, direct and indirect, to the SQL Anywhere Server originates from a Web Browser; (b) the primary purpose of the SQL Anywhere Server is to support interactive Web Browser applications; and (c) the SQL Anywhere Server is installed on only one Web Server.

3.2 Synchronization. Each instance of this SQL Anywhere Web Edition permits you to install and use the MobiLink Synchronization Server component, provided that (a) the MobiLink Synchronization Server consolidated database is the SQL Anywhere Server described in Section 3.1, a SQL Anywhere Server with a valid purchased License, or a MySQL database server; (b) all synchronization requests to the MobiLink Synchronization Server are from SQL Anywhere UltraLite components described in Section 3.3; and (c) the MobiLink Synchronization Server is installed on only one computer.

3.3 Local Web Database. Each instance of this SQL Anywhere Web Edition permits you to distribute and install the SQL Anywhere UltraLite component on an unlimited number of Seats, and may include internal users within the Customer's organization and external users outside the Customer's organization, provided that (a) all access, direct and indirect, to the SQL Anywhere UltraLite component is from a Web Browser; (b) the sole purpose of the SQL Anywhere UltraLite component is to support interactive Web Browser applications; and (c) data stored in the SQL Anywhere UltraLite component is synchronized using the MobiLink Synchronization Server described in Section 3.2.

3.4 Administration, Maintenance, and Data Import/Export. You may install and use the SQL Anywhere client software and tools on additional Seats solely for administration and maintenance of the SQL Anywhere Server described in Section 3.1 and the MobiLink Synchronization Server described in Section 3.2. Administration and maintenance may include, but is not limited to, importing or exporting of data in the SQL Anywhere Server, or running non-interactive reporting applications.

3.5 Diagnostic Tracing. When using the Application Profiling feature in SQL Anywhere, you have the option of storing tracing data within the database being profiled or in a separate tracing database stored on a separate machine. You may install the SQL Anywhere Server components on a separate Server solely for use with the tracing database used in conjunction with the Application Profiling feature. No other components may be installed on the separate Server, and the SQL Anywhere Server may not be used to process other applications, without obtaining an additional license.

3.6 High Availability Option: SQL Anywhere has a High Availability Option where an additional SQL Anywhere server runs on a secondary computer for Use in the event of a failure of the primary server. A SQL Anywhere High Availability Option deployment is only permitted if applicable licenses are purchased.

3.7 MobiLink Server Farm. A MobiLink Server Farm is an environment where more than one MobiLink server is synchronizing the same set of remote databases with one application or database management system. A MobiLink Server Farm deployment requires MobiLink to run in shared state mode and is only permitted if applicable licenses are purchased.

3.8 Security Option. RSA Encryption is included in the SQL Anywhere Web Edition. Software for Encryption using ECC or FIPS-certified technology, which may be included with the Program, may not be installed or used without the purchase of a separate license at an additional charge. If the needed license has been purchased, the Encryption may only be used in conjunction with Sybase SQL Anywhere. You are advised that these Encryption Programs and related Documentation are cryptographic in nature and therefore are highly regulated. Obtaining any necessary export or import approval for any of your applications or the Security Options or Documentation is your responsibility.

3.9 Sample Code. Sample Code is provided AS IS, without warranty or liability of any kind. You may use, reproduce, modify and distribute the Sample Code that is provided without limitation, on the condition that you retain the copyright notice and disclaimer as to the original iAnywhere code.

SQL Anywhere 11 Makes Top Ten TPC-C

For the first time Sybase iAnywhere has published a TPC-C benchmark test using SQL Anywhere, and that benchmark test made it on to the Top Ten TPC-C list.

TPC stands for Transaction Processing Performance Council:

"The TPC is a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry."

TPC-C is the name of one particular test, The Order-Entry Benchmark:

"As an OLTP system benchmark, TPC-C simulates a complete environment where a population of terminal operators executes transactions against a database. The benchmark is centered around the principal activities (transactions) of an order-entry environment. These transactions include entering and delivering orders, recording payments, checking the status of orders, and monitoring the level of stock at the warehouses."

Test results are ranked two different ways, one absurd and the other meaningful.

First the absurd: If you have $17,111,788 to spend, and you've got room for 11,000 disk drives, you too can own the top ranked system in the Top Ten TPC-C by Performance list. With that system you'll be able to process 6,085,166 transactions per minute submitted by your 5,184,000 users.

In other words, cost is no object... like I said, absurd.

The meaningful list includes cost in the ranking, and this is the list where SQL Anywhere 11 appears: Top Ten TPC-C by Price/Performance...



With a figure of 85 cents per transaction per minute, SQL Anywhere doesn't just break into the Top Ten, it debuts at Number Five!

This is huge news, for many reasons:

• The test handled 16,500 users. That's more than most real-world enterprises. SQL Anywhere isn't just a departmental database any more.

• By going public with this benchmark, Sybase iAnywhere shows that it is serious about SQL Anywhere performance and that it is willing to compete with Oracle, SQL Server and DB2.

• The initial release of SQL Anywhere 11 was used, not some version that's been tweaked and tuned for running TPC-C.

• The total system cost for the hardware, software and support was $17,540.16. That means SQL Anywhere ranks Number One in total system cost by a very wide margin: $20,000 cheaper than the next cheapest system in the Top 10 price/performance list.

• Economies of scale apply to larger, more expensive hardware configurations, not the affordable setup used for SQL Anywhere 11. The other 9 systems in the Top 10 price/performance list cost an average of $166,895.11 versus $17,540.16 for SQL Anywhere, and that makes a 5th place showing even more impressive.

• The test achieved a rate of 20,705 transactions per minute. That's a great number, especially when viewed from a historical perspective: In 1997 the best cost-is-no-object performance was only 30,390 transactions per minute, and best price/performance was $42.53 per transaction per minute... fifty times higher than SQL Anywhere's figure of $0.85 per transaction per minute.

Here's another view of the Top Ten list. The left side ranks price/performance with SQL Anywhere's $0.85 per transaction per minute shown in red. The right side shows how SQL Anywhere's total system cost of $17,540.16 compares so very favorably with the other systems, especially the half-million dollar systems that didn't fare too well when it came to economies of scale:

Top 10 Cool New Features in SQL Anywhere 11

Read it here.