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

1. You administer a Microsoft SQL Server 2008 R2 database that hosts an order-processing application.
You need to ensure that the database allows full-text searches on the Customers table.
You also need to ensure that the full-text index is ready for use by the users.
You execute a Transact-SQL statement to create the full-text index on the Customers table
by using the CHANGE_TRACKING
OFF and NO POPULATION clauses in the CREATE FULLTEXT INDEX statement.
You execute an ALTER FULLTEXT INDEX.
Which command or commands should you use next?

A) Option B
B) Option D
C) Option C
D) Option A

2. You design a maintenance plan for a SQL Server 2008 instance that contains a database named SalesDB.
The SalesDB database includes spatial indexes to support queries on spatial data.
You need to perform physical consistency checks on SalesDB. You also need to ensure that the performance effect on the SalesDB database is minimized.
Which Transact-SQL statement should you execute?

A) DBCC CHECKDB (SalesDB);
B) DBCC SYS_CHECK (SalesDB);
C) DBCC SQLPERF (SalesDB);
D) DBCC RSPAIRDB (SalesDB);

3. You administer a Microsoft SQL Server 2008 R2 instance.
You need to configure mirroring between two servers in high-performance mode.
What should you do?

A) Create mirroring endpoints on both partner servers.
Use Microsoft SQL Server Management Studio on both partner servers to select the High Performance option, and start mirroring.
B) Create mirroring endpoints on both partner servers.
Execute the following stored procedure: Sp_configure 'Lightweight Pooling', 1
C) Create mirroring endpoints on both partner servers.
On the principal server, use the ALTER DATABASE statement along with the PARTNER SAFETY OFF clause.
D) Create mirroring endpoints on both partner servers.
Use the ALTER ENDPOINT statement along with the ENCRYPTION=DISABLED clause.

4. You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales. The database is replicated between SQL1 and SQL2 by using transactional replication. A full backup of each database is performed every night.
Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on SQLTEST1 becomes corrupted. You are unable to repair the database. The Sales database on SQLTEST2 is unaffected. You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

A) You should reserve a full database backup on SQL2. Reserve the backup to SQL1.
B) You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
C) You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
D) You should reserve only the newest transaction log backup.

5. You maintain a SQL Server 2008 instance that contains a database named Finance. A small percentage of data in the database is modified daily.
You need to create a read-only copy of the database for reporting purposes at the beginning of each day by ensuring that minimal disk space is used.
Which strategy should you use?

A) Use database snapshots.
B) Use database backup and restore along with compression.
C) Use schema-bound views in a read-only database that resides on the same instance.
D) Use database backup and restore.

Solutions: