Microsoft 70-450 Real Exam Questions
Exam 70-450: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008: counts as credit toward the following certification(s):Microsoft Certified IT Professional: Database Administrator 2008
1: You administer a SQL Server 2008 instance for your company. Your company has a team of database administrators. A team of application developers create SQL Server 2008 Integration Services (SSIS) packages on the test server in a shared project. One of the packages requires a fixed cache file. On completion of development, the packages will be deployed to the production server. Only the database administrators can access the production server. You need to ensure that the application developers can deploy the project successfully to the production server. What should you do?
A.Use the Import and Export Wizard to save packages.
B.Create a deployment utility for the SSIS project.
C.Create a direct package configuration for each package.
D.Create an indirect package configuration for all packages.
Correct Answers: B
2: You administer a SQL Server 2008 instance. You plan to deploy new SQL Server 2008 Integration Services (SSIS) packages that are created by the SQL Server Import and Export Wizard. The packages will connect to external data sources by using stored user names and passwords. You need to ensure that the user names and passwords are protected from access by unauthorized users. What should you do?
A.Use the SQL Server 2008 server to store the packages, and manage the security in the SQL Server 2008 server.
B.Ensure that no data is saved along with the packages, and save the files to the most secure directory.
C.Ensure that no sensitive data is saved along with the packages, and save the packages in the SQL Server 2008 server.
D.Set a common password to make all information in the package as secure as possible when each package is saved.
Correct Answers: A
3: You administer a SQL Server 2008 infrastructure. You administer two SQL Server 2008 instances named Instance1 and Instance2. Instance1 contains the Sales database, and Instance2 contains the Accounts database. A procedure in the Sales database starts a transaction. The procedure then updates the Sales.dbo.Order table and the Accounts.dbo.OrderHistory table through a linked server. You need to ensure that the transaction uses a two-phase commit. What should you do?
A.Configure the linked server to use distributed transactions.
B.Configure a Service Broker to enable the appropriate transaction control.
C.Ensure that the linked server is appropriately configured for delegation.
D.Ensure that the linked server is appropriately configured for impersonation.
Correct Answers: A
4: You administer a SQL Server 2008 infrastructure.
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 merge replication along with each portable computer that is set up as a subscriber.
B.Use snapshot replication along with each portable computer that is set up as a subscriber.
C.Use transactional replication along with each portable computer that is set up as a publisher.
D.Use SQL Server Integration Services (SSIS) to push data changes and pull updates to the Sales database along with the SSIS packages, on demand.
Correct Answers: A
5: 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.
Correct Answers: A
6: 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.
Correct Answers: C D
7: 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.
Correct Answers: A
8: 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
Correct Answers: D
9: 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.
Correct Answers: C
10: 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)
Correct Answers: A
Download | Password: ciscobibles.com