最新的Microsoft Administering Microsoft SQL Server 2012/2014 Databases - 70-462免費考試真題
問題1
You are planning to install a Microsoft SQL Server Database Engine instance. The instance will be used to support a high-volume OLTP database application The server has the following logical disk configuration Drive C is used as the system drive:
* Drive C: RAID 1
* Drive D: RAID 10
* Drive E: RAID 5
You need to ensure that the default data folders for the Database Engine are configured for optimal performance. Which three actions should you perform? Each correct answer presents pan of the solution.
You are planning to install a Microsoft SQL Server Database Engine instance. The instance will be used to support a high-volume OLTP database application The server has the following logical disk configuration Drive C is used as the system drive:
* Drive C: RAID 1
* Drive D: RAID 10
* Drive E: RAID 5
You need to ensure that the default data folders for the Database Engine are configured for optimal performance. Which three actions should you perform? Each correct answer presents pan of the solution.
正確答案: E,F
問題2
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
正確答案: D
說明：（僅 PDFExamDumps 成員可見）
問題3
You administer a Microsoft SQL Server failover cluster that contains two nodes named Node A and Node B.
A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster.
What should you do?
You administer a Microsoft SQL Server failover cluster that contains two nodes named Node A and Node B.
A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster.
What should you do?
正確答案: A
說明：（僅 PDFExamDumps 成員可見）
問題4
You administer a Microsoft SQL Server database.
The database is backed up according to the following schedule:
* Daily full backup at 23:00 hours.
* Differential backups on the hour, except at 23:00 hours.
* Log backups every 10 minutes from the hour, except on the hour.
The database uses the Full recovery model.
A developer accidentally drops a number of tables and stored procedures from the database between 22:40 hours and 23:10 hours. You perform a database restore at 23:30 hours to recover the dropped table.
You need to restore the database by using the minimum amount of administrative effort.
You also need to ensure minimal data loss.
Which three 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.)
You administer a Microsoft SQL Server database.
The database is backed up according to the following schedule:
* Daily full backup at 23:00 hours.
* Differential backups on the hour, except at 23:00 hours.
* Log backups every 10 minutes from the hour, except on the hour.
The database uses the Full recovery model.
A developer accidentally drops a number of tables and stored procedures from the database between 22:40 hours and 23:10 hours. You perform a database restore at 23:30 hours to recover the dropped table.
You need to restore the database by using the minimum amount of administrative effort.
You also need to ensure minimal data loss.
Which three 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.)
正確答案:
Explanation
問題5
You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.
You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.
You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.
Which Transact-SQL statement should you use?
You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.
You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.
You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.
Which Transact-SQL statement should you use?
正確答案: D
說明：（僅 PDFExamDumps 成員可見）
問題6
You are implementing the indexing strategy for a fact table in a data warehouse.
The fact table is named Quotes.
The table has no indexes and consists of seven columns:
* [ID]
* [QuoteDate]
* [Open]
* [Close]
* [High]
* [Low]
* [Volume]
Each of the following queries must be able to use a columnstore index:
* SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
* SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
* SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
You need to ensure that the indexing strategy meets the requirements.
The strategy must also minimize the number and size of the indexes.
What should you do?
You are implementing the indexing strategy for a fact table in a data warehouse.
The fact table is named Quotes.
The table has no indexes and consists of seven columns:
* [ID]
* [QuoteDate]
* [Open]
* [Close]
* [High]
* [Low]
* [Volume]
Each of the following queries must be able to use a columnstore index:
* SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
* SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
* SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
You need to ensure that the indexing strategy meets the requirements.
The strategy must also minimize the number and size of the indexes.
What should you do?
正確答案: A
說明：（僅 PDFExamDumps 成員可見）
問題7
You administer a Microsoft SQL Server 2012 database.
The database uses SQL Server Agent jobs to perform regular FULL and LOG backups. The database uses the FULL recovery model.
You plan to perform a bulk import of a very large text file.
You need to ensure that the following requirements are met during the bulk operation:
* The database transaction log is minimally affected.
* The database is online and all user transactions are recoverable.
* All transactions are fully recoverable prior to the import.
Which three 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.)
You administer a Microsoft SQL Server 2012 database.
The database uses SQL Server Agent jobs to perform regular FULL and LOG backups. The database uses the FULL recovery model.
You plan to perform a bulk import of a very large text file.
You need to ensure that the following requirements are met during the bulk operation:
* The database transaction log is minimally affected.
* The database is online and all user transactions are recoverable.
* All transactions are fully recoverable prior to the import.
Which three 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.)
正確答案:
Explanation
問題8
You administer a database named SalesDb that has users named UserA, UserB, and UserC.
You need to ensure that the following requirements are met:
* UserA must be able to provide Windows logins access to the database.
* UserB must be able to select, update, delete, and insert data to the database tablets.
* UserC must be able to create new tables and stored procedures.
You need to achieve this goal by granting only the minimum permissions required.
To which fixed database role or roles should you add the users? (To answer, drag the appropriate user or users to the correct database role or roles. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
You administer a database named SalesDb that has users named UserA, UserB, and UserC.
You need to ensure that the following requirements are met:
* UserA must be able to provide Windows logins access to the database.
* UserB must be able to select, update, delete, and insert data to the database tablets.
* UserC must be able to create new tables and stored procedures.
You need to achieve this goal by granting only the minimum permissions required.
To which fixed database role or roles should you add the users? (To answer, drag the appropriate user or users to the correct database role or roles. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
正確答案:
Explanation
UserA: db_accessadmin
Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins.
UserB: db_datareader, db_datawriter
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
UserC: db_ddladmin
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
問題9
You administer a Microsoft SQL Server database.
You want to import data from a text file to the database.
You need to ensure that the following requirements are met:
* Data import is performed from a Windows batch file.
* Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all.
Additionally, you may need to drag the split bar between panes or scroll to view content.)
You administer a Microsoft SQL Server database.
You want to import data from a text file to the database.
You need to ensure that the following requirements are met:
* Data import is performed from a Windows batch file.
* Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all.
Additionally, you may need to drag the split bar between panes or scroll to view content.)
正確答案:
Explanation
問題10
You administer a Microsoft SQL Server database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account.
What should you use?
You administer a Microsoft SQL Server database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account.
What should you use?
正確答案: D
問題11
You administer a Microsoft SQL Server database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.
What should you create?
You administer a Microsoft SQL Server database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.
What should you create?
正確答案: E
問題12
You administer a Microsoft SQL Server database called Human_Resources. The database contains a table named dbo.Salary.
You need to ensure that all read activity against dbo.Salary is audited and written to the Windows Security Log.
What should you do? (To answer, move the three appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
You administer a Microsoft SQL Server database called Human_Resources. The database contains a table named dbo.Salary.
You need to ensure that all read activity against dbo.Salary is audited and written to the Windows Security Log.
What should you do? (To answer, move the three appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
正確答案:
Explanation
Box 1:
Box 2:
Box 3:
Note:
Example (step 2) (we should audit SELECT on the Salary table):
USE AdventureWorks2012 ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT, INSERT
ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON);
GO
References:
http://msdn.microsoft.com/en-us/library/cc280386.aspx
http://msdn.microsoft.com/en-us/library/cc280448.aspx
http://msdn.microsoft.com/en-us/library/cc280404.aspx
問題13
You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup.
Which backup option should you use?
You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup.
Which backup option should you use?
正確答案: D
問題14
You administer a Microsoft SQL Server database server that has a database named Contoso. The Contoso database has a table named EmployeeSalary in a schema named HumanResources.
You need to create a script that writes audit events into the application log whenever data in the EmployeeSalary table is modified by the public principal.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
You administer a Microsoft SQL Server database server that has a database named Contoso. The Contoso database has a table named EmployeeSalary in a schema named HumanResources.
You need to create a script that writes audit events into the application log whenever data in the EmployeeSalary table is modified by the public principal.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
正確答案:
Explanation
References:
http://msdn.microsoft.com/en-us/library/cc280386.aspx
http://msdn.microsoft.com/en-us/library/cc280448.aspx
http://msdn.microsoft.com/en-us/library/cc280404.aspx