70-462 Exam
70 462 pdf [Jun 2021]
Free of 70 462 exam dumps free practice test materials and questions pool for Microsoft certification for candidates, Real Success Guaranteed with Updated microsoft 70 462 pdf dumps vce Materials. 100% PASS Administering Microsoft SQL Server 2012 Databases exam Today!
Q61. You administer two Microsoft SQL Server 2012 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.
Answer: B
Q62. You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size.
The media files will require fast access and will be retrieved frequently.
What should you do?
A. Use the CAST function.
B. Use the DATE data type.
C. Use the FORMAT function.
D. Use an appropriate collation.
E. Use a user-defined table type.
F. Use the VARBINARY data type.
G. Use the DATETIME data type.
H. Use the DATETIME2 data type.
I. Use the DATETIMEOFFSET data type.
J. Use the TODATETIMEOFFSET function.
Answer: F
Q63. 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. TheSQLBrowser account
Answer: A
Q64. You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schemA.
You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers.
Which Transact-SQL statement should you use?
A. GREATE ROLE proc_caller GRANT EXECUTE ON Schema : : dbo TO proc_caller ALTER ROLE proc_caller ADD MEMBER BillingUsers
B. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers
C. EXEC sp_addrolemember 'executor' , 'BillingUsers'
D. GREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller
Answer: D
Q65. You administer a Microsoft SQL Server 2012 database that includes a table named Application.Events. Application.Events contains millions of records about user activity in an application.
Records in Application.Events that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.
You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.
Which type of solution should you use?
A. Partitioned tables
B. Online index rebuild
C. Change data capture
D. Change tracking
Answer: A
Q66. You administer a Microsoft SQL Server 2012 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.
Answer: CD
Q67. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal.
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?
A. HOLDLOCK
B. ROWLOCK
C. XLOCK
D. UPDLOCK
E. TABLOCK
Answer: E
Q68. You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance.
You need to ensure that the application can connect to the named instance.
What should you do?
A. Use the Data Quality Client to configure the application.
B. Start the SQL Server Browser Service.
C. Use the Master Data Services Configuration Manager to configure the application.
D. Start the SQL Server Integration Services Service.
Answer: B
Q69. 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
Q70. 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 and 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 the Regions table.
Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. REVOKE SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember 'Sales', 'UserA'
D. DENY SELECT ON Schema::Customers FROM Sales
E. EXEC sp_droprolemember 'Sales', 'UserA'
F. REVOKE SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM UserA
J. DENY SELECT ON Object::Regions FROM Sales
Answer: J