UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 : 070-447 Exam

Exam Number/Code: 070-447
Exam Name: UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005

“UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005″, also known as 070-447 exam, is a Microsoft certification.
Preparing for the 070-447 exam? Searching 070-447 Test Questions, 070-447 Practice Exam, 070-447 Dumps?

With the complete collection of questions and answers, Pass4sure has assembled to take you through 132 Q&A to your 070-447 Exam preparation. In the 070-447 exam resources, you will cover every field and category in MCITP helping to ready you for your successful Microsoft Certification.
Exam news
Are you upgrading from MCDBA certification?

• Visit the MCDBA to MCITP upgrade page for information

Exam 70-447: UPGRADE: MCDBA Skills to MCITP Database Administrator by Using Microsoft SQL Server 2005 became available in February 2006. This is a Microsoft Certified IT Professional (MCITP) Upgrade Exam for those who have obtained the MCDBA certification.

• This upgrade exam is available at 40 percent off retail price

Audience profile
Candidates for this exam are professional database administrators who optimize and maintain database solutions. They have three or more years dedicated to database work, which may include two or more of the following phases in the product life cycle: design, development, deployment, optimization, maintenance, or support. The typical work environment is an enterprise or a medium-sized organization. Candidates should be experienced in using Microsoft SQL Server 2005. Candidates for this exam should be experts in the following areas:

• Defining high-availability solutions

• Automating administrative tasks

• Defining security solutions

• Monitoring and troubleshooting the database server

• Designing and executing deployments

• Defining the infrastructure (such as storage, hardware, and number of servers or instances)

Credit toward certification
When you pass Exam 70-447: UPGRADE: MCDBA Skills to MCITP Database Administrator by Using Microsoft SQL Server 2005, as well as obtain the prerequisite MCTS: SQL Server 2005, you complete the requirements for the following certification:

• Microsoft Certified IT Professional: Database Administrator

Preparation tools and resources
To help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the “Skills measured” section.

Classroom training Microsoft E-Learning Microsoft Press books Practice tests
Course 2786: Designing a Microsoft SQL Server 2005 Infrastructure (two days)

Course 2787: Designing Security for Microsoft SQL Server 2005 (two days)

Course 2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005 (three days)

Course 2789: Administering and Automating Microsoft SQL Server 2005 Databases and Servers (one day)

Course 2790: Troubleshooting and Optimizing Database Servers Using Microsoft SQL Server 2005 (two days)
Collection 2786: Designing a Microsoft SQL Server 2005 Infrastructure (14-hour series)

Collection 2787: Designing Security for Microsoft SQL Server 2005 (12-hour series)

Collection 2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005 (16-hour series)

Collection 2789: Administering and Automating Microsoft SQL Server 2005 Databases and Servers (four-hour series)

Collection 2790: Troubleshooting and Optimizing Database Servers Using Microsoft SQL Server 2005 (12-hour series)
MCITP Self-Paced Training Kit (Exam 70-443): Designing a Database Server Infrastructure Using Microsoft SQL Server 2005 (ISBN: 9780735621732)

MCITP Self-Paced Training Kit (Exam 70-444): Optimizing and Maintaining a Database Administration Solution Using Microsoft SQL Server 2005 (ISBN: 9780735622548)

Inside Microsoft SQL Server 2005: The Storage Engine (ISBN: 9780735621053)

Inside Microsoft SQL Server 2005: T-SQL Querying (ISBN: 9780735623132)

Inside Microsoft SQL Server 2005: T-SQL Programming (ISBN: 9780735621978)

Inside Microsoft SQL Server 2005: Query Tuning and Optimization (ISBN: 9780735621961)

Microsoft SQL Server 2005 Analysis Services Step by Step (ISBN: 9780735621992)

Programming Microsoft SQL Server 2005 (ISBN: 9780735619234)

Microsoft SQL Server 2005 Administrator’s Pocket Consultant (ISBN: 9780735621077)

Microsoft SQL Server 2005 Administrator’s Companion (ISBN: 9780735621985)
MeasureUp
(Measureup.com)

Self Test Software
(Selftestsoftware.com)

Microsoft online resources
• Learning Plan: Get started with a step-by-step study guide that is based on recommended resources for this exam.

• SQL Server 2005 – Learning Portal: Find special offers and information on training and certification.

• Product information: Visit the SQL Server 2005 Web site for detailed product information.

• Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.

• TechNet: Designed for IT professionals, this site features how-to instructions, best practices, downloads, technical resources, newsgroups, and chats.

• MSDN: Designed for developers, the Microsoft Developer Network (MSDN) features code samples, technical articles, downloads, newsgroups, and chats.

Skills measured
This certification exam measures your ability to optimize and maintain a database administration solution and to design a database server infrastructure by using SQL Server 2005. Before taking the exam, you should be proficient in the job skills listed in the following table. The table shows the official Microsoft Learning products that may help you reach competency in the skills that are tested in the exam.

KEY: = The course provides a general introductory overview of this task. You will need to supplement the course with additional work. = The course includes some material to prepare you for this task. You will need to supplement the course with additional work. = The course includes material to prepare you for this task.
Skills measured by Exam 70-447 Course 2786 Course 2787 Course 2788 Course 2789 Course 2790
Section 1
Designing a Database Server Infrastructure
Design for capacity requirements.

• Analyze storage requirements.

• Analyze network requirements.

• Analyze CPU requirements.

• Analyze the current configuration.

• Analyze memory requirements.

• Forecast and incorporate anticipated growth requirements into the capacity requirements.

Specify software versions and hardware configurations.

• Choose a version and edition of the operating system.

• Choose a version of SQL Server 2005.

• Choose a CPU type.

• Choose memory options.

• Choose a type of storage.

Design physical storage.

• Design transaction log storage.

• Design backup file storage.

• Decide where to install the operating system.

• Decide where to place SQL Server service executables.

• Specify the number and placement of files to create for each database.

Design instances.

• Decide how many databases to create.

• Decide on the placement of system databases for each instance.

• Decide on the physical storage for the tempdb database for each instance.

• Decide on the number of instances.

• Decide on the naming of instances.

• Decide how many physical servers are needed for instances.

• Establish service requirements.

• Specify instance configurations.

Design a database consolidation strategy.

• Gather information to analyze the dispersed environment.

• Identify potential consolidation problems.

• Create a specification to consolidate SQL Server databases.

• Design a database migration plan for the consolidated environment.

• Test existing applications against the consolidated environment.

Designing Security for a Database Server Solution
Analyze business requirements.

• Gather business and regulatory requirements.

• Decide how requirements will impact choices at various security levels.

• Evaluate costs and benefits of security choices.

• Decide on appropriate security recommendations.

• Inform business decision-makers about security recommendations and their impact.

• Incorporate feedback from business decision-makers into a design.

Integrate database security with enterprise-level authentication systems.

• Decide which authentication system to use.

• Design Active Directory organizational units (OUs) to implement server-level security policies.

• Ascertain the impact of authentication on a high-availability solution.

• Establish the consumption of enterprise authentication.

• Ascertain the impact of enterprise authentication on service up-time requirements.

Develop Microsoft Windows server-level security policies.

• Develop a password policy.

• Develop an encryption policy.

• Specify server accounts and server account rights.

• Specify the interaction of the database server with antivirus software.

• Specify the set of running services, and disable unused services.

• Specify the interaction of the database server with server-level firewalls.

• Specify a physically secure environment for the database server.

Modify the security design based on the impact of network security policies.

Analyze the risk of attacks to the server environment and specify mitigations.

Design SQL Server service-level security.

• Specify logins.

• Select SQL Server server roles for logins.

• Specify a SQL Server service authentication mode.

• Design a secure HTTP endpoint strategy.

• Design a secure job role strategy for the SQL Server Agent Service.

• Specify a policy for .NET assemblies.

Design database-level security.

• Specify database users.

• Design schema containers for database objects.

• Specify database roles.

• Define encryption policies.

• Design DDL triggers.

Design object-level security.

• Design a permissions strategy.

• Analyze existing permissions.

• Design an execution context.

• Design column-level encryption.

• Design security for CLR objects in the database.

Designing a Physical Database
Modify an existing database design based on performance and business requirements.

• Ensure that a database is normalized.

• Allow selected denormalization for performance purposes.

• Ensure that the database is documented and diagrammed.

Design tables.

• Decide if partitioning is appropriate.

• Specify primary and foreign keys.

• Specify column data types and constraints.

• Decide whether to persist computed columns.

• Specify physical location of tables, including file groups and a partitioning scheme.

Design file groups.

• Design file groups for performance.

• Design file groups for recoverability.

• Design file groups for partitioning.

Design index usage.

• Design indexes for faster data access.

• Design indexes to improve data modification.

• Specify physical placement of indexes.

Create database change-control procedures.

• Establish where to store database source code.

• Isolate development and test environments from the production environment.

• Define procedures for moving from development to test.

• Define procedures for promoting from test to production.

• Define procedures for rolling back a deployment.

• Document the database change control procedures.

Designing a Database Solution for High Availability
Select high-availability technologies based on business requirements.

• Analyze availability requirements.

• Analyze potential availability barriers.

• Analyze environmental issues.

• Analyze potential problems related to processes and staff.

• Identify potential single points of failure.

• Decide how quickly the database solution must fail over.

• Choose automatic or manual failback.

• Analyze costs versus benefits of various solutions.

• Combine high-availability technologies to improve availability.

Develop a strategy for migration to a highly available environment.

• Analyze the current environment.

• Ascertain migration options.

• Choose a migration option.

Design a highly available database storage solution.

• Design the RAID solutions for your environment.

• Design a SAN solution.

Design a database-clustering solution.

• Design a Microsoft Cluster Service (MSCS) implementation.

• Design the cluster configuration of the SQL Server service.

Design database mirroring.

• Design server roles for database mirroring.

• Design the initialization of database mirroring.

• Design a test strategy for planned and unplanned role changes.

Design a high-availability solution that is based on replication.

• Specify an appropriate replication solution.

• Choose servers for peer-to-peer replication.

• Establish a strategy for resolving data conflicts.

• Design an application failover strategy.

• Design a strategy to reconnect client applications.

Design log shipping.

• Specify the primary server and secondary server.

• Switch server roles.

• Design an application failover strategy.

• Design a strategy to reconnect client applications.

Designing a Data Recovery Solution for a Database
Specify data recovery technologies based on business requirements.

• Analyze how much data the organization can afford to lose.

• Analyze alternative techniques to save redundant copies of critical business data.

Free PassGuide Practice Engine Demo Download Pass4sure offers free demos for each certification exam, including all IT vendors. You can check out the testing engine software, or pdf file question quality and usability of our practice exams before you decide to buy it. We are the only one site that offers demos for almost all IT certification exams.If you want to try p4s exam practice engine demo. http://demo.passguide.com/download

• Analyze how long the database system or database can be unavailable.

Design backup strategies.

• Specify the number and location of devices to be used for backup.

• Specify what data to back up.

• Specify the frequency of backup.

• Choose a backup technique.

• Specify the type of backup.

• Choose a recovery model.

Create a disaster recovery plan.

• Document the sequence of possible events.

• Create a disaster decision tree that includes restore strategies.

• Establish recovery success criteria.

• Validate restore strategies.

Designing a Strategy for Data Archiving
Select archiving techniques based on business requirements.

• Gather requirements that affect archiving.

• Ascertain data-movement requirements for archiving.

Design the format of archival data.

Plan for data archival and access.

• Specify the destination for archival data.

• Specify the frequency of archiving.

• Decide if replication is appropriate.

• Establish how to access archived data.

Design the topology of replication for archiving data.

• Specify the publications and articles to be published.

• Specify the distributor of the publication.

• Specify the subscriber of the publication.

Design the type of replication for archiving data.

Section 2
Optimizing the Performance of Database Servers and Databases
Troubleshoot physical server performance.

Troubleshoot instance performance.

Troubleshoot database performance.

Troubleshoot and maintain query performance.

• Identify poorly performing queries.

• Analyze a query plan to detect inefficiencies in query logic.

• Maintain and optimize indexes.

• Enforce appropriate stored procedure logging and output.

Troubleshoot concurrency issues.

Optimizing and Implementing a Data Recovery Plan for a Database
Diagnose causes of failures. Failure types include database failures, physical server failures, and SQL Server service failures.

Plan for fault-tolerance.

Recover from a failure of SQL Server 2005.

Recover from a database disaster.

• Plan a strategy.

• Restore a database.

• Configure logins.

• Recover lost data.

• Maintain server and database scripts for recoverability.

Salvage good data from a damaged database by using restoration techniques.

Designing a Strategy to Monitor and Maintain a Database Solution
Define and implement monitoring standards for a physical server.

• Establish the thresholds for performance.

• Establish the baselines for performance.

• Define which types of information to monitor on the physical server.

• Define traces.

• Set alerts.

• Set notifications.

Choose the appropriate information to monitor.

Create and implement a maintenance strategy for database servers.

• Create a job dependency diagram.

• Manage the maintenance of database servers.

Design a database maintenance plan.

Design a strategy to manage Reporting Services.

Designing a Database Data Management Strategy
Design and manage SQL Server Integration Services (SSIS) packages.

• Construct complex SSIS packages.

• Design security for accessing packages.

• Restart failed packages.

• Troubleshoot or debug packages.

• Deploy and move packages.

• Schedule package execution.

• Move packages to different servers.

Enforce data quality according to business requirements.

• Establish the business requirements for quality.

• Create queries to inspect the data.

• Use checksum.

• Clean the data.

Design data integrity.

• Reconcile data conflicts.

• Make implicit constraints explicit.

• Assign data types to control characteristics of data stored in a column.

Design a strategy to manage data across linked servers.

• Set up and manage linked servers.

Design a strategy to manage replication.

• Design alerts.

• Design a maintenance plan to monitor health, latency, and failures.

• Verify replication.

• Design a plan to resolve replication conflicts.

• Design a plan to modify agent profiles.

• Tune replication configuration.

Optimize a database control strategy to meet business requirements.

• Verify that database change-control procedures are being followed.

• Identify all database objects related to a particular deployment.

Designing a Strategy to Manage and Maintain Database Security
Perform a security audit of the existing security infrastructure based on the security plan.

• Analyze the physical server security.

• Compare the existing security infrastructure to business and regulatory requirements.

• Identify variations from the security design.

Maintain a server-level security strategy.

• Design a strategy to audit Windows account permissions.

• Design a strategy to audit SQL Server service access.

• Maintain a strategy to assign the appropriate minimum level of privileges.

• Maintain an encryption strategy that meets business requirements.

• Design a strategy to apply service packs and security updates.

• Configure the surface area.

Maintain a user-level security strategy.

• Verify the existence and enforcement of account policies.

• Verify SQL Server login authentication.

• Verify permissions on SQL Server roles and accounts.

Prepare for and respond to threats and attacks.

• Prepare for and respond to SQL Server injection attacks.

• Prepare for and respond to denial-of-service attacks that are specific to SQL Server.

• Prepare for and respond to virus and worm attacks that are specific to SQL Server.

• Prepare for and respond to internal attacks that are specific to SQL Server.
Topic 1, MainQuestions (120 Questions)

QUESTION 1
You work as DBA at Certkiller .com. You administer two Windows Server 2003 computers named Certkiller A and Certkiller B. You install SQL Server 2005 on both Certkiller A and Certkiller B to host a new company database. Certkiller A hosts a
read-write copy of the company database in which all changes are made. Certkiller B subscribes to a publication on Certkiller A and is only used for reporting. A Windows domain administrator provides you with a domain user account named SQLSRV to use as the security context for the SQL Server services. A password policy of 42 days exists in
the Default Domain Group Policy object (GPO). You install the database on Certkiller A
and Certkiller B and configure replication. Everything works fine for six weeks, but then
all SQL Server services fail. You need to correct the problem. What should you do?

A. Ask the Windows domain administrator to grant the Log on as service right to the
SQLSRV domain user account.
B. Configure the SQLSRV domain user account with a new strong password. Configure the new password in the properties of each SQL Server service that failed.
C. Create a local user account on Certkiller A named Certkiller A and a local user account
on Certkiller B named Certkiller B. Configure Certkiller A and Certkiller B to run under the context of the appropriate local user account.
D. Ask the Windows domain administrator to grant the SQLSRV domain user account membership in the Domain Admins group.

Answer: B

Explanation: The Default Domain Group Policy object requires that passwords are changed every 42 days. In order to correct this problem you have to change the password in the Active Directory AND configure the new password on both you SQL Servers to the newly changed password.

QUESTION 2
You are a database administrator of two SQL Server 2005 computers named Certkiller A and Certkiller B. You have a Microsoft .NET application that has been modified so that it now accesses a database on Certkiller B in addition to Certkiller
A. You do not want the
user application to connect directly to Certkiller B. You need to enable the data retrieval from Certkiller B while maintaining the ability to assign different permissions to different users who use the .NET application. What should you do?

A. Change the .NET application to define a new server connection to Certkiller B. B. Configure a linked server on Certkiller A to point to Certkiller B.
C. Change the stored procedures called by the .NET application to include the
OPENXML command.
D. Configure a linked server on Certkiller B to point to Certkiller A.

T

Answer: B

Explanation: SQL Server lets you access external data sources from your local
Transact-SQL code. You need to define a linked server for each external data
source you want to access and then configure the security context under which your distributed queries will run. After you create a linked server, you can use the
Transact-SQL OPENQUERY function to execute your distributed queries.

QUESTION 3
You are a database administrator for Certkiller .com. Your company uses a different company’s application that is based on SQL Server 2005 Standard Edition. The application executes a query that uses an index query hint. The index query hint is not suitable for your environment, but you cannot modify the query. You need to force the application to use a different query execution plan. What should you do?

A. Create a plan guide for the query. B. Clear the procedure cache.
C. Create a new covering index on the columns that the query uses. D. Update the statistics for all of the indexes that the query uses.
Answer: A Explanation:
Over the past few years, Microsoft SQL Server has increased its
presence in the industry and has reduced its TCO. This reduced TCO is a direct result, primarily, of the numerous self-tuning mechanisms built into Microsoft(r) SQL Server. These mechanisms automatically perform tasks that would otherwise have to be performed by experienced database administrators. One such
mechanism is the cost-based optimizer (CBO) that is used to dynamically generate query execution plans. The CBO probes several system-wide resource states and employs many complex, heuristical algorithms to generate the best possible plan for
a given query and the underlying table and index structures. This mechanism works well for the vast majority of user queries, but there are times when experienced
users need to force a particular query plan, based on some prior knowledge or insights into future uses.

QUESTION 4
You are a database administrator for Certkiller .com. Your SQL Server 2005 computer contains one user database that holds sales transaction information. Users report that the queries and stored procedures that they use every day are taking progressively longer to execute. You also notice that the amount of free disk space on the SQL Server computer
is decreasing. You need to create a maintenance plan to correct the performance and
storage problems. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

A. In the SQL Server Maintenance Plan Wizard, use the Check Database Integrity option.

B. In the SQL Server Maintenance Plan Wizard, use the Reorganize Index option.
C. In the SQL Server Maintenance Plan Wizard, use the Shrink Database option.
D. In the SQL Server Maintenance Plan Wizard, use the Clean Up History option.
E. In the SQL Server Maintenance Plan Wizard, use the Execute SQL Server Agent Job option.

Answer: B,C

Explanation: When you indexes are not heavily fragmented you can reorganize indexes, which uses few system resources and runs automatically online. In SQL Server 2005, certain operations such as large delete operations or -one-time data loads might leave database files larger than they need to be. SQL Server 2005 enables a DBA to shrink each file within a database to remove unused pages and regain disk space.

QUESTION 5
You are a database administrator for Certkiller .com. You are responsible for a SQL
Server 2005 database that has several indexes. You need to write a stored procedure that checks the indexes for fragmentation. Which Transact-SQL statement should you use?

A. DBCC INDEXDEFRAG
B. SELECT * FROM sys.dm_db_index_physical_stats
C. SELECT * FROM sys.indexes
D. DBCC DBREINDEX Answer: B
Explanation: The index_physical_stats function takes five parameters: database_id, object_id, index_id, partition_id, and mode. This function returns row size and fragmentation information.

QUESTION 6
You work as DBA at Certkiller .com. You administer two SQL Server 2005 computers named Certkiller A and Certkiller B. Certkiller A and Certkiller B contain a copy of a database named Sales. The database is replicated between Certkiller A and Certkiller B by using transactional replication. A full backup of each database is performed every night. Transaction log backups are performed every hour. Replication latency is typically less
than two minutes. One afternoon, the Sales database on Certkiller A becomes corrupted. You are unable to repair the database. The Sales database on Certkiller B is unaffected. You need to return the Sales database on Certkiller A to normal operation as quickly as possible. You must ensure a minimum loss of data and minimal impact to users of either server. What should you do?

A. Perform a full database backup on Certkiller B. Restore the backup to Certkiller A.
B. Restore the most recent full database backup and all transaction logs made since the full backup was made.
pass4sure 70-447

Questions and Answers : 132 Q&A Updated: October 8nd , 2008 Price: $129.99 $89.99

Free download:pass4sure MCITP 70-447 v2.93
Free download:testking MCITP 70-447 v2.93

PassGuide provides high-quality test materials, for example, Cisco CCNA CCNP CCIE, Comptia A + NETWORK + Security +, Juniper jncia, jncis, Vmware VCP-410,certification practice exams and so on.We are committed to give full refund to candidates if they fail the exam with use of our products.And we are confident to make such a guarantee. Buy Best Practice Exam,high-quality ,100% Guarantee ,Pls contact me,Mail:Sales@passguide.com

P4S Free Downloads

Type	Exam Braindumps	New Questions & Answers	Latest Updated	Available link
	All Pass4sure's Exam Pack	858	1 days ago	Download Free Testing Engines

PassGuide Braindumps-Free Test king Help You Quick Pass Any it Certifications Exams

Click links: www.testking.la/braindumps/free/down/crack/all/testking