Microsoft Administering Relational Databases on Microsoft Azure - DP-300 Exam Practice Test
Hotspot Question
You have an Azure subscription that contains an Always On availability group named AG1. AG1 contains three replicas named Server1, Server2, and Server3.
You execute the following T-SQL statements on Server1.
For each of the following statements, select Yes if the statement is true, otherwise select No.
NOTE: Each correct selection is worth one point.
You have an Azure subscription that contains an Always On availability group named AG1. AG1 contains three replicas named Server1, Server2, and Server3.
You execute the following T-SQL statements on Server1.
For each of the following statements, select Yes if the statement is true, otherwise select No.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: Yes
Note: sys.dm_hadr_availability_group_states (Transact-SQL)
Returns a row for each Always On availability group that possesses an availability replica on the local instance of SQL Server. Each row displays the states that define the health of a given availability group.
Box 2: Yes
We see: WHERE primary_replica != @@Servername
The @@Servername variable would be Server1 as the T-SQL statements are run on Server1.
Note: AlwaysOn Availability Groups provide high availability for groups of database by adding secondary replicas. These replicas allow failing over databases in case of a failure. In addition they can be used to offload read workloads or backup tasks.
Box 3: No
Hotspot Question
You have SQL Server on an Azure virtual machine that contains a database named DB1.
The database reports a CHECKSUM error.
You need to recover the database.
How should you complete the statements? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
You have SQL Server on an Azure virtual machine that contains a database named DB1.
The database reports a CHECKSUM error.
You need to recover the database.
How should you complete the statements? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: SINGLE_USER
The specified database must be in single-user mode to use one of the following repair options.
Box 2: REPAIR_ALLOW_DATA_LOSS
REPAIR_ALLOW_DATA_LOSS tries to repair all reported errors. These repairs can cause some data loss.
Note: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup.
Incorrect Answers:
REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.
Box 3: MULTI_USER
All users that have the appropriate permissions to connect to the database are allowed.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
You have a version-8.0 Azure Database for MySQL database.
You need to identify which database queries consume the most resources.
Which tool should you use?
You need to identify which database queries consume the most resources.
Which tool should you use?
Correct Answer: A
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
Your on-premises network contains a server that hosts a 60-TB database named DB1. The network has a 10-Mbps internet connection.
You need to migrate DB1 to Azure. The solution must minimize how long it takes to migrate the database.
What should you use?
You need to migrate DB1 to Azure. The solution must minimize how long it takes to migrate the database.
What should you use?
Correct Answer: A
Vote an answer
Drag and Drop Question
You create an Azure SQL managed instance and a job that performs backups.
You need to configure the job to notify a distribution group by email when the job fails. The solution must minimize administrative effort.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
You create an Azure SQL managed instance and a job that performs backups.
You need to configure the job to notify a distribution group by email when the job fails. The solution must minimize administrative effort.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Correct Answer:
Explanation:
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance?view=azuresql
Case Study 1 - Litware, Inc
Overview
Litware, Inc. is a renewable energy company that has a main office in Boston. The main office hosts a sales department and the primary datacenter for the company.
Physical Locations
Existing Environment
Litware has a manufacturing office and a research office is separate locations near Boston. Each office has its own datacenter and internet connection.
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
Network Environment
The primary datacenter has an ExpressRoute connection that uses both Microsoft peering and private peering. The private peering connects to an Azure virtual network named HubVNet.
Identity Environment
Litware has a hybrid Azure Active Directory (Azure AD) deployment that uses a domain named litwareinc.com. All Azure subscriptions are associated to the litwareinc.com Azure AD tenant.
Database Environment
The sales department has the following database workload:
* An on-premises named SERVER1 hosts an instance of Microsoft SQL Server 2012 and two 1- TB databases.
* A logical server named SalesSrv01A contains a geo-replicated Azure SQL database named SalesSQLDb1. SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool. SalesSQLDb1 uses database firewall rules and contained database users.
* An application named SalesSQLDb1App1 uses SalesSQLDb1.
The manufacturing office contains two on-premises SQL Server 2016 servers named SERVER2 and SERVER3. The servers are nodes in the same Always On availability group. The availability group contains a database named ManufacturingSQLDb1 Database administrators have two Azure virtual machines in HubVnet named VM1 and VM2 that run Windows Server 2019 and are used to manage all the Azure databases.
Licensing Agreement
Litware is a Microsoft Volume Licensing customer that has License Mobility through Software Assurance.
Current Problems
SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
Requirements
Planned Changes
Litware plans to implement the following changes:
* Implement 30 new databases in Azure, which will be used by time-sensitive manufacturing apps that have varying usage patterns. Each database will be approximately 20 GB.
* Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01. ResearchDB1 will contain Personally Identifiable Information (PII) data.
* Develop an app named ResearchApp1 that will be used by the research department to populate and access ResearchDB1.
* Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.
* Migrate the SERVER1 databases to the Azure SQL Database platform.
Technical Requirements
Litware identifies the following technical requirements:
* Maintenance tasks must be automated.
* The 30 new databases must scale automatically.
* The use of an on-premises infrastructure must be minimized.
* Azure Hybrid Use Benefits must be leveraged for Azure SQL Database deployments.
* All SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.
Security and Compliance Requirements
Litware identifies the following security and compliance requirements:
* Store encryption keys in Azure Key Vault.
* Retain backups of the PII data for two months.
* Encrypt the PII data at rest, in transit, and in use.
* Use the principle of least privilege whenever possible.
* Authenticate database users by using Active Directory credentials.
* Protect Azure SQL Database instances by using database-level firewall rules.
* Ensure that all databases hosted in Azure are accessible from VM1 and VM2 without relying on public endpoints.
Business Requirements
Litware identifies the following business requirements:
* Meet an SLA of 99.99% availability for all Azure deployments.
* Minimize downtime during the migration of the SERVER1 databases.
* Use the Azure Hybrid Use Benefits when migrating workloads to Azure.
* Once all requirements are met, minimize costs whenever possible.
Drag and Drop Question
You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Overview
Litware, Inc. is a renewable energy company that has a main office in Boston. The main office hosts a sales department and the primary datacenter for the company.
Physical Locations
Existing Environment
Litware has a manufacturing office and a research office is separate locations near Boston. Each office has its own datacenter and internet connection.
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
Network Environment
The primary datacenter has an ExpressRoute connection that uses both Microsoft peering and private peering. The private peering connects to an Azure virtual network named HubVNet.
Identity Environment
Litware has a hybrid Azure Active Directory (Azure AD) deployment that uses a domain named litwareinc.com. All Azure subscriptions are associated to the litwareinc.com Azure AD tenant.
Database Environment
The sales department has the following database workload:
* An on-premises named SERVER1 hosts an instance of Microsoft SQL Server 2012 and two 1- TB databases.
* A logical server named SalesSrv01A contains a geo-replicated Azure SQL database named SalesSQLDb1. SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool. SalesSQLDb1 uses database firewall rules and contained database users.
* An application named SalesSQLDb1App1 uses SalesSQLDb1.
The manufacturing office contains two on-premises SQL Server 2016 servers named SERVER2 and SERVER3. The servers are nodes in the same Always On availability group. The availability group contains a database named ManufacturingSQLDb1 Database administrators have two Azure virtual machines in HubVnet named VM1 and VM2 that run Windows Server 2019 and are used to manage all the Azure databases.
Licensing Agreement
Litware is a Microsoft Volume Licensing customer that has License Mobility through Software Assurance.
Current Problems
SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
Requirements
Planned Changes
Litware plans to implement the following changes:
* Implement 30 new databases in Azure, which will be used by time-sensitive manufacturing apps that have varying usage patterns. Each database will be approximately 20 GB.
* Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01. ResearchDB1 will contain Personally Identifiable Information (PII) data.
* Develop an app named ResearchApp1 that will be used by the research department to populate and access ResearchDB1.
* Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.
* Migrate the SERVER1 databases to the Azure SQL Database platform.
Technical Requirements
Litware identifies the following technical requirements:
* Maintenance tasks must be automated.
* The 30 new databases must scale automatically.
* The use of an on-premises infrastructure must be minimized.
* Azure Hybrid Use Benefits must be leveraged for Azure SQL Database deployments.
* All SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.
Security and Compliance Requirements
Litware identifies the following security and compliance requirements:
* Store encryption keys in Azure Key Vault.
* Retain backups of the PII data for two months.
* Encrypt the PII data at rest, in transit, and in use.
* Use the principle of least privilege whenever possible.
* Authenticate database users by using Active Directory credentials.
* Protect Azure SQL Database instances by using database-level firewall rules.
* Ensure that all databases hosted in Azure are accessible from VM1 and VM2 without relying on public endpoints.
Business Requirements
Litware identifies the following business requirements:
* Meet an SLA of 99.99% availability for all Azure deployments.
* Minimize downtime during the migration of the SERVER1 databases.
* Use the Azure Hybrid Use Benefits when migrating workloads to Azure.
* Once all requirements are met, minimize costs whenever possible.
Drag and Drop Question
You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Explanation:
Automating Azure SQL DB index and statistics maintenance using Azure Automation:
1. Create Azure automation account (Step 1)
2. Import SQLServer module (Step 2)
3. Add Credentials to access SQL DB
This will use secure way to hold login name and password that will be used to access Azure SQL DB
4. Add a runbook to run the maintenance (Step 3)
Steps:
1. Click on "runbooks" at the left panel and then click "add a runbook"
2. Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create"
5. Schedule task (Step 4)
Steps:
1. Click on Schedules
2. Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule.
Reference:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/automating-azure-sql-db-index-and-statistics-maintenance-using/ba-p/368974
You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.
You need to create a surrogate key for the table. The solution must provide the fastest query performance.
What should you use for the surrogate key?
You need to create a surrogate key for the table. The solution must provide the fastest query performance.
What should you use for the surrogate key?
Correct Answer: A
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
SIMULATION
You need to add an Azure AD user named [email protected] to db1. User2-
12345678 must be able to read data from all the tables in db1 without being able to modify the data.
To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.
You need to add an Azure AD user named [email protected] to db1. User2-
12345678 must be able to read data from all the tables in db1 without being able to modify the data.
To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.
Correct Answer:
Stage 1: Create the user
Create a user with SSMS (SQL Server Management Studio)
Step 1: In Object Explorer, expand the Databases folder.
Step 2: Expand the database in which to create the new database user. [Here db1] Step 3: Right-click the Security folder, point to New, and select User....
Step 4: In the Database User - New dialog box, on the General page, select one of the following user types from the User type list:
SQL user with login
SQL user with password (when contained database is enabled)
SQL user without login
User mapped to a certificate
User mapped to an asymmetric key
*-> Windows user
Step 4a: Select Windows User
Step 5: When you select an option, the remaining options in the dialog may change. Some options only apply to specific types of database users. Some options can be left blank and will use a default value.
User name
Enter a name for the new user. If you have chosen Windows user from the User type list, you can also select the ellipsis (...) to open the Select User or Group dialog box. [Specify User2-
12345678]
Step 6: Select OK.
Stage 2: Assign permissions
Additional Options
The Database User - New dialog box also offers options on four other pages: Owned Schemas, Membership, Securables, and Extended Properties.
Step 7: Select Securables
The Securables page lists all possible securables and the permissions on those securables that can be granted to the login.
Step 8: Select tables
Step 9: Grant Read Permission
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user
Create a user with SSMS (SQL Server Management Studio)
Step 1: In Object Explorer, expand the Databases folder.
Step 2: Expand the database in which to create the new database user. [Here db1] Step 3: Right-click the Security folder, point to New, and select User....
Step 4: In the Database User - New dialog box, on the General page, select one of the following user types from the User type list:
SQL user with login
SQL user with password (when contained database is enabled)
SQL user without login
User mapped to a certificate
User mapped to an asymmetric key
*-> Windows user
Step 4a: Select Windows User
Step 5: When you select an option, the remaining options in the dialog may change. Some options only apply to specific types of database users. Some options can be left blank and will use a default value.
User name
Enter a name for the new user. If you have chosen Windows user from the User type list, you can also select the ellipsis (...) to open the Select User or Group dialog box. [Specify User2-
12345678]
Step 6: Select OK.
Stage 2: Assign permissions
Additional Options
The Database User - New dialog box also offers options on four other pages: Owned Schemas, Membership, Securables, and Extended Properties.
Step 7: Select Securables
The Securables page lists all possible securables and the permissions on those securables that can be granted to the login.
Step 8: Select tables
Step 9: Grant Read Permission
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user