New Braindump2go 70-450 Exam Questions Updated Today! Want to know New Questions in 2015 70-450 Exam? Download Free Braindump2go 70-450 Exam Preparation Materials Now!
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 21
You administer a SQL Server 2008 infrastructure.
You plan to deploy a new SQL Server 2008 multi-node failover cluster.
The failover cluster uses a storage area network (SAN) that will use redundant array of independent disks (RAID) level 5.
You need to format the logical unit numbers (LUNs) for optimal performance of the database data files.
Which of the following is the best allocation unit size you should use?
A. 8-KB
B. 32-KB
C. 64-KB
D. 156-KB
Answer: C
Explanation:
Windows NT File System (NTFS) Allocation Unit
SCSI Drives: When you format the new drives in Disk Administrator, you should consider an allocation unit, or block size, that will provide optimal performance. Significant performance gains may be obtained by sizing this to a larger value in order to reduce disk I/Os; however, the default value is based on the size of the physical disk. The best practice for SQL Server is to choose 64 KB, because this reduces the likelihood of I/Os that span distinct NTFS allocations, which then might result in split I/Os. Keep in mind that although this information can be useful, the type of storage you are using (and in some cases also your backup software) will drive the format of your disks. If you are changing the block size on an existing system, be sure to run a baseline in your test environment and another after you have tested the changes.
http://technet.microsoft.com/nl-nl/library/cc966510(en-us).aspx
QUESTION 22
You administer a SQL Server 2005 instance.
The instance is configured to use the named pipes network communication protocol.
You plan to upgrade the instance to SQL Server 2008.
You need to ensure that the upgraded isntance can continue to use the named pipes network communication protocol.
You also need to ensure that the server uses the most secure authentication method available.
Which authentication method should you use?
A. NTLM authentication
B. Kerberos authentication
C. SQL Server authentication
D. Mixed-mode authentication
Answer: B
Explanation:
Kerberos is also supported with Named pipes protocol:
http://msdn.microsoft.com/en-us/library/cc280744.aspx
Very good discussion about this:
http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/1787d4ac-3f56-4702-922f90ecd8ea4384/
QUESTION 23
You administer a SQL Server 2008 instance.
Your company security policy is designed to prevent changes to the server to the server
configuration.
You plan to use Policy-Based Management Framework to implement the security policy.
You need to ensure that the policy is configured to meet the security requirement.
What should you do?
A. Use a domain account for the SQLAgent service.
B. Use the Local Service account for the SQLAgent service.
C. Enable the nested triggers server configuration option.
D. Disable the nested triggers server configuration.
Answer: C
Explanation:
http://technet.microsoft.com/en-us/library/bb510667.aspx
“If the nested triggers server configuration option is disabled, On change: prevent will not
work correctly. Policy-Based Management relies on DDL triggers to detect and roll back
DDL operations that do not comply with policies that use this evaluation mode. Removing
the Policy-Based Management DDL triggers or disabling nest triggers, will cause this
evaluation mode to fail or perform unexpectedly.”
QUESTION 24
You administer a SQL Server 2008 infrastructure.
The infrastructure contains SQL Server 2008 servers across four sites.
All sites are connected via a WAN link.
All sites use the same database application.
Your company has the following business requirements:
– All sites maintain data related to their site.
– Each site has access to data related to other sites.
– In the event of any site failure, database activity is redirected to
another site.
You need to implement a replication topology that meets the business requirements.
Which solution should you implement?
A. Snapshot replication
B. HTTP Merge replication
C. Peer-to-Peer replication
D. Transactional replication that uses updating subscriptions
Answer: C
Explanation:
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies
of data across multiple server instances, also referred to asnodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes
in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in
near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
QUESTION 25
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. Symmetric key encryption
B. Asymmetric key encryption
C. Certificate-based encryption
D. Transparent data encryption
Answer: A
Explanation:
Symmetric key encryption is the most commonly recommended option for SQL Server 2008 encryption.
It provides security without overly taxing the server like asymmetric encryption might.
Certificates have a similar architecture (public-private key).
TDE is not appropriate, as it would encrypt the entire database.
http://technet.microsoft.com/en-us/library/cc278098%28SQL.100%29.aspx?ppud=4
http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKe yEncryptionTutorial.aspx
QUESTION 26
You administer SQL Server 2008 instances at three site.
All sites share a mission-critical database.
The business requirements specify that users at each site must be able to perform the following tasks:
– Access and modify data on all sites with minimal latency.
– Minimize data loss in the event of a server failure.
You need to provide a high-availability solution that meets the business requirements.
Which solution should you implement?
A. Failover clustering
B. Peer-to-Peer replication
C. Asynchronous database mirroring without a witness server
D. Log shipping to servers at two of the sites to provide read-only copies of data
Answer: B
Explanation:
To minimize latency, users need a local copy of the database.
Failover clustering doesn’t provide multiple copies.
Database mirroring only supports one destination.
Database mirroring and log shipping leave the secondary or secondaries in a restoring
state, so data is not easily accessible.
Peer-to-peer replication is the only viable solution.
QUESTION 27
You deploy a new database application to a SQL Server 2008 instance on a server that has 4 processor cores and 64 GB of RAM.
The application uses partitioned tables.
During testing, deadlocks occasionally occur on queries that are attempting to read data.
You find out that the deadlocks are related to the table partitions.
You need to ensure that the application executes without deadlocks for the read queries.
You also need to ensure that dirty reads do not occur.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Disable partition-level lock escalation.
B. Move each partitioned table to its own filegroup.
C. Add additional database files to each partitioned table.
D. Move the clustered indexes for the partitioned tables to a separate filegroup.
Answer: A
Explanation:
Full-recovery Model
Description
– Requires log backups.
– No work is lost due to a lost or damaged data file.
– Can recover to an arbitrary point in time (for example, prior to application or user error).
Work loss exposure
– Normally none.
– Il the tail of the log is damaged, changes since the most recent log backup must be redone.
Recover to point in time S3.
– Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
http://msdn.microsoft.com/en-us/library/ms184286.aspx
QUESTION 28
You administer a SQL Server 2008 instance.
You plan to design a high-availability solution by using database mirroring.
The current application includes programming logic that will retry transactions if a failover occurs.
You need to configure a strategy that will provide the minimum amount of latency for committed transactions.
What should you do?
A. Pause database mirroring.
B. Configure manual failover role switching.
C. Configure forced service role switching.
D. Configure automatic failover role switching.
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms189852.aspx
QUESTION 29
You administer a SQL Server 2008 instance.
The instance hosts a new database application.
You plan to design the data security strategy for the application.
You will use Snapshot replication to replicate the data to another instance of SQL Server 2008.
You need to ensure that all stored data is encrypted by using the least amount of administrative effort.
What should you do?
A. Enable encrypted connections between the instances.
B. Enable Transparent data encryption for the MSDB database on the two instances.
C. Enable Transparent data encryption for the Publisher, Distribution, and Subscriber databases.
D. Enable certificate-based encryption for the Publisher, Distribution, and Subscriber databases.
Answer: C
QUESTION 30
You are a professional level SQL Sever 2008 Database Administrator.
A SQL Server 2008 instance at the New York central site is managed by you, and it is called Ins01. There is a sales team in your company, and their task is to finish purchase orders for customer requests.
The sales team updates data regularly in a local database by utilizing their portable computers. When the central site is connected by portable computers, a database named Sales will be synchronized with the local database.
A duplication model will be created to duplicate the local database to the Sales database.
The requirements listed below should be satisfied by the duplication model:
– First, when the same data is updated by multiple users independently, there should be no data conflicts.
– Secondly, sensitive data such as product price cannot be updated by the sales team.
– Thirdly, the data should be synchronized by the sales team not only at scheduled times but also on demand.
The best model should be identified to have data duplicated, and you should be reduced the development efforts to the least.
Which action will you perform?
A. You should utilize snapshot duplication along with each portable computer that is set up as
a subscriber.
B. You should utilize transactional duplication along with each portable computer that is set up
as a publisher.
C. You should utilize merge duplication along with each portable computer that is set up as a
subscriber.
D. You should utilize SQL Server Integration Services (SSIS) to promote the date modification
and pull updates to the Sales database along with the SSIS packages, on demand.
Answer: C
Explanation:
Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers.
The Subscriber synggronizes with the Publisher when connected to the network and exchanges all rows that h e changed between the Publisher and Subscriber since the last time synchronization occurred.
Merge replication is typically used in server-to-client environments.
All the 132 Questions and Answers in Braindump2go 70-450 Exam Dumps are the latest 70-450 Real Exam Questions not just 70-450 Practice Tests Questions! Braindump2gp Microsoft 70-450 Exam Dumps PDF&VCE Guarantees you 100% Pass 70-450 Exam! Braindump2go Can Provide the Latest 70-450 Dumps Questions from Microsoft Official Exam Center for You!
