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.
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 of creating 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, Actian 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 SQL Database 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, Actian Data Platform offers the best platform availability.
- 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.
- 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.
- 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 Actian 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, Actian offers the best performance.
- 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 Actian Data Platform, making it easy to get data into your database from hundreds of different data sources.
- 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.
- Operating System – 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.