Microsoft 070-450 Exam Syllabus Topics:

Microsoft PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu Sample Questions:

Question #1
You administer a SQL Server 2008 instance.
The instance is using a 32-bit version on a Windows Server 2008 64-bit server. The awe enabled option is
enabled.
The instance will experience a predictable increase in query activity.
You plan to ascertain the appropriate time when the migration of the databases to a 64-bit SQL Server 2008
server
on the same hardware is beneficial.
You need to identify a data collector type that provides the appropriate information.
Which collector should you use?

A. SQL Trace collector
B. Performance Counters collector
C. Query Activity collector
D. T-SQL Query collector

Question #2
You administer SQL Server 2008 servers on a single site.
The site contains the following two SQL Server 2008 instances:
An Enterprise Edition server on a server that has a redundant array of independent disks (RAID) 10 disk system
A Standard Edition server on a server that has a RAID 5 disk system
Each instance hosts a single application. You need to recommend a high-availability solution for the site to meet the following business requirements:
The solution can be implemented on the existing systems.
The database is available with minimal downtime.
Data loss is minimal.
There is minimal effect on the existing system.
What are two possible ways to achieve this goal? (Each correct answer presents a complete solution.

A. Replication
B. Database snapshot
C. Failover clustering
D. Log shipping

Question #3
You administer a SQL Server 2008 instance named Instance1 at the New York central site. Your company has a sales team to fulfill purchase orders for customer requests.
The sales team uses portable computers to update data frequently in a local database. When the portable computers connect to the central site, the local database must be synchronized with a database named Sales.
You plan to create a replication model to replicate the local database to the Sales database.
The replication model must meet the following requirements:
Data conflicts are handled when multiple users update the same data independently.
The sales team cannot update sensitive data such as product price.
The sales team can synchronize data at scheduled times and on demand also.
You need to identify the best model to replicate data by using minimum development efforts.
What should you do?

A. Use SQL Server Integration Services (SSIS) to push data changes and pull updates to the Sales database along with the SSIS packages, on demand.
B. Use transactional replication along with each portable computer that is set up as a publisher.
C. Use snapshot replication along with each portable computer that is set up as a subscriber.
D. Use merge replication along with each portable computer that is set up as a subscriber.

Question #4
You administer a SQL Server 2008 infrastructure.
You plan to upgrade a Microsoft SQL Server 2005 instance to SQL Server 2008 by using an in-place
upgrade.
All user databases use the simple recovery model and have the PAGE_VERIFY option turned off.
You need to ensure that the database engine can identify I/O path errors and memory corruptions for the
data in tables.
Which two actions should you perform before the upgrade? (Each correct answer presents part of the
solution. Choose two.)

A. Execute the DBCC CHECKDB command by using the DATA_PURITY option.
B. Rebuild the clustered indexes and heaps.
C. Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION.
D. Rebuild the nonclustered indexes.
E. Set the database recovery model to FULL.
F. Set the PAGE_VERIFY database option to CHECKSUM.

Question #5
You administer a SQL Server 2008 instance.
The instance hosts a database that is used by a Web-based application. The application processes 15,000 transactions every minute. A table in the database contains a column that is used only by the application. This column stores sensitive data.
You need to store the sensitive data in the most secure manner possible.
You also need to ensure that you minimize the usage of memory space and processor time.
Which encryption type should you use?

A. Transparent data encryption
B. Symmetric key encryption
C. Certificate-based encryption
D. Asymmetric key encryption

Solutions: