Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

1. 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) Change the Merge agent on the problem subscribers to run continuously.
B) Set the Remote Connection Timeout on the Publisher to 0.
C) Set the Merge agent on the problem subscribers to use the slow link agent profile.
D) Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.

2. 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.)
Build List and Reorder:

3. 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 during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

A) Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
B) Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
C) Cover the unique clustered index with a columnstore index.
D) Add a FORCESCAN hint to the Attach query.
E) Add a LOOP hint to the query.
F) Enable the optimize for ad hoc workloads option.
G) Add an INCLUDE clause to the index.
H) Add a HASH hint to the query.
I) Include a SET STATISTICS PROFILE ON statement before you run the query.
J) Include a SET FORCEPLAN 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) Add a columnstore index to cover the query.
N) Add a FORCESEEK hint to the query.

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

5. 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) SKIP
B) SIMPLE
C) CHECKSUM
D) NORECOVERY
E) Differential
F) NO_CHECKSUM
G) CONTINUE AFTER ERROR
H) 8ULK_LOGGED
I) Transaction log
J) COPY_ONLY
K) STANDBY
L) DBO ONLY
M) FULL
N) RESTART

Solutions: