Microsoft Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform Sample Questions:

1. You need to modify the usp.DetectSuspiciousActivity stored procedure.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.

A) Option E
B) Option D
C) Option B
D) Option C
E) Option F
F) Option A

2. You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet
the following requirements:
The strategy must not disrupt backup operations.
DB1 must be online while the changes are deployed.
You must be able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
---

A) Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.
B) Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
C) Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
D) Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

3. You need to implement a backup strategy to support the requirements.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.

A) Create a credential called MyCredential on SQL Server by using a Windows domain account and password.
B) Create a share on the hot standby site and grant permission to the SQL Server service login.
C) Create a share on your Windows Azure site by using your Windows Azure storage account information, and grant permission to the SQL Server service login.
D) Schedule a full backup by using the command BACKUP DATABASE ProdDB TO URL ... WTTH CREDENTIAL=N'MyCredential'
E) Schedule a full backup by using the command BACKUP DATABASE ProdDB TO DISK...
F) Create a credential called MyCredential on SQL Server, using MyStorageAccount for the storage account name and StorageAccountKey for the access key.
G) Schedule a full backup by using the command BACKUP DATABASE ProdDB TO SHARE ... WITH CREDENTIAL=N' MyCredential'

4. You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?

A) DBCC INDEXDEFRAG (ProdDB, SalesOrderDetail, SODIndex)
B) ALTER INDEX SODIndex ON SalesOrderDetail REBUILD
C) ALTER INDEX SODIndex ON SalesOrderDetail REORGANIZE
D) ALTER INDEX SODIndex ON SalesOrderDetail REBUILD WITH (ONLINE * ON)

5. You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?

A) the collation of the Products table
B) the collation of the ProductName column
C) the data type of the ProductName column
D) the index on the ProductName column

Solutions: