SQL Server Reference Guide

You might think that as the SQL Server Guide host I would always recommend that SQL Server be used in every development situation. But there’s an old saying: "When all you have is a hammer, everything looks like a nail." Perhaps you’ve run into consultants who find that whatever problem you have, the solution is the same (theirs). That usually comes from being familiar with only a few products.

I’ve found, however, that each situation is unique. After close evaluation, solutions are normally dictated by several factors, some of them "weighing" more than others. I’ll cover a few of those factors in this article, to help you understand when to pick SQL Server as the back-end architecture. In fact, you may not need a Relational Database Management System (RDBMS) at all — you might be able to relax the requirements you have enough to use all kinds of storage for your applications. An RDBMS isn’t suited for every situation.

All that being said, I’m still a big SQL Server fan. I’ve found it to be a capable, relatively inexpensive solution for many business needs. The development team at Microsoft seems willing to listen to its customer base, and the product has a great balance between feature-set and complexity. I have recommended it for many companies and continue to do so. With newer offerings like SQL Azure (Cloud Database), I think Microsoft continues to innovate in the data storage and retrieval area.

Of course, the application you’re using might require a particular RDBMS engine. Even if that’s the case, it’s still best to understand the strengths and weaknesses of each database platform.

Let’s take a look at some of the popular alternatives to the Microsoft enterprise offering. This list is by no means exhaustive, but it does cover the ones I’ve personally seen in use recently.

I update this tutorial frequently so that it is as current as possible. Even so, each vendor changes things with new releases, patches, updates and modifications throughout the year. This isn't intended as a complete marketing comparison, only my own experiences with these products. Also, as technology evolves, new paradigms for storing and retrieving data (like NoSQL variants and Programmatic Databases) come into play. I’ll talk briefly about those here, but keep in mind the information may be dated.

Direct Competitors in Commercial RDBMS Databases

There are several commercial databases that compete with SQL Server, and more coming online all the time. The list I have here is in no particular order, and you may have others you might see in use. Again, not an exhaustive list — just my personal experience.

Oracle

http://www.oracle.com/us/products/database/index.htm

Oracle is one of the oldest relational database platforms still in use. It runs on almost anything, anywhere, has amazing levels of performance and features, and Oracle is the second largest software company in the world. In short, it defines the genre.

With so many features and advantages, why not run Oracle as the back-end of your application? It’s a valid question, and one that you should ask during your product evaluations. The answer, in the cases where I’ve recommended it, is usually in two parts — cost and ease of administration and development.

Oracle has a fairly complex licensing scheme, and can quickly become very expensive. There are times when you can actually beat SQL Server on price with Oracle, but not very often.

Oracle is necessarily more complex than SQL Server, because it runs on so many platforms, scales differently and has more parts. That’s not a bad thing; it’s just part of the package. You should make sure you keep these things in mind when you’re making your choice.

Sybase

http://www.sybase.com/products/databasemanagement

I won’t say a lot about Sybase features and structures, since it’s very similar to SQL Server. In fact, up until SQL Server version 7, the engine in SQL Server was essentially the engine from Sybase. The two systems diverged at that point and have been leapfrogging each other in performance and features ever since. A Sybase developer would have little trouble in programming in SQL Server, and visa-versa. For a time Sybase seemed to be decreasing in the offices I’ve consulted with, but as of late it has started to make a comeback.

Sybase has one distinct advantage over SQL Server: It runs on more platforms. On the other hand, it can also be more costly than SQL Server, at least as of this writing.

There are several editions of their RDBMS platform and also other products from Sybase available.

IBM’s DB2

http://www-306.ibm.com/software/data/db2/

IBM’s DB2, lately dubbed DB2 Universal database, is actually a collection of products used to manage a data system. It has a unique way of implementing database constructs as objects, and as of this writing holds the TPC crown for speed. It runs on many platforms from Windows to various UNIX flavors, and of course on mainframes.

IBM’s DB2 is quite capable, and has an immense installed base on many mainframes. IBM has recently begun a foray into the desktop (and smaller) arena, and time will tell if they are successful.

DB2’s major disadvantage is in its lack of departmental popularity. There just aren’t that many people outside of an IBM world (which itself is pretty large) that are familiar with its administration and development. Another drawback is that DB2 can also be fairly expensive, depending on the situation.

There are, of course, several other products (such as Firebird, Ingres, Informix and more), not to mention flat-files (popular in several mainframe systems) or custom-developed solutions. My suggestion, if you’re interested in learning more about other options, is to check the various vendor’s Web sites to see what they tout as their main strengths. Then lurk on several newsgroups for that product and determine what the main difficulties DBAs are having with it. If the "strength" is the main difficulty, then caveat emptor.

Open-Source RDBMS Platforms

You don’t have to pay for a commercial license to have a very capable RDBMS. That isn’t to say Open Source = Free — it doesn’t. Open-Source merely means (generally) that the company will deliver the source code for the product along with the compiled binaries — something commercial software doesn’t often do. You may in fact still pay for an Open Source RDBMS, but in large part many of them don’t cost anything to install.

MySQL

http://www.mysql.com/

MySQL was arguably (and possibly still is) one of the most widely distributed RDBMSs out there. I say “was” because they sold themselves to Oracle, and many in the Open Source community viewed this as a betrayal. Since then, the product is still offered, but has been forked a few times and in the investigations I’ve seen is dropping off in popularity.

Still offered, however, are two variants: a standard database engine, and the other is called MySQL Enterprise. If you tried MySQL early on, you might not have considered it ready for enterprise applications. You may want to re-evaluate the product, since it now supports transactions, multiple filegroups, full-text indexing, and more. Another advantage is that it runs on multiple platforms, including Microsoft Windows and Linux. One of the most interesting features of MySQL is the fact that it can use multiple database engines, such as MyISAM, Heap, InnoDB, and Berkeley DB. You can even select an engine per table. If planned properly, this flexibility can vastly increase an application's speed. It’s quite fast for standard SELECT statements, and well suited for many Web-based applications.

MySQL has come a long way, adding replication, indexed views, stored procedures, cursors, foreign keys arrays, and views. Many large companies, including Apple and Google, use MySQL in some of their public websites.

While the smaller version of MySQL is very capable, it doesn't recover as quickly or as easily as do Oracle and SQL Server (MyISAM requires clean shutdowns). You’ll also need to choose the InnoDB or Berkeley DB for heavy UPDATE operations, and both of these have some recent licensing considerations.

MySQL isn’t free — not totally, anyway. If you’re developing open-source solutions, you don’t have to pay for the license. If you’re developing a commercial product and aren’t going to abide by the open-source licensing model yourself, you will need to buy a license. Even so, MySQL is significantly cheaper than most other database engines.

PostgreSQL

http://www.postgresql.org/

PostgreSQL is also open-source, but has been around for quite a bit longer than MySQL. As such, it has even more documentation and case studies available, and has evolved a richer feature set. It boasts a very wide set of data types, has hooks into just about any programming language on earth (and ODBC for the few not available), and runs on everything from Apple Macintosh to Intel to big-iron mainframes, although the Windows version is a bit of a hack. PostgreSQL supports really huge databases, has support for large memory types, and can handle API calls. It also has a Geospatial offering, which some other systems lack.

PostgreSQL implements standard ISO joins, views, indexes, triggers, stored procedures and more. In short, it definitely conforms to the standard notions of what an enterprise database platform should do.

It’s sometimes difficult to find talent in the PostgreSQL dialects, from administration to development. To be sure, several administrators and developers are familiar with it, just not quite as many as for an Oracle or SQL Server environment.

Small, Single Use Databases

You might not need a full RDBMS database, even a free one like SQL Server Express, on a local system. If you’re developing something for a family member and you need to bring it up quickly, there are alternatives.

Microsoft Access

http://office.microsoft.com/en-us/access/default.aspx

It might be a little strange to some to find Microsoft Access as a competitor to SQL Server, but in fact sometimes it is. Microsoft Access is a complete database, development and reporting environment which is highly integrated into the Microsoft Office suite of programs. It uses the "Jet" engine for storage technology, but it can also use SQL Server, Excel or even XML files as a back-end as well. In fact, it can use all of these at once.

The main strengths of Microsoft Access are its availability, its low cost and its ease of use. It seems almost everyone has access to Access, and it’s quite easy to understand, even for writing code. It has several built-in wizards, a graphical reporting tool and the ability to export included in the base product. There are hundreds of applications already written using Microsoft Access.

New developers are sometimes confused by Access — is it a database file or a program? The answer is both. An MDB file is an access database that you can use in VB or C# code, and it also has all the plumbing for the Microsoft Access program to store reports, programs and more right in the same file. That feature sets it apart from the other engines I’ll discuss.

Access’s primary drawback is its scalability. Granted, Microsoft doesn’t tout Access as an enterprise system, but some applications try to force it into that mold. The point is that Access just isn’t suited for more than a few concurrent users.

Another drawback is that Access doesn’t have a true transaction log, and it doesn’t allow you to create multiple files to house the data. For that reason, it isn’t as safe as an enterprise model requires.

Access doesn’t have great security, either. You can create users and restrict them to certain parts of the program, but it’s easy to circumvent.

Don’t write this product off, however. A modified version of the Jet engine was used in Windows NT’s naming database (WINS) and another version of it (Jet Black) was the early database engine for Microsoft Exchange.

You can think of Microsoft Access as inexpensive and feature-rich but having very low-scalability. It runs on all Microsoft platforms.

OpenOffice Base

http://www.openoffice.us.com/

In my experience, the OpenOffice Product has been pretty good. That being said, the “Base” product within it, which is often touted as an Access replacement, doesn’t quite do the same thing, especially in the reporting and coding capabilities. You also can’t download just the “Base” product, at least in my experience (I could be wrong here). I’ve had to download the whole package, customize the install, and then install only the parts I wanted.

But if you are looking for an Open Source alternative (once again, this one is now owned by Oracle), there are forks available.

NoSQL And Writing Your Own Data Storage and Retrieval

“NoSQL” is a term that means “Not Only SQL” — not meant as a rebuttal of the RDBMS offerings. The issue is that it gets pretty varied from there. Each and every offerings handles data input, computation, storage and output in a different way.

Essentially the changes are in the way you query a NoSQL system, since it tends to handle concurrency, locking and alterations in a different way than an RDBMS. I’ll direct you to other links here, since each handles that differently. Also, this arena seems to change by the month, so check this link first to see what has changed.

Since there’s no single governing body for NoSQL, all references have to be taken with a grain of salt. This one claims to be the primary location for information on NoSQL, and does seem to be kept current as of this writing: http://nosql-database.org/.

You can also use something like a Platform-as-a-Service (or Cloud) offering like Microsoft Windows Azure and wire up the Table and Blob storage there as a NoSQL-type offering.

Making a Choice

So now let’s move on to the factors that will help you determine your choice. The process is that you’ll develop a matrix that lays out the decision-points that fit your situation. You then "weight" the factors that are important to you, assign a score to the products that you’ve researched.

I don’t set the factors in any particular order. Also remember that the weighting you give each choice will be dictated not only by technical merit, but budget, political factions and other considerations.

I generally look at multiple factors, based on what I need to do. It’s not unusual to have multiple data storage engines in a large company, although it can be advantageous to stay on fewer platforms as well.

Once I define my requirements, here are a few of the criteria I lay out when I’m making the decision to implement a particular data store:

• Architecture
• Federation
• Replication
• Client types
• Licensing and Cost
• Language
• Current Infrastructure

There are other criteria to help you with your decision — many of them are on the vendors' Web sites. I’ve included these as the ones they don’t always cover, since the answers don’t necessarily favor their solution as the right choice. In any case, make sure you educate yourself on many technologies so that you’ll be able to select wisely.

With this information, you can see that you have a lot of choices to make. While all of these products are very capable, it’s been my experience that SQL Server has usually been at least a "safe" choice. It scales well, and while it doesn’t run on every platform it runs on a very ubiquitous one. As enterprise software goes, it’s not terribly expensive and it performs reasonably well, even in some of the larger applications.