Provisioning SQL Databases v1.0 (70-765)

Page:    1 / 15   
Total 229 questions

You have a single-server on-premises Microsoft SQL Server deployment.
You must migrate the environment to an Azure virtual machine (VM). You must minimize costs while maintaining the same level of performance as the on- premises SQL environment.
You need to evaluate the number and types of read/write operations for the existing deployment.
Which tool should you use?

  • A. SQL Profiler trace using Tuning template
  • B. DiskSpd
  • C. Import/Export wizard
  • D. SQL Server Logs


Answer : B

Explanation:
The DiskSpd utility is a feature-rich and versatile storage testing tool that is ideal for testing the storage subsystem. DiskSpd supersedes SQLIO
References:
https://gallery.technet.microsoft.com/DiskSpd-A-Robust-Storage-6ef84e62

HOTSPOT -
You plan to create a database in Microsoft Azure to manage sales data for an organization.
You need to create an Azure SQL Database that meets standard naming requirements.
Which names should you use? To answer, select the appropriate options in the answer area.
Note: Each correct selection is worth one point.
Hot Area:




Answer :

HOTSPOT -
A company plans to deploy Microsoft SQL Server on Azure using an Azure Resource Manager template. The deployment must be fault-tolerant with a 99.99 percent service level agreement (SLA).
You need to complete the Azure Resource manager template while minimizing costs.
How should you complete the Azure Resource manager template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:




Answer :

References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-single-databases

DRAG DROP -
You are deploying a virtual machine (VM) to run Microsoft SQL Server in Azure.
The environment currently requires 10,000 IOPS and two terabytes (TB) of data. You expect the IOPS and data volume to double in the first six months after you deploy. The server is currently configured with an Azure Blob storage volume as the F: drive on the SQL Server.
You need to ensure that the environment can support growth requirements while providing the lowest cost solution.
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.
Select and Place:




Answer :

DRAG DROP -
You are deploying a DS-13 series virtual machine (VM) to run Microsoft SQL Server in Azure. You plan to migrate a large data warehouse to the SQL Server instance. The data warehouse is currently 15 terabytes (TB) in size.
You expect growth of 5 TB per year. You cannot increase the size of the VM.
You need to design a storage strategy to support the expected growth of the data warehouse over the next five years.
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.
Select and Place:




Answer :

Explanation:
The DS-13 has 16TB storage. However, the database is 15TB and is expected to grow by 5TB a year over the next 5 years for a total of 25TB over the 5 years.
References:
https://cloudblogs.microsoft.com/sqlserver/2015/04/23/azure-premium-storage-provides-highest-performance-for-sql-server-in-azure-vm/

You have three Microsoft SQL Server instances. Each instance is deployed to a separate physical server.
You must install a new SQL Server instance for the marketing team. All instances must have the same configuration.
You need to create an unattended answer file to deploy the marketing SQL instance.
What should you do?

  • A. Create an autounattend.xml file.
  • B. Cope the ConfigurationFile.ini from one of the other servers.
  • C. Cope the setup.exe.config file from one of the other servers.
  • D. Copy the rlauncher.log file from one of the other servers.


Answer : B

References:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file?view=sql-server-2017

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.
Your company plans to use Microsoft Azure Resource Manager templates for all future deployments of SQL Server on Azure virtual machines.
You need to create the templates.
Solution: You use Visual Studio to create a XML template that defines the deployment and configuration settings for the SQL Server environment.
Does the solution meet the goal?

  • A. Yes
  • B. No


Answer : B

Explanation:
Azure Resource Manager template consists of JSON, and expressions that you can use to construct values for your deployment.
A good JSON editor, not a Resource Group template, can simplify the task of creating templates.
Note: In its simplest structure, an Azure Resource Manager template contains the following elements:
{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "",
"parameters": { },
"variables": { },
"resources": [ ],
"outputs": { }
}
References:
https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring-templates

You plan to create an AlwaysOn availability group that will have two replicas in Microsoft Azure and two on-premises replicas.
You need to ensure that a replica in Azure is always available.
Which cmdlet should you run before you deploy the virtual machines?

  • 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

References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-ps-alwayson-int-listener

HOTSPOT -
You are migrating an on-premises database server to Microsoft SQL Server on a Microsoft Azure virtual machine. The virtual machine will use Premium Storage.
The on-premises database files for a user database are configured as shown in the following table.


What is the minimum number of VHDs that should be provisioned in Azure for each file type to meet the current IOPS needs? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:



Answer :

References:
https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy

HOTSPOT -
You are building the database platform for a multi-tenant application. The application will have one database per tenant and there will be 30 tenants. The application will require at least 10 GB of In-Memory OLTP per tenant database.
You need to implement the database platform for the application. The solution must minimize costs.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:




Answer :

References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-in-memory#additional-resources https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

You plan to deploy 20 Microsoft Azure SQL Database instances to an elastic pool in Azure to support a batch processing application.
Two of the databases in the pool reach their peak workload threshold at the same time every day. This leads to inconsistent performance for batch completion.
You need to ensure that all batches perform consistently.
What should you do?

  • A. Limit the maximum number of elastic Database Transaction Units (eDTUs) per database
  • B. Increase the storage limit in the pool
  • C. Create an In-Memory table
  • D. Create an elastic job


Answer : A

Explanation:
In SQL Database, the relative measure of a database's ability to handle resource demands is expressed in Database Transaction Units (DTUs) for single databases and elastic DTUs (eDTUs) for databases in an elastic pool.
A pool is given a set number of eDTUs, for a set price. Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more eDTUs to meet demand.
Additional eDTUs can be added to an existing pool with no database downtime.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

HOTSPOT -
You have an AlwaysOn availability group on Microsoft Azure virtual machines. All the virtual machines are on a virtual network that uses a network address of
10.0.0.0/24.
You plan to deploy a new replica to an additional Azure virtual machine. You deploy the new virtual machine by using an Azure virtual machine template that has
SQL Server preconfigured.
The network security group for the new virtual machine is configured as shown in the following exhibit.


Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:



Answer :

You are planning a Microsoft SQL Server deployment. You do not have a Storage Area Network (SAN) or Network Attached Storage (NAS) infrastructure.
You have a physical host that has 128 gigabytes (GB) RAM, two ""core processors, and two 1 GB network interface cards (NICs). The host supports RAID through a write-caching RAID controller. The RAID controller is not specifically designed for DBMS systems. The host can support up to eight physically attached disks.
The system does not include a battery backup power supply.
You must implement SQL Server best practices when installing and configuring the server to host SQL Server.
You need to configure the system to support a SQL Server installation by using best practices.
What should you do?

  • A. Increase the RAM in the server.
  • B. Disable write-caching on the RAID controller.
  • C. Configure NIC Teaming.
  • D. Add two more processors.


Answer : B

You are the database administrator for your company. Your company has one main office and two branch offices. You plan to create three databases named DB1,
DB2, and DB3 that will be hosted on one Azure SQL Database server. You have the following requirements:
-> The main office must be able to connect to all three databases.
-> The branch offices must be able to connect to DB2 and DB3.
-> The branch offices must not be able to access DB1.
You need to configure transparent data encryption (TDE) for DB1.
Which two actions should you perform? Each correct answer presents part of the solution.

  • A. Run CREATE CERTIFICATE certl WITH Subject = TDE Cert1 on DB1.
  • B. Connect to DB1.
  • C. Run ALTER DATABASE DB1 SET ENCRYPTION ON;.
  • D. Connect to the master database.
  • E. Run CREATE MASTER KEY on the master database.


Answer : BC

Explanation:
You should connect to DB1. To encrypt DB1, you connect directly to DB1. When you connect to DB1.
You use your dbmanager or administrative credentials.
You should run ALTER DATABASE DB1 SET ENCRYPTION ON.
You use the ALTER DATABASE DB1 SET ENCRYPTION ON statement to encrypt the database. This is the statement that turns on TDE for Azure SQL
Database.
Incorrect Answers:
A: You should not run CREATE CERTIFICATE certl WITH Subject = TDE Cert' on DB1. You do not need to create a certificate to encrypt an Azure SQL database. This would be a part of the solution when you encrypt an on-premises database. You should not connect to the master database. To encrypt DB1, you need to connect directly to DB1. not to the master database.
E: You should not run CREATE MASTER KEY on the master database.
You would execute CREATE MASTER KEY when you have to encrypt an on-premises database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

You have a server named Serverl that is hosted in an Azure virtual machine. Server1 contains the following:
-> One instance of SQL Server 2016 Enterprise
-> 10 databases
-> 500 stored procedures
You have a database named Databasel that is hosted on Server1.
Database1 contains 100 queries that are executed dynamically from web applications.
You plan to remove data from the procedure cache on Database1.
You have the following requirements:
Changes to Database1 must not affect other databases that are hosted on Server1


-> Changes to Database1 must not affect the performance of queries that are stored in other databases.
-> The solution must minimize administrative effort.
You need to remove the data from the procedure cache as quickly as possible.
What should you do?

  • A. Run DBCC FREEPROCCACHE.
  • B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database 1.
  • C. Run DBCC DROPCLEANBUFFERS.
  • D. Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition.


Answer : B

Explanation:
You should run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database! This statement lets you change the settings of a database without affecting other databases that are installed on the instance of SQL Server 2016.
Incorrect Answers:
A: You should not run DBCC FREEPROCCACHE. DBCC FREEPROCCACHE would clean the entire plan cache and would affect all databases. It is possible to remove a single plan from the cache by using the plan_handle argument to DBCC FREEPROCCACHE, but you would have to identify all plans that are related to
Databasel, which requires a lot more administrative effort.
C: You should not run DBCC DROPCLEANBUFFERS. DBCC DROPCLEANBUFFERS will remove the clean pages from the buffer cache. Columnstore pages are removed from the columnstore cache.
D: You should not write a script that will iterate through each stored procedure definition and add WITH RECOMPILE to the definition. Each time the procedure is called, it will be recompiled, and this might degrade the server's performance.
This approach would require additional administrative effort to produce the script. In addition, some calls are made from the web application and the script would not have any control over these calls. In earlier versions of SQL Server, prior to the availability of the ALTER DATABASE statement, this option would have been the way to avoid affecting other databases.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

Page:    1 / 15   
Total 229 questions