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.

EXAMPLE SCENARIO

Let’s say you’re managing a data warehousing solution built on SQL Server 2016 with signifi-cant reliance on SQL Server Analysis Services (SSAS). Here’s how you could implement this on Azure VMs:

1. Select and provision an appropriate VM size from the Azure portal, considering the compute, memory, and I/O requirements of your SSAS workloads.
2. Attach premium solid-state drive (SSD) storage to ensure quick data processing and set up automated backups with geo-redundancy.
3. Configure Azure VNet for secure access and establish a virtual private network (VPN) connection to your on-premises network.
4. Install SQL Server 2016 and configure SSAS, mirroring your on-premises setup.

• Optimize the VM for performance, adjusting settings based on the monitoring insights provided by Azure.

SQL Server on Azure VMs is the quintessential choice for data professionals who require the comprehensive capabilities of SQL Server combined with the scalability, security, and reliabil-ity of Azure’s infrastructure. Whether your workload is heavy on data processing or you need specific SQL Server features, Azure VMs provide a robust environment to meet and adapt to your evolving business needs.

Identify Azure database services for open-source database systems

Now let’s expand your knowledge to encompass Azure’s offerings in the realm of open-source databases. You’ll delve into the various services Azure provides to accommodate the vast ecosystem of open-source database systems such as MySQL, PostgreSQL, and Maria DB. These databases have been widely adopted in industries and enterprises worldwide for their robust-ness, flexibility, and compatibility with various data models and languages. Azure’s managed services for these databases provide a scalable, secure, and fully managed environment that retains all the benefits of open-source solutions while freeing you from the administrative and maintenance tasks. By the end of this section, you’ll be well-versed in identifying and describ-ing the key features, advantages, and use cases of Azure’s database services for these open-source systems.

Skill 2.2: Describe relational Azure data services    CHAPTER 2       55

What are MySQL, MariaDB, and PostgreSQL?

My SQL, MariaDB, and PostgreSQL are all popular open-source relational database manage-ment systems (RDBMSs). Each has unique features and benefits that make them suitable for different applications and use cases.

■■ MySQL: MySQL is a widely used open-source RDBMS. It is known for its speed, reli-ability, and ease of use. MySQL is often used in web applications and online publishing and is a central component of the popular LAMP open-source web application software stack (Linux, Apache, MySQL, PHP/Python/Perl).

■■ MariaDB: MariaDB is a fork of MySQL created by the original developers of MySQL, and it’s intended to remain open-source. MariaDB is designed to be highly compatible with MySQL, meaning that, in most cases, data and code can be switched seamlessly between the two. MariaDB includes more storage engines than MySQL, and it includes several features not found in MySQL.

■■ PostgreSQL: PostgreSQL is a powerful, open-source object-relational database sys-tem. It supports both SQL (relational) and JSON (non-relational) querying. It’s highly extensible and can be customized via functions and stored procedures. PostgreSQL is renowned for its performance, advanced features, and standards compliance.

While all three systems can manage relational data, they each have unique advantages. MySQL is known for its speed and efficiency, MariaDB offers rich features and compatibility with MySQL, and PostgreSQL is known for its standards compliance and versatility. All three are supported by Azure, allowing you to leverage these benefits in a fully managed cloud environment.

Azure Database for MySQL

Azure Database for MySQL is a relational database service in the Microsoft cloud based on MySQL Community Edition (available version 5.6/5.7/8.0). As a fully managed service, it allows developers to focus more on developing applications and less on managing infrastructure.

■■ Fully managed: This service is fully managed by Microsoft. What does that mean for you? It means that common maintenance operations such as patching, backups, and handling failovers are automated. These features help save time and reduce the com-plexity of administrative tasks, freeing you to focus on what matters: developing your applications and improving functionality.

■■ Performance and scalability: Performance is a crucial aspect of any application. With Azure Database for MySQL, you have various options to match your performance requirements. Azure offers a range of compute sizes, so you can select the right amount of compute power you need. It allows you to independently scale compute and storage. This means you can adjust the compute power and storage space to match the demand of your application. This independent scaling is especially helpful as it provides flexibility and helps to manage costs.

56 CHAPTER 2 Identify considerations for relational data on Azure

■■ Security and compliance: Security and compliance are top priorities for any business. Azure Database for MySQL integrates well with Azure’s advanced security and compli-ance services. It supports Azure Active Directory authentication so that identities can be managed in one central location. It also provides encryption at rest and in transit, adding a layer of protection to your data.

In addition, Azure offers a comprehensive set of compliance offerings. Whether you are dealing with general data protection regulations or industry-specific ones, Azure has you covered.

■■ Developer productivity: Azure Database for MySQL integrates seamlessly with popu-lar open-source applications and Azure services. This makes it a natural choice if you are developing apps with Azure App Service, building microservices with Azure Kubernetes Services, or running analytics with Power BI.

■■ Data protection: Data is the heart of your business. To protect it, Azure Database for MySQL offers automatic backups, which are stored in geo-redundant storage. This allows you to restore your database to any point within the backup retention period. Additionally, the service provides the option to increase your retention period and to use long-term backup retention.

■■ Global availability and industry-leading service-level agreements (SLAs): With Azure’s global footprint, you can run your applications closer to your customers. And with industry-leading SLAs, you can have peace of mind knowing your app will run smoothly and reliably.

In the following section, we will delve deeper into the business benefits and use cases of Azure Database for MySQL.

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.

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

Describe responsibilities for database administrators

As a database administrator (DBA), your role is crucial in the management and maintenance of databases, ensuring their smooth operation, integrity, and performance. You are the guard-ian of data within your organization, responsible for various tasks that contribute to efficiently storing, retrieving, and securing data.

You are involved in the entire life cycle of databases, starting from the initial design and creation to ongoing maintenance and optimization. You work closely with stakeholders to understand data requirements and design database structures that optimize performance and scalability. You determine data models, create database schemas, and define relationships between tables.

Ensuring data security is a critical aspect of your role. You implement access controls, user authentication, and encryption mechanisms to protect sensitive data from unauthorized access or malicious activities. You establish backup and recovery procedures to safeguard against data loss, ensuring the continuity of business operations.

20 CHAPTER 1   Describe core data concept

Monitoring databases and optimizing performance are essential responsibilities. You constantly monitor database performance, identifying and resolving bottlenecks to enhance system efficiency. You analyze query performance, tune database configurations, and optimize indexing strategies to improve overall performance and ensure timely data retrieval.

Your expertise also extends to backup and recovery. You design and implement robust backup and recovery strategies to protect data from system failure, human errors, or disasters. You schedule regular backups, perform restoration tests, and maintain disaster recovery plans to ensure data availability and minimize downtime.

Keeping databases up to date is another aspect of your role. You oversee database upgrades and apply patches, ensuring that the database systems are equipped with the latest features, bug fixes, and security updates. You perform compatibility tests and ensure seamless transitions to new versions or releases.

Your role as a DBA is instrumental in maintaining data integrity, ensuring system availabil-ity, and supporting business continuity. Your expertise ensures that databases operate effi-ciently, adhere to industry standards, and meet regulatory requirements. With your skills and knowledge, you contribute to the data-driven systems within your organization functioning smoothly.

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.