Chapter 7 Database

Global From The Go

One of the key aspects of Cosmos is its ability to be global from the start, and with a click of a button. Why is this a big deal, you might ask? Well, having data near your users is crucial for providing the best user experience. Traditional databases were notoriously difficult to set up across multiple locations and keep them in sync. With Cosmos DB, the entire headache of global distribution of your data is taken care of by Azure. For example, if you have data in the US East, West Europe, and South Africa, and want to expand to Australia, all you have to do is click on that region in the Azure portal to enable it. Azure then takes care of moving the data and keeping it in sync. Pretty neat, if you ask me.

Easy with Cosmos

Traditional databases that weren’t cloud enabled could be very difficult to set up across multiple regions. Azure takes care of all of it with Cosmos.

One Click to Add Regions

It is very easy to expand to more regions with Cosmos DB and have the data stay in sync.

Continued Synchronization

Cosmos DB stays on top of all reads and writes to your data and makes sure data is moved between regions to stay in sync.

Latency, the time it takes for data to travel, is a critical factor. Cosmos DB promises single-digit millisecond latency anywhere in the world. That is less time than it takes for you to blink. Try it. Nope, Cosmos is still faster.

Automated

Cosmos DB automatically scales to meet resource demand.

Infinite Resources

Any number of users of your application can be supported.

Lowest Price

Even though the scaling is automatic, you only pay for the resources you use.

You can scale Cosmos DB to infinity and beyond. When demand goes up, you can set Cosmos to automatically scale with demand for each region and instance. More resources are added, and there is no known limit to how many users can be supported. Your users are all met with open arms. The best part is that you still only pay for what you use. As your application users increase, you can dial your Cosmos up to follow along.

Developer

Choose from various software development kits (SDKs) and application programming interfaces (APIs).

Languages

A language for most modern developers, including C#, Java and Node.js.

Platforms

Choose from lots of data platforms to integrate with, including SQL, MongoDB and Cassandra.

Cosmos DB supports a wide range of ways to work with it. If you're a developer, you can choose from various built-in ways to connect your application to Cosmos, such as software development kits (SDKs), application programming interfaces (APIs), and more. You can use different languages, such as C#, Java, and Node.js. There's also a wide range of platforms, such as SQL, MongoDB, and Cassandra, to make it as seamless as possible to use.

One thing to be cautious about with Cosmos DB is that it can get expensive. You certainly get what you pay for, but costs can increase as Cosmos scales up. It's something to keep an eye on. Azure has many great tools to help you stay on top of costs, but those are outside the scope of this course.

Costs can run up quickly!

Microsoft SQL Database was launched in 1989 and has been one of the most stable Microsoft products since. It's no surprise then that there is a Managed Azure SQL service as well. Azure SQL is, in effect, a database as a service. As with other fully managed services, Azure will take care of your hardware and infrastructural level needs, including servers, storage, network, and everything else infrastructure-related. Azure SQL sits on top of this and provides you with the business logic and functions you need.

Think of Azure SQL as a fully-fledged database solution where Azure manages all the parts that don't have anything directly to do with managing the data in the database. One of the main use cases highlighted by Microsoft for Azure SQL is that you can migrate your on-premises SQL server instances directly to Azure and get all the benefits of managed instances. No changes are needed to your on-premises databases, and the process is frictionless. This can result in significant cost savings for businesses, bringing down the Total Cost of Ownership (TCO).

Azure SQL takes advantage of being on a cloud platform by integrating machine learning aspects too. Database performance and optimization suggestions are provided based on your usage, patterns, and other metrics. Machine learning also warns of any degradation and detects if there are issues affecting your database's performance. There's a dedicated lecture on machine learning in this course for you to learn more.

Optimization

Suggestions on how to optimize and improve performance of Azure SQL instances.

Warnings

You will get warnings about degrading instances and if anything out of the ordinary is happening.

Being a cloud service, Azure SQL offers excellent scalability and high availability. It supports up to a hundred terabytes in size and can restore large databases in just a matter of minutes if necessary. Security is also a key aspect of managed services, and with Azure SQL, you benefit from the extensive team behind Azure and their efforts in securing the platform. You don't have to worry about data security at the infrastructure level, as Azure handles that.

Scalabiliy:

Scale your Azure SQL instances and get high availability as well.

Space:

Manage huge databases up to 100 terabytes. Yeah, enormous.

Security

Benefit from the built-in security features of the Azure cloud platform.

Azure SQL Database Azure SQL Managed Instance

• Most like traditional SQL Server

Azure SQL Managed Instance

• Aimed at migrating from on-premises

While I've been referring to Azure SQL as if it's a single product, there are actually two: Azure SQL Database and Azure SQL Managed Instance. The SQL Database has more traditional SQL features, while the SQL Managed Instance is designed to bridge the gap between on-premises SQL server and Azure SQL Database. There's a table provided to show the differences between the two. Although exam questions might not delve into these specifics, it's good to be aware of them.

In this lecture, we delve into the community-based counterpart of Azure SQL: Azure Database for MySQL. There are various types of databases, and we have already explored Azure SQL in this chapter, which is a Microsoft product. In contrast, MySQL is a product built and maintained by the community. It is an open-source project where anyone can contribute to new features and maintenance. Like Azure SQL, MySQL is relational, meaning the data is related to itself and other pieces through defined connections in the database. For example, the table of customers is related to the orders table, establishing which customer made which order.

MySQL is widely used, with millions of databases in operation, and it underpins a significant portion of the public internet. Its mature and proven architecture is a testament to its reliability. As reiterated throughout this course, the advantage of using Azure services lies in their nature as Platform as a Service (PaaS) offerings. Azure Database for MySQL is no exception, completely managed by Microsoft, eliminating the need for you to handle hardware, servers, or security concerns.

Microsoft emphasizes the following advantages of using the managed version of MySQL:

1. Focus on developing your core business processes instead of managing infrastructure.

2. Use any language and framework of your choice. For instance, popular content management systems like WordPress, which uses MySQL and PHP, can be deployed on Azure using Azure Database for MySQL.

3. Achieve high availability and scale your applications rapidly to accommodate a larger user base, thanks to the capabilities of the cloud.

4. Leverage Azure's security features, including advanced threat protection, monitoring, and identity management.

5. Benefit from PaaS capabilities such as automatic database patching, automated backups, and built-in monitoring—all at no extra cost.

   
   

Azure highlights various use cases for MySQL, similar to other relational database types:

1. Web applications incorporating machine learning and analytics.

2. E-commerce implementations for online shopping platforms.

3. Mobile applications capable of scaling to millions of devices.

4. Digital marketing experiences dealing with significant spikes in traffic.

5. Finance management applications handling and managing highly sensitive data.

6. Gaming applications managing large spikes in data and traffic with low latency requirements.

Azure Database for MySQL stands as another excellent option in Azure's array of managed database services.

Azure extends its managed database services with Azure Database for PostgreSQL, commonly referred to as Postgres. This open-source relational database is similar to MySQL, as covered in this chapter. The name "Postgres" originates from being based on a database called Ingres, with "Post" indicating that it came after Ingres. Over time, it evolved into PostgreSQL to denote its support for SQL, the language used for querying data in the database. Postgres has been freely available since 1996, and it has gained a significant user base, even serving as the default database for macOS.

PostgreSQL boasts several features that attract developers and businesses:

1. Rich Extensions: It integrates with numerous additional features through extensions, including JSONB (a binary version of the JSON data format), spatial capabilities, rich indexing, integration with Visual Studio Code, and more.

2. Language Flexibility: Users can employ their programming language of choice, such as Ruby on Rails or Python.

3. Hyperscale Horizontal Scaling: It supports high-performant distributed data usage across many PostgreSQL instances, allowing easy scaling to hundreds of nodes without requiring application rewrites.

4. Performance Recommendations: The platform provides recommendations based on how users interact with the data and the database.

5. Monitoring and Detection: Features are in place to monitor and detect disruptive events that may impact performance, empowering users to take corrective action.

PostgreSQL on Azure benefits from fully managed cloud capabilities, including automatic database patching, backups, and built-in monitoring—all without additional costs.

Various industries find PostgreSQL well-suited for their needs:

• Financial Industry: Ideal for online transaction processing.

• Mathematical Software Integration: Can be seamlessly integrated with mathematical software like MATLAB and R.

• Government (GIS): Used for handling geometric data (GIS) with the powerful PostGIS extension.

• Industrial Manufacturing: Chosen for its reliability, supporting automatic failover, full redundancy, and almost zero downtime upgrades crucial for the manufacturing industry.

Single Tool

One step migration for Microsoft SQL to Azure SQL.

Documentation

Comprehensive step-by-step guides and documentation for helping you migrate.

Guides for non-MS

Very detailed guides for migrating from non-Microsoft databases.

In the preceding lecture, we delved into the topic of migrating databases to Azure, specifically focusing on SQL Server. Now, you might be wondering how to execute such migrations. Enter the Database Migration Services (DMS) from Azure—a dedicated tool designed to facilitate the seamless transfer of your database from on-premises environments to the cloud. Azure aims to make the migration process as effortless as possible, as their objective is to have you on Azure.

The appeal of Azure DMS lies in its ability to handle the migration of your existing SQL server in one comprehensive sweep. No additional, intricate steps are needed. The tool provides extensive documentation to assist you in selecting the appropriate settings and offers step-by-step guidance to successfully complete the migration process. Additionally, detailed guides are available for migrating from non-Microsoft databases to Azure SQL (a managed Platform as a Service, PaaS) or Azure SQL Server (a non-managed Infrastructure as a Service, IaaS).

Let's illustrate the value of Azure DMS with a practical example. Consider Rainbow Dolphins, a company with an on-premises setup, including an online shop. The current database is a Microsoft SQL instance residing in a server cupboard. With Database Migration Services, Rainbow Dolphins can effortlessly move the SQL server from their on-premises environment to a managed Azure SQL instance in the cloud. Moreover, if they also have a MySQL database on-premises that needs to be migrated to Azure, DMS is equipped to handle that scenario as well. While the second scenario might require a bit more manual effort, Azure Database Migration Services provides step-by-step instructions to guide users through the process, ensuring a smooth and straightforward transition of data to Azure.

Data Warehouse is a database of databases. Collate multiple data sources to provide insights to non-technical employees and customers.

Cosmos DB

Globally distributed database. It is super fast and easy to manage. Scale to infinite performance and size. Can be costly!

Azure SQL

Fully managed and using stable Microsoft SQL Technology. Compatible with on-premises SQL servers.

Database Migration Services

Migration of almost any kind of database to Azure SQL or SQL Server. Guides, step-by-step instructions, comprehensive documentation.

SQL Data Warehouse

Data Warehouse is a database of databases. Collate multiple data sources to provide insights to non-technical employees and customers.