Latest MCTS 70-432 Dumps Real Exam Download 161-170

Ensurepass

QUESTION 161

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB1 in the instance. VIP customer data of the company are stored in DB1.

The customers access their profile data by using a Web application.

You must ensure the security of the customer data, that is to say, even if the backup media is lost, you

must guarantee that data files, log files and subsequent backups and so on are as safe as possible.

So what action should you perform to achieve this goal?

 

A. For both the CustomerDB database and the master database, enable Transparent Database Encryption

B. You should use the built-in encryption functions to encrypt the sensitive data at the cell level

C. You should make the CustomerDB database accessible only through stored procedures and functions.

D. For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.

 

Correct Answer: D

Explanation/Reference:

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 (TDE) performs real-time I/O encryption and decryption of the dataand 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 asymmetric key protected by an EKM module. TDE protects data “at rest”, meaning the data and log files. It provides the ability 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 162

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named DB01 in the instance. The company CIO wants all the application developers

can capture traces to troubleshoot the application that uses the database DB1, and they can perform this

by using SQL Server Profiler.

But they have to be given as little permission as possible. The company CIO assigns this task to you.

So what action should you perform to achieve this goal?

 

A. All the SQL Server logins of the application developers have to be added to a fixed server role.

B. All the database users of the application developers have to be added to a fixed database role

C. All SQL Server logins of the application developers should be given the appropriate server-level permissions.

D. All database users of the application developers should be given the appropriate database-level permissions.

 

Correct Answer: C

Explanation/Reference:

SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the server.

ALTER TRACE

Grants or denies the ability to execute a server-side or SQL Server Profiler trace. Without this permission, the only logins capable of running traces are members of the sysadmin fixed server role. http://www.mssqltips.com/tip.asp?tip=1714

 

 

QUESTION 163

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 cluster.

According to the company requirement, the failover response of the cluster has to be tested. The company

assigns this task to you. On the cluster, you have to implement a manual failover.

What should you do? (choose more than one)

 

A. The contents of a shared folder on the active node should be encrypted by using Encrypting File System (EFS)

B. You should restore a backup to the active node

C. You should get rid of the shared array from the active node

D. From the active node, you have to uplug the network cable(s)

 

Correct Answer: CD

Explanation/Reference:

To implement a manual failover on one MSCS, you have to interrupt the correct run of the hardware. To do that, you have to break one of the following thinks on the active node: Power plug Heartbeat / Public network cables Storage links (shared array)

 

 

QUESTION 164

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance which is named Instance01.

Instance01 contains a database which is named Dworks. The bulk-logged recovery mode is used by the

Dworks database. You log ship the Dworks database through a WAN link. A scheduled job rebuilds the

indexes of the Dworks database. You find that the size of the log backups for log shipping has been

enlarged by the job.

Now you have to reduce the size of the log backup.

So what action should you perform to achieve this goal?

 

A. You should recreate the indexes.

B. You should compress the log file backups

C. You should make the Dworks database use the Full recovery mode

D. You should make the Dworks database use the Simple recovery mode.

 

Correct Answer: B

Explanation/Reference:

Use Compression option for the backup log. This will reduce the size of the database log backups COMPRESSION In SQL Server 2008 Enterprise and later versions only, specifies whether backup compression is performed on this backup, overriding the server-level default. At installation, the default behavior is no backup compression. But this default can be changed by setting the backup compression default server configuration option. For information about viewing the current value of this option, see How to: View Server Properties (SQL Server Management Studio). COMPRESSION Explicitly enables backup compression.

Note:

By default, when a backup is compressed, checksums are performed to detect media corruptions.

 

 

QUESTION 165

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance which is named Instance01.

Instance01 contains a database which is named SellingHelp. There isthe Products table in the SellingHelp database. Now according to the company requirement, the Products table has to be replicated to the SQL

Server instances installed in laptops computers. You have to create a Replication topology to perform this.

The Products table is updated from Instance01 when the laptops reconnect to the corporate network.

The Products table on Instance01 is frequently updated between reconnections.

You must make sure that you can implement the Replication topology along with the Subscription type successfully and meanwhile reduce the bandwidth usage to the least.

So what action should you perform to achieve this goal?

 

A. You should implement the Snapshot Replication topology along with a Push Subscription.

B. You should implement the Merge Replication topology along with a Pull Subscription.

C. You should implement the Snapshot Replication topology along with a Pull Subscription.

D. You should implement the Transactional Replication topology along with a Pull Subscription

 

Correct Answer: B

Explanation/Reference:

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 synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred. Merge replication is typically used in server-to-client environments.

 

 

QUESTION 166

You work for a company which uses SQL Server 2008. You are the administrator of the company

database and in charge of three SQL Server 2008 instances.

All the instances run on their independent server. The three instances are respectively named InstanceA,

InstanceB, and InstanceC. Between InstanceA and InstanceB, you configure a mirroring mission-critcal database.

InstanceA currently acts as the Principal and InstanceC acts as the witness.

You intend to apply a patch to both servers as soon as possible, the patch needs to restart the server.

You must make sure that the database is online on the partner that currently does not apply the patch and during this time the database does not failover to the other partner.

So what action should you perform to achieve this goal?

 

A. First, you should stop the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should resume the mirroring session. Fourth, you should manually failover the mirroring session. At last, you should apply the patch to the server that runs InstanceA.

B.            First, you should apply the patch to the server that runs InstanceB. Second you apply the patch to the server that runs InstanceA.

C. First, you should apply the patch to the server that runs InstanceB. Second you failover the mirroring session manually. At last apply the patch to the server that runs InstanceA

D. First, you should remove the mirroring session. Second you apply the patch to the server that runs InstanceB. Third, you should apply the patch to the server that runs InstanceA. At last re-establish the mirroring session.

 

Correct Answer: A

Explanation/Reference:

1. Status= SUSPENDED: The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.

2. Patch the target server

3. resume the mirrorin, in order to have the SYNCHRONIZED status

4. Make a failover from the source Instance A to InstanceB, the already patched server.

5. Apply the patch to the Instance A

 

 

QUESTION 167

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. In the database, a small percentage of data is modified everyday. Ever day in the morning, you have to create a read-only copy of the database for reporting purposes and make sure that you use as little disk space as possible. In the options below, which strategy should you use?

 

A. You should use database snapshots

B. You should use database backup and restore

C. You should use database backup and restore along with compression

D. You should use schema-bound views in a read-only database. The database resides on the same instance.

 

Correct Answer: A

Explanation/Reference:

This topic describes the database snapshot feature, which was new in Microsoft SQL Server 2005. Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots. A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner. Unlike default behavior for user databases, a database snapshot is created with the ALLOW_SNAPSHOT_ISOLATION database option set ON regardless of the setting of this option on the primary database or the model system database. Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

 

 

QUESTION 168

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance.

There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2 minutes and logs information to the text file.

You get a report from users saying that the sever doesn’t make response. As the technical support, you check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer functions.

You have to identify the reason why the SQL Server Agent service is unresponsive.

In the options below, which log should you check?

 

A. You should check SQL1.log

B. You should check SQLAGENT.OUT

C. You should check log_xx.trc

D. You should check ERRORLOG

 

Correct Answer: B

Explanation/Reference:

All the informations about SQL Server Agent error could be found in the SQLAGENT.OUT file.

 

 

QUESTION 169

You work in a company which uses SQL Server 2008. You are the administrator of the company

database.

Now you are in charge of a SQL Server 2008 instance which contains a database.

According to the company requirement, you have to move the application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008.

You have to monitor the SQL Server instance to record the use of features. These features will be discontinued.

What should you do?

 

A. The SQL Server 2008 Upgrade Advisor should be used.

B. The SQL Server Profiler which captures the SQL:BatchCompleted and Exception event classes should be used.

C. A SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes should be used

D. A SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes should be used.

 

Correct Answer: C

Explanation/Reference:

The Deprecation Final Support event class occurs when you use a feature that will be removed from the next major release of SQL Server. For greatest longevity of your applications, do not use features that cause the Deprecation Final Support event class or the Deprecation Announcement event class.

Modify applications that use final deprecation features as soon as possible.

 

 

QUESTION 170

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 cluster. According to the company requirement, the failover response of the cluster has to be tested. The company assigns this task to you.

On the cluster, you have to implement a manual failover. What should you do? (choose more than one)

 

A. The contents of a shared folder on the active node should be encrypted by using Encrypting File System (EFS)

B. You should restore a backup to the active node

C. You should get rid of the shared array from the active node

D. From the active node, you have to uplug the network cable(s)

 

Correct Answer: CD

Explanation/Reference:

To implement a manual failover on one MSCS, you have to interrupt the correct run of the hardware. To do that, you have to break one of the following thinks on the active node: Power plug Heartbeat / Public network cables Storage links (shared array)

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.