Microsoft 70-432 Online Practice Questions and Exam Preparation

Microsoft 70-432 Online Questions & Answers

• Question 11:

  You administer a SQL Server 2008 instance.

  A stored procedure is used as the data source for a report that is frequently executed during business hours.

  Users report that the data returned by the report is inconsistent. You discover that the inconsistencies are caused by phantom reads.

  You need to ensure that the report returns consistent results without affecting other users.

  What should you do?

  A. Configure the database for Read Committed Snapshot isolation.
  B. Configure the database for Auto Update Statistics asynchronously.
  C. Modify the stored procedure to use the Snapshot isolation level.
  D. Modify the stored procedure to use the Repeatable Read isolation level.
  C. Modify the stored procedure to use the Snapshot isolation level.

• Question 12:

  You maintain a SQL Server 2008 instance that contains a database named Finance. The data file and the transaction log file are located on the E: drive. The E: drive has only 5 percent available space.

  You need to move both files to the V: drive.

  Which procedure should you use?

  A. Run the following Transact-SQL statement. ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE ( NAME = Finance_Data, FILENAME = 'v:\SQLServer \Finance_Data.mdf'); ALTER DATABASE Finance SET MULTI_USER;
  B. Run the following Transact-SQL statement. ALTER DATABASE Finance SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Log, FILENAME ='v:\SQLServer\Finance_Log.ldf'); ALTER DATABASE Finance SET ONLINE;
  C. Stop the SQL Server service. Move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_db @dbname = N'Finance', @physname = N'v:\SQLServer \Finance_Data.mdf';
  D. Stop the SQL Server Service. Move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_db @dbname = N'Finance', @filename1 = N'v: \SQLServer\Finance_Data.mdf', @filename2 = N'v:\SQLServer\Finance_Log.ldf';
  B. Run the following Transact-SQL statement. ALTER DATABASE Finance SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Log, FILENAME ='v:\SQLServer\Finance_Log.ldf'); ALTER DATABASE Finance SET ONLINE;

• Question 13:

  You maintain a SQL Server 2008 instance that contains a database named CustomerDB. The CustomerDB database stores customer data for the company. The customers use a Web application to access their profile data. You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost. You want to achieve this goal without affecting the Web application. What should you do?

  A. Enable Transparent Database Encryption for both the CustomerDB database and the master database.
  B. Encrypt the sensitive data at the cell level by using the built-in encryption functions.
  C. Make the CustomerDB database accessible only through stored procedures and functions.
  D. Enable Transparent Database Encryption for the CustomerDB database and back up the transaction log.
  D. Enable Transparent Database Encryption for the CustomerDB database and back up the transaction log.

• Question 14:

  You administer a Microsoft SQL Server 2008 R2 database instance. The instance has a Products table that is currently in the OevUser schema. The Appusers database role is assigned Read and update permissions to the table.

  The DevUser schema is owned by a database login that has been renamed.

  You need to move the Products table from the DevUser schema to the dbo schema.

  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.)

  Select and Place:

  

  

• Question 15:

  You maintain a SQL Server 2008 instance that contains a database named AdventureWorks. The AdventureWorks database contains two tables as shown in the following table:

  

  The OrderHistory table contains two table partitions: one partition contains all data and the other partition is empty.

  You plan to develop a process to move data from the partition that contains data for the oldest month of the Orders table to the appropriate partition of the OrderHistory table.

  You need to ensure that the process can be repeated at the end of every month.

  What should you do?

  A. Alter the partition function of the Orders table by using the split option. Alter the Orders table by using the merge option.
  B. Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the split option.
  C. Alter the partition function of the Orders table by using the split option. Alter the OrderHistory table by using the split option. Alter the partition function of the OrderHistory table by using the merge option. Alter the Orders table by using the merge option.
  D. Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the merge option. Alter the Orders table by using the merge option.
  D. Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the merge option. Alter the Orders table by using the merge option.

• Question 16:

  You administer a SQL Server 2008 instance that contains a database named Adventure Works. The AdventureWorks database contains the Products table.

  You create a Merge Replication topology and a Publication to replicate the Products table to the SQL Server instances at remote locations. The Publication has a 21-day retention period.

  When a user returns from a one-month vacation, she discovers that her database does not contain the most recent data. The Windows Event log states the following error message:

  "Replication: expired subscription dropped."

  You need to obtain the most recent data in the database of the user.

  You also need to ensure that future data changes are appropriately replicated.

  What should you do?

  A. Recreate the publication.
  B. Upload unsynchronized changes.
  C. Upload unsynchronized changes, and then reinitialize the publication.
  D. Reinitialize the publication and immediately generate a new snapshot.
  D. Reinitialize the publication and immediately generate a new snapshot.

• Question 17:

  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 the data collector to recapture the workload.
  B. Use SQL Profiler to recapture the workload to a trace file.
  C. Enable the XP_MSVER stored procedure on the local server.
  D. Enable the XP_MSVER stored procedure on the remote server.
  B. Use SQL Profiler to recapture the workload to a trace file.

• Question 18:

  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 move the data from the oldest year into a new table in a different Schema to archive the data.

  What should you do?

  A. Remove the clustered index from the table.
  B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
  C. Use the ALTER TABLEstatement to remove the COLLATEoption.
  D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
  E. 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.
  F. 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.
  G. 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.
  H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
  I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
  J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';
  G. 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.

• Question 19:

  You have a server that contains a default SQL Server 2005 instance. You need to install a SQL Server 2008 instance for a new application on the same server. The new application requires SQL Server 2008 functionality.

  You need to ensure that both database instances are available for their respective certified third-party applications. The existing application environments remain unchanged.

  What should you do?

  A. Install SQL Server 2008 as the default instance.
  B. Install SQL Server 2008 as a named instance.
  C. Upgrade the SQL Server 2005 application to use SQL Server 2008.
  D. Upgrade the SQL Server 2005 instance to a SQL Server 2008 instance.
  B. Install SQL Server 2008 as a named instance.

• Question 20:

  You administer a SQL Server 2008 instance named CorpPub that contains a database named SalesSupport The SalesSupport database contains the Products table.

  You plan to create a Replication topology to replicate the Products table to the SQL Server instances installed in portable computers. When the portable computers reconnect to the corporate network, the Products table is updated from

  CorpPub.

  The Products table on the CorpPub instance is frequently updated between reconnections.

  You need to successfully implement the Replication topology along with the Subscription type by ensuring that bandwidth usage is minimized.

  What should you do?

  A. Implement the Merge Replication topology along with a Pull Subscription.
  B. Implement the Snapshot Replication topology along with a Pull Subscription.
  C. Implement the Snapshot Replication topology along with a Push Subscription.
  D. Implement the Transactional Replication topology along with a Pull Subscription.
  A. Implement the Merge Replication topology along with a Pull Subscription.