Latest MCTS 70-432 Dumps Real Exam Download 101-110

Ensurepass

QUESTION 101

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 report which is often executed during

business time. There is a stored procedure that is used as the data source for the report.

You get a report from users saying that they receive the data returned by the report but the data is

inconsistent. As the technical support, you check and find that phantom reads cause this problem.

You must make sure that consistent data is returned by the report while not affecting other users.

So what action should you perform to achieve this goal?

 

A. You should configure the database for Read Committed Snapshot isolation.

B. You should modify the stored procedure to use the Snapshot isolation level

C. You should configure the database for Auto Update Statistics asynchronously

D. You should modify the stored procedure to use the Repeatable Read isolation level

 

Correct Answer: B

Explanation/Reference:

Phantom Reads occur when one transaction accesses a range of data more than once and a second

transaction inserts or deletes rows that fall within that range between the first transaction’s read attempts.

This can cause “phantom” rows to appear or disappear from the first transaction’s perspective.

SNAPSHOT

Specifies that data read by any statement in a transaction will be the transactionally consistentversion of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed dataas it existed at the start of the transaction.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data. During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted. The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database. A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.

A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

 

 

QUESTION 102

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. On two cluster-ready nodes, you intend to perform

an installation of a SQL Server 2008 mission-critical cluster. Constant availability is needed by the

missioncritical

cluster.

You have to configure the SQL Server cluster to failover and reduce the service disruption to the least.

Which failover option should you use?

 

A. You should allow failback only during non-business hours

B. You should immediately allow failback

C. You should prevent automatic failback

D. You should allow failback only during business hours

 

Correct Answer: C

Explanation/Reference:Failover/Failback Strategies

An overall cluster failover/failback policy is recommended. Failovers can be controlled in terms of a threshold, meaning that after a certain point, a resource will not be failed over. There are two levels of thresholds: resource and cluster. Depending on how the resource is configured, it can affect the group failing over to another node. In the event of a failover, the cluster group containing the SQL Server resources can be configured to fail back to the primary node when and if it becomes available again. By default, this option is set to off because usually there is no problem with continuing on the secondary node. This setting provides an opportunity to analyze and repair the problem on the failed node. It will also minimize the service disruption.

 

 

QUESTION 103

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 Dworks in the

instance. You are log shipping the Adventureworks database to a remote SQL Server 2008 instance.

The primary SQL Server instance required a restart during the weekend. But you notice that log shipping

has stopped working after the weekend. You have to check log shipping of the Adventureworks database

and find out the problem.

What should you do?

 

A. You should use the EXTENDED_LOGICAL_CHECKS option to execute a DBCC CHECKDB statement on the AdventureWorks database by.

B. You should identify whether the SQL Server Agent is started on the primary server.

C. You should identify whether the AdventureWorks database uses the Simple recovery model.

D. You should identify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server

 

Correct Answer: B

Explanation/Reference:

The logshipping is base on a set of SQL Server Agent jobs.

One on the primary: Bckup transaction log

Two on the secondary: Copy the transaction backup and Restore the transaction log.

So, verify the SQL Server Agent Service state.

 

 

QUESTION 104

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 Dworks in the

instance. The Dworks database has the Products table.

Now you have to replicate the Products table to the SQL Server instances at remote locations, so you

create a Merge Replication topology and a Publication to do this.

The Publication has a 21-day retention period. There is a user who goes on a business trip, 30 days later

he comes back and finds that data in his database is not the latest.

The Windows Event log states the following error message:

“Replication: expired subscription dropped.”

You must get the latest data in the user’s database and make sure that future data changes are

appropriately replicated.

So what action should you perform to achieve this goal?

 

A. You should recreate the publication

B. You should upload unsynchronized changes.

C. You should reinitialize the publication and generate a new snapshot at once.

D. You should upload unsynchronized changes, and then reinitialize the publication

 

Correct Answer: C

Explanation/Reference: Merge replication uses the publication retention period (the @retention and @retention_period_unit parameters of sp_addmergepublication (Transact-SQL)). When a subscription expires, it must be reinitialized, because metadata for the subscription is removed. Subscriptions that are not reinitialized are dropped by the Expired subscription clean up job that runs on the Publisher. By default, this job runs daily; it removes all push subscriptions that have not synchronized for double the length of the publication retention period. For example: If a publication has a retention period of 14 days, a subscription can expire if it has not synchronized within 14 days. If the Publisher is running SQL Server 2005 or a later version and the agent for the subscription is from SQL Server 2005 or a later version, a subscription only expires if there have been changes to the data in that subscription’s partition. For example, suppose a Subscriber receives customer data only for customers in Germany. If the retention period is set to 14 days, the subscription expires on day 14 only if there have been changes to the German customer data in the last 14 days. From 14 days to 27 days after the last synchronization, the subscription can be reinitialized. At 28 days after the last synchronization, the subscription is dropped by the Expired subscriptionclean up job. If a push subscription expires, it is completely removed, but pull subscriptions are not. You must clean up pull subscriptions at the Subscriber. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming).

 

 

QUESTION 105

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 in a high security environment, you manage a SQL Server 2008 cluster. You intend to configure and

use encrypted connections for the clustered virtual SQL Server.

You have to install a certificate. The cetification will be used for encryption.

What should you do?

 

A. In the cluster quorum drive, install the encryption certificate

B. In the SQL Server shared disk, install the encryption certificate

C. In the cluster group, install the encryption certificate.

D. On each individual node, install the encryption certificate

 

Correct Answer: D

Explanation/Reference:Encryption on a Cluster If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in thefailover cluster. For example, if you have a two-node cluster, with nodes named test1.your company. com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes. To configure the failover cluster for encryption, you can then select the ForceEncryption check box on the Protocols for <server> property box of SQL Server Network Configuration.

 

 

QUESTION 106

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.

The company stores clients data in DB1. Clients can access their profile data by using a Web application.

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

data files, log files and subsequent backups and so on are quite safe.

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 107

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 an internal application which uses

Analysis Services and Reporting Services. You use the application on the basis of SQL Server 2008.

According to the requirement of company security policy, the surface area for all the deployed components

of SQL Server 2008 should be configured. You have to implement the security policy and devise a method

to evaluate the security policy against other database servers.

What should you do?

 

A. You should create policies based on the appropriate facets. Apply the policies against a server group. The server group includes the database servers that need to be configured

B. You should create a Transact-SQL script based on the sp_configure stored procedure. Use a configuration server to run the script against a server group that includes the database servers that need to be configured

C. You should edit the RSReportServer.config configuration file. Distribute the file to all database servers that need to be configured

D. You should analyze your database servers by using the SQL Server Best Practices Analyzer (BPA). Implement the recommendations of the BPA

 

Correct Answer: A

Explanation/Reference:

The Enterprise Policy Management (EPM) Framework leverages and extends the new Microsoft SQL

Server 2008 Policy-Based Management feature across an entire SQL Server enterprise, including downlevel

instances of SQL Server such as SQL Server 2000 and SQL Server 2005.

In addition, the EPM Framework can be used to:

Automate the evaluation of policies against a defined set of SQL Server instances, including SQL

Server 2000 and SQL Server 2005.

Centralize the policy evaluation history to a single source for enterprise policy reporting.

Define best practices for implementing policy evaluation in extremely large enterprise environments.

In order to manage multiple servers, we have the ability in SQL Server 2008 to create server groups and

apply the policies to this groups

 

 

QUESTION 108

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 Service in the

instance. There is a database user who is named ServiceGreatUser.

A SQL Server login named ServiceGreatLogin maps to ServiceGreatUser in the Service database.

According to the requirement of the company CIO, the database user can only perform the two tasks

below:

the database user executes all stored procedures that currently exist in the Sales database

executes all stored procedures that will be created in the Sales database.

So what should you do to ensure this?

 

A. ServiceGreatUser should be added to the appropriate fixed database roles.

B. You should assign the appropriate object-level permissions to ServiceGreatUser

C. You should assign the appropriate server-level permissions to ServiceGreatLogin

D. You should assign the appropriate database-level permissions to ServiceGreatUser

 

Correct Answer: D

Explanation/Reference:

As the user ServiceGreatLogin is mapped to ServiceGreatUser in the database, all the correct rights and/ or roles should be assigned to ServiceGreatUser

 

 

QUESTION 109

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 Windows group named WiikigoService.

According to the security policy, members of this group are allowed to set up new connections to the SQL

Server instance. But this is can only be done during office time from 8:00-20:00. Other users may connect

to the SQL Server instance any time.

You write the following Transact-SQL statements.

CREATE TABLE Security.RestrictedLogonHours

(

Id int NOT NULL IDENTITY(1,1),

GroupName sysname NOT NULL,

RestrictedTimeStart time NOT NULL,

RestrictedTimeStop time NOT NULL,

CONSTRAINT RestrictedLogonHours_pk

PRIMARY KEY CLUSTERED(Id)

);

INSERT INTO Security.RestrictedLogonHours (

GroupName,

RestrictedTimeStart,

RestrictedTimeStop

)

VALUES (

‘WIIKIGOService’,

CAST(’07:00′ AS time),

CAST(’19:00′ AS time)

);

You need to implement the company security policy which is stored in the RestrictedLogonHours table.

What should you do?

 

A. You should create a SQL Server Agent job. The job causes the SQL Server Windows service to pause during non-business hours.

B. You should create a logon trigger. The trigger disallows the connection to the WIIKIGOService group during non-business hours

C. You should create a SQL Server Agent job. The job periodically looks for and kills connections made by the WIIKIGOService group during non-business hours.

D. You should create a policy. The policy uses a condition based on the Server Audit facet and disallows the connection to the WIIKIGOService group during non-business hours

 

Correct Answer: B

Explanation/Reference:

Logon triggers fire stored procedures in response to a LOGON event.

This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire

after the authentication phase of logging in finishes, but before the user session is actually established.

Therefore, all messages originating inside the trigger that would typically reach the user, such as error

messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon

triggers do not fire if authentication fails.

You can use logon triggers to audit and control server sessions, such as by tracking login activity,

restricting logins to SQL Server, or limiting the number of sessions for a specific login.

 

 

QUESTION 110

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 developer who is named

UserJack.

UserJack views the definitions of all database objects in a database to read data from all user-defined

tables, views, and table-valued functions. For UserJack, you have to assign the required permissions.

Besides this, you must make sure that other developers can also be given the same permissions, but this

should be achieved by executing as little Transact-SQL statements as possible.

In the options below, which Transact-SQL statements should you execute?

 

A. GRANT VIEW ANY DEFINITION TO UserJack;EXEC sp_addrolemember ‘db_datareader’, ‘UserJack’;

B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember ‘Developers’, ‘UserJack’;

C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember ‘Developers’, ‘UserJack’;

D. CREATE ROLE Developers;EXEC sp_addrolemember ‘sp_dbdatareader’, ‘Developers’;EXEC sp_addrolemember ‘sp_dbddladmin’, ‘Developers’;EXEC sp_addrolemember ‘Developers’, ‘UserJack’;

 

Correct Answer: C

Explanation/Reference:

1. Create ROLE: Roles are database-level securables. After you create a role, configure the databaselevel permissions of the role by using GRANT, DENY, and REVOKE. To add members to a database role, use the sp_addrolemember stored procedure.

2. VIEW DEFINITION:The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

3. GRANT SELECT: Grants permissions on a securable to a principal.

 

 

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