Category SQL Server on Azure Virtual Machines

Describe features of transactional workloads

Transactional workloads play a critical role in ensuring the smooth operation of businesses and maintaining data integrity. These workloads encompass activities such as capturing customer orders, processing financial transactions, and updating inventory levels.

Transactional workloads are designed to handle business operations that involve data modifications, ensuring the accuracy, consistency, and reliability of data. Let’s consider an e-commerce platform that processes customer orders. Each customer order represents a trans-action that requires capturing the order details, updating inventory levels, and recording the financial transaction. These transactions must be executed reliably and in an atomic manner, meaning they should either complete successfully or be rolled back entirely if an error occurs.

Transactional workloads offer several advantages for business. First, they ensure data con-sistency and integrity. The ACID properties guide transactional processing, ensuring that data remains in a consistent state even in the event of failure or concurrent access. This integrity is crucial for financial systems, inventory management, and other critical business functions.

Second, transactional workloads support concurrency control and isolation in a multiuser environment, where multiple transactions can occur simultaneously. Transactional processing mechanisms ensure that transactions are executed independently and do not interfere with each other, maintaining data integrity and preventing conflicts.

Furthermore, transactional workloads facilitate data durability and reliability. Transactional systems employ techniques such as write-ahead logging and database recovery mechanisms to ensure that committed transactions persist even in the face of system failures. This durability ensures that critical business operations can be restored and recovered without data loss.

Transactional workloads are supported by various database systems such as relational databases, where ACID properties are typically enforced. These systems provide transac-tion management features that guarantee data consistency, durability, and isolation. We can say transactional workloads are essential for maintaining accurate data, supporting reliable business operations, and ensuring data integrity. By executing operations in an atomic and consistent manner, businesses can confidently process customer orders, handle financial trans-actions, and manage inventory levels, fostering trust and reliability in their operations.

BUSINESS BENEFITS

When it comes to managing relational databases in the cloud, Azure Database for MySQL stands out as a service that not only streamlines operations but also scales seamlessly and ensures robust security. Here are some of the core benefits that this managed database service offers:

■■ Reduce administrative overhead: By using a fully managed service such as Azure Database for MySQL, you can focus on the application development side of things rather than managing the database infrastructure. Capabilities such as automatic patch-ing, backup, and recovery are taken care of for you.

■■ Scalability on demand: With the scalable nature of Azure Database for MySQL, you can dynamically adjust resources to meet the changing demands of your application.

This flexibility allows you to efficiently manage your workloads without substantial manual invention.

■■ Robust security: Security is paramount when dealing with databases. Azure Database for MySQL incorporates various security measures such as Azure AD authentication, firewall rules, and data encryption at rest and during transit via SSL connections. This added layer of security helps protect your data and reduce the risk of breaches.

Skill 2.2: Describe relational Azure data services CHAPTER 2 57

■■ Compliance: Azure is compliant with a broad set of international and industry-specific regulatory standards. This includes General Data Protection Regulation (GDPR), Inter-national Organization for Standardization (ISO) 27001, Health Insurance Portability and Accountability Act (HIPAA), Service Organization Control (SOC), and others, which can be critical for businesses dealing with sensitive data.

These benefits highlight how Azure Database for MySQL can be a strategic asset for your business, ensuring operational efficiency, scalability, security, and compliance, all within a man-aged cloud service.

USE CASES

Azure Database for MySQL is a versatile cloud database service that caters to a wide array of applications and scenarios, capitalizing on its managed nature and the scalability of Azure. Let’s explore how this service aligns with various use cases:

■■ Web and mobile applications: Azure Database for MySQL is ideal for running web and mobile applications. It supports popular languages and frameworks, which allows developers to continue using the tools they’re familiar with while benefiting from

Azure’s managed service.

■■ Content management systems (CMSs): Many popular content management systems such as WordPress, Joomla, and Drupal use MySQL as their back-end database. Azure Database for MySQL provides a scalable, reliable, and secure platform for hosting these systems.

■■ E-commerce platforms: E-commerce platforms such as WooCommerce and Magento can benefit from Azure Database for MySQL’s scalability and reliability, particularly dur-ing peak demand periods.

■■ Data warehousing: With Azure Database for MySQL, you can store and analyze large volumes of data, making it a suitable option for data warehousing use cases. It also integrates with Azure’s analytics and AI services, providing comprehensive tools for your data warehousing needs.

■■ Mitigation: If you’re running on-premises MySQL databases and planning a migra-tion to the cloud, Azure Database for MySQL could be a prime choice. It offers minimal downtime during migration and compatibility with your existing MySQL applications.

Remember, the specific benefits and use cases for your organization may vary based on your particular situation and requirements. It’s essential to carefully evaluate these aspects when deciding whether Azure Database for MySQL is right for you.

Skill 1.4: Identify roles and responsibilities for data workloads

This section focuses on the critical aspect of identifying roles and responsibilities for data workloads. In the world of data management, different professionals contribute their expertise to ensure the efficient handling, processing, and utilization of data. Understanding these roles and responsibilities is vital for organizations to effectively manage and leverage their data assets.

Skill 1.4: Identify roles and responsibilities for data workloads  CHAPTER 1   19

In today’s data-driven landscape, organizations rely on dedicated professionals to fulfill specific roles related to data management. This skill highlights the significance of recogniz-ing and assigning the appropriate roles within data workloads. By identifying the individuals responsible for specific tasks, organizations can streamline their data operations, promote collaboration, and optimize the overall data management process.

Assigning roles and responsibilities for data workloads ensures that the right expertise is applied to each aspect of data management. Database and administrators, data engineers, and data analysts play pivotal roles in supporting data workloads, each with their unique skill sets and responsibilities.

Identifying these roles helps establish clear line of responsibility and accountability. By understanding and assigning these roles, organizations can foster collaboration and coordi-nation among professionals involved in data workloads. This alignment promotes effective data management, offers smooth data workflows, and maximizes the value derived from data assets.

This exam skill emphasizes the importance of recognizing these roles and responsibilities in the broader context of data workloads. By understanding the significance of each role and its contribution to successful data management, individuals can grasp the collaborative efforts required to leverage data effectively. Let’s take a closer look at each of these data roles and their responsibilities.

This skill covers how to:

• Describe responsibilities for database administrators

• Describe responsibilities for data engineers

• Describe responsibilities for data analysts

Describe responsibilities for data engineers

As a data engineer, your role is vital in designing, constructing, and maintaining the data infrastructure and pipeline to promote efficient data processing and analysis. You play a crucial part in the data management process, ensuring that data flows seamlessly across systems and remains accessible for analysis.

Your primary responsibility is to design and construct the data infrastructure necessary for effective data management. You collaborate with stakeholders to understand their data requirements, identify relevant data sources, and determine the best approach to data integra-tion. You develop data pipelines, ensuring the smooth and reliable flow of data from the source systems to the target destinations.

You are involved in data ingestion, where you extract data from various sources such as databases, files, or APIs. You transform and cleanse the data to ensure its quality and consis-tency, making it suitable for downstream analysis. This may involve tasks such as data extrac-tion, data validation, data cleansing, and data enrichment.

In addition to data ingestion, you are responsible for data transformation and integration. You apply data processing techniques to convert raw data into a usable format, ensuring it aligns with the required data model’s schema. This may involve tasks such as data aggrega-tions, data filtering, data normalization, and data enrichment.

Skill 1.4: Identify roles and responsibilities for data workloads      CHAPTER 1    21

Data engineering also involves developing data processing workflows. You design and implement efficient workflows that orchestrate the movement and transformation of data, ensuring optimal performance and reliability. This may include using workflow management tools or frameworks to schedule and monitor data processing tasks.

An example of your role as a data engineer could be working on a project to develop a real-time analytics platform for a financial institution. You would be responsible for designing and implementing the data infrastructure, ingesting real-time transaction data from multiple sources, transforming and aggregating the data, and making it available for real-time analysis and reporting. Your expertise in data engineering contributes to the overall success of data-driven initia-tives within your organization. By building robust data pipelines, ensuring data quality and reliability, and implementing efficient data processing, you facilitate effective data analysis and drive actionable insights.

Describe responsibilities for data analysts

As a data analyst, your role is crucial in uncovering valuable insight and patterns within data to support informed decision-making within your organization. You play a pivotal role in analyz-ing, interpreting, and visualizing the data to derive meaningful information that drives business strategies. Let’s explore your comprehensive role as a data analyst from a business perspective.

Your primary responsibility is to explore and analyze data to uncover trends, correlations, and patterns that provide valuable insights. You work with various data sources, ranging from structured databases to unstructured text files, and use statistical techniques and analytical tools to extract meaningful information.

Data exploration is an essential part of your role. You dive deep into the data, examining its structure, quality, and relationships. You identify relevant variable and metrics to analyze, ensuring that the data is appropriate for the questions or problems at hand.

Once you have gathered and cleaned the data, you apply statistical analysis tech-niques to identify patterns and relationships. You may perform tasks such as descriptive statistics, hypothesis testing, regression analysis, or clustering to extract insights from the data. These analyses help you uncover trends, anomalies, and relationships that can guide decision-making.

Data visualization is another crucial aspect of your role. You use visual tools and techniques to present data in a clear and concise manner. By creating charts, graphs, and dashboards, you transform complex datasets into easily understandable visual representations. These visualiza-tions help stakeholders to grasp insights quickly and make informed decisions.

22 CHAPTER 1 Describe core data concept

An example of your role as a data analyst is analyzing customer behavior data for an e-commerce company. You would examine the data to understand customer preferences, identify purchasing patterns, and segment customers based on their buying behaviors. These insights would then inform marketing strategies, product recommendations, and customer retention efforts.

Your expertise as a data analyst contributes to evidence-based decision-making within your organization. By analyzing and interpreting data, you provide insights that support strategic planning, optimize operations, and drive business growth.

EXAM TIP

When preparing for the exam, you should focus on understanding the core data concepts and their practical application. Familiarize yourself with different data representation for-mats, storage options, and common data workloads. Pay attention to the roles and respon-sibilities of database administrators, data engineers, and data analysts. Additionally, practice relating these concepts to real-world scenarios to reinforce your understanding. Being able to apply your knowledge to practical situations will help you excel on the exam and in real-world data management scenarios.

Skill 2.1: Describe relational concepts

When exploring relational concepts, you will find yourself thinking about data manage-ment within the cloud-centric world of Microsoft Azure. Data management handling is

29

indispensable for leveraging Azure’s robust data services, which require a keen understanding of relational database intricacies.

Envision organizing data in a structured manner, akin to tables with rows signifying unique records and columns delineating specific attributes. This visualization encapsulates the essence of relational data, which serves as the bedrock for building, querying, and manipulating data-bases in a coherent and systematic fashion. Mastery of relational data concepts is imperative, as it fosters data integrity and efficiency.

Delving deeper, you encounter normalization, a methodical strategy aimed at minimizing redundancy and fortifying data integrity. Normalization is about dissecting your data, dis-tributing it across related tables, and ensuring each piece of information resides in its rightful place. Grasping normalization’s significance is paramount, as it directly influences your data-base’s structural optimization, enhancing maintainability and expediting query performance.

As you navigate through this data-centric realm, SQL emerges as your primary tool of communication with relational databases. Learning SQL means learning the lexicon of data-bases, where you can retrieve, insert, update, and delete data with precision and efficiency. A standardized language across relational databases, SQL is vital for effective data management and manipulation.

Lastly, your journey brings you to database objects—diverse elements within a database that you can create, modify, and interact with. Objects range from tables and views to indexes and stored procedures, each playing a unique and crucial role in how data is structured, stored, and accessed. Understanding these components is like solving a complex puzzle; recogniz-ing how each piece connects allows you to forge a complete, efficient, and effective database solution.

This skill covers how to:

• Identify features of relational data

• Describe normalization and why it is used

• Identify common structured query language (SQL) statements

• Identify common database objects

SQL statement types

SQL statements can be broadly classified into several types:

■■ Data Definition Language (DDL)

■■ Data Manipulation Language (DML)

■■ Data Control Language (DCL)

■■ Transaction Control Language (TCL)

Skill 2.1: Describe relational concepts     CHAPTER 2      35

DATA DEFINITION LANGUAGE

You’ll use DDL statements to create, modify, or delete the structure of database objects. The common statements are CREATE, ALTER, and DROP, as shown in Table 2-7.

TABLE 2-7  DDL statements

Figure 2-2 shows an example of the DDL CREATE statement that has been used to create a new table named Student with two columns: StudentID with the data type INT (an integer, or whole number) and FirstName with the data type VARCHAR (VARCHAR stands for variable-length character data).

FIGURE 2-2  DDL CREATE statement

Figure 2-3 shows an ALTER statement that has been used to alter the Students table by add-ing a new column named Email with a data type of VARCHAR (255).

FIGURE 2-3  DDL ALTER statement

Figure 2-4 shows the DROP statement used to delete the Student table from the database.

FIGURE 2-4  DDL DROP statement

36 CHAPTER 2     Identify considerations for relational data on Azure

Figure 2-5 shows the TRUNCATE statement used to delete all the records from the Students table while keeping the table structure for future use.

FIGURE 2-5 DDL TRUNCATE statement

Figure 2-6 shows the RENAME statement used to rename the Statement table to Learners.

FIGURE 2-6 DDL RENAME statement These DDL statements form the basis of structuring your data in a relational database. You’ll frequently use these commands as you build and manage your databases, so be sure to get comfortable with their syntax and uses. Practice is key!

Identify common database objects

Database objects are essential components that you will interact with while working with data-bases. These objects are utilized to store, manipulate, and retrieve the data housed within your database.

• Tables: At the heart of any database are tables. They are the structure that holds the data. A table consists of rows (or records) and columns (or fields). Each column represents a category of data, and each row contains the actual data values, as shown in Table 2-11.

TABLE 2-11 Table object

• Views: A view is a virtual table based on the resultset of a SQL statement. They allow you to present the data in a different perspective from that of the base table, and they can hide complexity or secure sensitive data. Figure 2-19 shows how you would create a view called TopStudent that shows only those students with a GPA greater than 3.5.

FIGURE 2-19 Simplifying data queries with a view to display top students

• Indexes: As you delve deeper into your interaction with databases, you’ll find that indexes are fundamental to optimizing performance and enhancing the speed of data retrieval.

An index, in the context of a database, is similar to an index in a book. In a book, an index allows you to find information quickly without having to read every page. Similarly, a database index provides a quick lookup pathway to the data in a table.

42 CHAPTER 2   Identify considerations for relational data on Azure

Creating an index involves the CREATE INDEX statement. When you create an index on a table column, the database management system (DBMS) generates a data structure that allows it to find the data associated with the indexed columns more swiftly.

Let’s look at an example. Say you frequently execute a query as shown in Figure 2-20 that searches students based on their last names in the Students table.

FIGURE 2-20  The search query

To speed up this query, you can create an index on the LastName column, as shown in Figure 2-21.

FIGURE 2-21  Enhancing query efficiency with an index on the LastName column in the Students table

Now, the DBMS will use the idx_lastname index to find Smith rapidly, which can lead to significantly faster query performance.

However, while indexes are powerful, they should be used judiciously. Creating an index is not a trivial task for the DBMS. It takes time and consumes storage space. Moreover, every time a table’s data changes (via an INSERT, UPDATE, or DELETE statement), the associated indexes also need to be updated. Thus, having too many indexes, especially on a table that frequently changes, can actually degrade the performance.

Therefore, a balanced approach is required when dealing with indexes. They should be cre-ated on columns that are frequently searched or used in the WHERE clause, JOIN operations, or sorting data (ORDER BY).

By implementing indexes in your database, you’re well on your way to creating efficient,

performance-tuned data retrieval options. This knowledge will greatly enhance your ability to effectively manage data in a relational database.

• Stored procedures: These are a group of SQL statements that form a logical unit and perform a particular task. Stored procedures can be used to encapsulate logic, enforce security, and increase performance.

In Figure 2-22 you could create a stored procedure to add a new student to the Students table.

Skill 2.1: Describe relational concepts     CHAPTER 2   43

FIGURE 2-22 Stored procedure

• Triggers: Triggers are special types of stored procedures that automatically execute when an event occurs in the database server. Triggers can help maintain the integrity of the data. You can create a trigger that automatically updates a LastModified field in the

Students table whenever a record is updated, as shown in Figure 2-23.

FIGURE 2-23 Trigger implementation to auto-update the LastModified timestamp in the Students table

By understanding and utilizing these objects, you can effectively manage your data and optimize your interactions with the database.

Comparing Azure SQL services

In the ever-evolving landscape of cloud services, Microsoft Azure presents a suite of SQL service offerings, each tailored to meet different operational and technological needs. From platform-as-a-service (PaaS) solutions designed for seamless management to infrastructure-as-a-service (IaaS) options that provide full control over your environment, Azure caters to a wide spectrum of data management scenarios. Table 2-12 compares the distinct characteristics, compatibilities, and use cases for each Azure SQL service offering, providing you with a clear overview to make informed decisions aligned with your business requirements.

46 CHAPTER 2 Identify considerations for relational data on Azure

TABLE 2-12  Comparative overview of Azure SQL services

This table serves as a guide to help you discern which Azure SQL service is best suited for your specific data management needs, whether you’re looking to modernize applications, manage big data, or extend SQL capabilities to the edge.

Azure SQL Database

Azure SQL Database is a fully managed relational database service provided by Microsoft Azure. It offers the broadest SQL Server engine compatibility and powers your cloud applica-tions with a variety of built-in features such as intelligent performance tuning, scalability, high availability, and advanced security capabilities.

Azure SQL Database operates as a database as a service (DBaaS), meaning you don’t have to worry about managing the underlying infrastructure. Instead, you can focus on optimizing your data and developing robust, data-driven applications.

USE CASES

Azure SQL Database, with its fully managed, scalable capabilities, is an exemplary choice for a range of contemporary application architectures. This managed database service is fine-tuned for the cloud environment, making it particularly well-suited for the following scenarios:

■■ Modern cloud applications: Azure SQL Database is an excellent choice for modern cloud applications because of its built-in intelligence and scalability features.

Skill 2.2: Describe relational Azure data services CHAPTER 2 47

• Microservices architectures: Its ability to independently scale out database makes it suitable for microservices architecture where each microservice can have its dedicated database.

• Multitenant applications: If you’re developing a SaaS application that needs to pro-vide a separate, isolated database for each customer (multitenancy), Azure SQL Data-base is a great choice because of its isolated database capabilities.

BUSINESS BENEFITS

Azure SQL Database stands out in the realm of cloud services, offering a suite of benefits that underscore its value proposition for businesses seeking efficient, scalable, and secure database solutions. Embracing Azure SQL Database can lead to a more streamlined operational model with a focus on innovation and growth:

• Lower total cost of ownership (TCO): With Azure SQL Database, you don’t need to purchase, set up, or manage any physical hardware, reducing your overall expenses. It also provides predictable billing and cost-efficiency with a pay-as-you-go model.

• Scalability: Azure SQL Database offers the ability to quickly scale resources up or down based on your needs. You can adjust compute and storage resources independently

in seconds, ensuring you always have the resources you need without overpaying for excess capacity.

• High availability and disaster recovery: Azure SQL Database comes with built-in high availability, automated backups, and geo-replication capabilities. It ensures that your data is available and protected at all times, reducing the risk of data loss.

• Advanced security: Azure SQL Database provides a high level of security with features such as automated updates, threat detection, data encryption, and compliance with various international and industry-specific compliance standards.

• Time savings: Because Azure SQL Database is a fully managed service, you don’t need to spend time on database administration tasks. Instead, you can focus on what truly matters: developing and optimizing your applications.