This thread is a list of pointers to different places on the web where you can find good info on databases, and issues surrounding them. You'll find mostly links below, but more and more text here, in this thread, eventually. We want people to get answers as easily as possible.

Only moderators will post in this thread. If you'd like to see a question answered, a link or paragraph added, make a suggestion, or anything else, please visit the companion thread.

Table of Contents

1. Database Engines (Download, Documentation, etc)
   1. Commercial
      1. Microsoft
         1. SQL Server
         2. Access (Office)
      2. Oracle
   2. Open Source
      1. MySQL
      2. PostgresSQL
      3. VistaDB
2. Building Client Connections
   1. From Microsoft.NET (C#, VB, J#, F#, PHP)
   2. From Microsoft COM languages (VB 6, ASP Classic)
   3. From PHP
   4. Through JDBC
3. Extraction, Transformation, and Loading (ETL)
   1. For SQL Server
      1. BCP (Bulk Copy Program)
      2. DTS (Data Transformation Services)
      3. SSIS (SQL Server Integration Services)
   2. For Oracle
      1. Oracle Loader
      2. Oracle Streams
   3. For MySQL
      1. ???

Database Engines

The word database can mean different things to different people - somebody once told me that Word can be a database, because it has a "Find" feature. Most people agree on a technical definition, which describes a database as a set of components working together to provide a very useful for of data storage and quick retrieval.

A database engine, or relational database management system (RDBMS) is software that controls a disc storage engine, a query processing engine, and some type of authentication engine that controls access to that data. Virtually all modern databases with much use, chose structured query language (SQL) as their query interface, although most choose their own SQL dialect.

Most (but not all) databases use the client server paradigm. Both of these components can live on the same machine (and thus use shared memory rather than TCP to transfer data) but still enforce a strict separation. This will be covered further down in this thread, under connectivity, ODBC, and so on.

The job of a database is to store, manipulate, and retrieve information on demand, and quickly. All modern databases do an acceptable job of this. Many of them provide added functionality and take their mission to be optimizing (reducing) disc access, because IO is the slowest thing that happens in the server. When developing an application and choosing database engines, most people weigh performance needs against complexity against price.

Below is a discussion in more detail of specific database engines, with download links when possible.

SQL Server (Microsoft)

SQL Server is perhaps Microsoft's best product - a relational (OLTP) database engine with analysis, reporting, and integration services. As its name implies, "MSSQL" implements the client server paradigm. These components (client and server) often live on the same machine in hosted scenarios, but for high scale work, the database prefers to be the only software running on a server.

The query language is extended with Transact SQL, or TSQL. This allows for cursors, flow control blocks (if/then/else, while, case, etc) as well as Microsoft's proprietary functions and clauses. These will be covered in more detail below, under SQL dialect compatibility.

SQL Server comes with client tools (SQL Server Management Studio, or SSMS) but no GUI, as is typical of Windows Services. As such, it has traditionally been classified as part of Microsoft's "back office" suite. SQL is very powerful, but has a steeper learning curve than other databases, including Access.

SQL Server comes in a commercial variety (with Enterprise and Standard Editions), and with an "Express" version, which is free. Enterprise is far better suited to high scale workloads. SQL Express can be used as an embedded database, allowing smaller, non distributed apps to avoid the need for a server.

You can download SQL Server from Microsoft at these addresses

• SQL Server Express - Feature limited, time unlimited. Databases cannot exceed 4 GB, including data, indexes, and code. The server will not accept more than 25 connections at any given time.
• 180 Day Trial, Enterprise Edition - Feature unlimited, but (obviously) time limited.

Access (Microsoft Office)

Microsoft Access is a "desktop database," simplifying the client server paradigm heavily. This simplification goes beyond the database implementation. Access comes with a fully featured GUI (graphical user interface) that looks and behaves exactly like the rest of Office. Integration with Office has been good for 10 years, making analysis far easier.

Access is a database (storage or persistence engine, plus SQL query engine), but it's also an application framework. Microsoft's goal here is to make it as easy as humanly possible to use bulk amounts of data. Access provides a built in (and generally sufficient) framework for

• Forms. Data entry and review is the obvious use, but these can be tied to programability of different sorts, allowing business intelligence and full blown applications. For example, a built in "Switchboard Manager" builds out fancy menus to help users navigate a database.
• Reports. Perhaps the most common demand of database systems is reporting on the data. Access has a reporting system very much like Crystal Reports, which allows for fancy printouts with sorting, grouping, etc, based on queries against your data.
• QBE. A "Query by Example" builder guides users through querying data, writing the SQL out behind the scenes.
• VB. Access provides (and in many cases is run by) Visual Basic for Applications. This allows for procedural and very limited OOP programming to enhance the functionality of a database.
• Linked Tables. External data access is very easy through Access, for any data source that implements ODBC. In many cases, Access will establish a read and write link to the source data.

Despite these (somewhat) advanced features in a very friendly package, Access isn't always the best choice. It has trouble with performance compared to most database engines. In part this is because of the simplified nature of the storage engine. It's also because VBA is among the slowest programming frameworks taken seriously.

These makes Access best for storing relatively small amounts of data (generally no more than 10s or 100s of thousands of rows) that isn't used in terribly complex ways. In theory, an Access database can handle as many as 255 simultaneous connections, although in practice 25 is a more realistic limit. Furthermore, a database cannot store more than 2 GB of data, although with linked tables, this is an annoyance more than a limit.

Access sticks somewhat close to the ANSI SQL standard, with a few extensions (like PIVOT queries). Nothing like TSQL or PL-SQL is provided. Access queries can use VBA functions in select and where clauses, but all Access SQL queries are limited to a single statement. (Subqueries are allowed, flow control within SQL is not.)

The familiar GUI in Access coupled with the weak storage engine leads to an unexpected use. Access is often used as a customizable front end for SQL Server databases. It provides a (weak) development environment, but is more often used to provide forms and reports.

A 60 day trial download is provided by Microsoft.