You plan to use an Azure SQL data warehouse to store the customer data. You need to recommend a disaster recovery solution for the data warehouse.

What should you include in the recommendation?

Reveal Solution Hide Solution

DRAG DROP

You need to design the encryption strategy for the tagging data and customer data.

What should you recommend? To answer, drag the appropriate setting to the correct drop targets. Each source may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

All cloud data must be encrypted at rest and in transit.

Box 1: Transparent data encryption

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.

Box 2: Encryption at rest

Encryption at Rest is the encoding (encryption) of data when it is persisted.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

https://docs.microsoft.com/en-us/azure/security/azure-security-encryption-atrest

You need to recommend a solution for storing the image tagging data.

What should you recommend?

Reveal Solution Hide Solution

Topic 3, Case study 2

Case study

This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

To start the case study

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

Background

Current environment

The company has the following virtual machines (VMs):

Requirements

Storage and processing

You must be able to use a file system view of data stored in a blob.

You must build an architecture that will allow Contoso to use the DB FS filesystem layer over a blob store. The architecture will need to support data files, libraries, and images. Additionally, it must provide a web-based interface to documents that contain runnable command, visualizations, and narrative text such as a notebook.

CONT_SQL3 requires an initial scale of 35000 IOPS.

CONT_SQL1 and CONT_SQL2 must use the vCore model and should include replicas. The solution must support 8000 IOPS.

The storage should be configured to optimized storage for database OLTP workloads.

Migration

– You must be able to independently scale compute and storage resources.

– You must migrate all SQL Server workloads to Azure. You must identify related machines in the on-premises environment, get disk size data usage information.

– Data from SQL Server must include zone redundant storage.

– You need to ensure that app components can reside on-premises while interacting with components that run in the Azure public cloud.

– SAP data must remain on-premises.

– The Azure Site Recovery (ASR) results should contain per-machine data.

Business requirements

– You must design a regional disaster recovery topology.

– The database backups have regulatory purposes and must be retained for seven years.

– CONT_SQL1 stores customers sales data that requires ETL operations for data analysis. A solution is required that reads data from SQL, performs ETL, and outputs to Power BI. The solution should use managed clusters to minimize costs. To optimize logistics, Contoso needs to analyze customer sales data to see if certain products are tied to specific times in the year.

– The analytics solution for customer sales data must be available during a regional outage.

Security and auditing

– Contoso requires all corporate computers to enable Windows Firewall.

– Azure servers should be able to ping other Contoso Azure servers.

– Employee PII must be encrypted in memory, in motion, and at rest. Any data encrypted by SQL Server must support equality searches, grouping, indexing, and joining on the encrypted data.

– Keys must be secured by using hardware security modules (HSMs).

– CONT_SQL3 must not communicate over the default ports

Cost

– All solutions must minimize cost and resources.

– The organization does not want any unexpected charges.

– The data engineers must set the SQL Data Warehouse compute resources to consume 300 DWUs.

– CONT_SQL2 is not fully utilized during non-peak hours. You must minimize resource costs for during non-peak hours.

You plan to use Azure SQL Database to support a line of business app.

You need to identify sensitive data that is stored in the database and monitor access to the data.

Which three actions should you recommend? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

You need to design a solution to meet the SQL Server storage requirements for CONT_SQL3.

Which type of disk should you recommend?

Reveal Solution Hide Solution

DRAG DROP

You are designing an Azure SQL Data Warehouse for a financial services company. Azure Active Directory will be used to authenticate the users.

You need to ensure that the following security requirements are met:

✑ Department managers must be able to create new database.

✑ The IT department must assign users to databases.

✑ Permissions granted must be minimized.

Which role memberships should you recommend? To answer, drag the appropriate roles to the correct groups. Each role may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: dbmanager

Members of the dbmanager role can create new databases.

Box 2: db_accessadmin

Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

You need to recommend the appropriate storage and processing solution?

What should you recommend?

Reveal Solution Hide Solution

You need to optimize storage for CONT_SQL3.

What should you recommend?

Reveal Solution Hide Solution

A company stores sensitive information about customers and employees in Azure SQL Database.

You need to ensure that the sensitive data remains encrypted in transit and at rest.

What should you recommend?

Reveal Solution Hide Solution

You need to recommend a backup strategy for CONT_SQL1 and CONT_SQL2.

What should you recommend?

Reveal Solution Hide Solution