Category Azure Database for MariaDB

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.

KEY FEATURES

SQL Server on Azure Virtual Machines is replete with features that not only bolster your data’s resilience and security but also enhance performance and operational flexibility. The following are the key features that make SQL Server on Azure VMs a comprehensive solution for your database management needs:

■■ Automated backup: Azure streamlines the data protection process with automated backup solutions that you can tailor to your specific recovery point objectives, ensur-ing that both system and user-initiated backups are within easy reach for point-in-time restores.

■■ High availability and disaster recovery: Utilize Azure’s expansive infrastructure to architect high availability solutions, such as Always On availability groups, and tap into Azure Site Recovery for robust disaster recovery plans, keeping your systems reliable and resilient.

■■ Security enhancements: While Azure inherently secures your VMs, you retain the option to further bolster your defenses, implementing granular security measures at the SQL Server and OS levels to safeguard your data comprehensively.

■■ Performance optimization: Azure grants you the tools for advanced monitoring and automatic tuning, empowering you to refine the performance of your SQL Server VMs for peak efficiency tailored to your unique workload demands.

■■ Hybrid flexibility: Embrace the versatility of Azure’s hybrid capabilities to seamlessly meld your on-premises infrastructure with the cloud, creating a harmonious, unified platform for managing your databases across environments.

USE CASES

SQL Server on Azure Virtual Machines is not just a service but a versatile solution, adept at addressing a wide range of operational scenarios that demand specific SQL Server functional-ities and configurations. Here’s how this service can be instrumental across various use cases:

■■ Legacy system support: Azure VMs are a sanctuary for your older SQL Server versions that require a familiar environment, providing a cloud-based haven for your legacy systems and ensuring uninterrupted service without the need for immediate upgrades.

■■ Customized solutions: When your workloads demand unique SQL Server configura-tions or the integration of specialized features, such as third-party plugins or specific compatibility settings, Azure VMs stand ready to offer the exact level of control and customization you need.

54 CHAPTER 2 Identify considerations for relational data on Azure

■ Development and testing: Offering the agility to swiftly spin up or wind down environments, Azure VMs are perfectly suited for development and testing, providing an environment that closely mirrors your production settings for accurate testing and development.

Azure Database for MariaDB

Azure Database for MariaDB is a fully managed relational database service provided by Microsoft Azure based on the open-source MariaDB Server engine. It’s part of Azure’s suite of fully managed database services, designed to save you time and resources in managing your database and allowing you to focus on your application development. Azure Database for

58 CHAPTER 2 Identify considerations for relational data on Azure

MariaDB offers automated patching, backups, and monitoring, making your database mainte-nance tasks easier.
MariaDB is a popular open-source database, developed by the original creators of MySQL. It is designed to remain free and open-source software under the GNU GPL. It’s largely com-patible with MySQL, while offering more robust, scalable, and reliable SQL server capabilities. MariaDB is developed with a focus on performance and stability, and it includes a rich set of features such as advanced query optimization and a variety of storage engines.

■■ Fully managed service: Azure Database for MariaDB is a fully managed database ser-vice, meaning that Microsoft handles much of the heavy lifting associated with database management. This includes server maintenance, failover, and disaster recovery capa-bilities. This way, you can focus on designing and coding your applications rather than dealing with the intricacies of database administration.

■■ Security and compliance: Azure Database for MariaDB integrates with Azure’s advanced security and compliance services. It includes protection capabilities such as Advanced Threat Protection, which can detect anomalous activities indicating unusual and potentially harmful attempts to access or exploit your database. You can also man-age and monitor all activity within your database using Azure Monitor and Azure Log Analytics.

■■ Integration with Azure Services: This database service also integrates well with other Azure services, allowing you to build comprehensive, cloud-based applications. You can integrate your database with Azure App Service for creating web and mobile apps, with Azure Functions for serverless computing, or with Azure Logic Apps for building automated workflows.

Now let’s dive deeper inro the business benefits and use cases of Azure Database for MariaDB.

Describe features of analytical workloads

Analytical workloads play a pivotal role in extracting valuable insights and patterns from data to support informed decision-making and strategic planning within businesses. These work-loads involve complex data analysis, aggregations, and transformations to uncover meaningful information.

18 CHAPTER 1   Describe core data concept

Analytical workloads encompass a range of activities, such as data exploration, statistical analysis, data mining, and predictive modeling. The process begins by identifying relevant data sources and extracting the required data. The advanced analytics techniques, such as data visualization, machine learning, and statistical algorithms, are applied to gain insights and patterns from the data. The results are interpreted and translated into actionable business intelligence, which results in data-driven decision-making.

Analytical workloads serve different data personas within organizations.

• Data analysts: Data analysts leverage analytical workloads to explore and analyze data, uncovering trends, correlations, and patterns that provide valuable insights. They use statistical techniques and data visualization tools to communicate their findings effec-tively to stakeholders, resulting in evidence-based decision-making.

• Data scientists: Data scientists go beyond analyzing data and utilize advanced analyti-cal methods to develop predictive models, machine learning algorithms, and data-driven solutions. They leverage analytical workloads to build models that forecast future trends, identify opportunities, and optimize business processes.

• Business executives: Business executives rely on analytical workloads to gain high-level insight and make strategic decisions. They rely on reports, dashboards, and visual-izations generated by analytical workloads to monitor key performance indicators, track business metrics, and assess the effectiveness of strategies.

• Data engineers: Data engineers support analytical workloads by designing and implementing the data infrastructure necessary for data analysis. They ensure that data is ingested, processed, and made available in a format that facilitates efficient analysis.

They collaborate with data analysts and scientists to ensure data quality and reliability.

Analytical workloads are supported by various technologies and tools, including data plat-forms, machine learning frameworks, and business intelligence tools. These solutions provide capabilities for data exploration, modeling, visualization, and advanced analytics.

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

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

Identify features of relational data

Relational data refers to the type of data that can be organized into a table structure with related values connected to each other. This table structure consists of rows and columns, where each row represents a record, and each column represents a particular type of data called a file or attribute. Here are some of the key features of relational data:

• Tables: In a relational database, all data is stored in a table, which is structured in rows and columns. Each column represents a type of data (an attribute), and each row repre-sents an instance of that data (a record). For example, in a Student table, columns could

30 CHAPTER 2   Identify considerations for relational data on Azure

be StudentID, Name, and Major, while each row would represent an individual student’s data.

• Keys: Keys are a crucial feature of relational data. A primary key is a unique identifier for a row in the table. Foreign keys establish relationships between tables. For instance, a CourseID might be a foreign key in an Enrollments table, linking it to the Courses table.

• Normalization: This is the process of organizing data to minimize redundancy and avoid data anomalies, such as update errors. It involves decomposing a table into less redundant tables without losing information.

• Relationships: The relational model allows for relations among tables, typically through foreign keys. Relationships can be one-to-one, one-to-many, or many-to-many. For example, one student (in the Student table) can be enrolled in many courses (in the Courses table); this is a one-to-many relationship.

• Data integrity: Relational database enforces data integrity rules, ensuring the accuracy and consistency of data. These include entity integrity (no duplicate rows), referential integrity (consistency across relationships), and domain integrity (data types and value consistency).

• SQL: SQL is used for querying and manipulating the data stored in a relational database.

In Table 2-1, each row represents a student with a unique Student_id, their name is Student_ Name, and the ID of the course in which they’re enrolled is Course_id. The Course_id in Table 2-1 is a foreign key that connects Table 2-1 to Table 2-2.

In Table 2-2, each row represents a course with a unique Course_id and its name is Course_ Name. The Course_id in this table is the primary key.

TABLE 2-1  Student table

TABLE 2-2  Course table

Skill 2.1: Describe relational concepts     CHAPTER 2     31

In the context of Azure, there are several services that are built on the relational model, such as Azure SQL Database and SQL Server on Azure VMs. These services allow you to work with relational data using SQL for managing and manipulating the relational data.

The relational concept is at the heart of most traditional database systems, and understand-ing it is key to becoming proficient in any database-related work, especially when working with Azure’s data services.

DATA MANIPULATION LANGUAGE

You’ll use DML statements to insert, update, and delete data in a database. The common state-ments include INSERT, UPDATE, and DELETE, as shown in Table 2-8.

TABLE 2-8 DML statements

Skill 2.1: Describe relational concepts       CHAPTER 2    37

Figure 2-7 shows an example of a DML INSERT statement used to add a new record into the Students table. The new student has a StudentID of 1, a FirstName of John, and a LastName of Doe.

FIGURE 2-7  DML INSERT statement

Figure 2-8 shows the UPDATE statement used to update the Students table by changing the FirstName to Jane for the student with a StudentID of 1.

FIGURE 2-8  DML UPDATE statement

Figure 2-9 shows the DELETE statement used to remove records from the Students table where the StudentID is 1.

FIGURE 2-9  DML DELETE statement

Although technically part of the Data Query Language, SELECT is often considered part of DML as it is fundamental to manipulating data. The SELECT statement is used to fetch data from a database.

Figure 2-10 shows the SELECT statement fetching all the data from the Students table for the record where StudentID is 1.

FIGURE 2-10  DML SELECT statement

Figure 2-11 shows how to merge data from UpdatedStudents (based on StudentID) and insert new records from UpdatedStudents into Students.

38 CHAPTER 2 Identify considerations for relational data on Azure

FIGURE 2-11  DML MERGE statement

Mastering the DML statements is crucial for effective database operations and data management. Make sure to practice these commands with different tables and databases to thoroughly understand how they work.

DATA CONTROL LANGUAGE

You will use DCL to create roles, permissions, and referential integrity; it is also used to con-trol access to databases by securing them. The common statements are GRANT, REVOKE, and DENY, as shown in Table 2-9.

TABLE 2-9  DCL statements

Figure 2-12 shows how to grant User1 permission to execute SELECT and INSERT operations on the Students table.

FIGURE 2-12  DCL GRANT statement

Skill 2.1: Describe relational concepts     CHAPTER 2   39

Figure 2-13 shows how to revoke User1’s permission to perform INSERT operations on the Students table.

FIGURE 2-13  DCL REVOKE statement

Figure 2-14 shows how to deny User1 the permission to perform DELETE operations on the Students table. Even if another rule grants User1 the DELETE permission, this DENY rule will override it.

FIGURE 2-14  DCL DENY statement

Understanding these DCL statements is important for managing access control and ensur-ing data security in your database. Practice using these statements to gain a clear under-standing of their impact on database operations. Make sure to always carefully consider

the implications of granting, revoking, or denying permissions to maintain the integrity and security of your data.

TRANSACTION CONTROL LANGUAGE

TCL commands are used to manage transactions in the database. They include COMMIT, ROLL-BACK, and SAVEPOINT, as shown in Table 2-10.

TABLE 2-10  TCL statements

Figure 2-15 shows how you initialize a new transaction. Any SQL statements following this will be part of this transaction.

40 CHAPTER 2 Identify considerations for relational data on Azure

FIGURE 2-15  TCL BEGIN TRANSACTION statement

Figure 2-16 shows how to start a transaction, make an update to the Students table, and then commit the transaction. This means the update is saved and cannot be rolled back.

FIGURE 2-16  TCL COMMIT statement

Figure 2-17 shows a transaction started that is making an update to the Students table. However, you can decide to roll back the transaction, meaning the update to the Students table will not be saved and the data remains as it was before the transaction began.

FIGURE 2-17  DCL ROLLBACK statement

Figure 2-18 shows a transaction started that is making an update to the Students table, cre-ating a savepoint, and making another update. Then, you decide to roll back the transaction to the savepoint. The first update will be saved, but the second update will not.

FIGURE 2-18  TCL SAVEPOINT statement

These TCL commands are crucial for maintaining the integrity of your data, especially in situations where you need to perform multiple related operations as a single atomic unit. If any of these operations fails, the TCL commands allow you to roll back the entire transaction, preventing your data from ending up in an inconsistent state.
Skill 2.1: Describe relational concepts     CHAPTER 241

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.