Thursday, February 21, 2008

Today's Client Question: SQL Anywhere vs ASE

Q: One of my customers is insisting on changing the database from SQL Anywhere to ASE just because ASE has the word "Enterprise" in its name. Can you provide any ammunition to me why SQL Anywhere is as much Enterprise as ASE?

A: I've written the answer in the form of a FAQ...

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; see SQL Anywhere Performance Benchmarks.

Q: Why don't more companies use SQL Anywhere for their enterprise databases?

A: One reason is that many companies think they are in the top 10% when they actually aren't.

Q: How big a database can SQL Anywhere handle?

A: There are many successful production SQL Anywhere databases that measure in the tens and hundreds of gigabytes in size.

Q: How many users can SQL Anywhere handle?

A: There are many successful production SQL Anywhere databases with hundreds and thousands of active connections.

Q: Why don't I hear about SQL Anywhere?

A: Many companies embed SQL Anywhere in their own products, and they take a "stealth" approach: they don't advertise the fact they are using SQL Anywhere to their own customers or the public at large.

Q: Why can't I find people with SQL Anywhere experience?

A: SQL Anywhere is so easy to learn and use that many companies take a "do it yourself" approach. Staff with SQL Anywhere skills often have other important duties; their DBA role is part-time.

Q: Why can't I find books about SQL Anywhere?

A: Because it's easier to use than other products, and the Help is so good, there isn't the same market for books. There is one recent book: SQL Anywhere Studio 9 Developer's Guide, ISBN 1-55622-506-7.

Q: Why should I pick SQL Anywhere over ASE?

A: For many reasons:

  • SQL Anywhere has a more powerful procedural SQL language. That means when appropriate, it is possible to push complex logic down into the database in an efficient manner: efficient for the developer to write, efficient for the engine to execute. SQL Anywhere has scheduled and triggered events, row-level triggers, local blob variables, global temporary tables, persistent local temporary tables, user-defined connection-level variables, user-defined SQL functions with no artificial semantic limitations... the list goes on and on.

  • SQL Anywhere has a more powerful SELECT statement, including local views via the WITH clause, the LIST aggregate function with ORDER BY, the recursive union for hierarchical queries, modern OLAP query operators, ORDER BY clauses on derived tables... this list goes on and on as well.

  • SQL Anywhere has great tools: stored procedure and trigger debugger, capturing of expensive queries, the index consultant, the graphical plan display.

  • SQL Anywhere has simpler administration. You won't get panic calls "The transaction log is full!" Installation is easier, configuration is easier, performance tuning is easier (and mostly unnecessary), backup is easier, recovery is easier, migration from one platform to another is easier (even to different-endian setups: install the software, copy the database file, start the server).

  • SQL Anywhere has a lower price tag, not just for licensing but for all those DBAs you do not need to hire. Everything comes "in the box"... no need for third-party tool purchases, no need to buy another DBMS to do OLAP, or another product to do replication.

  • SQL Anywhere has very bright future. The SQL Anywhere engineers definitely "get it", they understand what people need from a DBMS, what the default settings and default behavior should be, why the ANSI standards are important... they do things the way they should be done. SQL Anywhere has always had many important features before the other guy; examples include declarative referential integrity, row-level locking, scrollable cursors and the LEFT OUTER JOIN as an alternative to the unpredictable *= operator.

Add to Technorati Favorites


Anonymous said...

I know there is software that does what I need and software that does not... and what does "Enterprise software" mean ;) ?

Breck Carter said...

to stuffittrans: I like Wikipedia's definition of "Enterprisey" :)

Seriously, in this context, to me, "Enterprise" means "good enough to run anything the company needs to run."

Anonymous said...

Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ Architectures

Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ are complementary products, with architectures designed to suit their distinct purposes.

Sybase IQ is a high-performance decision-support server designed specifically for data warehousing and analytic processing. SQL Anywhere works well as a workgroup or departmental server requiring little administration, and as a personal database. Adaptive Server Enterprise works well as an enterprise-level server for large databases, with a focus on transaction processing.

This section describes architectural differences among the three products. It also describes the Adaptive Server Enterprise-like tools that Sybase IQ and SQL Anywhere include for compatible database management.

Anonymous said...

BTW. Above comment is extract from Sybase web-site.

To me, this clearly indicates SQLAnywhere is not intended for enterprise level use.

Breck Carter said...

@Anonymous: That extract comes WAY down inside the documentation for Sybase IQ 15.1 where it describes "Compatibility with Other Sybase Databases":

It is a wild stretch, verging on the foolish, to interpret "works well as a workgroup or departmental server" to mean "not intended for enterprise level use".

But if you look harder, you will be able to find A LOT MORE marketing bumpf that desperately tries to keep up the fiction that an overpriced and underfeatured DBMS is required to run an "enterprise"... it's all about the money :)

Thruth Hurts said...

Come on, that's a lame excuse why you won't find more than one book on SQL Anywhere or ASE. And why developer won't mention that on their CV is because all DBMS software currently on the market are pushing the envelop on innovation compare to those you endorse period. Even the upper coming DBMS like PostgreSQL or MongoDb are currently braking boundaries on the rapid functionality expansion and rapid delivery of updates. Simple things like spacial data is non existent in SQL Anywhere and some data-type are difficult to work with due to size limit imposed and truncation issues users experiences daily just attempting to read tables from the editor. These are just simple things that could make you thing that these brands are not to be taken seriously and binned immediately.

Breck Carter said...

@Thruth Hurts: Vodka + Keyboard = Speling Misteaks, like "Thruth" and "upper coming" and "braking boundaries" and "spacial data"... but the booze doesn't explain ignorant claims like spatial data being non existent in SQL Anywhere (it's been there for many years)... Vodka + Clueless = You :)