QUESTION 1
You are the administrator of a SQL Server 2008 instance. 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. Which 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 Instance1.
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 Instance1. At last re-establish the mirroring session.
Answer: A
QUESTION 2
You are the administrator of a SQL Server 2008 instance with a database named DB1. In the Services schema there is a table named Service.Table. The Services.Table has to be moved from the Sales schema to a new schema named Costs. Which Transact-SQL statement should you execute?
A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;
B. ALTER SCHEMA Costs TRANSFER Service.Table;
C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;
Answer: B
QUESTION 3
You are the administrator of a SQL Server 2008 instance with a database named DB1. Which contains the following stored procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID(‘Service.Table’) IS NOT NULL
04 DROP TABLE Service.Table;
05
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 …
12 GO
The following exception is raised when a user tries to invoke Procedure1,
“Msg 262, Level 14, State 1, Procedure Procedure1, Line 5
CREATE TABLE permission denied in database ‘DB1’.”
You should grant the user access to execute Procedure1, you must assign only the required permissions. Which action should you perform?
A. Between lines 01 and 02, you should insert the WITH EXECUTE AS ‘dbo’ clause.
B. Between lines 01 and 02, you should insert the EXECUTE AS USER = ‘dbo’ statement.
C. You should give the user the ALTER permission on the Service schema
D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.
Answer: A
QUESTION 4
You are the administrator of a SQL Server 2008 instance with a database named Dworks. The table below shows the two tables of the Dworks database:
There are two partitions in the BillHistory table. Partition1 is empty while partition2 contains all data.
You intend to move data from partition2 to the appropriate partition of the BillHistory table.
Partions2 contains data for the oldest month of the Bills table.
You are going to develop a process to achieve this.
You have to make sure that at the end of every month the process can be repeated.
A. Alter the partition function of the BillHistory table by using the split option.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
Alter the BillHistory table by using the split option.
B. Alter the partition function of the Bills table by using the split option.
Alter the Billstable by using the merge option.
C. Alter the partition function of the BillHistory table by using the split option.
Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.
Alter the BillHistory table by using the merge option.
Alter the Bills table by using the merge option.
D. Alter the partition function of the Bills table by using the split option.
Alter the BillHistory table by using the split option.
Alter the partition function of the BillHistory table by using the merge option.
Alter the Bill stable by using the merge option.
Answer: C
QUESTION 5
You are the administrator of a SQL Server 2008 instance. You notice that the application has a poor performance. On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler. You use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance. Which action should you perform to achieve this goal?
A. You should recapture the workload by using the data collector.
B. You should enable the XP_MSVER stored procedure on the local server.
C. You should enable the XP_MSVER stored procedure on the remote server
D. You should recapture the workload to a trace file by using SQL Profiler.
Answer: D
QUESTION 6
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. You have to make sure that stored procedures that do not contain this prefix cannot be created by you. What should you do?
A. A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.
B. A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.
C. A condition should be created. The condition targets the name of the stored procedure that is evaluated on change
D. A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.
Answer: B
QUESTION 7
You are the administrator of a SQL Server 2008 instance. There is a n On-Line Analytical Processing (OLAP) database named in the instance. The database contains a dimension table named Clients. Ever hour backup of data of the Clients table is performed. But the Clinets table contains redundant data. You must keep the disk space used to store the Clients table. Which compression technology should you use?
A. You should use row compression
B. You should use page compression
C. You should use backup compression
D. You should use windows NTFS file system compression
Answer: B
QUESTION 8
You are the administrator of a SQL Server 2008 instance with a database named Dworks, the Dworks database has the Products table. 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?
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
Answer: C
QUESTION 9
You are the administrator of a SQL Server 2008 instance. There is 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?
A. sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘default trace enabled’, 1;
GO
RECONFIGURE
GO
B. sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘common criteria compliance enabled’, 1;
GO
RECONFIGURE
GO
C. 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
D. 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
Answer: C
QUESTION 10
You are the administrator of a SQL Server 2008 instance. There is a database named DB1 in the instance. On DB1, quite 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?
A. You should verify that the simple recovery model is used by the database
B. You should verify that the full recovery model is used by the database.
C. You should verify that the checksum page verify option is used by the database
D. You should verify that the bulk-logged recovery model is used by the database
Answer: B
http://www.passleader.com/70-432.html
QUESTION 11
You are the administrator of a SQL Server 2008 instance. There is a database named DB1 in the instance. In the Services schema, There is a table named Service.Table. The Services.Table has to be moved from the Sales schema to a new schema named Costs. Which Transact-SQL statement should you execute?
A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;
B. ALTER SCHEMA Costs TRANSFER Service.Table;
C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;
Answer: B
QUESTION 12
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. Which 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.
Answer: A
QUESTION 13
You are the administrator of a SQL 2008 instance. There is an Internet Information Services application. This application will use anonymous access to access the SSAS instance. You must make sure that the application can access the SSAS instance. What should you do?
A. The Security\RequireClientAuthentication server configuration should be set to False
B. The Security\RequireClientAuthentication server configuration should be set to True.
C. The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.
D. The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration
Answer: A
QUESTION 14
You are the administrator of a SQL Server 2008 with a named Instance01. Instance01 contains a database which is named Dworks. The bulk-logged recovery mode is used by the Dworks database. You logship 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. You have to reduce the size of the log backup. Which 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.
Answer: B
QUESTION 15
You are the administrator 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. Which actions should you take? (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)
Answer: CD
QUESTION 16
You are the administrator of a SQL Server 2008 instance with a database named DB1. You have set the recovery model of DB1 to Full. You deploy a new process. The process modifies 10,000 records from the Accounts table at 18:00 every day. Any modification made by the process to the data must be reverted and database keeps online. You must make sure of this. Which strategy should you implement?
A. You should implement Differential backup
B. You should implement Database snapshots
C. You should implement Primary filegroup backup
D. You should implement Transaction log backup
Answer: B
QUESTION 17
You are the administrator of a SQL Server 2008 instance with a database named DB01. 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 must have given little permission as possible. The company CIO assigns this task to you. Which 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.
Answer: C
QUESTION 18
You are the administrator of a SQL Server 2008 instance. You have to perform the backup of the default trace according to the requirement of your failure recovery plan. The default trace is contained in the subdirectory. You have to backup the subdirectory. Which SQL Server database engine subdirectory should be backed up?
A. DATA
B. INSTALL
C. LOG
D. BINN
Answer: C
QUESTION 19
You are the administrator of a SQL Server 2008 instance. There is a database named DB1 in the instance. The backup strategy for DB1 has the following requirements:
— Every day at 9:00 full database backup to a file named cash.bak is performed; at 9:15 a transaction log backup to a file named cash_HHMM.trn is performed every 15 minutes.
— DB1 is being used in single-user mode.
— Today a user reports that a query deleted some data by accident, the query was executed at 9: 25.
— Since the deleted data is quite important, the data has to be restored to its original state. You must achieve this goal.
Which action should you perform to achieve this goal?
A. RESTORE DATABASE Cash
FROM DISK = ‘t:\backups\cash.bak’
WITH NORECOVERY;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0930.trn’
WITH RECOVERY,
STOPAT = ‘Mar 17, 2008 9:25AM’;
B. RESTORE DATABASE Cash
FROM DISK = ‘t:\backups\cash.bak’;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0915.trn’
WITH NORECOVERY;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0930.trn’
WITH RECOVERY,
STOPAT = ‘Mar 17, 2008 9:25AM’;
C. RESTORE DATABASE Cash
FROM DISK = ‘t:\backups\cash.bak’;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0915.trn’
WITH NORECOVERY;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0930.trn’
WITH RECOVERY;
D. RESTORE DATABASE Cash
FROM DISK = ‘t:\backups\cash.bak’
WITH NORECOVERY;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0915.trn’
WITH NORECOVERY;
RESTORE LOG Cash
FROM ‘t:\backups\cash_0930.trn’
WITH RECOVERY,
STOPAT = ‘Mar 17, 2008 9:25AM’;
Answer: D
QUESTION 20
You are the administrator of a SQL Server 2008 instance with a database named DB1 in the instance. At present about 90 GB of data is stored in DB1. You made some rough calculations that every day about 1 GB of data is modified or inserted. You set the recovery model of DB1 to Simple. According to the company requirements, during business hours, data loss of more than 120 minutes worth of transactions is unacceptable. You have to choose a backup strategy which must comply with the following two requirements:
— Using as little disk space as possible
— Complying with the company requirements.
Which action should you perform to achieve this goal?
A. A full database backup should be performed once daily. During business hour, a transaction log backup should be performed every two hours.
B. A full database backup should be performed once daily
C. A full database backup should be performed once daily. During business hours, a differential backup should be performed every two hours
D. Perform a full database backup should be performed once every week. A differential backup should be performed once daily. During business hours a transaction log backup should be performed every two hours.
Answer: C
Try Passleader 70-432 New Update Premium VCE Dumps And Pass 70-432 Exam Quickly