70-432-20170720

1.

1 point

You are the administrator of a SQL Server 2008 cluster in a high security environment,
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?

in the cluster group, install the encryption certificate.

In the SQL Server shared disk, install the encryption certificate

On each individual node, install the encryption certificate

In the cluster quorum drive, install the encryption certificate

2.

1 point

You are the administrator of a SQL Server 2008 instance. There is an internal application which uses
Analysis Services and
Reporting Services.
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?

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

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

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

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

3.

1 point

You are the administrator of a SQL Server 2008 instance.
There is a SQL Server 7.0 database. You upgrade the database to the SQL Server instance.
According to the company requirement, you have to make sure that suspect pages can be detected in the
database.
Which action should you perform to achieve this goal?

For the model database, the PAGE_VERIFY database option should be set to TORN_PAGE_DETECTION.

For the database, the database compatibility level option should be set to 10.

For the database, the PAGE_VERIFY database option should be set to CHECKSUM

For the database, you should turn on the TRUSTWORTHY database option

4.

1 point

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. Today you notice that applications that run on the
server
have poor performances. You doubt that this has something to do with table scans.
You have to capture the appropriate information by using an appropriate Windows System Monitor object.
Which performance object should you use?

You should use SQLServer:Buffer Manager

You should use SQLServer:Databases

You should use SQLServer:Access Methods

You should use SQLServer:Memory Manager

5.

1 point

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You are checking and configuring the security of the instance.
According to the requirement of the security audit policy, only successful and failed logon attempts are
recorded in log files and if records cannot be written to the log files, the SQL Server instance is shut down.
You must configure the SQL Server instance making it not violate the security audit policy.
Which Transact-SQL statements should you run?

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1; GO RECONFIGURE GO

CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH (ON_FAILURE = SHUTDOWN); GO CREATE SERVER AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON); GO ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON); GO

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'common criteria compliance enabled', 1; GO RECONFIGURE GO

CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH (ON_FAILURE = SHUTDOWN); GO CREATE DATABASE AUDIT SPECIFICATION Audit_Specification FOR SERVER AUDIT Srv_Audit ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP) WITH (STATE=ON); GO ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON); GO

6.

1 point

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 has a table which is named Table01 and a stored procedure named Procedure01.
Procedure01 choose data from Table01 by using a sp_executesql Transact-SQL statement.
You company security rules forbid users to access tables directly in any database.
Look at the exception below:
"Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'Table01', database
'DB1', schema 'dbo'."
The exception is raised when Procedure01 is executed by users. You have to make sure that a user
can successfully execute Procedure1 complying with the company rules.
Which action should you perform to achieve this goal?

You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement.

You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header

You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.

You should alter Procedure01 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql stored procedure.

7.

1 point

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. The server has a very large database named DB1.
An application which is constantly available uses DB1.
Now you get a report from users, in the report users complain that server has a poor performance. As the
technical support,
you have to improve the performance of the application by using the Database Engine Tuning Advisor.
Besides this, you must make sure that the action of analyzing the workload will not affect the performance
of the production server.
What should you do?

On the production server, use the dta.exe utility along with an XML input file.

On the local server, enable the XP_MSVER stored procedure

On the remote server, enable the XP_MSVER stored procedure.

You should configure a test server that has a similar hardware configuration. Use the dta.exe utility on the test server along with an XML input file

8.

1 point

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 which is named Sellings in the
instance.
The Sellings database contains a table named Productions. The table is used to stores information about all
types of products.
The Productions table is often queried by users on the basis of the RadioSize column. The RadioSize
column contains the NULL value
for all products other than Radios. Currently no index exists on the RadionSize column.
According to the requirement of the company CIO, you have to optimize the query performance and reduce
the effect on the disk space to the least.
Which action should you perform to achieve this goal?

On the Products table, you should create a view.

On the RadioSize column, you should create a unique clustered index

On the RadioSize column, you should create a filtered index

On the RadioSize column, you should create a clustered index

9.

1 point

You are the administrator SQL 2008 instance with a database named DB1. 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.
Which action should you perform to achieve this goal?

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

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

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

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

10.

1 point

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The Dworks database has a table named Orderthings. The Orderthings table is partitioned on the OrderId
column.
The first partition contains integer values greater than 100,000, while the second partition contains integer
values between 1 and 100,000.
You have to add a new partition. The new partition should contain integer values greater than 200,000.
What should you do?

A new partition function should be created

You should use a Split clause to change the existing partition function

You should change the existing partition scheme

You should use a Merge clause to change the existing partition function

11.

1 point

You are the administrator of a SQL Server 2008 instance.
There is a Windows group named Wiikigo\Service. 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 (
'WIIKIGO\Service',
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?

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

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

You should create a logon trigger. The trigger disallows the connection to the WIIKIGO\Service group during non-business hours

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

12.

1 point

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 a table named Orderthings. According to the company requirement, you have
to export all data from the Orderthings table to a file.
During the export, you must make sure that the data export process is saved for reuse and a Microsoft
Office Open XML document format is used.
What should you do?

You should run the SQL Import and Export Data Wizard and save the output to a file.

You should run the bulk copy program utility along with a format file and an output file.

You should run the bulk copy program utility along with an output file and no format file

You should run the SQLCmd utility and save the output to a file

13.

1 point

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. You are going to use the data collector to gather
performance data periodically on all instances.
You must store all collected data in the same database. This database is hosted on a single instance.
Every five hours, you have to collect and load performance data in the management data warehouse.
Which data collection process should you implement?

You should create a scheduled non-cached data collection.

You should create two different SQL Agent jobs. The two jobs are scheduled at the same time. One job uploads the data collection and the other job creates a data collection.

You should create a cached data collection

You should create an on-demand non-cached data collection

14.

1 point

You are the administrator of a SQL Server 2008 instance.
The company CIO wants you to configure FILESTREAM data and the two requirements below must be
met: you must enable
FILESTREAM for file I/O streaming access; remote client computers must be able to have streaming
access to FILESTREAM data.
You have to make sure that FILESTREAM data is enabled.
Which service should you configure?

You should configure SQL Server

You should configure Distributed File System

You should configure SQL Server VSS Writer

You should configure SQL Server Full Text

15.

1 point

You are the administrator 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.
Which Transact-SQL statements should you execute?

CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader', 'Developers';EXEC sp_addrolemember 'sp_dbddladmin', 'Developers';EXEC sp_addrolemember 'Developers', 'UserJack';

CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';

GRANT VIEW ANY DEFINITION TO UserJack;EXEC sp_addrolemember 'db_datareader', 'UserJack';

CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';

16.

1 point

You are the administrator of a SQL Server 2008 instance.
According to the company requirement, the names of all user-defined stored procedures
must contain the prefix usp_ on all instances.
Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created
by you.
What should you do?

A condition should be created. The condition targets the name of the stored procedure that is evaluated on change

A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.

A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.

A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.

17.

1 point

You are the administrator of a SQL Server 2008. There are queries that have an estimated execution cost
less than 3.
You intend to configure the instance, make it use a single thread for these queries.
Which sp_configure configuration option should you set?

You should set cost threshold for parallelism

You should set query governor cost limit

You should set precompute rank

You should set priority boost

18.

1 point

You are the administrator of a SQL Server 2008. According to the company requirement,
you are designing a consolidated repository of performance data.
You must make sure that the four requirements below are met:
1. the data collector is used to gather performance information
2. a single database stores performance information for all instances
3. performance information that is older than 15 days is deleted
4. reduce the administrative effort to manage performance to the least.
Which action should you perform to achieve this goal?

You should create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process, then use this process to store and delete performance data in a single database for all instances.

You should create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.

You should configure an automated server-side trace process on each instance, then use this process to store and delete performance data in a single database for all instances.

You should configure a management data warehouse process on each instance, then use this process to store and delete performance data in a single database for all instances.

19.

1 point

You are the administrator of a SQL Server 2008 instance.
You configure the SQL Server instance to use the -T1222 and -T1204 trace flags during startup.
You have to make sure that your failure recovery plan performs backup of the use of the trace flags.
Which action should you perform to achieve this goal?

You should backup the default.trc file

You should backup the master database

You should backup the SQL Server registry hive.

You should backup the resource database

20.

1 point

You are the administrator of a SQL Server 2008 instance.
You make sure that all SQL Server instances are consistently configured for naming conventions, security
settings,
force index creation and avoidance of data fragmentation.
Which action should you perform to achieve this goal?

You should use the Database Engine Tuning Advisor.

You should use the SQL Server Configuration Manager

In Microsoft SQL Server Management Studio, you should create a maintenance plan

In Microsoft SQL Server Management Studio, you should create a policy

21.

1 point

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 OrderIn in the instance.
The OrderIn database contains a table which is named OrdeSend.
A column named SendSite which is of the Geography data type has been added to the OrdeSend. The
OrderSend table contains no indexes.
On the SentSite point, you have to create a spatial index.
What should you do first?

For the OrderSend table, you should create a clustered index

First, you should copy the OrderSend data to a temporary table, then you should truncate the existing OrderSend table.

You must make sure that the SendSite column does not allow NULL values.

You should define a primary key for the OrderSend table

22.

1 point

You are the administrator of a SQL Server 2008.
According to the company requirement, you intend to perform an installation of an instance of SQL Server
Reporting Services (SSRS) to the same machine.
Now your company CIO wants version histories of all deployed reports can be kept.
As a technical support, what should you do to ensure this?

You should configure the SSRS database, make it use Native mode

You should configure the SSRS database, make it use SharePoint integrated mode

You should use the Internet Information Services default Web site to install the SSRS instance

You should use the http.sys listener to install the SSRS instance.

23.

1 point

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
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.
Which action should you perform to achieve this goal?

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

You should upload unsynchronized changes.

You should upload unsynchronized changes, and then reinitialize the publication

You should recreate the publication

24.

1 point

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
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?

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

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

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

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

25.

1 point

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 is available to a Windows group which is named WIIKIGO\Supervisors.
A Windows user named User1 is a member of the WIIKIGO\Supervisors group. WIIKIGO\Supervisors is a
member
of the db_owner role in the DB1 database.
You have to make sure that the SQL Server instance is (not?) available to User1.
In the DB1 database, which Transact-SQL statement(s) should you execute?

DROP LOGIN "WIIKIGO\User1";

EXEC dbo.sp_droprolemember 'db_owner', 'WIIKIGO\User1';

EXEC dbo.sp_revokedbaccess 'WIIKIGO\User1'; EXEC dbo.sp_revokelogin 'WIIKIGO\User1';

CREATE LOGIN "WIIKIGO\User1" FROM Windows;DENY CONNECT SQL TO "WIIKIGO\User1";

26.

1 point

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. The server hosts databases for several missioncritical
applications.
Microsoft SQL Server Management Studio executes queries and it has some effect. Now you intend to limit
the effect by using the Resource Governor.
You have to make sure that queries initiated through SQL Server Management Studio is less than 20
percent of CPU utilization, besides this, you must make sure that queries initiated by the mission-critical
applications can consume 100 percent of CPU utilization when required.
Which action should you perform to achieve this goal?

First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 20. Then assign this resource pool to the workload group used by SQL Server Management Studio.

You should alter the default resource pool and set the MAX_CPU_PERCENT option to 80. Then assign this resource pool to the workload group used by SQL Server Management Studio.

You should alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Then assign this resource pool to the workload group used by the mission-critical applications.

First, you should create a new resource pool and set the MAX_CPU_PERCENT option to 80. Then assign this resource pool to the workload group used by the mission-critical applications.

27.

1 point

You are the administrator of a SQL Server 2008 instance with a database named DB1.
On DB1, quite a few logged operations are performed.
Now according to the requirement of the company CIO, you must validate that the database can be
restored to a specific point in time.
Which action should you perform to achieve this goal?

You should verify that the checksum page verify option is used by the database

You should verify that the full recovery model is used by the database.

You should verify that the simple recovery model is used by the database

28.

1 point

You work in a Organization which is named Wiikigo Corp. The Organization uses SQL Server 2008.
You are the administrator of the Organization database.
Now you are in charge of a SQL Server 2008 instance.
Look at the following query:
SELECT s.*, i.*
FROM SensitiveTbl AS s
INNER JOIN InsensitiveTbl AS i
ON i.OrganizationName = s.OrganizationName
You use the above query to join two tables on a column named OrganizationName.
The following error is returned when you execute the query.
"Msg 468, Level 16, State 9, Line 17 Cannot resolve the collation conflict between
'SQL_Latin1_General_CP1_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation."
The ON clause of the query has to be modified so that it can perform a case-sensitive joinsuccessfully.
What should you do?

ON LOWER(i.OrganizationName) = LOWER(s.OrganizationName)

ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CS_AS

ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CI_AS

ON UPPER(i.OrganizationName) = UPPER(s.OrganizationName)

29.

1 point

You are the administrator of a SQL 2008 two cluster-ready nodes. You intend to perform an installation
of a SQL Server 2008 mission-critical cluster. Constant availability is needed by the mission-critical 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?

You should prevent automatic failback

You should allow failback only during non-business hours

You should immediately allow failback

You should allow failback only during business hours

30.

1 point

You are the administrator of a SQL Server 2008 instance with an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You have to make sure that the application can access the SSAS instance.
What should you do?

The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security \SecurityPackageList server configuration

The Security\RequireClientAuthentication server configuration should be set to False

The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security \SecurityPackageList server configuration.

The Security\RequireClientAuthentication server configuration should be set to True.

31.

1 point

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
You get a report from users saying that DB1 meets deadlock problems. As the technical support,
you have to capture the deadlock information to the SQL Server error log.
What should you do?

First you should set the appropriate trace flags as a startup parameter, and then restart the SQL Server instance

You should configure the data collector and make it capture the deadlock graphs

For the AdventureWorks database, enable Server Auditing

You should configure a SQL Profiler trace, and make it capture the deadlock graphs

32.

1 point

You are the administrator of a SQL Server 2008, with a report 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 have to make sure that consistent data is returned by the report while not affecting other users.
Which action should you perform to achieve this goal?

You should configure the database for Read Committed Snapshot isolation.

You should configure the database for Auto Update Statistics asynchronously

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

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

33.

1 point

You are the administrator of a SQL Server 2008 instance with a database named Service with
a database user 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.
What should you do to ensure this?

ServiceGreatUser should be added to the appropriate fixed database roles.

You should assign the appropriate database-level permissions to ServiceGreatUser

You should assign the appropriate server-level permissions to ServiceGreatLogin

You should assign the appropriate object-level permissions to ServiceGreatUser

34.

1 point

You are the administrator of a SQL Server 2008 instance which hosts several applications in your
company.
A job named DeliveryList is created by you. This job requires a file to be written to a file server. Because the
job
cannot access the file server, it fails to run. You intend to configure the SQL Server Agent service so only
the
SQL Server Agent service has read and write access to the file server.
Which account type should you use?

You should use local Service account

You should use network Service account

You should use local System account

You should use domain account

35.

1 point

You are the administrator in charge of two instances both of which run on the same computer. One is a
SQL Server 2008 instance;
another is a SQL Server 2005 instance.
There is a database named DB1 in the SQL Server 2008 instance. DB1 uses the Fulltext indexes. Several
records that
include the word "root" are added to DB1. An empty resultset is returned when the Fulltext index is queried
for the word "root."
You have to make sure the query can return records that contain the word "root".
What should you do?

The word "root" should be added to the stop list

You should terminate and restart the MSFTESQL service

The word "root" should be added to the thesaurus file

You should rebuild the full-text index