NEW QUESTION 1

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
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:
References:
http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx http://msdn.microsoft.com/en-us/library/ms187728.aspx

NEW QUESTION 2

You plan to install Microsoft SQL Server 2012 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?

• A. Express Edition with Tools
• B. Standard Edition
• C. Web Edition
• D. Express Edition with Advanced Services

Answer: B

NEW QUESTION 3

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements.
There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.
You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

• A. SERIALIZABLE
• B. SNAPSHOT
• C. READ COMMITTED SNAPSHOT
• D. REPEATABLE READ

Answer: C

NEW QUESTION 4

You administer three Microsoft SQL Server 2008 R2 instances.
Database mirroring is configured in High-Safety mode with Automatic Failover between the following three servers:
SQL1 is the Principal server.
SQL2 is the mirror server.
SQL3 is the witness server.
You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade.
Which six 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 5

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 6

You have an index called Employee_IDX on the table Employees. Which of the following will rebuild this index online?

• A. ALTER INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
• B. CREATE INDEX Employee_IDX ON Employees REBUILD WITH (ONLINE = ON);
• C. REBUILD ONLINE INDEX Employee_IDX ON Employees;
• D. ALTER INDEX REBUILD ONLINE Employee_IDX;

Answer: A

Explanation:
References:
https://msdn.microsoft.com/en-us/library/ms177442%28v=sql.110%29.aspx

NEW QUESTION 7

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_AccountCodeON AccountTransaction.AccountCode REORGANIZE
• B. ALTER INDEX ALL ON AccountTransaction REBUILD
• C. ALTER INDEX IDX_AccountTransaction_AccountCodeON AccountTransaction.AccountCode REBUILD
• D. CREATE INDEX IDXAccountTransactionAccountCodeON AccountTransaction.AccountCode WITH DROP EXISTING

Answer: A

NEW QUESTION 8

You administer a Microsoft SQL Server instance.
You discover that the SQL Agent Error Log file is rapidly growing in size.
You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute.
What should you do?

• A. Execute the sp_cycle_agent_errorlog stored procedure.
• B. Configure event forwarding.
• C. Enable the Auto Shrink option on the master database.
• D. Enable the Auto Shrink option on the msdb database.
• E. Disable the Include execution trace messages feature.

Answer: E

NEW QUESTION 9

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server database 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 the Regions table. Which Transact-SQL statement should you use?

• A. DENY SELECT ON Object::Regions FROM Sales
• B. DENY SELECT ON Schema::Customers FROM Sales
• C. REVOKE SELECT ON Object::Regions FROM Sales
• D. REVOKE SELECT ON Schema::Customers FROM Sales
• E. DENY SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM UserA
• G. REVOKE SELECT ON Object::Regions FROM UserA
• H. REVOKE SELECT ON Schema::Customers FROM UserA
• I. EXEC sp_addrolemember 'Sales', 'UserA'
• J. EXEC sp_droprolemember 'Sales', 'UserA'

Answer: C

NEW QUESTION 10

You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?

• A. Expression task
• B. Script task
• C. Execute SQL task
• D. Execute Process task
• E. Term Extraction transformation

Answer: A

Explanation:
References:
http://msdn.microsoft.com/en-us/library/hh213137.aspx

NEW QUESTION 11

You administer a Microsoft SQL Server 2012 instance. After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server. What should you do?

• A. Modify tempdb location in startup parameters.
• B. Start SQL Server in minimal configuration mode.
• C. Start SQL Server in single-user mode.
• D. Configure SQL Server to bypass Windows application logging.

Answer: B

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms186400.aspx http://msdn.microsoft.com/en-us/library/ms345408.aspx

NEW QUESTION 12

You administer a Microsoft SQL Server 2012 database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message. What should you do?

• A. Update all stored procedure to use a TRY…CATCH block.
• B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.
• C. Flag all stored procedures for recompilation by using sp_recompile.
• D. Execute sp_who.

Answer: A

NEW QUESTION 13

Users report very poor performance. You run the following query and find several wait types with high wait-time-ms values:

You need resolve the high wait times to improve the performance.
Which actions should you perform to attempt to resolve each wait type? To answer, drag the appropriate actions to the correct wait types. answer choices 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:

NEW QUESTION 14

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 15

You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several
production databases have been deployed.
You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions.
You need to ensure that the login for the ticketing application cannot access other production databases. What should you do?

• A. Use the SQL Server default instance and enable Contained Databases.
• B. Use the SQL Server default instance and configure a user-defined server rol
• C. Add the login for the ticketing application to this role.
• D. Install a new named SQL Server instance on the server.
• E. Install a new default SQL Server instance on the server.

Answer: C

NEW QUESTION 16

You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012 instances contains a database named Sales.
You plan to migrate Sales to Microsoft Azure SQL Database. To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set database containment to AZURE.
• B. Enable server property contained database authentication.
• C. Disable server property cross db ownership chaining.
• D. Set database containment to PARTIAL.
• E. Disable server property contained database authentication.
• F. Set database containment to FULL.

Answer: BD

NEW QUESTION 17

You administer a Microsoft SQL Server database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
Which two actions should you perform? Each correct answer presents part of the solution.

• A. Grant the VIEW SERVER STATE permission to User1.
• B. Move the stored procedure to the User1 schema.
• C. Modify the stored procedure to include the EXECUTE AS OWNER statemen
• D. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
• E. Grant the db_datareader role on the database to User1.
• F. Create a SQL Server login that has VIEW SERVER STATE permission
• G. Modify the stored procedure to include the EXECUTE AS (newlogin) statement.

Answer: CE

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms187861.aspx http://msdn.microsoft.com/en-us/library/ms191291.aspx

NEW QUESTION 18

You manage a server that has SQL Server installed and hosts databases for five applications. Each application has a different login to the SQL Server instance.
You need to create a TCP endpoint to be used only by a login named Application3. The solution must ensure that Application only uses the new endpoint.
Which three statements should you execute? Each correct answer presents part of the solution.

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F

Answer: CEF

NEW QUESTION 19

You administer a Microsoft SQL Server environment. You purchase a new server and plan to migrate your database from an existing server to the new server.
You want to evaluate the new server’s performance based on the workloads of the existing server.
To do so, you need to run the workloads captured from the existing server against the new server from multiple computers.
Which SQL Server tool should you use?

• A. Distributed Replay
• B. Data Tools
• C. Migration Assistant
• D. Upgrade Advisor

Answer: A

Explanation:
With Distributed Replay, you can replay a workload from multiple computers and better simulate a mission-critical workload.
References: https://technet.microsoft.com/en-us/library/ff878183(v=sql.110).aspx

NEW QUESTION 20

You administer all the deployments of Microsoft SQL Server 2012 in your company. A database contains a large product catalog that is updated periodically.
You need to be able to send the entire product catalog to all branch offices on a monthly basis. Which configuration should you use?

• A. Two servers configured in the same data centerA primary server configured to perform log-shipping every 10 minutesA backup server configured as a warm standby
• B. SQL Server that includes an application database configured to perform transactional replication
• C. Two servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability ModeOne server configured as an Active Secondary
• D. Two servers configured in a Windows Failover Cluster in the same data centerSQL Server configured as a clustered instance
• E. SQL Server that includes an application database configured to perform snapshot replication
• F. Two servers configured in different data centersSQL Server Availability Group configured in Synchronous-Commit Availability ModeOne server configured as an Active Secondary
• G. Two servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode
• H. Two servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode

Answer: E

NEW QUESTION 21

Which of the following is NOT a pre-defined server role in SQL Server 2012?

• A. securityadmin
• B. serveradmin
• C. sysdba
• D. dbcreator

Answer: C

Explanation:
References:
https://msdn.microsoft.com/en-us/library/ms188659%28v=sql.110%29.aspx

NEW QUESTION 22

In which of the following statements about audit information stored in an SQL Server 2012 environment is TRUE?

• A. It is located in a file specified in the File Path property of the Audit.
• B. It is located in the Audit table in the MSDB database.
• C. Each login has an audit record, which is viewable under Logins -> Audit.
• D. SQL 2012 does not store any audit information.

Answer: A

Explanation:
References:
https://msdn.microsoft.com/en-us/library/cc280525(v=sql.110).aspx

NEW QUESTION 23

You administer a Microsoft Azure SQL Database database used for data warehouse operations. The database contains a table named OrdersHistory, defined as follows:

A weekly ETL (extract-transform-load) runs a large INSERT statement to add data into the OrdersHistory table. The process is taking a long time to complete. You discover that the bulk of the process is performing non-clustered index updates on the OrdersHistory table.
You need to improve the performance of the ETL process faster. You need to meet the following requirements:
✑ Use minimal administrative effort.
✑ Avoid losing existing permissions on existing objects.
What should you do? (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:
Step 1: Run the following statement:ALTER INDEX ALL on OrdersHistory DISABLE. Step 2: Run the INSERT statement.
Step 3: Run the following statement:
ALTER INDEX ALL on OrdersHistory REBUILD
The ALTER INDEX ALL REBUILD statement rebuilds and enables all disabled indexes on the table. References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints

NEW QUESTION 24

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2.
You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?

• A. USE Database2
• B. EXECUTE AS OWNER
• C. USE Database1
• D. EXECUTE AS CALLER

Answer: B

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-callingstoredprocedure-o

NEW QUESTION 25

You administer a Microsoft SQL Server 2012 database.
The database contains a customer 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 customer table. What should you do?

• A. Implement row-level compression.
• B. Implement page-level compression.
• C. Convert all indexes to Column Store indexes.
• D. Implement Unicode compression.

Answer: B

NEW QUESTION 26

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

• A. STANDBY
• B. Differential
• C. FULL
• D. CHECKSUM
• E. BULK_LOGGED
• F. CONTINUE_AFTER_ERROR
• G. SIMPLE
• H. DBO_ONLY
• I. COPY_ONLY
• J. SKIP
• K. RESTART
• L. Transaction log
• M. NO_CHECKSUM
• N. NORECOVERY

Answer: F

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.continueaftere

NEW QUESTION 27

Which of the following is NOT a method of failover for an SQL Server 2012 mirror?

• A. Automatic Failover
• B. Manual Failover
• C. Forced Failover
• D. Intermediate Failover

Answer: D

NEW QUESTION 28
......