Mcts 70-432

Front 1

What is the order in which query clauses are processed?

Back 1

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Front 2

What is a self-join?

Back 2

A JOIN operation (CROSS, INNER or OUTER) against two instances of the same table

Front 3

What is a non-equi join?

Back 3

A join when the condition involves any operator besides equality, e.g. <, >.

Front 4

What is a candidate key?

Back 4

A candidate key is a key defined on one or more attributes preventing more than one occurrence of the same tuple (row) in a relation.

Front 5

What are the requirements for 1NF?

Back 5

Rows in the table must be unique and attributes should be atomic.

Front 6

What are the requirements for 2NF?

Back 6

1. data must meet the 1NF requirements.
2. For every candidate key, every nonkey has to be fully functionally dependent on part of the candidate key.

Front 7

What are the requirements of 3NF?

Back 7

1. Data must meet requirements for 2NF.
2. All nonkey attributes must be mutually independent.

Front 8

What is a data mart?

Back 8

A data warehouse that only serves part of an organization.

Front 9

What is the language used to manage and query SSAS cubes?

Back 9

Multidimensional Expressions (MDX).

Front 10

What is the language SSAS uses to manage and query data mining models?

Back 10

Data Mining Extensions (DMX).

Front 11

What is the DB_ID function?

Back 11

The DB_ID function accepts a database name as input and returns its internal database ID. If a databse with the input name does not exist, the function returns a NULL.
IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;

Front 12

What is the OBJECT_ID function?

Back 12

The OBJECT_ID function accepts an object name and type as input. The type 'U' represents a user table. This function returns the internal object ID if an object with the given input name and type exists, and NULL otherwise.

IF OBECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees

Front 13

Why should you not specify both DISTINCT and ROW_NUMBER in the same SELECT statement?

Back 13

The DISTINCT cluase would have no effect.

Front 14

What port is the database mirroring endpoint defined for?

Back 14

5022

Front 15

What are the three operating modes that you can configure for database mirroring?

Back 15

high availability
high performance
high safety

Front 16

What servers does high availability mode in database mirroring require?

Back 16

A principal server, a mirror server and a witness server.

Front 17

In High Availability mode, how is failure detected?

Back 17

High Availability mode uses a ping between each instance participating in the Database mirroring session.

Front 18

Which database mirroring mode is not recommended for normal operations?

Back 18

High Safety - its synchronous transfers have a high-performance impact without the benefit of automatic failover.

Front 19

What are the four general steps to prepare for Database mirroring?

Back 19

1. Ensure that databases are set to use the Full recovery model.
2. Back up the primary database.
3. Restore the database to the instance hosting the mirror database by using the NORECOVERY option.
4. Copy all necessary system objects to the instance hosting the mirror database.

Front 20

Why must you copy system objects when you configure Database mirroring?

Back 20

It enables applications to function after a failover. The most common objects to transfer are logins, linked server, SQL Server Integration Services (SSIS) packages, SQL Server Agent jobs, custom error messages.

Front 21

What can you use to transfer objects to the instance hosting the mirror database?

Back 21

You can use SSIS, which includes the Transfer Logins task for transferring logins from one instance of SQL Server to another while keeping any passwords encrypted. SSIS also provides tasks for transferring SQL Server Agent jobs, error messages, and other types of objects.

Front 22

If you do not remember the endpoint addresses when configuring database mirroring, what are two ways you can retrieve them?

Back 22

-- You can query sys.database_mirroring_endpoints on each instance to get the endpoint address
-- You can start the Configure Database Mirroring Security Wizard by first right-clicking on the principal database and selecting Tasks, Mirroring. Then, in the Database Properties dialog box, click Configure Security and walk through each step. Because you have created the endpoints already, the wizard automatically retrieves information about them. When the wizard fi nalizes, it automatically enters the endpoint addresses into the appropriate fields for confi guring Database Mirroring.

Front 23

How do you fail back from a forced failover?

Back 23

If the partners were synchronized at the time of the failover, you can apply transaction log backups to roll the failed partner forward in time, and then Database Mirroring finishes the resynchronization process. If the partners were not synchronized at the time of the failover, you need to remove mirroring and reinitialize.

Front 24

When does a forced failover occur?

Back 24

When the principal fails while the mirroring session is in an unsynchronized state, causing transactions that were committed on the principal to become lost. This is only possible for the High Performance operating mode.

Front 25

What command do you execute to cause a mirrored session to fail over in High Performance Mode?

Back 25

ALTER DATABASE <database name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

Front 26

What command to you execute to remove a mirroring session?

Back 26

ALTER DATABASE <database name> SET PARTNER OFF;

Front 27

What are the three components of log shipping?

Back 27

The primary database is accessible and accepts transactions.
The secondary database has a continuous chain of transaction logs applied to keep it up to date with the primary.
The monitor instance sends alerts when the log shipping session is out of sync.

Front 28

What happens to log shipping if the recovery model is changed to Simple?

Back 28

Since log shipping depends on transaction log backup, this change would cause log shipping to cease functioning.

Front 29

What affect would issuing a BACKUP LOG....WITH TRUNCATE_ONLY have on log shipping?

Back 29

Since log shipping depends on transaction log backup, this change would cause log shipping to cease functioning.

Front 30

What is the basic process for initializing log shipping?

Back 30

1. Create a share on both the primary and secondary because the backups need to be accessed across servers.
2. Create jobs to back up transaction logs, copy logs to the secondary, and restore the logs.
3. Restore a full backup to the secondary database.
4. Restore all subsequent transaction logs.
5. Start up jobs to automate restoration of the logs.
6. Copy any instance-level objects upon which the secondary database depends to service applications.

Front 31

What are the four steps that comprise DTA analysis?

Back 31

1. Generate a workload for analysis
2. Start DTA and connect to a server running SQL Server that contains a database to analyze the workload against
3. Select the workload to use
4. Specify tuning options

Front 32

What are the valid input sources for DTA to analyze?

Back 32

DTA can analyze queries and stored procedures that are stored in either a file or a table. The most common tuning source for DTA is a trace output file.

Front 33

What are the three database mirroring roles? Which are required and which are optional?

Back 33

principal - req
mirror - req
witness - optional

Front 34

What is the cause of enabling file rollover for a trace file?

Back 34

After the file reaches the upper size limit specified, the file closes and a new file is opened.

Front 35

What event groups are commonly captured to baseline and troubleshoot query performance?

Back 35

What event groups are commonly captured to baseline and troubleshoot query performance?

Stored Procedures, Performance, and TSQL event groups.

Front 36

What event group is used to define auditing quickly across a variety of security events?

Back 36

The Security Audit event group.

Front 37

What is an update lock?

Back 37

A hybrid of the shared and exclusive locks. An update lock starts out by acquiring a shared locks on resources until it finds the piece of data that needs to be modified, the shared lock is then changed to an exclusive lock while the data is changed.

Front 38

What type of lock is acquired for a resource being modified?

Back 38

An exclusive lock; it is held until the modification is complete.

Front 39

Errors in what three databases can prevent an instance from starting?

Back 39

Errors in the master, tempdb, and mssqlsystemresource databases can prevent an instance from starting. Errors in all other databases just make the problem database inaccessible until you can fix the problem.

Front 40

What sequence of events appear in the Windows Application Event viewer log indicate that SQL Server service account does not have sufficient permissions to access the data or log file(s) for either the master or tempdb databases?

Back 40

Service startup

Front 41

What permissions do SQL Server service accounts need?

Back 41

Read and Write access to the folder(s) where the data/log files for system databases are stored

Front 42

The start mode for all SQL Server services in a clustered installation should be set to what?

Back 42

Manual - they are controlled by the cluster service.

Front 43

If tempdb runds out of space, what error codes are generated?

Back 43

3958, 3959, 3966 and 3967

Front 44

If a db runs out of disk space, what errors are logged? What action can you take?

Back 44

1101 and 1105. You could add a new file to the appropriate filegroup on a disk volume that has space.

Front 45

Before adding files to a filegroup to resolve errors 1101 and 1105, what should you do?

Back 45

Check to see if the data files have auto-grow disabled. If the disk still has space, you could increase the size of the data files and resolve the issue.

Front 46

If a transaction log fills up, what 5 actions can you perform?

Front 47

When the transaction log fills up and any write action attempted rolls back, what is the error logged to the SQL Server error log and Windows Application Event log?

Back 47

9002

Front 48

What stored procedure can you run to extract the contents of the current error log?

Back 48

sys.xp_readerrorlog

Front 49

What are the four places that error and information messages related to your server running SQL Server can be found?

Back 49

Windows Event Log
SQL Server error log
SQL Server Agent log (sqlagent.out)
Database mail log

Front 50

Which events are commonly used to establish a performance baseline?

Back 50

The RPC:Completed and SQL:BatchCompleted events.

Front 51

What error is returned when a deadlock is detected?

Back 51

1205

Front 52

How can you troubleshoot a deadlock?

Back 52

Use SQL Server Profiler to capture a deadlock trace, which allows you to inspect the cause of the gridlock graphically.

Front 53

What are the two arguments that are universal for all TCP endpoints? Which is required and which is optional?

Back 53

LISTENER_PORT -Required
LISTENER_IP -Optional

Front 54

What is the default TCP port for database mirroring?

What is the default TCP port for TSQL?

Back 54

5022

1433

Front 55

What is the default setting for LISTENER_IP?

Back 55

ALL - listens for connections sent to any valid IP address configured on the machine

Front 56

What additional options do both Database Mirroring and Service Broker endpoints offer that TSQ Endpoints do not?

Back 56

They offer options to specify the authentication method and the encryption settings.

Front 57

What authentication method can be used for Database Mirroring and Service Broker endpoints?

Back 57

Windows-based authentication or certificates.

Front 58

What third argument do Database mirroring endpoints offer?

Back 58

You can specify that an endpoint is a PARTNER, WITNESS or ALL.
PARTNER - can participate only as the principal or as the mirror.
WITNESS - can participate only as a witness
ALL - can function in either role

Front 59

What is the TSQL to create a database mirroring endpoint?

Back 59

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED);
ALTER ENDPOINT [Mirroring] STATE = STARTED;

Front 60

How does SQL Server map a windows user or group to a Standard SQL Server login?

Back 60

SQL stores the name of the login or group along with the corresponding security identifier (SID); when a user logs in to the instance using windows credentials, SQL makes a call to the windows security application programming interface to validate the account, retrieve the SID and then compare the SID to those stored in the master database.

Front 61

How can you prevent access by user/users without revoking permissions?

Back 61

You can disable the account:
ALTER LOGIN <loginname> DISABLE

Front 62

What command can you execute to allow a login to view metadata on any object?

Back 62

GRANT VIEW ANY DEFINITION TO <user>

Front 63

In order for a login to see execution statistics, such as sys.dm_exec_requests, what command do you need to execute?

Back 63

GRANT VIEW SERVER STATE TO <user>

Front 64

How are DDL triggers related to the Policy-based management framework?

Back 64

The Policy-based managment framework creates DDL triggers for all policies that you configure to prevent an out of compliance situation.

Front 65

Can you prevent an ALTER DATABASE with a DDL trigger?

Back 65

No, ALTER DATABASE executes outside the scope of a transaction and cannot be rolled back with a DDL trigger. A DDL trigger can only audit it.

Front 66

What are DDL triggers?

Back 66

They allow you to trap and respond to login events.

Front 67

To restore and be able to decrypt data successfully, you must be able to backup the db master key and then regenerate the db master key on another instance. What commands do you use to perform this?

Back 67

OPEN MASTER KEY
BACKUP MASTER KEY
RESTORE MASTER KEY
CLOSE MASTER KEY

Front 68

What is the filestream.hdr file for?

Back 68

it is a system file that is created t manage the files subsequently written tot the filestream folder

Front 69

If you are creating an index on a sparse column, what type of index should you use?

Back 69

You should use a filtered index.

Front 70

What are the only operations not allowed during a backup?

Back 70

adding or removing a database file
shrinking a database

Front 71

In BCP, what does the -h switch specify?pp

Back 71

enforces check constraints and fire triggers during the importf

Front 72

What is unique about the FILENAME property defined for a FILESTREAM filegroup?

Back 72

The initial part of the folder path definition must exist; however, the last folder in the path defined cannot exist and is created automatically

Front 73

TINYINY
SMALINY
INT
BIGINT

Back 73

TINYINT - 0 to 255 - 1bytes
SMALLINT - -32,768 to 32,767 - 2 bytes
INT - -2^31 to 2^31 - 4 byte
BIGINT - -2^63 to 2^63 - 8 bytes

Front 74

How to clustered and nonclustered index builds differ when done off-line.

Back 74

When you build a clustered index offline, the table is locked and does not allow select statements or data modifications. If you build a nonclustered index offline, a shared table lock is acquired, which allows select statements but not data modification.

Front 75

What does the leaf node of a clustered index correspond to; how does this compare to the leaf node of a heap?

Back 75

In a clustered index, the leaf node is the actual data; in a heap, it is a forward pointer to the location of the row in the table's data pages.

Front 76

What do you need to do if you change the ACCENT_SENSITIVITY option on a full text catalog?

Back 76

You need to rebuild the full text indexes with the catalog.

Front 77

How are transactions handled in databases operating in high availability mirroring mode?

Back 77

1)Transactions are first written in memory buffers
2)Then they are committed to the transaction log which fires a DB mirroring trigger
3)Transaction is committed on the mirror DB
4)An acknowledgement is send back to principal
5)Principal gets to commit the transactions on itself
6)Acknowledgement is send back to the application

Front 78

What are the general steps for automatic failure detection and failover in the High Availability operating mode?

Back 78

1. The principal and mirror continuously ping each other.
2. The witness periodically pings both the principal and the mirror.
3. The principal fails.
4. The mirror detects the failure and makes a request to the witness to promote itself to the principal database.
5. The witness cannot ping the principal but can ping the mirror, so the witness agrees with the role reversal, and SQL Server promotes the mirror to the principal.
6. The principal server comes back online from the failure and detects that the mirror has been promoted to the principal.
7. SQL Server demotes the original principal to a mirror, and transactions begin flowing to this database to resynchronize it with the new principal.

Front 79

What is the process to sign code digitally?

Back 79

1. Create a database master key.
2. Create a certificate in the database.
3. Create a user mapped to the certificate.
4. Assign permissions on an object or objects to the user.
5. Execute ADD SIGNATURE on a module by the certificate.

Front 80

What is the general database space and index utilization DMV

Back 80

dm_db_*

Front 81

What is the general purpose of DMVs with a prefix of: dm_exec_* ?

Back 81

Statistics for queries that are executing as well as queries that have completed and still have plans in the query cache

Front 82

What is the general purpose of DMVs with a prefix of: dm_io_* ?

Back 82

Disk subsystem statistics

Front 83

What is the general purpose of DMVs with a prefix of: dm_os_* ?

Back 83

Statistics related to the use of hardware resources

Front 84

What does the sys.dm_db_index_usage_stats view contain?

Back 84

The number of times (and the last time) each index was used to satisfy a seek, scan or lookup as well as the number of times and the last time an update was performed on each index.

Front 85

What 4 optional parameters does sys.dm_db_index_operational_stats take?

Back 85

database_id
object_id
index_id
partition_id

Front 86

What is db_index_operational_stats used for?

Back 86

The function returns locking, latching and access statistics for each index--helps in determining contention issues.

Front 87

What are the five optional parameters that sys.dm_db_index_physical_stats takes?

Back 87

database_id
object_id
index_id
partition_id
mode

Front 88

What is sys.dm_db_index_physical_stats takes used for?

Back 88

It returns the size and fragmentation statistics for each index and should be the primary source for determining when an index needs to be defragmented.

Front 89

What is the sys.dm_db_missing_index_* views used for?

Back 89

Every time an index miss (when the Optimizer determines that an index would be beneficial but doesn't exist) occurs, SQL Server logs the details.

Front 90

What is sys.dm_exec_connections used for?

Back 90

Contains one row for each connection to the instance. Contains time connection was made along with properties of the connection and encryption settings. Can also tell you the total number of reads and writes for the connection as well as the last time a read or write was executed.

Front 91

What is sys.dm_exec_sessions used for?

Back 91

Contains a row for each currently authenticated session. In addition to login info, also track the current state of each possible query option and the current execution status. Also returns the accumulated reads, writes, CPU and query execution duration for the session.

Front 92

What is sys.dm_exec_requests used for?

Back 92

Contains one row for each currently executing request in the instance. you can use the blocking_session_id column to diagnose contention issues. Also contains the start time, elapsed time, estimated completion time, reads wrties and CPU for the request. In addition, you can retrieve the database and command being executed, along with handles for the SQL statement and query plan associated with the request.

Front 93

What is sys.dm_exec_query_stats used for?

Back 93

contains detailed stats on the performance and resources consumed for every query in the query cache. Lists the last time the query was executed and how many times the query was executed, along with the min and max execution time, logical/physical reads/writes/CPU, and a handle to the query plan generated by the Optimizer.

Front 94

What is sys.dm_exec_query_stats used for?

Back 94

contains detailed stats on the performance and resources consumed for every query in the query cache. Lists the last time the query was executed and how many times the query was executed, along with the min and max execution time, logical/physical reads/writes/CPU, and a handle to the query plan generated by the Optimizer.

Front 95

What is a handle

Back 95

It is the unique value by which SQL Server identifies the stored query plan and text stored in the query cache.

Front 96

What is sys.dm_exec_sql_text used for?

Back 96

It returns the text of the SQL statement associated to the handle that was passed in.

Front 97

What is sys.dm_exec_query_plan used for?

Back 97

It accepts a plan handle and returns the corresponding XML showplan.

Front 98

What statement would you use to return the query and XML showplan for every query currently executing?

Back 98

SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Front 99

What does the sys.dm_io_virtual_file_stats function return?

Back 99

Statistics about the reads and writes for every database file. Provides aggregate number as well as the bytes written to each file since the instance was started. You can also retrieve IOStalls. You use info from this function to determine whether disk contention is contributing to performance issues. You can also determine if you have disk I/O balanced across database files or if you have a disk hot spot.

Front 100

What is an IOStall?

Back 100

When SQL has to wait for the disk subsystem to become available to satisfy either a read or write operation; the time for IOStalls, measured in ms, is logged for each database file.

Front 101

What does the sys.dm_io_pending_requests return?

Back 101

Contains a row for each request that is waiting for the disk subsystem to complete an I/O request. If you have a request that appear frequently or stays for a long time, you probably have a disk bottleneck.

Front 102

Data collection for the Performance Data Warehouse is configured using which collector types?

Back 102

T-SQL Query
SQL Trace
Performance Counter
Query Activity

Front 103

Because the Data Collector dynamically generates the table based on the results of the query defines, what must you ensure?

Front 104

What are the items that you can capture data for with System Monitor?

Back 104

objects, counters and counter instances.

Front 105

What is an ownership chain, and how can you have a broken ownership chain?

Back 105

An ownership chain applies to objects that reference other objects within a database. The owner of the schema that contains the object is considered the owner of the object. SQL Server checks permissions for the fi rst object that you access, as well as each time the owner changes within the calling stack. The chain of object owners within a calling stack is called an ownership chain. You have a broken ownership chain when the object owner changes within a calling stack and you have not been granted sufficient permissions to continue accessing objects within the call stack.

Front 106

What features should you have disabled unless you need the specific functionality?

Front 107

What is the SNAPSHOT transaction isolation level?

Back 107

Uses the row versioning feature to keep shared and exclusive locks from blocking each other while maintaining data consistency. A read operation retrieves data from the version of the row prior to the start of a data modification operation.

Front 108

What is the READ SERIALIZED transaction isolation level?

Back 108

All the restrictions as the REPEATABLE READ isolation level. In addition, you cannot insert a new row within the keyset range
currently locked by the transaction. Locks are held for the duration of the transaction.

Front 109

What is the REPEATABLE READ transaction isolation level?

Back 109

Exclusive locks block both shared and exclusive locks. Shared locks block exclusive locks. Shared locks are held for the duration of the transaction.

Front 110

What is the READ COMMITTED transaction isolation level?

Back 110

This is the default isolation level for SQL Server. An exclusive lock blocks both shared as well as exclusive locks. A shared lock blocks an exclusive lock. Shared locks are released as soon as the data has been read.

Front 111

What is the READ UNCOMMITTED transaction isolation level?

Back 111

Data can be read that has not been committed. Although an exclusive lock still blocks another exclusive lock, any read
operations ignore an exclusive lock.

Front 112

What is required to correlate a counter log trace with a trace file?

Back 112

You need to have captured the StartTime data column in the trace.

Front 113

What is the purpose of the sp_trace_gettable trace module?

Back 113

A function that reads one or more trace files and returns the contents as a result set. Commonly used to import trace files into a table.

Front 114

What is the purpose of the sp_trace_getinfo trace module?

Back 114

A function that returns status and property information about all traces defined in the instance.

Front 115

What is the purpose of the sp_trace_getfilterinfo trace module?

Back 115

A function that returns the filters applied to a specified trace.

Front 116

What is the purpose of the sp_trace_geteventinfo trace module?

Back 116

A function that returns the events and data columns being captured by a trace.

Front 117

What is the purpose of the sp_trace_setstatus trace module?

Back 117

A stored procedure that starts, stops, and closes a trace. A status of 0 stops a trace. A status of 1 starts a trace. A status of 2 closes a trace and removes the trace defi nition from the instance.

Front 118

What is the purpose of the sp_trace_setfilter trace module?

Back 118

A stored procedure that adds a fi lter to a trace. Equivalent to the Edit Filter dialog box in Profi ler.

Front 119

A stored procedure that adds a fi lter to a trace. Equivalent to the Edit Filter dialog box in Profi ler.

Back 119

A stored procedure that adds a data column for an event to be captured by a trace. You need to call sp_trace_setevent once for each data column being captured for an event. Equivalent to the event and data column selection grid in the Events Selection tab of the New Trace dialog in Profiler.

Front 120

What is the purpose of the sp_trace_generateevent trace module?

Back 120

A stored procedure that allows you to define your own trace event.

Front 121

What is the purpose of the sp_trace_create trace module?

Back 121

A stored procedure that creates a new trace object. Equivalent to the definition on the General tab of the New Trace dialog in Profiler.

Front 122

What events return a significant amount of data and should be used with caution on a very busy instance?

Front 123

What event group is commonly used to troubleshoot concurrency issues?

Back 123

The Locks event group.

Front 124

What event group is used to define auditing quickly across a variety of security events?

Back 124

The Security Audit event group.

Front 125

What is the purpose of the User Configurable SQL Trace Event Group?

Back 125

10 events that you can confi gure with SQL Trace

Front 126

What is the purpose of the TSQL SQL Trace Event Group?

Back 126

9 events for the execution of ad hoc T-SQL or XQuery calls. Events for an entire SQL batch as well as each statement within a batch

Front 127

What is the purpose of the Transactions SQL Trace Event Group?

Back 127

13 events for the begin, save, commit, and rollback of transactions

Front 128

What is the purpose of the Stored Procedures SQL Trace Event Group?

Back 128

12 events for the execution of a stored procedure, cache usage,
recompilation, and statements within a stored procedure

Front 129

What is the purpose of the Sessions SQL Trace Event Group?

Back 129

3 events for existing connections when the trace starts as well as tracking the execution of logon triggers and resource governor classifier functions

Front 130

What is the purpose of the Server SQL Trace Event Group?

Back 130

3 events for mounting a tape, change to the server memory, and closing a trace file

Front 131

What is the purpose of the Security Audit SQL Trace Event Group?

Back 131

44 events to track the use of permissions, impersonation, changes to security objects, management actions are taken on objects, start/stop of an instance, and backup/restore of a database.

Front 132

What is the purpose of the Scans SQL Trace Event Group?

Back 132

2 events to track when a table or index is scanned

Front 133

What is the purpose of the Query Notifications SQL Trace Event Group?

Back 133

4 events to track the parameters, subscriptions, and templates for query notifications

Front 134

What is the purpose of the Progress Report SQL Trace Event Group?

Back 134

1 event for online index creation progress

Front 135

What is the purpose of the Performance SQL Trace Event Group?

Back 135

14 events that allow you to capture show plans, use of plan guides, and parallelism. This event group also allows you to capture full text queries

Front 136

What is the purpose of the Objects SQL Trace Event Group?

Back 136

3 events that track when an object is created, altered, or dropped

Front 137

What is the purpose of the OLEDB SQL Trace Event Group?

Back 137

5 events for distributed queries and remote stored procedure calls

Front 138

What is the purpose of the Locks SQL Trace Event Group?

Back 138

9 events for lock acquisition, escalation, release, and deadlocks

Front 139

What is the purpose of the Full Text SQL Trace Event Group?

Back 139

3 events to track the progress of a full text index crawl

Front 140

What is the purpose of the Errors and Warnings SQL Trace Event Group?

Back 140

16 events for errors, warnings, and information messages being logged. Events to detect suspect pages, blocked processes, and missing column statistics.

Front 141

What is the purpose of the Database SQL Trace Event Group?

Back 141

2 events to notify when a deprecated feature is used within the instance

Front 142

What is the purpose of the Database SQL Trace Event Group?

Back 142

6 events for data/log fi le grow/shrink as well as Database Mirroring state changes

Front 143

What is the purpose of the Cursors SQL Trace Event Group?

Back 143

7 events for the creation, access, and disposal of cursors

Front 144

What is the purpose of the CLR SQL Trace Event Group?

Back 144

1 event for the loading of a Common Language Runtime (CLR) assembly

Front 145

What is the purpose of the Broker SQL Trace Event Group?

Back 145

13 events for Service Broker messages, queues, and conversations

Front 146

What is the purpose of the Tuning Profiler Trace Template?

Back 146

Captures basic performance information for ad hoc SQL batches, stored procedures, and each statement inside a stored procedure.

Front 147

What is the purpose of the TSQL_SPs Profiler Trace Template?

Back 147

Captures performance data for all ad hoc SQL batches, stored procedures, and each statement inside a stored procedure. Every login and logout is also captured.

Front 148

What is the purpose of the TSQL_Replay Profiler Trace Template?

Back 148

Captures the stored procedures and ad hoc SQL batches executed against the instance in a format that allows you to replay the trace
against a test system. This template is commonly used to perform load and regression tests.

Front 149

What is the purpose of the TSQL_Locks Profiler Trace Template?

Back 149

Captures blocking and deadlock information such as blocked processes, deadlock chains, deadlock graphs, lock escalation, and lock timeouts. This template also captures every stored procedure, each command within a stored procedure, and every ad hoc SQL request.

Front 150

What is the purpose of the TSQL_Grouped Profiler Trace Template?

Back 150

Captures every login and logout along with the stored procedures and ad hoc SQL batches that are executed. Includes information to identify the application and user executing the request, but does not include any performance data.

Front 151

What is the purpose of the TSQL_Duration Profiler Trace Template?

Back 151

Captures the duration of every stored procedure and ad hoc SQL batch that is executed.

Front 152

What is the purpose of the TSQL Profiler Trace Template?

Back 152

Captures a list of all the stored procedures and ad hoc SQL batches that are executed, but does not include any performance statistics.

Front 153

What is the purpose of the Standard Profiler Trace Template?

Back 153

The most common template to start with; captures stored procedure and ad hoc SQL being executed along with performance statistics for each procedure and batch. Every login and logout is also captured.

Front 154

What is the purpose of the SP_Counts Profiler Trace Template?

Back 154

Captures each stored procedure executed so that you can determine
how many of each procedure is being executed.

Front 155

What is the purpose of the Blank Profiler Trace Template?

Back 155

An empty trace; allows you to create an entire trace from scratch.

Front 156

What are the three counters that, by themselves, indicate a system problem?

Back 156

System:Processor Queue Length, Network Interface:Output Queue Length, and Physical Disk:Avg. Disk Queue Length.

Front 157

What types of data can system monitor capture?

Back 157

numeric data for performance counters that are defined for hardware or software components.

Front 158

What steps do you take to implement Resource Governor?

Back 158

1. Enable Resource Governor.
2. Create one or more resource pools.
3. Create one or more workload groups.
4. Associate each workload group to a resource pool.
5. Create and test a classifier function.
6. Associate the classifi er function to Resource Governor.

Front 159

What are the two modes that a secondary database participating in log shipping can be in?

Back 159

Standby Mode or No Recovery Mode. When the secondary database is in Standby Mode, users can connect to and issue SELECT statements against the database. When the secondary database is in No Recovery Mode, users cannot connect to the database. In either mode, you can restore transaction logs to the secondary database. You cannot restore transaction logs when users are connected to the database, so you should not use Standby Mode for high-availability architectures.

Front 160

If your security access is defined using SQL Server logins, what additional action might you need to perform after failover to a mirrored database?

Back 160

When you create a SQL Server login, a dummy security identifi er (SID) is generated. This SID is then used to link the user in a database to the login for a server. If you do not re-create the SQL Server logins in the same order that you created them on the principal, a mismatch between the login and the user in the database occurs that can grant elevated permissions to a login. In this case, you need to execute ALTER LOGIN to remap the logins. However, because the mirror is inaccessible, you can perform this step only after a failover has occurred.

Front 161

What is the purpose of the additional thread that a witness has in database mirroring?

Back 161

It is used to manage all the messages between the witness and participating principal/mirror sessions. The primary messages that are sent on the witness thread are state changes of the principal/mirror and failover requests.

Front 162

How many threads are created per database in database mirroring and what is their purpose?

Back 162

One thread per database participating in Database Mirroring sessions is created on the instance. The purpose of the database threads is to exchange messages between the principal and the mirror, such as transactions and acknowledgments. On the mirror, one thread per database is opened to manage the process of writing log records and maintaining the query and data caches.

Front 163

What is the global communications thread in database mirroring?

Back 163

When you initiate a Database Mirroring session, Service Broker is enlisted to manage a global communications thread. The global communications thread is used to detect when a participant in the Database Mirroring session is not available so that failover can occur when configured in High Availability operating mode and status messages can be passed between the session members.

Front 164

What is transparent client redirection in terms of database mirroring?

Back 164

The new version of Microsoft Data Access Components (MDAC) that ships with Microsoft Visual Studio 2008 contains a Database Mirroring–related feature within the connection object called Transparent Client Redirect. When a client makes a connection to a principal, the connection object caches the principal as well as the mirror. This caching is transparent to the application, and developers do not need to write any code to implement this functionality.

Front 165

One benefit of database mirroring is that it does not have caching issues, why is this?

Back 165

In addition to sending transactions to the mirror, database mirroring performs periodic metadata transfers. The purpose of these metadata transfers is to cause the mirror to read pages into the data cache. This process maintains the cache on the mirror in a "semi-hot" state.

Front 166

What is high safety operating mode?

Back 166

High Safety operating mode transfers transactions synchronously, but it does not have a witness server. The synchronous transfer guarantees that all transactions committed at the principal are first committed at the mirror, and it requires the same performance considerations as the High Availability operating mode. However, the lack of a witness prevents automatic failover to the mirror if the principal fails. If the principal fails in High Safety operating mode, you must
promote the mirror manually to serve the database.

Front 167

Where SQL Server Configuration Manager writes startup parameters ...........

Back 167

to the registry. They take effect upon the next startup of the Database Engine. You can override the default startup options temporarily and start an instance of SQL Server by using the following startup options.

-T trace#
Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used t start the server with nonstandard behavior

Front 168

What happens if the nested triggers server configuration option is disabled with the On change: prevent

Back 168

Will not work correctly. policy-based management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that uses this evaluation mode. Removing the Policy-Based management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly

Front 169

What start up parameter collects info about the process and the resources when the deadlock detection algorithm encounters a deadlock

Back 169

-T1204

Front 170

What startup parameter needs to be configured with T1204 in order for the info acquired to be written to SQL Server error log?

Back 170

The t3605 startup parameter writes this info to the SQ Server error logs.

Front 171

What cmd to use to monitor the population status of an FULL-TEXT Index

Back 171

SELECT FULLTEXTCATALOGPROPERTY('AdvWksDcFTCat', 'Populatestatus');

Typically if a full population is in progress the result returned is 1

Front 172

SQL Server Access Methods Object

Back 172

The Access Methods object in SQL Server provides counters to monitor how the logical data within the DB is accessed

Front 173

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 DB. This DB is hosted on a single instances. Every five hours, you have to collect and load perforce data in the management data warehouse.
Which data collection process should you implement?

A)Create a cached data collection
B)Create an on-demand non-cached data collection
C)Create a scheduled non-cached data collection
D)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 create a data collection

Back 173

Answer C

Data collection and upload specifies how data is collected and uploaded to the management data warehouse.
Possible options:

Non-cached: Collection and data upload on the same schedule

When selected, specify one of the following
On-demand: Data is collected and uploaded on demand
Schedule: data is collected and uploaded according to a schedule.

Front 174

You must make sure that the four requirements are met:
1. the data collector is used to gather performance info
2. a single database stores performance information for all instances
3. performance info that is older than 15 days is deleted
4. reduce the administrative effort to manage performance to the least.
What will you do?
A) Create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances
B) config a management data warehouse process n each instance, then use this process to store and delete performance data in a single DB for all instances
C) Config an automated server-side trace process on each instance, then use this process to store and delete performance data in a single DB for all instance
D) Create and schedule a single SSIS package process, then use this process to store and delete performance data in a single DB for all instances

Back 174

Answer B

The data collection is a component installed on a SQL Server, running all the time or on a user define schedule, and collecting dif sets of data. The data collector the stores the collected data in a relational DB (solve 2) known as the management data warehouse.
The data collectori is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL. The data collector provides one central point for data collection across your DB servers and appz (solve 4)
The collection point can obtain data from a variety of sources and is not limited solely o performance data (solve 1) unlike SQL Trace.
The data collector enables you to adjust the scope of the data collection to suit your test and production environment. The data collector also uses a data warehouse, a relational DB that enables you to manage the data you collect by setting different retention periods for your data (solve 3).
The data collector supports dynamic tuning for data collection and is extensible through its API

Front 175

There is a report which is often executed during business time. There is a stored procedure that is used as the data source for the report. You get a report from users saying that they receive the data returned by the report but the data is inconsistent. You must make sure that consistent data is returned by the report while not affection other users. What you do?

A)Config the DB for Read Committed Snapshot isolation
B)Mod the stored procedure t use the Snapshot isolation level
C)Config the DB for Auto Update Statistics asynchronously
D)Config the stored procedure to use Repeatable Read isolation level

Back 175

Answer B

Front 176

There is a DB name Dworks in an instance. You are log shipping Adventureworks DB 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 t check log shipping of the Adventureworks DB and find out the problem. What should you do?
A) use EXTENDED_LOGICAL_CHECKS option to execute a DBCC CHECKDB statement on the Adventureworks DB
B) Identify whether the SQL Server Agent is started on the primary server
C) Identify whether the Adventureworks DB uses Simple recovery model
D) Identify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server

Back 176

Answer B
The log shipping is base on a set of SQL Server Agent jobs.
1)On the primary backup transaction log
2)Copy the transaction backup and Restore the transaction log

Front 177

In a high security environment, you manage a SQL Server 2008 cluster. You intend to configure and use
encrypted connections for the clustered virtual SQL Server. You have to install a certificate. The cetification will be used for encryption. What should you do?
A. In the cluster quorum drive, install the encryption certificate
B. In the SQL Server shared disk, install the encryption certificate
C. in the cluster group, install the encryption certificate.
D. On each individual node, install the encryption certificate

Back 177

Answer: D

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

Front 178

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. The
company stores clients data in DB1. Clients can access their profile data by using a Web application.
You must ensure the security of the customer data, you must ensure that even if the backup media is lost, data
files, log files and subsequent backups and so on are quite safe.
So what action should you perform to achieve this goal?
A. For both the CustomerDB database and the master database, enable Transparent Database Encryption.
B. You should use the built-in encryption functions to encrypt the sensitive data at the cell level.
C. You should make the CustomerDB database accessible only through stored procedures and functions.
D. For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.

Back 178

Answer: D

Front 179

Now you are in charge of a SQL Server 2008 instance. There is an internal application which uses Analysis
Services and Reporting Services. You use the application on the basis of SQL Server 2008.
According to the requirement of company security policy, the surface area for all the deployed components of
SQL Server 2008 should be configured. You have to implement the security policy and devise a method to
evaluate the security policy against other database servers.
What should you do?
A. You should create policies based on the appropriate facets. Apply the policies against a server group. The
server group includes the database servers that need to be configured
B. You should create a Transact-SQL script based on the sp_configure stored procedure. Use a configuration
server to run the script against a server group that includes the database servers
that need to be configured
C. You should edit the RSReportServer.config configuration file. Distribute the file to all database servers that
need to be configured
D. You should analyze your database servers by using the SQL Server Best Practices Analyzer (BPA).
Implement the recommendations of the BPA

Back 179

Answer: A

Front 180

UserJack views the definitions of all database objects in a database to read data from all user-defined tables,
views, and table-valued functions. For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions, but this
should be achieved by executing as little Transact-SQL statements as possible.
In the options below, which Transact-SQL statements should you execute?
A. GRANT VIEW ANY DEFINITION TO UserJack;EXEC sp_addrolemember 'db_datareader', 'UserJack';
B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers',
'UserJack';
C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;
EXEC sp_addrolemember 'Developers', 'UserJack';
D. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader', 'Developers';EXEC
sp_addrolemember 'sp_dbddladmin', 'Developers';EXEC sp_addrolemember 'Developers',
'UserJack';

Back 180

Answer: C

1. Create ROLE: Roles are database-level securables. After you create a role, configure the database-level
permissions of the role by using GRANT, DENY, and REVOKE. To add members to a database role, use
the sp_addrolemember stored procedure.
2. VIEW DEFINITION:The VIEW DEFINITION permission lets a user see the metadata of the securable on
which the permission is granted. However, VIEW DEFINITION permission does not confer access to the
securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see
metadata related to the table in the sys.objects catalog view. However, without additional permissions such
as SELECT or CONTROL, the user cannot read data from the table.
3. GRANT SELECT: Grants permissions on a securable to a principal.

Front 181

Now you are in charge of a SQL Server 2008 instance.
There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2
minutes and logs information to the text file.
You get a report from users saying that the sever doesn't make response. As the technical support, you check
and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer functions.
You have to identify the reason why the SQL Server Agent service is unresponsive.
In the options below, which log should you check?
A. You should check SQL1.log
B. You should check SQLAGENT.OUT
C. You should check log_xx.trc
D. You should check ERRORLOG

Back 181

Answer: B

Front 182

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. 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 must make sure that e user can
successfully execute Procedure1 complying with the company rules.
So what action should you perform to achieve this goal?
A. You should execute the GRANT SELECT ON dbo.Table01 TO User1 Transact-SQL statement.
B. You should execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
C. You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header
D. You should alter Procedure01 and add the EXECUTE AS USER = 'dbo' option immediately before the call
to the sp_executesql stored procedure.

Back 182

Answer: C

Front 183

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance. 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 must make sure that the SQL Server instance is (not?)
available to User1.
In the DB1 database, which Transact-SQL statement(s) should you execute?
A. DROP LOGIN "WIIKIGO\User1";
B. EXEC dbo.sp_droprolemember 'db_owner', 'WIIKIGO\User1';
C. EXEC dbo.sp_revokedbaccess 'WIIKIGO\User1';
EXEC dbo.sp_revokelogin 'WIIKIGO\User1';
D. CREATE LOGIN "WIIKIGO\User1" FROM Windows;DENY CONNECT SQL TO "WIIKIGO\User1";

Back 183

Answer: D

Front 184

You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There 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.
In the options below, which sp_configure configuration option should you set?
A. You should set query governor cost limit
B. You should set cost threshold for parallelism
C. You should set priority boost
D. You should set precompute rank

Back 184

Answer: B
Explanation/Reference:
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates
and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the
estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for
parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a
specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors.
Use the query governor cost limit option to specify an upper limit on the time period in which a query can run.
Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific
hardware configuration.

Front 185

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?
A. You should use the http.sys listener to install the SSRS instance.
B. You should configure the SSRS database, make it use Native mode
C. You should configure the SSRS database, make it use SharePoint integrated mode
D. You should use the Internet Information Services default Web site to install the SSRS instance

Back 185

Answer: C

When created for SharePoint integrated mode, the report server database stores server properties, report
execution snapshots, report history, subscription definitions, and schedules. It stores a secondary copy of
reports, report models, shared data sources, and resources to improve processing performance on the server.
Primary storage for report documents is in the SharePoint content databases.
QUESTION

Front 186

There is a database named DB1 in the instance. You have set the recovery model of DB1 to Full. You deploy a
new process. The process modifies 10,000 records from the Accounts table at 18:00 every day.
Any modification made by the process to the data must be reverted and database keeps online. You must
make sure of this.
So which strategy should you implement?
A. You should implement Differential backup
B. You should implement Database snapshots
C. You should implement Primary filegroup backup
D. You should implement Transaction log backup

Back 186

Answer: B

Front 187

There is a database named DB1 in the 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.
So what action should you perform to achieve this goal?
A. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak'
WITH NORECOVERY;
RESTORE LOG Cash
FROM 't:\backups\cash_0930.trn'
WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
B. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak';
RESTORE LOG Cash FROM 't:\backups\cash_0915.trn'
WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn'
WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';
C. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak';
RESTORE LOG Cash
FROM 't:\backups\cash_0915.trn'
WITH NORECOVERY;
RESTORE LOG Cash FROM 't:\backups\cash_0930.trn'
WITH RECOVERY;
D. RESTORE DATABASE Cash
FROM DISK = 't:\backups\cash.bak'
WITH NORECOVERY;
RESTORE LOG Cash
FROM 't:\backups\cash_0915.trn'
WITH NORECOVERY;
RESTORE LOG Cash
FROM 't:\backups\cash_0930.trn'
WITH RECOVERY,
STOPAT = 'Mar 17, 2008 9:25AM';

Back 187

Answer: D

1. Restore the full backup with the NORECOVERY option.
2. Restore the log backup of 9:15 AM with the NORECOVERY option.
3. Restore the log backup of 9:30 AM with the RECOVERY and STOPAT = 'Mar 17, 2008 9:25AM' option.

Front 188

There is a database named DB1 in the instance. 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.
So what action should you perform to achieve this goal?
A. You should restore the full backup
B. You should restore the database snapshot from 16:30 hours.
C. You should restore the database snapshot from 17:30 hours
D. You should restore both the full backup and the differential backup.

Back 188

Answer: D

Explanation/Reference:
You can't revert from the database snapshot because a snashot is a view of the db and stores only the pages
that where changed since the snapshot was taken.
Thus, the only way to get back the database is to restore the database full backup of 13:30 and the differential
backup of 15:30.
There will be a gap of 1 hour 32 minutes.

Front 189

There is a database named DB1 in the instance. A backup of DB1 is performed every day. You have to
minimize the size of the full database backup files of DB1.
In the options below, which Transact-SQL statement should you use?
A. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak';
B. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION;
C. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH DIFFERENTIAL;
D. BACKUP DATABASE DB1 TO DISK = 't:\backups\db1.bak' WITH COMPRESSION, DIFFERENTIAL;

Back 189

Answer: B
Section: (none)
Explanation/Reference:
We will use the compression option to minimize the FULL backup size. So, answer D is giving us one
differential backup
COMPRESSION:

Front 190

There is 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.
So which Transact-SQL statement should you run?
A. DBCC CHECKCATALOG (DB1);
B. DBCC CHECKALLOC (DB1) WITH TABLOCK;
C. DBCC CHECKDB (DB1) WITH TABLOCK, PHYSICAL_ONLY;
D. DBCC CHECKDB (DB1) WITH EXTENDED_LOGICAL_CHECKS;

Back 190

Answer: D

Explanation/Reference:
DBCC CHECKDB Checks the logical and physical integrity of all the objects in the specified database.
EXTENDED_LOGICAL_CHECKS
If the compatibility level is 100 (SQL Server 2008) or higher, performs logical consistency checks on an indexed
view, XML indexes, and spatial indexes, where present.

Front 191

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.
So what action should you perform to achieve this goal?
A. You should enable notifications to the Windows application event log when the job completes.
B. You should enable write OEM file
C. You should enable all job steps, making them send the output to a file
D. You should include execution trace messages in the SQL Agent Error log

Back 191

Answer: C
Section: (none)
Explanation/Reference:
In all the jobsteps, go to the option panel and select Output file to get all info from the step. Note that you can
append or overide the file. All job steps should be adapted with the option.
Output file Sets the file to use for output from the job step.

Front 192

You upgrade 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 must make sure that the job can complete execution, so you have to configure the job step.
What action should you perform?
A. You should configure a certificate
B. You should configure the SQL Server Agent service to use the NetworkService account.
C. You should configure the job step to use a proxy account
D. You should configure the SQL Server Agent service to use a local Windows account

Back 192

Answer: C

Front 193

Now you get an order from your company CIO, you have to identify whether a database integrity check (DBCC
CHECKDB) was run for a particular database.
Which log file should you examine?
A. log.trc
B. default.trc
C. ERRORLOG
D. SQLAGENT

Back 193

Answer: C

View the SQL Server error log to ensure that processes have completed successfully (for example, backup and
restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current
or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server
has been stopped and restarted), kernel messages, or other server-level error messages.

Front 194

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 must make sure that an operator named
ReactGroup is informed by receiving an e-mail.
So what action should you perform to achieve this goal?
A. First you should enable Notifications. Direct the Notification to the ReactGroup operator on job failure after
you create the ReactGroup operator.
B. 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.
C. First you should assign the ReactGroup operator as the failsafe operator. Then enable the job after you
create the ReactGroup operator.
D. 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.

Back 194

Answer: A

Front 195

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 must make sure
that after it is completed successfully, the job is removed.
What should you do?
A. You should create a job which is assigned to the Database Maintenance category
B. You should create an Alert which will run another to delete the maintenance job.
C. You should create a job. You schedule the job to run only once.
D. You should create a job. The job uses the Automatically delete job option

Back 195

Answer: D

Front 196

You have to perform the backup of the default trace according to the requirement of your failure recovery plan.
The default trace is contained in the subdirectory. You have to backup the subdirectory.
Which SQL Server database engine subdirectory should be backed up?
A. DATA
B. INSTALL
C. LOG
D. BINN

Back 196

Answer: C

Use the default trace enabled option to enable or disable the default trace log files. The default trace
functionality provides a rich, persistent log of activity and changes primarily related to the configuration options.
Purpose
Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log
data necessary to diagnose problems the first time they occur.
Viewing
The default trace logs can be opened and examined by SQL Server Profiler or queried with Transact-SQL by
using the fn_trace_gettable system function. SQL Server Profiler can open the default trace log files just as it
does normal trace output files. The default trace log is stored by default in the \MSSQL\LOG
directory using a rollover trace file. The base file name for the default trace log file is log.trc. In a typical
installation of SQL Server, the default trace is enabled and thus becomes TraceID 1. If enabled after installation
and after creating other traces, the TraceID can become a larger number.

Front 197

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.
What process should you implement?
A. You should create a cached data collection
B. You should create an on-demand non-cached data collection
C. You should create a scheduled non-cached data collection
D. You should create two different SQL Agent jobs that are scheduled simultaneously

Back 197

Answer: A

Data collection and upload
Specifies how data is collected and uploaded to the management data warehouse. Pick one of the following
options.
Cached. Collection and data upload not on the same schedule

Front 198

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. Now 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.
So what action should you perform to achieve this goal?
A. You should recapture the workload by using the data collector.
B. You should enable the XP_MSVER stored procedure on the local server.
C. You should enable the XP_MSVER stored procedure on the remote server
D. You should recapture the workload to a trace file by using SQL Profiler.

Back 198

Answer: D

Explanation/Reference:
In order to use the Database Engine Tuning Advisor, you need one trace file, here in the question, there is a
trace table availlable, which is not good. We have then to restart one trace to a file.

Front 199

You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance which contains a database. Now
your company CIO assigns a task to you. 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. So in the options below, which dynamic management view should you choose to use?
A. sys.dm_os_wait_stats
B. sys.dm_exec_requests
C. sys.dm_exec_sessions
D. sys.dm_exec_query_stats

Back 199

Answer: D

Front 200

You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. 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?
A. The workload group which is used by default
B. The workload group which is used by the finance application
C. The classifier function which identifies the finance application
D. The workload group which is used by the manufacturing application

Back 200

Answer: D

Front 201

You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance. For some reason, now 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?
A. SQLServer:Buffer Manager
B. SQLServer:Access Methods
C. SQLServer:General Statistics
D. SQLServer:Exec Statistics

Back 201

C. SQLServer:General Statistics

Front 202

There are two partitions in the BillHistory table. Partition1 is empty while partition2 contains all data.
Now 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 must make sure that at the end of every month the process can be repeated.
A. 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.
B. 1. Alter the partition function of the Bills table by using the split option.
2. Alter the Billstable by using the merge option.
C. 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.
D. 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.

Back 202

Answer: C
Explanation/Reference:
1. split the boundaries of the partition function of the target table
2. switch the appropriate partition of source table to the target table
3. merge the boundaries of the partition function of the target table
4. merge the boundaries of the partition function of the source table

Front 203

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.
In the options below, which utility should you choose to use?
A. bcp.exe
B. dta.exe
C. dtexec.exe
D. sqlmaint.exe

Back 203

Answer: C

Front 204

There is a n On-Line Analytical Processing (OLAP) database named in the instance. The database contains a
dimension table named Clients. Ever hour backup of data of the Clients table is performed. But the Clinets table
contains redundant data. You must keep the disk space used to store the Clients table.
In the options below, which compression technology should you use?
A. You should use row compression
B. You should use page compression
C. You should use backup compression
D. You should use windows NTFS file system compression

Back 204

B. You should use page compression

Front 205

There is a database named DB1 in the instance. 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?
A. sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE
GO
B. sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE
GO
C. CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\MAIN_SERVER\Audit\' ) WITH
(ON_FAILURE = SHUTDOWN);
GO
CREATE 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
D. 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

Back 205

Answer: D

Front 206

Now you are in charge of a SQL Server 2008 instance which uses mixed authentication mode. The instance
runs on a computer which has Windows Server 2003 installed. You must 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.
So what action should you perform to achieve this goal? (choose more than one)
A. You should create an SQL Server Agent job. If a rule violation is detected, the job runs periodically to raise
an alert
B. You should modify all logins by using the ALTER LOGIN ... HASHED statement.
C. You should modify all logins by using the ALTER LOGIN ... CHECK_POLICY = ON statement to.
D. You should modify all logins by using the ALTER LOGIN ... CHECK_EXPIRATION = ON statement.
E. You should create a policy by using Policy-Based Management. The policy prevents any violation of these
rules

Back 206

Answer: CE

Front 207

There is a database named DB1 in the instance. 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.
What action should you do perform?
A. Between lines 01 and 02, you should insert the WITH EXECUTE AS 'dbo' clause.
B. Between lines 01 and 02, you should insert the EXECUTE AS USER = 'dbo' statement.
C. You should give the user the ALTER permission on the Service schema
D. You should give the CREATE TABLE permission and permit the user to drop the Service.Table table.

Back 207

Answer: A

Front 208

There is a database named DB1 in the instance. In the Services schema, There is a table named Service.
Table. The Services.Table has to be moved from the Sales schema to a new schema named Costs.
In the options below, which Transact-SQL statement should you execute?
A. ALTER TABLE Service.Table SWITCH TO Costs.Table1;
B. ALTER SCHEMA CostsTRANSFER Service.Table;
C. ALTER USER Service WITH DEFAULT_SCHEMA = Costs;
D. ALTER AUTHORIZATION ON Service.Table1 TO Costs;

Back 208

Answer: B

ALTER SCHEMA : Transfers a securable between schemas.
ALTER SCHEMA schema_name TRANSFER securable_name

Front 209

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 must make sure that the user can access the Dworks database on InstanceB successfully.
So on InstanceB, which Transact-SQL statements should you execute?
A. USE AdventureWorks;ALTER USER Mary WITH LOGIN = Mary;
B.USE AdventureWorks;ALTER LOGIN Mary ENABLE;
C. USE AdventureWorks;ALTER LOGIN Mary WITH PASSWORD = "Tk@87#34" UNLOCK;
D. USE AdventureWorks;ALTER LOGIN Mary WITH DEFAULT_DATABASE = AdventureWorks;

Back 209

Answer: A

ALTER USER : Renames a database user or changes its default schema

Front 210

There is a database named DB1 in the instance. VIP customer data of the company are stored in DB1. The
customers access their profile data by using a Web application.
You must ensure the security of the customer data, that is to say, even if the backup media is lost, you must
guarantee that data files, log files and subsequent backups and so on are as safe as possible.
So what action should you perform to achieve this goal?
A. For both the CustomerDB database and the master database, enable Transparent Database Encryption
B. You should use the built-in encryption functions to encrypt the sensitive data at the cell level
C. You should make the CustomerDB database accessible only through stored procedures and functions.
D. For the CustomerDB database, enable Transparent Database Encryption and back up the transaction log.

Back 210

Answer: D

Front 211

There is a database named DB01 in the instance. The company CIO wants all the application developers can
capture traces to troubleshoot the application that uses the database DB1, and they can perform this by using
SQL Server Profiler.
But they have to be given as little permission as possible. The company CIO assigns this task to you.
So what action should you perform to achieve this goal?
A. All the SQL Server logins of the application developers have to be added to a fixed server role.
B. All the database users of the application developers have to be added to a fixed database role
C. All SQL Server logins of the application developers should be given the appropriate server-level
permissions.
D. All database users of the application developers should be given the appropriate database-level
permissions.

Back 211

Answer: C

ALTER TRACE
Grants or denies the ability to execute a server-side or SQL Server Profiler trace. Without this permission, the
only logins capable of running traces are members of the sysadmin fixed server role.

Front 212

According to the company requirement, the failover response of the cluster has to be tested. The company
assigns this task to you. On the cluster, you have to implement a manual failover.
What should you do? (choose more than one)
A. The contents of a shared folder on the active node should be encrypted by using Encrypting File System
(EFS)
B. You should restore a backup to the active node
C. You should get rid of the shared array from the active node
D. From the active node, you have to uplug the network cable(s)

Back 212

Answer: CD

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

Front 213

Instance01 contains a database which is named SellingHelp. There isthe Products table in the SellingHelp
database. Now according to the company requirement, the Products table has to be replicated to the SQL
Server instances installed in laptops computers. You have to create a Replication topology to perform this. The
Products table is updated from Instance01 when the laptops reconnect to the corporate network.
The Products table on Instance01 is frequently updated between reconnections.
You must make sure that you can implement the Replication topology along with the Subscription type
successfully and meanwhile reduce the bandwidth usage to the least.
So what action should you perform to achieve this goal?
A. You should implement the Snapshot Replication topology along with a Push Subscription.
B. You should implement the Merge Replication topology along with a Pull Subscription.
C. You should implement the Snapshot Replication topology along with a Pull Subscription.
D. You should implement the Transactional Replication topology along with a Pull Subscription

Back 213

Answer: B

Front 214

There is a text file which is named SQL01.log on the E: drive. A SQL Server Agent job executes every 2
minutes and logs information to the text file.
You get a report from users saying that the sever doesn't make response. As the technical support, you check
and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer functions.
You have to identify the reason why the SQL Server Agent service is unresponsive.
In the options below, which log should you check?
A. You should check SQL1.log
B. You should check SQLAGENT.OUT
C. You should check log_xx.trc
D. You should check ERRORLOG

Back 214

Answer: B

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

Front 215

You work in a company which uses SQL Server 2008. You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 cluster. According to the company
requirement, the failover response of the cluster has to be tested. The company assigns this task to you. On
the cluster, you have to implement a manual failover. What should you do? (choose
more than one)
A. The contents of a shared folder on the active node should be encrypted by using Encrypting File System
(EFS)
B. You should restore a backup to the active node
C. You should get rid of the shared array from the active node
D. From the active node, you have to uplug the network cable(s)

Back 215

Answer: CD

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

Front 216

There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?
A. The Security\RequireClientAuthentication server configuration should be set to False
B. The Security\RequireClientAuthentication server configuration should be set to True.
C. The NTLM Security Support Provider Interface (SSPI) provider should be added to the Security
\SecurityPackageList server configuration.
D. The Kerberos Security Support Provider Interface (SSPI) provider should be added to the Security
\SecurityPackageList server configuration

Back 216

Answer: A

Front 217

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.
So what action should you perform to achieve this goal?
A. You should enable notifications to the Windows application event log when the job completes.
B. You should enable write OEM file
C. You should enable all job steps, making them send the output to a file
D. You should include execution trace messages in the SQL Agent Error log

Back 217

Answer: C

Front 218

You administer a SQL Server 2008 instance named CorpPub that contains a database named
SalesSupport. The SalesSupport database contains the Products table.
You plan to create a Replication topology to replicate the Products table to the SQL Server instances installed
in portable computers. When the portable computers reconnect to the corporate network, the Products table is
updated from CorpPub.
The Products table on the CorpPub instance is frequently updated between reconnections.
You need to successfully implement the Replication topology along with the Subscription type by ensuring that
bandwidth usage is minimized.
What should you do?
A. Implement the Merge Replication topology along with a Pull Subscription.
B. Implement the Snapshot Replication topology along with a Pull Subscription.
C. Implement the Snapshot Replication topology along with a Push Subscription.
D. Implement the Transactional Replication topology along with a Pull Subscription.

Back 218

Answer: A

Front 219

You maintain 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?
A. 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.
B. Create a SQL Agent job process on each instance to store and delete performance data in
a single database for all instances.
C. Configure a management data warehouse process on each instance to store and delete
performance data in a single database for all instances.
D. Configure an automated server-side trace process on each instance to store and delete
performance data in a single database for all instances.

Back 219

Answer: C

Front 220

You manage 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?
A. Create a cached data collection.
B. Create a scheduled non-cached data collection.
C. Create an on-demand non-cached data collection.
D. 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.

Back 220

Answer: A

Front 221

You administer a remote 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?
A. Use the data collector to recapture the workload.
B. Use SQL Profiler to recapture the workload to a trace file.
C. Enable the XP_MSVER stored procedure on the local server.
D. Enable the XP_MSVER stored procedure on the remote server.

Back 221

Answer: B

Front 222

TheFinance 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?
A. Perform a full database backup once daily.
B. Perform a full database backup once daily.
Perform a differential backup every two hours during business hours.
C. Perform a full database backup once daily.
Perform a transaction log backup every two hours during business hours.
D. 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.

Back 222

Answer: B

Front 223

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?
A. ALTER SCHEMA Billing TRANSFER Sales.Table1;
B. ALTER USER Sales WITH DEFAULT_SCHEMA = Billing;
C. ALTER AUTHORIZATION ON Sales.Table1 TO Billing;
D. ALTER TABLE Sales.Table1 SWITCH TO Billing.Table1;

Back 223

Answer: A

Front 224

The DB1 database contains the following stored procedure. (Line numbers are included for
reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Sales.Table1') 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
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?
A. Grant the ALTER permission on the Sales schema to User1.
B. Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.
C. Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.
D. Insert the EXECUTE AS USER = 'dbo' statement between lines 02 and 03.

Back 224

Answer: C

Front 225

There is a database named Dworks in the instance. The
Dworks database has the Products table.
Now you have to replicate the Products table to the SQL Server instances at remote locations, so you create a
Merge Replication topology and a Publication to do this.
The Publication has a 21-day retention period. There is a user who goes on a business trip, 30 days later he
comes back and finds that data in his database is not the latest.
The Windows Event log states the following error message:
"Replication: expired subscription dropped."
You must get the latest data in the user's database and make sure that future data changes are appropriately
replicated.
So what action should you perform to achieve this goal?
A. You should recreate the publication
B. You should upload unsynchronized changes.
C. You should reinitialize the publication and generate a new snapshot at once.
D. You should upload unsynchronized changes, and then reinitialize the publication

Back 225

Answer: C

When a subscription expires, it must be
reinitialized, because metadata for the subscription is removed. Subscriptions that are not
reinitialized are dropped by the Expired subscription clean up job that runs on the Publisher. By default, this
job runs daily; it removes all push subscriptions that have not synchronized for double the length of the
publication retention period

Front 226

Now you are in charge of a SQL Server 2008 instance. There is a database developer who is named UserJack.
UserJack views the definitions of all database objects in a database to read data from all user-defined tables,
views, and table-valued functions. For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions, but this
should be achieved by executing as little Transact-SQL statements as possible.
In the options below, which Transact-SQL statements should you execute?
A. GRANT VIEW ANY DEFINITION TO UserJack;EXEC sp_addrolemember 'db_datareader', 'UserJack';
B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers',
'UserJack';
C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;
EXEC sp_addrolemember 'Developers', 'UserJack';
D. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader', 'Developers';EXEC
sp_addrolemember 'sp_dbddladmin', 'Developers';EXEC sp_addrolemember 'Developers',
'UserJack';

Back 226

Answer: C

Create ROLE: Roles are database-level securables. After you create a role, configure the database-level
permissions of the role by using GRANT, DENY, and REVOKE. To add members to a database role, use
the sp_addrolemember stored procedure.
2. VIEW DEFINITION:The VIEW DEFINITION permission lets a user see the metadata of the securable on
which the permission is granted. However, VIEW DEFINITION permission does not confer access to the
securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see
metadata related to the table in the sys.objects catalog view. However, without additional permissions such
as SELECT or CONTROL, the user cannot read data from the table.
3. GRANT SELECT: Grants permissions on a securable to a principal.

Front 227

Clustered Index Insert Showplan Operator

Back 227

The Clustered Index Insert Showplan operator inserts rows from its input into the clustered index specified in the Argument column. The Argument column also contains a SET:() predicate, which indicates the value to which each column is set. If Clustered Index Insert has no children for insert values, the row inserted is taken from the Insert operator itself.

Front 228

Clustered Index Update Showplan Operator

Back 228

The Clustered Index Update operator updates input rows in the clustered index specified in the Argument column.

If a WHERE:() predicate is present, only those rows that satisfy this predicate are updated. If a SET:() predicate is present, each updated column is set to this value. If a DEFINE:() predicate is present, the values that this operator defines are listed. These values may be referenced in the SET clause or elsewhere within this operator and elsewhere within this query.

Clustered Index Update is a logical and physical operator.

Front 229

Clustered Index Delete operator

Back 229

The Clustered Index Delete operator deletes rows from the clustered index specified in the Argument column of the query execution plan. If a WHERE:() predicate is present in the Argument column, then only those rows that satisfy the predicate are deleted.

Front 230

Clustered Index Merge Showplan Operator

Back 230

The Clustered Index Merge operator applies a merge data stream to a clustered index. The operator deletes, updates, or inserts rows from the clustered index specified in the Argument column of the operator. The actual operation performed depends on the runtime value of the ACTION column specified in the Argument column of the operator.

Clustered Index Merge is a physical operator.

Front 231

sys.fn_builtin_permissions (Transact-SQL)

Back 231

Returns a description of the built in permissions hierarchy of the server.

Front 232

list all built in permission

Back 232

SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

Front 233

DBCC SHRINKDATABASE (Transact-SQL)

Back 233

Shrinks the size of the data and log files in the specified database.

DBCC SHRINKDATABASE
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
NOTRUNCATE
Compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file. target_percent is optional.
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_percent is ignored if specified with TRUNCATEONLY.

TRUNCATEONLY is applicable only to data files. The log files are not affected.

WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.

Front 234

sysmail_add_principalprofile_sp (Transact-SQL)

Back 234

Grants permission for a database user or role to use a Database Mail profile.

Front 235

ALTER AUTHORIZATION
ON [ <class_type>:: ] entity_name
TO { SCHEMA OWNER | principal_name }

Back 235

Changes the ownership of a securable.
entity_name
Is the name of the entity.

principal_name
Is the name of the principal that will own the entity.
ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal.
Ownership of server-level entities can be transferred only to server-level principals.

Front 236

Transfer ownership of a table
The following example transfers ownership of table Sprockets to user MichikoOsada. The table is located inside schema Parts.

Back 236

ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO

Front 237

System Data Collection Sets

Back 237

The data collector installs three System Data collection sets during the SQL Server 2008 Setup process. These collection sets can be configured to suit your monitoring requirements but cannot be deleted. The System Data collection sets consist of the following:

Disk Usage. Collects data about disk and log usage for all the databases installed on the system.

Server Activity. Collects resource usage statistics and performance data from the server and SQL Server.

Query Statistics. Collects query statistics, individual query text, query plans, and specific queries.

Front 238

Query Statistics Collection Set

Back 238

the Query Statistics collection set gathers data about query statistics and individual query text, query plans, and specific queries. This data, when linked with system-level statistics and activities, enables you to drill down below the session level to an individual query.

This collection set collects data from the following sources:

sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats and other related dynamic management views.

The text of selected batches and queries.

The plan of selected batches and queries.

The normalized text of selected batches.

The Query Statistics collection set uses the Query Activity collector type. The Query Activity collector type collects data by using the QueryActivityCollect.dtsx SSIS package, and uploads data by using the QueryActivityUpload.dtsx SSIS package. For more information about the collection and upload phases of the Query Activity collection type, including the queries that are used, see Query Activity Collector Type.

The following tables provided information about the Query Statistics collection set and its collection item.

Front 239

Server Activity Collection Set

Back 239

The Server Activity collection set provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention. The collection set also provides an encapsulated view of overall system resource utilization that enables you to determine if performance issues are related to activities outside the scope of SQL Server.

This collection set collects data samples from the following dynamic management views:

sys.dm_os_wait_stats

sys.dm_os_latch_stats

sys.dm_os_schedulers

sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_waiting_tasks (using a joined query)

sys.dm_os_process_memory

sys.dm_os_memory_nodes

Additionally, data samples are collected from several system and SQL Server performance counters.

The Server Activity collection set gives you an overall view of the system in terms of resource utilization and resource bottlenecks. Resource usage is tracked in four general areas: CPU, Disk I/O, Memory, and Network. The sampling of sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_os_waiting_tasks enables the correlation of system activity with resource bottlenecks and with blocking problems.

Run on its own, this collection set enables you to associate resource bottlenecks with a blocked session and to show blocking chains at the session level. Although query texts are not collected, you can use the sql_handle and plan_handle information that is collected by the Query Statistics collection set to drill down below the session level.

The following tables provide detailed information about the Server Activity collection set and its collection items.

Front 240

Disk Usage Collection Set

Back 240

The Disk Usage collection set tracks the growth of database and log files and provides file-related statistics such as the average growth (in megabytes) per day.

The collection set has two collection items, Disk Usage - Data Files and Disk Usage - Log Files. Both use the Generic T-SQL Query collector type. The collection set gathers the following data:

Snapshots of data file sizes obtained from the sys.partitions and sys.allocation_units views.

Snapshots of log file sizes obtained from the DBCC SQLPERF (LOGSPACE) command.

Snapshots of I/O statistics from the sys.dm_io_virtual_file_stats function.

The following tables provide detailed information about the Disk Usage collection set and its collection items.

Front 241

A developer in your organisation has added a new table to the company database called PERSON. However, he has forgotten the table structure and his manager needs to recreate the table in his local database. What should you do to help?
Select the table in SQL Server Management studio and..

1)Select Tasks – Script Table as – Create to – File
2)Select Tasks – Script Table as – Select to – File
3)Select Tasks - Import Data
4)Select Tasks – Export Data"

Back 241

1)Select Tasks – Script Table as – Create to – File

Front 242

Your manager is testing your experience of SQL Server 2008 and stored procedures. He wants you to show him which active users are connected to the server, which tasks they are performing and also disk IO usage. However, you can’t use the Activity Monitor window in SMSS. Which sp should you use?
1)sp_who2 ‘active’
2)sp_who2 55
3)sp_who
4)sp_who ‘active’

Back 242

1)sp_who2 'active' - Only the sp_who2 command used in conjunction with ‘active’ will show you active connections and also disk IO usage

Front 243

What is the name given to the system database that records all the system-level information for an instance of SQL Server.? This includes instance-wide metadata such as login accounts, endpoints, linked servers, and system configuration settings.
1)default
2)master
3)model
4)tempdb

Back 243

2)master

Front 244

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Which of the following is a component of SQL Server Agent?
1)All of these
2)Jobs – work to be done
3)Schedules – when job is executed
4)Alerts – notification when event occurs

Back 244

1)All of these

Front 245

What is the name given to the column or combination of columns whose values match the primary key (PK) or unique key in the same or another table. "
1)Foreign Reference
2)Foreign Key
3)Primary Reference
4)Foreign Match

Back 245

2)Foreign Key

Front 246

You are trying to restore a database using the SQL Management Studio but receive an error message stating that the database is in use. You did tell the users to stop using the database temporarily. Which of the following should you do to correct this?
1)Do nothing, wait 30 seconds for the locks to timeout on users connections
2)Use the Activity Monitor and kill connections to the database you are restoring
3)Use the Restrict access to the restored database option
4)Theres nothing you can do but wait for all users to stop using the database

Back 246

2)
In this scenario its best to kill the connections as long as the users have been warned that they should not be using the database. Even if you wait, sometimes connections can stay in the sleeping state.

Front 247

Side by side upgrades are a common choice when installing SQL 2008 alongside an older version of SQL Server such as 2000 or 2005. Which of the following is not an advantage of SQL side by side upgrades?
1)Best suited for very large databases
2)Ability to upgrade platforms from 32 to 64 bit
3)Ability to run SQL Servers side by side for testing and verification
4)Instance naming for connecting applications do not change

Back 247

Answer 4 You should consider several factors before choosing an upgrade strategy. Your strategy should include the need for component-level upgrade, ability to roll back in case of failure, the size of databases, and whether a partial upgrade is needed

Front 248

Which of the following is not a recommended optimization technique for SQL Server?
1)Use a RAID 10 configuration on the server
2)Store Data and Log files the same physical disks
3)Use regular transaction log backups instead of full backups
4)Use multi core processors instead of single core

Back 248

Answer 2)Store Data and Log files the same physical disks

Front 249

What is the name given to the process of replacing a table with multiple smaller tables. Each smaller table has the same format as the original table, but with a subset of the data. Each of these tables has rows allocated to it based on some characteristic of the data, such as specific key ranges.
1)Splitting
2)Partitioning
3)Dividers
4)Subsets

Back 249

2)Partitioning

Front 250

A database administrator working at your company has defined a startup stored procedure that is causing a problem preventing SQL Server from starting. Not only is he in big trouble but you’ve got to find a way round this. Which flag should you use to start SQL 2008 in minimal mode?
1)m
2)d
3)h
4)f

Back 250

The f flag starts SQL Server 2008 in minimal mode (if used from the command prompt). This ensures that only one connection is allowed to the server, giving you a chance to fix any problems.

Front 251

One of the developers at your company has written a CLT stored procedures that’s clever enough to call a web service and update database records based on local weather information. He has packaged and deployed this CLR stored procedure as .NET assembly. However, the code has a major bug and you have been asked to delete the assembly from SQL Server. Which command should you use?
1)DROP ASSEMBLY
2)DELETE ASSEMBLY
3)REMOVE ASSEMBLY
4)CLEANUP ASSEMBLY

Back 251

1)DROP ASSEMBLY

Front 252

You just opened SSMS to connect to the company server to do a manual backup. Your connection request waits and then eventually times out. You cannot get connected no matter how hard you try. Your colleague thinks that the problem is limited resources on the server preventing any additional connections. If the name of the server is SQL1 how can you still connect to the server using SSMS? "
1)SQL1:admin
2)override:SQL1
3)DAC:SQL1
4)admin:SQL1

Back 252

4)admin:SQL1 - The Dedicated Administrator Connection (DAC) enables you to connect and do your work on almost all occasions. To connect to the DAC using SSMS, you add the admin prefix to the server name

Front 253

SQL Server Agent is commonly used to schedule work to be done automatically, such as backing up a database. A SQL Server Agent job contains the definition of the work to be done. Which of the following is FALSE regarding SQL Server jobs? "
1)Each job can be in multiple categories
2)Each job has a name, description and an owner
3)Jobs can be ran manually using SQL Server Management Studio
4)Jobs can have one or more job steps

Back 253

1)Each job can be in multiple categories

Front 254

SQL Server Agent allows you to schedule jobs to perform any number of steps from backing up databases to running SQL scripts. Which of the following is the odd one out with regard to scheduling a job through SQL Server Agent? "Jobs can be run at random times
1)Jobs can be scheduled on a recurring basis
2)Jobs can be run once at a specific date and time
3)Jobs can be run when CPU utilization is idle
4)Jobs can be run at random times

Back 254

1)Jobs cannot be run on a random basis, you need to choose a specific trigger such as date and time, or CPU utilization.

Front 255

When you schedule a SQL Server job you can configure which individuals are alerted about its completion. What is the correct term given to these people that can be alerted?
1)Operators
2)Alert Groups
3)Contacts
4)Notifiers

Back 255

1)operators

Front 256

What is the process of copying, at set intervals, a log backup from a read/write database (the primary database) on a primary server to one or more copies (the secondary databases) that reside on remote servers known as?
1)Linked server
2)Log shipping
3)Dynamic Recovery
4)Linked Measure

Back 256

2)Log shipping

Front 257

Your company has just launched a new in-house application for call monitoring. However, the database is performing poorly and you have been asked to investigate why. You have opted to use the Database Engine Tuning Advisor. What does this tool need to run?
1)Nothing, you just start the advisor during the peak usage period
2)A trace file to run against
3)The database must be offline and not in use
4)An xml configuration file of the database to analyse

Back 257

1)A trace file to run against - Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases

Front 258

You are in the process of installing SQL 2008 as a a side-by-side upgrade. You want to migrate all databases from SQL 2005 to this new instance. Which method could you use to achieve this?
1)Backup the database and restore to the new SQL 2008 instance
2)Any of these options
3)Use the Copy Database Wizard to migrate databases
4)Detach the databases from the old instance and reattach to the new instance

Back 258

2)Any of these options

Front 259

The tempbd plays an even more important role than in previous versions of SQL Server. This includes DBCC operations, event notifications, large object variables and parameters. Its important to ensure that certain settings are enabled for the tempdb database. Which two are likely settings that you should configure? "
1)Auto growth disabled, Full Recovery model selected
2)Auto growth enabled, Compatibility level set to SQL 7
3)Auto growth enabled, Full Recovery model selected
4)Auto growth enabled, Simple Recovery model selected

Back 259

1)Auto growth enabled, Simple Recovery model selected - you also want to ensure that enough space has been preallocated to the tempdb to ensure that most operations are handled. You do this by setting the file size to an appropriate value. Autogrow should be enabled be default but autogrow operations are expensive and time consuming so its best to preallocate space

Front 260

Stored procedures, triggers and functions can all be written in CLR compatible languages in SQL Server 2008. Why would someone want to use CLR store procedures rather than standard T-SQL stored procedures?
1)They are faster
2)T-SQL is outdated and CLR languages are a better choice
3)They require less development effort
4)Type safety

Back 260

4)Type safety - Using the CLR gives all the benefits of writing stored procedures with the benefit of using a type safe, structured framework that is independent of the actual language its written in. However, it is not true that it is any easier, faster or that T-SQL is out of date.

Front 261

Your manager is trying to create a new SQL login using a T-SQL script. This is the command that he is trying to run:
-
CREATE LOGIN users\temp1 FROM WINDOWS MUST CHANGE
-
However, the command fails when he tries to run it in SQL Management Studio. What is wrong with his SQL statement?" "
1)He needs to use WITH SUBJECT as well
2)He can’t use MUST CHANGE with a windows login
3)He needs to use CHECK POLICY = ON
4)He needs to use the HASHED argument for windows logins

Back 261

2)He can’t use MUST CHANGE with a windows login

Front 262

You are worried that your SQL 2008 master database is corrupted. However, your manager was smart enough to keep a temporary configuration of the master database that is not corrupt. Which flags should you use to run SQLServr.exe to point it to an alternative master database?
1)e and m
2)f and h
3)n and x
4)d and l

Back 262

4)SQLServr.exe [-d master_file_path] [-l master_log_path]

Front 263

The RESTORE DATABASE T-SQL command can be used to restore a database. You want to restore a database from a physical file on a removable media disk USB) called MyBackup.bak. Which restore parameters should you use?
1)FROM <logical name>
2)FROM DISK [Path to file]
3)STOPATMARK
4)WITH MOVE

Back 263

2)FROM DISK [Path to file]

Front 264

Your manager has asked you to create a new user called Report Manager. They will be responsible for running queries to gather information used for departmental reports. They only need access to the main company database called My Comp. Which database role should you give the person?
1)db_datawriter
2)db_owner
3)db_accessadmin
4)db_datareader

Back 264

db_datareader - Although you could give them db_owner or a combination of db_datareader and db_datawriter, this would be unwise because they are only reading data from the database for reporting purposes.