Provisioning SQL Databases v1.0
Question 1
You create a new Microsoft Azure subscription.
You need to create a group of Azure SQL databases that share resources.
Which cmdlet should you run first?
- A. New-AzureRmAvailabilitySet
- B. New-AzureRmLoadBalancer
- C. New-AzureRmSqlDatabaseSecondary
- D. New-AzureRmSqlElasticPool
- E. New-AzureRmVM
- F. New-AzureRmSqlServer
- G. New-AzureRmSqlDatabaseCopy
- H. New-AzureRmSqlServerCommunicationLink
Answer : D
Explanation:
SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources (elastic Database Transaction Units
(eDTUs)) at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool
Question 2
You have a Microsoft Azure SQL database in the US West region.
You need to create a replica in the US East region.
Which cmdlet should you run first?
- A. New-AzureRmAvailabilitySet
- B. New-AzureRmLoadBalancer
- C. New-AzureRmSqlDatabaseSecondary
- D. New-AzureRmSqlElasticPool
- E. New-AzureRmVM
- F. New-AzureRmSqlServer
- G. New-AzureRmSqlDatabaseCopy
- H. New-AzureRmSqlServerCommunicationLink
Answer : G
Explanation:
The New-AzureRmSqlDatabaseCopy command creates a copy of a SQL Database that uses the snapshot at the current time.
References:
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/new-azurermsqldatabasecopy?view=azurermps-5.1.1
Question 3
You plan to deploy Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine will have a 30-TB database and will have 30 1-TB VHDs for the database.
You need to configure the storage to meet the following requirements:
-> Evenly distribute read and write operations across the VHDs.
Minimize the read and write time.
Which storage configuration should you use?
- A. a parity storage pool
- B. a simple storage pool
- C. a mirrored storage pool
- D. a striped volume
- E. a RAID-5 volume
Answer : D
Explanation:
Data that is written to a striped volume is interleaved to all disks at the same time instead of sequentially. Therefore, disk performance is the fastest on a RAID 0 volume as compared to any other type of disk configuration.
Reference:
https://support.microsoft.com/en-us/help/323433/how-to-establish-a-striped-volume-raid-0-in-windows-server-2003 https://github.com/uglide/azure-content/blob/master/articles/virtual-machines/virtual-machines-windows-migrate-sql.md
Question 4
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space. The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has 30 containers. You create a VHD in each container.
Does this meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References:
https://whttps://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/
Question 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space. The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create 30 storage accounts that each has one container. You create a VHD in each container.
Does this meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References:
https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/
Question 6
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space. The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has one container. You create multiple VHDs in the container.
Does this meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References:
https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/
Question 7
You plan to deploy an on-premises SQL Server 2014 database to Azure SQL Database. You have the following requirements:
-> Maximum database size of 500 GB
-> A point-in-time-restore of 35 days
-> Maximum database transaction units (DTUs) of 500
You need to choose the correct service tier and performance level.
Which service tier should you choose?
- A. Standard S3
- B. Premium P4
- C. Standard SO
- D. Basic
Answer : B
Explanation:
You should choose Premium P4. The Premium tier is the highest Azure SQL Database tier offered. This tier is used for databases and application that require the highest level of performance and recovery. The P4 level supports a maximum of 500 DTUs, a maximum database size of 500 GB, and a point-in-time-restore to any point in the last 35 days.
Incorrect Answers:
A: You should not choose Standard S3. The Standard tier with a performance level of S3 only supports a maximum database size of 250 GB. a maximum of 100
DTUs, and a point-in-timerestore of 35 days.
C: You should not choose Standard SO. The Standard tier with a performance level of SO only supports a maximum database size of 250 GB. a maximum of 10
DTUs, and a point-in-timerestore of 35 days.
D: You should not choose Basic. The Basic service tier only supports a maximum database size of 2 GB, a maximum of 5 DTUs, and a point-in-time-restore of 7 days.
References:
https://azure.microsoft.com/en-us/pricing/details/sql-database/
Question 8
You manage an on-premises, multi-tier application that has the following configuration:
-> Two SQL Server 2012 databases named SQL1 and SQL2
-> Two application servers named AppServerl and AppServer2 that run IIS
You plan to move your application to Azure.
You need to ensure that during an Azure update cycle or a hardware failure, the application remains available.
Which two deployment configurations should you implement? Each correct answer presents part of the solution.
- A. Deploy AppServerl and AppServer2 in a single availability set.
- B. Deploy all servers in a single availability set.
- C. Deploy SQL1 and AppServerl in a single availability set.
- D. Deploy SQL2 and AppServer2 in a single availability set.
- E. Deploy SQL1 and SQL2 in a single availability set.
Answer : AE
Explanation:
You should deploy AppServerl and AppServer2 in a single availability set.
You should deploy SQL1 and SQL2 in a single availability set.
Note: Using availability sets allows you to build in redundancy for your Azure services. By grouping related virtual machines and services (tiers) into an availability set (in this case, deploying both of your databases into an availability set), you ensure that if there is a planned or unplanned outage, your services will remain available. At the most basic level, virtual machines in an availability set are put into a different fault domain and update domain. An update domain allows virtual machines to have updates installed and then the virtual machines are rebooted together.
If you have two virtual machines in an availability set, each in its own update domain, a rebooting of one server does not bring down all of the servers in a given tier. A fault domain operates in the same manner, so if there is a physical problem with a server, rack, network, or other service, both machines are separated, and services will continue.
Incorrect Answers:
B: You should not deploy all servers in a single availability set. This will not provide the fault tolerance needed, as all machines would be rebooted (or suffer a hardware failure) together.
C: SQL1 and AppServerl provide different services, so they should not be grouped together.
D: You should not deploy SQL2 and AppServer2 in a single availability set. SQL2 and AppServer2 provide different services, so they should not be grouped together.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/manage-availability
Question 9
You are the database administrator in your company. You plan to create 10 identical environments that use SQL Server 2016 as a database engine. Each environment has the following custom requirements:
-> Three user databases must be preinstalled.
-> The tempdb database must contain eight data files that are 1024 MB each.
-> Trace flag 2371 must be turned at the instance level.
The solution must meet the following requirements:
-> The instance must be preconfigured.
-> No other database features are required in the future.
The solution must use the minimum administrative effort.
You need to prepare the environments. What should you do?
- A. Provision 10 Azure virtual machines that each contain SQL Server 2016, installed by using the default settings.
- B. Create an installation configuration file and perform unattended installations of SQL Server 2016.
- C. Create a virtual machine template by using a prepared instance of SQL Server 2016.
- D. Create a virtual machine template by using a complete instance of SQL Server 2016.
Answer : D
Explanation:
You should create a virtual machine template by using a complete instance of SQL Server 2016. You use the sysprep tool to prepare a complete instance of SQL
Server 2016. By using a complete instance, SQL Server, the network, and the users are all created, and the system cannot be reconfigured during the installation process.
Incorrect Answers:
A: You should not provision 10 Azure virtual machines that each contain SQL Server 2016, installed by using the default settings. A virtual machine created by using the Azure library provides you with a standard installation, and the requirements call for a custom installation. The instance will need some specific parameters for your custom installation. Therefore, you would have to customize each Azure virtual machine.
B: You should not create an installation configuration file and perform unattended installations of SQL Server 2016. This could be a valid option, but you would need to prepare 10 installations, and this would require a lot of effort. There are also some other administrative actions that must be performed that could also increase the amount of effort required, such as user database configuration.
C: You should not create a virtual machine template by using a prepared instance of SQL Server 2016. A virtual machine template that contains a prepared instance of SQL Server could be modified during the installation process. For example, some features could be added or removed during the installation process.
The prepared instance contains a preconfigured version of SQL Server without network and user configurations.
References:
https://support.neverfail.com/hc/en-us/articles/115015350287-Deploy-Microsoft-SQL-Server-2016-Virtual-Machine-Template
Question 10
You have an on-premises server that runs Windows Server 2012 R2. The server has a Microsoft SQL Server 2016 instance that has one user database. The database is 2 TB.
Your company has a Win32 application installed on 1,000 computers. The application connects to the database by using a network name of server1.contoso.local.
You need to migrate the database to SQL Server 2016 on a Microsoft Azure virtual machine that runs Windows Server 2016. The solution must minimize outages to the application.
What should you do?
- A. Copy the database files and update the records in DNS.
- B. Implement an availability group and update the records in DNS.
- C. Implement database mirroring and update the records in DNS.
- D. Implement database mirroring and change the connection string.
- E. Copy the database files and change the connection string.
Answer : B
Explanation:
SQL Server high availability and disaster recovery (HADR) technologies that are supported in Azure include:
-> Always On Availability Groups
-> Always On Failover Cluster Instances
-> Log Shipping
-> SQL Server Backup and Restore with Azure Blob Storage Service
Incorrect Answers:
A: We need a high availability solution.
C, D: Database Mirroring is deprecated in SQL Server 2016.
Reference:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr
Question 11
You plan to deploy an AlwaysOn failover cluster in Microsoft Azure. The cluster has a Service Level Agreement (SLA) that requires an uptime of at least 99.95 percent.
You need to ensure that the cluster meets the SLA.
Which cmdlet should you run before you deploy the virtual machine?
- A. New-AzureRmAvailabilitySet
- B. New-AzureRmLoadBalancer
- C. New-AzureRmSqlDatabaseSecondary
- D. New-AzureRmSqlElasticPool
- E. New-AzureRmVM
- F. New-AzureRmSqlServer
- G. New-AzureRmSqlDatabaseCopy
- H. New-AzureRmSqlServerCommunicationLink
Answer : B
Explanation:
On Azure virtual machines, a SQL Server Availability Group requires a load balancer. The load balancer holds the IP address for the Availability Group listener.
The New-AzureRmLoadBalancer cmdlet creates an Azure load balancer.
Incorrect Answers:
E: The New-AzureRmVM cmdlet creates a virtual machine in Azure.
Reference:
https://docs.microsoft.com/en-us/powershell/module/azurerm.network/new-azurermloadbalancer?view=azurermps-6.2.0
Question 12
DRAG DROP -
You plan to migrate on-premises Microsoft SQL Server to SQL Server on a Microsoft Azure virtual machine.
You need to ensure that the Azure virtual machine can handle the workload.
Which tool should you use for each environment? To answer, drag the appropriate tools to the correct options. Each tool may be used once. More than once, or not at all.
Select and Place:
Answer : 
Explanation:
Question 13
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
A network administrator upgrades the hardware firewalls on the network.
You need to verify whether data migration still runs successfully.
Which stored procedure should you run?
- A. Sys_sp_testlinkedserver
- B. Sys_sp_rda_test_connection
- C. Sys_sp_rda_reauthorized_db
- D. Sp_set_firewall_rule
Answer : B
Explanation:
The Sys_sp_rda_test_connection cmdlet tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-rda-test-connection-transact-sql?view=sql-server-2017
Question 14
HOTSPOT -
You plan to deploy multiple Microsoft SQL Servers to a set of Windows Server virtual machines. You create the following configuration file:
You run the following batch file to start the installation:
The PostInstall.sql script contains the following Transact-SQL statements:
You need to evaluate the installation process.
What changes are made during the installation?
To answer, select the appropriate responses in the answer area.
Hot Area:
Answer : 
Explanation:
References:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file?view=sql-server-ver15
Question 15
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You plan to migrate an on-premises Microsoft SQL Server virtual machine (VM) to a Microsoft Azure IaaS VM.
You deploy the VM by using the following settings:
The workload on this instance has a very high tempdb load.
You need to maximize the performance of the tempdb database.
Solution: You use a D-Series VM and store the tempdb database on drive D.
Does this meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance