Do you want to pass Microsoft 70-450 Exam ? If you answered YES, then look no further. Braindump2go offers you the best 70-450 exam questions which cover all core test topics and certification requirements.All REAL questions and answers from Microsoft Exam Center will help you be a 70-450 certified!
Vendor: Microsoft
Exam Code: 70-450
Exam Name: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
Keywords: 70-450 Exam Dumps,70-450 Practice Tests,70-450 Practice Exams,70-450 Exam Questions,70-450 PDF,70-450 VCE Free,70-450 Book,70-450 E-Book,70-450 Study Guide,70-450 Braindump,70-450 Prep Guide
Microsoft 70-450 Dumps VCE Download: http://www.braindump2go.com/70-450.html
QUESTION 1
You administer a SQL Server 2008 instance.
The instance hosts a database configured by using high-safety mirroring operation mode along with a witness server.
The witness server is experiencing memory failure and will be offline for four hours.
You need to reconfigure the mirroring strategy to minimize the risk of database unavailability.
What should you do?
A. Pause database mirroring.
B. Use asynchronous operating mode.
C. Remove database mirroring.
D. Remove the witness server from the mirroring session.
Answer: D
QUESTION 2
You administer a SQL Server 2008 instance that hosts a database solution in a production environment.
The database solution uses several SQL Server Agent jobs to periodically transfer data from heterogeneous data sources to the production environment.
You also have a separate development environment that is used by several development teams. You design a test recovery plan for the database.
You plan to test the recovery plan by performing the following tasks:
– Restoring the database on a separate SQL Server 2008 instance in a development environment
– Running a number of unit tests.
You need to ensure that all database dependencies are included in the recovery plan.
Which two objects should you transfer from the production environment to the development environment? (Each correct answer presents part of the solution. Choose two.)
A. msdb database
B. Login accounts
C. master database
D. SQL Server Agent jobs
E. Custom error messages
Answer: AC
Explanation:
Correct: A (http://msdn.microsoft.com/en-us/library/ms187112.aspx)
Correct: C (http://msdn.microsoft.com/en-us/library/ms187837.aspx)
Incorrect: B (master database also includes the login accounts of SQL Server instance.
See http://msdn.microsoft.com/en-us/library/ms187837.aspx).
Incorrect: D (msdb database also includes the SQL Agent schedules.
See http://msdn.microsoft.com/en-us/library/ms187112.aspx)
QUESTION 3
You administer a SQL Server 2008 infrastructure .
You design a corporate backup and recovery strategy that has to be validated.
You need to ensure the successful recovery of any single database from a catastrophic failure without requiring a backup data center in a different location.
Which three tasks should you include? (Each correct answer presents part of the solution. Choose three.)
A. Store all backup media offsite.
B. Script SQL login accounts and credentials.
C. Install all SQL Server instances on a failover cluster.
D. Maintain one list of all Windows logins and passwords.
E. Document the administrative processes and application access requirements.
Answer: ABE
Explanation:
There isn’t enough information here to make any specific recommendations.
It’s just a best practices question.
Some dumps suggests other answers, but I don’t think a cluster would help in this scenario.
It’s hard to tell because the type of failure isn’t explained. Since a restore operation is needed,
it seems like a disk failure.
QUESTION 4
You administer a SQL Server 2008 infrastructure.
Your company requires capacity planning information.
You need to design a long-term infrastructure monitoring strategy.
Which two tasks should you include in your design? (Each correct answer presents part of the
solution. Choose two.)
A. Backup all databases every day.
B. Clear the system log and the application log every hour.
C. Review system monitor counters on a regular basis.
D. Baseline the system before you deploy production databases.
E. Create a maintenance plan that rebuilds indexes every week.
Answer: CD
Explanation:
Establishing a performance baseline and keeping an eye on fluctuations is an ideal way to determine current and future resource requirements. Backing up databases and rebuilding indexes are good ideas, but not related to this task.
QUESTION 5
You administer a SQL Server 2008 instance.
Customers report server performance degradation because of a newly implemented process.
You use Dynamic Management Views to verify that there are no long running queries.
You need to correlate the operating system performance data with the actual query execution trace by using minimum administrative effort.
What should you do?
A. Use Data Collector.
B. Use the SQLdiag.exe utility.
C. Use SQL Server Profiler and System Monitor.
D. Use SQL Server Profiler and the tracerpt.exe utility.
Answer: C
Explanation:
http://support.microsoft.com/kb/298475
http://msdn.microsoft.com/en-us/library/ms181091.aspx
QUESTION 6
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. T-SQL Query collector
C. Query Activity collector
D. Performance Counters collector
Answer: D
Explanation:
This question is referencing SQL 2008’s management data warehouse feature, which allows the collection of performance-related SQL statistics.
The T-SQL Query collector allows you to write a custom query.
The query is executed at intervals and the results are logged.
The SQL Trace collector leverages SQL Profiler to capture trace data.
The performance counters collector tracks OS and SQL-related counters in the same way PERFMON does.
The query activity collector tracks DMV results. It is generally not used.
As far as the question goes, it seems logical to pick the time when the server is experiencing the least activity.
The performance counters collection makes the most sense, as it’s possible to determine activity based on disk I/O, processor, memory, etc.
http://www.sql-server-performance.com/2008/Management-Data-Warehouse/
http://www.sql-server-
performance.com/articles/per/System_Data_Collection_Reports_Install_p1.aspx
QUESTION 7
You administer a SQL Server 2008 infrastructure.
You discover that an instance experiences performance degradation for the following reasons:
– Excessive CPU usage
– Server processes paging
– Deadlocks
You need to design a monitoring solution that can provide data, including detailed deadlock information, to monitor and troubleshoot performance issues.
You want to achieve this goal by using the minimum amount of administrative effort.
What tool should you use?
A. Extended Events
B. Resource Governor
C. Database Engine Tuning Advisor
D. Performance Monitor (SYSMON)
Answer: A
Explanation:
Is a broad error-handling system that can correlate data between SQL server, the OS, and other applications.
Events from SQL Server can be collected, then passed elsewhere (a “target”) for reporting or event response.
Resource governor is for limiting resource consumption.
The DTA (Database Tuning Advisor) makes index recommendations.
PERFMON monitors operating system and sql server counters.
Official source: http://msdn.microsoft.com/en-us/library/bb630354.aspx
Extra reading: http://www.sqlteam.com/article/introduction-to-sql-server-2008-extended-events
QUESTION 8
You administer a SQL Server 2008 instance.
You plan to design a monitoring solution for the instance to monitor object usage statistics.
The solution must identify a list of first 10 objects for each of the following components:
– Most frequently executed stored procedures and functions
– Long running Transact-SQL statements
You need to implement the monitoring solution to minimize performance effect by using the
least amount of administrative effort.
What should you do?
A. Use dynamic management views.
B. Use a System Monitor counter log.
C. Use a client-side SQL Server Profiler trace.
D. Use a server-side SQL Server Profiler trace.
Answer: A
Explanation:
Background info:
http://www.simple-talk.com/sql/performance/which-of-your-stored-procedures-are-using-the-most-resources/
http://www.sql-server-performance.com/2008/monitor-stored-procedure-performance/
QUESTION 9
You administer a SQL Server 2008 infrastructure.
A financial application uses several instances across multiple servers.
The majority of queries require distributed transactions across all servers.
The application administrator reports that the application experiences excessive deadlocks.
You need to design a data collector type that provides the appropriate information for remote
servers.
What collector should you use?
A. T-SQL Query collector
B. Query Activity collector
C. Performance Counters collector
D. A custom Query collector that uses WMI
Answer: C
Explanation:
Background info:
http://msdn.microsoft.com/en-us/library/bb933940.aspx
http://msdn.microsoft.com/en-us/library/ms190216.aspx
QUESTION 10
You administer a SQL Server 2008 infrastructure.
The instance contains a database required for the day-to-day business of your company.
Users experience slow response time when they execute reports.
You plan to design a performance monitoring strategy that captures and stores the following data:
– Executed Transact-SQL statements and query activity.
– Blocking and deadlock information.
– Counters for disk, CPU, and memory.
You need to implement the monitoring process by using the minimum amount of administrative effort.
What should you do?
A. Use the data collector
B. Use the client-side profiler trace.
C. Use the dynamic management views.
D. Use the System Monitor counter log trace.
Answer: A
Explanation:
The data collector is a component installed on a SOL Server server, running all the time or on a user-defined schedule, and collecting different sets of data. The data collector then stores the collected data in a relational database known as the management data warehouse.
The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited solely to performance data, unlike SOL Trace. w
The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data you collect by setting different retention periods for your data. The data collector supports dynamic tuning for data collection and is extensible through its API.
Instant Download Braindump2go New Released Microsoft 70-450 Exam Dumps PDF & VCE! Enjoy 1 year Free Updation! 100% Exam Pass Guaranteed Or Full Money Back!
