Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Want to know features? Want to lear more about experience? Study . Gat a success with an absolute guarantee to pass Microsoft 70-765 (Provisioning SQL Databases (beta)) test on your first attempt.
Check 70-765 free dumps before getting the full version:
NEW QUESTION 1
You administer a Microsoft SQL Server 2014 instance.
The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week.
You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
No more than 5 minutes worth of transactions are lost. Data can be recovered by using the minimum amount of administrative effort.
What should you do? Choose all that apply.
Answer: BCDE
Explanation: The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you restore a database to any point of time that is contained within a log backup (point-in-time
recovery). You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data.
References:https://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx
NEW QUESTION 2
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).
You load data to the database by using Azure Data Factory. You need to reduce the amount of time it takes to load the data.
Solution: You move the database to a Premium database pool that has 125 eDTUs. Does the solution meet the goal?
Answer: B
Explanation: We need at least 400 eDTUs.
NEW QUESTION 3
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine.
You must enable Always Encrypted for columns in a database. You need to configure the key store provider.
What should you do?
Answer: D
Explanation: There are two high-level categories of key stores to consider - Local Key Stores, and Centralized Key Stores.
Centralized Key Stores - serve applications on multiple computers. An example of a centralized key store is Azure Key Vault.
Local Key Stores References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-storecolumn-master-ke
NEW QUESTION 4
You administer a SQL Server 2014 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met: Which Transact-SQL statement should you use?
Answer: J
Explanation: Use REVOKE to remove the grant or deny of a permission.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-
NEW QUESTION 5
You have a server named server1-contoso.database.windows.net that has a Microsoft Azure SQL database.
You need to create a group of Azure SQL databases that share the resources on server1-contoso.database.windows.net.
Which cmdlet should you run before you create the database?
Answer: D
Explanation: The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database.
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 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.
NEW QUESTION 6
Settings Value VM size D3
Storage Location Drive E Storage type Standard Tempdb location Drive C
The workload on this instance has of the tembdb 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?
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-performan
NEW QUESTION 7
You have Microsoft SQL server on a Microsoft Azure virtual machine. The virtual machine has 200 GB of data.
User report a slow response time when querying the database.
You need to identify whether the storage subsystem causes the performance issue. Which performance monitor counter should you view?
Answer: B
NEW QUESTION 8
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?
Answer: D
Explanation: In SQL Database, the relative measure of a database's ability tohandle 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
NEW QUESTION 9
You plan to create an AlwaysOn availability group that will have two replicas in Microsoft Azure and two on premises replicas.
You need to configure the network to support the availability group listener. Which cmdlet should you run first?
Answer: B
Explanation: An availability group listener is a virtual network name that clients connect to for database access. On Azure virtual machines, a load balancer holds the IP address for the listener. The load balancer routes traffic to the instance of SQL Server that is listening on the probe port. Usually, an availability group uses an internal load balancer.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windowsportal-sql-ps-al
NEW QUESTION 10
You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012. You create an Availability Group defined by the following schema. (Line numbers are included for reference
only.)
You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
Answer: C
Explanation: As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.
NEW QUESTION 11
You use Microsoft SQL Server 2014 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?
Answer: A
Explanation: A common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic.
References:https://blogs.msdn.microsoft.com/sqlcat/2011/11/28/a-computed-column-defined-with-a-user-define
NEW QUESTION 12
You administer two Microsoft SQL Server 2014 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers. What should you do?
Answer: B
Explanation: To enable certificate authentication for database mirroring on a given server instance, the system administrator must configure each server instance to use certificates on both outbound and inbound connections.
References:
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/use-certificates-for-a-database-mirrorin
NEW QUESTION 13
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01.
What should you create?
Answer: C
Explanation: SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don't refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on.
References: https://www.mssqltips.com/sqlservertip/3384/configuring-critical-sql-server-alerts/
NEW QUESTION 14
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.
You need to implement real-time encryption of the database and all backups. Solution: You enable Transparent Data Encryption (TDE) on the primary instance. Does the solution meet the goal?
Answer: A
Explanation: Azure SQL Database and Data Warehouse offer encryption-at-rest by providing Transparent Data Encryption (TDE) for all data written to disk, including databases, log files and backups. This protects data in case of unauthorized access to hardware. TDE provides a TDE Protector that is used to encrypt the Database Encryption Key (DEK), which in turn is used to encrypt the data. With the TDE and Bring Your Own Key (BYOK) offering currently in preview, customers can take control of the TDE Protector in Azure Key Vault.
Taking advantage of TDE with BYOK for databases that are geo-replicated to maintain high availability requires to configure and test the scenario carefully.
References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/
NEW QUESTION 15
You administer a single server that contains a Microsoft SQL Server 2014 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?
Answer: B
Explanation: References:
https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server
NEW QUESTION 16
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.
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 create the desired SQL Server configuration in an Azure Resource Group, then export the Resource Group template and save it to the Templates Library.
Does the solution meet the goal?
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, a 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
NEW QUESTION 17
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are tuning the performance of a virtual machines that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores.
The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1.
Several reports use the PIVOT statement and access more than 100 million rows in table1.
You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database.
You need to prevent the PAGELATCH_IO waits from occurring. Solution: You add more tempdb databases.
Does this meet the goal?
Answer: B
Explanation: From SQL Server’s perspective, you can measure the I/O latency from sys.dm_os_wait_stats. If you consistently see high waiting for PAGELATCH_IO, you can benefit from a faster I/O subsystem for SQL Server.A cause can be poor design of your database - you may wish to split out data located on 'hot pages', which are accessed frequently and which you might identify as the causes of your latch contention. For example, if you have a currency table with a data page containing 100 rows, of which 1 is updated per transaction and you have a transaction rate of 200/sec, you could see page latch queues of 100 or more. If each page latch wait costs just 5ms before clearing, this represents a full half-second delay for each update. In this case, splitting out the currency rows into different tables might prove more performant (if less normalized and logically structured).
References: https://www.mssqltips.com/sqlservertip/3088/Explanation:-of-sql-server-io-and-latches/
NEW QUESTION 18
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 AppServer1 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.
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.
NEW QUESTION 19
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.
Answer:
Explanation: 
100% Valid and Newest Version 70-765 Questions & Answers shared by prep-labs.com, Get Full Dumps HERE: https://www.prep-labs.com/dumps/70-765/ (New 209 Q&As)