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

1. You maintain a SQL Server 2008 instance that contains a database named Finance. The Finance database contains the following filegroups:
The PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP) data stored in the F: volume.
A filegroup named Histoiy that contains historical data stored in the G: volume. The transaction log of the database is stored in the H: volume.
As part of the archiving strategy for the Finance database, data is frequently moved from the PRIMARY filegroup to the History filegroup.
The backup strategy for the Finance database consists of the following requirements:
Backing up the PRIMARY filegroup five times a day Backing up the History filegroup at the end of each working day Backing up the transaction log every 15 minutes At 13:00 hours, the volume that contains the History filegroup fails.
You need to restore the database by ensuring minimal data loss in minimum possible time. What should you do?

A) Restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup.
B) Backup the transaction log. Restore the most recent backup of the PRIMARY filegroup. Restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup.
C) Backup the transaction log. Restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup.
D) Restore the most recent backup of the History filegroup.

2. DRAG DROP
You administer a Microsoft SQL Server 2008 R2 server that hosts two databases named NorthwindOLTP and NorthwindOLAP. Cross database ownership chaining is currently disabled for all databases.
A stored procedure in the NorthwindOLTP database writes data to a table in the NorthwindOLAP database. The public role in both databases has only Read and Execute permissions assigned to all objects. All objects are located in the DBO schema. Guest access has been disabled for all databases on the server.
You need to ensure that the stored procedure can execute successfully.
Which two 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.)

3. You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:

The following DDL has been run on the database:
CREATE PARTITION FUNCTION FUNC_FG (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
You need to create a partition scheme that will place all data to the SECONDARY filegroup.
What should you do?

A) Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
B) Create a new Filegroup.
Create a new database File.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
C) Create a new filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use ALTER INDEX REORGANIZE statement.
D) Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E) Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO (SECONDARY);
F) Remove the clustered index from the table.
G) Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
H) Use the ALTER TABLEstatement to remove the COLLATEoption.
I) Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItem3',
@OptionName= 'PartltionByYear';
@OptionValue= 'true';
J) Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE
clause.

4. View Exhibit

A) Use the Activity Monitor in Microsoft SQL Management Studio and locate process 55.
B) Execute sp_who 56.
C) Select from the sys.syslockinfo compatibility view and locate spid 55.
D) Execute sp_who 55.

5. You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition: Currently, the table is partitioned by year with each year in its own filegroup.

You need to add a new partition for the upcoming year.
What should you do?

A) Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
B) Create a new Filegroup.
Create a new database File.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
C) Create a new filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use ALTER INDEX REORGANIZE statement.
D) Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E) Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO (SECONDARY);
F) Remove the clustered index from the table.
G) Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
H) Use the ALTER TABLEstatement to remove the COLLATEoption.
I) Run the following statement:
EXECUTE sp_tableoption
@TableNamePattern ='OrderItem3',
@OptionName= 'PartltionByYear';
@OptionValue= 'true';
J) Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE
clause.

Solutions: