Best Quality 70-765 Exam Questions 2021

NEW QUESTION 1
Note: This questions is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
Your company has several Microsoft Azure SQL Database instances.
Data encryption should be allowed to be implemented by the client applications that access the data. Encryption keys should not be made available to the database engine.
You need to configure the database. What should you implement?

• A. transport-level encryption
• B. cell-level encryption
• C. Transparent Data Encryption
• D. Always Encrypted
• E. Encrypting FileSystem
• F. BitLocker
• G. dynamic data masking

Answer: A

Explanation: Using encryption during transit with Azure File Shares
Azure File Storage supports HTTPS when using the REST API, but is more commonly used as an SMB file share attached to a VM.
HTTPS is a transport-level security protocol.

NEW QUESTION 2
Note: This questions is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You manage on-premises and Microsoft Azure SQL Database instances for a company. Your environment must support the Microsoft SQL Server 2012 ODBS driver.
You need to encrypt only specific columns in the database. What should you implement?

• A. transport-level encryption
• B. cell-level encryption
• C. Transparent Data Encryption
• D. Always Encrypted
• E. Encrypting File System
• F. BitLocker
• G. dynamic data masking

Answer: D

Explanation: To encrypt columns you can configure Always Encrypted.
SQL Server Management Studio (SSMS) provides a wizard that helps you easilyconfigure Always Encrypted by setting up the column master key, column encryption key, and encrypted columns for you.
Always Encrypted allows client applications to encrypt sensitive data and never reveal the data or the encryption keys to SQL Server or Azure SQL Database. An Always Encrypted enabled driver, such as the ODBC Driver 13.1 for SQL Server, achieves this by transparently encrypting and decrypting sensitive data in the client application.
Note: The ODBC driver automatically determines which query parameters correspond to sensitive database columns (protected using Always Encrypted), and encrypts the values of those parameters before passing the data to SQL Server or Azure SQL Database. Similarly, the driver transparently decrypts data retrieved from encrypted database columns in query results.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault#encrypt-columns-configure-always-encrypted https://msdn.microsoft.com/en-us/library/mt637351(v=sql.110).aspx

NEW QUESTION 3
You have an on-premises Microsoft SQL Server named Server1.
You provision a Microsoft Azure SQL Database server named Server2. On Server1, you create a database named DB1.
You need to enable the Stretch Database feature for DB1.
Which five 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.

Answer:

Explanation: Step 1: Enable the remote data archive option in DB1 Prerequisite: Enable Stretch Database on the server
Before you can enable Stretch Database on a database or a table, you have to enable it on the local server. To enable Stretch Database on the server manually, run sp_configure and turn on the remote data archive option.
Step 2: Create a firewall rule in Azure
On the Azure server, create a firewall rule with the IP address range of the SQL Server that lets SQL Server communicate with the remote server.
Step 3: Create a master key in the master database
To configure a SQL Server database for Stretch Database, the database has to have a database master key. The database master key secures the credentials that Stretch Database uses to connect to the remote database.
Step 4: Create a database scoped credential for authentication to Azure
When you configure a database for Stretch Database, you have to provide a credential for Stretch Database to use for communication between the on premises SQL Server and the remote Azure server. You have two options.
Step 5: Create a server-level credential for authentication to Azure.
To configure a database for Stretch Database, run the ALTER DATABASE command. For the SERVER argument, provide the name of an existing Azure server, including the
.d atabase.windows.net portion of the name - for example, MyStretchDatabaseServer.database.windows.net.
Provide an existing administrator credential with the CREDENTIAL argument, or specify FEDERATED_SERVICE_ACCOUNT = ON. The following example provides an existing credential.
ALTER DATABASE <database name> SET REMOTE_DATA_ARCHIVE = ON (
SERVER = '<server_name>' ,
CREDENTIAL = <db_scoped_credential_name>
) ; GO
References:
https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-a-database?view=sq

NEW QUESTION 4
You have a Microsoft Azure SQL Database server named server1-contoso.database.windows.net in a resource group named RG1.
You need to create an elastic pool.
How should you complete the script? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: New-AzureRmSqlServer
Create an Azure SQL Database logical server using the New-AzureRmSqlServer command. A logical server contains a group of databases managed as a group.
Example:
New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-Location $location `etc.
Box 2: New-AzureRmSqlElasticPool
The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database. Example:
New-AzureRmSqlElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01"
-ElasticPoolName "ElasticPool01" -Edition "Standard"
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-powershell https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/new-azurermsqlelasticpool?view=azurermps-6

NEW QUESTION 5
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?

• A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
• B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
• C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
• D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.

Answer: B

Explanation: To set up a readable secondary replica, you first create an availability group. Then you add replicas. You can choose either Yes or Read-intent only options.

References: http://msdn.microsoft.com/en-us/library/jj542414.aspx

NEW QUESTION 6
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 Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema::Customers FROM UserA
• B. DENY SELECT ON Object::Regions FROM UserA
• C. EXEC sp_addrolemember 'Sales', 'UserA'
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM Sales
• G. DENY SELECT ON Schema::Customers FROM UserA
• H. EXEC sp_droprolemember 'Sales', 'UserA'
• I. REVOKE SELECT ON Object::Regions FROM Sales
• J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: F

Explanation: Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-

NEW QUESTION 7
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 manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the LEGACY_CARDINALITY_ESTIMATION option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 8
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?

• A. A Database Audit Specification
• B. A Policy
• C. An Alert
• D. A SQL Profiler Trace
• E. A Resource Pool
• F. An Extended Event session
• G. A Server Audit Specification

Answer: D

Explanation: Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.
References:
http://msdn.microsoft.com/en-us/library/ms188246.aspx

NEW QUESTION 9
You administer two Microsoft SQL Server 2014 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber. The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
"Cannot access the file. Operating system error code 5 (Access is denied.)." You need to configure the distribution agent by granting only the minimum required access to all accounts.
What should you do?

• A. Configure the Subscriber to use the Local System account.
• B. Configure the SQL Server Agent service to run under the Local System accoun
• C. Configure the Subscriber to use the SQL Server Agent service account.
• D. Configure the SQL Server Agent service to run under a Windows domain accoun
• E. Configure the Subscriber to use the SQL Server Agent service accoun
• F. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1.
• G. Configure the Subscriber to use a Windows domain accoun
• H. Grant READ access for the domain account to the ReplData share on ProdSrv1.

Answer: D

Explanation: Confirm that distribution agent has read privileges, full control access is not required, to the folder in question.
References:
http://stackoverflow.com/questions/14555262/cannot-bulk-load-operating-system-error-code-5-access-is-denied

NEW QUESTION 10
You have a database named DB1. You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?

• A. Run DBCC CHECKDB (‘db1’, REPAIR_ALLOW_DATA_LOSS).
• B. Run DBCC CHECKDB (‘db1’, REPAIR_FAST).
• C. Delete the transaction logs and restart the Microsoft SQL Server instance.
• D. Run DBCC CHECKDB (‘db1’, REPAIR_REBUILD).
• E. Restore the database from a backup.

Answer: C

Explanation:
REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

NEW QUESTION 11
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

• A. STANDBY
• B. Differential
• C. FULL
• D. CHECKSUM
• E. BULK_LOGGED
• F. CONTINUE_AFTER_ERROR
• G. SIMPLE
• H. DBO_ONLY
• I. COPY_ONLY
• J. SKIP
• K. RESTART
• L. Transaction log
• M. NO_CHECKSUM
• N. NORECOVERY

Answer: F

Explanation: The CONTINUE_AFTER_ERROR option, of the Transact-SQL BACKUP command, instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

NEW QUESTION 12
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.
What should you create?

• A. An Alert
• B. A Resource Pool
• C. An Extended Event session
• D. A Server Audit Specification
• E. A SQL Profiler Trace
• F. A Database Audit Specification
• G. A Policy
• H. A Data Collector Set

Answer: C

Explanation: SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.
Extended Events is a light weight performance monitoring system that uses very few performance resources. A SQL Server Extended Events session is created in the SQL Server process hosting the Extended Events
engine.
References:https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events

NEW 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-tr

NEW QUESTION 14
You administer all the deployments of Microsoft SQL Server 2014 in your company. You have two servers in the same data center that hosts your production database.
You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers.
You need to achieve these goals without manual intervention. Which configuration should you use?

• A. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
• B. SQL Server that includes an application database configured to perform transactional replication
• C. Two servers configured in the same data centerA primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
• D. Two servers configured in different data centersSQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
• E. Two servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
• F. Two servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• G. SQL Server that includes an application database configured to perform snapshot replication
• H. Two servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode

Answer: H

Explanation: Always On availability groups supports two availability modes—asynchronous-commit mode and synchronous-commit mode
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References:https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-mode

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 instance named SQL2012. You are in the process of migrating a database from a SQL Server 2008 instance named SQL2008 to the SQL2012 instance.
You have upgraded a database from the SQL2008 instance by using the side-by-side migration technique. You need to migrate the SQL Server logins from the SQL2008 instance to the SQL2012 instance.
What should you do?

• A. Back up the master database on the SQL2008 instanc
• B. Restore the master database on the SQL2012 instance
• C. Use the Transfer Logins task in a Microsoft SQL Server Integrated Services package
• D. Use sp_grantlogin
• E. Use xp_logininfo.

Answer: C

Explanation: sp_grantlogin creates a SQL Server login.

NEW QUESTION 16
You are a database developer of a Microsoft SQL Server 2014 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);
• D. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID,CustomerID));

Answer: D

NEW QUESTION 17
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

• A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112)=CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime <DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

Explanation: To compare a time with date we must use >= and > operators, and not the = operator.