Provisioning SQL Databases – Microsoft: 70-765 exam. Share the latest 70-765 dumps and Practice test questions for free. Real and effective exam questions and answers. 70-765 pdf online Download, Examthings share 30 Practice test questions for FREE! Get the full 70-765 exam dumps: https://www.leads4pass.com/70-765.html (Total questions:208 Q&A)
[PDF] Free Microsoft MCSA 70-765 pdf dumps download from Google Drive: https://drive.google.com/open?id=1T53F4Z36tsS41UDYoNog_fVOP3ueIPk6
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
Exam 70-765: Provisioning SQL Databases – Microsoft: https://www.microsoft.com/en-us/learning/exam-70-765.aspx
Latest effective Microsoft MCSA 70-765 Exam Practice Tests
QUESTION 1
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 the minimum amount of data is lost.
Which recovery model should the database use?
A. NORECOVERY
B. FULL
C. NO_CHECKSUM
D. CHECKSUM
E. Differential
F. BULK_LOGGED
G. STANDBY
H. RESTART
I. SKIP
J. Transaction log
K. DBO ONLY
L. COPY_ONLY
M. SIMPLE
N. CONTINUE AFTER ERROR
Correct Answer: B
The full recovery model requires log backups. No work is lost due to a lost or damaged data file. Can recover to a
specific point in time, assuming that your backups are complete up to that point in time.
QUESTION 2
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?
A. Yes
B. No
Correct Answer: B
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/
QUESTION 3
You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named
Sales.Orders with the following script.
Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that that you can retrieve the following information:
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate. Update rows as the
order status changes.
B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status.
Insert new rows into the table as the order status changes.
C. Implement change data capture on the Sales.Orders table.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the
value of each column from null to the appropriate date as the order status changes.
Correct Answer: A
This stores only the minimal information required.
QUESTION 4
You administer a Microsoft SQL Server 2014 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
A. Execute sp_configure \\’max log size\\’, 2G.
B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.
C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of
the file for the transaction log.
D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the
Transaction log Autogrowth window and set the maximum size of the file.
Correct Answer: B
You can use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file
To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the
MAXSIZE option. However, there is no SET LOGFILE subcommand.
References: https://technet.microsoft.com/en-us/library/ms365418(v=sql.110).aspx#ControlGrowth
QUESTION 5
You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named
CONTOSO\SQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all
that apply.
A. Restart the SQL Server Agent Service.
B. Disable snapshot isolation.
C. Restart the SQL Server Service.
D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
E. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
F. Enable snapshot isolation.
Correct Answer: CD
How To Enable Instant File Initialization http://msdn.microsoft.com/en-us/library/ms175935.aspx
QUESTION 6
You administer a Microsoft SQL Server 2016 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.
A. Disable FILESTREAM on the Database.
B. Enable FILESTREAM on the Server Instance.
C. Configure the Database for Partial Containment.
D. Create a non-empty FILESTREAM file group.
E. Enable Contained Databases on the Server Instance.
F. Set the FILESTREAM directory name on the Database.
Correct Answer: BDF
QUESTION 7
You administer a Microsoft SQL Server 2014 server. One of the databases on the server supports a highly active OLTP
application.
Users report abnormally long wait times when they submit data into the application. You need to identify which queries
are taking longer than 1 second to run over an extended period of time.
What should you do?
A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of
more than 1,000.
B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the
duration of each step.
D. Run the sp_who command from a query window.
E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
Correct Answer: A
QUESTION 8
You are the administrator for a SQL Server 2016 instance that stores the data for an online transaction processing sales
system. The company takes full backups every week; differential backups on the days with no full backups; and hourly
transaction backups.
These backups are stored on a backup server in the company\\’s data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose
orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or
purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Set up SQL Server Always On with a SQL Azure database as a replica.
B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
C. Put the differential backup on tape and send it to the third-party backup storage system.
D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different
geographical location.
Correct Answer: D
Microsoft SQL Server Backup to Microsoft Azure Tool enables backup to Azure Blob Storage and encrypts and
compresses SQL Server backups stored locally or in the cloud. References: https://www.microsoft.com/en-us/download/
details.aspx?id=40740
QUESTION 9
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
Correct Answer: D
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
QUESTION 10
Note: This question 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 have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL
Server in an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
You need to recommend a storage solution for the SQL Servers. The solution must minimize costs.
Which storage option should you use?
A. Premium P10 disk storage
B. Premium P20 disk storage
C. Premium P30 disk storage
D. Standard locally redundant disk storage
E. Standard geo-redundant disk storage
F. Standard zone redundant blob storage
G. Standard locally redundant blob storage
H. Standard geo-redundant blob storage
Correct Answer: A
P10 has 500 IOPS per disk, which provides for more than 1 million IOPS per month. Note: 3600*30* 500 is 54 million
IOPS/month.
References: https://azure.microsoft.com/en-us/pricing/details/managed-disks/
QUESTION 11
You plan to install a Microsoft SQL Server 2014 instance.
The instance will support a database that has the following requirements:
Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met.
Which feature should you use?
A. Excel Services
B. FILESTREAM
C. SQL Server Integration Services (SSIS)
D. OpenXML
Correct Answer: B
FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on
the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same
time maintain transactional consistency between the unstructured data and corresponding structured data.
References:https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql- server
QUESTION 12
You administer a Microsoft SQL Server 2014 environment. One of the SQL Server 2014 instances contains a database
named Sales.
You plan to migrate Sales to Windows Azure SQL Database.
To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Set database containment to AZURE.
B. Enable server property contained database authentication.
C. Disable server property cross db ownership chaining.
D. Set database containment to PARTIAL.
E. Disable server property contained database authentication.
F. database containment to FULL.
Correct Answer: BD
A contained database is a database that is isolated from other databases and from the instance of SQL Server that
hosts the database.
B: In the contained database user model, the login in the master database is not present. Instead, the authentication
process occurs at the user database, and the database user in the user database does not have an associated login in
the master database. SQL Database and SQL Data Warehouse support Azure Active Directory identities as contained
database users.
D: The contained database feature is currently available only in a partially contained state. A partially contained
database is a contained database that allows the use of uncontained features. References:
https://docs.microsoft.com/en-us/sql/relational- databases/databases/contained-databases
QUESTION 13
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
Correct Answer: C
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
QUESTION 14
You plan to migrate a database To Microsoft Azure SQL Database. The database requires 500 gigabytes (GB) of
storage.
The database must support 50 concurrent logins. You must minimize the cost associated with hosting the database.
You need to create the database.
Which pricing tier should you use?
A. Standard S3 pricing tier
B. Premium P2tier
C. Standard S2 pricing tier
D. Premium P1 tier
Correct Answer: D
For a database size of 500 GB the Premium tier is required. Both P1 and P2 are adequate. P1 is preferred as it is
cheaper. Note:
QUESTION 15
A company has an on-premises Microsoft SQL Server environment with a SQL-Server named SQL01.
You need to create a local sysadmin account on SQL01 named Admin1.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in
the answer area.
Hot Area:
B: First we create a login with the CREATE LOGIN command.
B: First we create a login with the CREATE LOGIN command. E: Then we add it to the sysadmin role.
1.
To add a member to a fixed server role.
2.
In Object Explorer, connect to an instance of Database Engine.
3.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
ALTER SERVER ROLE diskadmin ADD [Domain\Juan] ;
GO
G: Finally we add a database user for the login we created.
References: https://technet.microsoft.com/en-us/library/aa337562(v=sql.110).aspx
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.
You have a mission-critical application that stores data in a Microsoft SQL Server instance.
The application runs several financial reports. The reports use a SQL Server-authenticated login named
Reporting_User. All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete. The queries
consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the
application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You configure the Resource Governor to set the MAXDOP parameter to 0 for all queries against the
database.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
SQL Server will consider parallel execution plans for queries, index data definition language (DDL) operations, and
static and keyset-driven cursor population. You can override the max degree of parallelism value in queries by
specifying the MAXDOP query hint in the query statement.
References: https://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx
QUESTION 17
You administer a Microsoft SQL Server 2014 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the
database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database.
What should you do?
A. Use the copy database wizard
B. Use the Database Transfer wizard
C. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database
D. Backup the database from the local server and restore it to Windows Azure SQL Database
Correct Answer: C
You would need to use either the SQL Server Management Studio or Transact-SQL. References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud- migrate
QUESTION 18
You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2014 Standard Edition. The server
hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index
named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?
A. ALTER INDEX IX_OrderDateON CustomerOrder DISABLE
B. ALTER INDEX IX_OrderDateON CustomerOrder ENABLE
C. ALTER INDEX IX_OrderDateON CustomerOrder REORGANIZE
D. ALTER INDEX IX OrderDateON CustomerOrder REBUILD
Correct Answer: C
Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it
goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This
operation is always online, and if you cancel it then it\\’s able to just stop where it is (it doesn\\’t have a giant operation to
rollback).
References:https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server- rebuild-reorganize/
QUESTION 19
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 the backup size is as small as possible.
Which backup should you perform every two hours?
A. NORECOVERY
B. FULL
C. NO_CHECKSUM
D. CHECKSUM
E. Differential
F. BULK_LOGGED
G. STANDBY
H. RESTART
I. SKIP
J. Transaction log
K. DBO ONLY
L. COPY_ONLY
M. SIMPLE
N. CONTINUE AFTER ERROR
Correct Answer: J
Minimally, you must have created at least one full backup before you can create any log backups. After that, the
transaction log can be backed up at any time unless the log is already being backed up. References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup- restore/transaction-log-backups-sql-server
QUESTION 20
You administer a Microsoft SQL Server 2014 instance that has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail.
The following error message is an example error message:
“The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1
(Subplan_1).”
You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained.
What should you do?
A. Configure output files.
B. Expand agent logging to include information from all events.
C. Disable the Limit size of job history log feature.
D. Configure event forwarding.
Correct Answer: B
References: http://msdn.microsoft.com/en-us/library/ms175488.aspx
QUESTION 21
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 account. Configure the Subscriber to use the
SQL Server Agent service account.
C. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use
the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share
on ProdSrv1.
D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the
ReplData share on ProdSrv1.
Correct Answer: D
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
QUESTION 22
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.
Correct Answer: BC
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.
QUESTION 23
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
Correct Answer: F
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-parallel-data-warehouse
QUESTION 24
You are building a new Always On Availability Group in Microsoft Azure. The corporate domain controllers (DCs) are
attached to a virtual network named ProductionNetwork. The DCs are part of an availability set named
ProductionServers1.
You create the first node of the availability group and add it to an availability set named ProductionServers2. The
availability group node is a virtual machine (VM) that runs Microsoft SQL Server. You attach the node to
ProductionNetwork.
The servers in the availability group must be directly accessible only by other company VMs in Azure.
You need to configure the second SQL Server VM for the availability group.
How should you configure the VM? To answer, drag the appropriate configuration settings to the correct target
locations. Each configuration setting may be used once, more than once, or not at all. You may need to drag the split
bar between
panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:
Correct Answer:
Box 1: ProductionNetwork
Correct Answer:Box 1: ProductionNetwork The virtual network is named ProductionNetwork.
Box 2: None /Not Assigned
As the servers in the availability group must be directly accessible only by other company VMs in Azure, there should be
no Public IP address.
Box 3: ProductionServer2
You create the first node of the availability group and add it to an availability set named ProductionServers2. The
availability group node is a virtual machine (VM) that runs Microsoft SQL Server.
QUESTION 25
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 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
Correct Answer: G
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-parallel-data-warehouse
QUESTION 26
You manage an on-premises Microsoft SQL server that has a database named DB1.
An application named App1 retrieves customer information for DB1.
Users report that App1 takes an unacceptably long time to retrieve customer records.
You need to find queries that take longer than 400 ms to run.
Which statement should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Total_worker_time: Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was
consumed by executions of this plan since it was compiled.
QUESTION 27
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
Correct Answer: A
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.
QUESTION 28
You administer a Microsoft SQL Server 2014 database.
The database contains a Product table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should
you do?
A. Convert all indexes to Column Store indexes.
B. Implement Unicode Compression.
C. Implement row-level compression.
D. Implement page-level compression.
Correct Answer: D
QUESTION 29
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?
A. New-AzureRmAvailabilitySet
B. New-AzureRmLoadBalancer
C. New-AzureRmSqlDatabaseSecondary
D. New-AzureRmSqlElasticPool
E. New-AzureRmVM
F. New-AzureRmSqlServer
G. New-AzureRmSqlDatabaseCopy
H. New-AzureRmSqlServerCommunicationLink
Correct Answer: B
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-alwayson-int-listener
QUESTION 30
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?
A. Configure the SQL Server service account to use Network Service.
B. Use a server certificate.
C. Use a database certificate.
D. Configure the SQL Server service account to use Local System.
Correct Answer: B
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-mirroring-
endpoint-transact-sql
Share 30 of the latest and effective 70-765 exam dumps and Practice test questions for free, 100% real and effective exam questions and answers! Get the full 70-765 dumps: https://www.leads4pass.com/70-765.html
(Total questions:208 Q&A)
[PDF] Free Microsoft MCSA 70-765 pdf dumps download from Google Drive: https://drive.google.com/open?id=1T53F4Z36tsS41UDYoNog_fVOP3ueIPk6
[PDF] Free Full Microsoft pdf dumps download from Google Drive: https://drive.google.com/open?id=1AwBFPqkvdpJBfxdZ3nGjtkHQZYdBsRVz
leads4pass Promo Code 12% Off
related: https://www.certificatedumps.com/hot-microsoft-70-331-dumps-real-exam/