Most Recent Administering Microsoft SQL Server 2012 Databases 70-462 Prep

NEW QUESTION 1

You have the following table.

You need to create a full-text index for the table.
Which two statements should you execute? Each correct answer presents part of the solution.

• A. CREATE RULE KnowledgeBaseRuleAS @Problem LIKE ‘%’
• B. CREATEFULLTEXT STOPLIST KnowledgeBaseStopListFROM KnowledgeBase
• C. CREATE INDEX OurIndex ON KnowledgeBase(Problem, Solution) ON [Catalog]
• D. CREATEFULLTEXT CATALOG [Catalog] WITH ACCENT_SENSITIVITY = ONAUTHORIZATION [dbo]
• E. CREATEFULLTEXT INDEX ON KnowledgeBase(Problem, Solution) KEY INDEX PK_KnowledgeBase ON OurKB

Answer: DE

Explanation:
The CREATEFULLTEXT INDEX ON table_name command creates a full-text index on a table or indexed view in a database in SQL Server.
Example: The following example creates a full-text catalog, production_catalog, in the AdventureWorks sample database. The example then creates a full-text index that uses this new catalog.
CREATEFULLTEXT CATALOG production_catalog; GO
CREATEFULLTEXT INDEX ON Production.ProductReview
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql

NEW QUESTION 2

Note: This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.
You administer a Microsoft SQL Server server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.
You also need to ensure that data loss is minimal. What should you do?

• A. Perform a partial restore.
• B. Restore the latest full backup, and restore the latest differential backu
• C. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• D. Restore the latest full backup.
• E. Restore the latest full backup, and restore the latest differential backu
• F. Then, restore the latest log backup.
• G. Perform a page restore.
• H. Restore the latest full backu
• I. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• J. Perform a point-in-time restore.
• K. Restore the latest full backu
• L. Then, restore the latest differential backup.

Answer: H

Explanation:
Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored.
Page restore
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. Piecemeal restore
Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored.

NEW QUESTION 3

You are implementing a SQL Server five-node failover cluster. You need to choose a quorum configuration.
Which configuration should you use?

• A. Distributed file system (DFS)
• B. Cluster Shared Volume (CSV)
• C. Node and Disk Majority
• D. Node Majority

Answer: C

NEW QUESTION 4

You administer a Microsoft SQL Server database that is used by an application. Users of the application report performance issues.
You need to choose the appropriate tool for performance-tuning of SQL Server databases.
Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their corresponding task or tasks in the answer area. Each tool 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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
References:
http://msdn.microsoft.com/en-us/library/bb630282.aspx http://msdn.microsoft.com/en-us/library/ms191246.aspx http://msdn.microsoft.com/en-us/library/ms181091.aspx

NEW QUESTION 5

You have a server named SQL1 mat has SQL Server installed SQU hosts several applications. You plan to deploy a third-party application to SQU. The application must have the following:
*A vendor-provided password for the sa account
*A vendor-provided user account that is a member of the sysadmin fixed server role
*A vendor-provided user account that is a member of the db.owner fixed database role in the msdb
database
*A vendor-provided user account that is a member of the db.owner fixed database role in the master database
*A vendor-provided user account that is a member of the SQLAgentOperatorRole fixed database role m the msdb
Database
You need to identify the minimum number of SQL Server instances that you must add to SQL1 to support the application requirements. The solution must use the principle of least privilege
How many SQL Server instances should you add to SQL1?

• A. 1
• B. 2
• C. 3

Answer: D

NEW QUESTION 6

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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 7

You administer a Microsoft SQL Server database.
You use an OrderDetail table that has the following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the CarrierTrackingNumber column.
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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
According to these references, this answer looks correct. References:
http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms189280.aspx

NEW QUESTION 8

You administer a Microsoft SQL Server 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. ADatabase Audit Specification
• G. A Policy

Answer: C

NEW QUESTION 9

You administer a Microsoft SQL Server 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. SqlDumperDumpFlags
• B. FailureConditionLevel
• C. HealthCheckTimeout
• D. SqlDumperDumpPath

Answer: B

NEW QUESTION 10

You administer a single server that contains a Microsoft SQL Server 2012 default instance.
You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?

• A. Use the SQL Server default instance and configure an affinity mask.
• B. Install a new named SQL Server instance on the server.
• C. Use the SQL Server default instance and enable Contained Databases.
• D. Install a new default SQL Server instance on the server.

Answer: B

NEW QUESTION 11

You administer a Microsoft SQL Server instance.
You need to configure a new database to support FILETABLES.
Which three actions should you perform? Each correct answer presents part of the solution.

• 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.

Answer: BDF

NEW QUESTION 12

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.

• A. db_accessadmin
• B. db_ddladmin
• C. db_datawriter
• D. db_datareader
• E. db_owner

Answer: A

Explanation:
User A = db_ddladmin
User B= db_datawriter, db_datareader User C = db_accessadmin

NEW QUESTION 13

You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks.
You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA.
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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 14

You install Microsoft SQL Server 2012 on a new server.
After setup is complete, you attempt to start the SQL Server service. After being in a starting state for a few moments, the service goes back to a stopped state.
You need to determine the cause of the failure. Which file should you use?

• A. %programfiles%Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLogErrorlog
• B. %programfiles%Microsoft SQL Server110Setup BootstrapLogSummary.txt
• C. %programfiles%Microsoft SQL Server110SharedErrorDumpsSQLDmpr[XXXX].mdmp
• D. %programfiles%Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAmastlog.ldf

Answer: A

NEW QUESTION 15

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.
BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 16

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?

• A. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
• B. Configure the AgentPortalUser user as a member of SQLAgentOperationRole in the msdb database.
• C. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser
• D. Enable the Guest user in the msdb database.

Answer: C

Explanation:
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

NEW QUESTION 17

You develop a database for a travel application.
You need to design tables and other database objects. You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone information. 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: I

NEW QUESTION 18

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

NEW QUESTION 19

You administer a Microsoft SQL Server 2012 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.

Answer: D

Explanation:
References:
http://msdn.microsoft.com/en-us/library/cc280449.aspx http://msdn.microsoft.com/en-us/library/cc280464.aspx http://msdn.microsoft.com/en-us/library/cc280576.aspx http://msdn.microsoft.com/en-us/library/ee240835.aspx

NEW QUESTION 20

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.

• A. Mastered
• B. Not Mastered

Answer: A

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

NEW QUESTION 21

You administer a Microsoft SQL Server 2012 database instance. Other applications run on the server. Some of the applications are throwing errors because of insufficient memory.
You need to ensure that the other applications have sufficient memory. Which setting should you configure?

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 22

You administer two Microsoft SQL Server 2012 databases named Contoso and ContosoWarehouse. You plan to replicate tables from the Contoso database to the ContosoWarehouse database.
Indexes will be added to the tables in ContosoWarehouse to improve the performance of reports. You need to ensure that the following requirements are met:
✑ Indexes are not modified when the subscriptions are reinitialized.
✑ Only the clustered index from Contoso will be replicated.
✑ Minimal transaction log activity during the reinitialization.
What should you do? Choose the correct option(s).

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 23

You administer a Microsoft SQL Server instance.
The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. The database is configured to use the FULL recovery model.
You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
No more than 5 minutes worth of transactions are lost.
Data can be recovered by using the minimum amount of administrative effort.
What should you do? Each correct answer presents part of the solution.

• A. Create a LOG backup every 5 minutes.
• B. Configure the database to use the SIMPLE recovery model.
• C. Create a DIFFERENTIAL database backup every 4 hours.
• D. Create a DIFFERENTIAL database backup every 24 hours.
• E. Create a FULL database backup every 24 hours.

Answer: AE

NEW QUESTION 24

You are a database administrator for a Microsoft SQL Server instance.
You need to ensure that data can be migrated from a production server to two reporting servers with minimal data latency. You also need to ensure that data on the reporting server is always accessible.

• A. Database Snapshot
• B. Log Shipping
• C. Availability Groups
• D. Change Data Capture

Answer: C

NEW QUESTION 25

You administer a Microsoft SQL Server database instance. You create a new user named UserA.
You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server Agent jobs owned by UserA
To which role should you add UserA?

• A. RSExecRole
• B. SQLAgentUserRole
• C. serveradmin
• D. DatabaseMailUserRole

Answer: B

NEW QUESTION 26

You want to remove SQL Server Integration Services from a server running the Windows Server 2008 R2 operating system that also has the Database Engine and SQL Server Analysis Services installed.
Which of the following tools can you use to accomplish this goal?

• A. SQL Server Management Studio
• B. SQL Server Configuration Manager
• C. Add/Remove Programs in Control Panel
• D. SQL Server Installation Center

Answer: D

NEW QUESTION 27

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

• A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

NEW QUESTION 28

You administer a Microsoft SQL Server database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables.
You create a new user named UserC.
You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC.
Which two permission or permissions should you assign to UserC? Each correct answer presents part of the solution.

• A. The Select permission on Customers
• B. The Execute permission on GetCustomerOrderInfo
• C. The Take Ownership permission on Customers
• D. The Control permission on GetCustomerOrderInfo
• E. The Take Ownership permission on Orders
• F. The Select permission on Orders

Answer: AB

Explanation:
Execute permission on the Stored procedure is the minimal permission that is required. Select permission is not necessary.
References:
http://msdn.microsoft.com/en-us/library/ms188676.aspx http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas http://sqlservercentral.com/blogs/steve_jones/2012/03/14/ownership-chains-in-sql-server

NEW QUESTION 29

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?

• A. a schedule
• B. an alert
• C. an operator
• D. a proxy

Answer: D

NEW QUESTION 30
......