Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

1. You administer two SQL Server 2008 instances named Instance1 and Instance2.
A database named Customers resides on Instance1. You move the Customers database from Instance1 to Instance2.
A SQL Server login named User1 that has the password "111999" is used by a user to access the database on Instance1. You create the same SQL Server login on Instance2.
The user attempts to access the Customers database on Instance2 by using the SQL Server login User1. However, the user receives an error message which indicates that the access to the Customers database is denied.
You need to ensure that User1 can access the Customers database.
Which Transact-SQL statements should you execute on Instance2?

A) USE Customers; ALTER USER User1 WITH LOGIN = User1;
B) USE Customers; ALTER USER User1 ENABLE;
C) USE Customers; ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers;
D) USE Customers; ALTER LOGIN User1 WITH PASSWORD = '111999' UNLOCK;

2. You administer a SQL Server 2008 instance that contains a database named InsightDB.
The InsightDB database is used by an application that is continuously connected.
The application uses the INSERT command extensively. The application uses triggers to populate multiple tables.
You need to ensure that the reports generated by the application using the InsightDB database return current information with minimal negative effects on the application inserts.
What should you do?

A) Set the isolation level to Read Committed Snapshot for the InsightDB database.
B) Use the database replication feature and configure the application to use the replicated database.
C) Use the database snapshot feature and configure the application to use the snapshot database.
D) Set the isolation level to Read Uncommitted for the InsightDB database.

3. You administer a SQL Server 2008 instance.
You need to find out the number of users who access the SQL Server instance by using Windows System Monitor.
Which performance object should you capture?

A) SQLServenBuffer Manager
B) SQLServer:Access Methods
C) SQLServer:Exec Statistics
D) SQLServer:General Statistics

4. You manage a SQL Server 2008 instance. You upgrade a SQL Server 7.0 database to the SQL Server instance.
You need to ensure the early detection of suspect pages in the database.
What should you do?

A) Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION for the model database.
B) Turn on the TRUSTWORTHY database option for the database.
C) Set the database compatibility level option to 10 for the database.
D) Set the PAGE_VERIFY database option to CHECKSUM for the database.

5. You administer a remote SQL Server 2008 instance. Users report that the performance of the application is poor.
You use SQL Profiler to capture a workload of the remote instance to a trace table on the remote SQL Server instance.
You need to analyze the workload of the remote SQL Server instance on a local SQL Server instance by using the Database Engine Tuning Advisor.
What should you do?

A) Use SQL Profiler to recapture the workload to a trace file.
B) Use the data collector to recapture the workload.
C) Enable the XP_MSVER stored procedure on the local server.
D) Enable the XP_MSVER stored procedure on the remote server.

Solutions: