70-462 239 Q&S Administering Microsoft SQL Server 2012 Databases

Which of the following DBCC commands shows optimizer statistics for objects such as tables?

Which of the following DBCC commands shows optimizer statistics for objects such as tables?
A. STATS
B. SHOW_STATISTICS
C. OBJECT_STATS
D. OPTIMIZER


Answer Clike
Which of the following keywords must you specify if you intend to roll forward a transaction log backup after restoring a database?

If you are restoring and recovering a database, which of the following keywords must you specify if you intend to roll forward a transaction log backup after restoring a database?
A. NOFINISH
B. NORECOVERY
C. TRANSACTIONLOG RECOVER
D. NEXTLOG


Answer Clike
Which of the following Microsoft SQL Server 2012 components is responsible for executing scheduled administrative tasks?

Which of the following Microsoft SQL Server 2012 components is responsible for executing scheduled administrative tasks?
A. SQL Server Instance
B. SQL Server Jobs File
C. SQL Server Agent
D. SQL Server SSIS


Answer Clike
Which of the following is NOT a hardware requirement for SQL Server 2012?

Which of the following is NOT a hardware requirement for SQL Server 2012?
A. 6GB of hard drive space
B. 1GB of memory
C. 1GHZ CPU speed
D. a graphics card with 2GB video memory


Answer Clike
Which of the following SQL Server 2012 tools coordinates transactions across SQL Servers in a clustered environment?

Which of the following SQL Server 2012 tools coordinates transactions across SQL Servers in a clustered environment?
A. SQL Server Profiler
B. SQL Server Query Optimizer
C. SQL Server Management Studio
D. Microsoft Distributed Transaction Coordinator


Answer Clike
Which of the following permissions must a user have in order to query all of the built-in Dynamic Management views?

Which of the following permissions must a user have in order to query all of the built-in Dynamic Management views?
A. VIEW ALL
B. SELECT ALL VEWS
C. VIEW SERVER INFO
D. VIEW SERVER STATE


Answer Clike
Which of the following is NOT a potential Cluster resource when configuring clustering in SQL Server 2012?

Which of the following is NOT a potential Cluster resource when configuring clustering in SQL Server 2012?
A. SQL Server Database Engine Service
B. SQL Server Agent Service
C. SQL Server Table Service
D. All of the above


Answer Clike
Which of the following recovery models must be in use for point-in-time recovery to be possible for an SQL Server 2012 database?

Which of the following recovery models must be in use for point-in-time recovery to be possible for an SQL Server 2012 database?
A. Differential
B. Full
C. Simple
D. ArchiveLog


Answer Clike
Which of the following is the default file extension for an SQL Server 2012 backup file?

Which of the following is the default file extension for an SQL Server 2012 backup file?
A. .rman
B. .bac
C. .bak
D. .db


Answer Clike
Which of the following statements will create an index called “SalesDate” on the DATEOFSALE column on the SALES table residing in the WAREHOUSE database?

Which of the following statements will create an index called “SalesDate” on the DATEOFSALE column on the SALES table residing in the WAREHOUSE database?
A. CREATE INDEX SalesDate ON SALES (DATEOFSALE);
B. MAKE INDEX SalesDate ON SALES (DATEOFSALE);
C. CREATE INDEX ON SALES (DATEOFSALE);
D. CREATE INDEX SalesDate ON SALES +DATEOFSALE;


Answer Clike
Which of the following SQL Server 2012 database is NOT created by default in a new installation?

Which of the following SQL Server 2012 database is NOT created by default in a new installation?
A. model
B. msdb
C. tempdb
D. sql
E. master


Answer Clike
Which of the following will kill that session?

If you find that session ID 42 is blocking several other sessions, which of the following will kill that session?
A. REMOVE 42;
B. TERMINATE 42;
C. KILL SESSION 42;
D. KILL 42;


Answer Clike
Which of the following is a valid upgrade path for SQL Server 2012?

Which of the following is a valid upgrade path for SQL Server 2012?
A. SQL Server 2000 -> SQL Server 2012
B. SQL Server 2000 -> SQL Server 2005 -> SQL Server 2012
C. SQL Server 7 -> SQL Server 2012
D. Oracle 11g -> SQL Server 2012


Answer Clike
Which of the following is NOT a valid recovery model for an SQL 2012 database?

Which of the following is NOT a valid recovery model for an SQL 2012 database?
A. ArchiveLog
B. Simple
C. Bulk-logged
D. Full


Answer Clike
Which of the following is NOT a database role that you can assign users in SQL Server 2012?

Which of the following is NOT a database role that you can assign users in SQL Server 2012?
A. db_ddladmin
B. db_owner
C. db_datawriter
D. db_databaseadmin


Answer Clike
Which of the following file types associated with it?

Every SQL Server 2012 database must have a datafile and which of the following file types associated with it?
A. log file
B. index file
C. transaction log file
D. instance file


Answer Clike
Which of the following must first be accomplished?

Before moving an SQL Server 2012 database file to a new location, which of the following must first be accomplished?
A. The database must be set to OFFLINE.
B. The SQL Server Service must be stopped.
C. The database must be deleted.
D. The database must have the “shrink” operation run.


Answer Clike
Which of the following is a prerequisite for an SQL Server 2012 installation?

Which of the following is a prerequisite for an SQL Server 2012 installation?
A. .NET Framework 3.5 SP1
B. Visual Basic 6
C. Internet Explorer 10
D. SQL Server Integration Services


Answer Clike
Which of the following SQL Server 2012 features generates numeric values at a defined interval and order?

Which of the following SQL Server 2012 features generates numeric values at a defined interval and order?
A. sequence
B. number table
C. index
D. bigint


Answer Clike
Which of the following DBCC commands can you use to check the physical and logical integrity of an SQL Server 2012 database?

Which of the following DBCC commands can you use to check the physical and logical integrity of an SQL Server 2012 database?
A. CVU
B. VERIFYINTEGRITY
C. CHECKINTEGRITY
D. CHECKDB


Answer Clike
Which of the following will do this at the database level?

If you are required to move an SQL 2012 database from one server to another, which of the following will do this at the database level?
A. You just have to mirror the database.
B. Detach the database, move the database files, and attach the database on the new server.
C. Use the SQL Server Move Wizard to define the old and new server as well as the scheduled move time.
D. The database must be created on the new machine and then the data moved with Export/Import.


Answer Clike
Which of the following SQL Server 2012 indexes is ordered independently of the actual physical order of the table data on which the index is based?

Which of the following SQL Server 2012 indexes is ordered independently of the actual physical order of the table data on which the index is based?
A. CLUSTERED
B. BITMAP
C. NONCLUSTERED
D. IND_ORDERED


Answer Clike
Which of the following files keeps track of all transactions that occur in an SQL Server database?

Which of the following files keeps track of all transactions that occur in an SQL Server database?
A. Transaction Log
B. Redo Log
C. Archive Log
D. Data File


Answer Clike
Which of the following will show you a history of job errors that have occurred in jobs scheduled to run in an SQL Server 2012 installation?

Which of the following will show you a history of job errors that have occurred in jobs scheduled to run in an SQL Server 2012 installation?
A. Look at the sys.all_job_hist table.
B. In Management Studio, navigate to SQL Server Agent -> Error Logs and look at the log for the corresponding date.
C. Open the c:errorjob.log file and search for the particular job and the errors you wish to view.
D. You must use T-SQL to write a job-logging procedure to monitor jobs.


Answer Clike
Which of the following SQL Server 2012 tools assists DBAs in running and analyzing trace results?

Which of the following SQL Server 2012 tools assists DBAs in running and analyzing trace results?
A. SSIS
B. SQL Profiler
C. AlwaysOn
D. EXP


Answer Clike
Which of the following statements will set the maximum amount of system memory that is managed by the SQL Server Memory Manager to 4GB?

Which of the following statements will set the maximum amount of system memory that is managed by the SQL Server Memory Manager to 4GB?
A. sp_configure ‘max memory’, 4;
B. ALTER SYSTEM SET ‘max server memory’=4096;
C. sp_configure ‘max server memory’, 4;
D. sp_configure ‘max server memory’, 4096;


Answer Clike
Which of the following file systems is supported for an SQL Server 2012 installation?

Which of the following file systems is supported for an SQL Server 2012 installation?
A. EXT4
B. NTFS
C. FAT16
D. EXT3


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

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 Clike
Which of the following is a disaster recovery solution offered in SQL Server 2012 and involves maintaining a standby copy of a primary database?

Which of the following is a disaster recovery solution offered in SQL Server 2012 and involves maintaining a standby copy of a primary database?
A. AlwaysOn
B. Log Shipping
C. Clustering
D. DataGuard


Answer Clike
Which of the following is a high availability feature with SQL Server 2012?

Which of the following is a high availability feature with SQL Server 2012?
A. Clustering
B. AlwaysOn
C. Log Shipping
D. Active-Active


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

Which of the following is NOT a pre-defined server role in SQL Server 2012?
A. securityadmin
B. serveradmin
C. sysdba
D. dbcreator


Answer Clike
Which of the following is required in order for a differential backup to be useful for recovery purposes?

Which of the following is required in order for a differential backup to be useful for recovery purposes?
A. A full backup taken previous to the differential backup.
B. Hard drive space that is 2 times the size of the database you want to restore.
C. Less than 1000 database transactions an hour.
D. A full backup taken after the differential backup.


Answer Clike
Which of the following statements about the tempdb system database is FALSE?

Which of the following statements about the tempdb system database is FALSE?
A. It is a globally available resource for all connected users.
B. It can hold explicitly or implicitly created database objects.
C. It must be sized in accordance with the smallest user-database on the machine.
D. It is created every time the SQL Server instance is restarted.


Answer Clike
Which of the following will rebuild this index online?

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 Clike
Which of the following is a valid data source which you can import into SQL Server 2012?

Which of the following is a valid data source which you can import into SQL Server 2012?
A. Oracle data file
B. .DOC file
C. Access 2010
D. Oracle export file


Answer Clike
Which of the following SQL Server 2012 indexes stores data in a column-wise fashion instead of the traditional row-wise method?

Which of the following SQL Server 2012 indexes stores data in a column-wise fashion instead of the traditional row-wise method?
A. Bitmap Index
B. Clustered Index
C. Function-based Index
D. Columnstore Index


Answer Clike
Which of the following is the default port for an SQL Server database to communicate over?

Which of the following is the default port for an SQL Server database to communicate over?
A. 1433
B. 21 C. 1344 D. 1521


Answer Clike
Which of the following conditions must exist prior to restoring a system database in SQL Server 2012?

Which of the following conditions must exist prior to restoring a system database in SQL Server 2012?
A. The hard drive must be reformatted.
B. You must detach the user databases from the SQL 2012 instance.
C. The SQL Server 2012 instance must be started in single-user mode.
D. The SQL Server 2012 instance must be removed.


Answer Clike
Which of the following is the default instance name in an SQL Server 2012 installation?

Which of the following is the default instance name in an SQL Server 2012 installation?
A. SQLSERVER12
B. MSSQLSERVER
C. MSSQL
D. 2012SQL


Answer Clike
Which of the following statements about audit information stored in an SQL Server 2012 environment is TRUE?

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 Clike
Which of the following transaction safety setting in SQL Server 2012 mirroring forces the mirror to be synchronized with the primary at all times?

Which of the following transaction safety setting in SQL Server 2012 mirroring forces the mirror to be synchronized with the primary at all times?
A. SAFETY FULL
B. SAFETY OFF
C. MIRROR SYNC
D. MIRROR 100


Answer Clike
What should you configure?

You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when complex calculations are performed on large amounts of data under heavy system load. You need to limit the number of cores that process the calculations.
What should you configure?
A. Max worker threads
B. Processor affinity
C. I/O affinity
D. Lightweight pooling


Answer Clike
DRAG DROP

DRAG DROP
You administer a Microsoft SQL Server 2012 database instance that uses transparent database encryption.
You plan to move the database from the current server to a new server by using Backup and Restore.
Select and Place:



Answer Clike
What should you do?

DRAG DROP
You are configuring a Microsoft SQL Server 2012 server and setting up a new database. You need to ensure that the following requirements are met:
Provide access to the database for a Windows group named CORPEmployees.
The database can be moved to another server with no additional changes to the security configuration of the database or server.
What should you do? (To answer, move the appropriate action or actions from the list of actions to the answer area and arrange them in the correct order.)



Answer Clike
What should you do?

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

One of the hard disk drives that stores the reporting database fails at 23:32 hours.
You need to ensure that the reporting database is restored.
You also need to ensure that data loss is minimal. What should you do?
A. Perform a page restore.
B. Perform a partial restore.
C. Perform a point-in-time restore.
D. Restore the latest full backup.
E. Restore the latest full backup. Then, restore the latest differential backup.
F. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
H. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.


Answer Clike
What should you do?

DRAG DROP
You administer a Microsoft SQL Server 2012 database named Human_Resources.
You need to ensure that all read activity against any object in the Human_Resources database is audited and written to a text file.
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.)
Select and Place:



Answer Clike
Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration?

Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.)
A. Database Engine Services
B. SQL Server Replication
C. Analysis Services
D. Reporting Services


Answer Clike
What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?

What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?
A. 512 MB
B. 1 GB
C. 2 GB
D. 4 GB


Answer Clike
Which of the following tools would you use to accomplish this goal?

You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization’s SQL Server
Which of the following tools would you use to accomplish this goal?
A. SQLIO
B. SQLIOSim
C. SQLIOStress
D. chkdsk


Answer Clike
What is the maximum number of SQL Server 2012 Enterprise edition Database Engine instances that you can deploy on a non-clustered server running Windows Server 2008 R2 Enterprise edition?

What is the maximum number of SQL Server 2012 Enterprise edition Database Engine instances that you can deploy on a non-clustered server running Windows Server 2008 R2 Enterprise edition?
A. 10
B. 25
C. 50 D. 100


Answer Clike
Which of the following commands would you use to accomplish this goal?

You have recently removed a SQL 2012 Database Engine instance from a computer running the Windows Server 2008 R2 operating system. Prior to the removal of the instance, you had configured affinity so that the default instance used CPU 0 and 1 and the second instance used CPU 2 and 3.
You want to ensure that the default instance can use all processors available to the host. Which of the following commands would you use to accomplish this goal?
A. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
B. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3
C. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1
D. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,4


Answer Clike
Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?

Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?
A. Resource Governor
B. Windows System Resource Manager
C. Processor affinity
D. I/O affinity


Answer Clike
What is the maximum CPU and memory value you can assign to this resource pool?

You have configured Resource Governor with three resource pools.
You have assigned the first resource pool a minimum CPU and memory value of 20%.
You have assigned the second resource pool a minimum CPU and memory value of 30%.
You want to assign maximum CPU and memory values to the third resource pool.
What is the maximum CPU and memory value you can assign to this resource pool?
A. 30%
B. 50%
C. 70%
D. 100%


Answer Clike
Which of the following operating systems can you deploy the 64-bit version of SQL Server 2012 Enterprise edition?

On which of the following operating systems can you deploy the 64-bit version of SQL Server 2012 Enterprise edition? (Choose all that apply.)
A. Windows 7 Ultimate (x64) edition
B. Windows Server 2008 R2 SP1 (x64) Standard edition
C. Windows Server 2008 SP2 (x64) Enterprise edition
D. Windows Server 2003 R2 (x64) Enterprise edition


Answer Clike
Which of the following editions of SQL Server 2012 can you run on a computer that is running the Windows 7 Professional (x64) operating system?

Which of the following editions of SQL Server 2012 can you run on a computer that is running the Windows 7 Professional (x64) operating system? (Choose all that apply.)
A. SQL Server 2012 (x64) Developer edition
B. SQL Server 2012 (x64) Web edition
C. SQL Server 2012 (x64) Enterprise edition
D. SQL Server 2012 (x64) Standard edition


Answer Clike
Which of the following files will you generate by using SQL Server Setup to accomplish this goal?

You want to reproduce the same SQL Server 2012 installation configuration across five servers.
Which of the following files will you generate by using SQL Server Setup to accomplish this goal?
A. Configuration.xml
B. Setup.ini
C. Setup.xml
D. ConfigurationFile.ini


Answer Clike
Which of the following tools can you use to accomplish this goal?

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 Clike
Which role or roles should you add the users?

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 the users?
A. db_accessadmin
B. db_ddladmin
C. db_datawriter
D. db_datareader
E. db_owner


Answer Clike
Which of the following features must you install on the ETL server in addition to SSIS to accomplish this goal?

You are planning on deploying a server that will be dedicated for ETL (Extraction, Transformation, and Loading) processes.
You want to ensure that SSIS (SQL Server Integration Services) packages will run on this dedicated ETL server and not on any other server on which they were started.
Which of the following features must you install on the ETL server in addition to SSIS to accomplish this goal?
A. Database Engine
B. SQL Server Reporting Services
C. SQL Server Analysis Services
D. Client Tools SDK


Answer Clike
Which of the following features must you install if you want to ensure that you can run packages in 32-bit mode?

You have installed the SQL Server Integration Services (SSIS) feature on a server running Windows Server 2008 R2 SP1.
Which of the following features must you install if you want to ensure that you can run packages in 32-bit mode?
A. Client Tools SDK
B. Data Quality Client
C. SQL Server Data Tools
D. Client Tools Backwards Compatibility


Answer Clike
What should you include in your solution?

You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Microsoft SQL Server 2012. All databases are hosted on a SAN (Storage Area Network).
You need to design a database solution for a new application.
You are tasked with designing a high-availability database solution.
The solution must include a single copy of the database to save disk space and the database must remain online in the event of a SQL Server failure. What should you include in your solution?
A. You should include two servers configured as a failover cluster.
B. You should include two servers and database mirroring.
C. You should include two servers and log shipping.
D. You should include two servers configure as a SQL Server Availability Group


Answer Clike
Which of the following steps should you perform to restore ABCApp1DB?

You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers.
A full backup of all databases is taken every night at midnight.
A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at 11pm. A log backup is taken every 15 minutes for databases configured with the Full Recovery Model.
One application named ABCApp1 stores data in a database named ABCApp1DB. ABCApp1DB is configured with the Simple Recovery Model. ABCApp1DB fails at 3:25am. You discover that the last differential backup of ABCApp1DB failed.
You need to restore ABCApp1DB from backup as quickly as possible and minimize data loss.
Which of the following steps should you perform to restore ABCApp1DB? (Choose one or more answers).
A. Restore the latest full backup.
B. Restore the latest differential backup
C. Restore the latest log backup
D. Restore each differential backup taken since the last full backup.
E. Restore each log backup since the last full backup.


Answer Clike
What should you include in your solution?

You work as the Senior Database Administrator (DBA) at ABC.com. The company has a main office and 10 branch offices. Each branch office contains a single database server running Microsoft SQL Server 2012. The main office has multiple clustered servers running Microsoft SQL Server 2012.
Your role includes the management of the entire Microsoft SQL Server 2012 infrastructure.
The company runs a custom application that stores data in a large Microsoft SQL Server 2012 database.
The primary database is hosted in the main office. Each branch office SQL Server hosts a copy of the database.
You need to configure a solution that will replicate the entire primary database from the main office SQL Server every weekend.
What should you include in your solution?
A. Transactional Replication
B. Log Shipping
C. Snapshot Replication
D. SQL Server Availability Group


Answer Clike
Which solution should you use?

You are a database administrator for a Microsoft SQL Server 2012 instance.
You need to ensure that data can be migrated from a production server to two reporting servers with minimal data to ensure that data on the reporting server is always accessible which solution should you use?
A. Database snapshot
B. Log Shipping
C. Change Data Capture
D. Availability Groups


Answer Clike
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?
A. SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60
B. DBCC INPUTBUFFER (60)
C. EXEC sp_helpdb 60
D. SELECT * FROM sys.dm_exec_request WHERE session_id = 60


Answer Clike
Which set of tools should you install?

You install a Microsoft SQL Server 2012 instance. The instance will store data extracted from two database running on windows Azure SQL Database. You hire a data steward to perform interactive data cleaning and ad hoc querying and updating of the data.
You need to ensure that the data steward is given the correct client tools to perform these tasks. Which set of tools should you install?
A. SQL Server Management Studio and Distributed Reply Client
B. Master Data Services and Data Quality Client
C. Data Quality Client and Distributed Replay Client
D. Data Quality Client and SQL Server Management Studio


Answer Clike
Which four Transact-SQL statements should you use?

DRAG DROP
You create a view based on the following statement:

You grant the Select permission to User1 for this view. You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1. 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.)
Select and Place:


Answer Clike
Which four Transact-SQL statements should you use?

DRAG DROP
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.)
Select and Place:



Answer Clike
What should you do?

You develop a database for a travel application.
You need to design tables and other database objects. You create a stored procedure.
You need to supply the stored procedure with multiple event names and their dates as parameters. 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 Clike
What should you create for each application?

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data. The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.
What should you create for each application?
A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables


Answer Clike
Which Transact-SQL query should you use?

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2.
Which Transact-SQL query should you use?
A. SELECT StudentCode as Code,
RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks GROUP BY StudentCode
B. SELECT Id, Name, Marks,
DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM StudentMarks
C. SELECT StudentCode as Code,
DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks GROUP BY StudentCode
D. SELECT StudentCode as Code,
NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks GROUP BY StudentCode
E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,
RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1
H. SELECT StudentCode AS Code,Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
FROM StudentMarks) tmp WHERE Rank = 1


Answer Clike
What should you do?

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 Clike
Which Transact-SQL statement should you use?

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?
A. BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData COMMIT TRANSACTION END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH
B. BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData COMMIT TRANSACTION END TRY
BEGIN CATCH
IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW;
END CATCH
C. BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData COMMIT TRANSACTION END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW;
END CATCH
D. BEGIN TRANSACTION BEGIN TRY
EXEC dbo.ModifyData COMMIT TRANSACTION END TRY
BEGIN CATCH
IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH


Answer Clike
What should you do?

You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.
What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.


Answer Clike
What should you do?

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional indexes. What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.


Answer Clike
What should you do?

You develop a Microsoft SQL Server 2012 database.
You create a view from the Orders and OrderDetails tables by using the following definition.
You need to improve the performance of the view by persisting data to disk.
What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Create an AFTER trigger on the view.
C. Modify the view to use the WITH VIEW_METADATA clause.
D. Create a clustered index on the view.


Answer Clike
Which Transact-SQL query should you use?

Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?
A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; SELECT ProductCode, Price, Price * 1.05 FROM Products


Answer Clike
Which Transact-SQL query should you use?

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
C. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits


Answer Clike
Which Transact-SQL query should you use?

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed 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 SalesOrders
WHERE OrderTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()
C. SELECT COUNT(*) FROM SalesOrders
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
D. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime >= CONVERT(DATE, GETDATE())
AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))


Answer Clike
Which Transact-SQL statement should you use?

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

Users are able to use single INSERT statements or INSERT…SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TrgVwEmployee ON VwEmployee
FOR INSERT AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted
END
B. CREATE TRIGGER TrgVwEmployee ON VwEmployee
INSTEAD OF INSERT AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted
END
C. CREATE TRIGGER TrgVwEmployee ON VwEmployee
INSTEAD OF INSERT AS
BEGIN
DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT,
@EmployeeNumber NVARCHAR(15)
SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber = EmployeeNumber
FROM inserted
INSERT INTO Person(Id, FirstName, LastName) VALUES(@ID, @FirstName, @LastName)
INSERT INTO Employee(PersonID, EmployeeNumber) VALUES(@PersonID, @EmployeeNumber
End
D. CREATE TRIGGER TrgVwEmployee ON VwEmployee
INSTEAD OF INSERT AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End


Answer Clike
What should you do?

You have three tables that contain data for vendors, customers, and agents.
You create a view that is used to look up telephone numbers for these companies.
The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view. What should you do?
A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
C. Create an AFTER UPDATE trigger on the view.
D. Create an INSTEAD OF UPDATE trigger on the view.


Answer Clike
What should you do?

You develop a database for a travel application.
You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats. 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 Clike
Which object should you use?

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: Takes an input variable
Returns a table of values
Cannot be referenced within a view Which object should you use?
A. Scalar-valued function
B. Inline function
C. User-defined data type
D. Stored procedure


Answer Clike
Which Transact-SQL statement should you use?

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 following requirements are met:
The Sales role does not have the Select permission on the Customers schema. UserA has the Select permission on 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 FOR UserA
I. EXEC sp_addrolemember ‘Sales’, ‘UserA’
J. EXEC sp_droprolemember ‘Sales’, ‘UserA’


Answer Clike
Which table hint should you use?

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 Clike
Which Transact-SQL query should you use?

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 Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*) FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
D. SELECT COUNT(*) FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))


Answer Clike
What should you do?

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 Clike
Which Transact-SQL query do you use?

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?
A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM
(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c
INNER JOIN
(SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC


Answer Clike
Which four T-SQL statements should you use?

DRAG DROP
You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-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.)
Select and Place:


Answer Clike
What should you do?

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?
A. Use a FORCESCAN hint in the query.
B. Add a clustered index on SalesOrderId in SalesOrderHeader.
C. Use a FORCESEEK hint in the query.
D. Update statistics on SalesOrderId on both tables.


Answer Clike
Which user account should you use?

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. The BUILTINLocalService account
B. A domain user
C. A local administrative user
D. The BUILTINNetworkService account


Answer Clike
Which Transact-SQL statement should you use?

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. CREATE 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. CREATE ROLE proc_caller
GRANT EXECUTE ON ALL PROCEDURES TO proc_caller
ALTER MEMBER BillingUsers ADD TO ROLE proc_caller


Answer Clike
What should you include in your solution?

You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2012 Online
Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?
A. You should include a primary database with scheduled log shipping to the secondary database configured.
B. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster.
C. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to the secondary database.
D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.
E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group.


Answer Clike
Which Transact- SQL statement should you use?

You are a database developer of a Microsoft SQL Server 2012 database.
You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID.
You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?
A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,
CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
B. CREATE TABLE Customer (SourceID int NOT NULL,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);
C. CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(SourceID, CustomerID));


Answer Clike
Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?
A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1
FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS
F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
G. SELECT Name AS ‘@Name’, Country AS ‘@Country’, OrderId, OrderDate, Amount FROM
Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML PATH (‘Customers’)
H. SELECT Name AS ‘Customers/Name’, Country AS ‘Customers/Country’, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH (‘Customers’)


Answer Clike
Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?
A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers
ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= FOR XML AUTO
F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
G. SELECT Name AS ‘@Name’, Country AS ‘@Country’, OrderId, OrderDate, Amount FROM
Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers. CustomerId= 1
FOR XML PATH (‘Customers’)
H. SELECT Name AS ‘Customers/Name’, Country AS ‘Customers/Country’, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1
FOR XML PATH (‘Customers’)


Answer Clike
What should you do?

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?
A. Reference a user-defined function within the computed column.
B. Create a BEFORE trigger that maintains the state of the computed column.
C. Add a default constraint to the computed column that implements hard-coded values.
D. Add a default constraint to the computed column that implements hard-coded CASE statements.


Answer Clike
Which configuration should you use?

You are implementing a SQL Server 2012 four-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 Clike
What should you do?

HOTSPOT
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).
Hot Area:


Answer Clike
What should you do?

DRAG DROP
You administer a Windows 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: Avoid losing existing permissions on existing objects.
Use minimal administrative effort.
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.)
Select and Place:



Answer Clike
What should you configure?

You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when complex calculations are performed on large amounts of data under heavy system load.
You need to limit the number of cores that handle the processing.
What should you configure?
A. Max worker threads
B. Processor affinity
C. I/O affinity
D. Lightweight pooling


Answer Clike
Which hard disk should each database belong?

DRAG DROP
You are migrating an OLTP database from Windows Azure SQL Database to on-premise.
You are planning the installation of a Microsoft SQL Server 2012 server failover cluster. The server has the following partitions:

You install the operating system on the C: drive.
The database solution will use row versioning, triggers, and cursors. The tempdb database cannot be placed on the same disk subsystem as the OLTP database.
The OLTP database needs to be on the fastest disk subsystem possible. The database is currently 200GB in size. It will in the next 3 years.
You need to plan the usage of the disk subsystems.
To which hard disk should each database belong? (To answer, drag the appropriate database or databases to their corresponding hard disk or disks 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.)
Select and Place:



Answer Clike
What should you do?

DRAG DROP
You administer several Microsoft SQL Server 2012 servers.
You want to run scheduled checks to confirm that the databases on the servers are not configured to AutoShrink.
You need to create a policy to check the condition.
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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 instance named SQL2012. You are in the process of migrating a database from a SQL Server 2008 instance named SQL2008 to the SQL2012 instance.
You have upgraded a database from the SQL2008 instance by using the side-by-side migration technique. You need to migrate the SQL Server logins from the SQL2008 instance to the SQL2012 instance.
What should you do?
A. Back up the master database on the SQL2008 instance. Restore the master database on the SQL2012 instance
B. Use the Transfer Logins task in a Microsoft SQL Server Integrated Services package
C. Use sp_grantlogin
D. Use xp_logininfo.


Answer Clike
Which role should you add UserA?

You administer a Microsoft SQL Server 2012 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. DatabaseMailUserRole
B. ServerGroupAdministratorGroup
C. SQLAgentUserRole
D. Securityadmin


Answer Clike
What should you use?

You administer a Microsoft SQL Server 2012 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 Clike
Which set of tools should you install?

You install a Microsoft SQL Server 2012 instance.
The instance will store data extracted from two databases running on Windows Azure SQL Database.
You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the data.
You need to ensure that the data steward is given the correct client tools to perform these tasks.
Which set of tools should you install?
A. SQL Server Management Studio and Distributed Reply Client
B. Data Quality Client and SQL Server Data Tools
C. Data Quality Client and Distributed Replay Client
D. SQL Server Management Studio and Master Data Services


Answer Clike
Which SQL Server tool should you use?

You administer a Microsoft SQL Server environment. You purchase a new server and plan to migrate your database from SQL Server 2008 to SQL Server 2012.
You want to evaluate to prepare for possible conflicts and issues that may arise during or after the migration. Which SQL Server tool should you use?
A. Distributed Replay
B. Migration Assistant
C. Data Tools
D. Upgrade Advisor


Answer Clike
What should you create?

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. 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. A Database Audit Specification
G. A Policy
H. A Data Collector Set


Answer Clike
What should you create?

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


Answer Clike
Which edition should you choose?

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 Clike
Which feature should you use?

You plan to install a Microsoft SQL Server 2012 instance.
The instance will support a database that has the following requirements: Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL. Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met. Which feature should you use?
A. Excel Services
B. FILESTREAM
C. SQL Server Integration Services (SSIS)
D. OpenXML


Answer Clike
What should you do?

DRAG DROP
You administer a Microsoft SQL Server 2012 instance.
An application executes a large volume of dynamic queries.
You need to reduce the amount of memory used for cached query plans.
What should you do? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Select and Place:



Answer Clike
Which setting should you configure?

HOTSPOT
You administer a Microsoft SQL Server 2012 database instance.
The instance is running on a server with the following configuration: 1TB RAM
SAN storage for database and log files 4 quad-core processors
64-Bit Windows 2008R2 operating system
This instance hosts a database with large partitioned tables. Users report that complex queries are taking a long time to complete.
While troubleshooting, you discover that CPU utilization is low (less than 20 percent), disk activity is low (little or no waiting processes), and no significant blocking is occurring.
You need to ensure that the instance can process queries as quickly and efficiently as possible. Which setting should you configure?
Hot Area:




Answer Clike
What should you do?

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 Windows 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 Clike
Which type of solution should you use?

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 Clike
Which feature should you use?

You are a database administrator for a Microsoft SQL Server 2012 environment.
You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.
You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.
Which feature should you use?
A. Database Mirroring
B. Peer-to-Peer Replication
C. Log Shipping
D. Availability Groups


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN).
You discover I/O issues. The SAN is at capacity and additional disks cannot be added. You need to reduce the I/O workload on the SAN at a minimal cost.
What should you do?
A. Move user databases to a local disk.
B. Expand the tempdb data and log files
C. Modify application code to use table variables
D. Move the tempdb files to a local disk


Answer Clike
Which Transact-SQL statement should you insert at line 06?

You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions: Production transactions should be minimally affected.
The secondary server should allow reporting queries to be performed.
If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
A. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE ( ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’) PRIMARY_ROLE (
ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = NONE)
B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE ( ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE ( ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = YES,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)


Answer Clike
Which performance counter or counters should you use for each server?

DRAG DROP
You are a database administrator for a Microsoft SQL Server 2012 environment that contains two SQL Server instances named Server01 and Server02. The Contoso database is located on Server01. Through database mirroring, a copy of the Contoso database is on Server02.
During the last manual failover of database mirroring, the recovery took longer than usual.
You need to determine where the synchronization bottleneck is for the database mirroring topology.
Which performance counter or counters should you use for each server? (To answer, drag the appropriate performance counter or counters to their corresponding server or servers 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.)
Select and Place:



Answer Clike
What should you do?

You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber. The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
"Cannot access the file. Operating system error code 5 (Access is denied.).”
You need to configure the distribution agent by granting only the minimum required access to all accounts. What should you do?
A. Configure the Subscriber to use the Local System account.
B. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.
C. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1.
D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.


Answer Clike
Which command(s) or tool(s) should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 database. The database contains two tables named Table A and Table B.
You perform an import and export operation by using the following steps in sequence, as shown in the exhibit. (Click the Exhibit button.)

Load data from a third-party database to Table A. Insert a subset of records from Table A to Table B. Export the data from Table B to a flat file. You also create a format file for the data export.
You need to choose the correct command or tool for each task. You need to ensure that each task is performed efficiently.
Which command(s) or tool(s) should you use? (To answer, drag the appropriate command(s) or tool(s) to their corresponding task or tasks 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.)
Select and Place:



Answer Clike
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?
A. STATS
B. COMPRESSION
C. CHECKSUM
D. IN IT


Answer Clike
Which option or options should you choose in the Login Properties of the login for User1?

HOTSPOT
You administer a Microsoft SQL Server 2012 instance.
You need to configure an existing SQL Authenticated Login that meets the following requirements: Enforces password policy
No password expiration enforcement
Resets the default database to AdventureWorks database
Which option or options should you choose in the Login Properties of the login for User1? (To answer, configure the appropriate option or options in the dialog box in the answer area. )
Hot Area:


Answer Clike
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 database named Orders. Orders contain a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.
Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:

Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources. Which Transact-SQL statement should you use?
A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL
C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL


Answer Clike
Which option of the BACKUP LOG command should you use?

You administer a Microsoft SQL Server 2012 database. The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail- log backup.
You need to leave the primary database in a restoring state. Which option of the BACKUP LOG command should you use?
A. NO_TRUNCATE
B. NORECOVERY
C. STANDBY
D. FORMAT


Answer Clike
Which options should you select on the Options page of the Restore Database window?

HOTSPOT
You administer a Microsoft SQL Server database. The database is in the Simple recovery mode.
You schedule the following backup plan: Full backup every day at midnight
Differential backups every hour on the hour, except at midnight
The database fails at 20:45 hours.
You need to use SQL Server Management Studio (SSMS) to begin restoring the database to a new server instance with a minimum amount of data loss.
Which options should you select on the Options page of the Restore Database window? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Hot Area:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database. What should you do?
A. Use the copy database wizard
B. Use the Database Transfer wizard
C. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database
D. Backup the database from the local server and restore it to Windows Azure SQL Database


Answer Clike
Which permission or permissions should you choose?

DRAG DROP
You administer a Microsoft SQL Server 2012 server along with a Windows Azure SQL Database database.
For both servers, you need to grant users the ability to create logins and create databases. You need to determine which permission to grant users in each instance.
Which permission or permissions should you choose? (To answer, drag the appropriate permission or permissions to their corresponding instance type or types 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.)
Select and Place:



Answer Clike
What should you do?

You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory. Users need to be able to SELECT from all tables in the database and execute the stored procedure.
You need to grant only the necessary permissions. What should you do?
A. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
C. Add all users to the db_owner role.
D. Grant EXECUTE permission on p_Add!nventory to all users. Add all users to the db_datareader role.


Answer Clike
Which user account should you use?

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 BUILTINSYSTEM account
C. A local user with Run as Service permissions
D. TheSQLBrowser account


Answer Clike
What should you do?

You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails.
You add two Windows groups as logins for the server: CORPEmployees – All company employees CORPHRAdmins – HR administrators only
HR Administrators are also company employees.
You need to grant users access according to the following requirements: CORPEmployees should have SELECT access to the Employees table.
Only users in CORPHRAdmins should have SELECT access to the SalaryDetails table. Logins are based only on Windows security groups.
What should you do?
A. Create a database role called Employees.
Add CORPEmployees to the db_datareader role.
Add all company employees except HR administrators to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.
B. Create a database role called HRAdmins.
Add all company employees except HR administrators to the db_datareader role, Add all HR administrators to the HRAdmins role.
Grant SELECT access to the SalaryDetails table to the HRAdmins role. Deny SELECT access to the SalaryDetails table to the db_datareader role.
C. Create two database roles: Employees and HRAdmins. Add all company employees to the Employees role. Add HR administrators to the HRAdmins role.
Grant SELECT access to all tables except SalaryDetails to the Employees role. Grant SELECT access to the SalaryDetails table to the HRAdmins role.
Deny SELECT access to the SalaryDetails table to the Employees role.
D. Create a database role called Employees.
Add all HR administrators to the db_datareader role. Add all company employees to the Employees role.
Grant SELECT access to all tables except the SalaryDetails table to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.


Answer Clike
What should you do?

DRAG DROP
You administer a Microsoft SQL Server 2012 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.)
Select and Place:



Answer Clike
Which file should you use?

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 Clike
Which command or commands should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 database.
The database contains a schema named CUSTOMER. CUSTOMER contains several tables and views with sensitive data, as well as various stored procedures and functions.
In order to configure security for CUSTOMER, you need to determine how to meet the security requirements listed in the answer area.
Which command or commands should you use? (To answer, drag the appropriate command or commands to their corresponding security requirement or requirements 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.)
Select and Place:



Answer Clike
Which event should you select?

You administer a Microsoft SQL Server 2012 database.
Users report that a billing application becomes unresponsive during busy times of the day. While investigating, you notice large number of processes taking or waiting for table locks. You suspect that SQL Server is assigning stronger locks to queries.
You start a SQL Profiler trace. Which event should you select?
A. Deadlock graph
B. Lock: Escalation
C. Lock: Timeout
D. Lock: Deadlock


Answer Clike
Which four Transact-SQL statements should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 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.
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.)
Select and Place:


Answer Clike
Which type of solution should you use?

You administer a Microsoft SQL Server 2012 database that includes a table named dbo.Log. This table contains millions of records about user activity in an application.
Records in dbo.Log 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 dbo.Log without requiring any changes to the applications that utilize dbo.Log.
Which type of solution should you use?
A. Extended events
B. Columnstore index
C. Partitioned tables
D. Read committed snapshot


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause. What should you do?
A. Schedule A SQL Agent job to run every 60 Seconds and insert the results of executing the SP_who2 stored procedure table
B. Use System Monitor to catch the Lock Wait Time event
C. Use Sp_Configure to set the blocked process threshold. Start a Trace using SQL Server Profiler to catch the Blocking Pro-Report event.
D. Start a Trace using SQL Server Profiler to catch the Lock: Timeout event


Answer Clike
Which Transact-SQL batch should you use?

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_AccountCode ON AccountTransaction.AccountCode REORGANIZE
B. ALTER INDEX ALL ON AccountTransaction REBUILD
C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD
D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING


Answer Clike
Which Transact-SQL command should you use?

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation. Which Transact-SQL command should you use?
A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE
B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE
C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE
D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD


Answer Clike
Which Transact-SQL command should you use?

You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
A. BACKUP DATABASE Orders TO DISK = ‘D:Orders.bak’RESTORE DATABASE Orders_Reporting FROM DISK = ‘D:Orders.bak
B. BACKUP DATABASE Orders TO DISK = ‘D:Orders.bak’CREATE DATABASE Orders_Reporting FROM DISK = ‘D:Orders.bak
C. CREATE DATABASE Orders_Reporting AS COPY OF Orders
D. BACKUP DATABASE Orders TO DISK = ‘D:Orders.bak’MIRROR TO DISK = ‘Orders_Reporting


Answer Clike
Which server configuration property should you set?

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


Answer Clike
Which setting should you configure?

HOTSPOT
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?
Hot Area:



Answer Clike
What should you do?

You are the administrator of a Microsoft SQL Server 2012 server.
Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications You need to dynamically limit resource consumption.
What should you do?
A. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor
B. Set up Service Broker to ensure that application are not allowed to consume more than the specified amount of resource
C. Create a new rule for each application that sets the resource limit allowed
D. Create a new plan Guide with a Scope Type of sql and define the resource limits for each application


Answer Clike
Which solution should you use?

You are a database administrator for a Microsoft SQL Server 2012 instance.
You need to ensure that data can be replicated from a production server to two reporting servers in real time. You also need to ensure that data on the reporting server is always accessible.
Which solution should you use?
A. Availability Groups
B. Extended Events
C. Snapshot Replication
D. Policy Based Management


Answer Clike
What should you do?

You maintain several databases on a 32-bit Microsoft SQL Server 2005 instance on a Windows Server 2008 R2 64-bit server.
You need to migrate the databases to a 64-bit SQL Server 2012 instance on the same server. You also need to ensure that the new Transact-SQL functionality in SQL Server 2012 can be used in the database after the migration.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Perform a side-by-side installation of a 32-bit SQL Server 2012 instance.
B. Perform a side-by-side installation of a 64-bit SQL Server 2012 instance.
C. Perform an in-place upgrade to 64-bit SQL Server 2012.
D. Detach the database from the old instance and attach it to the new instance.
E. Change the compatibility level of the database.


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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 Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 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.)
Select and Place:



Answer Clike
What should you do? Choose all that apply.

You administer a Microsoft SQL Server 2012 default instance.
The instance is hosted by a server that has a local firewall configureD. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server.
You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections. What should you do? Choose all that apply.
A. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
B. Execute the following Transact-SQL command: sp_configure ‘remote admin connections’,
C. Execute the Reconfigure command.
D. Execute the following Transact-SQL command: sp_configure ‘remote access’, 1
E. Restart the SQL Server Agent Service.
F. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.


Answer Clike
Which four actions should you perform in sequence?

DRAG DROP
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.)
Select and Place:



Answer Clike
Which isolation level should you use?

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 Clike
Which backup option should you use?

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 implement log shipping of the financial database to another SQL Server 2012 instance. You decide to failover to this secondary database.
You need to ensure that all transactions will be replicated to the secondary database. Which backup option should you use?
A. Differential
B. Transaction Log
C. FULL
D. SIMPLE
E. SKIP
F. RESTART
G. STANDBY
H. CHECKSUM
I. DBO_ONLY
J. COPY_ONLY
K. NORECOVERY
L. NO_CHECKSUM
M. CONTINUE_AFTER_ERROR
N. BULK_LOGGED


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 server.
You need to install the Power View components on a stand-alone server.
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.)
Select and Place:



Answer Clike
What should you use?

You administer a Microsoft SQL Server 2012 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 permissions.
You need to ensure that the job step can run using a different user account.
What should you use?
A. A notification
B. A schedule
C. A job category
D. A proxy


Answer Clike
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?
A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
B. Disable the guest user in the msdb database.
C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
D. In the Database Mail Configuration Wizard, create an email account for each recipient’s email address in the Database Mail profile.


Answer Clike
What should you do?

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 Clike
What should you do?

You administer a Microsoft SQL Server 2012 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. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A.
B. Use Node A to install SQL Server on Node C.
C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
D. Use Cluster Administrator to add a new Resource Group to Node B.


Answer Clike
Which type of user should you create?

You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
A. SQL user without login
B. User mapped to an asymmetric key
C. Domain user
D. login mapped to a virtual account


Answer Clike
Which Transact-SQL statement should you use before attempting the restore?

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 Clike
Which Transact-SQL statement should you use?

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 Clike
Which Transact-SQL statement should you use?

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 UserA is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Object::Regions FROM Sales
C. REVOKE SELECT ON Schema::Customers FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. REVOKE SELECT ON Object::Regions FROM UserA
G. DENY SELECT ON Schema::Customers FROM Sales
H. DENY SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember ‘Sales’, ‘UserA’
J. EXEC sp droprolemember ‘Sales’, ‘UserA’


Answer Clike
Which Transact-SQL statement should you use?

You administer a SQL 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 remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Schema::Customers FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. REVOKE SELECT ON Object::Regions FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. EXEC sp_droproiemember ‘Sales’, ‘UserA’
G. REVOKE SELECT ON Schema::Customers FROM UserA
H. DENY SELECT ON Object::Regions FROM Sales
I. DENY SELECT ON Schema::Customers FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales


Answer Clike
Which Transact-SQL statement should you use?

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 Clike
What should you do?

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 role. Add the login for the ticketing application to this role.
C. Install a new named SQL Server instance on the server.
D. Install a new default SQL Server instance on the server.


Answer Clike
What should you do?

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. Configure the application as data-tiered.
B. Open port 1433 on the Windows firewall on the server.
C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
D. Start the SQL Server Browser Service.


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail.
The following error message is an example error message:
"The job failed. The Job was invoked by User CONTOSOServiceAccount. The last step to run was step 1 (Subplan_1).”
You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained.
What should you do?
A. Configure output files.
B. Expand agent logging to include information from all events.
C. Disable the Limit size of job history log feature.
D. Configure event forwarding.


Answer Clike
Which four actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job.
The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package.
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.)
Select and Place:



Answer Clike
What should you do? Choose all that apply.

You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.
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 Clike
What should you do?

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 Clike
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?
A. EXEC sp_who 60
B. SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
C. EXEC sp_helpdb 60
D. DBCC INPUTBUFFER (60)


Answer Clike
Which configuration should you use?

You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database.
You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers.
You need to achieve these goals without manual intervention. Which configuration should you use?
A. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
B. SQL Server that includes an application database configured to perform transactional replication
C. Two servers configured in the same data center
A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
D. Two servers configured in different data centers
SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
E. Two servers configured in the same data center
SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
F. Two servers configured in different data centers
SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
G. SQL Server that includes an application database configured to perform snapshot replication
H. Two servers configured on the same subnet
SQL Server Availability Group configured in Synchronous-Commit Availability Mode


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You are a database administrator of a Microsoft SQL Server 2012 environment. The environment contains two servers named SQLServer01 and SQLServer02. The database Contoso exists on SQLServer01.
You plan to mirror the Contoso database between SQLServer01 and SQLServer02 by using database mirroring. You need to prepare the Contoso database for database mirroring.
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.)
Select and Place:



Answer Clike
What should you do?

You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica. You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?
A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.


Answer Clike
What should you create?

You administer a Microsoft SQL Server 2012 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?
A. A Database Audit Specification
B. A Policy
C. An Alert
D. A SQL Profiler Trace
E. A Resource Pool
F. An Extended Event session
G. A Server Audit Specification


Answer Clike
What should you create?

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01.
What should you create?
A. An Alert
B. A Server Audit Specification
C. An Extended Event session
D. A Resource Pool
E. A Policy
F. A SQL Profiler Trace
G. A Database Audit Specification


Answer Clike
What should you create?

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?
A. An Extended Event session
B. A Policy
C. A Database Audit Specification
D. An Alert
E. A Server Audit Specification
F. A SQL Profiler Trace
G. A Resource Pool


Answer Clike
What should you create?

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to prevent users from disabling server audits in Server01. What should you create?
A. An Alert
B. A Resource Pool
C. An Extended Event session
D. A Policy
E. A Database Audit Specification
F. A SQL Profiler Trace
G. A Server Audit Specification


Answer Clike
Which four Transact-SQL statements should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.
You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.
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.)
Select and Place:



Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 database. Your database is experiencing deadlock issues.
You need to be able to monitor deadlocks.
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.)
Select and Place:



Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
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.)
Select and Place:



Answer Clike
Which data import command and recovery model should you choose?

DRAG DROP
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.)
Select and Place:



Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 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.)
Select and Place:



Answer Clike
Which four actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do?
A. Query the sys.dm_db_index_usage_stats DMV.
B. Query the sys.dm_db_missing_index_details DMV.
C. Use the Database Engine Tuning Advisor.
D. Query the sys.dm_db_missing_index_columns DMV.


Answer Clike
Which Transact-SQL batch should you use?

You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.
You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD


Answer Clike
What should you do?

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

You discover that all files except Sales_2.ndf are corrupt.
You need to recover the corrupted data in the minimum amount of time. What should you do?
A. Perform a file restore.
B. Perform a transaction log restore.
C. Perform a restore from a full backup.
D. Perform a filegroup restore.


Answer Clike
Which backup should you perform every two hours?

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 the backup size is as small as possible. Which backup should you perform every two hours?
A. BULK_LOGGED
B. NO_CHECKSUM
C. FULL
D. RESTART
E. CHECKSUM
F. STANDBY
G. DBO.ONLY
H. NORECOVERY
I. SIMPLE
J. SKIP
K. Transaction log
L. COPY_ONLY
M. Differential
N. CONTINUE_AFTER_ERROR


Answer Clike
What should you do on Saturday?

You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time.
What should you do on Saturday?
A. Perform a differential backup at 08:59 hours.
B. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
C. Create a database snapshot at 08:59 hours.
D. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
E. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
F. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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.
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? Choose all that apply.
A. Configure the database to use the SIMPLE recovery model.
B. Create a DIFFERENTIAL database backup every 4 hours.
C. Create a LOG backup every 5 minutes.
D. Configure the database to use the FULL recovery model.
E. Create a FULL database backup every 24 hours.
F. Create a DIFFERENTIAL database backup every 24 hours.


Answer Clike
Which backup option should you use?

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 Clike
Which backup option should you use?

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.
On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version. You need to perform a full database backup that will be restored on the development server.
Which backup option should you use?
A. NORECOVERY
B. FULL
C. NO_CHECKSUM
D. CHECKSUM
E. Differential
F. 8ULK_LOGGED
G. STANDBY
H. RESTART
I. SKIP
J. Transaction log
K. DBO ONLY
L. COPY_ONLY
M. SIMPLE
N. CONTINUE AFTER ERROR


Answer Clike
Which recovery model should the database use?

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 the minimum amount of data is lost. Which recovery model should the database use?
A. FULL
B. DBO_ONLY
C. CONTINUE_AFTER_ERROR
D. CHECKSUM
E. NO_CHECKSUM
F. SIMPLE
G. Transaction log
H. SKIP
I. RESTART
J. COPY_ONLY
K. NORECOVERY
L. BULK_LOGGED
M. Differential
N. STANDBY


Answer Clike
What should you configure?

You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?
A. Processor affinity
B. Lightweight pooling
C. Max worker threads
D. I/O affinity


Answer Clike
What should you do?

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 Clike
What should you do?

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 Clike
What should you do?

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 Clike
What should you do? Choose all that apply.

You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account named CONTOSOSQLService. 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 CONTOSOSQLService account to the Perform Volume Maintenance Tasks local security policy.
E. Add the CONTOSOSQLService account to the Server Operators fixed server role.
F. Enable snapshot isolation.


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a single Microsoft SQL Server instance on a two-node failover cluster that has nodes named Node A and Node B. The instance is currently running on Node A.
You want to patch both Node A and Node B by using the most recent SQL Server Service Pack. You need to ensure that the following requirements are met:
Both nodes receive the update.
Downtime is minimized. No data is lost.
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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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. Run the New SQL Server stand-alone installation Wizard on Node C.
B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
C. Use Node B to install SQL Server on Node C.
D. Use Node A to install SQL Server on Node C.


Answer Clike
Which tool or tools should you use?

DRAG DROP
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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?
A. Execute sp_configure ‘max log size’, 2G.
B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.
C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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?
A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.
B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
D. Run the sp_who command from a query window.
E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.


Answer Clike
Which server-level audit action group should you use?

You administer a Microsoft SQL Server 2012 database.
You provide temporary securityadmin access to User1 to the database server. You need to know if User1 adds logins to securityadmin.
Which server-level audit action group should you use?
A. SERVER_STATE_CHANGE_GROUP
B. SERVER_PRINCIPAL_IMPERSONATION_GROUP
C. SUCCESSFUL_LOGIN_GROUP
D. SERVER_ROLE_MEMBER_CHANGE_GROUP


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 instance.
You need to stop a blocking process that has an SPID of 64 without stopping other processes What should you do?
A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64
B. Restart the SQL Server service.
C. Execute the following Transact-SQL statement: KILL 64
D. Execute the following Transact-SQL statement: ALTER SESSION KILL ’64’


Answer Clike
Which tool or tools should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 server.
A variety of issues occur from time to time in the production environment. You need to identify the appropriate tool for each issue.
Which tool or tools should you use? (To answer, drag the appropriate tool or tools to the correct issue or issues 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.)
Select and Place:



Answer Clike
What should you do?

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. Create an Extended Events session by using the sqlserver.error_reported event.
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 Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 database.
All database traffic to the SQL Server must be encrypted by using secure socket layer (SSL) certificates or the connection must be refused.
Network administrators have deployed server certificates to the Windows store of all Windows servers on the network from a trusted Certificate Authority. This is the only Certificate Authority allowed to distribute certificates on the network.
You enable the Force Encryption flag for the MSSQLServer protocols, but client computers are unable to connect. They receive the following error message:
"A connection was successfully established with the server, but then an error occurred during the pre-login handshake, (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server)”
You notice the following entry in the SQL Server log:
"A self-generated certificate was successfully loaded for encryption.”
You need to configure SQL Server to encrypt all client traffic across the network.
You also need to ensure that client computers are able to connect to the server by using a trusted certificate.
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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D. Grant the db_owner role on the database to User1.
E. Grant the sysadmin role on the database to User1.


Answer Clike
Which Transact-SQL statement should you use?

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012.
You attempt to add the [CorpnetUser1] login to the database. However, you receive the following error message: “User already exists in current database.”
You need to configure the [CorpnetUser1] login to be able to access the Orders database and retain the original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?
A. DROP USER [User1];
CREATE USER [CorpnetUser1] FOR LOGIN [CorpnetUser1]; ALTER ROLE [db_owner] ADD MEM3ER [CorpnetUser1];
B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [CorpnetUser1];
C. ALTER USER [CorpnetUser1] WITH LOGIN [CorpnetUser1];
D. ALTER ROLE [db owner] ADD MEMBBR [CorpnetUser1];


Answer Clike
Which Transact-SQL statement should you use before attempting the restore?

You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

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. Option A
B. Option B
C. Option C
D. Option D


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 server that has multiple databases.
You need to ensure that users are unable to create stored procedures that begin with sp_.
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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema.
Some users must be prevented from deleting records in any of the tables in the Sales schema.
You need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.


Answer Clike
Which SQL statement or statements should you use?

You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment.
All DBAs are members of the DOMAINJrDBAs Active Directory group.
You grant DOMAINJrDBAs access to the SQL Server.
You need to create a server role named SpecialDBARole that can perform the following functions: View all databases.
View the server state.
Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAINJrDBAs to the server role.
You also need to provide the least level of privileges necessary. Which SQL statement or statements should you use? Choose all that apply.
A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAINJrDBAs];
C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
D. GRANT VIEW DEFINITION TO [SpecialDBARole];
E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];


Answer Clike
Which permission or permissions should you assign to UserC?

You administer a Microsoft SQL Server 2012 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 permission or permissions should you assign to UserC? Choose all that apply.
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 Clike
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?
A. GRANT DELETE ON Purchases. Suppliers TC ContosoUser
B. CREATE PROCEDURE Purchases.PurgelnactiveSuppliers WITH EXECUTE AS USER = ‘dbo’
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
D. CREATE PROCEDURE Purchases. PurgeInactiveSuppliers AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO
GRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser


Answer Clike
Which type of user should you create?

You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database.
You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
A. User mapped to a certificate
B. SQL user without login
C. Domain user
D. SQL user with login


Answer Clike
What should you do?

HOTSPOT
You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data. Each record is of the same length.
The date follows the U.S. date format. The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Hot Area:



Answer Clike
Which four Transact-SQL statements should you use?

DRAG DROP
You administer a Microsoft SQL Server 2012 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 SpecialOfferID 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.)
Select and Place:



Answer Clike
What should you do?

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 Clike
What should you configure?

You administer a Microsoft SQL Server 2012 server.
When transaction logs grow, SQL Server must send an email message to the database administrators.
You need to configure SQL Server to send the email messages.
What should you configure?
A. SQL Mail
B. An Extended Events session
C. Alerts and operators in SQL Server Agent
D. Policies under Policy-Based Management


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.
The development team develops a new application that uses the SQL Server 2012 functionality.
You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.
You need to migrate the database without affecting the production environment.
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.)
Select and Place:



Answer Clike
Which six actions should you perform in sequence?

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

Select and Place:


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.
You need to configure the SQL Server environment to support these packages. What should you do?
A. Create SSIS configuration files for the packages.
B. Create an Integration Services catalog.
C. Install Data Quality Services.
D. Install Master Data services.


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
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.)
Select and Place:



Answer Clike
How should you configure HaContoso?

You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA. Currently, Server01l/HA is the primary replicA.
You need to ensure that the following requirements are met: Backup operations occur on Server02/HA.
If Server02/HA is unavailable, backup operations occur on Server03/HA. Backup operations do not occur on Server01/HA.
How should you configure HaContoso?
A. • Set the backup preference of HaContoso to Prefer Secondary.
• Set the backup priority of Server02/HA to 20.
• Set the backup priority of Server03/HA to 10.
B. • Set the backup preference of HaContoso to Secondary only.
• Set the backup priority of Server02/HA to 20.
• Set the backup priority of Server03/HA to 10.
C. • Set the backup preference of HaContoso to Secondary only.
• Set the backup priority of Server02/HA to 10.
• Set the backup priority of Server03/HA to 20.
D. • Set the exclude replica of Server01/HA to true.
• Set the backup priority of Server02/HA to 10.
• Set the backup priority of Server03/HA to 20.


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured. When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated.
The following error message is an example of the truncated error message:
"Executed as user CONTOSOServiceAccount….0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript… The package execution fA. .. The step failed. ”
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs. What should you do?
A. Expand agent logging to include information from all events.
B. Disable the Limit size of job history log feature.
C. Configure event forwarding.
D. Configure output files.


Answer Clike
What should you do?

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN).
Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors.
You need to reduce the occurrence of these timeout errors.
What should you do?
A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
C. Change the Merge agent on the problem subscribers to run continuously.
D. Set the Remote Connection Timeout on the Publisher to 0.


Answer Clike
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01Contoso01.
You need to configure the availability group to have the highest availability.
You also need to ensure that no data is lost. Which Transact-SQL statement should you use?

A. Option A
B. Option B
C. Option C
D. Option D


Answer Clike
What should you do?

DRAG DROP
You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for each scenario.
What should you do? (To answer, drag the appropriate replication method or methods to the correct location or locations in the answer area. Each replication method may be used once, more than once, or not at all.)



Answer Clike
What should you do?

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

One of the hard disk drives that stores the reporting database fails at 16:40 hours.
You need to ensure that the reporting database is restored.
You also need to ensure that data loss is minimal. What should you do?
A. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
B. Perform a partial restore.
C. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
D. Perform a point-in-time restore.
E. Restore the latest full backup.
F. Perform a page restore.
G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
H. Restore the latest full backup. Then, restore the latest differential backup.


Answer Clike
Which three actions should you perform in sequence?

DRAG DROP
You administer three Microsoft SQL Server 2012 servers named ServerA, ServerB, and ServerC. ServerA is the acting principal and ServerB is the mirror.
You need to add ServerC as a witness to the existing mirroring session between ServerA and ServerB.
You need to achieve this goal without delaying synchronization.
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.)
Select and Place:



Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database.
You need to ensure that the transactional database is restored.
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 backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
C. Perform a point-in-time restore.
D. Restore the latest full backup.
E. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
F. Perform a page restore.
G. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
H. Restore the latest full backup. Then, restore the latest differential backup.


Answer Clike
Which configuration should you use?

You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.
You also need to minimize the amount of storage used by the database.
Which configuration should you use?
A. • Two servers configured in different data centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
B. • SQL Server that includes an application database configured to perform transactional replication
C. • Two servers configured in the same data center
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
D. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. • Two servers configured in the same data center
• A primary server configured to perform log-shipping every 10 minutes
• A backup server configured as a warm standby
F. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. • SQL Server that includes an application database configured to perform snapshot replication
H. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance


Answer Clike
What should you do?

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

The differential backup of the reporting database fails. Then, the reporting database fails at 14:00 hours.
You need to ensure that the reporting database is restored.
You also need to ensure that data loss is minimal. What should you do?
A. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
B. Perform a point-in-time restore.
C. Restore the latest full backup.
D. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
E. Restore the latest full backup. Then, restore the latest differential backup.
F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
G. Perform a page restore.
H. Perform a partial restore.


Answer Clike
What should you do?

You administer a Microsoft SQL Server 2012 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 backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
C. Restore the latest full backup.
D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
E. Perform a page restore.
F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
G. Perform a point-in-time restore.
H. Restore the latest full backup. Then, restore the latest differential backup.


Answer Clike
Which configuration should you use?

You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server.
You also need to be able to add indexes to the secondary database.
Which configuration should you use?
A. • Two servers configured in different data centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
B. • Two servers configured in the same data center
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
C. • Two servers configured in the same data center
• A primary server configured to perform log-shipping every 10 minutes
• A backup server configured as a warm standby
D. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F. • SQL Server that includes an application database configured to perform transactional replication
G. • SQL Server that includes an application database configured to perform snapshot replication
H. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance


Answer Clike
Which configuration should you use?

You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that data changes are sent to a non-SQL Server database server in near real time.
You also need to ensure that data on the primary server is unaffected. Which configuration should you use?
A. • SQL Server that includes an application database configured to perform transactional replication
B. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
C. • Two servers configured in different data centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
D. • SQL Server that includes an application database configured to perform snapshot replication
E. • Two servers configured in the same data center
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
F. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance
H. • Two servers configured in the same data center
• A primary server configured to perform log-shipping every 10 minutes
• A backup server configured as a warm standby


Answer Clike
Which configuration should you use?

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 center
• A primary server configured to perform log-shipping every 10 minutes
• A 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 center
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• One server configured as an Active Secondary
D. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL 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 centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
G. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
H. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode


Answer Clike