Microsoft 70-450 Exam Questions has already been updated recently! Braindump2go Provide you the Latest 70-450 Exam Dumps: 70-450 PDF and 70-450 VCE! Braindump2go helps you keep in step with Microsoft Official Exam Center!
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 and 70-450 Dumps PDF Download: http://www.braindump2go.com/70-450.html
QUESTION 101
You administer a SQL Server 2008 instance that hosts a large financial database.
The database has the following backup strategies:
– A full database backup is performed once a week.
– A differential backup is performed every day.
– A transaction log backup is performed every hour.
You plan to execute an end-of-year batch process that takes two hours to run.
The batch process will modify about five percent of data within the database.
You need to ensure that if the Finance department does not approve the batch process,
the batch operation can be rolled back in minimum possible time.
What should you do prior to starting the batch process?
A. Perform a differential backup.
B. Create a database snapshot.
C. Create a marked transaction. Perform a transaction log backup.
D. Record the time before the batch operation. Perform a transaction log backup.
Answer: B
Explanation:
Reasons to take database snapshots include:
– Maintaining historical data for report generation. Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter} for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance.
– Using a mirror database that you are maintaining for availability purposes to offload reporting.
Using database snapshots with database mirroring permits you to make the data on the mirror sewer accessible for reporting, Additionally, running queries on the mirror database can free up resources on the principal. For more information, see Database Mirroring and Database
Snapshots,
– Safeguarding data against administrative error.
– Before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Fleverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.
QUESTION 102
You administer a SQL Server 2008 database solution.
All data modifications are performed through stored procedures that use only the INSERT, UPDATE, or DELETE statements.
You are designing a backup strategy.
You need to ensure that the following business requirements are met:
– The backup strategy supports point-in-time recovery for failure at any time of day.
– The transaction log uses the least amount of disk space.
What should you do?
A. Use hourly database snapshots.
B. Use the full-recovery model along with differential backups.
C. Use the simple-recovery model along with differential backups.
D. Use the full-recovery model along with transaction log backups.
Answer: D
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.
QUESTION 103
You administer a SQL Server 2008 instance that hosts a database.
The backup strategy used by the database is as shown in the following table:
You run a batch process on Saturday that lasts from 21:00 hr to 23:00 hr.
You discover that the batch process is invalidated because a user has modified some data at 21:05 hr on the same day.
You need to restore the database to its state at the start of the batch process in the least possible time.
What should you do?
A. Restore the full database backup that was performed on Saturday.
B. Restore the full database backup that was performed on Thursday
Restore all transaction logs from the time of the full backup on Thursday and stop at
21:00 hr on Saturday.
C. Restore the full database backup that was performed on Saturday.
Restore all transaction logs from the time of the full backup on Saturday and stop at
21:00 hr on Saturday.
D. Restore the full database backup that was performed on Thursday.
Restore the differential backup that was performed on Friday.
Restore all transaction logs from the time of the differential backup on Friday and stop at
21:00 hr on Saturday.
Answer: D
Explanation:
The backup just before the batch takes 90 minutes to complete.
The full backup will grab any recently completed transactions as it nears completion, so
most- if not all-of the batch modifications will be included. It’s necessary to go back
further than that. Lesson: don’t run major changes during your backups.
QUESTION 104
You administer a SQL Server 2008 infrastructure.
Several production databases use Transparent database encryption.
You perform full database backups of the production databases as part of your backup strategy. You need to ensure that the database backups can be restored on another SQL Server 2008 instance if the original host server fails.
What should you do?
A. Back up the resource database.
B. Back up the server master key.
C. Back up the database master key.
D. Ensure that the instances use the same service account.
Answer: C
Explanation:
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the Triple DES algorithm and a user-supplied password.
When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database isnot encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
QUESTION 105
You administer a SQL Server 2008 Enterprise Edition instance that hosts a large database.
The database uses the simple-recovery model.
The database contains the following three additional filegroups:
– FileGroupA that is read/write
– FileGroupB that is read-only
– FileGroupC that is read-only
FilegroupB contains less critical data than FilegroupC.
You discover that the PRIMARY filegroup and FileGroupB have failed.
You need to recover the database in the minimum possible time.
You also need to ensure that data is available as soon as possible.
What should you do?
A. Perform a full database restore.
B. Perform a partial restore of the PRIMARY filegroup and FileGroupB from a partial backup.
C. Perform a partial restore of the PRIMARY filegroup and FileGroupA from a partial backup.
Perform an online recovery of FileGroupB, and then perform an online restore of FileGroupC.
D. Perform a partial restore of the PRIMARY filegroup and FileGroupA from a partial backup.
Perform an online recovery of FileGroupC, and then perform an online restore of FileGroupB.
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms190984.aspx
QUESTION 106
You administer a SQL Server 2008 instance.
The instance is located on a four-processor, quad-core server.
The server frequently experiences CPU pressure.
The instance contains a very large mission-critical database that is used continuously.
You need to ensure that online index rebuilds do not consume all available CPU cycles.
Which configuration option should you use?
A. affinity mask
B. affinity I/O mask
C. max degree of parallelism
D. optimize for ad hoc workloads
Answer: C
QUESTION 107
You administer a SQL Server 2008 infrastructure.
An instance hosts a business-critical database that must be continuously available to the users without data loss. The database includes Filestream data.
You need to implement a high-availability solution for the site.
Which solution should you use?
A. Failover clustering
B. Database snapshot
C. Asynchronous database mirroring
D. Synchronous database mirroring with a witness server
Answer: A
Explanation:
Failover clustering in SQL Server provides high–availability support for an entire SQL Server instance. For example, you can configure a SQL Server instance on one node of a failover cluster to fail over to any other node in the cluster during a hardware failure, operating system failure, or a planned upgrade.
A failover cluster is a combination of one or more nodes (servers) with two or more shared disks, known as a resource group. The combination of a resource group, along with its network name, and an internet protocol (IP) address that makes up the clustered application or server, is referred to as a failover cluster or a failover cluster instance. A SQL Server failover cluster appears on the network as if it were a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. A failover cluster appears on the network as a normal application or single computer, but it has additional functionality that increases its availability.
Failover clustering has a new architecture and new work flow for all Setup scenarios in SQL Server 2008. The two options for installation are Integrated installation and AdvancedlEnterprise installation. Integrated installation creates and configures a single-node SQL Server failover cluster instance. Additional nodes are added using add node functionality in Setup. For example, for Integrated installation, you run Setup to create a single-node failover cluster.
Then, you run Setup again for each node you want to add to the cluster. AdvancedlEnterprise installation consists of two steps. The Prepare step prepares all nodes of the failover cluster to be operational.Nodes are defined and prepared during this initial step. After you prepare the nodes, the Complete step is run on the active nods–the node that owns the shared disk–to complete the failover cluster instance and make it operational.
When to Use Failover Clustering
Use failover clustering to:
– Administer a failover cluster from any node in the clustered SQL Server configuration.
For more information, see Installing a SQL Server 2008 Failover Cluster.
– Allow one failover cluster node to fail over to any other node in the failover cluster configuration.
For more information, see Installing a SQL Server 2008 Failover Cluster.
– Configure Analysis Services for failover clustering. For more information, see How to: install
Analysis Services on a failover cluster.
– Execute full-text queries by using the Microsoft Search segilce with failover clustering.
For more information, see Using SQL Server Tools with Failover stering.
QUESTION 108
You administer four SQL Server 2008 instances.
Each instance hosts a single database application.
You plan to migrate all four instances to a new SQL Server failover cluster.
The four instances are configured as shown in the following table.
The four instances are fully optimized and have no spare CPU cycles or extra memory.
The new cluster will host all four databases on a single virtual cluster IP address.
You need to ensure that the new cluster is configured to handle the workload of all the database applications by using the minimum amount of hardware resources.
Which cluster configuration should you use?
A. Four-node active/active/active/active cluster that has each node containing a minimum of 9
processors and a 25-GB memory
B. Two-node active/active cluster that has each node containing a minimum of 9 processors
and a 25-GB memory
C. Two-node active/passive cluster that has each node containing a minimum of 18 processors
and a 50-GB memory
D. Four-node active/active/active/passive cluster that has each node containing a minimum of
18 processors and a 50-GB memory
Answer: C
Explanation:
Clustering provides protection against server hardware failure by using multiple systems (nodes) that share a disk array.
It does NOT protect against that disk array failing.
Active/Passive means that one node is hosting the SQL Server instance, while the other is sitting around waiting for the first one to fail. It’s the preferred solution, but means that both nodes have to be powerful enough to host the entire SQL server instance.
Two-node implementations are the most common and least expensive.
More nodes can be added to distribute the load.
Since a requirement mentioned is to minimize hardware usage, a two-node option is preferable. Active/Passive is generally recommended by Microsoft as well. Also, the question states that each instance is currently EXACTLY powerful enough to perform its duties without any overhead. This would make splitting the hardware into two active nodes impossible, as the values for processor and RAM don’t add up neatly enough to split the instances 50/50 based on existing requirements.
Required reading: Start at SQL-Server-Performance, move on to MSSQLTips, finish with the white paper (PDF).
http://www.sql-server-performance.com/2002/clustering-intro/
http://www.mssqltips.com/sqlservertip/1554/sql-server-clustering-active-vs-passive/
http://www.google.com/url?sa=t&source=web&cd=1&ved=0CBIQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F6%2F9%2FD%2F69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6%2FSQLServer2008FailoverCluster.docx&rct=j&q=sql%20server%202008%20cluster%20microsoft%20white%
20paper&ei=7T2rTOG8EcWBlAfN8fnEDA&usg=AFQjCNGI1hQIgs4JkiNAfD6zVvVl1-8UuA&cad=rja
QUESTION 109
You administer a SQL Server 2008 instance.
You plan to deploy a new database that has the following capacity requirements:
– 275 GB for the database data file
– 50 GB for the transaction log file
The storage array has six 100-GB disk drives available for the database.
The disks are attached to a redundant array of independent disks (RAID) controller that supports RAID levels 0, 1, 5, and 10.
The write performance of the transaction log needs to be maximized.
The database and transaction log files must be protected in the event of a drive failure.
You need to design the storage system.
Which storage configuration should you use?
A. A single RAID 5 volume
B. A single RAID 10 volume
C. A RAID 0 volume and a RAID 5 volume
D. A RAID 1 volume and a RAID 5 volume
Answer: D
QUESTION 110
You administer a SQL Server 2008 infrastructure.
An instance contains a database that includes a table named EmployeeData.
The EmployeeData table has a column named Profile.
The column is used to store Microsoft Office Word 2003 documents.
You need to minimize the space occupied by the Profile column.
You also need to maintain a reasonable performance to retrieve the content of the column.
What should you do?
A. Use ROW compression for the EmployeeData table.
B. Use PAGE LEVEL compression for the EmployeeData table.
C. Implement the Profile column as a CLR user-defined type.
D. Store the Profile column by using filestream storage, and use the NTFS file system
compression.
Answer: D
For those who feel the overwhelming anxiety before their 70-450 exam,Braindump2go Latest updated 70-450 Exam Dumps will help you Pass 100% in a short time preparation! 70-450 Exam Dumps PDF & VCE Full Version Instant Download!
