How to select a database

The overall balance of your needs from 5 aspects:

(1) The underlying

(2) function

(3) How many people to use (concurrency problem)

(4) Safety and stability

(5) operating system used (UNIX, Linux, Windows)

For used databases there are:

Dbase/foxbase/foxpro in the Dos/novell/windows 3.x world

MS SQL Server

Oracle

Sybase sql/ase Servers

Sybase IQ

informix/dynamic

MySQL

Postgresql/enterprisedb

For some key requirements, choose from:　　

　　Mission-Critical (OLTP) and high-performance systems

If we need databases to play a more important role in critical business systems, and want to achieve the goals of failure-free time/high availability/clustering, reliability (performance and data volume), disaster replication and automated reporting, or the need for extreme high-speed and low-latency (HPC, high-performance computing) effects, Then we may need the enterprise-class capabilities of the database, but this is also more expensive.

　　HA (High availability) Clustering feature

The cluster functions of each database platform are as follows:

Oracle RAC is best suited for ha clusters, load balancing and scaling;

MS SQL Server provides ha failover functionality, but no load balancing;

The Sybase ASE 15 cluster is equivalent to the Oracle RAC function;

Teradata itself is a distributed database platform;

The MySQL cluster claims that the TPC-C performance benchmark is higher than the Oracle RAC;

• IBM DB2/UDB works well on clusters of IBM P-series/power servers and Veritas Vcs/sun that run Aix HACMP (highly available cluster multi-processing).

　　High performance/low latency/In- memory database

Oracle provides timesten memory database;

The MySQL cluster uses a memory storage engine;

Sybase provides an ASE memory database;

• In addition, we can install SQL Server databases using RAMSAN/SSD (solid-state drives), all database-related disk I/O are on SSDs, not on traditional hard drives, so we can significantly reduce disk I/O latency, provide I/O throughput, and improve the overall performance of the database;

• There are other memory databases, such as SQLite or eXtremeDB, but most of them either support only embedded systems or only support single-user or single-connection at the same time.

　　Scalability/performance

• When using Oracle RAC and Sybase ASE 15 clusters, if the existing hardware does not meet the performance requirements, we only need to add a more powerful server to the cluster, and then gradually replace the other nodes;
• When using a SQL Server cluster, because it only supports active/passive mode, there is no extensibility at the instance level, and if the existing hardware does not meet the needs, we must replace the entire cluster;

The MySQL cluster also supports adding nodes to the cluster where all nodes are active, but it is a no-shared cluster, more nodes means more database replicas, more storage and more network traffic when replicating data between all nodes .

　　Copy
Sybase:sybase has the best replication solution beyond Oracle's data defender and SQL Server replication solution (that is, mirroring/log shipping/transactional replication using it, and SQL Server replication is still far worse than Sybase's replication server);

Sybase's heterogeneous replication and homogenous replication were one of the main reasons that Sybase was ruled out by Wall Street in the 90 's and was used by many large companies.

Sybase replication can be at the database level (Active/Active replication, better than SQL Server mirroring), transaction level (similar to SQL Server transactional replication), table level, stored procedure level, function level, With MSA (multi-site availability) and through log shipping (the solution for replicating the world's poor), its mirror activator combined with EMC/SRDF can truly guarantee ZDL (0 data loss) during planned and unplanned outages.

SQL Server:sql Server replication can be implemented through log shipping and database mirroring (Active/passive, r/w or read-only), supporting object-level (table) granular transactional replication.

Oracle:oracle Data Defender is one of the best solutions for database cloning and disaster recovery (using Rman).

Mysql:mysql provides a non-shared cluster, essentially all active nodes are replicated synchronously, and its asynchronous replication also supports transactional and object-level replication.

• Storage-level solution: Using EMC's Bcv,netapp Snapshot manager, Hitachi's snapshot, Veritas's volume replicator, data replication can occur at the disk block level and at the volume level.

　　Hybrid Systems

Typically, a back-end database stores a large amount of data, which can cause performance problems when retrieving data (sorting, grouping, summarizing, and computing), which can affect the data-writing process when the speed of operation slows down.

Therefore, for these types of applications, we want the database to handle large amounts of data well, and to handle read/write blocking issues well.

• Oracle
Oracle is the best choice for this system because in Oracle, read/write programs do not block each other, the data is consistent throughout the Read process/session/transaction, and the overhead is primarily on memory locks and redo logs.

• SQL Server
New snapshot Isolation for SQL Server 2005 uses the same row versioning capabilities as Oracle, but more IO is generated in tempdb.

• Sybase ASE
Sybase ASE's read/write programs block each other, but Sybase IQ uses snapshot versioning to address this blocking issue.

• other databases
There are also other databases that do not have blocking problems, but some do not even conform to the acid (atomicity, consistency, isolation, and durability) standard.

　　Data Warehouse

Businesses build data warehouses to store/Archive all historical data, as well as the data they think needs to be placed in the database, often non-normalized data into the data warehouse, multidimensional degrees for OLAP and business Intelligence (BI) solutions to provide fast retrieval.

Data warehouses are also used for new or enhanced business strategy backtesting, and the data in the data warehouse grows exponentially and linearly.

　　Column-based relational database benefits

Column-based relational databases have great advantages because they are inherently multidimensional and each column is self-indexed (B tree).

A typical column-based database has Google's big table,sybase Iq,vertica and MySQL-built kickfire.

On average, these column-based databases are 60 to 100 times times faster to retrieve data than traditional row-based relational databases such as Oracle,sql server,sybase ase,teradata,db2 and MySQL. But they are slower when data is updated based on row-level, so far, Sybase IQ seems to be the leader in this area, claiming that the world's largest data warehouse (over 1P bytes) is the Sybase IQ.

　　

Overall, row-based relational data (SQL server,oracle,informix,db2/udb,mysql,sybase ASE, etc.) is suitable for OLTP applications, while column-based relational databases (Sybase Iq,kickfire, Vertica, etc.) is more suitable for OLAP/DSS and data warehouse applications.

Note: Those time series databases such as onetick,kdb+ and Vhayu are not relational databases, their storage structure is file-based, each column of data uses a separate file store, basically a column-based database, and a column-based database can also be used for backtesting. In addition, data in a row-based data Warehouse (cube) grows exponentially, but the data in a column-based data warehouse grows linearly.

Traditionally, Oracle has a leading edge in data warehousing, but now has lost its edge in column-based relational databases such as Sybase Iq,vertica and Kickfire, and the big table Google is using is also a column-based database/storage System.

How to select a database