The Invisible Database

Today, June 2, 2011 marks my 25th anniversary with “the company”. I put “the company” in quotes, because it is somewhat indicative of the technology industry that my “company” has undergone several name changes over the years:

• WATCOM
• Powersoft



• Sybase
• iAnywhere Solutions
• Sybase iAnywhere
• Sybase, An SAP Company

Those of us who have been here a while like to say: “Same office, many different company names”.

When I started 25 years ago, on June 2, 1986, I was working in our Technical Support and Marketing team. In WATCOM, the customer support team was part of Marketing. The idea was that word-of-mouth comments from happy customers was the best marketing you could have. We were a small company with fewer than 50 people total, so my team actually handled ALL interactions with customers, including technical support, pre-sales support AND actual sales. I think this unique approach was fundamental to the development of the unique attitude toward customers that still survives here in our Waterloo office.

Because our support team WAS the marketing team, and I had previous database experience, I became involved during the earliest stages with our new WATCOM SQL product. I remember traveling to Boston for the Database World tradeshow in June 1992 to launch WATCOM SQL. Our product logo was our trademark lightning bolt. Prior to this time, WATCOM was primarily known for language products such as WATCOM C, but not for databases. We were well known in the development tool market, but pretty much unknown in the database industry. If I am not mistaken, our booth was directly beside one of our primary competitors at the time: Sybase.

Of the 7 of us who traveled to Boston that week, 5 of us are still here, and still working on SQL Anywhere! (Dave Neudoerffer, Jim Graham, Brian Vink, Terry Stepien, and myself). I clearly recall Terry and I walking around the show floor talking to the various database tool vendors, encouraging them to add support into their product for WATCOM SQL. In particular, I remember watching the demonstration at the Powersoft booth of their new tool called PowerBuilder.

In 1994, WATCOM was acquired by Powersoft, and then became part of Sybase in 1995. We changed our database product name from WATCOM SQL to Sybase SQL Anywhere shortly thereafter, and we have never looked back.

As I mentioned above, many of my colleagues from those early days are still here. The absolute greatest aspect of working here for 25 years has been the many wonderful people I have been blessed to work with over the years. I am honored to be able to come in each day and work side by side with some of the smartest people in the world. I am constantly being challenged to learn new technology, to find new ways of explaining what we do, to break down barriers to help our customers, and to encourage others to let their passion shine through.

Thank you.

I have recently been giving a great deal of thought to the important interactions between our engineering team and our customers. The question that I am always asking is “How is our team using the information they derive from these interactions to innovate new products and technology?” I am convinced that understanding these interactions is crucial to the development of great software that is valued by customers.

When I look back in history and pull out those products and product features that were really successful, they all share a common trait: they were not invented by our customers, but they were invented by our developers who worked very closely with our customers to understand the real customer problem. Let me repeat that in a slightly different way:

Our most successful products were not invented by our customers. They were invented by our own developers who worked very closely with our customers to understand their real needs. These developers were in constant communication with a small number of customers (often just one), and created something new to solve that customer’s problem. Once solved, they then worked closely with another customer to enhance the software to solve that customer’s problem. After 3 or 4 similar iterations, the software was able to solve many customer needs, and was launched in a larger way.

Viewed from a different perspective, another truth emerges: that our most spectacular product failures were when our developers sat in our lab and invented what they “thought” a customer might want, without actually working with a customer to solve their very particular problem. Usually this process was part of a “strategic initiative to expand our business into other markets,” where we hoped we would find new customers.
[Read more →]

Customers have frequently requested that we add a switch to the SQL Anywhere Server to make it run faster in their particular environment. Here in Waterloo, we have taken to calling this the mythical “dash go_fast” option. It is frequently a partner with the “dash no_crash” option.

While it would certainly be handy to have a simple option to make the server go faster, one wonders why we wouldn’t make it the default! It would seem that some people might believe that we were holding some level of performance in reserve waiting for the smartest or most informed customers to make a simple change to their command line. REALLY?

To me, this seems like the perfect subject for an April 1 post. In North America, April 1 is sometimes referred to as “April Fools Day“.

Of course there is no magic “go_fast” option. SQL Anywhere is renowned for having intelligent defaults, so you don’t have to be an expert with the Midas touch to achieve good performance. Even so, there are times when you do want your system to go faster. That is the time when the hard work of tuning your design, your schema or your application needs to be done to achieve better performance.

My colleague Glenn Paulley has written many times about various performance and tuning aspects of database applications, including most recently his post on the Seven Deadly Sins of Database Application Performance.

As well, there are several whitepapers available on sybase.com that describe various aspects of tuning database performance. Glenn has pulled many of them together in a nice list on the Whitepapers page of his blog. Here are a select couple that reference performance:

• Diagnosing Application Performance Issues with SQL Anywhere: This whitepaper presents a method for troubleshooting performance problems in SQL Anywhere. It describes the nature of performance problems and classifies them into categories of CPU-bound, I/O-bound, and concurrency-bound problems. We describe the steps a DBA should take to classify a performance problem, the tools provided in the product to examine the problem, and the analysis that must be done to resolve it.
• Optimizing Adaptive Server Anywhere Performance Over a WAN: This document discusses tuning performance for an Adaptive Server Anywhere network server (version 8 or higher) that is running over a WAN. TCP/IP is the protocol of choice for WAN implementations and is the main focus of this document. Note that network performance tuning is an iterative process of determining what works best with a particular application and network.
• Improving Performance in SQL Anywhere: So you want to improve performance? Join the club. Performance is an issue on everyone’s agenda, since faster and better mean more effective, and ultimately, more profitable. While Adaptive Server Anywhere is designed to give you great performance right out of the box with very little intervention on your part, there is always room to fine tune your database to give you even better results.

If you find yourself wishing for a magic “-go_fast” option, perhaps you should review one or more of these whitepapers!

Today, we published a detailed technical whitepaper: “SQL Anywhere I/O Requirements for Windows and Linux.” This paper grew out of many discussions with customers who occasionally have problems with SQL Anywhere database files. Database servers have a fundamental requirement that the database file is stored in a stable way to enable durability guarantees to be made. (Durability is the “D” in ACID) It is the REASON people use an RDBMS. Here’s an excerpt from the introduction to the paper:

Database servers need to be able to guarantee that data gets to stable storage to ensure that committed transactions persist and to properly implement recovery in case of power loss. Operating systems and storage devices cache and reorder write operations in order to improve performance. Running a database server on an improperly configured system can lead to data loss and file corruption.

It is important that anyone who uses SQL Anywhere to store mission critical data understand the challenges that may exist in the configuration of some systems when wanting to make the durability guarantee. The paper describes the internal steps that the SQL Anywhere Server uses to ensure the durability of the database. It then describes a typical system’s storage stack, and the various complications the various layers add, and the problems with mis-configuration in any of the layers.

The paper concludes:

SQL Anywhere has a single, modest requirement of the storage stack provided by the operating system. The flush call must guarantee that all writes issued before the call are on stable storage once the call completes. When you use a disk with the write cache enabled, a number of conditions can make the storage stack unstable.

I recommend this paper as required reading for EVERYONE who wants to ensure the durability of their data.

On Friday, we made the switch, and the new SQL Anywhere web forum http://sqlanywhere-forum.sybase.com is now in production.

I would like to publicly thank Breck Carter for all he has done to foster the initial forays into a SQL Anywhere technical web forum. Through his efforts, the community is quite active, with over 690 questions and answers in the new forum. My previous post gave some background on how we created the forum.

I would also like to thank Graeme Perrow for all of his efforts to get this forum up and running within Sybase, using SQL Anywhere as its database.

If you haven’t had a chance, check out the new forum at:

http://sqlanywhere-forum.sybase.com.

Last week we launched the beta for the new look SQL Anywhere forum at http://sqlanywhere-forum.sybase.com. Many people are wondering how it works, so I’ve put together this blog post to describe some of the details.

As I mentioned last week, it was just over a year ago that Breck Carter decided to try a new approach and created a web forum using the StackExchange platform. Breck called the new venture “SQLA“.  When the StackExchange folks changed their direction, we undertook a project to create a web forum based on the SQLA idea, yet removes the requirement for StackExchange.

There were two guiding principles for our efforts:

1. We had to control the system so we could make promises to our customers that were within our power to live up to.
2. SQL Anywhere had to be the database server behind the system.

One of the first decision points was the question of building our own system, or using one of the open source alternatives that had emerged. We chose as our starting point the system from OSQA. The OSQA system has much of the same look and feel from the StackExchange platform, yet was open source, so we could get a copy of the source code in house. OSQA is written in Python and uses the Django interface, both of which are supported by SQL Anywhere. When we first pulled down the source from the OSQA web site, we literally had it working with SQL Anywhere within the first 1/2 hour. It really was that easy!

(More info on Python and Django here, and here. More info on SQL Anywhere’s support for Python here, and on the SQL Anywhere Django driver here.)

The next step in creation of the site was to make decisions on things like the name, the url, the logo, and the license. As you can imagine, some of these decisions had to be revisited after our acquisition by SAP.

At the same time, we were working with Breck to get dumps of the data from SQLA, and using the necessary scripts to load that data into the new OSQA / SQL Anywhere database.

Another effort that happened was the development of several new features:

• Threaded comments.
• Syntax highlighting.
• Promotion of comments to Answers.
• Ability to attach arbitrary files. OSQA already supported the insertion of images into the text, but it did not support other file types.
• Changed the text search feature from the simple “find a string” method that OSQA had to one that uses SQL Anywhere’s full text search feature within the database.

These features are all done in such a way that we can submit them back to the open source community. One new feature that will remain unique to our site is the ability to authenticate using a MySybase account.

We also spent significant effort on performance analysis of the system to make sure the system would scale. It does.

All of this work was done by Nathan Auch and Graeme Perrow.

The final effort was to work with our own Sybase IT group to setup a suitable machine, and make it available in Sybase’s production DMZ, so it is visible to everyone. Our machine is running Red Hat Enterprise Linux Server release 5.4, and uses an Apache web server.

Over the years, the SQL Anywhere focused NNTP newsgroups have been among the most used of all Sybase newsgroups. To date, there have been over 60,000 posts to these newsgroups. A very active user community has built up around our newsgroups to help answer questions for users and developers, aided and assisted by active Team Sybase customers and Sybase employees. I have often searched the newsgroups for answers to technical questions myself. Without hesitation, I direct any sales prospects to visit the newsgroups for the good, the bad, and the ugly about SQL Anywhere. I am not afraid of the truth, even when it hurts, because overall, I think SQL Anywhere is a great product, and our community of users is the best.

Just over a year ago, one of our long time SQL Anywhere focused “Team Sybase” members, Breck Carter decided to try a new approach and created a web forum using the StackExchange platform. Breck called the new venture “SQLA“. Without much advertising outside of his own blog, the SQLA web forum grew. It attracted many of the same newsgroup community members, as well as newer customers who find a web based approach much easier to use than the old NNTP approach. Sybase welcomed the creation of this site, and we were happy that it was community driven.

We would have been happy to allow SQLA to continue to grow on its own using the StackExchange platform, however in April of last year, that all changed. The StackExchange folks decided to completely change their approach, and prepared to pull the plug on the SQLA community, to “make the Internet a better place to get expert answers to your questions.” We don’t agree.

Seeing that a web forum was proving very popular, we decided that we couldn’t let SQLA simply die. As a result, the SQL Anywhere team undertook a project to create a web forum based on the SQLA idea, yet sponsored by Sybase in a way that removes the requirement for StackExchange.

Today, we are very pleased to announce that the new SQL Anywhere Forum Q&A site is live and in beta test at http://sqlanywhere-forum.sybase.com/. All the questions from the existing SQLA site have been loaded into the new site. Existing users of SQLA are able to claim their userid on the new system, and retain their accumulated “reputation” points. If you are a current user of SQLA, please see details here.

Check out the new site, and provide any feedback you may have right in the site itself. In my next post, I will describe how we created the new SQL Anywhere Forum, and how it works.

In my post on January 24, I described my belief that the first step for an ISV who is considering how to offer a cloud version of their application is to choose the most appropriate database infrastructure. One key concern I didn’t mention in that post was that ISVs need to consider what options their DBMS offers for high availability (HA) Often, an ISV’s application will be mission critical to their end-user customers, and needs to be available 7×24.

While many end-users may be reluctant to spend extra money for onsite HA solutions, ISVs implementing a cloud environment have the unique opportunity, perhaps even the mandate, to ensure their system is protected against hardware failure. There are certainly many aspects of a high availability system that are beyond the database software; including storage infrastructure, networking, and server hardware. Even taking all these items into account, there still needs to be support in the DBMS for high availability.

SQL Anywhere is an example of one such DBMS that offers full support for high availability. In a running SQL Anywhere HA environment, 3 machines are utilized:

• 1 system to act as the primary database server
• 1 system to act as the standby mirror server
• 1 system to act as the arbiter

In this setup, if anything happens to the primary database server, the standby mirror server automatically takes over, becomes the primary server and begins to handle new connections and requests. The database is guaranteed to have all committed transactions from the primary. Any uncommitted transactions are automatically rolled-back. When the other machine restarts, it is automatically updated to the current state, and it assumes its new role as the standby mirror.

There are several nice characteristics of SQL Anywhere’s HA solution:

• Any supported platform and operating system can be used. For example, one of the machines could be running Windows while the other 2 machines are running Linux.
• There are no special hardware requirements. Each of the machines could be a standard off-the-shelf system.
• Systems that are acting as the standby mirror server or arbiter for one database could be acting in another capacity for a different database. As an example, in an multi-tenant ISV cloud setup, one of the servers could be acting as a mirror for one tenant, and as the primary for another tenant.
• Applications can connect to the mirror server in read-only mode, offloading read-only reporting queries from the primary server.

You can read the complete details of SQL Anywhere’s High Availability implementation on DCX here.

I am pleased to note that our 12.0.1 maintenance release is now available on the Sybase web site. Versions for Windows and Linux are available for download. Versions for our other supported platforms will become available over the next month, as our QA team completes their efforts.

SQL Anywhere Version 12.0.1 offers a roll up of bug fixes and performance enhancements for current 12.0.0 customers, while providing some additional capabilities to attract new customers. A complete list of the enhancements in 12.0.1 is available on our DocCommentXchange system.

Some of the important enhancements include:

• UltraLite
• Support for Android – See the Sybase press release from earlier this week: Sybase Speeds Enterprise Adoption of Android
• Dynamic cache sizing
• MobiLink
• Sybase IQ as consolidated database
• SQL Anywhere Server
• Significant performance improvements in spatial data operations

Anyone can access the Developer Edition download here.

When considering the database server capabilities that make the concept of an On-Ramp to the Cloud and an Off-Ramp from the Cloud possible, SQL Anywhere exhibits many of necessary qualities, especially for ISVs.

As I mentioned in my previous post, one of the key capabilities required is the ability to easily move the database files around; to different platforms and to different machines. This fundamental property enables an ISV to architect the most appropriate cloud data infrastructure for their customers, and to easily modify the system when needs change over time.

SQL Anywhere uses a self-contained database model, where each database contains everything; the schema, user information, permissions, stored procedures, indexes, and of course, the data. Because each database is independent, it can easily be moved from one machine to another machine. There is no “master” database to worry about. By default, each database consists of 2 files: the database file and the transaction log file. If it becomes necessary because of size constraints to split the database over multiple hard drives, the database file can consist of multiple files.

A further capability of SQL Anywhere is that database files are binary compatible across all supported operating system and hardware platforms. This means that a SQL Anywhere database can be easily moved from one supported system, to any other supported system. For example, you can simply copy a database from a Windows machine to HP-UX machine, and then later to a Linux X64 machine, and use it with SQL Anywhere on that machine, without worrying about any endian issues.

The last piece of the puzzle is that a single SQL Anywhere server can run with multiple independent databases at the same time. I’ve written previously about the “Shared Database Server, Separate Database” approach for managing multiple tenants on one machine. Using this approach, the ISV can easily load balance their system by moving heavily used databases to its own server machine. As well, if the customer chooses to migrate to an on-premise installation, the database file simply needs to be transferred to the customer’s server machine. Another advantage of this system is that it can be constructed from off the shelf commodity machines, reducing the hardware costs.

Some readers may observe that I am challenging the “conventional” wisdom that dictates that a multi-tenant cloud SaaS system needs to be run on huge back-end databases, running on large computing systems, where all users’ data is stored in the same schema. This approach may be appropriate for some applications where the essential value of the cloud system is the interaction between everyone’s data. Facebook is an example of this type of system. ISV’s however typically do NOT want one customer’s data to interact with another customer’s data. In this case, having separate databases significantly eases the management and security concerns.

I am not alone in this respect:

“If you’re giving customers their own instance of an application, I think it makes much more sense for them to have their own database.” Joel Spolsky – Stack Overflow podcast #19 originally aired on August 26, 2008

Building on this fundamental database architecture, SQL Anywhere offers many other features appropriate for ISV cloud environments that I will discuss in future posts.