Copy and Edit

SQL Server 2008 MCITP 70-432 Exam Review

Description

Content available in printed form from PassLeader: http://www.passleader.com/70-432.html
Bob Balsman
Quiz by Bob Balsman, updated more than 1 year ago
Bob Balsman
Created by Bob Balsman almost 8 years ago
206
1
0

Resource summary

Question 1

Question
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?
Answer
  • 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.
  • First, you should apply the patch to the server that runs InstanceB. Second you apply the patch to the server that runs InstanceA.
  • 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.
  • 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.

Question 2

Question
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?
Answer
  • ALTER TABLE Service.Table SWITCH TO Costs.Table1;
  • ALTER SCHEMA Costs TRANSFER Service.Table;
  • ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
  • ALTER AUTHORIZATION ON Service.Table1 TO Costs;

Question 3

Question
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?
Answer
  • Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
  • Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
  • You should give the user the ALTER permission on the Service schema.
  • You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

Question 4

Question
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.
Answer
  • 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.
  • Alter the partition function of the Bills table by using the split option. Alter the Billstable by using the merge option.
  • 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.
  • 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.

Question 5

Question
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?
Answer
  • You should recapture the workload by using the data collector.
  • You should enable the XP_MSVER stored procedure on the local server.
  • You should enable the XP_MSVER stored procedure on the remote server.
  • You should recapture the workload to a trace file by using SQL Profiler.

Question 6

Question
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?
Answer
  • A policy should be created. The policy targets the name of the 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.
  • A condition should be created. The condition targets the name of the stored procedure that is evaluated on change.
  • A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.

Question 7

Question
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 Clients table contains redundant data. You must keep the disk space used to store the Clients table. Which compression technology should you use?
Answer
  • You should use row compression
  • You should use page compression
  • You should use backup compression
  • You should use windows NTFS file system compression

Question 8

Question
You are the administrator 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. Which log should you check?
Answer
  • You should check SQL1.log
  • You should check SQLAGENT.OUT
  • You should check log_xx.trc
  • You should check ERRORLOG

Question 9

Question
You are the administrator of a SQL Server 2008 instance. A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be truncated after you review it. All information produced by a job must be available for viewing. Which action should you perform to achieve this goal?
Answer
  • You should enable notifications to the Windows application event log when the job completes.
  • You should enable write OEM file.
  • You should enable all job steps, making them send the output to a file.
  • You should include execution trace messages in the SQL Agent Error log.

Question 10

Question
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?
Answer
  • You should verify that the simple recovery model is used by the database.
  • You should verify that the full recovery model is used by the database.
  • You should verify that the checksum page verify option is used by the database,
  • You should verify that the bulk-logged recovery model is used by the database.

Question 11

Question
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?
Answer
  • A policy should be created. The policy targets the name of the 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.
  • A condition should be created. The condition targets the name of the stored procedure that is evaluated on change.
  • A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.

Question 12

Question
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?
Answer
  • You should configure SQL Server VSS Writer.
  • You should configure Distributed File System.
  • You should configure SQL Server.
  • You should configure SQL Server Full Text

Question 13

Question
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?
Answer
  • The ure the SQL Server service torun un server configuration should be set to False.
  • The Security\RequireClientAuthentication server configuration should be set to True.
  • The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.
  • The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.

Question 14

Question
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?
Answer
  • You should recreate the indexes.
  • You should compress the log file backups.
  • You should make the Dworks database use the Full recovery mode.
  • You should make the Dworks database use the Simple recovery mode.

Question 15

Question
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)
Answer
  • The contents of a shared folder on the active node should be encrypted by using Encrypting File System (EFS)
  • You should restore a backup to the active node
  • You should get rid of the shared array from the active node
  • From the active node, you have to uplug the network cable(s)

Question 16

Question
You are a database developer for your company. You are creating a parameterized stored procedure that will query the data and return rows that include any verb tense of the verbs supplied as input to the stored procedure. The query should also return rows that contain both plural and singular forms of nouns. The data is included in several varchar(max) and varbinary(max)columns. You need to create the stored procedure to fulfill these requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
Answer
  • Use the LIKE operator.
  • Use the CONTAINS predicate.
  • Create a full-text index on the appropriate column or columns.
  • Create a nonclustered index on the appropriate column or columns.

Question 17

Question
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?
Answer
  • All the SQL Server logins of the application developers have to be added to a fixed server role.
  • All the database users of the application developers have to be added to a fixed database role.
  • All SQL Server logins of the application developers should be given the appropriate server-level permissions.
  • All database users of the application developers should be given the appropriate database-level permissions.

Question 18

Question
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?
Answer
  • DATA
  • INSTALL
  • LOG
  • BINN

Question 19

Question
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?
Answer
  • 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';
  • 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';
  • 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;
  • 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';

Question 20

Question
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?
Answer
  • A full database backup should be performed once daily. During business hour, a transaction log backup should be performed every two hours.
  • A full database backup should be performed once daily.
  • A full database backup should be performed once daily. During business hours, a differential backup should be performed every two hours.
  • 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.

Question 21

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. The latest differential backup is performed at 15:30, the full back up was performed at 13:30, and database snapshots were created at 16:30 and at 17:30. The backups and the database snapshots are stored on a different disk from the database files. At 17:05, the hard disk containing the database files fails at 17:02 hours. You must restore DB1 while reduce data loss to the least. Which action should you perform to achieve this goal?
Answer
  • You should restore the full backup
  • You should restore the database snapshot from 16:30 hours.
  • You should restore the database snapshot from 17:30 hours
  • You should restore both the full backup and the differential backup.

Question 22

Question
You are the administrator of a SQL Server 2008 instance. There is a database named DB1 in the instance. There are data file and the transaction log file on the E: drive. You find that there is only 6% spare space. Both files have to be moved to the V: drive. Which procedure should you use?
Answer
  • You should run the following Transact-SQL statement: ALTER DATABASE DB1 SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = 'v:\SQLServer\DB1_Data.mdf'); ALTER DATABASE DB1 SET MULTI_USER;
  • You should terminate the SQL Server Service. Then move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_DB @DBname = N'DB1',@filename1 = N'v:\SQLServer\DB1_Data.mdf',@filename2 = N'v:\SQLServer\DB1_Log.ldf';
  • You should run the following Transact-SQL statement: ALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Data, FILENAME = 'v:\SQLServer\DB1_Data.mdf'); ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Log, FILENAME = 'v:\SQLServer\DB1_Log.ldf'); ALTER DATABASE DB1 SET ONLINE;
  • You should terminate the SQL Server service. Then move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_DB @DBname = N'DB1',@physname = N'v:\SQLServer\DB1_Data.mdf';

Question 23

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. A backup of DB1 is performed every day. You have to minimize the size of the full database backup files of DB1. Which Transact-SQL statement should you use?
Answer
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak';
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION;
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION, DIFFERENTIAL;

Question 24

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. When you are absent, a user will use a login named Mary to log in and maintain the database snapshots. The user has to delete the database snapshots for DB1, so you have to give the appropriate permissions to the user. Which database permission should you give the user?
Answer
  • DELETE
  • CONTROL
  • DROP DATABASE
  • ALTER ANY DATASPACE

Question 25

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1 in the instance. The DB1 database includes spatial data types. On DB1 database, you have to perform a database consistency check to include the spatial indexes. Besides this, you must make sure that you can reduce the effect on the database concurrency to the least. Which Transact-SQL statement should you run?
Answer
  • DBCC CHECKCATALOG (DB1);
  • DBCC CHECKALLOC (DB1) WITH TABLOCK;
  • DBCC CHECKDB (DB1) WITH TABLOCK, PHYSICAL_ONLY;
  • DBCC CHECKDB (DB1) WITH EXTENDED_LOGICAL_CHECKS;

Question 26

Question
You are the administrator of a SQL Server 2008 instance. A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be truncated after you review it. All information produced by a job must be available for viewing. Which action should you perform to achieve this goal?
Answer
  • You should enable notifications to the Windows application event log when the job completes.
  • You should enable write OEM file
  • You should enable all job steps, making them send the output to a file
  • You should include execution trace messages in the SQL Agent Error log

Question 27

Question
You are the administrator of a SQL Server 2008 instance containing a database named DB1. An application which is continuously connected uses DB1. The application extensively uses the INSERT command and triggers the population of multiple tables. Now you notice that the application has a poor performance. You doubt that this problem has something to do with blocking. You have to monitor the state of the instance at regular intervals while not affecting the application performance further. Which tool should you use?
Answer
  • You should use Dynamic Management Views
  • You should use SQL Server Resource Governor
  • You should use SQL Server Profiler
  • You should use Windows System Monitor

Question 28

Question
You are the administrator of a SQL Server 2008 instance. You upgraded this instance to SQL Server 2008. You configure the Agent service to use the LocalSystem account. There is a file named Orderdata on a remote network share. A job accesses this Orderdata file by using a CMDExec step. The job step fails to complete execution after the upgrade. You have to make sure that the job can complete execution, so you have to configure the job step. Which action should you perform?
Answer
  • You should configure a certificate
  • You should configure the SQL Server Agent service to use the NetworkService account.
  • You should configure the job step to use a proxy account
  • You should configure the SQL Server Agent service to use a local Windows account

Question 29

Question
You are the administrator of a SQL Server 2008 instance. A user reports that when he runs a query, it takes a quite long time and it is still unfinished. As the IT support, you have to verify whether the query is blocked. Which tool should you use?
Answer
  • You should use the Database Engine Tuning Advisor tool
  • You should use the Windows System Monitor tool
  • You should use the Job Activity Monitor tool in Microsoft SQL Server Management Studio
  • You should use the Activity Monitor tool in Microsoft SQL Server Management Studio

Question 30

Question
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. At 2:00 There is a full database backup of all the user databases every day. Every quarter the transaction log backup is performed. Every 4 hours a differential backup is occurred. You are going to perform a full backup of DB1 at 11:00. When you perform this back up, you must make sure that the overall backup will not be affected. What's more, you have to restore procedures for DB1. You must make sure that the backup files are restored in proper sequence. Which Transact-SQL statement should you use?
Answer
  • BACKUP LOG DB1 TO DISK = 't:\backups\db1.trn';
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH NOUNLOAD;
  • BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COPY_ONLY;

Question 31

Question
You are the administrator of a SQL Server 2008 instance. You get an order from your company CIO to identify whether a database integrity check (DBCC CHECKDB) was run for a particular database. Which log file should you examine?
Answer
  • log.trc
  • default.trc
  • ERRORLOG
  • SQLAGENT

Question 32

Question
You are the administrator of a SQL Server 2008 instance. Users report that they are unable to connect to the named instances. You check and verify that they can only connect to the default instance. You also ensure that all SQL Server instances run normally. You have to start the service which is required to connect to the named instances. Which service should you start?
Answer
  • Server
  • SQL Server Agent
  • SQL Server Browser
  • SQL Active Directory Helper

Question 33

Question
You are the administrator of a SQL Server 2008 instance. There are user-defined stored procedures. You have to ensure two things. First the names of all user-defined stored procedures must contain the prefix usp_ on all instances; secondly stored procedures wich do not contain this prefix cannot be created by you. What should you do to ensure these two?
Answer
  • You should create a policy that targets the name of the stored procedure that is evaluated on change.
  • You should create a policy that targets the name of the stored procedure that is evaluated on demand.
  • You should create a condition that targets the name of stored procedure that is evaluated on demand.
  • You should create a condition that targets the name of the stored procedure that is evaluated on change.

Question 34

Question
You are the administrator of a SQL Server 2008 instance. A new SQL Agent job has been created by you. The job includes a Windows PowerShell job step which transfer data between servers by using the SQLCmd utility. If the job fails, you have to make sure that an operator named ReactGroup is informed by receiving an e-mail. What action should you perform to achieve this goal?
Answer
  • First you should enable Notifications. Direct the Notification to the ReactGroup operator on job failure after you create the ReactGroup operator.
  • First you should enable Notifications. Direct the Notification to the ReactGroup operator on failure of the Powershell job step after you create the ReactGroup operator.
  • First you should assign the ReactGroup operator as the failsafe operator. Then enable the job after you create the ReactGroup operator.
  • First you should configure the job step proxy account to use the ReactGroup operator account after you create the ReactGroup operator. At last choose the proxy account for the e-mail profile.

Question 35

Question
You are the administrator of a SQL Server 2008 instance. There is a stored procedure. The stored procedure implements a database maintenance process. A SQL Server Agent job should be created to that runs the stored procedure. Besides this, You have to make sure that after it is completed successfully, the job is removed. What should you do?
Answer
  • You should create a job which is assigned to the Database Maintenance category
  • You should create an Alert which will run another to delete the maintenance job.
  • You should create a job. You schedule the job to run only once.
  • You should create a job. The job uses the Automatically delete job option

Question 36

Question
You are the administrator of a SQL Server 2008 instance which contains multiple Agent jobs. The jobs run each job on each day of the week by using seven shared schedules. You have to make sure that a job named Job4 doesn't run Wednesday. Which action should you perform to achieve this goal?
Answer
  • You should remove the schedule for Wednesday from Job4.
  • You should add a new Job4 schedule for Wednesday and disable it.
  • You should delete the schedule for Wednesday.
  • You should disable the schedule for Wednesday.

Question 37

Question
You are a database developer for your company. You use the following Transact-SQL code. SELECT e.Resume,j.EmployeeID FROM JobCandidate j, Employee e WHERE j.EmployeeID =* e.EmployeeID You need to rewrite this code to be ANSI compliant. How should you rewrite the code?
Answer
  • SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j LEFT OUTER JOIN Employee AS e ON EmployeeID = e.EmployeeID
  • SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j FULL OUTER JOIN Employee AS e ON EmployeeID = e.EmployeeID
  • SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j RIGHT OUTER JOIN Employee AS e ON EmployeeID = e.EmployeeID
  • SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j CROSS JOIN Employee AS e

Question 38

Question
You are the administrator of a SQL Server 2008 instance. There is a management data warehouse that uses the data collector to collect performance data. You intend to maintain the management data. You want to implement a data collection process. The process can gather and upload data in the management data warehouse on different schedules. Which process should you implement?
Answer
  • You should create a cached data collection
  • You should create an on-demand non-cached data collection
  • You should create a scheduled non-cached data collection
  • You should create two different SQL Agent jobs that are scheduled stimultaneously

Question 39

Question
You are the administrator of a remote 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 have to 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?
Answer
  • You should recapture the workload by using the data collector.
  • You should enable the XP_MSVER stored procedure on the local server.
  • You should enable the XP_MSVER stored procedure on the remote server
  • You should recapture the workload to a trace file by using SQL Profiler.

Question 40

Question
You are the administrator of a SQL Server 2008 instance. 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?
Answer
  • The SQL Server 2008 Upgrade Advisor should be used.
  • The SQL Server Profiler which captures the SQL:BatchCompleted and Exception event classes should be used.
  • A SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes should be used
  • A SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes should be used.

Question 41

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G: volume; the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP) data stored in the F: volume. The backup strategy of DB1 has three requirements: - first, the History filegroup should be backed up at the end of each working day; - second, the PRIMARY filegroup should be backed up five times a day; - third, the transaction log should be backed up every quarter. You get a report at 13:00 saying that the volume which contains the History filegroup fails. As the technical support, you have to restore the database, and you must reduce the data loss to the least as soon as possible. What should you do?
Answer
  • You should restore the most recent backup of the History filegroup.
  • You should restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup
  • You should backup the transaction log. Then restore the most recent backup of the PRIMARY filegroup and restore the most recent backup of the History filegroup. After the most recent backup of the History filegroup, restore all the transaction log backups
  • You should backup the transaction log. Then restore the most recent backup of the History filegroup and restore all the transaction log backups after the most recent backup of the History filegroup.

Question 42

Question
You are the administrator of a SQL Server 2008 instance. Your company CIO assigns you a task. You have to check the performance of the SQL Server 2008 instance. You have to find out which common language runtime (CLR) queries takes the longest-running time. Which dynamic management view should you choose to use?
Answer
  • sys.dm_os_wait_stats
  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.dm_exec_query_stats

Question 43

Question
You are a database developer for your company. You are developing a sales report for a given time period. The report must list all sales representatives and their position based on their total sales for the period. If two sales representatives had the same total sales, they should be assigned the same position. A sample output is shown in the following data: SALESREPID SALES POSITION 456 123,456 1 765 123,456 1 983 103,762 3 106 101,101 4 You need to create the report to fulfill the requirements. Which operator should you use?
Answer
  • RANK
  • DENSE_RANK
  • NTILE
  • ROW_NUMBER

Question 44

Question
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?
Answer
  • You should use local Service account
  • You should use network Service account
  • You should use domain account
  • You should use local System account

Question 45

Question
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?
Answer
  • You should verify that the simple recovery model is used by the database
  • You should verify that the full recovery model is used by the database.
  • You should verify that the checksum page verify option is used by the database
  • You should verify that the bulk-logged recovery model is used by the database

Question 46

Question
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?
Answer
  • For the database, you should turn on the TRUSTWORTHY database option
  • For the database, the PAGE_VERIFY database option should be set to CHECKSUM
  • For the database, the database compatibility level option should be set to 10.
  • For the model database, the PAGE_VERIFY database option should be set to TORN_PAGE_DETECTION.

Question 47

Question
You are a database developer for your company. You are developing a stored procedure that must accept a parameter of the xml data type and store the data in a relational table structure. You need to write the stored procedure to fulfill the requirements. Which functionality should you use?
Answer
  • FOR XML AUTO
  • the nodes method of the xml data type
  • the query method of the xml data type
  • the modify method of the xml data type

Question 48

Question
You are a database developer for your company. You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure. This cursor must have a fixed membership where the order of the rows does not change. The data in the rows that are retrieved through the cursor should be updatable. These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor. You need to create the stored procedure to fulfill these requirements. Which option should you use in the DECLARE CURSOR statement?
Answer
  • DYNAMIC
  • FAST_FORWARD
  • KEYSET
  • STATIC

Question 49

Question
You are a database developer for your company. You are creating a parameterized stored procedure that will query the data and return rows that include any verb tense of the verbs supplied as input to the stored procedure. The query should also return rows that contain both plural and singular forms of nouns. The data is included in several varchar(max) and varbinary(max)columns. You need to create the stored procedure to fulfill these requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
Answer
  • Use the LIKE operator.
  • Use the CONTAINS predicate.
  • Create a full-text index on the appropriate column or columns.
  • Create a nonclustered index on the appropriate column or columns.

Question 50

Question
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?
Answer
  • You should backup the master database
  • You should backup the default.trc file
  • You should backup the SQL Server registry hive.
  • You should backup the resource database

Question 51

Question
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?
Answer
  • You should terminate and restart the MSFTESQL service
  • You should rebuild the full-text index
  • The word "root" should be added to the stop list
  • The word "root" should be added to the thesaurus file

Question 52

Question
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?
Answer
  • You should use the Database Engine Tuning Advisor.
  • In Microsoft SQL Server Management Studio, you should create a maintenance plan
  • You should use the SQL Server Configuration Manager
  • In Microsoft SQL Server Management Studio, you should create a policy

Question 53

Question
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?
Answer
  • A new partition function should be created
  • You should change the existing partition scheme
  • You should use a Merge clause to change the existing partition function
  • You should use a Split clause to change the existing partition function

Question 54

Question
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?
Answer
  • For the AdventureWorks database, enable Server Auditing
  • 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
  • You should configure a SQL Profiler trace, and make it capture the deadlock graphs

Question 55

Question
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?
Answer
  • 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
  • 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.

Question 56

Question
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?
Answer
  • On the Products table, you should create a view.
  • On the RadioSize column, you should create a clustered index
  • On the RadioSize column, you should create a filtered index
  • On the RadioSize column, you should create a unique clustered index

Question 57

Question
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?
Answer
  • ON LOWER(i.OrganizationName) = LOWER(s.OrganizationName)
  • ON UPPER(i.OrganizationName) = UPPER(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

Question 58

Question
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?
Answer
  • You must make sure that the SendSite column does not allow NULL values.
  • You should define a primary key for the OrderSend table
  • 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.

Question 59

Question
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?
Answer
  • On the local server, enable the XP_MSVER stored procedure
  • On the remote server, enable the XP_MSVER stored procedure.
  • On the production server, use the dta.exe utility along with an XML input file.
  • 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

Question 60

Question
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?
Answer
  • You should use SQLServer:Buffer Manager
  • You should use SQLServer:Memory Manager
  • You should use SQLServer:Databases
  • You should use SQLServer:Access Methods

Question 61

Question
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 mission-critical 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 missioncritical applications can consume 100 percent of CPU utilization when required. Which action should you perform to achieve this goal?
Answer
  • 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.
  • 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 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.

Question 62

Question
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?
Answer
  • You should create a cached data collection
  • You should create an on-demand non-cached data collection
  • 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.

Question 63

Question
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?
Answer
  • 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 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.
  • 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 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.

Question 64

Question
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?
Answer
  • You should configure the database for Read Committed Snapshot isolation.
  • You should modify the stored procedure to use the Snapshot isolation level
  • You should configure the database for Auto Update Statistics asynchronously
  • You should modify the stored procedure to use the Repeatable Read isolation level

Question 65

Question
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?
Answer
  • You should allow failback only during non-business hours
  • You should immediately allow failback
  • You should prevent automatic failback
  • You should allow failback only during business hours

Question 66

Question
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?
Answer
  • 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 AdventureWorks database uses the Simple recovery model.
  • You should identify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server.

Question 67

Question
You are the administrator of a SQL Server 2008 with two instances. The two instances are respectively named InstanceA and InstanceB. There is a database named Dworks on InstanceA. You move the Dworks database to InstanceB. A user use a SQL Server login named Mary to access the database on InstanceA with the password "Tk@87#34". On InstanceB, you create the same SQL Server login. The user tries to use the SQL Server login Mark to access the Dworks database on InstanceB. But the user gets an error message, according to the indication of the message, the access to the Dworks database has been denied. You have to make sure that the user can access the Dworks database on InstanceB successfully. On InstanceB, which Transact-SQL statements should you execute?
Answer
  • USE AdventureWorks;ALTER USER Mary WITH LOGIN = Mary;
  • USE AdventureWorks;ALTER LOGIN Mary ENABLE;
  • USE AdventureWorks;ALTER LOGIN Mary WITH PASSWORD = "Tk@87#34" UNLOCK;
  • USE AdventureWorks;ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;

Question 68

Question
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?
Answer
  • In the cluster quorum drive, install the encryption certificate
  • In the SQL Server shared disk, install the encryption certificate
  • in the cluster group, install the encryption certificate.
  • On each individual node, install the encryption certificate

Question 69

Question
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?
Answer
  • For both the CustomerDB database and the master database, enable Transparent Database Encryption.
  • You should use the built-in encryption functions to encrypt the sensitive data at the cell level.
  • You should make the CustomerDB database accessible only through stored procedures and functions.
  • For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.

Question 70

Question
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?
Answer
  • 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
  • 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

Question 71

Question
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?
Answer
  • ServiceGreatUser should be added to the appropriate fixed database roles.
  • You should assign the appropriate object-level permissions to ServiceGreatUser
  • You should assign the appropriate server-level permissions to ServiceGreatLogin
  • You should assign the appropriate database-level permissions to ServiceGreatUser

Question 72

Question
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 in 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 ( 'CONTOSO\Sales', 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?
Answer
  • 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.
  • 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.

Question 73

Question
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?
Answer
  • GRANT VIEW ANY DEFINITION TO UserJack; EXEC sp_addrolemember 'db_datareader', 'UserJack';
  • CREATE ROLE Developers; GRANT CONTROL TO Developers; EXEC sp_addrolemember 'Developers', 'UserJack';
  • CREATE ROLE Developers; GRANT VIEW DEFINITION TO Developers; GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers', 'UserJack';
  • CREATE ROLE Developers; EXEC sp_addrolemember 'sp_dbdatareader', 'Developers'; EXEC sp_addrolemember 'sp_dbddladmin', 'Developers'; EXEC sp_addrolemember 'Developers', 'UserJack';

Question 74

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. The DB1 database 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; 06 CREATE TABLE Service.Table ( 07 Id int PRIMARY KEY CLUSTERED, 08 Name varchar(100); 09 ); 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?
Answer
  • Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
  • Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
  • You should give the user the ALTER permission on the Service schema
  • You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

Question 75

Question
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?
Answer
  • You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement.
  • You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
  • You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header
  • You should alter Procedure01 and add the EXECUTE AS USER = 'dbo' option immediately before the call to the sp_executesql stored procedure.

Question 76

Question
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?
Answer
  • 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";

Question 77

Question
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?
Answer
  • You should set query governor cost limit
  • You should set cost threshold for parallelism
  • You should set priority boost
  • You should set precompute rank

Question 78

Question
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?
Answer
  • You should use the http.sys listener to install the SSRS instance.
  • 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

Question 79

Question
You are the administrator of a default SQL Server 2005 instance. For a new application, you have to install a SQL Server 2008 instance on the server. Your company CIO assigns this task to you. You have to make sure that the respective certified third-party applications have access to both database instances. You have to achieve this goal without changing the existing application environments. What should you do to achieve this goal by using as little database administrative effort as possible?
Answer
  • You should install SQL Server 2008 as a named instance, and make the new application to use the new instance.
  • You should install SQL Server 2008 as the default instance, and make the new application to use the default instance.
  • You should upgrade the SQL Server 2005 application to use SQL Server 2008.
  • You should upgrade the SQL Server 2005 instance to a SQL Server 2008 instance.

Question 80

Question
You are the administrator of SQL Server 2008 instance. There is a single Database Mail profile. Reports are sent to all the company employees by using the Mail profile. During office hours, a large volume of reports are sent by the sp_send_dbmail stored procedure. Now you notice that it takes a long time for the reports to be sent to the company manager. As the technical support, you have to speed up the delivery. Which action should you perform to achieve this goal?
Answer
  • Another SMTP account should be added to the existing Database Mail profile
  • When you send the reports to the manager, you should use the @importance = high parameter.
  • You should change the Account Retry Attempts option of the Database Mail system properties
  • You should configure a new Database Mail profile. The new one will be used for sending the reports to the manager.

Question 81

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. You put forward a database maintenance plan. The maintenance plan rebuilds indexes, checks database integrity, writes a report to a text file. The company requires that when the maintenance plan executes abnormally, the operators can be notified by an e-mail message. You've been assigned this task. So you have to make modifications on the SQL Server instance. What should you do?
Answer
  • For the SQL Server Agent, you should enable a fail-safe operator
  • You should modify the database maintenance plan, making it send an e-mail to the appropriate operators
  • You should modify the database maintenance plan, making it include a Notify Operator task that e-mails the appropriate operators.
  • You should modify the SQL Server Agent job. The job executes the database maintenance plan to notify the appropriate operators.

Question 82

Question
You are the administrator of a SQL Server 2008 instance with a database named Finance. The Finance database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted daily.The recovery model of the Finance database is set to Simple. Business requirements specify that data loss of more than two hours worth of transactions during business hours is unacceptable. You need to select a backup strategy that uses the least amount of disk space by adhering to the business requirements. What should you do?
Answer
  • Perform a full database backup once daily.
  • Perform a full database backup once daily. Perform a differential backup every two hours during business hours.
  • Perform a full database backup once daily. Perform a transaction log backup every two hours during business hours.
  • Perform a full database backup once every week. Perform a differential backup once daily. Perform a transaction log backup every two hours during business hours.

Question 83

Question
You are the administrator of a SQL Server 2008 instance 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 have to make sure that you can implement the Replication topology along with the Subscription type successfully and meanwhile reduce the bandwidth usage to the least. Which action should you perform to achieve this goal?
Answer
  • You should implement the Snapshot Replication topology along with a Push Subscription.
  • You should implement the Merge Replication topology along with a Pull Subscription.
  • You should implement the Snapshot Replication topology along with a Pull Subscription.
  • You should implement the Transactional Replication topology along with a Pull Subscription

Question 84

Question
You are the administrator of a SQL Server 2008 instance. According to the requirement of the company CIO, users on the SQL Server instance must be able to use the OPENROWSET() function to query remote data source. Which sp_configure configuration option should you configure?
Answer
  • Agent XPs
  • remote access
  • remote proc trans
  • Ad Hoc Distributed Queries

Question 85

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. At 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?
Answer
  • You should use database snapshots
  • You should use database backup and restore
  • You should use database backup and restore along with compression
  • You should use schema-bound views in a read-only database. The database resides on the same instance.

Question 86

Question
You are a database developer for your company. You are writing a query that will search a resume text column for specified skills. The results must include both synonyms and exact matches. You need to write the query to fulfill the requirements. Which Transact-SQL function should you use?
Answer
  • SOUNDEX
  • CHARINDEX
  • CONTAINS
  • PATINDEX

Question 87

Question
You are a database developer for your company. You are developing a report that will return the customer name and phone number. Customers might have a business number, a mobile number, and a fax number. The report must return the first nonnull phone number for each customer. You need to write the query that returns the required customer information for the report. What should you use?
Answer
  • NULLIF
  • IF
  • REPLACE
  • COALESCE

Question 88

Question
You are a database developer for your company. You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure. This cursor must have a fixed membership where the order of the rows does not change. The data in the rows that are retrieved through the cursor should be updatable. These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor. You need to create the stored procedure to fulfill these requirements. Which option should you use in the DECLARE CURSOR statement?
Answer
  • DYNAMIC
  • FAST_FORWARD
  • KEYSET
  • STATIC

Question 89

Question
You are the administrator of a SQL Server 2008 instance with a database named AdventureWorks. The AdventureWorks database contains the Products table. You create a Merge Replication topology and a Publication to replicate the Products table to the SQL Server instances at remote locations. The Publication has a 21-day retention period. When a user returns from a one-month vacation, she discovers that her database does not contain the most recent data. The Windows Event log states the following error message: "Replication: expired subscription dropped." You need to obtain the most recent data in the database of the user. You also need to ensure that future data changes are appropriately replicated. What should you do?
Answer
  • Recreate the publication.
  • Upload unsynchronized changes.
  • Upload unsynchronized changes, and then reinitialize the publication.
  • Reinitialize the publication and immediately generate a new snapshot.

Question 90

Question
You are the administrator of three SQL Server 2008 instances named Instance1, Instance2, and Instance3. Each of the three instances runs on a separate server. A mission-critical database is mirrored between Instance1 and Instance2. Instance3 acts as the witness. Instance1 currently acts as the Principal. You plan to apply a patch to both servers. The patch requires a restart of the server. You need to find out the sequence of steps necessary to ensure that the following requirements are met: 1. The patching process is completed in the shortest possible time. 2. The database is online on the partner that currently does not apply the patch. 3. The database does not failover to the other partner during this time. What should you do?
Answer
  • Apply the patch to the server that runs Instance2. Apply the patch to the server that runs Instance1.
  • Apply the patch to the server that runs Instance2. Manually failover the mirroring session. Apply the patch to the server that runs Instance1.
  • Remove the mirroring session. Apply the patch to the server that runs Instance2. Apply the patch to the server that runs Instance1. Re-establish the mirroring session.
  • Suspend the mirroring session. Apply the patch to the server that runs Instance2. Resume the mirroring session. Manually failover the mirroring session. Apply the patch to the server that runs Instance1.

Question 91

Question
You are the administrator of multiple SQL Server 2008 instances. You are designing a consolidated repository of performance data. You need to ensure that the following requirements 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 14 days is deleted. 4. Administrative effort to manage performance data is minimized. What should you do?
Answer
  • Create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process to store and delete performance data in a single database for all instances.
  • Create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances.
  • Configure a management data warehouse process on each instance to store and delete performance data in a single database for all instances.
  • Configure an automated server-side trace process on each instance to store and delete performance data in a single database for all instances.

Question 92

Question
You are the administrator of a SQL Server 2008 instance. . You plan to maintain a management data warehouse that collects performance data by using the data collector. You need to implement a process that routinely gathers and uploads data in the management data warehouse on different schedules. What data collection process should you implement?
Answer
  • Create a cached data collection.
  • Create a scheduled non-cached data collection.
  • Create an on-demand non-cached data collection.
  • Create two different SQL Agent jobs that are scheduled at the same time. One job creates a data collection and the other job uploads the data collection.

Question 93

Question
You are the administrator of a SQL Server 2008 instance. Users report that the performance of the application is poor. You use SQL Profiler to capture a workload of the remote instance to a trace table on the remote SQL Server instance. You need to analyze the workload of the remote SQL Server instance on a local SQL Server instance by using the Database Engine Tuning Advisor. What should you do?
Answer
  • Use the data collector to recapture the workload.
  • Use SQL Profiler to recapture the workload to a trace file.
  • Enable the XP_MSVER stored procedure on the local server.
  • Enable the XP_MSVER stored procedure on the remote server.

Question 94

Question
You are the administrator of a SQL Server 2008 instance. A user named Mary reports that she is waiting for a query to complete. You need to ascertain whether the query is blocked. Which tool should you use?
Answer
  • The Windows System Monitor tool
  • The Database Engine Tuning Advisor tool
  • The Activity Monitor tool in Microsoft SQL Server Management Studio
  • The Job Activity Monitor tool in Microsoft SQL Server Management Studio

Question 95

Question
You are the administrator of a SQL Server 2008 instance that runs on a computer that hosts several applications. You configure the SQL Server Agent service to run by using the SERVER1\AGENT account. You create a job named MailingList that requires a file to be written to a file server. The job fails to run because it does not have appropriate access to the file server. You plan to configure the SQL Server Agent service. You need to perform the configuration such that only the SQL Server Agent service has read and write access to the file server. Which account type should you use?
Answer
  • Domain account
  • Local System account
  • Local Service account
  • Network Service account

Question 96

Question
You are the administrator of a SQL Server 2008 instance with a database named Sales. The Sales database has a table named Products that stores information about all types of products. Users frequently query the Products table based on the TelevisionSize column. The TelevisionSize column has the NULL value for all products other than Televisions. There is currently no index on the TelevisionSize column. You need to improve the query performance by ensuring that the effect on the disk space is minimized. What should you do?
Answer
  • Create a filtered index on the TelevisionSize column.
  • Create a clustered index on the TelevisionSize column.
  • Create a unique clustered index on the TelevisionSize column.
  • Create a view on the Products table by filtering on the TelevisionSize column.

Question 97

Question
You are the administrator of a SQL Server 2008 instance. The instance contains an On-Line Analytical Processing (OLAP) database along with a dimension table named Customers. The data of the Customers table is updated every hour. The Customers table contains redundant data. You need to conserve the disk space used to store the Customers table. Which compression technology should you use?
Answer
  • Row compression
  • Page compression
  • Backup compression
  • Windows NTFS file system compression

Question 98

Question
You are the administrator of a SQL Server 2008 instance with a database named AdventureWorks. The AdventureWorks database contains two tables as shown in the following table: 1. The Orders table contains a table partition for each month. 2. The OrderHistory table contains two table partitions; one partition contains all data and the other partition is empty. 3. You plan to develop a process to move data from the partition that contains data for the oldest month of the Orders table to the appropriate partition of the OrderHistory table. 4. You need to ensure that the process can be repeated at the end of every month. What should you do?
Answer
  • Alter the partition function of the Orders table by using the split option. Alter the Orders table by using the merge option.
  • Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the split option.
  • Alter the partition function of the Orders table by using the split option. Alter the OrderHistory table by using the split option. Alter the partition function of the OrderHistory table by using the merge option Alter the Orders table by using the merge option.
  • Alter the partition function of the OrderHistory table by using the split option. Switch the appropriate partition of the Orders table to the appropriate partition of the OrdersHistory table. Alter the OrderHistory table by using the merge option. Alter the Orders table by using the merge option. e model and master databases for the data

Question 99

Question
You are the administrator of a SQL Server 2008 instance with a database named Finance. Minimally logged operations are performed on the Finance database. You need to verify that the database can be restored to a specific point in time. What should you do?
Answer
  • Verify that the database uses the full recovery model
  • Verify that the database uses the simple recovery model.
  • Verify that the database uses the bulk-logged recovery model.
  • Verify that the database uses the checksum page verify option.

Question 100

Question
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. The table below shows the backup strategy for DB1. Your company stores the system databases, user database data, and log files on separate disks. A user report that the hard disks which store the user database data files have failed at 15:00 on Wednesday. According to the requirement of the company CIO, you have to restore DB1 from the database backups, and you must reduce the data loss as soon as possible. Which action should you perform to achieve this goal? Day Sunday Weekdays Weekdays Type of Backup Full backup Differential backup Transaction Log backup Time 01:00 22:00 8:00 12:00 16:00
Answer
  • First you should restore the full backup taken on Sunday. Then you restore the differential backup taken on Wednesday.
  • First you should back up the transaction log. Then restore the full backup taken on Sunday. At last restore all transaction log backups
  • First you should back up the transaction log. Second, restore the full backup taken on Sunday. Third, restore the differential backup taken on Tuesday and restore all transaction log backups taken during Wednesday.
  • First you should restore the full backup taken on Sunday. Then restore the differential backup taken on Monday and Tuesday and restore the transaction log backups taken at 08:00 hours and at 12:00 hours on Wednesday.

Question 101

Question
You are the administrator of a SQL Server 2008 instance. The instance contains a database named Finance. The recovery model of the Finance database is set to Full. You deploy a new process that modifies 10,000 records from the Accounts table at 19:00 hours daily. You need to ensure that any modification to the data can be reverted without the database going offline. Which strategy should you implement?
Answer
  • Database snapshots
  • Differential backup
  • Transaction log backup
  • Primary filegroup backup

Question 102

Question
You are the administrator of a SQL Server 2008 instance. The instance that runs on a Windows Server 2003 computer. with mixed authentication mode. You need to ensure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003. You also need to ensure that the password complexity rules continue to be enforced. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
Answer
  • Use the ALTER LOGIN ... HASHED statement to modify all logins.
  • Use the ALTER LOGIN ... CHECK_POLICY = ON statement to modify all logins.
  • Use the ALTER LOGIN ... CHECK_EXPIRATION = ON statement to modify all logins.
  • Use Policy-Based Management to create a policy that prevents any violation of these rules.
  • Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.

Question 103

Question
You maintain a SQL Server 2008 instance. You create a new SQL Agent job that includes a Windows PowerShell job step. The PowerShell job step uses the SQLCmd utility to transfer data between servers. You need to ensure that an operator named ResponseTeam is notified by an e-mail message if the job fails. What should you do?
Answer
  • Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on job failure.
  • Enable Notifications. Create the ResponseTeam operator. Direct the Notification to the ResponseTeam operator on failure of the Powershell job step.
  • Create the ResponseTeam operator. Assign the ResponseTeam operator as the fail safe operator. Enable the job.
  • Create the ResponseTeam operator. Configure the job step proxy account to use the ResponseTeam operator account. Select the proxy account for the e-mail profile.

Question 104

Question
You are the administrator of a SQL Server 2008 instance. The instance has databases for a finance application and a manufacturing application. You want to put some limitation on the maximum CPU query time allowed by the applications, so you configure the Resource Governor to achieve this. But you notice that certain reports do not execute successfully any longer on the manufacturing application. You have to allow the manufacturing application to consume more CPU time, so you have to change the Resource Governor configuration. Which Resource Governor component should you configure?
Answer
  • The workload group which is used by default
  • The workload group which is used by the finance application
  • The classifier function which identifies the finance application
  • The workload group which is used by the manufacturing application

Question 105

Question
You are the administrator of a SQL Server 2008 instance. For some reason, the company wants to view the number of users who access the SQL Server instance. The company CIO assigns this task to you. You have to use Windows System Monitor to find out the concrete number. Which performance object should you capture?
Answer
  • SQLServer:Buffer Manager
  • SQLServer:Access Methods
  • SQLServer:General Statistics
  • SQLServer:Exec Statistics

Question 106

Question
You are the administrator of a SQL Server 2008 instance with a database named Dworks. The Dworks database has a table named Bills which contains several indexes and a great amount of rows.An enterprise Web environment is supported by the database. On the Bills table 28% of the indexes have been fragmented. You have to defragment the indexes, making sure that you reduce the effect on database availability to the least. In the ALTER INDEX statement, which option should be included?
Answer
  • Sort in tempdb
  • Online
  • Fill factor
  • Pad index

Question 107

Question
You are the administrator of a SQL Server 2008 instance with a database named 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 toachieve this. You have to make sure that at the end of every month the process can be repeated. Which actions should you take? Table Name Function Bills Store bill information of the current year BillHistory Store bill information of the last years
Answer
  • 1. Alter the partition function of the BillHistory table by using the split option. 2. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table. 3. Alter the BillHistory table by using the split option.
  • 1. Alter the partition function of the Bills table by using the split option. 2. Alter the Billstable by using the merge option.
  • 1. Alter the partition function of the BillHistory table by using the split option. 2. Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table. 3. Alter the BillHistory table by using the merge option. 4. Alter the Bills table by using the merge option.
  • 1. Alter the partition function of the Bills table by using the split option. 2. Alter the BillHistory table by using the split option. 3. Alter the partition function of the BillHistory table by using the merge option. 4. Alter the Bill stable by using the merge option.

Question 108

Question
You are the administrator of a SQL Server 2008 instance. The company wants to export data from SQL Server to a Microsoft Excel file. The company assigns this task to you. You perform this by using the SQL Server Import and Export Wizard. After this, you save the package definition to a file. Now you have to reexecute the package from the command line. Which utility should you choose to use?
Answer
  • bcp.exe
  • dta.exe
  • dtexec.exe
  • sqlmaint.exe

Question 109

Question
You are the administrator of a SQL Server 2008 instance with a database named named Dworks. The Dworks database contains a table named BillDetails which has a clustered primary key named BillId on the BillId column. The BillDetails table has a single XML column named GoodsDetails. The XML column has an XML index and XML data are stored in this XML column. A new column named GoodsID is added to the BillDetails table. GoodsID must be included in the primary key. Which action should you perform to achieve this goal?
Answer
  • You should drop the XML index on the table. Modify the primary key. Recreate the XML index
  • You should alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.
  • You should move the XML data to a temporary table. Clear the XML data from the original table by setting the GoodsDetails column to NULL. Modify the primary key. Repopulate the ProductSpecs column.
  • You should disable the XML index on the GoodsDetails column. Modify the primary key. Enable the XML index on the ProductSpecs column.

Question 110

Question
You are the administrator of a SQL Server 2008 instance with a database named DB1. According to the requirements of the security audit policy only successful and failed logon attempts are recorded in log files. if records cannot be written to the log files, the SQL Server instance is shut down. You have to make the SQL Server instance comply with the security audit policy by performing the configuration. Which Transact-SQL statements should you run?
Answer
  • sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1; GO RECONFIGURE 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
  • 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

Question 111

Question
You are the administrator of a SQL Server 2008 instance which uses mixed authentication mode. The instance runs on a computer which has Windows Server 2003 installed. You have to make sure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003. Besides this, you must make sure that the password complexity rules continue to be enforced. Which action should you perform to achieve this goal? (choose more than one)
Answer
  • You should create an SQL Server Agent job. If a rule violation is detected, the job runs periodically to raise an alert
  • You should modify all logins by using the ALTER LOGIN ... HASHED statement.
  • You should modify all logins by using the ALTER LOGIN ... CHECK_POLICY = ON statement to.
  • You should modify all logins by using the ALTER LOGIN ... CHECK_EXPIRATION = ON statement.
  • You should create a policy by using Policy-Based Management. The policy prevents any violation of these rules

Question 112

Question
Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS). Which is correct?
Answer
  • You should create the package by using DTS Designer.
  • You should create the package by using the Package Migration Wizard.
  • You should create the package by using the Microsoft SQL Server Import and Export Wizard.
  • On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, you should click the Create Package submenu.

Question 113

Question
You are mastering the company database called TesterDB; The database includes a database that uses SQL Server log shipping. Log shipping will happen half a hour. You should make the full backup. In order to cut the net bandwidth utilization when maintenance every night. Which is the correct answer?
Answer
  • You should set the database to utilize the bulk-logged recovery model.
  • You should reproduce only those indexes that ask it every night.
  • You should add the time during transaction log backups.
  • You should disable log shipping after the nightly maintenance.

Question 114

Question
You are mastering the company database, Users complaints that the SQL Server 2008 application is running slowly. You should check the information of the CPU utilization, disk utilization, and memory utilization. The network should be considered. The detailed information should be checked for the proper example of SQL Server2008. Which is the correct answer?
Answer
  • You should check the proper dynamic management views (DMVs).
  • In the databases, you should check the distribution of the statistics which are hosted
  • You should check a statistics update commands the output.
  • You should check the recovery model of the master database.

Question 115

Question
You maintain an instance of Microsoft SQL Server 2008. The instance contains a database named Finance. The recovery model of the Finance database is set to Full. A full database backup of all the user databases is performed at 02:00 hours daily. The transaction log backup occurs every 15 minutes. A differential backup is performed every 4 hours. You plan to perform a full backup of the Finance database at 11:00 hours. You need to ensure that the backup is performed without affecting the overall backup and restore procedures for the Finance database. You also need to ensure that the backup files are restored in proper sequence. Which Transact-SQL statement should you use?
Answer
  • BACKUP LOG FINANCE TO DISK = 't:\backups\finance.trn';
  • BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak' WITH NOUNLOAD;
  • BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak` WITH COPY_ONLY:
  • BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak` WITH DIFFERENTIAL;

Question 116

Question
You are mastering the company database, and managing 20 SQL Server 2005 computers which are operated by other administrators that are asked to meet company service level agreements (SLAs) on querying response time. Some of the method on controlling query response times on the servers should be told for these administrators. Which is the correct answer?
Answer
  • You should create a query that searches the sys.dm_db_partition_stats dynamic management view (DMV). Then distribute this search to the administrators.
  • You should develop an XML schema which includes the event and column names of the query response times, which should be captured in SQL Server Profiler.
  • You should develop SQL Server Profiler templates, which contain query start times and end times. Then distribute these templates to the database.
  • You should teach the administrators to use System Monitor Control Tool.

Question 117

Question
You are managing a SQL Server 2008 instance which includes some applications for Home.com. You set the SQL Server Agent service to implement through utilizing the SerTest\Test account. You create a job named MailingTester that allows a file to be stored to a log server. The job could not run normally because it could not own proper access to the log server. You decide to set the service of SQL Server Agent. You should make sure that you could run the settings such that the SQL Server Agent service owns all access to the log server. Which is the correct answer?
Answer
  • You should utilize the type of Domain account
  • You should utilize the type of remote System account
  • You should utilize the type of remote Service account
  • You should utilize the type of Local Service account

Question 118

Question
You are the administrator of a SQL Server 2008 instance. You determine that you need more information about SPID 56. Which of these do you do?
Answer
  • Execute sp_who 55 in SSMS
  • Execute sp_who 56 in SSMS
  • Use the Activity Monitor in Microsoft SQL Management Studio and locate process 55.
  • Select from the sys.syslockinfo compatibility view and locate spid 55.

Question 119

Question
You administer a SQL Server 2008 instance. The instance contains a database named DB1. You plan to allow all the application developers to use SQL Server Profiler to capture traces to troubleshoot the application that uses the database DB1. You need to grant the minimum necessary permission to the application developers. What should you do?
Answer
  • Grant the appropriate database-level permissions to all database users of the application developers.
  • Grant the appropriate server-level permissions to all SQL Server logins of the application developers.
  • Add all the database users of the application developers to a fixed database role.
  • Add all the SQL Server logins of the application developers to a fixed server role.

Question 120

Question
You are a database administrator for your company. The company uses a SQL Server 2008 database that includes a table named Inventory. The table contains a column named Price. A company policy states that the value in the Price column cannot be decreased by more than 10 percent in any single database operation. Updates to the Price column are made by various means, including by using ad hoc queries. You need to ensure that this company policy is enforced. What should you do?
Answer
  • You should develop a trigger which rolls back changes to the Price column which breaks company policy.
  • On the Price column, you should develop a primary key constraint to a table which includes valid prices.
  • You should develop a view which rolls back changes to the Price column which breaks company policy.
  • You should develop a stored procedure which allows changes to the Price column which breaks company policy.

Question 121

Question
You maintain a SQL Server 2008 instance that contains a database named DB1. DB1 stores customer data for the company. The customers use a Web application to access their profile data. You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost. Your solution must not affect the Web application or impact performance. What should you do?
Answer
  • Encrypt the customer data at the cell level and then back up DB1.
  • Configure access to DB1 to only use stored procedures and functions.
  • Enable Transparent Database Encryption for DB1 and then back up the transaction logs.
  • Encrypt the customer data at the folder level by using Encrypted File System (EFS) and then back up the transaction logs.

Question 122

Question
You administer a SQL Server 2008 instance that contains a database named DB1. A table named Sales.Table1 exists in the Sales schema. You need to move the Sales.Table1 table to a new schema named Billing. Which Transact-SQL statement should you execute?
Answer
  • ALTER SCHEMA Billing TRANSFER Sales.Table1;
  • ALTER USER Sales WITH DEFAULT_SCHEMA = Billing;
  • ALTER AUTHORIZATION ON Sales.Table1 TO Billing;
  • ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1;

Question 123

Question
You are a database administrator for your company. There are four automated testing areas for finished items in the company's manufacturing floor. SQL Server 2008 is used to reserve testing results for every testing area. You should create a replication method to make sure that test results flow from the testing areas to the SQL Server quickly. The results of testing will be used on reporting. Which is the correct answer?
Answer
  • At each of the testing areas for the test results, you should develop a separate snapshot publication. Develop a pull subscription on each distributor to the testing areas.
  • At each of the testing areas for the test results, you should develop a separate snapshot publication. Develop a merge subscription on each distributor to the testing areas.
  • You should develop a separate transactional publication at each of the testing areas for the test results. Develop push subscriptions on each of the testing areas.
  • You should develop a separate transactional publication at each of the testing areas for the test results. Develop a merge subscription on each distributor to the testing areas.

Question 124

Question
You administer a SQL Server 2008 instance that contains a database named DB1. The DB1 database contains the following stored procedure. (Line numbers are included for reference only.) When a user named User1 attempts to invoke Procedure1, the following exception is raised: "Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'." You need to provide User1 access to execute Procedure1 by allocating only the required permissions. What should you do? 01 CREATE PROCEDURE Sales.Procedure1 02 AS 03 IF OBJECT_ID('Sales.Tables1') IS NOT NULL 04 DROP TABLE Sales.Table1; 05 06 CREATE TABLE Sales.Table1 ( 07 Id int PRIMARY KEY CLUSTERED, 08 Name varchar(100); 09 ); 10 11 ... 12 GO
Answer
  • Grant the ALTER permission on the Sales schema to User1.
  • Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.
  • Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.
  • Insert the EXECUTE AS USER = 'dbo' statement between lines 02 and 03.

Question 125

Question
You are managing a SQL Server 2008 instance for Home.com. You use a Microsoft SQL Server 2008 Analysis Services (SSAS) instance. A data mining structure should be deployed which contains the Microsoft Clustering mining model. From Scalable K-Means to Non-scalable K-Means. You should alter the attribute of the mining model. Which is the correct answer?
Answer
  • You should change MODELLING_CARDINALITY
  • You should change CLUSTERING_METHOD
  • You should change INDEXDEFRAG
  • You should change SHRINKFILE

Question 126

Question
You are mastering the company database, including the SQL Server 2005 database which include a table called Production. Product. The table has more than 50 columns The most common queries are listed below Transact-SQL statements. --QUERY 1SELECT [Name], [ProductLine], [ListPrice], [Class], [Style] FROM [Production]. [Product]WHERE [ProductLine] = 'T' --QUERY 2SELECT * FROM [Production]. [Product] ORDER BY [Name] When you are minimizing the space required for the indexes. You should select the best indexes to maintain the most common searched to the Production. Product table Which is the correct answer?
Answer
  • You should develop a clustered index on the Name column.
  • You should develop a nonclustered index on the Name column.
  • You should develop a clustered index on the ProductLine, ListPrice, Class and Style columns.
  • You should develop a nonclustered index on the ProductLine, ListPrice columns.

Question 127

Question
You are mastering the company database, with SQL Server 2008 computer named SQLTEST1 The master database on SQLTEST1 is down. Some SQL Server Agent jobs should be included in the SQLTEST1. The whole database should be backed up every day. You rebuild and restore the master database. You should make sure that SQLTEST1 functions as it did before the database lost. Which is the correct answer?
Answer
  • You should reserve the model and master databases for the database.
  • You should reserve the Log Transaction for the database.
  • You should rebuild the whole table schema for the database.
  • You should rebuild the whole SQL Server Web Service for the database.

Question 128

Question
You are mastering the company database; a new SQL Server 2008 computer named TesterDB should be set by yourself. Reporting Services will work in TesterDB. It should be set to run database backups and other maintenance job automatically. In the network environment, the company has no other SQL Server computers. The whole approach SQL2 will be made by using SQL1s DNS name. You should enable any necessary devices on SQL2. Which is the correct answer?(select more than one)
Answer
  • You should utilize SQL Server Web Service technology
  • You should utilize SQL Server Browser technology
  • You should utilize Internet Information Services technology
  • You should utilize Microsoft Distributed Transaction Coordinator technology

Question 129

Question
You are mastering the company database called Testing; you should back up the Testing database fully. You do transaction log once every two hours from 08:00 to 17:00. You should run database snapshots for the Testing database. You should develop database snapshot every day at 07:00. At 8:30 the user removes the whole information on the Testing database which was entered intothe CurrentSpeaker table yesterday. Deletion is the first step for the database. It is now 11:45 and many other changes have happened in the database. You should find solution to solve this problem . Which is the correct answer?
Answer
  • You should utilize the SELECT subquery in the INSERT statement to move the deleted rows from this mornings database snapshot to the CurrentSpeaker table.
  • You should utilize the Delete subquery in the INSERT statement to transfer the deleted rows from yesterday mornings database snapshot to the CurrentSpeaker
  • You should reserve last nights Full Backup and all transaction log backups until the 10:00 backup. Use the STOP AT statement on the first restores to prevent data loss
  • You should reserve last nights Full Backup and all transaction log backups until the 10:00 backup. Utilize the STOP AT statement on the last restore to prevent data loss

Question 130

Question
You are mastering the company database, you should check the log of the SQL Server 2008 Enterprise Edition everyday, and you get the information on the torn page errors of the database. In order to fine the solution of the torn page, which is the correct answer?
Answer
  • You backup to restore only the torn page. Then restore any transaction logs which have been made since the full backup.
  • You should reserve the newest database backup and then reserve any transaction logs which have been made since the past backup.
  • You should operate DBCC CHECKDB.
  • You should reserve the database from the newest database snapshot.

Question 131

Question
You administer a SQL Server 2008 instance. The instance contains various SQL Server Agent jobs that use seven shared schedules to run each job on each day of the week. You need to ensure that a job named Job5 no longer runs on Thursdays. What should you do?
Answer
  • Delete the schedule for Thursday.
  • Disable the schedule for Thursday.
  • Remove the schedule for Thursday from Job5.
  • Add a new Job5 schedule for Thursday and disable it.

Question 132

Question
You administer a SQL Server 2008 instance. You have a stored procedure that implements a database maintenance process. You need to create a SQL Server Agent job that runs the stored procedure. You also need to ensure that the job is removed after successful completion. What should you do?
Answer
  • Create a job that is scheduled to run once.
  • Create a job that uses the automatically delete job option.
  • Create a job that is assigned to the Database Maintenance category.
  • Create an Alert that will be raised when the job completes. This Alert will run another job to delete the maintenance job.

Question 133

Question
You maintain a SQL Server 2008 instance that contains a database named Finance. The data file and the transaction log file are located on the E: drive. The E: drive has only 5 percent available space. You need to move both files to the V: drive. Which procedure should you use?
Answer
  • Run the following Transact-SQL statement. ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE ( NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance SET MULTI_USER;
  • Run the following Transact-SQL statement. ALTER DATABASE Finance SET OFFLINE WITH ROLLBACK_IMMEDIATE; Move the data file and transaction log file to the new location. Run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Data, FILENAME = 'v:\SQLServer\Finance_Data.mdf'); ALTER DATABASE Finance MODIFY FILE (NAME = Finance_Log, FILENAME = 'v:\SQLServer\Finance_Log.ldf'); ALTER DATABASE Finance SET ONLINE;
  • Stop the SQL Server service. Move the data file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_single_file_db @dbname = N'Finance', @physname = N'v:\SQLServer\Finance_Data.mdf';
  • Stop the SQL Server Service. Move the data file and transaction log file to the new location. Start the SQL Server service. Run the following Transact-SQL statement. EXEC sp_attach_db @dbname = N'Finance', @filename1 = N'v:\SQLServer\Finance_Data.mdf', @filename2 = N'v:\SQLServer\Finance_Log.ldf';

Question 134

Question
You are mastering the company database, with SQL Server 2008 computer named SQLDB. The function of the SQLDB is to perform transaction log backups and so on. SQL1 should be reset by another administrator using the tool of SQL Server Surface Area Configuration. You should see that SQL1 no longer runs the maintenance tasks automatically. On SQLDB, You should make sure the tasks of the maintenance are finished without men Which is the correct answer?
Answer
  • You should reset the Web service to log on by using the role of administrator.
  • You should reset the server so that the SQL Server Agent service starts automatically.
  • You should reset operate a full backup of the master database by hand.
  • You should reset SQLDB to use Windows Integrated authentication.

Question 135

Question
You are mastering the company database, On a SQL Server 2008, you find out that one of the data files computer is broken. You should reserve the database which is from the most recent configurations of backups. In order to cut the lost, you should find the method as quickly as possible. Which is the correct answer?
Answer
  • You should run a transaction log backup for the database.
  • You should reserve the old database backup for the database.
  • You should reserve the most recent store produce log backup for the database.
  • You should run the whole database backup.

Question 136

Question
Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS). You should design a package, which has the following features: - The package should be transactional. - The package should be optimized for 10 tables. - The package should be stored safely in the msdb database of a server which is remote. Which is correct?
Answer
  • You should create the package by using DTS Designer.
  • You should create the package by using the Package Migration Wizard.
  • You should create the package by using the Microsoft SQL Server Import and Export Wizard.
  • On the Microsoft SQL Server 2005 Integration Services (SSIS) menu, You should click the Create Package submenu.

Question 137

Question
You maintain a SQL Server 2008 instance. You find a SQL Server Agent job is failing. When you review the job history information, you notice that the job history information is incomplete and appears to be truncated. You need to ensure that all information produced by a job is available for viewing. What should you do?
Answer
  • Enable write OEM file.
  • Enable all job steps to send the output to a file.
  • Include execution trace messages in the SQL Agent Error log.
  • Enable notifications to the Windows application event log when the job completes.

Question 138

Question
You administer a SQL Server 2008 instance that contains a database named SpatialDB. The SpatialDB database includes spatial data types. You need to perform a database consistency check on SpatialDB to include the spatial indexes. You also need to ensure that the effect on the database concurrency is minimized. Which Transact-SQL statement should you execute?
Answer
  • DBCC CHECKCATALOG (SpatialDB);
  • DBCC CHECKALLOC (SpatialDB) WITH TABLOCK;
  • DBCC CHECKDB (SpatialDB) WITH TABLOCK. PHYSICAL_ONLY:
  • DBCC CHECKDB (SpatialDB) WITH EXTENDED_LOGICAL_CHECKS:

Question 139

Question
You are mastering the company database; there is the transaction information which is for company's Web-based order system in the database of the company. The database is set to utilize the full recovery model. Between the hours of 20:00 and 04:00, you get the fewest orders; one hour should be accepted for the risk when the system is running Database backups should be run as quickly as possible. You should create the solution to avoid this kind of risk. What should you do?
Answer
  • You should set a Back up Database job to run the whole backup of the database every hour. Set a Check Database Integrity task to run every day in order to avoid corruption.
  • You should set a Back up Database job to do the whole backup of the database every day at 20:00. Set a second Back up Database task to run differential backups every hour.
  • Set a third Back up Database job to run transaction log backups every hour.
  • You should set a Back up Database task to do the whole backup of the database every day at 20:00. Configure a second Back up Database job to run differential backups at 12:00.
  • Configure a Check Database Integrity task to run every hour.
  • You should set a Back up Database job to run the whole backup at 09:00 and 14:00. Set a second Back up Database task to do transaction log backups every 30 minutes.

Question 140

Question
You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales. The database is replicated between SQL1 and SQL2 by using transactional replication. A full backup of each database is performed every night. Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on SQLTEST1 becomes corrupted. You are unable to repair the database. The Sales database on SQLTEST2 is unaffected. You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible. You must ensure a minimum loss of data and minimal impact to users of either server. What should you do?
Answer
  • You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
  • You should reserve only the newest transaction log backup.
  • You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
  • You should reserve a full database backup on SQL2. Reserve the backup to SQL1.

Question 141

Question
You maintain a SQL Server 2008 Enterprise Edition instance that contains a database named Finance. You need to reduce the size of the full database backup files of the Finance database. Which Transact-SQL statement should you use?
Answer
  • BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak*;
  • BACKUP DATABASE FINANCE TO DISK = ,t:\backups\finance.bak' WITH COMPRESSION:
  • BACKUP DATABASE FINANCE TO DISK = ,t:\backups\finance.bak' WITH DIFFERENTIAL:
  • BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak' WITH COMPRESSION- DIFFERENTIAL;

Question 142

Question
You are managing a SQL Server 2008 instance which includes a database called TesterDB for Home.com. The data file and the transaction log file which are located on the D: drive that owns only 10 percent useful space. You should make sure that you move both files to the G: drive. Which is the correct answer?
Answer
  • You should implement the statement below: ALTER DATABASE Finance SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE; Move the data file and run the following Transact-SQL statements. ALTER DATABASE Finance MODIFY FILE(NAME = Finance_Data, FILENAME = G:\Data\Finance_Data.mdf'); ALTER DATABASE Finance SET MULTI_USER;
  • You should utilize the following Transact-SQL statement. ALTER DATABASE TesterDB SET OFFLINE WITH ROLLBACK_IMMEDIATE; ALTER DATABASE TesterDB SET FILEEXTENSIONS('mdf', 'ldf'); ALTER DATABASE TesterDB SET ONLINE;

Question 143

Question
You are managing your company database. You manage a SQL Server 2005 database called ContractTEST, which is down. You operate a restore by using the following Transact-SQL script. RESTORE DATABASE ContractTESTFROM contracts_bu_deviceWITH CONTINUE_AFTER_ERROR, CHECKSUM, RECOVERY After the restore, the SQL Server logs show errors in some page restores. What you should do is to repair the database pages, which are down, and facilitates repair by using the fastest method. Which is the correct answer?
Answer
  • You should add code: DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
  • You should add code: ALTER DATABASE Contracts SET TORN_PAGE_DETECTION ON
  • You should add code: DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK
  • You should add code: ALTER DATABASE Contracts SET TRUSTWORTHY ON

Question 144

Question
Yon maintain a SQL Server 2008 instance that contains a database named Finance. The backup strategy for the Finance database specifies the following requirements: - Full database backup to a file named finance.bak is performed at 08:00 hours daily. - A transaction log backup to a file named finance_HHMM.trn is performed every 15 minutes starting at 08:15 horns. The Finance database is being used in single-user mode. At 08:40 hours, a user reports that some important data was accidentally deleted by a query that was executed at 08:23 horns. You need to restore the database to its original state. Which Transact-SQL statement(s) should you use?
Answer
  • RESTORE DATABASE Finance FROM DISK = 't:\backups\finance.bak' WITH NORECOVERY; RESTORE LOG Finance FROM 't:\backups\finance_0830.trn' WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM';
  • RESTORE DATABASE Finance FROM DISK = 't:\backups\finance.bak'; RESTORE LOG Finance FROM 't:\backups\finance_0815.trn' WITH NORECOVERY; RESTORE LOG Finance FROM 't:\backups\finance_0830.trn' WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM';
  • RESTORE DATABASE Finance FROM DISK = 't:\backups\finance.bak' WITH NORECOVERY; RESTORE LOG Finance FROM 't:\backups\finance_0815.trn' WITH NORECOVERY; RESTORE LOG Finance FROM 't:\backups\finance_0830.trn' WITH RECOVERY, STOPAT = 'Mar 17, 2008 08:23 AM';
  • RESTORE DATABASE Finance FROM DISK = 't:\backups\finance.bak'; RESTORE LOG Finance FROM DISK = 't:\backups\finance_0815.trn' WITH NORECOVERY; RESTORE LOG Finance FROM 't:\backups\finance_0830.trn' WITH RECOVERY;

Question 145

Question
You are managing a SQL Server 2008 computer called SQLTEST1. You should configure the SQL Server service and the SQL Server Agent service to start automatically, and set SQLTEST1 to audit all user names and application names that plan to get information from SQLTEST1. SQLTEST1 must operate auditing always, and you should keep the results for 10 years. Which is the correct answer?
Answer
  • You should disable the C2 Audit Tracing setting on the server properties.
  • You should create a SQL Server Agent job to run the SQL Trace stored procedure. Configure the job to start when the SQL Server Agent service starts.
  • You should set the Login auditing setting to both failed and successful logins
  • You should configure the server authentication mode as SQL Server and Windows Authentication mode.

Question 146

Question
You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales. The database is replicated between SQL1 and SQL2 by using transactional replication. A full backup of each database is performed every night. Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on SQLTEST1 becomes corrupted. You are unable to repair the database. The Sales database on SQLTEST2 is unaffected. You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible. You must ensure a minimum loss of data and minimal impact to users of either server. What should you do?
Answer
  • You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
  • You should reserve only the newest transaction log backup.
  • You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
  • You should reserve a full database backup on SQL2. Reserve the backup to SQL1.

Question 147

Question
You are mastering the company database; you should use the SQL Server 2005 database which enables business users to operate both ad hoc and predefined searches. You doubt that some searched will cost too many resources. You should clarify which searches cost the most resources. You decide to finish this aim as quickly as possible. Which is the correct answer?
Answer
  • You should utilize the sqldiag function
  • You should utilize the sys.dm_exec_query_stats dynamic management view (DMV).
  • You should utilize the DBCC INPUTBUFFER requirements
  • You should utilize the SHOWPLAN session selection.

Question 148

Question
You administer a SQL Server 2008 instance that contains a database named Sales. A SQL Server login named SalesAppLogin maps to a database user named SalesAppUser in the Sales database. You need to ensure that the database user can perform only the following tasks: - Execute all stored procedures that currently exist in the Sales database. - Execute all stored procedures that will be created in the Sales database. What should you do?
Answer
  • Add SalesAppUser to the appropriate fixed database roles.
  • Grant the appropriate object-level permissions to SalesAppUser.
  • Grant the appropriate server-level permissions to SalesAppLogin.
  • Grant the appropriate database-level permissions to SalesAppUser.

Question 149

Question
You are mastering the company database, with a SQL Server 2008 computer called SQLTEST1. There are three examples of SQL Server in the SQLTEST1. The ability to connect to the dedicated administrative connection on every example of SQL Server should be included in the disaster recovery plan of the company. You find that you could connect to the connection on SQLTEST1 default example while testing this ability; you could not connect to the dedicated connection on the two other examples on SQLTEST1. You should clarify that the examples are implementing and that client applications should be able to approach them. You should make sure that no other administrators are attempting to connect to any dedicated connections on SQLTEST1. You should also make sure that you could connect to the dedicated connection on all three examples. Which is the correct answer?
Answer
  • You should begin the SQL Server Browser service. Set the service to run automatically.
  • You should utilize the IIS tool to make the dedicated administrative connection.
  • You should stop and restart the default examples of SQL Server.
  • You should reset the default example to utilize a default port amount other than 1434.

Question 150

Question
You are mastering the company database. During the development, you find that Transact- SQL query below is running slowly: SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo. VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510' A clustered index exists on the VideoTitle column. There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table, In order to find this method to solve this problem, which is the correct answer?
Answer
  • You should reproduce the whole indexes on the VideoTitle table.
  • You should delete the query hint from the query.
  • You should alter the query hint to force the optimizer to force a unclustered index seek.
  • You should Re-create the index on only the ReleaseDate column.

Question 151

Question
You are mastering the company database; there are 15 retail stores in your company. On its own SQL Server 2008 computer, every retail store keeps transactions of point-of-sale in a database table called Saling. The Saling table also includes sales information from other stores to enable customer returns to any of the 15 retail stores. You could refresh the data in the main office to every retail store every hour. You should use trigger called trg_Coupon to produce sales coupons based on customer sales and buying patterns. Through using the fewest number of steps, you should set replication between the server in every retail store and a middle server in the mainoffice. In real time. You should not have the Replication. Which is the correct answer?
Answer
  • Snapshot replication should be utilized. You should set the trg_Coupon trigger on the server in every store to utilize the NOT FOR REPLICATION option.
  • You should utilize merge replication. You should set the trg_Coupon trigger on the server in each retail store to use the NOT FOR REPLICATION option.
  • You should utilize transactions log between the server in each retail store and the central server in the main office.
  • You should build multiple merge publications, one at every store and one in the main office.

Question 152

Question
You are mastering the company database, You should check the log of the a SQL Server 2005 Enterprise Edition everyday, You get the information on the torn page errors of the database. In order to fine the solution of the torn page, Which is the correct answer?
Answer
  • You should utilize the latest database backup to restore only the torn page. Then restore any transaction logs which have been made since the full backup.
  • You should reserve the newest database backup and then reserve any transaction logs which have been made since the past backup.
  • You should operate DBCC CHECKDB.
  • You should reserve the database from the newest database snapshot.

Question 153

Question
You are the administrator of a Microsoft Windows Server 2003 computer. Your company purchases a new enterprise sales application that runs SQL Server 2008. The application uses stored procedures that include the EXECUTE AS clause and that use Microsoft Windows accounts. The company's written security policy states that all enterprise applications must run under the context of a service account that requires the minimum amount of privileges. You need to configure the SQL Server service torun under the appropriate context for the installation of the new enterprise sales application. Under which context should the SQL Server service run?
Answer
  • Under the context of the local system account
  • Under the context of the local service account
  • Under the context of the domain user account
  • Under the context of the local Administrator account

Question 154

Question
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 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?
Answer
  • The Security\RequireClientAuthentication server configuration should be set to False
  • The Security\RequireClientAuthentication server configuration should be set to True.
  • The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration.
  • The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security\SecurityPackageList server configuration

Question 155

Question
You maintain a SQL Server 2008 instance. You have an existing database maintenance plan that performs the following tasks: - Rebuilds indexes. - Checks database integrity. - Writes a report to a text file. You need to ensure that an e-mail message is sent to the operators when the maintenance plan fails to execute successfully. What should you modify?
Answer
  • The instance.
  • The SQL Server service.
  • The SQL Server Agent job.
  • The database maintenance plan.

Question 156

Question
You administer two SQL Server 2008 instances named Instance1 and Instance2. A database named Customers resides on Instance1. You move the Customers database from Instance1 to Instance2. A SQL Server login named User1 that has the password "111999" is used by a user to access the database on Instance1. You create the same SQL Server login on Instance2. The user attempts to access the Customers database on Instance2 by using the SQL Server login User1. However, the user receives an error message which indicates that the access to the Customers database is denied. You need to ensure that User1 can access the Customers database. Which Transact-SQL statements should you execute on Instance2?
Answer
  • USE Customers; ALTER USER User1 WITH LOGIN = User1;
  • USE Customers; ALTER USER User1 ENABLE;
  • USE Customers; ALTER LOGIN User1 WITH PASSWORD = '111999' UNLOCK;
  • USE Customers; ALTER LOGIN User1 WITH DEFAULT_DATABASE = Customers;

Question 157

Question
You administer a SQL Server 2008 instance. You configure a single Database Mail profile that is used to send reports to all the employees of your organization. The sp_send_dbmail stored procedure is used to send a large volume of reports during business hours. The reports to the manager take a long time to be delivered. You need to ensure that the reports to the manager are sent as quickly as possible. What should you do?
Answer
  • Modify the SMTP relay settings.
  • Create a separate Database Mail profile for the reports.
  • Configure the security settings of the Database Mail system.
  • Modify the parameters of the email that contains the reports.

Question 158

Question
You maintain a default SQL Server 2008 instance. You plan to configure FILESTREAM data to meet the following requirements: - Enable FILESTREAM for file I/O streaming access. - Allow remote client computers to have streaming access to - FILESTREAM data. You need to ensure that FILESTREAM data is enabled. What should you do?
Answer
  • Configure the SQL Server service.
  • Configure the SQL Server Full Text service.
  • Modify the Volume Shadow Copy settings.
  • Modify the Background Intelligent Transfer Service settings.

Question 159

Question
You configure a SQL Server 2008 instance that contains a database named Customers. The Customers database experiences deadlock problems. You need to capture the deadlock information to the SQL Server error log. What should you do?
Answer
  • Enable file system auditing.
  • Enable the Windows Event Collector service.
  • Configure trace flags and then restart the SQL Server instance.
  • Configure a Data Collector Set and then restart the SQL Server instance.

Question 160

Question
You plan to install a SQL Server 2008 instance for a new application on an existing server that contains a default SQL Server 2005 instance. You need to ensure that both database instances are available for their applications. Your solution must meet the following requirements: - Minimize administrative effort. - Prevent changes to the existing application environments. What should you do?
Answer
  • Configure both instances to use SQL Server 2008.
  • Configure both applications to use SQL Server 2008.
  • Configure the new application to use a SQL Server 2008 named instance.
  • Configure the new application to use the SQL Server 2008 default instance.

Question 161

Question
You administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The backup schedule is shown in the following table: FULL backups Differential backups Transaction Log backups Sundays - 18:00 Tuesdays - 18:00 Mondays - 18:00 Wednesdays - 18:00 Fridays - 18:00 Thursdays - 09:00 Thursdays - 18:00 Saturdays - 18:00 A drive fails and the CustomerOrders database goes into suspect mode on Friday at 09:00 hours. You need to restore the backup. What should you do?
Answer
  • Restore the backup taken on Sunday only.
  • Restore the backups taken on Sunday and Monday.
  • Restore the backup taken on Wednesday only.
  • Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
  • Restore the backups taken on Wednesday and Thursday at 09:00 hours.
  • Restore the backups taken on Wednesday, Thursday, and Friday.
  • Restore the backups taken on Wednesday and Friday.
  • Restore the backups taken Wednesday, Friday, and Saturday.

Question 162

Question
You administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The backup schedule is shown in the following table: FULL backups Differential backups Transaction Log backups Sundays - 18:00 Tuesdays - 18:00 Mondays - 18:00 Wednesdays - 18:00 Fridays - 18:00 Thursdays - 09:00 Thursdays - 18:00 Saturdays - 18:00 A drive fails and the CustomerOrders database goes into suspect mode on Saturday at 10:00 hours. You need to restore the backup. What should you do?
Answer
  • Restore the backup taken on Sunday only.
  • Restore the backups taken on Sunday and Monday.
  • Restore the backup taken on Wednesday only.
  • Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
  • Restore the backups taken on Wednesday and Thursday at 09:00 hours.
  • Restore the backups taken on Wednesday, Thursday, and Friday.
  • Restore the backups taken on Wednesday and Friday.
  • Restore the backups taken Wednesday, Friday, and Saturday.

Question 163

Question
You administer a Microsoft SQL Server 2008 R2 instance. The instance has a database named CustomerOrders. The database is backed up by using the weekly scheduled SQL Server Agent jobs. The backup schedule is shown in the following table: FULL backups Differential backups Transaction Log backups Sundays - 18:00 Tuesdays - 18:00 Mondays - 18:00 Wednesdays - 18:00 Fridays - 18:00 Thursdays - 09:00 Thursdays - 18:00 Saturdays - 18:00 A drive fails and the CustomerOrders database goes into suspect mode on Thursday at 17:00 hours. You need to restore the backup. What should you do?
Answer
  • Restore the backup taken on Sunday only.
  • Restore the backups taken on Sunday and Monday.
  • Restore the backup taken on Wednesday only.
  • Restore the backups taken on Wednesday, Thursday at 09:00 hours, and Thursday at 18:00 hours.
  • Restore the backups taken on Wednesday and Thursday at 09:00 hours.
  • Restore the backups taken on Wednesday, Thursday, and Friday.
  • Restore the backups taken on Wednesday and Friday.
  • Restore the backups taken Wednesday, Friday, and Saturday.

Question 164

Question
You administer a Microsoft SQL Server 2008 R2 database that contains a schema named Reporting. The Reporting schema contains store procedures, tables, and views. Stored procedures and views in the Reporting schema access objects that are not contained in the schema. You need to ensure that the DOMAIN\BusinessReporting Active Directory group meets the following requirements: - Users are able to access objects in the Reporting schema through existing stored procedures. - Users are unable to modify tables and views in the database. What should you grant the DOMAIN\BusinessReporting group?
Answer
  • Access to the db_datareader role in the database
  • The EXECUTE permissions on the Reporting schema
  • The EXECUTE permissions to each schema that is queried by the stored procedures and views in the Reporting schema
  • Access to the sysadmin server role

Question 165

Question
You are reviewing and configuring the security of a SQL Server 2008 instance that contains database DB1. The security audit policy specifies the following requirements: - Only successful and failed logon attempts are recorded in log files. - The SQL Server instance is shut down if records cannot be written to the log files. You need to configure the SQL Server instance to comply with the security audit policy. What should you do?
Answer
  • Enable C2 auditing.
  • Enable common criteria compliance.
  • Create a server audit specification.
  • Configure Change Data Capture.

Question 166

Question
You administer a Microsoft SQL Server 2008 R2 database instance. The instance has a Products table that is currently in the OevUser schema. The Appusers database role is assigned Read and update permissions to the table. The DevUser schema is owned by a database login that has been renamed. You need to move the Products table from the DevUser schema to the dbo schema. Which two actions should you perform in sequence?
Answer
  • Run the following Transact-SQL command: REVOKE SELECT, UPDATE on DevUser.Products FROM Appusers; Then run the following Transact-SQL command: ALTER SCHEMA dbo TRANSFER DevUser.Products;
  • Run the following Transact-SQL command: GRANT SELECT, UPDATE ON dbo.Products to Appusers; Then run the following Transact-SQL command: ALTER SCHEMA dbo TRANSFER DevUser.Products;
  • Run the following Transact-SQL command: REVOKE SELECT, UPDATE on DevUser.Products FROM Appusers; Then run the following Transact-SQL command: GRANT SELECT, UPDATE ON dbo.Products to Appusers;
  • Run the following Transact-SQL command: ALTER SCHEMA DevUser TRANSFER dbo.Products; then run the following Transact-SQL command: REVOKE SELECT, UPDATE on DevUser.Products FROM Appusers;
  • Run the following Transact-SQL command: ALTER SCHEMA dbo TRANSFER DevUser.Products; Then run the following Transact-SQL command: GRANT SELECT, UPDATE ON dbo.Products to Appusers;

Question 167

Question
You administer a SQL Server 2008 instance. A user named Julie reports repeated failed connections because of incorrect password usage for the login name srpc8521. Julie has been able to successfully login using this account in the past. Julie then recalls the correct password. However, her connection is still denied. You need to enable the SQL Server login. Which Transact-SQL statement should you use?
Answer
  • ALTER LOGIN srpc8521 ENABLE;
  • ALTER LOGIN srpc8521 UNLOCK;
  • ALTER LOGIN srpc8521 WITH CHECK_EXPIRATION = OFF;
  • ALTER LOGIN srpc8521 WITH NO CREDENTIAL;

Question 168

Question
You administer a Microsoft SQL Server 2008 R2 server that hosts two databases named NorthwindOLTP and NorthwindOLAP. Cross database ownership chaining is currently disabled for all databases. A stored procedure in the NorthwindOLTP database writes data to a table in the NorthwindOLAP database. The public role in both databases has only Read and Execute permissions assigned to all objects. All objects are located in the DBO schema. Guest access has been disabled for all databases on the server. You need to ensure that the stored procedure can execute successfully. Which two actions should you perform in sequence?
Answer
  • Enable Cross Database ownership chaining for the NortwindOLAP database. Then enable Cross Database ownership chaining for the Server instance.
  • Enable Guest access to the NorthwindOLTP database. Then enable Guest access to the NorthwindOLAP database.
  • Disable Cross Database ownership chaining for the Server instance. Then enable Cross Database ownership chaining for the NortwindOLAP database.
  • Enable Cross Database ownership chaining for the Server instance. Then enable Cross Database ownership chaining for the NortwindOLAP database.
  • Disable Cross Database ownership chaining for the NortwindOLAP database. Then enable Cross Database ownership chaining for the Server instance.

Question 169

Question
You maintain a SQL Server 2008 instance that contains a database named Finance. You need to configure SQL Server to automatically send an e-mail message when any file in the Finance database increases. Which technology should you include in your solution?
Answer
  • SQL Server Profiler
  • Trace flag
  • Query notifications
  • Event notifications

Question 170

Question
You administer a Microsoft SQL Server 2008 R2 database instance. The service account used by SQL Server services must not have administrative permissions. You configure a new SQL Server Agent job to run every night. One of the steps in the job runs a PowerShell step. The job continuously fails on this step and throws the following error message: "The process could not be created for step 1 of job (reason: A required privilege is not held by the client). The step failed." You need to ensure that the SQL Server Agent Job executes successfully. Which four actions should you perform in sequence?
Answer
  • 1. Create a Credential object on the SQL Server and assign the object to the Windows Account. 2. Create a proxy in the SQL Server Agent and assign the proxy to the Credential object. 3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server. 4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select SQL Server Agent Account.
  • 1. Create a Credential object on the SQL Server and assign the object to the Windows Account. 2. Create a proxy in the SQL Server Agent and assign the proxy to the Credential object. 3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server. 4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.
  • 1. Create a Credential object on the SQL Server and assign the object to the Windows Account. 2. Create a SQL Server account and map the account to the credential. Assign the account to the SysAdmin server role.. 3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server. 4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.
  • 1. Create a Credential object on the SQL Server and assign the object to the Windows Account. 2. Create a proxy in the SQL Server Agent and assign the proxy to the Credential object. 3. Create a Windows domain account. Add the account to the Users group on the Production SQL Server. 4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.
  • 1. Create a Credential object on the SQL Server and assign the object to the Windows Account. 2. Create a Windows domain account. Add the account to the Users group on the Production SQL Server. 3. Create a Windows domain account. Add the account to the Local Administrators group on the Production SQL Server. 4. Open the job in the SQL Server Agent, and open the Powershell step. Under 'Run As', select the proxy.

Question 171

Question
You administer a Microsoft SQL Server 2008 R2 database instance that supports a sales management application. Occasionally, users of the application report that they receive a deadlock error. You plan to troubleshoot this error. You need to ensure that the instance is configured to capture troubleshooting information that will allow you to isolate the cause of the error. Which two actions should you perform in sequence?
Answer
  • 1. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts 2. Use Microsoft SQL Server Management Studio to enable the OleAutomationEnabled management facet.
  • 1. Restart the SQL Server service. 2. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts
  • 1. Use the SQL Server Configuration Manager tool to enable Trace Flag 1204 when the server starts 2. Restart the SQL Server service.
  • 1. Use the SQL Server Configuration Manager tool to enable the Shared Memory protocol. 2. Use Microsoft SQL Server Management Studio to enable the OleAutomationEnabled management facet.
  • 1. Use Microsoft SQL Server Management Studio to enable the OleAutomationEnabled management facet. 2. Restart the SQL Server service.

Question 172

Question
You administer a Microsoft SQL Server 2008 R2 database that contains customer invoice accounts. Backups are performed according to the following schedule: - Full database backup at 00:00 hours daily. - Transaction log backups at 06:00 hours, 14:00 hours, and 20:00 hours daily. You discover that the database failed at 11:59 hours. You also discover that the database is no longer accessible due to a failure of the hard disk drive that contains the database data file. You need to restore the database. You also need to ensure that data loss is minimal. Which four actions should you perform in sequence?
Answer
  • 1. Restore the most recent full database backup by using the WITH NORECOVERY option. 2. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement. 3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option. 4. Restore the last transaction log backup by using the WITH RECOVERY option.
  • 1. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement. 2. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option. 3. Restore the last transaction log backup by using the WITH RECOVERY option. 4. Restore the most recent full database backup by using the WITH NORECOVERY option.
  • 1. Restore the most recent full database backup by using the WITH RECOVERY option. 2. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement. 3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option. 4. Restore the last transaction log backup by using the WITH RECOVERY option.
  • 1. Back up the transaction log by using the WITH NO_TRUNCATE option on the BACKUP LOG statement. 2. Restore the most recent full database backup by using the WITH NORECOVERY option. 3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option. 4. Restore the last transaction log backup by using the WITH RECOVERY option.
  • 1. Back up the transaction log. 2. Restore the most recent full database backup by using the WITH NORECOVERY option. 3. Restore the transaction log backup from 06:00 hours by using the WITH NORECOVERY option. 4. Restore the last transaction log backup by using the WITH NORECOVERY option.

Question 173

Question
You maintain multiple SQL Server 2008 instances. You develop a failure recovery strategy. You need to find out the edition and the build of SQL Server that is deployed in your environment. What should you use to find the required information?
Answer
  • SELECT @@VERSION
  • SELECT * FROM sys.dm_os_sys_info
  • DBCC HELP
  • EXEC xp_msver

Question 174

Question
You administer a Microsoft SQL Server 2008 instance that contains two databases named Work and WorkHistory. Work uses the Latin1_General_CS_AS collation. WorkHistory uses the Latin1_General_100_CI_AI collation. Each database has an Orders table that has the following definition: CREATE TABLE [dbo].[Orders]( [OrderID] [int[ IDENTITY(1,1) NOT NULL, [OrderAmt] [money] NULL, [OrderDate] [datetime] NULL, [OrderTID] [char] (10) NULL, [OrderUID] [uniqueidetifier] NULL ) ON [PRIMARY] ; You need to return records from WorkHistory.dbo.Orders that exist in Work.dbo.Orders. Which Transact-SQL query should you use? (see image)
Image:
9f9edafc-1aaa-4fcc-99e5-d8e59f9a3336 (image/jpeg)
Answer
  • A.
  • B.
  • C.
  • D.

Question 175

Question
You administer a Microsoft SQL Server 2008 R2 instance. Mirroring has been configured between two servers. You need to ensure that automatic failover will occur when the principal server fails What should you do?
Answer
  • Create a witness server to monitor the mirroring session. On the principal server, use the ALTER DATABASE statement along with the SET WITNESS clause.
  • Create a witness server to monitor the mirroring session. On both partner servers, use the ALTER DATABASE statement along with the SET WITNESS clause.
  • Create a Microsoft SQL Server Integration Services package on the partner server to monitor the principal server.
  • Implement Policy Based Management. Enable the Server Performance management facet.

Question 176

Question
You administer a Microsoft SQL Server 2008 R2 database instance. You plan to automate maintenance tasks. What task types are included in a SQL Maintenance Plan? (Pick all that apply.)
Answer
  • Copy data from another server
  • Update database statistics
  • Rebuild indexes
  • Examine hard disk integrity
  • Back up databases

Question 177

Question
You maintain a SQL Server 2008 instance. You discover that the database named CityPowerLightDB presents data integrity problems. You perform a full database backup at 22:00 hours. The SQL Server instance experiences a power failure at 05:59 hours. No data modifications have occurred after the backup. When the database is online, you discover that some table data pages are corrupt. You need to completely repair the database in the minimum amount of time. What should you do?
Answer
  • Restore the corrupt pages from the full database backup.
  • Restore the database from the full database backup.
  • Use the DBCC CHECKTABLE command along with the PHYSICAL_ONLY option.
  • Use the DBCC CHECKDB command along with the REPAIR_ALLOW_DATA_LOSS option.

Question 178

Question
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition: CREATE TABLE [OrderItems] (OrderID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderLine INT NOT NULL, ProductID INT NOT NULL, Quantity INT NOT NULL, PriceEach MONEY NOT NULL );
Answer
  • A. Remove the clustered index from the table.
  • B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
  • C. Use the ALTER TABLEstatement to remove the COLLATEoption.
  • D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
  • E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
  • F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
  • G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
  • H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
  • I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
  • J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';

Question 179

Question
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition: CREATE TABLE [OrderItems] (OrderID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderLine INT NOT NULL, ProductID INT NOT NULL, Quantity INT NOT NULL, PriceEach MONEY NOT NULL ); The following DDL has been run on the database: CREATE PARTITION FUNCTION FUNC_FG (INT) AS RANGE LEFT FOR VALUES (1, 100, 1000); You need to create a partition scheme that will place all data to the SECONDARY filegroup. What should you do?
Answer
  • A. Remove the clustered index from the table.
  • B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
  • C. Use the ALTER TABLEstatement to remove the COLLATEoption.
  • D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
  • E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
  • F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
  • G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
  • H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
  • I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
  • J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';

Question 180

Question
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition: CREATE TABLE [OrderItems] (OrderID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderLine INT NOT NULL, ProductID INT NOT NULL, Quantity INT NOT NULL, PriceEach MONEY NOT NULL ); Currently, the table is partitioned by year with each year in its own filegroup. You need to move the data from the oldest year into a new table in a different Schema to archive the data. What should you do?
Answer
  • A. Remove the clustered index from the table.
  • B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
  • C. Use the ALTER TABLEstatement to remove the COLLATEoption.
  • D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
  • E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
  • F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
  • G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
  • H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
  • I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
  • J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';

Question 181

Question
You maintain a SQL Server 2008 instance. You use SQL Server Database Mail to distribute multiple reports that are scheduled and generated every night. You need to enforce a mail retention policy to delete copies of automatically generated mails after 60 days. Which process should you use?
Answer
  • A. Set the Subscription retention interval to delete mails after 60 days.
  • B. Set the MailProfile property of a Management Policy on the Server facet to delete mails after 60 days.
  • C. Create a SQL Agent job to use the sysmail_delete_log_sp stored procedure to delete mails older than 60 days.
  • D. Create a SQL Agent job to use the sysmail_delete_mailitems_sp stored procedure to delete mails older than 60 days.

Question 182

Question
You administer a Microsoft SQL Server 2008 database for an order-processing application. The following Transact-SQL statements have been run against the database (see attached image) --- You need to ensure that members of the reporting group can use only up to 35 percent of the CPU and up to 45 percent of the available RAM. Which four Transact-SQL statements a correct & in order?
Image:
bd6a1dac-1cc5-4872-86ff-0c34321a3e87 (image/jpeg)
Answer
  • 1. CREATE RESOURCE POOL ReportingPool WITH ( MAX CPU_PERCENT = 35, MAX_MEMORY_PERCENT = 45 ); 2. CREATE WORKLOAD GROUP ProductionGroup USING ProductionPool; 3. ALTER RESOURCE GOVERNOR RECONFIGURE; 4. ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
  • 1. CREATE WORKLOAD GROUP ProductionGroup USING ProductionPool; 2. CREATE RESOURCE POOL ReportingPool WITH ( MAX CPU_PERCENT = 35, MAX_MEMORY_PERCENT = 45 ); 3. ALTER RESOURCE GOVERNOR RECONFIGURE; 4. ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
  • 1. CREATE RESOURCE POOL ReportingPool WITH ( MAX CPU_PERCENT = 35, MAX_MEMORY_PERCENT = 45 ); 2. CREATE WORKLOAD GROUP ReportingGroup; 3. ALTER RESOURCE GOVERNOR RECONFIGURE; 4. ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
  • 1. CREATE RESOURCE POOL ReportingPool WITH ( MAX CPU_PERCENT = 35, MAX_MEMORY_PERCENT = 45 ); 2. CREATE WORKLOAD GROUP ProductionGroup; 3. ALTER RESOURCE GOVERNOR RECONFIGURE; 4. ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);

Question 183

Question
You troubleshoot a SQL Server 2008 instance. You suspect that an application uses an inefficient locking strategy and causes concurrency problems. You need to identify the following: - The sessions that wait for a resource - The resource needed - The sessions that block the resource Which dynamic management view should you use?
Answer
  • A. sys.dm_os_waiting_tasks
  • B. sys.dm_os_wait_stats
  • C. sys.dm_tran_active_transactions
  • D. sys.dm_exec_requests

Question 184

Question
You administer a SQL Server 2008 instance that contains a very large database named FinanceDB. You plan to create a maintenance plan that meets the following objectives for the FinanceDB database: - It executes the DBCC CHECKDB statement. - It rebuilds all the indexes. - It updates all index statistics. You need to ensure that the maintenance plan is executed in the minimum amount of time. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
Answer
  • A. Use the Reorganize Index task.
  • B. Use the Update Statistics task.
  • C. Use the Shrink Database task.
  • D. Use the Check Database Integrity task.
  • E. Use the Rebuild index task.

Question 185

Question
You design a maintenance plan for a SQL Server 2008 instance that contains a database named SalesDB. The SalesDB database includes spatial indexes to support queries on spatial data. You need to perform physical consistency checks on SalesDB. You also need to ensure that the performance effect on the SalesDB database is minimized. Which Transact-SQL statement should you execute?
Answer
  • A. DBCC SYS_CHECK (SalesDB);
  • B. DBCC SQLPERF (SalesDB);
  • C. DBCC RSPAIRDB (SalesDB);
  • D. DBCC CHECKDB (SalesDB);

Question 186

Question
You administer a SQL Server 2008 instance. You need to identify the network protocol used by the current connection. What should you do?
Answer
  • A. View the SQL error log.
  • B. Use a Dynamic Management View.
  • C. Set a trace flag.
  • D. Use the T-SQL template in Profiler.
Show full summary Hide full summary

Want to create your own Quizzes for free with GoConqr? Learn more.

Similar

music
puertotranquilo1
Biology AQA 3.1.3 Osmosis and Diffusion
evie.daines
FCE Practice Quiz - B2
Christine Sang
Evolution
rebeccachelsea
Edexcel Additional Science Biology Topic 1
hchen8nrd
CHARACTERS IN OF MICE AND MEN
jessicasusanevans
B1.1.1 Diet and Exercise Flash Cards
Tom.Snow
Chemistry 1
Peter Hoskins
Flame tests
Joshua Rees
The Periodic Table
asramanathan
MAPA MENTAL DISEÑO GRAFICO
puntoideascali
Browse Library