| Databases can be so useful, nowadays even small | | | | Work that requires a separate compiled software |
| organizations use them. Everywhere you look, there is | | | | program in SQL Server can sometimes be done with |
| a database: mailing lists, customer profiles, organizing a | | | | mere scripting in Oracle. Oracle's dialect of SQL is |
| wedding, routing toll free telephone calls all over the | | | | called PL/SQL, and has capabilities T-SQL lacks. |
| country... Some are small and simple. Some are | | | | DB2 |
| immense and complex. | | | | Some organizations prefer mainframes instead of |
| Each database runs in a database management | | | | Windows or UNIX servers. For these, DB2 is a |
| system (DBMS). If you have dealt with computers long | | | | frequent choice. It is not meant to run on everything |
| enough, you remember some of the earliest desktop | | | | from small PCs to big servers. It is intended for heavy |
| DBMSs. Most of the early desktop DBMSs were | | | | duty use with large amounts of data and high |
| shoved aside long ago by Microsoft Access(TM), | | | | availability--extremely serious databases. |
| although several are still available such as | | | | The more commonly used a DBMS is, the easier it is |
| Paradox(TM), dBase(TM) and Alpha Five(TM). For | | | | to find people who have experience working with it. |
| larger databases, you are probably aware of | | | | Because DB2 is so focused on very large databases, |
| MySQL(TM), IBM DB2(TM), Oracle(TM), Microsoft SQL | | | | fewer people know how to work with it. Part of the |
| Server(TM). | | | | price of using DB2 is needing to look harder for the |
| In my consulting practice, I often encounter people who | | | | right people to help you set it up and use it. |
| think those are the only DBMSs worth considering, and | | | | For some organizations, that is almost as important a |
| they are all just different flavors of the same thing. For | | | | factor as the technical merits of a DBMS. When you |
| more than a decade, I have been helping them past | | | | have demanding requirements, you may need a DBMS |
| their confusion so they can choose a DBMS | | | | that is not commonplace. If so, the experts who work |
| intelligently before they build a database. This short | | | | with it are likely to be harder to find and may cost |
| primer will give you a basic grounding. You may still | | | | more because of their rare skills. |
| need help to choose the right DBMS for your | | | | Not All Databases Live on Disks |
| purposes, but at least you will be able to ask smart | | | | The DBMSs mentioned so far keep their information |
| questions and get the answers you need. | | | | primarily in disk files. The more sophisticated DBMSs |
| Low-Priced Desktop Databases | | | | may allow you to configure them so the most |
| Your dictionary may say a spreadsheet qualifies as a | | | | frequently used data is in computer memory, where |
| database. In the computing world, a spreadsheet does | | | | performance is much faster, but the data in memory is |
| not even come close. Through special interfaces, you | | | | sent to disk as soon as possible so it will not be lost if |
| can use a spreadsheet as a handy, familiar way to | | | | the system crashes. |
| look at a portion of the data in a database. But only | | | | That trick speeds up database performance, but not |
| one person at a time can open and make data | | | | enough for some purposes. Telephony, spacecraft |
| changes in a spreadsheet. | | | | telemetry, manufacturing automation, and many other |
| If the data needs to be usable for a group of people | | | | applications need to keep up with equipment instead of |
| and it is in a spreadsheet, eventually somebody will get | | | | with comparatively slow human beings. The next |
| frustrated about not being able to make changes. | | | | move up in performance is a database that lives |
| They will make a private copy of the file, put their | | | | entirely in memory, which is much faster than disk |
| changes into it, and replace the group spreadsheet | | | | storage. |
| with their version whenever they have a chance. That | | | | Memory resident databases, if well architected and |
| wipes out any changes made by someone else while | | | | well built, can be blazingly fast. Of course, speed |
| the frustrated person was working in a private version. | | | | comes with a trade-off. Memory resident databases |
| Valuable information is lost this way. | | | | disappear if the system crashes. As a fallback, this |
| If more than one person needs to work with | | | | type of database saves a copy of itself to disk |
| information, it does not belong in a spreadsheet. It | | | | occasionally, like a snapshot of its condition at an |
| belongs in a database. Even the cheapest, least | | | | instant in time. When restarted, the database uses the |
| capable DBMS will allow everyone to look at the data | | | | copy on disk to repopulate itself. If it is gracefully shut |
| at the same time. When someone makes a change, | | | | down and restarted, the disk copy is up to date and |
| only that small portion of the data-the equivalent of a | | | | nothing is lost. But if the system crashes, everything |
| row on a spreadsheet-is "locked" during the change to | | | | that occurs between the last pre-crash snapshot and |
| make sure two people do not make different changes | | | | the restart is lost. |
| to it at the same time. As soon as the change is | | | | Notice that I said these databases are very fast when |
| finished, that "row" is "unlocked" and made available for | | | | well built. I have seen highly proprietary memory |
| other changes by anyone in the team. | | | | resident databases in telecommunications with |
| However, the adage "you get what you pay for" is | | | | mind-boggling speed. I have seen another that is |
| frequently just as true in computing as it is anywhere | | | | commercially available, runs on multiple operating |
| else. Desktop DBMSs do enough to satisfy modest | | | | systems, and is so reliable that it is used by nuclear |
| goals. You may be able to start out with one of these. | | | | power stations. But I have also seen one that is |
| As your database grows larger or your needs | | | | commercially available, sold for automation purposes, |
| become more sophisticated, expect to need to | | | | and unable to meet the performance of leading disk |
| migrate to a more substantial DBMS. | | | | resident database engines. The fact that a database is |
| As an example, let's say you run a small business with | | | | memory resident does not guarantee it will run like |
| 5 employees. You have a few hundred customers. | | | | greased lightning. |
| Their orders, your order fulfillment, and your | | | | Not All Data is Relational and Sorted Into Lists |
| administrative procedures are straightforward. A | | | | Up to now, all the DBMSs mentioned have been |
| database in Microsoft Access can handle that with no | | | | relational. They organize data into tables, which you |
| problem. | | | | can think of as being similar to spreadsheets. Each |
| Now let's say your business grows. By the time you | | | | table has fields similar to the columns in a spreadsheet, |
| hire employee number 50, you will probably either be | | | | and records similar to the rows in a spreadsheet. |
| migrating to another DBMS, or already be using | | | | Tables can connect with each other through keys. If |
| another DBMS. Most desktop DBMSs in the low end | | | | you have a table of customer contact information and |
| of the price range are designed to work well with a | | | | another table of orders placed by customers, the |
| few simultaneous users. They struggle as both the | | | | customer identification code that appears in both |
| number of users and the amount of data grow. You | | | | tables will relate each customer's contact information |
| might buy yourself a little time by putting your database | | | | with all the orders that customer has placed. |
| on a more powerful computer, but ultimately a serious | | | | Not all data can be readily organized that way. |
| database needs a serious engine, just like a racecar | | | | Sometimes you need a spaghetti bowl of |
| needs a more serious engine than a commuter's car. | | | | interconnections. I see this most often in memory |
| MySQL as a Step Up | | | | resident databases with a profound need for speed. |
| MySQL has become a popular next step up. It is often | | | | Some of them use a maze of interconnections. A |
| available free with website hosting services. It runs on | | | | relational database may have to trace several |
| several flavors of UNIX as well as on Windows(TM). | | | | relationships to pull together the same data, where a |
| Many free or cheap web applications (and some more | | | | maze of interconnections allows a database to grab |
| costly ones) run on it. It can handle more data and | | | | related data more directly. |
| more users with better, more tunable security than | | | | Superfast databases also use a variety of methods to |
| typical desktop engines. | | | | locate desired pieces of data more rapidly. Sorting in |
| You can do a lot with this, handling a substantial | | | | alphabetical, numerical or date order is natural for |
| amount of data and many more simultaneous users | | | | people, but looking up data in that type of list is not the |
| than a desktop database can handle. | | | | most efficient way for a computer to do it. As |
| However, the adage about what you pay for will bite | | | | examples of faster methods, some databases use |
| you again when you reach the next threshold. Exactly | | | | hash tables or balanced binary searches for lookups. |
| which threshold you reach is not the same for every | | | | However, the rise of web applications has accelerated |
| database. You may need better security or disaster | | | | other needs for less rigidity in the structure of data that |
| redundancy because your data is sensitive. You may | | | | goes into databases. Non-relational databases are |
| need faster performance. You may need to run on an | | | | useful where data structure is in flux. Although you |
| operating system that is not UNIX or Windows. You | | | | might expect this to be needed mostly for exotic |
| may be dissatisfied with the tools available to help you | | | | purposes, I know of systems at a major airport and in |
| work with your database. Any of these is a good | | | | a large property management operation where |
| reason not to choose MySQL. | | | | non-relational databases are in use. |
| For most people, the best known alternatives are DB2, | | | | Some Databases Cast Data in Stone |
| SQL Server and Oracle. They are designed to be able | | | | A variety of other features can make one database |
| to cope with large, sophisticated, enterprise class | | | | stand out from others for a specific purpose. Most |
| databases and loads. | | | | people think of information in databases as easy to |
| Microsoft SQL Server | | | | find and update. But for an example of a special |
| SQL Server is often mistakenly called SQL. SQL | | | | purpose where the data is not updatable, you need not |
| stands for Structured Query Language. This is a | | | | look any farther than your medicine cabinet. |
| scripting language that can be used to pull data from | | | | Pharmaceutical manufacturers are required to keep |
| and put data into a database. Each SQL-compatible | | | | vast amounts of information on file about every batch |
| DBMS has its own dialect of the SQL language. SQL | | | | of medicine they make. If drug regulators notice an |
| Server is a DBMS, and its dialect of SQL is called | | | | alarming pattern and want to check whether |
| Transact-SQL or T-SQL. The database administration | | | | something went wrong in a batch, the manufacturer |
| toolset for SQL Server is heavily oriented toward | | | | needs to be able to produce that data |
| graphical usage instead of scripts. | | | | immediately-and that data must be pristine. It must |
| As a Microsoft(TM) product, SQL Server is only at | | | | never have been changed since the batch of medicine |
| home on Windows. Each version is released with | | | | was made and the data was captured. For this |
| several editions ranging from small (oriented toward | | | | reason, some database engines can be set so that |
| the desktop and developer) to enterprise (very large | | | | they will not allow any information in the database to |
| and complex with heavy usage). The bigger the edition, | | | | be changed. New data can be inserted, but from that |
| the greater the capabilities and the bigger the price. | | | | moment on, the data is cast in stone. |
| This is often the next step up from Microsoft Access | | | | A few DBMSs can keep track of the source of |
| because it runs on the same operating system, and | | | | everything done to their data. Most databases require |
| because it is easy to use Access as just an interface | | | | that type of tracking to be done by writing special |
| between the user and the new database. By using | | | | software that users must go through to touch the |
| Access to present the user with screens for SQL | | | | data, but it is possible to get a DBMS which has that |
| Server data, a business can migrate upward without | | | | capability built in. This is not necessarily practical in a |
| needing to retrain database users. The users still get | | | | database that has to keep up with machinery. But in a |
| the same look and feel they had in the small Access | | | | hospital, it may be crucial to know which person made |
| database. | | | | each change or addition in a patient's records. |
| This is the right solution for some databases, but not | | | | Abundant Choices |
| for others. Some of my clients run large databases on | | | | The DBMSs mentioned by name in this article are not |
| operating systems other than Windows or UNIX. | | | | the only ones available in their categories, let alone in |
| Some want to move their database to another | | | | the entire world of databases. Before you build a |
| operating system in the future without needing to | | | | database, it is worth your while to think about what |
| change to a new database engine at the same time. | | | | you need. You will put a lot of effort and expense into |
| Some find that T-SQL does not offer all the scripting | | | | creating your database, so it should be in a DBMS that |
| capabilities they need for the applications that must run | | | | suits what it has to do. |
| on their databases. Each of those is a viable reason to | | | | As an example of good versus bad choices, consider |
| choose Oracle. | | | | two large companies in regulated industries that need |
| Oracle | | | | to keep specific data for years and be ready to show |
| Oracle and SQL Server constantly compete for | | | | it to government auditors on a moment's notice. Both |
| benchmark test results that outdo each other for | | | | chose a memory resident DBMS because their |
| performance. Either can be fast, manage large | | | | systems need to collect data from machinery. As |
| amounts of complex data, and handle substantial | | | | mentioned earlier, if the system crashes, all data |
| usage loads. But Oracle runs on a wide variety of | | | | between the last "snapshot" and the crash is |
| operating systems. If you want the flexibility to move | | | | permanently lost. One of these companies copies the |
| to another operating system, this is obviously desirable. | | | | regulated data into a disk resident DBMS right away. |
| Oracle is especially entwined with the OpenVMS(TM) | | | | The other does not, leaving it exposed to fines from |
| operating system to deliver as much performance as | | | | regulatory agencies for losing data whenever the |
| possible on that platform. If you cannot tolerate | | | | system crashes. |
| frequent reboots for security patches and turn pale at | | | | If you are not sure which engine to use, ask a |
| the very thought of a system crash, you know it is | | | | professional. By now, you know just saying you want |
| common for OpenVMS to run for years between | | | | a new database is not enough. Use this article to help |
| reboots. You also know OpenVMS can make multiple | | | | you explain what you need. When you tell an expert |
| computers behave as though they are a single | | | | that you want a database to run on your favorite |
| computer when they are up to 500 miles apart, | | | | operating system, with minimal data loss in the event |
| offering great protection against disasters. A setup like | | | | of a crash, moderate speed, high security and 200 |
| that is certainly not cheap, but it is easy to see why | | | | simultaneous users... that is a great start. |
| some organizations demand it. | | | | |