Tag: Relational Database

100 Days of Cloud – Day 63: Azure SQL Server

Its Day 63 of my 100 Days of Cloud journey, and today I’m looking at SQL services in Azure and the different options we have for hosting SQL in Azure.

As we discussed in the previous post, SQL is an example of a Relational Database Management System (RDBMS), which follows a traditional model of storing data using 2-dimensional tables where data is stored in columns and rows in a pre-defined schema.

On-premise installations of Microsoft SQL Server would follow the traditional IAAS model, where we would install a Windows Server operating which provides the platform for the SQL Server Database to run on.

In Azure, we have 3 options for migrating and hosting our SQL Databases.

SQL Server on Azure VM

SQL Server on Azure VM is an IaaS offering and allows you to run SQL Server inside a fully managed virtual machine (VM) in Azure.

SQL virtual machines are a good option for migrating on-premises SQL Server databases and applications without any database change.

This option is best suited where OS-level access is required. SQL virtual machines in Azure are lift-and-shift ready for existing applications that require fast migration to the cloud with minimal changes or no changes. SQL virtual machines offer full administrative control over the SQL Server instance and underlying OS for migration to Azure.

SQL Server on Azure Virtual Machines allows full control over the database engine. You can choose when to start maintenance/patching, change the recovery model to simple or bulk-logged, pause or start the service when needed, and you can fully customize the SQL Server database engine. With this additional control comes the added responsibility to manage the virtual machine.

Azure SQL Managed Instance

Azure SQL Managed Instance is a Platform-as-a-Service (PaaS) offering, and is best for most migrations to the cloud. SQL Managed Instance is a collection of system and user databases with a shared set of resources that is lift-and-shift ready.

This option is best suited to new applications or existing on-premises applications that want to use the latest stable SQL Server features and that are migrated to the cloud with minimal changes. An instance of SQL Managed Instance is similar to an instance of the Microsoft SQL Server database engine offering shared resources for databases and additional instance-scoped features.

SQL Managed Instance supports database migration from on-premises with minimal to no database change. This option provides all of the PaaS benefits of Azure SQL Database but adds capabilities that were previously only available in SQL Server VMs. This includes a native virtual network and near 100% compatibility with on-premises SQL Server. Instances of SQL Managed Instance provide full SQL Server access and feature compatibility for migrating SQL Servers to Azure.

Azure SQL Database

Azure SQL Database is a relational database-as-a-service (DBaaS) hosted in Azure that falls into the category of a PaaS offering.

This is best for modern cloud applications that want to use the latest stable SQL Server features and have time constraints in development and marketing.
A fully managed SQL Server database engine, based on the latest stable Enterprise Edition of SQL Server. SQL Database has two deployment options:

  • As a single database with its own set of resources managed via a logical SQL server. A single database is similar to a contained database in SQL Server. This option is optimized for modern application development of new cloud-born applications. Hyperscale and serverless options are available.
  • An elastic pool, which is a collection of databases with a shared set of resources managed via a logical SQL server. Single databases can be moved into and out of an elastic pool. This option is optimized for modern application development of new cloud-born applications using the multi-tenant SaaS application pattern. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.

Overall Comparisons

Both Azure SQL Database and Azure SQL Managed Instance are optimized to reduce overall management costs since you do not have to manage any virtual machines, operating system, or database software. You do not have to manage upgrades, high availability, or backups.

Both options can dramatically increase the number of databases managed by a single IT or development resource. Elastic pools also support SaaS multi-tenant application architectures with features including tenant isolation and the ability to scale to reduce costs by sharing resources across databases. SQL Managed Instance provides support for instance-scoped features enabling easy migration of existing applications, as well as sharing resources among databases.

Finally, the database software is automatically configured, patched, and upgraded by Azure, which reduces your administration

The alternative is SQL Server on Azure VMs which provides DBAs with an experience most similar to the on-premises environment they’re familiar with. You can use any of the platform-provided SQL Server images (which includes a license) or bring your SQL Server license. All the supported SQL Server versions (2008R2, 2012, 2014, 2016, 2017, 2019) and editions (Developer, Express, Web, Standard, Enterprise) are available. However, as this is a VM, it’s up to you to update/upgrade the operating system and database software and when to install any additional software such as anti-virus.

Management

All of the above options can be managed from the Azure SQL page in the Azure Portal.

Image Credit -Microsoft

Migration

In order to migrate from existing SQL Workloads, in all cases you would use an Azure Migrate Project with the Data Migration Assistant. You can find all of the scenarios relating to migrations options here.

Conclusion

And thats a look at the different options for hosting SQL on Azure. Hope you enjoyed this post, until next time – I feel like going bowling now!

100 Days of Cloud – Day 62: Azure Database Solutions

Its Day 62 of my 100 Days of Cloud journey, and today I’m starting to look at the different Database Solutions available in Azure. “The Dude” to me to …..

The next 2 posts are going to cover the 2 main offerings – Azure SQL and Azure Cosmos. But first, we need to understand the different types of database that are available to us, how they store their data and the use cases where we would utilize the different database types.

Relational Databases

Lets kick off with Relational Database Management Systems, or RDBMS. These are the traditional model of storing data, and organises the data into 2-dimensional tables which have a series of rows and columns into which the data is stored.

RDBMS Databases follow a schema based model, where the data structure of the schema needs to be defined before any data is written. Any subsequent read or write operations must use the defined schema.

Vendors who use this model provide a version of Structured Query Language (SQL) for retrieving and managing the data. The most common examples of these would be Microsoft SQL, Oracle SQL or PostgreSQL.

RDBMS is useful when data consistency is required, however the downside is that RDBMS cannot easily scale out horizontally.

In Azure, the following RDBMS services are available:

  • Azure SQL Database – this is the full hosted version of SQL Server.
  • Azure Database for MySQL – open source relational database management system. MySQL uses standard SQL commands such as INSERT, DROP, ADD, and UPDATE, etc. The main purpose of MySQL is for e-commerce, data warehouse, and logging applications. Many database-driven websites use MySQL
  • Azure Database for PostgreSQL – this is a highly scalable RDBMS system which is cross-platform and can run on Linux, Windows and MacOS. PostgreSQL can perform complex queries, foreign keys, triggers, updatable views, and transactional integrity.
  • Azure Database for MariaDB – High performance OpenSource relational database based on MySQL. Dynamic columns allow a single DBMS to provide both SQL and NoSQL data handling for different needs. Supports encrypted tables, LDAP authentication and Kerberos.

The main use cases for RDBMS are:

  • Inventory management
  • Order management
  • Reporting database
  • Accounting

Non-Relational Databases

The opposite of relational databases are non-relational database, which is a database that does not use the tabular schema of rows and columns found in most traditional database systems. Instead, non-relational databases use a storage model that is optimized for the specific requirements of the type of data being stored. For example, data may be stored as simple key/value pairs, as JSON documents, or as a graph consisting of edges and vertices.

Because of the varying ways that data can be stored, there are LOADS of different types of non-relational databases.

Lets take a look at the different types of non-relational or NoSQL database.

  • Document Data Stores
Image Credit – Microsoft

A document data store manages a set of named string fields and object data values in an entity that’s referred to as a document. These are typically stored in JSON format, but can also be stored as XML, YAML, JSON, BSON, or even plain text. The fields within these documents are exposed to the storage management system, enabling an application to query and filter data by using the values in these fields. Typically, a document contains the entire data for an entity, and all documents are not required to have the same structure.

The application can retrieve documents by using the document key, which is hashed and is a unique identifier for the document.

From a service perspective, this would be delivered in Azure Cosmos DB.

Examples of use cases would be Product catalogs, Content management or Inventory management.

  • Columnar data stores
Image Credit – Microsoft

A columnar or column-family data store organizes data into columns and rows, which is very similar to a relational database. However, while a column-family database stores the data in tabular data with rows and columns, the columns are divided into groups known as column families. Each column family holds a set of columns that are logically related and are typically retrieved or manipulated as a unit. New columns can be added dynamically, and rows can be empty.

From a service perspective, this would be delivered in Azure Cosmos DB Cassandra API, which is used to store apps written for Apache Cassandra.

Examples of use cases would be Sensor data, Messaging, Social media and Web analytics, Activity monitoring, or Weather and other time-series data.

  • Key/value Data Stores
Image Credit – Microsoft

A key/value store associates each data value with a unique key. Most key/value stores only support simple query, insert, and delete operations. To modify a value (either partially or completely), an application must overwrite the existing data for the entire value. Key/value stores are highly optimized for applications performing simple lookups, but are less suitable if you need to query data across different key/value stores. Key/value stores are also not optimized for querying by value.

From a service perspective, this would be delivered in Azure Cosmos DB Table API or SQL API, Azure Cache for Redis, or Azure Table Storage.

Examples of use cases would be Data caching, Session management, or Product recommendations and ad serving.

  • Graph Databases
Image Credit – Microsoft

A graph database stores two types of information, nodes and edges. Edges specify relationships between nodes. Nodes and edges can have properties that provide information about that node or edge, similar to columns in a table. Edges can also have a direction indicating the nature of the relationship.

Graph databases can efficiently perform queries across the network of nodes and edges and analyze the relationships between entities.

From a service perspective, this would be delivered in Azure Cosmos DB Gremlin API.

Examples of use cases would be Organization charts, Social graphs, and Fraud detection.

Conclusion

And thats a whistle stop tour of the different types of databases available in Azure. There are other options such as Data Lake and Time Series, but I’ll leave those for future posts as they are bigger topics that deserve more attention.

Hope you enjoyed this post, until next time – I feel like going bowling now!