DP 300Practice 2

You need to recommend a disaster recovery solution for an on-premises Microsoft SQL Server database. The solution must meet the following requirements:

* Support real-time data replication to a different geographic region.

* Use Azure as a disaster recovery target.

* Minimize costs and administrative effort.

What should you include in the recommendation?

• database mirroring on an instance of SQL Server on Azure Virtual Machines

• availability groups for SQL Server on Azure Virtual Machines

• an Azure SQL Managed Instance link

• transactional replication to an Azure SQL Managed Instance

You have an Azure subscription.

You need to deploy an Azure SQL database. The solution must meet the following requirements:

* Dynamically scale CPU resources.

* Ensure that the database can be paused to reduce costs.

What should you use?

• the Business Critical service tier

• the serverless compute tier

• an elastic pool

• the General Purpose service tier

True or False: Hyperscale tier within Azure SQL Database architecture includes a multilayer caching system that can help with both speed and scale. The use of remote storage also allows for snapshot backups.

Once you move a database to Hyperscale tier, you can't "go back" to the General Purpose or Business Critical tier.

• True

• False

Understanding recovery time and recovery point objectives are crucial to your high availability and disaster recovery (HADR) plan as they are the foundation for any availability solution.

_____ is the point in time to which a database should be recovered and equates to the maximum amount of data loss that the business is willing to accept.

• RBS

• RPO

• RTO

• RBC

Answer explanation

Recovery Point Objective (RPO) is the point in time to which a database should be recovered and equates to the maximum amount of data loss that the business is willing to accept. For example, suppose an IaaS VM containing SQL Server experiences an outage at 10:00 AM and the databases within the SQL Server instance have an RPO of 15 minutes. No matter what feature or technology is used to bring back that instance and its databases, the expectation is that there will be at most 15 minutes worth of data lost. That means the database can be restored to 9:45 AM or later to ensure minimal to no data loss meeting that stated RPO. There may be factors that determine if that RPO is achievable

You have a data warehouse in Azure Synapse Analytics.
You need to ensure that the data in the data warehouse is encrypted at rest.
What should you enable?

• Transparent Data Encryption (TDE)

• Advanced Data Security for this database

• Always Encrypted for all columns

• Secure transfer required

Answer explanation

Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview

You are designing a security model for an Azure Synapse Analytics dedicated SQL pool that will support multiple companies.
You need to ensure that users from each company can view only the data of their respective company.
Which two objects should you include in the solution? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

• a column encryption key

• asymmetric keys

• a function

• a custom role-based access control (RBAC) role

• a security policy

You have an Azure subscription that contains an Azure Data Factory version 2 (V2) data factory named df1. DF1 contains a linked service.
You have an Azure Key vault named vault1 that contains an encryption kay named key1.
You need to encrypt df1 by using key1.
What should you do first?

• Disable purge protection on vault1.

• Remove the linked service from df1.

• Create a self-hosted integration runtime.

• • Disable soft delete on vault1.

Answer explanation

A customer-managed key can only be configured on an empty data Factory. The data factory can't contain any resources such as linked services, pipelines and data flows. It is recommended to enable customer-managed key right after factory creation.

You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:
✑ Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1.
✑ Restrict network connectivity to SqlSrv1.
What should you create on VNet1?

• a VPN gateway

• a service endpoint

• a private endpoint

• an ExpressRoute gateway

Answer explanation

A private endpoint is a network interface that uses a private IP address from your virtual network. This network interface connects you privately and securely to a service powered by Azure Private Link. By enabling a private endpoint, you're bringing the service into your virtual network.
The service could be an Azure service such as:
✑ Azure Storage
✑ Azure Cosmos DB
✑ Azure SQL Database
✑ Your own service using a Private Link Service.
Reference:
https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview

You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.
You need to encrypt the data in Col1.
Which four actions should you perform for DB1 in sequence?

Choose the option with the correct order

• 1. 1. Update Col1.

  2. 2. Create a Certificate.

  3. 3. Create a symmetric key.

  4. 4. Create a column master key

• 1. 1. Create a database master key.

  2. 2. Create a certificate.

  3. 3. Create a symmetric key

  4. 4. Open the symmetric key

• 1. 1. Update Col1.

  2. 2. Create a database.

  3. 3. Create a symmetric key.

  4. 4. Open a symmetric key

• 1. 1. Create a database.

  2. 2. Update Col1.

  3. 3. Create a symmetric key.

  4. 4. Create a column master key

Answer explanation

Use the following steps for column level encryption:
1. Create a database master key (Step 1)
2. Create a self-signed certificate for SQL Server (Step 2)
3. Configure a symmetric key for encryption (Step 3)
4. Encrypt the column data (this includes Open the symmetric key - Step 4)
5. Query and verify the encryption

Reference:
https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

You have an Azure subscription that contains a logical SQL server named Server1. The master database of Server1 contains a user named User1.
You need to ensure that User1 can create databases on Server1.
Which database role should you assign to User1?

• db_owner

• dbmanager

• dbo

• db_ddladmin

Answer explanation

dbmanager: Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles