facebooklinkedinrsstwitterBlogAsset 1PRDatasheetDatasheetAsset 1DownloadForumGuideLinkWebinarPRPresentationRoad MapVideofacebooklinkedinrsstwitterBlogAsset 1PRDatasheetDatasheetAsset 1DownloadForumGuideLinkWebinarPRPresentationRoad MapVideo

SQL

SQL Database

SQL Database

At a high level, the term SQL database is used to describe a database that uses the structured query language (SQL) to view and update the data that a Relational Database Management System (RDBMS) manages.

Concept image of SQL database

What is a SQL database?

A SQL database uses the notion of related tables made up of rows and columns. Tables are related to each other through primary and foreign key relationships. The RDBMS can enforce these relationships if they are defined using referential constraints. For example, you might set up a defined constraint that no employee record can exist without having a department. This declarative approach makes defining relationships between objects easy and maps well to the application entity-relationship model used to map applications to their respective data layout. 

How to Create a SQL Database

The process to create a SQL database can be as easy as installing it on a system. Vendors such as Actian and Microsoft provide installers for Windows and Linux that create a sample database with demo data, making it easy to verify the install and test client connectivity. Some installers create a running instance that accepts a CREATE DATABASE command to define database schema. 

Once you have a basic on-premise or cloud-based database instance running, you can create objects such as tables, indexes and views using SQL statements, including CREATE TABLE, CREATE VIEW, and CREATE INDEX. Most database systems support role-based security, which means you can create named permissions groups such as Database Administrator, Application Admin and App User. These named permissions can include statements such as GRANT DELETE ON TABLE-A, which allows records to be deleted from TABLE-A. Permissions can be removed using the REVOKE statement. Next, the DBA would create users or groups of users and grant them privileges to access database objects. 

Examples of SQL Databases

Commonly used relational databases include Oracle, Microsoft SQL Server, Avalanche Cloud Data Platform, Actian X, IBM DB2 and MySQL. 

Many database systems in use today can be accessed using key-values, which used to be known as indexed-sequential access. Examples of databases that can be accessed using key-values include MongoDB, Amazon DynamoDB, Redis and Actian Zen. 

Which one is Best?

There are lots of SQL databases from which to choose. The one that may be best for you depends on a multitude of factors, including the following:

Multi-Cloud

If you are a business that audits data for organizations, you need to put the database processing where your customer’s data currently resides. It could be on Google Cloud, AWS, Azure, or an on-prem data center. In this situation, Avalanche Cloud Data Platform offers the best platform availability.

Mission Critical Support

Most open-source databases are out of the question if you need the best technical support. You need to know if an issue is escalated to level 3; your provider has the engineers that can fix the issue. 

Hybrid

If your application needs an enterprise-grade transactional database coupled with a high-performance analytics database engine, Actian X could be your best choice. Microsoft could offer you the combination of SQL Server and Azure Synapse, but they are not the same database code base, which can mean changes to your SQL for your Analytics workload.

Cost

If you have a large application to support and want to avoid capital expenditure (CAPEX), you can opt for a cloud-based database service such as the Avalanche Cloud Data Platform that you pay by subscription using your Operating Expense (OPEX) budget. This can be particularly advantageous if you plan to start small and scale over time, so you don’t need to buy more capacity than you need.

Scalability

If you need the fastest query performance for your dashboards and it has to be on-premises, Avalanche offers the best performance.

Footprint

If your system is embedded in a mobile device or IoT camera running a Raspberry Pi, you may only have 10 MB of memory available. In this case, a small footprint database such as Actian Zen may be your only choice.

Connectivity

Some databases make it hard to get your data into their database by making you go to third parties for Extract, Transform and Load (ETL) technology. Others, such as Actian, have built ETL into their Avalanche Cloud Data Platform, making it easy to get data into your database from hundreds of different data sources.

Cost

If your application uses an unusual OS such as z/VM on a Mainframe system, you may find only IBM SQL/DS (Structured Query Language/Data System) may be your only choice. On VMS, Actian Ingres will be your best option.

These are just some factors to consider when making a database selection. Enterprise Architects in larger organizations often look at the entire application stack. They are factoring in supportability, observability, scalability, and security when deciding on their next-generation applications platform. 

Just picking the easiest or cheapest database solution to get started can land your business with an expensive migration project when you fail to look under the covers as to where the technology came from and whether it is currently being maintained and supported by your vendor of choice. To get more information on all Actian data products and solutions, visit our website.

 Explore our innovative data management, integration, and analytics solutions.