Microsoft Administering Microsoft SQL Server 2012/2014 Databases (070-462) Free Practice Test
Question 1
You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance.
You need to ensure that users can create subscriptions.
What should you configure?
You need to ensure that users can create subscriptions.
What should you configure?
Correct Answer: B
Question 2
You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows.
You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:
* Does not return an error
* Closes all opened transactions
Which Transact-SQL statement should you use?
You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:
* Does not return an error
* Closes all opened transactions
Which Transact-SQL statement should you use?
Correct Answer: A
Question 3
Which of the following is the default file extension for an SQL Server 2012 backup file?
Correct Answer: D
Question 4
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 by using a stored procedure.
* 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. Each data import command or recovery model 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.)
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 by using a stored procedure.
* 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. Each data import command or recovery model 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.)
Correct Answer:
Explanation
Question 5
You administer a Microsoft SQL Server database 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?
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?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 6
You have a SQL Server database named DB1.
You plan to load 10 million rows to DB1 by running bcp.exe.
You need to modify DB1 to minimize the amount of space required to store the transaction logs during the load operation. The solution must ensure that you can perform a point-in-time restore.
What data base setting should you modify?
You plan to load 10 million rows to DB1 by running bcp.exe.
You need to modify DB1 to minimize the amount of space required to store the transaction logs during the load operation. The solution must ensure that you can perform a point-in-time restore.
What data base setting should you modify?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 7
You have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes.
You install another server that has SQL Server installed.
Which three additional 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 plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes.
You install another server that has SQL Server installed.
Which three additional 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.
Correct Answer:
Explanation
Step 1: Add a new availability replica in asynchronous mode.
Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Incorrect: Add a new availability replica in synchronous mode.
Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Step 2: Set Readable Secondary to Read-intent only.
For the secondary role, select a new value from the Readable secondary drop list, as follows: Read-intent only Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
Step 3: Configure read-only routing.
Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." That is, they must be read-intent connection requests.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-f
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availa
Question 8
You have a SQL Server database server that contains a database named CustomerDB. CustomerDB is protected by using transparent data encryption (TDE) and a certificate named TDE_Cert.
The server fails.
You deploy a new server and restore all of the backups to a folder named C:\backups.
You need to restore the database to the new server.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.
The server fails.
You deploy a new server and restore all of the backups to a folder named C:\backups.
You need to restore the database to the new server.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.
Correct Answer:
Explanation
In order to perform a successful restore, we'll need the database master key in the master database in place and we'll need to restore the certificate used to encrypt the database, but we'll need to make sure we restore it with the private key. In checklist form:
There's a database master key in the master database.
The certificate used to encrypt the database is restored along with its private key.
The database is restored.
Step 1: CREATE MASTER KEY ENCRYPTION BY PASSWORD- 'My Password'
Step 2: Create CERTIFICATE
FROM FILE
DECRYPTION BY PASSWORD
Step 3: RESTORE DATABSE ..
References:
Question 9
You administer a Microsoft SQL Server instance that contains a database of confidential data.
You need to encrypt the database files at the page level. You also need to encrypt the transaction log files.
Which four 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 need to encrypt the database files at the page level. You also need to encrypt the transaction log files.
Which four 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.)
Correct Answer:
Explanation
References:
http://msdn.microsoft.com/en-us/library/bb510663.aspx
http://msdn.microsoft.com/en-us/library/bb934049.aspx
Question 10
You administer a Microsoft SQL Server database.
You need to convert the database to a contained database. You also need to ensure that all users are converted to contained users.
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 need to convert the database to a contained database. You also need to ensure that all users are converted to contained users.
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.)
Correct Answer:
Explanation
References:
Question 11
You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database
To which role or roles should you add to UserC? Select all that apply.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database
To which role or roles should you add to UserC? Select all that apply.
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 12
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 has 32 processor cores and 64 GB of RAM.
SQL1 hosts a database used for a public-facing marketing website. SQL1 performs Online Transaction Processing (OLTP) operations only.
Several of the queries that run on SQL1 use the 32 processor cores and complete in 30 ms.
You need to reduce the number of queries that use multiple cores. The solution must also reduce the number of processor cores used by the queries that require multiple processor cores.
Which two database settings should you modify? To answer, select the appropriate settings in the answer area.
SQL1 hosts a database used for a public-facing marketing website. SQL1 performs Online Transaction Processing (OLTP) operations only.
Several of the queries that run on SQL1 use the 32 processor cores and complete in 30 ms.
You need to reduce the number of queries that use multiple cores. The solution must also reduce the number of processor cores used by the queries that require multiple processor cores.
Which two database settings should you modify? To answer, select the appropriate settings in the answer area.
Correct Answer:
Explanation
Cost Threshold for Parallelism
The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time.
The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.
Max Degree of Parallelism
When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parall
Question 13
You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1 and Node
2.
Node 1 fails and the cluster fails over to Node 2.
You need to replace Node 1 and add it to the cluster.
Which four 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.)
2.
Node 1 fails and the cluster fails over to Node 2.
You need to replace Node 1 and add it to the cluster.
Which four 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.)
Correct Answer:
Explanation
References: