Microsoft Official Exam Center New Changed 70-450 Questions In PDF & VCE Format Offered By Braindump2go For Free Download (91-100)
Get Prepared with fully updated Microsoft 70-450 Real Exam Questions and Accurate Answers for 70-450 Exam Dumps. Braindump2go IT experts review the 70-450 newly added qustions and suggest Correct Microsoft 70-450 Exam Questions Answers in Real Time. 100% Pass easily!
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 91
You are a professional level SQL Sever 2008 Database Administrator.
A new database application is hosted by the instance.
The data security strategy should be designed for the application. The data should be duplicated to another instance of SQL Server 2008 by utilizing the Snapshot duplication.
Since you are the technical support of the company, you should make sure that all stored data should be encrypted, and the minimum amount of administrative effort should be utilized.
Which action will you perform?
A. Encrypted connections should be enabled between the instances.
B. Transparent data encryption should be enabled for the Publisher, Distribution, and Subscriber databases
C. Transparent data encryption for the MSDB database should be enabled on the two instances.
D. Certificate-based encryption should be enabled for the Publisher, Distribution, and Subscriber databases.
Answer: B
Explanation:
You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.
Transparent data encryption (TD E} performs real -time UO encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymn.@tric key protected by an EKM module. TDE protects data “at rest”, meaning the data and log files. It provides theability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
QUESTION 92
You are a professional level SQL Sever 2008 Database Administrator.
The solution is log-shipped for high-availability purposes.
The data files of the database reside on drive D, while the transaction log files of the database reside on drive E.
A restoring test plan should be implemented to satisfy the requirements listed below for the log-shipping solution.
– First, the secondary database is brought online in the shortest time.
– Secondly, the data is in a consistent state.
– Thirdly, the data loss is reduced to the least.
The first step for the recovery test plan should be identified in the event of drive D failure.
Which step should be performed?
A. You should bring the secondary database online.
B. You should execute the DBCC CHECKDB command along with the REPAIR_REBUILD
option against the primary database.
C. You should perform the tail-log backup of the primary database.
D. You should execute the DBCC CHECKDB command along with the REPAIR_ALLOW_DATA_LOSS option against the primary database.
Answer: C
Explanation:
This topic is relevant only for databases that are using the full or bulk-logged recovery models.
In most cases, under the full or bulk-logged recovery models, SOL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.
SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log
chain intact.
When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure. Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or it you are moving or replacing {overwriting) the database. Also, it the log tiles are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost.
QUESTION 93
You are a professional level SQL Sever 2008 Database Administrator.
Your company wants to enforce naming standards for the database objects.
A solution should be designed by you to enforce naming standards, and the least amount of administrative and programming efforts should be utilized.
Which action should you perform?
A. Policy-Based Management policies should be created, and then the policies should be
distributed by utilizing a configuration server.
B. Event notifications should be utilized to handle the DDL events.
C. DDL triggers should be created for one of the instances, and then SQL Server
Management Objects (SMO) should be utilized to script the definition of the DDL triggers.
At last, the script should be run by utilizing Microsoft Windows PowerShell on all instances.
D. DDL triggers should be created for one of the instances, and then Microsoft SQL Server
Management Studio (SSMS) should be utilized to script the definition of the DDL triggers.
At last, the script should be run on all instances by utilizing the registered servers node
from SSMS.
Answer: A
Explanation:
Policy-Based Management is a system for managing one or more instances of SOL Server 2008.
When SQL Server policy administrators use Policy-Based Management, they use SOL Server
Management Studio to create policies to manage entities on the server, such as the instance of
SOL Server, databases, or other SOL Server objects.
Policy-Based Management has three components:
– Policy management
– Policy administrators create policies.
– Explicit administration
Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.
Evaluation modes
There are four evaluation modes, three of which can be automated:
On demand. This mode evaluates the policy when directly specified by the user.
On change: prevent. This automated mode uses DDL triggers to prevent policy violations.
Important:
It the nested triggers server configuration option is disabled, OI1 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. Fiemoving the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.
On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.
On schedule. This automated mode uses a SOL Server Agent job to periodically evaluate a policy.
When automated policies are not enabled, Policy-Based Management will not affect system performance.
QUESTION 94
You administer a SQL Server 2008 instance that hosts a large database.
The following backup strategy is used for the database:
– A full database backup is performed at 02:00 hr every Monday.
– A differential backup is performed at 23:00 hr every day.
– A transaction log backup is performed on an hourly basis.
– A power failure on Thursday causes the SQL Server 2008 server to restart at 09:15 hr.
Fifteen minutes after the server restarts, the users report that they are unable to execute certain queries that access customer data.
You discover that the customer data is unmodified after the power failure.
When you execute the DBCC CHECKDB command on the database, you receive the following error message:
You need to ensure that the data is available as quickly as possible with minimal effect on users.
What should you do?
A. Drop and recreate the PK index.
B. Drop and recreate the NCIX index.
C. Restore the latest full database backup.
Restore all transaction log backups from the latest full database backup.
D. Restore the latest full database backup.
Restore the latest differential backup.
Restore all transaction log backups from the latest differential backup.
Answer: B
Explanation:
The error refers to index 2. Since the clustered index on a table is always index 1, this means there’s a problem with the nonclustered index. Dropping and recreating this may fix the seek issue quickly as indicated in the requirements.
In situations such as this, it’s always a better idea to restore from backups. There’s no way of telling how bad the damage is.
However, to get the database running as quickly as possible, D) is the immediate solution.
Any steps taken after that aren’t relevant.
QUESTION 95
You administer a SQL Server 2008 instance.
The instance runs on a computer that has the following features:
– A 64-GB RAM
– Four quad-core processors
– Several independent physical raid volumes
You plan to implement a transactional database on the instance.
The database is expected to have a high volume of INSERT, UPDATE, and DELETE activities. The activities include creation of new tables.
You need to optimize the performance of the database by maximizing disk bandwidth and reducing the contention in the storage allocation structures.
What should you do?
A. Create multiple data files for the database.
B. Place database and log files on the same volume.
C. Configure the affinity mask option appropriately.
D. Configure the affinity I/O mask option appropriately.
Answer: A
QUESTION 96
You administer a SQL Server 2008 instance.
You deploy a new database named Engineering.
The Engineering database manages large documents that will be revised occasionally.
You need to design a table structure that allows fast read access.
You also need to minimize storage space requirements.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Use NTFS file system compression on the volume.
B. Use row-level compression on the document table.
C. Use varbinary(MAX) data type with FILESTREAM storage.
D. Enable row-level compression on all columns that use the vardecimal() data type.
Answer: AC
QUESTION 97
You administer a SQL Server 2008 Reporting Services (SSRS) instance.
You plan to design an authentication method for SSRS reports that use SQL Server data connections.
You need to ensure that all SSRS reports communicate to the SQL Server instance by using the Kerberos authentication protocol.
What should you do?
A. Configure the default Web site on the instance to use the Integrated Security type.
B. Configure the application pool used by the default Web site to use a domain account.
C. Configure the default Web site on the instance to use the Secure Sockets Layer (SSL)
protocol.
D. Register a service principal name (SPN) in the Active Directory directory service for the
SSRS instance.
Answer: D
QUESTION 98
You administer a SQL Server 2008 infrastructure.
You implement log shipping for several databases on three SQL Server instances.
The logs are shipped to a fourth SQL Server instance.
You plan a manual failover.
You need to ensure that the database applications use the secondary server after failover.
You also need to ensure that users can access the most recent data.
Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)
A. Redirect client computers to the secondary instance.
B. Back up all databases on the secondary instance.
C. Copy all log shipping network shares to the secondary instance.
D. Back up the tail of the transaction log of primary databases by using the WITH
NORECOVERY option.
E. Apply any unapplied transaction log backups in sequence to each secondary database by
using the WITH RECOVERY option on the last log.
Answer: ADE
Explanation:
Official source: http://msdn.microsoft.com/en-us/library/ms191233.aspx
While the maturation of clustering and mirroring in SQL Server 2008, log shipping doesn’t see the widespread usage it once did. Jose Baretto’s blog has a nice concise explanation of the steps in a log shipping failover. They’re out of order in the above question, which can make thinking through the process a bit more difficult.
1. Backup of the tail of the log on the primary using NORECOVERY (assuming this is possible).
2. Restore that log backup as the final link to the secondary, and specify RECOVERY.
3. Point any applications to the now-restored secondary server.
QUESTION 99
You administer a SQL Server 2008 instance that hosts a large database.
The database experiences high volumes of queries and transactions that are processor-intensive.
You plan to implement an incremental backup strategy for the database.
You also plan to use backup compression.
You need to ensure that the backup jobs have a minimal effect on the performance of the server. What should you do?
A. Use database snapshots.
B. Use the Resource Governor.
C. Reconfigure the Affinity I/O Mask configuration option.
D. Spread the database backup across multiple backup devices.
Answer: B
Explanation:
Resource governor can be used to limit the resource usage of a given session.
http://www.sql-server-performance.com/2008/resource-governor-in-sql-server-2008/
Database snapshots are not a backup methodology, as they rely on the existing database.
The affinity mask I/O option is for binding disk operations to certain CPUs and was covered earlier.
Spreading backups across multiple devices is supported in SQL Server 2008.
If the resource in demand was disk I/O, this would be a viable solution.
QUESTION 100
You administer a SQL Server 2008 instance.
The instance hosts a database that contains sensitive data.
You plan to implement a database backup strategy for the database.
You need to ensure that all backups of the database are encrypted.
You also need to prevent unauthorized access of the backups.
What should you do?
A. Use Windows BitLocker Drive Encryption technology.
B. Use Transparent database encryption.
C. Use the BACKUP statement along with the PASSWORD option.
D. Use the BACKUP statement along with the MEDIAPASSWORD option.
Answer: B
Explanation:
Official source: http://msdn.microsoft.com/en-us/library/bb934049.aspx
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!