Database on Cloud

Connelly & Begg (2014) define cloud computing as a model comprising of shared pool of virtualized resources like, networks, servers, storage, applications and services which can be provisioned and released on the fly with minimal efforts. Distinguishing feature of cloud is that these computing resources are usually sourced from off-the-shelf commodity processors and internet connection thus offer efficiency and reliability at low cost (Johnson, 2009). Redundancy helps in circumventing failures, load balancing etc.

Some of the key characteristics of cloud computing are on-demand self-service, broad network access, resource pooling, rapid elasticity and measured service (Connelly & Begg, 2014). Cloud can be deployed as public, private, community or hybrid models. Cloud computing offers three service models:

Software as a Service (SaaS) – software and data are centrally loaded and can be accessed via thin clients like Google’s Gmail, Salesforce.com’s sales management applications etc.

Platform as a Service (PaaS) – computing platform which can be used to develop web applications using software and hardware hosted at cloud like Microsoft’s Azure, Google’ App Engine etc.

Infrastructure as a Service (IaaS) – virtualized resources like servers, storage, network, OS etc. to consumers as an on-demand service like Amazon’s Elastic Compute Cloud (EC2), Rackspace etc.

SQL on Cloud - SaaS

Using the software as a service (SaaS), SQL can be offered on a cloud using Database as a Service (DBaaS) with full database functionality (Connelly & Begg, 2014). A management layer in DBaaS monitor and configure the database to achieve optimized scaling, high availability, multi-tenancy and effective resource allocation in cloud.

Several architectural options available to implement SQL database on cloud, some of these models are (Connelly & Begg, 2014):

1. Separate servers

2. Shared server, separate database server process

3. Shared database server, separate database

4. Shared database, separate schema

5. Shared database, shared schema

Separate Server

This approach will have separate server and database for each tenant thus providing high degree of isolation, support large databases, high number of users and specific performance requirements with high cost (Connelly & Begg, 2014). Below Fig. 1 shows that group of users “A” and “B” are accessing their respective database via their own servers.

Figure 1: Separate Server

Shared Sever, separate database server process

In this architecture, different group of users have their own databases but using a shared server, shown below in Fig 2 (Connelly & Begg, 2014). This is a typical virtualized environment where server resources like processing etc. are virtualized for different group of users thus not available for other users. Performance may be an issue but security is not.

Figure 2: Separate database server process with shared server

Shared database server, separate database

In comparison to above architecture, this model offer separate database for group of users but all users share a server and process which improve efficiency and utilization of resources.

Figure 3: Separate database with shared server process

Shared database, separate schema

This architecture offer shared server, shared server process and shared database. However different group of users will have their own schema thus requiring strict DBMS permission structure (Connelly & Begg, 2014).

Figure 4: Shared database with separate schema

Shared database, shared schema

This architecture share the server, server process, database and schema thus every database table require a column to identify intended group of user (Connelly & Begg, 2014). This is the most effective solution in terms of cost, hardware and software with lowest data isolation. However this approach require additional security efforts to ensure that different group of users don’t access data of other group of users.

Figure 5: Shared database with shared schema

Alternate approaches

SQL on Hadoop in Cloud – PaaS

Another approach is to deploy SQL on Hadoop in cloud using Platform as a Service (PaaS) like Microsoft Azure, Amazon Web Services (AWS), Google Cloud Platform, Rackspace etc. Analytical service like Hive and Spark are usually preconfigured on Hadoop in a fully elastic and on-demand PaaS environment (Poggi, Berral, Fenech, Carrera, Blakeley, Minhas, & Vujic, 2016). The latest offerings from PaaS providers facilitate planning free infrastructures by separating processing from storage with enhancements in service times and reliability.

SQL in Cloud – IaaS

With the use of an off the shelf DBMS on a rented virtual machine hosted on a cloud, IaaS architecture can be realized (Zhang & Holland, 2015). Applications connect to database via APIs like JDBC or ODBC. Besides using virtual machine to host a database, this approach doesn’t offer much because one is fully responsible to maintain, install, configure, backup, recovery and system management. For persistency, virtual machine needs to be connected to persistent storage system thus difficult to scale.

Critical Requirements & Issues to be resolved for SQL on Cloud

Some of the important requirements to host SQL on cloud are (Bernstein, Cseri, Dani, Ellis, Kalhan, Kakivaya, Lomet, Manne, Novik, & Talius, 2011):

ACID – Although atomicity, consistency, isolation and durability are standard requirements of traditional RDBMS but not universally offered by web applications like Amazon’s Dynamo and Yahoo’s PNUTS.

High Availability – replication of data is necessary to offer high availability while using commodity hardware.

Failure recovery – reconfiguration of failed data using global partition management techniques.

Scalable – processing and storage resources should be scaled up and down easily with minimal intervention.

Cost Effective – compared to hosting a traditional server based database, SQL on cloud should be cost effective in low load as well as high load conditions.

Security – balance between complete isolation offering high security at high cost and less isolation with less cost needed to be achieved per requirements.

Reference

Connolly, T. & Begg, C. (2014). Database Systems: a practical approach to design, implementation, and management (6th ed.). Upper Saddle River, NJ: Pearson.

Poggi, N., Berral, J., Fenech, T., Carrera, D., Blakeley, J., Minhas, U., & Vujic, N. (2016). The state of SQL-on-Hadoop in the cloud. 2016 IEEE International Conference on Big data, 1432-1443

Johnson, J. (2009). SQL in the clouds. IEEE Computing in Science & Engineering, 11(4), 12-28

Bernstein, P., Cseri, I., Dani, N., Ellis, N., Kalhan, A., Kakivaya, G., Lomet, D., Manne, R.,

Novik, L., & Talius, T. (2011). Adapting Microsoft SQL server for cloud computing. 2011 IEEE 27th International Conference on Data Engineering, 1255-1263

Zhang, W. & Holland, D. (2015). Containerized SQL Query Evaluation in a cloud. 2015 IEEE International Conference on Smart city/SocialCom/SustainCom (Smart City), 1010-1017