Data Types and Volume

When we use the term data in the IT context, we automatically think of databases, SQL, tables, indexes, fields, and so forth. That is the traditional definition of data, also known as structured data.

However, not all data today fall into columns with fields and indexes. That is certainly not the case with videos or photos, aside from the metadata of such content. But you cannot afford not to classify them as data. For example, take the case of security video footage or videos captured by law enforcement body cameras. These are all content that is being used in line of business (LoB) applications and sometimes in court for legal proceedings. Therefore, they are truly the new data types that need to be handled differently. We call this content unstructured data.

Data also changes over time. Take a retailer, for example. Let’s look at one type of product that a retailer might sell, like a backpack. This simple product can easily fit into a structured database with fields like size, color, weatherproof qualities, or not. Then came along a change in trend that introduced a single strap backpack. Should the retailer add a field for the number of straps or perhaps an indicator field for a single strap and apply it to the entire table? Recently there is also the trend for smart backpacks with charging capabilities for electronic devices. Then came along airport security (e.g., TSA-friendly) backpacks. The bottom line is that new features and trends are introduced to products every time. So instead of adding new fields to the entire table that affects the entire product class, it may be better to have those fields present only when applicable. This is called semi-structured data.

There is also another better way to define certain data relationships rather than a traditional key/value pair in columns. For example, it is easier to map out relationships of data points by representing them as vertices and edges. This is widely used in social media to map the relationships between people. The database that manages this kind of data is known as a graph database .

By now, you are probably no longer surprised you need more storage space than you purchased and continue to fall short all the time. Not too long ago, it was gigabytes, but today, we are commonly referring to consumer-based storage in the terabyte range. That is because video takes up a lot of space. Your chat history with shared and reshared content are space hogs. You need the backup of precious digital photos. The type of content and the social aspect of the content drives the amount of volume such content.

The introduction of different data types—semi-structured, unstructured, or graph requires different approaches in storing and handling such data. The footprint of such data contributes significantly to the volume of data that needs to be managed today.

Data Analysis Trends

You may have heard of data-driven decision making. Data scientists are one of the fastest-growing professions. Colleges are introducing data science curriculums and degrees. Online and distant learning opportunities for professionals who are already in the workforce see high enrollments in such programs.

At the same time, tools such as Databricks and technologies that support big data like Hadoop clusters are rapidly being deployed to support modern data analysis so organizations can take advantage of data-driven decision making. Why all this investment? Because data-driven decision making leads to better customer service, lower operational costs, and in many cases, improve and save lives. Knowing what, when, and why to carry out certain actions is always better than guessing. The simple rule of thumb is that more data improve the quality of decision making.

The problem is that more data also takes up more compute and memory, and the decision-making timing may be cyclical, but also have a rigid timeframe. In other words, it is pointless to spend more to deploy and maintain a big data analysis infrastructure if the activity is carried out once annually, but it is also pointless if the right decision is made too late.

Modern Data Roles

With new and modern data comes new and redefined roles. We briefly mentioned data scientists. These are the professionals who take the massive amount of data, often time from disparate data sources, and conduct data exploration, experiments, and analysis to find correlations between the data.

To make the data accessible so data scientists can do their jobs is the role of the modern data engineer. Data engineers should not be confused with database administrators, although we are seeing a natural transition of professionals from the database administrator roles to that of the data engineer. We have also seen data scientists serve as the data engineer as well, especially in smaller organizations.

In either case, the role of the data engineer is very defined. It is a role that makes the data available in a useable form for data scientists to do the analysis. Making data available involves moving, copying, cleaning, consolidating, merging, and updating data into a central and easily accessible location. It has its roots from the ETL (extract-transform-load) days.

Data engineers need to know which is the right repository for the type of data. For example, as discussed, unstructured data should not be stored in structured databases. So, the data engineer ensures the efficient and best storage medium for the different types of data.

Data engineers also need to ensure security for the data. In recent years, many privacy policies have had a worldwide impact, even though they may originate from certain jurisdictions. One good example is the General Data Protection Regulation (GDPR), which is a European Union (EU) law aimed at protecting EU citizens’ privacy. Data security and access restrictions need to be in place to protect data belonging to EU citizens, but since we are in a global economy, EU citizens are no longer only in the European continent. Even small organizations are multinational if they do business on the Internet. Therefore, GDPR is a globally impacted regulation that everyone needs to adhere to. Using this single regulation as an example, all data engineers need to ensure GDPR data privacy, security, and notification requirements are in place. Throw in other regulations, such as personally identifiable information (PII) , Health Insurance Portability and Accountability Act (HIPAA), Internal Revenue Service form 1075 (IRS 1075), and so forth. While there may be overlapping requirements, the data engineer needs to ensure that they are all adhered to.

What a data engineer should not need to do is to ensure the technology platform is always available and healthy. For example, the data engineer should not need to patch the SQL Database or Cosmos DB every month. The data engineer should not need to ensure the geo-redundant capability is working, just that it is in place if there is a requirement for geo-redundancy of the data in accordance with the organizations’ business continuity and disaster recovery (BCDR) plan.

Data Platform as a Service

Data PaaS is the consumption of database services as a PaaS. This is a narrower definition of a PaaS to mean that it relates to data. It is sometimes incorrectly used interchangeably with data as a service (DaaS). We see DaaS as a definition of data available for consumption.

Data engineers deploying the right type of database to store data without having to manage the VM and the database engine and software is an example of PaaS.

Once data is cleaned, merged, consolidated, or the often used word is wrangled to the point where data scientists can analyze the data, or business users can browse to data repositories with their PowerBI tool and explore the data. Then this is considered data as a service. In Microsoft Azure, Azure SQL Database, and Cosmos DB are two examples of data PaaS. Azure Data Catalog is also an example of a data PaaS that is designed to deliver DaaS to the organization.

You have read from previous chapters the benefit of PaaS in other non-data specific terms, like Azure Cognitive Services and Azure Web Apps. The same benefits that PaaS delivers to these other areas are identical to the data realm: better redundancy, better resiliency, and better overall SLA. But remember our discussion regarding GDPR, PII, and all the numerous regulatory requirements?

Data PaaS offerings in Azure already legally adhere to these requirements. If there are required certifications in place, Microsoft already has the relevant documentation and certifications, so data engineers and organizations adopting the data PaaS can automatically ensure that the proper security requirements are in place.

The other data PaaS offers traditional on-premises databases. The common misconception for data PaaS is the network latency as it relates to data exchange since the data PaaS in the cloud is a remote location. Depending on the architecture and the use cases, this is often not the case.

If an application remains on-premises and pulls data in a traditional client-server approach, there is latency. This should be addressed with an application modernization effort, or, at the very least, combining the lessons learned in Chapter 12 from using web apps as PaaS integrated with data PaaS.

The other use case is in the field of data-driven decision making and the work of the data scientists. If the analytical tools are also PaaS, like Azure Databricks, and then there not be any latency issues. Of course, having to copy data on-premises to Azure take time from a transfer perspective, this is usually a batch process just by the nature of a data warehouse’s role, which is an Online Analytical Processing (OLAP) role as opposed to Online Transaction Processing (OLTP). Furthermore, remember the discussion related to data volume, the ability to store large amounts of data, and then assign sufficient memory and compute to process the analysis against the data on a consumption basis rather than a capital expense yield significant savings.

Hands-on with Azure SQL Database

Azure SQL Database is best for modern cloud applications that need a scalable database that is fully managed, has built-in high availability, and promises the lowest administrative overhead.

1. 1.

   As with all the other exercises throughout this book, we assume you already have an Azure subscription. Go to the Azure portal at https://portal.azure.com.

    
2. 2.

   Click Create a resource and type SQL Database in the search box, and then select SQL Database in the results, as seen in Figure 17-2.

    

1. 3.

   Click the Create button.

    
2. 4.

   Select the subscription, an existing or new resource group, and a name, and then click Create new to create a new database server to host this database. In the future, when you create another Azure SQL Database, you can use this same database server. Unlike the traditional SQL server licensing model, there is no SQL core or CPU licensing associated with spinning up a server to support Azure SQL databases, but there are compute and storage charges incurred based on what you select. For example, the charges for the server to support Azure SQL databases in Figure 17-3 is for a 2 vCore, 32GB server. Visit https://docs.microsoft.com/en-us/azure/sql-database/sql-database-resource-limits-database-server to learn more about the capacity limitations, but as an example, with this single database server deployment, you can reuse it for up to 5000 Azure SQL Databases.

    

1. 5.

   In the New server pane, provide a name for the server and the admin login credentials. The admin credentials you enter here is the SQL dba credentials. Select the region to place this database server and click OK.

    
2. 6.

   Select No for the SQL elastic pool for now. We discuss what elastic pool is later. Click Configure database in the Compute + storage section.

    
3. 7.

   Take note of the different scale options: General Purpose, Hyperscale, and Business Critical. Look at the characteristics, such as the IOPS and latency for each option, as seen in Figure 17-4. Click each box to see other details. In an actual deployment, select the option that meets the performance requirements of your database. For now, once you have explored the different options, select General Purpose.

    

At this point, you can access the Azure SQL Database that is hosted on this database server.

Assuming you are using SSMS as your client administrative tool, launch SSMS and connect to the server using the full URL of the server and the SQL dba login you created in step 5. Click Connect, as referenced in Figure 17-8. If your firewall is properly configured, as outlined in step 17, you are connected to the database server.

In the future, if you create additional Azure SQL Databases but reuse the same database server, you see those databases in SSMS as well. Furthermore, you can choose to create a new database from within SSMS, and that creates a new Azure SQL Database instead of going through the portal.

We did not go into extensive details on the use of SSMS or other tools because we assume that you already know about database administration and access. What this exercise was designed to do is to show you the provisioning of Azure SQL Database and the important features. Still, tools like SSMS that are compatible with traditional SQL server databases continue to work.

Applications that reference the database servers need to use the new URL, but if a CNAME record in DNS exists for the database URLs applications are configured to use, and the CNAME is repointed to the database server hosting Azure SQL Database, you likely would not need to make any changes to the applications themselves.

Azure SQL Managed Instance

Azure SQL Managed Instance is another deployment model of Azure SQL Database PaaS but with greater integration with a hybrid architecture.

Figure 17-9 depicts the difference in the access model between Azure SQL Database and Azure SQL Managed Instance, especially with the introduction of Azure Private Link for PaaS.

Both Azure SQL offerings provide all the benefits of PaaS. For Azure SQL Databases, access to the database that has been provisioned on the virtual server instance is via a URL that is associated with a public endpoint (<host>.database.azure.net) as we saw in the previous exercise. With Private Link, that public endpoint can have a URL that is a private IP address owned by the customer organization, thereby further obscuring the endpoint for potential exploitation.

With Azure SQL Managed Instance, the database PaaS is tied to a virtual network (VNet), which is inherently private and isolated. This VNet is then connected to the on-premises network via a Site-to-Site VPN or ExpressRoute. We covered hybrid networking in Chapter 9.

Azure SQL Managed Instance is a new deployment option that provides native VNet integration for customers that have security requirements that mirror traditional on-premises SQL server deployments and are not exposed to the public Internet. It is also a model for applications that need to remain on-premises and access the database via a high-speed, dedicated link to Azure like ExpressRoute, where latency is minimized or eliminated.

Another important differentiator between Azure SQL Database and Azure SQL Managed Instance is that it has nearly a 100% compatibility with the latest SQL Server that is deployed on-premises. In fact, the capabilities in Azure SQL Managed Instance are greater than SQL Server on-premises. As such, Azure SQL Managed Instance makes it likely that customers can lift-and-shift on-premises databases that serve on-premises applications with minimal disruptions.

Deploying an Azure SQL Managed Instance is very similar to deploying Azure SQL Database, with the exception that you select an existing VNet or configure a new one to deploy Azure SQL Managed Instance to, and then provide an IP address that is in that VNet’s virtual IP range.

Elastic Pools

The third Azure SQL Database deployment type is the use of Elastic Pools. Azure SQL Database elastic pool is a method of housing multiple Azure SQL Databases on a single Azure SQL Database server but letting Azure scale the capacity of the server based on a set price.

Elastic pools are most beneficial to software as a service (SaaS) application providers. In a SaaS delivery model, application providers may deploy multiple separate databases for each SaaS customer organization using a common application. Customer organizations may be different in size and have different peak usage. Traditionally, SaaS providers deploy infrastructure that can handle the peak utilization of all their customers to ensure that SLAs are met. This is often a costly strategy because there be times that the compute and IOPs remain idle. The solution is to house these customer databases on an elastic database server, so all the databases share the same resources on the server. It is up to the server to allocate and scale the right amount of resources to each database in real time based on current and historical consumption. This leads to predictable budgets for the SaaS provider without compromising the ability to meet published SLAs.

Hands-on with Elastic Pools

To close out the discussion on elastic pools, we have two other Azure SQL Databases in our lab environment. They were expected to have very low utilization, as referenced in Figure 17-12 so we used a small DTU purchasing model that results in approximately $15/month for each database.

Repeating the steps 6 and 7 from the previous exercise, we added these two databases to the elastic pool because we know that there is enough capacity to accommodate these two databases. By doing so, we saved $30/month since both databases are now absorbed into the cost of the elastic pool.

Hands-on Tuning and Monitoring Azure SQL Databases

One of the benefits of using PaaS in Azure is thebuilt-in

performance tuning and monitoring capabilities. In this exercise, we explore these capabilities using the database we created earlier.

To observe the results of tuning, we first set up the test environment by downloading and installing HammerDB, which is a free load testing tool for databases.

Next Steps: Self-Guided Assignment

Before we leave this exercise, create a table in the tpcc database that we are using in Chapter 19.

This concludes our exploration of Azure SQL Database. For a detailed discussion and a much deeper dive on Azure SQL Database, a forked copy of Microsoft’s extensive documentation on Azure SQL Database is on our GitHub repo at https://github.com/harris-soh-copeland-puca/azure-docs/tree/master/articles/sql-database.

Use Cases for Azure Cosmos DB

Now that you know the key characteristics of Azure Cosmos DB, we can explore the use cases that make Azure Cosmos DB the preferred data source. The commonly shared requirements among the use cases are the need for low latency, data availability on a global footprint, and data that does not adhere to a strict schema.

Hands-on: Deploying Azure Cosmos DB

You have seen how easy it is to replicate to multiple locations throughout the world. Let’s now use Azure

Cosmos DB to store data.

Hands-on: Using Azure Cosmos DB to Store Bot Conversation History

Remember Joe theBot from Chapter15? We are going to add more capabilities to Joe with the help of Cosmos DB. What we do in this exercise is to store conversation history. So, what are the benefits of storing bot conversation history?

Conversation history is a good source for analytics. For example, with conversation history, we can send that to Azure Cognitive Services for sentiment analysis to determine how satisfied customers are with their engagement with Joe. We can also mine conversation history to determine what were the top issues being asked. From a more traditional standpoint, conversation history may be a regulatory requirement or would be helpful in audits. There are many good reasons why conversation history should be stored.

This concludes our discussion on and hands-on implementation of Azure Cosmos DB. Documentation on Azure Cosmos DB is on GitHub at https://github.com/harris-soh-copeland-puca/azure-docs/tree/master/articles/cosmos-db.