2017 October Microsoft Official New Released 70-765 Dumps in Lead2pass.com!
100% Free Download! 100% Pass Guaranteed!
You can prepare for Microsoft 70-765 exam with little effort because Lead2pass is now at your service to act as a guide to pass Microsoft 70-765 exam. Our Microsoft 70-765 braindumps are rich in variety. We offer Microsoft 70-765 PDF dumps and Microsoft 70-765 VCE. Both are the newest version.
Following questions and answers are all new published by Microsoft Official Exam Center: https://www.lead2pass.com/70-765.html
QUESTION 76
You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyPassword1!’
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = ‘TDE Certificate’;
BACKUP CERTIFICATE TDE_Certificate TO FILE = ”d:\TDE_Certificate.cer’ WITH PRIVATE KEY (FILE = ‘D:\TDE_Certificate.key’,
ENCRYPTION BY PASSWORD = ‘MyPassword1!’);
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails.
You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
A. ALTER DATABASE Master SET ENCRYPTION OFF;
B. CREATE CERTIFICATE TDE_Certificate FROM FILE = ‘d:\TDE_Certificate.cer’ WITH PRIVATE KEY (FILE = ‘D:\TDE_Certificate.key’,
DECRYPTION BY PASSWORD = ‘MyPassword1!’);
C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = ‘TDE Certificate’; USE Orders; CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
D. CREATE CERTIFICATE TDE_Certificate FROM FILE = ‘d:\TDE_Certificate.cer’;
Answer: B
Explanation:
The CREATE CERTIFICATE command adds a certificate to a database in SQL Server.
Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files.
Code
Copy
USE AdventureWorks2012;
CREATE CERTIFICATE Shipping11
FROM FILE = ‘c:\Shipping\Certs\Shipping11.cer’
WITH PRIVATE KEY (FILE = ‘c:\Shipping\Certs\Shipping11.pvk’, DECRYPTION BY PASSWORD = ‘sldkflk34et6gs%53#v00’);
GO
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql
QUESTION 77
You administer a SQL Server 2012 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).
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse
QUESTION 78
You administer a SQL Server 2012 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
Answer: G
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).
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse
QUESTION 79
You administer a SQL 2012 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 remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
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: E
Explanation:
Use REVOKE to remove the grant or deny of a permission.
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse
QUESTION 80
You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction.
You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index.
You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process.
Which Transact-SQL batch should you use?
A. ALTER INDEX IDX_AccountTransaction_AccountCode ON
AccountTransaction.AccountCode REORGANIZE
B. ALTER INDEX ALL ON AccountTransaction REBUILD
C. ALTER INDEX IDX_AccountTransaction_AccountCode ON
AccountTransaction.AccountCode REBUILD
D. CREATE INDEX IDXAccountTransactionAccountCode ON
AccountTransaction.AccountCode WITH DROP EXISTING
Answer: A
Explanation: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).
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize
QUESTION 81
You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 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
Answer: C
Explanation:
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).
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/
QUESTION 82
You administer a Windows Azure SQL Database database named Orders.
You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
A. BACKUP DATABASE Orders TO DISK = ‘D:\Orders.bak’RESTORE DATABASE Orders_ReportingFROM DISK = ‘D:\Orders.bak
B. BACKUP DATABASE Orders TO DISK = ‘D:\Orders.bak’CREATE DATABASE Orders_ReportingFROM DISK = ‘D:\Orders.bak
C. CREATE DATABASE Orders_Reporting AS COPY OF Orders
D. BACKUP DATABASE Orders TO DISK = ‘D:\Orders.bak’MIRROR TO DISK = ‘Orders_Reporting
Answer: C
Explanation:
BACKUP DATABASE …AS COPY OF [source_server_name.]source_database_name Is used for copying a database to the same or a different SQL Database server.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database
QUESTION 83
You administer a Microsoft SQL Server 2012 failover cluster.
You need to ensure that a failover occurs when the server diagnostics returns query_processing error.
Which server configuration property should you set?
A. SqlOumperDumpFlags
B. FailureConditionLevel
C. HealthCheckTimeout
D. SqlDumperDumpPath
Answer: B
Explanation:
Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart.
The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions.
Note: The system stored procedure sp_server_diagnostics periodically collects component diagnostics on the SQL instance. The diagnostic information that is collected is surfaced as a row for each of the following components and passed to the calling thread.
1. system
2. resource
3. query process
4. io_subsystem
5. events
The system, resource, and query process components are used for failure detection.
The io_subsytem and events components are used for diagnostic purposes only.
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/configure-failureconditionlevel-property-settings
QUESTION 84
You are the administrator of a Microsoft SQL Server 2012 server.
Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications.
You need to dynamically limit resource consumption.
What should you do?
A. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor
B. Set up Service Broker to ensure that application are not allowed to consume more than the specified amount of resource
C. Create a new rule for each application that sets the resource limit allowed
D. Create a new plan Guide with a Scope Type of sql and define the resource limits for each application
Answer: A
Explanation:
In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool
QUESTION 85
You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECT from all tables in the database and execute the stored procedure.
You need to grant only the necessary permissions.
What should you do?
A. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
C. Add all users to the db_owner role.
D. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datareader role.
Answer: D
Explanation:
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx
QUESTION 86
You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster.
Which user account should you use?
A. A domain user
B. The BUILTIN\SYSTEM account
C. A local user with Run as Service permissions
D. The SQLBrowser account
Answer: A
Explanation:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-setup
QUESTION 87
You administer a Windows Azure SQL Database database named Human_Resources.
The database contains 2 tables named Employees and SalaryDetails.
You add two Windows groups as logins for the server:
CORP\Employees – All company employees
CORP\HRAdmins – HR administrators only
HR Administrators are also company employees.
You need to grant users access according to the following requirements:
CORP\Employees should have SELECT access to the Employees table.
Only users in CORP
\HRAdmins should have SELECT access to the SalaryDetails table.
Logins are based only on Windows security groups.
What should you do?
A. Create a database role called Employees.
Add CORP\Employees to the db_datareader role.
Add all company employees except HR administrators to the Employees role.
Deny SELECT access to the SalaryDetails table to the Employees role.
B. Create a database role called HRAdmins.
Add all company employees except HR administrators to the db_datareader role.
Add all HR administrators to the HRAdmins role.
Grant SELECT access to the SalaryDetails table to the HRAdmins role.
Deny SELECT access to the SalaryDetails table to the db_datareader role.
C. Create two database roles: Employees and HRAdmins.
Add all company employees to the Employees role.
Add HR administrators to the HRAdmins role.
Grant SELECT access to all tables except SalaryDetails to the Employees role.
Grant SELECT access to the SalaryDetails table to the HRAdmins role.
Deny SELECT access to the SalaryDetails table to the Employees role.
D. Create a database role called Employees.
Add all HR administrators to the db_datareader role.
Add all company employees to the Employees role.
Grant SELECT access to all tables except the SalaryDetails table to the Employees role.
Deny SELECT access to the SalaryDetails table to the Employees role.
Answer: D
Explanation:
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx
QUESTION 88
You administer a Microsoft SQL Server 2012 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
Answer: C
Explanation:
You would need to use either the SQL Server Management Studio or Transact-SQL.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate
QUESTION 89
You administer a Microsoft SQL Server 2012 database named Orders.
Orders contains a table named OrderShip that is defined as follows:
A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.
Customers require a procedure that will return orders for all customers from a specified country.
You create a new procedure:
Performance on this procedure is slow.
You need to alter the schema to optimize this query.
Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?
A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL
C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL
Answer: B
Explanation:
Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql
QUESTION 90
You administer a Microsoft SQL Server 2012 database.
The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online.
You want to perform a tail-log backup.
You need to leave the primary database in a restoring state.
Which option of the BACKUP LOG command should you use?
A. NO_TRUNCATE
B. NORECOVERY
C. STANDBY
D. FORMAT
Answer: B
Explanation:
It is recommended that you take a tail-log backup in the following scenarios:
* If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.
Note: A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server
Microsoft Certification 70-765 certificate are those engaged in IT industry’s dream. You need to choose the professional training by Lead2pass Microsoft 70-765 dumps. Lead2pass will be with you, and to ensure the success wherever you may increase pursuit your career. Let Lead2pass take all your heart, let the dream to reality!
More Microsoft 70-765 new questions (with images) on Google Drive: https://drive.google.com/open?id=0B3Syig5i8gpDejczeWp0aURaSnM
2017 Microsoft 70-765 exam dumps (All 115 Q&As) from Lead2pass:
https://www.lead2pass.com/70-765.html [100% Exam Pass Guaranteed]