Mcst 70-432 Question

Front 1

What edition of Win Serve 2008 is not supported for SQL server 2008 installation

Back 1

Win Serve 2008 Server Core is not supported for SQL Server 2008 installation

Front 2

Which operating systems are supported for all edition of SQL Server?

Back 2

Windows Server 2003 Standard SP2 or higher, Windows Server 2008 Standard RC0 or higher

Front 3

You are deploying a new server within Wide World Importers that will be running a SQL server 2008 instance in support of a new application. Because of the feature suppor that is needed you will be installing SQL Server 2008 Enterprise. Which operating system will support your installation? (Choose all that apply)
a)Win 2000 Server Enterprise SP4 or up
b)Win 2003 Server Enterprise
c)Win 2003 Server Enterprise SP2
d)Win Serve 2008 Enterprise

Back 3

Correct C and D
A)SQL Server 2008 is not supported on Win 2000
B)SQL Server 2008 is supported on Win 2003 Enterprise but SP2

Front 4

You are deploying SQL Server 2008 Express in support of a new Web-based application system does NOT support your installation?
A)Win XP home edition sp2
B)Window Server 2008 Server Core
C)Windows Server 2003 Enterprise SP2
D)Windows XP tablet edition SP2

Back 4

B - correct
A) SQL Server 2008 is not supported on Win 2000
C)SQL Server 2008 is supported on Windows Server 2003 Enterprise SP2
D)SQL Server 2008 Expres is supported on Win XP Tablet Edition SP2, but it is not the optimal operating system to use

Front 5

Which edition support the entire feature set available within the SQL Server data Platform? Of these editions which editions are not licensed for production use?

Back 5

Enterprise, Developer, and Evaluation editions have the entire set of features available within the SQL Server 2008 data platform. Developer and Evaluation edition are not licensed for use in a production environment

Front 6

Which edition of SQL Server are designed as storage engines for embedded applications with limited hardware and feature support?

Back 6

Express and Compact edition are designed as storage engines for embedded application and support only a single CPU, up to 1 GB of RAM. and a maximum database size of 4 gb

Front 7

Margie's Travel is opening a new division to offer online travel bookings to their customers. Managers expect the traffic volume to increase rapidly, to the point where hundreds of users will be browsing offerings and booking travel at any given time. Management would also like to synchronize multiple copies of the database of travel booking to support both online and face-2-face operations. Which editions of SQL Server 2008 would be appropriate for Margie's Travel to deploy for their new online presence?
A)Express
B)Standard
C)Enterprise
D)Compact

Back 7

Correct - B,C
A)Although Express could probably handle the user load it is not capable of acting as a publisher to synchronize multiple copies of a travel bookings database
D)Compact is not designed to be used as the storage engine for Web-base application

Front 8

Margie's travel decided to minimize the cost and deploy SQL Server 2008 Standard to support the new online division. After a successful launch, managers are having a hard time managing business operations and need to deploy advanced analytics. Which edition of SQL Serve needs to be installed on the new server to support the necessary data analytics?
A)SQL Server 2008 Standard
B)SQL Server 2008 Express with Advanced Service
C)SQL Server 2008 Work group
D)SQL Server 2008 Enterprise

Back 8

Correct - D
A,B,C) Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining

Front 9

Which edition of SQL Server supports installing more than one instance of SQL Server on a machine

Back 9

Only SQL Server enterprise supports multiple instances of the same machine

Front 10

What are the authentication modes that SQL Server can be configured with?

Back 10

You can configure SQL Server to operate under either Windows only or Windows and SQL Server authentication mode

Front 11

Wide world importers will be using the new FIESTREAM data type to store scanned images of shipping manifests. Which command must be executed against SQL Server instance before FILESTREAM data ca be stored?
A)ALTER DATABASE
B)DBCC
C)sp_configure
D)sp_filestream_configure

Back 11

Correct - d
sp_filestream_configure is used to enable the filestream access level as well as configure the Windows share to be used

Front 12

Contoso has implemented a new policy that requires the passwords on all service accounts to be changed every 30 day. Which tool should the Contoso database administrators use to change the service account passwords so that SQL Server service comply with the new policy?
A)Windows Service Control applet
B)SQL Server Management Studio
C)SQL Server Configuration Manager
D)SQL Server Surface Area Configuration Manager

Back 12

Correct C
Only the SQL Server configuration Manager has the code to decrypt and re-encrypt the service master key used by SQL Server services correctly when the service account or password is changed

Front 13

As part of the implementation of the new Web based booking system at Margies's Travel, customer should receive notices when a travel booking has been successfully saved. What technologies or feature can the developers at Margie's Travel use to implement notifications?(Select 2)
A)Notification Sercvices
B)Database Mail
C)Microsoft Visual Studion. NET code libraries
C)Activity Monitor

Back 13

Correct - B, C
B)Database Mail can be used to send messages to customers
C)Visual Studio .Net application can be created to use the mail libraries available within the .Net framework

Front 14

The developers at Margie's Trave have decided to utilize Databas Mail to send mesages to their customers. The ability to send mail messages through a given profile needs to be restricted, but it must not require an approved user to specify a mail profile when sending messages. What settings need to be configured to meet these requirements?(Select 2)
A. Set the mail profile to public
B. Set the mail profile to private
C. Set the mail profile to private and grant access to approved users.
D. Designate the mail profile as the default

Back 14

Correct C,D: You need to configure mail profile to be private along with granting access to the mail profile for approved users
D)Designating a mail profiles as the default allows approved users to send mail using the profile without the need to specify the profile explicitly

Front 15

What are the types of files that you create for databases and what are the commonly used file extensions?

Back 15

You can create data and log files for a database. Data files commonly have either an .mdf or .ndf extension, whereas log files have an .ldf. extensions.

Front 16

What is the purpose of the transaction log?

Back 16

The transaction log records every change that occurs within a database to persist all transactions to a disk

Front 17

You have a reference database name OrderHistory, which should not allow any data to be modified. How can you ensure, with the least amoun of effort, that users can only read data from the database?
A)Add all database users to the db_datareader role
B)Create views for all the tables and grant select permissions only on the views to database users
C)Set the database to READ_ONLY
D)Grant select permission on the database to all users and revoke insert, update and delete permission from all users on the database

Back 17

C)Set the database to READ_ONLY

Front 18

How do you restrict database access to members of the db_owner role and terminate all active transactions and connections at the same time?

Back 18

You would execute the following cmd:
ALTER DATABASE <database name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

Front 19

What backups can be executed for a database in each of the recovery models?

Back 19

You can create full, diferential, and file/filegroup backups in the Simple recovery model. The Bulk-logged recovery model allows you to execute types of backups, but you cannot restore a database to a point in time during an internal when a minimally logged transaction is executing. All types of backups can be executed in the Full recovery mode

Front 20

You are the database administrator at Blue Yonder Airline and are primarily responsible for the Reservations database, which runs on a server running SQL Server 2008. In addition to customers booking flights through the company's Web site, flights can be booked with server partners. Once an hour, the Reservations database receives multiple files from partners, which are then loaded into the database using the Bulk Copy Program(BCP) utility. You need to ensure that you can recover the database to any point in time while also maximizing the performance of import routines. How would you configure the database to meet business requirements?
A)Enable AUTO_SHRINK
B)Set PARAMETERIZATION FORCED on the database
C)Configure the database in the Bulk-logged recover model
D)Configure the database in the Full recovery model

Back 20

D)Correct
The full recovery model ensures that you can always recover the database to any point in time

Front 21

Which option should be enabled for all production databases?

Back 21

You should set the PAGE_VERIFY CHECKSUM option for all production databases

Front 22

What checks does DBCC CHECKDB perform?

Back 22

DBCC CHECKDB checks the logical and physical integrity of every table, index, and indexed view within the database, along with the contents of every indexed view, page allocations, Service broker data, and database catalog

Front 23

Which cmd are executed when you run the DBCC CHECKDB command?
A)DBCC CHECKTABLE
B)DBCC CHECKIDENT
C)DBCC CHECKCATALOG
D)DBCC FREEPROCCACHE

Back 23

A and C
DBCC CHECKDB executes DBCC CHECKTABLE
DBCC CHECKALLOC
DBCC CHECKCATALOG

Front 24

What are the three new options that you can configure for columns, rows, or pages within a table?

Back 24

You can designate columns as SPARSE to optimize the storage of NULLs. You can apply the FILESTREAM roperty to a VARBINARY(MAX) column to enable the storage of documents in a directory on the operating system that exceed 2 gb. Rows can be compressed to fit more rows on a page. Pages can be compressed to reduce the amount of storage space required for the table, index, or indexed view.

Front 25

Which options are not compatible with row or page compression? (2)
A)A column with a VARCHAR(MAX) data type
B)A sparse column
C)A table with a column set
D)A VARBINARY(MAX) column with he FILESTREAM property

Back 25

A and C
ROWGUIDCOL
IDENTITY
FILESTREAM
are not allowed to be used with S{ARSE columns. Because the NOT FOR REPLICATION option is applied to an identity column, you cannot have a column that is SPARSE, which also has the NOT FOR REPLICATION option, In addition, a column must allow NULLs to be designated as a sparse column.

Front 26

What is the difference between a primary key and a unique constraint?

Back 26

A primary key does not allow NULLs

Front 27

What restrictions does the parent table have when creating a foreign key?

Back 27

The parent table must have a primary key that us used to define the relationship between the parent and child tables. In addition, if the parents primary key is defined on multiple columns, all the columns must exist in the child table for the foreign key to be created

Front 28

Columns with which properties cannot be sparse columns?(2)
A)FILESTREAM
B)NULL
C)NOT FOR REPLICATION
D)NULL

Back 28

A and C
ROWGUIDCOL, IDENTITY, and FILESTREAM are not allowed to be used with SPARSE columns. Because the NOT FOR REPLICATION option is applied to an identity column, you cannot have a column that is SPARSE, which also has the NOT FOR REPLICATION option. In addition, a column must allow NULLs to be designated as a sparse column

Front 29

What type of structure does SQL use to construct an index?

Back 29

SQL Server uses a B-tree structure for indexes

Front 30

What are the three type of pages within an index?

Back 30

And index can contain
root
intermediate
leaf pages
An index has a single root page defined at the top of the index structure. An index can have one or more levels of intermediate pages, but it is optional. The leaf pages are the lowest-level page within an index

Front 31

Fabrikam store product info in the following table:
CREATE TABLE Products.Product
(
ProductID INT INDENTITY(1,1,),
ProductName VARCHAR(30) NOT NULL,
SKU CHAR(8) NOT NULL,
Cost MONEY NOT NULL,
ListPrice MONEY NOT NULL,
ShortDescription VARCHAR(200) NOT NULL,
LongDescription VARCHAR(MAX) NULL,
CONSTRAINT pk_product PRIMARY KEY CLUSTERED (ProductID))

The table is queried either by ProductID, ProductName, or SKU. The application displays ProductName, SKU, ListPrices, and ShortDescription. The ProductID is also returned to facilitate any subsequent operations. Several thousand new products were recently added and now you have performance degradation. Which index should you implement to provide the greater improvement in query performance?
A)CREATE NONCLUSTERED INDEX idx_product ON Products.Products (ProductID, ProductName, SKU)
B)CREATE NONCLUSTERED INDEX idx_product ON Products.Products (ProductName)
C)CREATE NONCLUSTERED INDEX idx_product ON Products.Products (ProductName) INCLUDE (SKY, ListPrice, ShortDescription, ProductID)
D)A)CREATE NONCLUSTERED INDEX idx_product ON Products.Products (ProductName, SKU, ProductID, ListPrice, ShortDescription)

Back 31

Correct answer C
C)This index allows SQL Server to search on ProductName and return the entire result set for the query from the index instead of the table. In addition, the index is kept small by having only the ProductName column define the B-tree and upper levels of the index
A)Because the optimizer considers only the first column in an index when determing the query plan, unless the query returned only ProductID, ProductName and SKU, it is very unlikely that SQL Server would use this index over the primary key on the table
B)Although this index allows SQL Server to locate queries that searched in ProductName, SQL Server must access the table to return the remainder of the data for the result set
D)Although SQL Server could use this index to satisfy queries entirely from the index every level of the index would be built on a 250 KB key causing a much larger set of pages to be read

Front 32

What if two indexes have at least one column in common

Back 32

SQL Server can join the two indexes to satisfy a query

Front 33

What is the difference between a clustered and a nonclustered index?

Back 33

2A clustered index imposes a sort order on the data pages in the table. A nonclustered index does not impose sort order

Front 34

Ho does the FILLFACTOR option affects the way an index is built?

Back 34

The FILLFACTOR option reserves space on the intermediate and leaf levels of the index

Front 35

A DB at a retail company that supplies blanks and kits to pen turners. You are designing a database to store characteristics of the products offered. Each product has a variety f characteristics, but not all products have the same set of characteristics. You are planning the index strategy for the database. The most common query will be the following

SELECT a.ProductName, b.ProductType, b.WoodSpecies, b.Color
FROM Product a INNER JOIN ProductAttributes b ON a.ProductID = b.ProductID WHERE b.Color = "X"

Not all products have a Color attribute. Which index strategy would be the most efficient

A)A nonclustered index on Color
B)A nonclustered index on Color that include the ProductType and WoodSpecies columns
C)A filtered, nonclustered index on Color
D)A filtered, nonclustered index on Color that includes the ProductType and WoodSpecies column

Back 35

Correct D)Because the Color column is nullable, the most efficient index for this query includes only the values that were not nullable. In addition, by including the ProductTYpe and WoodSpecies columns, the query could be satisfied entirely by the index

Front 36

What is the difference between the REBUILD and REORGANIZE options of ALTER INDEX?

Back 36

REBUILD de-fragments all levels of an index
REORGANIZE de-fragments inly the leaf level of an index

Front 37

What happens when an index is disabled?

Back 37

An index that is disabled is no longer used bu the optimizer.In addition, as data changes in the table any disabled index is not maintained

Front 38

You are in charge of building the process that loads approximately 150gb of data into the enterprise data warehouse every month. Every table in you data warehouse has at least eight indexes to support data analysis routines. You want to load the data directly into the tables as quickly as possible. Which operation provides the best performance improvement with the least amount of administrative effort?
A)USE a BULK INSERT cmd
B)Drop and re-create the indexes
C)Disable and enable indexes
D)Use integration services to import the data

Back 38

Correct C)By disabling the indexes prior to the load, you avoid all the overhead required to maintain idexes. By using the disable/enable method, you do not have to maintain scripts to recreate the indexes following the data load
A)Although a BULK INSERT statement can load data quickly, if the indexes exist on the table during the data load operation, you incur a very large overhead for the writes to indexes
B)Loading data to a table without any indexes and the creating the indexes after the load is more efficient than loading whit the indexes in place. However, dropping and recreating the indexes takes more effort than disabling and re-enabling
D)It is more efficient to load data into tables that do not have indexes. Integration Services cannot overcome the overhead of index maintenance during a load operation

Front 39

Before you can create a full text index, which structure do you need to create?

Back 39

Full text indexes are contained within full text catalogs. Therefor, you must create a full text catalog prior to creating a full text index

Front 40

What do you need to specify to create a full text index on documents stored within a FILESTREAM?

Back 40

SQL Server stores the documents within a VARBINARY(MAX) column with the FILESTREAM property enabled. In order to create a full text index, you need to also specify a type column that designates what type of document is stored in the VARBINARY(MAX) column to load the appropriate filter for the word breaker in use

Front 41

Your are the database administrator at your company. You need to enable the sales support team to perform fuzzy searches on product descriptions. Which actions do you need to perform to satisfy user needs with the least amount of effort?
A)Create a full text catalog specifying the filegroup for backup purposes and the root path to store the contents of the catalog on the file system
B)Create a full text catalog and specify the filegroup to store the contents of the catalog
C)Create a full text index on the table of product descriptions for the description column and specify NO POPULATION
D)Create a full text on the table of product description for the description column and specify CHANGE_TRACKING AUTO

Back 41

correct B and D
B)Full text catalogs contain full text indexes and the contents of the indexes are stored within the database in SQL Server 2008
D)CHANGE_TRACKING AUTO option enables SQL Server to populate the full text index upon initial creating and migrate changes automatically to underlying data into the index

Front 42

You want to configure your full text indexes such that SQL server migrate changes into the index as quickly as possible with the minimum amount of administrator effor. Which command should you execute?
A)ALTER FULLTEXT INDEX ON <table_name> START FULL POPULATION
B)ALTER FULLTEXT INDEX ON <table_name> START INCREMENTAL POPULATION
C)ALTER FULLTEXT INDEX ON <table_name> SET CHANGE_TRACKING AUTO
D)ALTER FULLTEXT INDEX ON <table_name> START UPDATE PIPULATION

Back 42

Correct C
When the CHANGE_TRACKING argument is set to AUTO, SQL Server automatically updates the full text index as changes to underlying data occur. In AUTO mode, no administration intervention is required either manually or via a scheduled job

Front 43

Which predicate performs fuzzy searching by default?

Back 43

FREETEXT and FREETEXTTABLE predicates perform wildcard searches by default

Front 44

Which predicate is used to perform proximity and synonym searches?

Back 44

CONTAINS and CONTAINSTABLE are used for proximity, thesaurus, and inflectional searches

Front 45

You want to search for two terms based on proximity within a row. Which full text predicates can be used ti perform proximity searches?(2)
A)CONTAINS
B)FREETEXT
C)CONTAINSTABLE
D)FREETEXT

Back 45

A and C

Front 46

You want to perform a proximity search based on a weighting value for the search argument. Which options for the CONTAINSTABLE predicate should you use?
A)FORMSOF with the THESAURUS keyword
B)FORMSOF with the INFLECTIONAL keyword
C)ISABOUT
D)ISABOUT with the WEIGHT keyword

Back 46

D

Front 47

What data types cannot be used with partition functions?

Back 47

text
ntext
image
xml
varbinary(max)
varchar(max)
CLR data types
Transct-SQL data type

Front 48

What is the max number of partitions allowed for a table

What is the max number of boundary points allowed per table

Back 48

1000

999

Front 49

How many groups can you specify if you use the ALL keyword when defining a partition scheme?

Back 49

You can specify exactly one filegroup when using the ALL keyword

Front 50

Can you create a new filegroup at the same time that you are creating a partition scheme?

Back 50

No. Any filegroups that you specify in CREATE PARTITION SCHEME statement must already exist in the DB

Front 51

What property must be set to use a cmpoted column as a partitioning key?

Back 51

A computed column must be PERSISTED

Front 52

Which clause of the CREATE TABLE or CREATE INDEX statements is used to partition the object?

Back 52

The ON clause is used to specify the storage structure, filegroup, or partition scheme, for the table or index

Front 53

Which operators are used to add or remove boundary point from a partition function?

Back 53

SPLIT add new boundary points
MERGE removes boundary point

Front 54

Which operator is used to remove a boundary point

Back 54

The SWITCH operator is used to move partitions between tables

Front 55

What are the data formats that BCP supports and the cmd line switches for each format?

Back 55

BCP can work with data in either a character or native format. Thec -c switch designates character mode while the -n switch is used for native mode

Front 56

Which parameter do you specify to export data using a query?

Back 56

The queryout parameter is used to export the result set of a query

Front 57

The Import and Export Wizard is based on which feature of SQL Server?

Back 57

The Import and Export Wizard uses a subset of the SSIS feature

Front 58

Which sources and destinations is the Import and Export Wizard capable of using?

Back 58

You can define any source or destination for which you have an OLE DB provider

Front 59

You want to import data into the Orders table. The table has triggers and check constrains that you want t obe checked to guarantee integrity. You choose to use the BCP utility and specify the -h "CHECK_CONSTRAINTS, FIRE_TRIGGERS" hint to accomplish your task. Which permission must be in place?(2)
A)SELECT permission on the Orders table
B)ALTER TABLE on the Orders table
C)INSERT permission on the Orders table
D)A member of the bulkadmin role

Back 59

A,C

if you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However if you do not disable triggers and check constraints you dont need ALTER TABLE persmissions

Front 60

You are performing a migration on the Order database at Contoso from Oracle to SQL Server. The Order database contains several hundred tables. The CustomerAddress table has an XML column named AddressBook. What is the most efficient, least intrusive way to move the data to the new SQL Server database?
A)Move the Order database from Oracle to SQL Server using replication
B)Unload the data using Oracle utilities and load the data into SQL Server using BCP
C)Move the Order database using the import and export wizard
D)Move the data form Oracle to SQL Server using the OPENROWSER function

Back 60

C

The import and export wizard allows you to connect to Oracle, select multiple tables, and move the data to SQL server in a single operaion

Front 61

You work as the network administrator at CertKiller.com. CertKiller.com has acquired an
enterprise application that runs SQL Server 2008. The stored procedures that the application uses
make use of the EXECUTE AS clause that makes use of the Microsoft Windows accounts.
A new CertKiller.com security policy requires that all enterprise applications must run under the
context of a service account. It is imperative that this account complies with the company policy
that states all accounts must have the least amount of privileges whilst allowing duties to be
carried out. Consequently this service account must have as little privileges as possible. You have
received instructions to configure the SQL Server service to be used in a suitable context for the
installation of the new enterprise application.
What should you do?
A. You should consider running the SQL Server service under the domain user account.
B. You should consider running the SQL Server service under the local system account.
C. You should consider running the SQL Server service under the local Administrator account.
D. You should consider running the SQL Server service under the local service account

Back 61

Answer: A

Front 62

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. The SQL Server 2008 instance contains a database named CK_DB. CK_DB has
its data file and the transaction log file on drive D. You have received instructions to move both the
data file and the transaction log file to drive G.

What should you do?

A. You should consider adding the following Transact-SQL statement:
ALTER DATABASE CK_DB SET RESTRICTED_USER WITH ROLLBACK_IMMEDIATE;
B. You should consider moving the data file and use the following Transact-SQL statement:
ALTER DATABASE CK_DB MODIFY FILE(NAME = CK_DB_Data, FILENAME = mdf');
ALTER DATABASE CK_DB SET MULTI_USER;
C. You should consider adding the following Transact-SQL statement:
ALTER DATABASE CK_DB SET OFFLINE WITH ROLLBACK_IMMEDIATE;
ALTER DATABASE CK_DB mdf';
ALTER DATABASE CK_DB SET ONLINE;
D. You should consider moving the data file and then start the SQL Server service with the
following Transact-SQL statement:
IEXEC sp_attach_single_file_db @dbname = N' CK_DB ',@physname = N'v:\SQLServer\
CK_DB_Data.mdf';
E. You should consider moving the data file and transaction log file and then start the SQL Server
service with the following Transact-SQL statement:
EXEC sp_attach_db @dbname = N' CK_DB ',@filename1 = N'G:\SQLServer\
CK_DB_Data.mdf',@filename2 = ldf';
F. You should consider moving the data file and transaction log file and then start the SQL Server
service with the following Transact-SQL statement:
EXEC sp_attach_db @dbname = N' CK_DB ',@filename1 = N'G:\SQLServer\
CK_DB_Data.mdf',@filename2 = N 'g:\SQLServer \CK_DB_log.ldf';

Back 62

Answer: F

Front 63

How do you detect and log corrupt pages?

Back 63

Execute
ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM

Front 64

Orders DB is critical to company operations and is set to the full recovery model. Daily backups are ran at 1am, differential backups every four hours beginning at 5am and transaction log backups every fie min. If the Orders DB is to become damaged and go off-line, what is the first step in the restore process?

A)Restore the most recent full backup with the NORECOVERY option
B)Restore the most recent differential backup with the NORECOVERY option
C)Back up the transaction log with the NO_TRUNCATE option
D)Back up the transaction log with the TRUNCATE_ONLY option

Back 64

Answer C
The first step of every restore operation is to backup the tail of the log. However the BACKUP LOG cmd write an entry into to the transaction log as well as the master data file. If the db is offline, you can backup the transaction log but not wrote to the master data file. The NO_TRUNCATE option allows you to back up the transaction log without writing to the master data file

A)If you start with a restore of the most recent ful lbackup you lose any transactions that have not yet been backed up
B)You cannot start a restore sequence with a differential backup
D)The TRUNCATE_ONLY option of the BACKUP LOG cmd no longer exists in SQL Server 2008

Front 65

Which two features are not compatible with DB snapshots?

Back 65

FILESTREAM
full text indexes

Front 66

Prior to reverting a db using a DB snapshot, what must be done?

Back 66

You must drop all DB snapshots except the DB snapshot being used as the source for the RESTORE cmd

Front 67

What are the two parts of an endpoint

Back 67

An endpoint has a transport defined as either TCP or HTTP and has a payload defined as TSQL, SERVICE_BROKER, DATABASE_MIRRORING or SOAP

Front 68

What are the 3 states of an endpoint, and what is the difference between each state?

Back 68

STARTED - listens for and allows connections
STOPPED - listens for connections and returns error messages
DISABLED - does not respond to any request

Front 69

What authority must be granted before an endpoint allows a connection request?

Back 69

To allow a connection request, the log in that is being used must have been granted the CONNECT permission on the endpoint

Front 70

What types of authentication are availabe for Service Broker and Database Mirroring endpoints?

Back 70

NTML
Kerberos
NEGOTIATE

Front 71

What are the two universal arguments for TCP endpoints?

Back 71

You are required to specify a port for the endpoint to listen on.

Front 72

first sql server specific virus

msde
cdoc

Back 72

slammer Trojan

Microsoft database engine
cross database ownership changing

Front 73

Biggest potential threat to an instance

Back 73

through the use of features that expose an external interface or ad hoc execution capability. The two features with the greatest risk are:
OPENROWSET/OPENDATASOURCE
OLE Automation procedure

Front 74

Features that should have been disabled unless specifically needed

Back 74

Ad Hoc Distributed Queries
CLR Enabled
Cross Database Ownership Chaining CDOC
Database Mail
External Key Management
Filestream Access Level
OLE Automation Procedures
Remote Admin Connections
SQL Mail extended stored procedures

Front 75

use of OLE Automation procedure

Back 75

exist within SQL Server to provide some basic interoperability with previous versions

Front 76

CLR uses

Back 76

with the inclusion of CLR in SQL server 2005 any application that need the services of Object Linking and Embedding automation should be re-writen as Visual Basin .Net or C#.Net assemblies

Front 77

OPENROWSET and OPENDATASURCE

Back 77

expose you to attack by allowing applications to embed security credentials into code that spawns a connection to another instance

Front 78

CDOC

Back 78

Cross Database Ownership
allows you to transfer execution authority across DBs. When enabled the owner of the database contain the object being called effectively cedes control to another db owner

Front 79

SQL Server and Surface
"Configuration Manager

Back 79

exist only in 2005. The functionality of it is provided bu SQL Server Config Manager

Front 80

How do you configure an instance so only local connections are allowed

Back 80

The TCP/IP provider enables connections to be created to the instance remotely. By disabling the TCP/IP provide, you can create only local connections to the instance

Front 81

What do you use to enable or disable feature for an instance

Back 81

The sp_confgure system stored procedure is used to enable or disable feature

Front 82

Which logins cannot be used to authenticate to an instance

Back 82

you cannot use logins that are mapped to a cert or asymmetric key to authenticate to an instance

Front 83

what db principal was create as a replacement for an application role

Back 83

Loginless users are the replacement for an application role

Front 84

Impersonations

Back 84

you can impersonate another principal to execute commands in a specific user context. You must have the IMPERSONATE permission granted to your account on the principal that you want to impersonate

Front 85

cmd to accomplish impersonation

Back 85

{ EXEC | EXECUTE } AS <context_specification>
[;]

<context_specification>::=
{ LOGIN | USER } = 'name'
[ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]
| CALLER

Front 86

master key

Back 86

provides the basis for the encryption hierarchy within SQL and are required before you can creates a certificate or asymmetric key

Front 87

How many SMK?
How many DMK?

Back 87

SMK - single service master key for the entire instance
DMK - database master key within each DB

Front 88

Service master key

Back 88

generated auto first time the instance is started.
Symmetric generated from the local machine key and encrypted using SQL Server Service account

Front 89

Database master key must be generated explicitly using what cmd

Back 89

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <Pass>

Front 90

DMK usage

Back 90

to protect ant certificates, symmetric keys, or asymmetric key stored in a DB.
Encrypted using Triple DES

Front 91

Certificates

Back 91

based on X.509 standard used to authenticate the credentials of the entity supplying the cert. You can create either public or private certs.

Front 92

Public Certs

Priv Certs

Back 92

essentially a file that is supplied by a certificate authority that validates the entity using the cert

generated by and used to protect data within an organization

Front 93

Signatures

Back 93

allow yo uto elevate a users permission but to provide a restriction such that the elevation occurs only when the user is executing a specified piece of code

Front 94

The process of signing code digitally

Back 94

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

Front 95

Wide world importers has just implemented a new order inquiry system. All users with access to the DB need to be able to issue a SELECT statement against any table within the DB. How can you accomplish this functionality with the least amount of effort
a)add the user to the db_datawrites database role
b)gran the users SELECT permission on every table in the DB
c)grant the users SELECT permission on the database
d)grant the users SELECT permission on every schema in the DB

Back 95

c) Correct
When you grant SELECT permission on the DB, the user us able to issue a SELECT statement against any table within the DB
a) db_datawrite role does not grant SELECT permission
b,d) plausible but too much administrative overhead

Front 96

Which statement prevents users from viewing metedata about objects in a single database, even if the user has access to the objects?
a)DENY VIEW DEFINITION\
b)DENY VIEW ANY DEFINITION
c)DENY VIEW SERVER STATE
d)REVOKE VIEW DEFINITION

Back 96

a) correct
DENY VIEW DEFINITION prevents a user from viewing object metadata to which the user would otherwise have access

Front 97

Which object can be used to audit as well as prevent most object changes?

Back 97

DDL triggers can audit any DDL command. If the DDL command executes within a transaction, a DDL trigger can be used to roll back the DDL and prevent the change from occuring

Front 98

Which object is required before you can create a server or database audit specification?

Back 98

You must create a server audit object before a server or database audit specification can be created

Front 99

the HR director of Conto needs to ensure that only authorize users are accessing employee payroll records. What do you need to implement to satisfy these auditing needs?
A)Database audit specification
B)DDL trigger
C)DML trigger
D)Server audit specification

Back 99

a correct
You can create a DB audit specification to log any SELECT, INSERT, UPDATE, DELETE, BCP or BULK INSERT statement executed against the employee pay records table for just the group of users who have access

Front 100

The DBA at Conto have implemented log shipping for the Orders database. To ensure that log shipping cannot break, you need to prevent anyone from changing the recovery model of the DB to Simple. How can you accomplish this task?
a)DDL trigger
b)DML trigger
c)You cant prevent the change of the recovery model
d)Server audit specification

Back 100

c correct
Because ALTER DATABASE can make changes to the file system, which is nontransactionl, you cannot prevent ALTER DATABASE from executing. Therefore the requirement to prevent the change is not possible to implement

Front 101

What object us required to implement TDE?

Back 101

You must create a certificate in the masted DB that is used to encrypt the database encryption key

Front 102

The DBAs manage several sensitive DB containing credit card and customer info. The need to encrypt the entire contents of the DB so that an attacker cannot read information off the disk. How can they meet their requirement with the least amount of effort?
A)Create a certificate in the DB that is used to encrypt the data
B)Create a database encryption key and enable the database for encryption
C)Create a symmetric key in the DB that is used to encrypt the data
D)Create an asymmetric key in the DB that is used to encrypt the data

Back 102

B Correct
TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After you enable it, SQL Server encrypts data on the disk so that it cannot be read by an attacker, without requiring any changes to application

All other types of encryption require changes to the code to encrypt and de-crypt for the application

Front 103

The DBA manage several sensitive databases containing credit card and customer information. Due to recent data thefts at other bank that have made headline, the business wants to ensure that all data within backups is encrypted. How can they accomplish the encryption requirement without needing to change applications?

A)Create a certificate in the DB that is used to encrypt the data
B)Create a database encryption key and enable the database for encryption
C)Create a symmetric key in the DB that is used to encrypt the data
D)Create an asymmetric key in the DB that is used to encrypt the data

Back 103

B Correct
TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After you enable it, SQL Server encrypts data on the disk so that it cannot be read by an attacker, without requiring any changes to application

All other types of encryption require changes to the code to encrypt and de-crypt for the application

Front 104

What are the three items that you define within a trace?

Back 104

events
data columns
filters within a trace

Front 105

Which events are commonly used to establish a performance baseline?

Back 105

The RPC:Completed and SQL:BatchCompleted events are used to establish a performance baseline

Front 106

What does a device activation error means?

Back 106

A device activation error means ttat SQL Server either cannot find or cannot access a data or log file

Front 107

Errors in which three databases prevent SQL Server from starting?

Back 107

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

Front 108

You have a requirement to store documents within one of your databases to centralize the storage and be able to employ full text search capabilities. Because several files exceed 2 gigabytes, you have decided to use the FILESTREAM capabilities of Microsoft SQL Server 2008.

What must you do before you can add a filegroup to the database enabled for FILESTREAM data? (Choose all that apply.)

a)Execute sp_configure and pass the 'filestream access level' parameter along with the appropriate access level.
b)Create a directory to store the documents.
c)Create a table that has a column with the FILESTREAM property.
d)Change the FILESTREAM properties in SQL Server Configuration Manager.

Back 108

a and d
explanation
Enabling FILESTREAM capabilities is a two-step process. You first have to open SQL Server Configuration Manager, right-click the instance, select Properties, and enable FILESTREAM capabilities on the FILESTREAM tab. Then you have to execute sp_configure 'filestream access level' with a second parameter of either 1 or 2 (along with the RECONFIGURE command) to be able to use the FILESTREAM feature within the instance.

You cannot create a table that has a column with the FILESTREAM property until you have a filegroup in the database enabled for FILESTREAM. You can't have a FILESTREAM filegroup until FILESTREAM has been enabled.

You do not have to create a directory for the documents. The directory is created when you add the FILESTREAM filegroup; it cannot already exist.

Front 109

Coho Vineyard has centralized all the backups within the environment. Susan has been hired as the new administrator to manage backups. You need to allow Susan to back up the Microsoft SQL Server databases without also being able to restore a database or view the contents of a database.

Which option meets your requirements with the least amount of administrative effort?
a)add Susan to the sysadmin role
b)Add Susan to the backup operator role in the model DB
c)Add Susan to the serveradmin role
d)Gran Susan Backup Database and Backup Log permissions on each DB

Back 109

Answer: d
Granting Backup Database and Backup Log permissions to Susan enables her to back up the database and transaction log without being able to access the database or restore a database. You have to grant Susan authority on each database that you want her to back up.

Adding Susan to the db_backupoperator role in the model database gives her the authority to back up only the model database.

Members of the serveradmin role do not have the authority to back up a database. Members of the sysadmin role can back up all databases, but you would be elevating Susan's permissions unnecessarily.

Front 110

You are responsible for managing the student database at the local university. The statement that you used to create the Enrollment_Data table is the following:

CREATE TABLE Enrollment_Data
(stu_id int,
stu_first_name varchar (30),
stu_last_name varchar (30),
stu_street_address varchar (30),
stu_city_name varchar (30),
stu_zip_code char (9),
stu_ssnum char (9),
CONSTRAINT stuid_PK PRIMARY KEY (stu_id));

You want to add a table that tracks student fee payments. The fees table should include a column that uses the column for storing Social Security numbers in the Enrollment data table, named stu_ssnum, as a foreign key.

Which of the following statements should you issue so that you can meet your goal of using the Social Security number stored in the Enrollment Data table as a foreign key in the fees table?
(cmd)

Back 110

ALTER TABLE Enrollment_Data
ADD CONSTRAINT stu_ssnum_unique UNIQUE (stu_ssnum);

Front 111

Which dynamic management view or function would you use to determine whether you have contention at a disk level and where the contention is?

Back 111

sys.dm_io_virtual_file_stats

Front 112

You have deployed a new solution that relies on four databases named Control, Products, Customers, and Inventory. All the objects in each database are contained within the dbo schema. All the databases are deployed to the same instance. The database named Control contains all the stored procedures used by the application. The procedures access data in all four of the databases.

How do you configure the environment to allow the application to make calls across databases successfully, with the least amount of administrative effort?
a)Enable cross-database ownership chaining.
b)Set the TRUSTWORTHY property for the Control database.
c)Add a signature to the procedures in the Control database.
d)Create a certificate in the Control database.

Back 112

Answer: a

Front 113

You have implemented transparent data encryption for the Payroll database.

In addition to the Payroll database, what object do you need to back up to ensure that you can recover successfully?
a)the master key for the Payroll DB
b)The master key for the master DB
c)the certificate in the master DB
c)The service master key

Back 113

Answer: c

the certificate in the master DB is used to encrypt the DB encryption key used by TDE. Without the certificate from the master DB, you cannot access the DB or any backup of the DB. However, you cannot simply backup the certificate. You must back up both the certificate as well as the private key for the certificate. If you dont back up the private key for the certificate, the certificate does not have the same signature when restored and does not allow you to gain access to the backup

Front 114

A new application has just been launched at Woodgrove Bank that implements a stored procedure written in Microsoft Visual C#.NET to calculate amortization. All the data required to calculate an amortization is stored within the database where the stored procedure is created.

What do you need to do to ensure that the application can execute the stored procedure?

a)enable the CLR
b)Create a DB master key
c)Set he assembly to EXTERNAL_ACCESS
d)Execute CREATE ASSEMBLY to create the stored procedure

Back 114

(A)
expl:
Although you can create stored procedures using CLR languages such as VB.NET and C#.NET, which are based on a dynamic-link library that is loaded using the CREATE ASSEMBLY statement, the stored procedure cannot execute unless the CLR has been enabled

The assembly does not require EXTERNAL_ACCESS.
A DB master key is not required for CLR stored procedures

Front 115

The backup schedule for the Mineralogical database is as follows:

* Full backup: Friday at 01:00, or 1:00 A.M.
* Differential backups: Saturday through Thursday at 01:00, or 1:00 A.M.
* Transaction log: Every hour on the hour.

On Tuesday, the Mineralogical database crashes at 03:25, or 3:25 A.M.

Which of the following do you need to do to restore data? (Choose 5.)
A)Restore Saturday backup
B)Restore Monday backup
C)Restore Wed backup
D)Restore Tuesday backup
E)Restore Sunday backup
F)Restore 4am transaction log
G)Restore Thursday backup
H)Restore Friday backup
I)Restore 3am transaction log
J)Restore 2am transaction log

Back 115

answer: F,G,H,I,J

expl:
It is necessary to restore the Friday full and Tuesday differential backups, as well as the 2, 3, 4 transaction logs. You should restore the 4am transaction log backup only up until the moment prior to the crash

Front 116

You want to import some data
into the Products table. The table has no identity column, no triggers, and no check constraints. You choose to use the BULK INSERT Transact-SQL command to import the data.

Which of the following permission settings must be in place to import the data? (Choose 2.)
a)You mus have SELECT permission on the Products table
b)You must have the ADMINISTER BULK OPERATION permission or be a member of the bulkadmin fixed server role
c)You must have ALTER TABLE permission on the Product table
d)You must have INSERT permission on the Product table

Back 116

answer:b,d

expl:
To import data by using the BULK INSERT cmd, you need to have INSERT permission on the table you want to load.
You also need to have the ADMINISTER BULK OPERATION instance wide permission. Being a member of the bulkadmin fixed server role grants you exactly that permission, so both permission settings are valid

To import data by using BULK INSERT cmd, you dont need to have SELECT permission on the table you want to load

ALTER TABLE permission is needed only if :
using -E switch to import identity values
the table has constrains (here they are disabled)
the table has triggers and trigger execution disabled

Front 117

You are installing Microsoft SQL Server 2008 and receive an error that the installation failed.

Where do you look for the cause of the installation failure?

Back 117

In the ...\100\Setup Bootstrap\LOG folder

Front 118

Contoso Limited has determined that an application that connects to the Shipping database is having performance issues. The database administrators (DBAs) configure a SQL Server Profiler trace by using the Tuning template.

Which columns do the DBAs need to add to the trace definition to allow them to decide which queries to tune based on system impact?
a)Start Time
b)Binary Data
c)CPU
d)RowCount
e)Writes
f)End Time
g)Reads

Back 118

answer:c,e,g

expl: The duration is only one part of the tuning puzzle. A query that takes 10 milliseconds to execute but runs 10,000 times per minute has a much greater performance impact than a query that requires 30 minutes to execute but runs only once per month. By gathering information on the CPU, Reads, and Writes, DBAs can make more informed decisions regarding the overall impact that a particular query is having on the environment. For example, if a given query that is supposed to return data but not modify it has a large number of writes, this might indicate that the code to gather the data was written by using a cursor or temporary table or was placed in a snapshot isolation level, causing a large amount of write activity that affects overall performance.

The times a query starts and ends are irrelevant factors in tuning a query. Getting a query to run faster is an exercise in reducing the amount of resources (such as central processing unit, memory, and disk input/output) that it uses.

RowCount indicates how many rows were returned. This total can vary greatly even when executing the same query multiple times unless the data is not allowed to be modified. In addition, because of the way that indexes allow large amounts of data to be rapidly accessed, large variations in this value or even large values are not an indication of performance issues.

Binary Data is captured when you select Showplans and several other events. The Tuning template captures only the RPC:Completed, SP: StmtCompleted, and SQL:Batch Completed events, which do not return anything for the Binary data column.

Front 119

You are implementing a log shipping configuration for a local government office. The primary server has Microsoft SQL Server 2008 Enterprise installed, and the secondary server has SQL Server 2008 Workgroup installed.

The secondary database is used by a reporting application that sends ad hoc queries. Users complain that the reporting application often fails when using the secondary database, but if the application uses the primary database, the application does not fail.

What could you change to respond to the users' complaints? (Choose 2.)
a)Change the restore task to occur after normal working hours
b)Change the setting for the Disconnect Users in the DB When Restoring Backup option
c)Upgrade the secondary server to SQL Server 2008 Enterprise
d)Change the log shipping mode from Standby to No Recovery

Back 119

answer:a,b

When restoring the transaction log, if the users are connected to the DB, they are either disconnected or the restore job fails.
The Standby mode of log shipping support either configuration.
You should choose to schedule the restore task when users are not using the DB or change the option so that they are not disconnected and the restore job fails.

If you changed the log shipping mode to No Recovery, users wont be able to query the DB at all.

Upgrading the secondary server to SQL Server 2008 Ent does not change the behavior of the log shipping mode

Front 120

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

Back 120

C. You should create the package by using the Microsoft SQL Server Import and Export Wizard.

Front 121

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

Back 121

B. You should reproduce only those indexes that ask it every night.

Front 122

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

Back 122

C. You should develop a separate transactional publication at each of the testing areas for the test
results. Develop push subscriptions on each of the testing areas

Front 123

You are managing a SQL Server 2008 instance for the Company.com. In order to search remote
information sources by utilizing the OPENROWSET() function. You should allow users on the
SQL Server sample. In order to solve the problem, which is the correct answer?
70-432
A. You should configure Tranaction Logs for sp_configure configuration
B. You should configure remote control for sp_configure configuration
C. You should configure Agent XPs for sp_configure configuration
D. You should configure Ad Hoc Distributed Queries for sp_configure configuration

Back 123

D. You should configure Ad Hoc Distributed Queries for sp_configure configuration

Front 124

You are checking the security of a SQL Server 2008 instance which includes database
TestDataBase. The following requirements are listed below: There are only successful and failed
logon attempts in log files.If records could not be written to the log files, the SQL Server instance
is closedIn order to set the SQL Server sample to conform to the requirements .Which is the
correct answer?
A. sp_configure 'show advanced options', 0;GORECONFIGURE;GO
B. sp_configure 'default trace enabled', 0;GORECONFIGUREGO
C. sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'common
criteria compliance enabled', 0;GORECONFIGUREGO
70-432
D. sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'common
criteria compliance enabled', 1;GORECONFIGUREGO
E. You should add CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH
='\\MAIN_SERVER\Audit\'v) WITH (ON_FAILURE = SHUTDOWN);GOCREATE SERVER
F. AUDIT SPECIFICATION Audit_SpecificationFOR SERVER AUDIT Srv_Audit
ADD (SUCCESSFUL_LOGIN_GROUP),ADD (FAILED_LOGIN_GROUP) WITH G.
(STATE=ON);GOALTER SERVER AUDIT Srv_Audit WITH (STATE=ON);GO

Back 124

D. sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'common
criteria compliance enabled', 1;GORECONFIGUREGO

Front 125

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

Back 125

B. You should change CLUSTERING_METHOD

Front 126

You are managing a SQL Server 2008 sample which includes an On-Line Analytical Processing
(OLAP) database together with a dimension table called Tester for the Company.com. You
should refresh the Tester table each hour. The Tester table includes data which is not useful. In
order to keep the disk space used to reserve the Tester table.Which is the correct answer?
A. You should the technology of Windows NTFS file system compression
B. You should the technology of Page compression
C. You should the technology of Row compression
D. You should the technology of Transaction compression

Back 126

Correct is B. You should the technology of Page compression

Front 127

You are managing a SQL Server 2008 sample which includes a database called Tester for the
Company.com. There is a table named Production which owns a clustered primary key called
ProductID on the ProductID column in the database and a single XML column called
ProductXml which stores XML data. On the same XML column, there is an XML index. you
increase a new column called ProductName to the Production table. You should make ProductId
primary key. Which is the correct answer?

A. You should change the XML index and set the ALLOW_ROW_LOCKS = ON option.
B. You should use the statement Drop the XML index on the table. and change the primary key.
And the reproduce the XML index.
C. You should make the XML index on the ProductName column disable and change the
primary key.
D. Before Clearing the XML information from the original table by configuring the ProductName
column to NULL. You should transfer the XML data to a temporary

Back 127

Correct is B. You should use the statement Drop the XML index on the table. and change the primary key.
And the reproduce the XML index.

Front 128

You are managing a SQL Server 2008 instance for the Company.com. The SQL Server Import and
Export Wizard should be used to output information from SQL Server to a Microsoft Excel .In order to save the package definition to a file. You should make sure you should run the package
from the command line. Which is the correct answer?

A. You should use the command of sqlmaint.exe
B. You should use the command of update.exe
C. You should use the command of dtexec.exe
D. You should use the command of Register.exe

Back 128

C. You should use the command of dtexec.exe

Front 129

You are managing a SQL Server 2008 instance for the Company.com.In order to know the
amount of workers who processed the SQL Server instance through utilizing Windows System
Monitor. Which is the correct answer?

A. You should capture the object of SQLServer:Exec Statistics
B. You should capture the object of SQLServer:Buffer Manager
C. You should capture the object of SQLServer:Access Methods
D. You should capture the object of SQLServer:General Statistics

Back 129

D. You should capture the object of SQLServer:General Statistics

Front 130

You are managing a SQL Server 2008 instance which includes databases for a shopping
70-432
application and a manufacturing application for the Company.com. You set the Resource
Governor to cut the maximum CPU search time permitted through the applications. The
administrator of the manufacturing application tells you that that some reports could not run
successfully. You should make sure that you change the Resource Governor settings to permit
the manufacturing application to cost more CPU time. Which is the correct answer?
A. The workload group that clarifies the shopping application
B. The classifier function that is used by default
C. The workload group that is utilized by default
D. The workload group which is utilized through the manufacturing application

Back 130

D. The workload group which is utilized through the manufacturing application

Front 131

You are troubleshooting the functions of a SQL Server 2008 instance for the Company.com. In
order to clarify the longest-running common language runtime (CLR) searched, which is the
correct answer?

Back 131

D. You should utilize view of sys.dm_exec_query_stats

Front 132

In order to control the SQL Server sample to remember the use of features which will be stopped
when you move a web application from Microsoft SQL Server 2005 to Microsoft SQL Server
2008? Which is the correct answer?
A. You should utilize Transaction Log.
B. You should utilize a SQL Server Profiler which gets the Deprecation Announcement and
Deprecation Final Support event classes.
C. You should utilize a store procedure which gets the Deprecation Announcement and
Deprecation Final Support event classes.
D. You should utilize a SQL server-side trace which gets the Deprecation Announcement and
Deprecation Final Support event classes.

Back 132

D. You should utilize a SQL server-side trace which gets the Deprecation Announcement and
Deprecation Final Support event classes.

Front 133

You are managing a remote SQL Server 2008 for Company.com. The Clients complaint that
application??s functions are running slowly. In order to get a workload of the remote instance to
a trace table on the remote SQL Server instance. You should use SQL Profiler tool through
utilizing the Database Engine Tuning Advisor, you could analyze the workload of the remote
SQL Server instance on a local SQL Server instance. Which is the correct answer?
A. You should make the XP_MSVER stored procedure enable on the local server.
B. You should utilize SQL Profiler tool to recapture the workload to a trace file.
C. You should make the XP_MSVER stored procedure enable on the remote server.
D. You should utilize SQL Profiler tool to capture the workload to a trace file.

Back 133

B. You should utilize SQL Profiler tool to recapture the workload to a trace file.

Front 134

You are managing a remote SQL Server 2008 for Company.com. The Clients complaint that
application??s functions are running slowly. In order to get a workload of the remote instance to
a trace table on the remote SQL Server instance. You should use SQL Profiler tool through
utilizing the Database Engine Tuning Advisor, you could analyze the workload of the remote
SQL Server instance on a local SQL Server instance. Which is the correct answer?
A. You should make the XP_MSVER stored procedure enable on the local server.
B. You should utilize SQL Profiler tool to recapture the workload to a trace file.
C. You should make the XP_MSVER stored procedure enable on the remote server.
D. You should utilize SQL Profiler tool to capture the workload to a trace file.

Back 134

B. You should utilize SQL Profiler tool to recapture the workload to a trace file.

Front 135

You are managing a SQL Server 2008 sample for Company.com. You decide to keep a
management data warehouse which stores functions data through utilizing the data collector.
You decide to run a process which gathers and uploads information in the management
information warehouse on different schedules. Which is the correct answer?
A. You should develop a cached data collection.
B. You should develop a scheduled non-cached data collection.
C. You should develop an on-demand non-cached data collection.
D. You should develop different SQL Agent jobs.

Back 135

A. You should develop a cached data collection.

Front 136

You are managing a SQL Server 2008 instance for Company.com. The failure recovery plan asks
you to backup the default trace In order to backup the subdirectory which includes the default
traces, which is the correct answer?
A. You should utilize LOG
B. You should utilize TRANSACTION LOG
C. You should utilize DATA
D. You should utilize INSTALL

Back 136

A. You should utilize LOG

Front 137

You are managing a SQL Server 2008 instance which owns a stored procedure which runs a
database maintenance process for Company.com. Not only you should develop a SQL Server
Agent job which implements the stored procedure, but also you should make sure that the job is
deleted after completion. Which is the correct answer?
A. You should develop Transactions Log which is scheduled to run twice.
B. You should develop a job that utilizes the automatically remove job option
C. You should develop a job which belongs to the Database Maintenance category.
D. You should develop an Alert that will be raised when the job completes. you could call anther
a job when you are using this alert.

Back 137

B. You should develop a job that utilizes the automatically remove job option

Front 138

You are managing some SQL Server 2008 instances for Company.com. Not only you should
include the prefix usp_ on all instances using all user-defined stored procedures, but also you
should make sure that you could not develop stored procedures which do not include this prefix.
Which is the correct answer?
A. You should develop a method which aims the name of the stored procedure which is
evaluated on change.
B. You should develop a method which aims the name of the stored procedure which is
evaluated on demand.
C. You should develop a method which aims the name of the stored procedure which is
evaluated on demand and change
D. You should develop a condition which aims the name of the stored procedure which is evaluated on demand

Back 138

A. You should develop a method which aims the name of the stored procedure which is
evaluated on change.

Front 139

You are managing the company database; a SQL Server 2008 should be used in the company.
You should backup the database everyday.You also should make transaction log backups.Every
afternoon and morning. You should know that the content of the log will get larger and larger.
You should take measure to do the log file. Which is the correct answer?
A. You should cut the number of time between transaction log backups for the database.
B. You should set the database to Utilize the simple recovery model for the database.
C. You should exec the SQL sentences in order to make the smallest value for the database.
D. You should exec DBCC SHRINKFILE towards the transaction log file for the database.

Back 139

A. You should cut the number of time between transaction log backups for the database.

Front 140

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

Back 140

B. You should reset the server so that the SQL Server Agent service starts automatically.

Front 141

You are managing a SQL Server 2008 instance which owns a stored procedure which runs a
database maintenance process for Company.com. Not only you should develop a SQL Server
Agent job which implements the stored procedure, but also you should make sure that the job is
deleted after completion. Which is the correct answer?
A. You should develop Transactions Log which is scheduled to run twice.
B. You should develop a job that utilizes the automatically remove job option
C. You should develop a job which belongs to the Database Maintenance category.
D. You should develop an Alert that will be raised when the job completes. you could call anther
a job when you are using this alert.

Back 141

B. You should develop a job that utilizes the automatically remove job option

Front 142

You are managing some SQL Server 2008 instances for Company.com. Not only you should include the prefix usp_ on all instances using all user-defined stored procedures, but also you should make sure that you could not develop stored procedures which do not include this prefix. Which is the correct answer?
A. You should develop a method which aims the name of the stored procedure which is evaluated on change.
B. You should develop a method which aims the name of the stored procedure which is evaluated on demand.
C. You should develop a method which aims the name of the stored procedure which is evaluated on demand and change
D. You should develop a condition which aims the name of the stored procedure which is 70-432 evaluated on demand

Back 142

A. You should develop a method which aims the name of the stored procedure which is
evaluated on change.

Front 143

You are managing a SQL Server 2008 instance for Company.com.In order to clarify whether a
database integrity check (DBCC CHECKDB) was implemented for a special database. Which is
the correct answer?
A. You should check the log file of VIEWDELETE
B. You should check the log file of INDEXDEFRAG
C. You should check the log file of ERRORLOG
D. You should check the log file of SQLAGENT

Back 143

C. You should check the log file of ERRORLOG

Front 144

You are managing a SQL Server 2008 instance which includes a database called TestDB for
Company.com. There are spatial data types in TestDB database. Not only you should run a
database consistency check on TestDB to contain the spatial indexes., but also you should make
sure that You make the effect on the database concurrency is minimized. Which is the correct
answer?
A. You should execute DBCC SHRINKFILE(TestDB)
B. You should execute DBCC INDEXDEFRAG(TestDB)
C. You should execute DBCC CHECKDB (TestDB) WITH VIEWDELETE;
D. You should execute DBCC CHECKDB (TestDB) WITH
EXTENDED_LOGICAL_CHECKS

Back 144

D. You should execute DBCC CHECKDB (TestDB) WITH
EXTENDED_LOGICAL_CHECKS;

Front 145

You’ve just created a new SQL login for your manager using SQL Management Studio. He wanted full access to the Archive database and no access to any other database. However when he tries to connect he receives a permission denied message. You’ve opened the properties page of the users login. Which section should you use to change his permissions?

1)Server Roles
2)General
3)Securable
4)User Mapping

Back 145

4)The User Mappings tab allows you to set explicit permissions to a particular database. Server Roles is the high level permission to the entire server whilst the Securable section should be used for granular permission to specific objects.

Front 146

A member of staff has requested that you install a SQL tool for data mining. They want to use the tool to identify rules and patterns in the company data and determine why things happen and predict what will happen in the future. Which installation component should you install to meet their requirements?

1)Database Engine
2)Analysis Services
3)Reporting Server
4)SQL Agent

Back 146

2)Analysis Services (SSAS) provides a unified and integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining

Front 147

Someone from your organisation is complaining that they can’t connect to the HR SQL Server using SQL Management Studio. However, they can open the Management Studio ok and other people can connect to the server fine. What might be the problem?

1)SQL Client tools have not been installed on the users machine
2)None of these
3)The main server is probably down and needs rebooting
4)The SQL Agent is not running

Back 147

4)The SQL Agent must be running on the users machine to connect to an instance of SQL Server. This is a local service, which can be configured to start automatically. If it has stopped, just start the service from the Control Panel - Services console.

Front 148

You want administer the SQL services that automatically start when a user starts their PC. SQL 2008 comes with a built in tool which allows you to changes services accounts and auto-start services. Which tool is this?

1)SQL Server Configuration Manager
2)SQL Server Surface Area Configuration
3)Notification Services Command Prompt
4)SQL Management Studio

Back 148

4)The SQL Server Configuration Manager tool enables you to specify SQL Server Services options and whether the service starts automatically or manually. You can also stop and start services using this tool.

Front 149

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 149

4)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 150

When you uninstall SQL 2008, you will find that certain files are not removed by the uninstaller. Which files/programs do you normally have to remove manually?

1)Log files
2)The 100 directory under program files/MS SQL Server
3).NET framework 3.5
4)All of these

Back 150

4)all of these

Front 151

Now your are in charge of a SQL Server 2008 instance. You are doing 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 instance. Every five hours, you have to collect and load performance data in the management data warehouse.

Which data collection 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. The two jobs are schdeuled at the same time. One job uploads the data collection and the other job create a data collection

Back 151

Answer C
Data collection and upload

Specifies how data is collected and uploaded to the management data warehouse.

Front 152

Now you are in charge of a SQL Server 2008 instance. Now according to the company requirment, you are designing a consolidated repository of performance data.

You mus make sure that the four requirment below are met:
1. the data collector is used to gather performance info
2. a single DB 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 action will achive your goals

A) You should create a SQL Agent job process on each instance to stroe and delete performance data in a single DB for all instances
B)You should config a management data warehouse process on each isntance, then use this process to store and delete performance data in a single DB for all instance
C) You should configure an automated server-side trace process on each instance, then use this process to store and delete performance data in a single DB for all instance
D) You should create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process, then use this process to store and delete performance data in a single DB for all instance

Back 152

The data collector is a component installed on a SQL Server server, running all the time or on a user-defined schedule, and collecting different sets of data. The data collector the stores the collected data in a relational DB (solve point 2) known as the management data warehouse

The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection accross your DB server and application (solve point 4)

This colleciton point can obtain data from a variaty of sources and is not limited solely to performance data (solve point 1) unlike SQL Trace

The data collector enables you to adjust the scopes of data collection to suit your test and production enviorments. The data collector also uses a data warehouse, a relational database that enables you to manage the data you collect by setting different retention periods for your data (solve point 3)

The data collector supports dynamic tuning for data collection and is extensible through its API

Front 153

On two cluster-ready nodes, you intend to perform an installation of a SQL Server 2008 mission-critical cluster. Constant availability is needed by the mission-critical cluster.

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

Which failover option should you use?

A) You should allow failback only during non-business hours
B) You should immediately allow fail back
C) You should prevent automatic failbacj
D) You should allow failback only during business hours

Back 153

Answer C
Failover/Failback Strategies
An overall cluster failover/failback policy is recommended. Failovers can be controlled in terms of a threshold, meaning that after a certain point, a resource will not be failed over. There are two levels of thresholds: resource and cluster. Depending on how the resource is configured it can affect the group failing over to another node.
In the event of a failover, the cluster group containing the SQL Server resources can be config to fail back to the primary node when and if it becomes available again

By default, this option is set to off because usually there is no problem with continuing on the secondary node. This setting proves an opportunity to analyze and repair the problem on the failed node.

It will also minimize the service disruption

Front 154

There is a DB named Dworks in the instance. You are log shipping the Adventureworks DB to a remote SQL Server 2008 Instance.
The primary SQL Server instance required a restart during the weekend. But you noticed that log shipping has stopped working after the weekend. You have to check log shipping of the Adventureworks database and find out the problems. What do you do?
A) You should use the EXTENDED_LOGICAL_CHECKS option to execute a DBCC CHECKDB statmetn on the AdventureWorks database by.
B) You should identify whether the SQL Server Agent is started on the primary server
C) You should identify whether the AdcentureWorks database uses the Simple recovery model
D) You should identify whether the SQL Serve Volume Shadow Copy Service (VSS) Writes is started on the primary server

Back 154

Answer B

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

1)On the primary: Backup transaciton log
2)On the secondary: Copy the transaction backup and Restore the transaction log

So verify the SQL Server state

Front 155

The Orders database is critical to company operations and is set to the FULL recovery model. You are running full backups daily at 1 A.M., differential backups every four hours beginning at 5 A.M., and transaction log backups every 5 minutes. If the Orders database were to become damaged and go offline, what is the first step in the restore process?
1.Restore the most recent full backup with the NORECOVERY option.
2.Back up the transaction log with the NO_TRUNCATE option.
3.Restore the most recent differential backup with the NORECOVERY option.
4.Back up the transaction log with the TRUNCATE_ONLY option.

Back 155

Answer B

The first step of every restore operation is to back up the tail of the log. However, the BACKUP LOG command writes an entry into the transaction log as well as the master data file. If the database is offline, you can back up the transaction log but not write to the master data file. The NO_TRUNCATE option allows you to back up the transaction log without writing to the master data file.

Front 156

The Human Resources (HR) director at Contoso needs to ensure that only authorized users are accessing employee pay records. What do you need to implement to satisfy these auditing needs?
1.Server audit specification
2.A DDL trigger
3.Database audit specification
4.A DML trigger

Back 156

Answer 3


You can create a database audit specification to log any SELECT, INSERT, UPDATE, DELETE, BCP, or BULK INSERT statements executed against the employee pay records table for just the group of users who have access

Front 157

Which statement prevents users from viewing metadata about objects in a single database, even if the user has access to the objects?
1.DENY VIEW SERVER STATE
2.DENY VIEW ANY DEFINITION
3.REVOKE VIEW DEFINITION
4.DENY VIEW DEFINITION

Back 157

Answer 4


DENY VIEW DEFINTION prevents a user from viewing object metadata to which the user would otherwise have access.

Front 158

You are in charge of building the process that loads approximately 150 GB of data into the enterprise data warehouse every month.Every table in your data warehouse has at least eight indexes to support data analysis routines. You want to load the data directly into the tables as quickly as possible. Which operation provides the best performance improvement with the least amount of administrative effort?
1.Use Integration Service to import the data
2.Drop and recreate the indexes
3.Use a BULK INSERT cmd
4.Disable and enable the indexes.

Back 158

Answer 4

By disabling the indexes prior to the load, you avoid all the overhead required to maintain the indexes. By using the disable/enable method, you do not have to maintain scripts to recreate the indexes following the data load.

Front 159

You want to perform a proximity search based on a weighting value for the search arguments. Which options for the CONTAINSTABLE predicate should you use?
1.ISABOUT
2.FORMSOF with the INFLECTIONAL keyword
3.ISABOUT with the WEIGHT keyword
4.FORMSOF with the THESAURUS keyword

Back 159

Answer 3
ISABOUT performs proximity searches and it also applies weighting if the WEIGHT keyword and weighting value are supplied.

Front 160

Which command is issued to recover the secondary database and enable it to start accepting transactions?
1.EXECUTE ALTER LOGIN
2.RESTORE DATABASE...WITH STANDBY
3.ALTER DATABASE
4.RESTORE DATABASE...WITH RECOVERY

Back 160

Restoring the database with the RECOVERY option brings the database online

Front 161

Which of the following are endpoint options that are required for transactions to be exchanged between principal and mirror databases? (Choose two. Each choice represents a portion of the correct answer.)
1.COMPRESSSION set to ENABLED
2.Port 6083 specified for communications
3.ROLE set to PARTNER
4.STATE configured with the default option

Back 161

answer 2 and 3


You must specify a port number for communications.

To exchange transactions between the principal and mirror database, the endpoint created on the instance hosting these databases must be created with a role of either PARTNER or ALL.

Front 162

You have a list of words that should be excluded from search arguments. Which action should you perform in SQL Server 2008 to meet your requirements with the least amount of effort?
1.Parse the inbound query and remove any common words from the search arguments.
2.Create a stop list and associate the stop list to the full text index.
3.Create a noise word file and associate the noise word file to the full text index.
4.Populate a thesaurus file and associate the thesaurus file to the full text index.

Back 162

Stop lists are created in SQL Server 2008 to exclude words from a full text index as well as search arguments. After the stop list is associated to a full text index, any queries that use the index automatically have any stop words removed from the search arguments.

Front 163

Consolidated Messenger distributes packages between businesses in downtown AbcCity. Orders are received at the central office, and messengers on inline skates receive pickup and delivery instructions through Microsoft Pocket PC-powered devices that use merge replication to synchronize data. When packages are received and delivered, the messenger collects a signature from the customer that triggers an update to the order record. Global positioning system (GPS) devices carried in the messengers' backpacks continuously transmit location data back to the office so that delivery progress can be tracked. Because changes occur to the same order data at both the central office as well as at the messenger, how should you design the database, replication, and conflict resolution?
1.One table that contains the delivery order and receipt/delivery information, one publication from the central office to messengers, and subscriber always wins conflict resolution
2.One table that contains the delivery order and receipt/delivery information, one publication from the central office to messengers, and publisher always wins conflict resolution
3.One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger with the data for the delivery order, one publication from the central office to each messenger with the receipt/delivery data, publisher always wins conflict resolution on the delivery order publication, and publisher always wins conflict resolution on the receipt/delivery data
4.One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger with the data for the delivery order, one publication from the central office to each messenger with the receipt/delivery data, publisher always wins conflict resolution on the delivery order publication, and subscriber always wins conflict resolution on the receipt/delivery data
5.One table that contains the delivery order and receipt/delivery information, one publication from each messenger to the central office, and publisher always wins conflict resolution
6.One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger, and publisher always wins conflict resolution

Back 163

answer 4


The central office owns the portion of the data related to the delivery order and always should have the master copy of the data. The messengers are delivering the packages, so any information related to receipt/delivery is owned by each messenger. Because there are conflicting requirements for data ownership between the portions of data, you need at least two publications because conflict detection cannot be chosen dynamically for each synchronization cycle. The publications should all be created at the central office for management. Because the central office owns the delivery order information, the central office always should win in a conflict. Because the messenger owns the delivery/receipt information, the messenger always should win in a conflict. The configuration described in this answer meets all these requirements.

Front 164

The DBAs at Woodgrove Bank manage several sensitive databases containing credit card and customer information. They need to encrypt the entire contents of the database so that an attacker cannot read information off the disk. How can they meet their requirement with the least amount of effort?
1.Create a database encryption key and enable the database for encryption.
2.Create an asymmetric key in the database that is used to encrypt the data.
3.Create a certificate in the database that is used to encrypt the data.
4.Create a symmetric key in the database that is used to encrypt the data.

Back 164

answer 1


TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After you enable it, SQL Server encrypts data on the disk so that it cannot be read by an attacker, without requiring any changes to applications.

Front 165

A. Datum Corporation specializes in credit card processing services, and its systems need to maintain more than 99.999% (five 9s) of availability. At the same time, the write volume exceeds the capacity of the current hardware and the projected capacity of new hardware that was proposed. Employees have determined that if the writes could be batched in some manner while read operations are offloaded to another server at the same time, the proposed new hardware would provide enough capacity for the next two or three years. To achieve the necessary write capacity and redundancy, they need to deploy five servers that are geographically separated and two more servers that can be used as both a failover and a primary server for read operations. What is the best choice of technology to meet these business needs?
1.Database Mirroring
2.Transactional replication in a central publisher configuration
3.Transactional replication in a peer-to-peer configuration
4.Transactional replication in a queued updating subscriber configuration

Back 165

answer 3


Peer-to-peer replication enables all servers to be readable and writable if desired, meets the uptime requirements, and enables more than two servers to participate in sending changes between them.

Front 166

You are the DBA at Blue Yonder Airlines and the phone rings. The main ticket booking application has just gone offline and cannot be reconnected to the database. You attempt to connect to the SQL Server and find that it is unreachable. You find that the service has stopped, and upon inspecting the error logs, you find a large number of device activation errors. What is the most likely cause of the problem?
1.Someone deleted the ticketing database files.
2.The disk storage system underneath the ticket booking database went offline.
3.The disk storage system underneath the master or tempdb databases went offline.
4.The SQL Server service account was locked out.

Back 166

answer 2

If a disk storage system suddenly goes offline, any databases with files on the offline storage log device activation errors. If the device activation errors occur for the master, tempdb, or mssqlsystemresource database, the entire instance shuts down.

Front 167

Contoso Limited needs to implement a replication architecture that maintains a reporting server. A small number of changes might be made on the report server when necessary corrections to reports need to be sent back to the publisher. The reporting solution needs to be always available, even if the publisher is offline, while also providing the best performance. Which replication option is the best solution?
1.Transactional replication with queued updating subscribers
2.Transactional replication with immediate updating subscribers
3.Transactional replication
4.Merge replication

Back 167

answer 1

Transactional replication with queued updating subscribers allows changes to be made at the subscriber even if the publisher is not available while also providing the best performance of all the options.

Front 168

What types of workloads can DTA use for analysis? (Choose all that apply.)
1.A T-SQL script
2.A trace file containing SP:StmtCompleted events
3.A trace file containing RPC:Completed events
4.A trace file containing Extensible Markup Language (XML) showplans

Back 168

answer 1,2,3

DTA can accept either a file or a table as a workload source. The workload source must have a stored procedure, BULK INSERT, SELECT, INSERT, UPDATE, DELETE, or MERGE statement to be evaluated by DTA.

Front 169

Which options are not compatible with row or page compression? (Choose two. Each forms a separate answer.)
1.A table with a column set
2.A VARBINARY(MAX) column with the FILESTREAM property
3.A sparse column
4.A column with a VARCHAR(MAX) data type

Back 169

answer 1,3

You cannot use either row or page compression with a table that has a sparse column.

A column set is constructed for a group of sparse columns, so because row and page compression is incompatible with sparse columns, it is also incompatible with a column set.

Front 170

Wide World Importers has just implemented a new order inquiry system. All users with access to the database need to be able to issue a SELECT statement against any table within the database. How can you accomplish this functionality with the least amount of effort?
1.Grant the users SELECT permission on every schema in the database.
2.Add the users to the db_datawriter database role.
3.Grant the users SELECT permission on every table in the database.
4.Grant the users SELECT permission on the database.

Back 170

answer 4

When you grant SELECT permission on the database, the user is able to issue a SELECT statement against any table within the database.

Front 171

Contoso has implemented a new policy that requires the passwords on all service accounts to be changed every 30 days. Which tool should the Contoso database administrators use to change the service account passwords so that SQL Server services comply with the new policy?
1.SQL Server Surface Area Configuration Manager
2.SQL Server Management Studio
3.SQL Server Configuration Manager
4.Windows Service Control applet

Back 171

answer 2


Only the SQL Server Configuration Manager has the code to decrypt and re-encrypt the service master key used by SQL Server services correctly when the service account or password is changed.

Front 172

You have configured a Database Mirroring session within your environment. The Principal and Mirror endpoints were created successfully with a ROLE setting of PARTNER and then started. You have verified that you can connect to and authenticate to each endpoint. However, Database Mirroring fails to configure properly. What might be the problem?
1.The encryption setting is different on each endpoint.
2.The encryption is set to AES on each endpoint.
3.The authentication mode is set to NEGOTIATE.
4.The authentication mode is set to NTLM.

Back 172

answer 1

If you have verified the connection and authentication, only a mismatch of encryption settings prevents Database Mirroring from configuring.

Front 173

The developers at Margie's Travel have decided to utilize Database Mail to send messages to their customers. The ability to send mail messages through a given profile needs to be restricted, but it must not require an approved user to specify a mail profile when sending messages. What settings need to be configured to meet these requirements? (Choose all that apply.)
1.Set the mail profile to private.
2.Designate the mail profile as the default.
3.Set the mail profile to public.
4.Set the mail profile to private and grant access to approved users.

Back 173

answer 2 and 4

You need to configure the mail profile to be private along with granting access to the mail profile for approved users.

Designating a mail profile as the default allows approved users to send mail using the profile without the need to specify the profile explicitly.

Front 174

Contoso Limited needs to implement a replication architecture that maintains a reporting server while also ensuring that any changes to the report server do not get sent back to the publisher. Which replication option would be the best solution?
1.Snapshot replication
2.Merge replication
3.Peer-to-peer replication
4.Transactional replication

Back 174

answer - 4 - Transactional replication
Transactional replication sends changes to the reporting server without allowing changes to be sent back to the publisher.

Front 175

You are the database administrator at A. Datum Corporation. Users are complaining that applications cannot connect to the SQL Server. You have verified all the application settings and you can connect to the server from your desktop using SSMS. But the users' applications keep returning an "Access denied" error message. What could be the problem?
1.Users do not have CONNECT permissions on the endpoint.
2.Remote connections are not enabled.
3.The TCP endpoint for TSQL is DISABLED.
4.The TCP endpoint for TSQL is STOPPED.

Back 175

answer 1

If users do not have CONNECT permission on the TCP endpoint for TSQL, they receive an "Access denied" error message.

Front 176

You have three named instances installed on a SQL Server 2008 Standard Edition server. The users cannot access the named instances from remote clients, but they can access the default instance. What is the most likely problem?
a.The SQL Browser service is not running.
b.The default instance is using all TCP ports.
c.The default instance is not running
d.The named instances do not run named pipes.

Back 176

answer d
The SQL Browser service is used to locate the TCP port of named instances. This service is required for remote users to map the name to the TCP port for all named instances.

Front 177

You are configuring the database mail component of SQL Server 2008 Standard Edition and have been asked to ensure that fault tolerance is provided in order to allow for unplanned mail server outages. Which one of the following actions will provide this fault tolerance?
a.Configure the account retry delay to 90 seconds.
b.Configure multiple mail profiles.
c.Set the database executable minimum lifetime to 900 seconds.
d.Configure the logging level as verbose.

Back 177

Answer B
Database mail provides for the configuration of multiple mail profiles so that a secondary or tertiary profile may be used if the first mail profile is not functioning due to a server outage.

Front 178

You have created a full-text index on the Sales_Projects table in the Sales database on your SQL Server 2008 Enterprise Edition installation. The server instance runs on a Windows Server 2008 Enterprise Edition server. You want to populate the index automatically. You created the full-text index without changing any of the default parameters related to population. What must you change in order to ensure that all changes to the table are automatically populated into the full-text index?
a.Set change tracking to manual.
b.Nothing; automatic population is the default.
c.Run the command: ALTER FULLTEXT INDEX ON Marketing_Projects START UPDATE POPULATION.
d.Enable the full-text_auto_update option with sp_configure.

Back 178

answer b
By default the full-text index will be updated any time the underlying table data changes, so you will not have to change any settings to accomplish your objective.

Front 179

GHz, Inc. has installed three SQL Server 2008 servers. The servers are named SQL10, SQL22, and SQL33. Lisa, the network administrator, has asked you to ensure that the SQL Server Agent jobs on all three database servers are backed up so that the jobs can be easily recovered if the servers’ disks fail. What database should you back up on each server?
a.MSDB
b.master
c.tempdb
d.model

Back 179

answer a
The MSDB database contains the SQL Server Agent job information as well as the objects related to jobs such as operators and alerts.

Front 180

Several individuals assist you with the maintenance of your SQL Server servers. You are configuring the servers to have operator objects in SQL Server 2008. Some of the assistants (group 1) can be notified Monday through Friday from 8:00 A.M. to 5:00 P.M. Others (group 2) can be notified only on Monday, Thursday, and Friday from 9:00 A.M. to 4:00 P.M. Will the operator object support this demand and, if so, how?
a.Yes. You can configure each operator individually. Simply check the days they may be notified and configure the workday hours.
b.Yes. You can configure each operator individually. Then disable Saturday and Sunday notifications.
c.No
d.Yes. You can configure all operators as a group and then override the default workday hours with policies.

Back 180

Answer A
Supporting such a configuration is possible; however, if you need different working hours on the different days of the work week, you will not be able to configure this in the normal operator management interface.

Front 181

You have created a policy that ensures the Web Assistant is disabled and the policy runs against several SQL Server 2005 servers and one SQL Server 2008 server. When it runs against the 2008 server, it fails. Why is the policy failing?
a.Because the Web Assistant is disabled by default in SQL Server 2008
b.Because the Web Assistant must be enabled in SQL Server 2008
c.Because the Web Assistant does not exist in SQL Server 2008
d.Because a policy cannot run against both SQL Server 2005 and 2008 servers

Back 181

answer c
To prevent this failure, a condition should be created that first checks for the Web Assistant feature. If the feature is there, you can ensure it is off. If it is not there, there is no need to check to see if it is off.

Front 182

You work as a DBA for a small business. You’ve been asked to ensure that the security of your databases is configured as it should be. When looking at your security policies, you notice that authorization must be provided at three levels. What are the three levels and in what order must they be provided?
a.SQL Server-level, Database-level, Table-level
b.Windows-level, Database-level, SQL Server–level
c.Windows-level, SQL Server–level, Database-level
d.Forest-level, domain-level, local-level

Back 182

Answer C
The Windows-level principals may be domain users/groups or local users/groups. The SQL Server–level principals may be SQL logins or mapped Windows logins. The Database-level principals may be users, mapped logins, database roles, application roles, or the public role.

Front 183

You are running several SQL Server 2008 Standard Edition servers. All of the servers are in the corporate office except one server that is located at a branch office. All servers run on Windows Server 2008. You want to utilize automatic auditing with SQL Server Audit on all servers. What action must be taken first?
a.Upgrade all servers to SQL Server 2008 Enterprise Edition.
b.Set the STATE = ON parameter for an audit specification.
c.Use the CREATE SERVER AUDIT command.
d.Use the CREATE SERVER AUDIT SPECIFICATION command.

Back 183

answer a
Only Enterprise Edition and higher support automatic auditing.

Front 184

You are creating a backup plan. Part of the plan includes backing up the transaction logs for your databases. All databases run on SQL Server 2008 Standard Edition, and the OS used is Windows Server 2008. You plan to use the following code to back up the transaction logs:

BACKUP LOG database_name TO DISK=’logback.bak’;

In addition to backing up the log, what would this code do?
A.Restore the database
B.Back up the database
C.Truncate the database
D.Truncate the log

Back 184

answer d
By default, without the use of the TRUNCATE_ONLY parameter, the log file is truncated after the backup completes. This truncation means that the backed up portion of the log is emptied for reuse.

Front 185

When a SQL Server 2008 database file is corrupted or lost due to drive failure and the transaction log is stored on a separate drive, what is the name of the _____ portion that has not been backed up?
a.Log end
b.Tail log
c.Incomplete transactions
d.Data file

Back 185

answer b

The tail log, or tail of the transaction log, is that portion of the transaction log that has not been backed up.

Front 186

You are the DBA for a small company in Florida. You must restore a database named Sales to 3:17 yesterday afternoon. The current date is Wednesday, June 3, 2009. A full backup is made every night to a file named day-fback.bak. The transaction log is backed up each day at 12 PM and is backed up to a file named day-log.bak. Each week, the files are overwritten. Which three of the following four commands would accurately restore the database to the desired point-in-time if run in the proper order?
a.RESTORE LOG Sales FROM DISK=’TUESDAY-log.bak’ WITH NORECOVERY, UNTIL = ‘Jun 2, 2009, 3:17 PM’;
b.RESTORE DATABASE Sales FROM DISK=’TUESDAY-fback.bak’ WITH NORECOVERY;
c.RESTORE LOG Sales FROM DISK=’TUESDAY-log.bak’ WITH NORECOVERY, STOPAT = ‘Jun 2, 2009, 3:17 PM’;
d.RESTORE DATABASE Sales WITH RECOVERY;

Back 186

answer bcd

You must first restore the full backup. Next, you will restore the transaction log backup. Finally, you will recover the database. UNTIL is not a proper WITH parameter of the RESTORE command.

Front 187

You are the DBA for a carefully planned database. In spite of the intensive planning, the data files are still growing very large. You’ve been asked to look for a solution that reduces the storage space consumed by the database. The performance of the database is not an issue and the users have been very happy with the performance; however, space is quickly running out on the server and the server administrator has indicated that more drive space cannot be purchased until the next budget cycle. What feature can be used to conserve storage space?
a.Table partitioning
b.Filestreams
c.Transparent data encryption
d.Data compression

Back 187

answer d

As long as the server is running Enterprise Edition, data compression can be used. Data compression is not available in Standard Edition.

Front 188

You’ve been asked to evaluate whether data compression would be useful in saving storage space for a database. The database runs on SQL Server 2008 Enterprise Edition, and the instance is installed on a Windows Server 2003 R2 server with 4GB of RAM. What command can you use to estimate the benefits that will be provided by data compression?
a.sp_data_compression_estimation
b.DBCC Compression
c.sp_estimate_data_compression_savings
d.SELECT CompressionEstimate(‘table_name’)

Back 188

answer c
The sp_estimate_data_compression_savings stored procedure can be used to determine the space savings that will be gained through this use of compression.

Front 189

You are troubleshooting a concurrency problem in SQL Server 2008. You suspect that deadlocks may be the problem. What event group in SQL Profiler contains information about deadlocks?
a.Transactions
b.Errors and Warnings
c.Scans
d.Locks

Back 189

answer d

The Locks event group contains events related to lock acquisition, escalation and release. It also contains events for tracing deadlocks.

Front 190

Two applications are accessing the same SQL Server 2008 database. The first application is locking a row in the Products table. A second application is locking a row in the Sales table. Now, the first applications needs to access the same row in the Sales table. What will occur?
a.blocking
b.Corruption
c.stalemate
d.Deadlock

Back 190

answer a
In this scenario, only blocking will occur. Hopefully, the row in the Sales table will be released shortly and then the first application can finish its processes.

Front 191

You manage a SQL Server 2008 server running Standard Edition, another running Enterprise Edition, and another running Workgroup Edition. You want to implement Resource Governor. Which of these servers must be upgraded to a different Edition to use the Resource Governor?
a.Enterprise, Standard, and Workgroup
b.No upgrades are necessary to support the Resource Governor
c.Standard and Workgroup
d.Workgroup Edition

Back 191

correct c
Only Enterprise edition supports the Resource Governor, so the Standard and Workgroup edition machines must be reinstalled with Enterprise edition.

Front 192

You have created a performance data collector set using the Reliability and Performance Monitor on a Windows Server 2008 server. During the same window of time, you captured a SQL Server Profiler trace. What actions should you take to correlate the performance data with the trace?
a.In the Performance Studio, select File > Import Performance Data.
b.In the Reliability and Performance Monitor, select File > Import Trace Data.
c.In SQL Server Profiler, select File > Import Performance Data.
d.In the Correlation Manager, select File > Import Performance Data.

Back 192

answer c

The correlation must take place within the SQL Server Profiler. The Reliability and Performance Monitor does not have the ability to read a trace file.

Front 193

You are the DBA for two SQL Server 2008 servers. SRV1 and SRV2 both run SQL Server 2008 Enterprise Edition on Windows Server 2008. SRV1 houses a database named Eng197. You want to mirror this database on SRV2. You want to provide automatic failover should SRV1 fail. You will need an additional server for this role. Which of the following cannot be used as the witness server in this configuration?
a.SQL Server 2000
b.SQL Server 2008 Standard
c.SQL Server 2008 Express
d.SQL Server 2005 Standard

Back 193

answer a
SQL Server 2000 is unaware of database mirroring. Database mirroring was first introduced in SQL Server 2005.

Front 194

The technology manager in your company has asked you to implement a fault-tolerant database with automatic failover. You have two servers on which to implement the database, and they do not connect to shared storage. Both servers are on the same network segment and run SQL Server 2008 Enterprise Edition. An additional server is on the segment and it runs SQL Server 2008 Express edition. What solution can you use to implement fault tolerance?
a.Failover clustering
b.High-Performance Database Mirroring
c.High-Safety Database Mirroring
d.High-Availability Database Mirroring

Back 194

correct A

High-Availability Database Mirroring provides automatic failover. The SQL Server 2008 Express Edition machine can be used as the witness.

Front 195

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. Management wants the prefix usp_ on all instances using all user-defined stored
procedures; however, they do not want you to develop stored procedures which do not include this
prefix.
What should you do?
A. You should consider using a method that will point to the stored procedure which is evaluated
on demand.
B. You should consider using a method that will point to the stored procedure which is evaluated
on change.
C. You should consider using a method where the condition targets point to the stored procedure
which is evaluated on demand.
D. You should consider using a method where the policy targets point to the stored procedure
which is evaluated on demand.

Back 195

Answer: B

Front 196

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. You have received instruction from management to move a web application from
Microsoft SQL Server 2005 to Microsoft SQL Server 2008. Be reminded that when a web
application is moved then the features will be stopped and you need to control the SQL Server sample to remember the use of features.
What should you do?
A. You should consider using a SQL server-side trace which gets the Deprecation Announcement
and Deprecation Final Support event classes.
B. You should consider using a store procedure which gets the Deprecation Announcement and
Deprecation Final Support event classes.
C. You should consider using Transaction Log.
D. You should consider using a SQL Server Profiler which gets the Deprecation Announcement
and Deprecation Final Support event classes.

Back 196

Answer: A

Front 197

The SQL Server 2008 instance contains a database named CK_DB. Management
wants CK_DB to have the following characteristics.
* I/O streaming access should be enabled on CK_DB.
* The data on CK_DB should be accessible to remote clients.
* Ensure that CK_DB is enabled.
What should you do?
A. You should consider configuring the IIS Service.
B. You should consider configuring the SQL Server service.
C. You should consider configuring the SQL Server VSS Writer.
D. You should consider configuring the Web Service.

Back 197

Answer: B

Front 198

You work as a database administrator at CertKiller.com. CertKiller.com is using SQL Server 2008.
CertKiller.com contains a SQL Server 2008 server named CERTKILLER-DB04. CERTKILLERDB04
will be used to run the Reporting Services. However, the Reporting Services must be able to
Microsoft 70-432: Practice Exam run the database backups and other maintenance job automatically. Because CertKiller.com has
no other SQL systems, you need to make use of the SQL's DNS name.
What should you do?
A. You should consider using the SQL Server Web Service and the Microsoft Distributed
Transaction Coordinator technology.
B. You should consider using the SQL Server Browser and the SQL Server Web Service
technology.
C. You should consider using the Microsoft Distributed Transaction Coordinator and the SQL
Server Browser technology.
D. You should consider using the Internet Information Services and the Microsoft Distributed
Transaction Coordinator technology.

Back 198

Answer: C

Front 199

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server 2008 database that makes use of the Analysis Services and Reporting Services. However, installed components at CertKiller.com adhere to the CertKiller.com security policy. You have received instruction to ensure that you adhere to the CertKiller.com security policy.
What should you do?
A. You should consider changing the configuration of the SQL Server Best Practices Analyzer (BPA) and assign the least amount of privilege.
B. You should consider changing the Default Resource Pool configuration file and run it again.
C. You should consider developing a Transact-SQL script based on the sp_configure stored procedure.
D. You should consider developing policies based on the security policy before applying the policies against a server group which contains the database servers which need to be set.

Back 199

Answer: D

Front 200

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server 2008 instance. You have received instructions to publish an instance of SQL Server Reporting Services (SSRS) that will keep the old information to the same machine, and as default as possible. What should you do?
A. You should consider using the Internet Information Services default Web site and update the SSRS instance.
B. You should consider using the Single mode and update the SSRS database.
C. You should consider using the http.sys listener and install the SSRS instance.
D. You should consider using the SharePoint integrated mode and update the SSRS database.

Back 200

Answer: D

Front 201

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. The SQL Server 2008 instance contains a database named CK_Prod which make
use of the Fulltext indexes. However, some of the records have the word 'root' in CK_Prod. This
causes the Fulltext index, when searching for the root word, an empty resultset. You need to
include 'root' in the query that returned.
What should you do?
A. You should consider adding a full-text index.
B. You should consider changing a full-text index.
C. You should consider rebooting the MSFTESQL services.
D. You should consider reproducing the full-text index.

Back 201

Answer: D

Front 202

You want the tasks in the database to have the following characteristics:
* Examining database integrity.
* Reproducing indexes.
* Stores a report to a text file.
You have received instructions to ensure that you could change the SQL Server instance in such a way that an e-mail notification is sent to the developers when the maintenance plan could not run normally.
What should you do? (Choose two)
A. You should consider disabling a fail-safe operator for the SQL Server Agent.
B. You should consider modifying the SQL Server Agent job so that it runs the database maintenance plan to send e-mail notifications to the developers.
C. You should consider modifying the database maintenance plan to contain a Notify Operator task that will e-mail the proper developers.
D. You should consider enabling a fail-safe operator for the SQL Server Agent.

Back 202

Answer: B,D

Front 203

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. The SQL Server 2008 instance has a stored procedure which runs a database
maintenance process. You were instructed to develop a SQL Server Agent job which implements
the stored procedure; however it should delete the job when it is finished.
What should you do?
A. You should consider developing an Alert that will be raised when the job completes.
In addition you should also set the alert to call another job when activated.
B. You should consider developing a Transactions Log to run twice on a schedule.
C. You should consider developing a job that uses the automatically remove job option.
D. You should consider developing a job which belongs to the Database Maintenance category.

Back 203

Answer: C

Front 204

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. CertKiller.com contains a SQL Agent job that runs every 5 minutes. CertKiller.com
also has a text file called CK_Tst.log on F: drive that logs all the information of the job. One
morning during monitoring you notice the following:
* The SQL Server Agent job does not function
* The SQL Server Agent service does not execute.
You need to make sure why the SQL Server Agent service got lost.
What should you do?
A. You should consider checking the TRANSACTION log.
B. You should consider checking the SQLAGENT.OUT log.
C. You should consider checking the ERRORLOG log.
D. You should consider checking the VIEWDELETE log.

Back 204

Answer: B

Front 205

However, when she tries to login on CK_DB at CKSmp2, she received an error message stating that she is not allowed access. Mia Hamm needs to access CK_DB and you need to make it happen.
What should you do?
A. You should consider using CK_DB and ALTER LOGIN Mia WITH DEFAULT_DATABASE =
CK_DB;
B. You should consider using CK_DB and ALTER LOGIN Mia WITH PASSWORD = 'Re#99$45'
UNLOCK;
C. You should consider the following: USE CK_DB and ALTER LOGIN Mia ENABLE.
D. You should consider using CK_DB and change USER Mia WITH LOGIN = Mia;

Back 205

Answer: D

Front 206

CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_Prod. CertKiller.com has a SQL Server login
named CK_Tst that maps to a CertKiller.com learner administrator named CK_ProdApp in
CK_Prod. You have received instruction from management that the CertKiller.com user can to do
the following:
* That the entire stored procedures which at present exist in CK_Prod can be run.
* That the entire stored procedures will be developed in CK_Prod can be run.
What should you do?
A. You should consider allowing the proper database-level rights to default database.
B. You should consider allowing the proper database-level rights to CK_Tst.
C. You should consider allowing the proper database-level rights to CK_ProdApp.
D. You should consider increasing a new role in the default domain group.

Back 206

Answer: C

Front 207

CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_DB. CK_DB contains a table named CK_Prod
where the Sales Product resides. You have received instruction to move CK_Prod' test to a new
schema named CK_Tester. You need to complete this task using the appropriate Transact-SQL
statement.
What should you do?
A. You should consider adding the following:
ALTER TABLE Products. Test SWITCH TO CK_Tester.Table1;
B. You should consider adding the following:
ALTER AUTHORIZATION ON Products. Test TO CK_Tester;
C. You should consider adding the following:
ALTER USER Products WITH DEFAULT_SCHEMA = CK_Tester;
D. You should consider adding the following:
ALTER SCHEMA CK_Tester TRANSFER Products. Test

Back 207

Answer: D

Front 208

com. CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_Prod. CertKiller.com also has a Windows
group named CertKiller\Managers that makes use of CK_Prod. Some of the users in that group
are members for the db_owner role. However, one member in the CertKiller\Managers group
named Mia is not allowed to access the SQL Server instance. You have received instructions from
the CEO to make sure that the Mia does not access the instance. You need to complete this task
using the appropriate Transact-SQL statement.
What should you do?
A. You should consider using the following:
EXEC dbo.sp_droprolemember 'db_master', "CertKiller\Mia";
B. You should consider using the following:
DROP LOGIN "CertKiller\Mia";
C. You should consider using the following:
CREATE LOGIN "Mia\Mia" FROM Windows;DENY CONNECT SQL TO " Mia\Mia ";
D. You should consider using the following:
REMOVE LOGIN "CertKiller\Mia";

Back 208

Answer: C

Front 209

You work as a database administrator at CertKiller.com. CertKiller.com have 12 retail stores, each
store is equipped with their own SQL Server 2008 computer.
A database table named CK-Sales is used to store point-of-sale transactions. Furthermore, CKSales
also hosts sales information from all 12 of the CertKiller.com stores to accommodate
customer returns of products. Consequently the data should be refreshed from the main office to
all 12 retail stores every hour, on the hour. To this end you make use of a trigger called
trg_Coupon to produce sales coupons based on customer sales and buying patterns. Currently
replication between the server in each of the 12 retail stores and a middle server in the main office occurs in real time. You should receive instruction to eliminate this replication.
What should you do? (Each correct answer presents part of the solution. Choose TWO.)
A. You should consider using multiple merge publications, one at every store and one in the main
office.
B. You should consider using transactions log between all the servers in the CertKiller.com
domain
C. You should consider using snapshot replication.
D. You should consider using merge replication.
E. You should consider setting the trg_Coupon trigger on the server in each retail store to use the
NOT FOR REPLICATION option.

Back 209

Answer: D,E

Front 210

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. The CertKiller.com failure recovery plan demands of you to back up the default
trace. However, you need to backup the subdirectory which comprises of the default traces.
What should you do?
A. You should consider using the TRANSACTION LOG.
B. You should consider using the BACKUP.
C. You should consider using the DATA.
D. You should consider using the LOG.

Back 210

Answer: D

Front 211

CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_DB. CK_DB have 100 GB information where 2
GB of the information is changed daily. CK_DB has its recovery model set for Simple. However,
CertKiller.com states that data loss of more than three hours worth of transactions during business
hours is a NO - NO. You need to ensure to use a backup strategy will use the least number of disk spaces and still complies with CertKiller.com.
What should you do?
A. You should consider running a transaction log backup every three hours during business hours.
B. You should consider running incremental as well as differential database backups daily.
C. You should consider running an incremental backup every three hours during business hours.
D. You should consider running a full database backup daily and a differential backup every three
hours during business hours.

Back 211

Answer: D

Front 212

CertKiller.com is using a SQL Server 2008 instance that contains a stored procedure which is used as the data source for a report. wever, a CertKiller.com user named Mia Hamm complains that the data returned by the report does not comply with normal safety standards. During the investigation you notice that the results are caused by phantom reads. You need to make sure that the report result does not connect to the other users at CertKiller.com. What should you do?
A. You should consider making the database Read Committed Snapshot isolation.
B. You should consider changing the stored procedure to utilize the Snapshot isolation level.
C. You should consider making the database run an Auto Update Statistics.
D. You should consider changing the stored procedure to utilize the Read Committed Snapshot isolation level.

Back 212

Answer: B

Front 213

CertKiller.com is using SQL Server 2008 for its database administration. CertKiller.com has five automated testing areas for completely assembled items. The Testing results of every area are stored on the SQL Server 2008 instance. You have received instructions to create a replication method to ensure that test results flow from the testing areas to the SQL Server efficiently and that it can be used for reporting. What should you do? (Each correct answer forms part of the solution. Choose TWO.)
A. You should consider developing a separate transactional publication at every testing area for the test results
B. You should consider developing a separate snapshot publication at every testing area for the test results.
C. You should consider developing a merge subscription on each distributor to the SQL Server.
D. You should consider developing a push subscription on each testing area.
E. You should consider developing a pull subscription on each distributor to the SQL Server.

Back 213

Answer: A,D

Front 214

CertKiller.com is using a SQL Server
2008 instance. The newly appointed CertKiller.com trainee wants to know what can be used to
check if the database integrity check (DBCC CHECKDB) was implemented for a special database.
What should you reply?
A. You should tell him to check the log file of INDEXDEFRAG.
B. You should tell him to check the log file of ERRORLOG.
C. You should tell him to check the log file of SQLAGENT.
D. You should tell him to check the log file of VIEWDELETE.

Back 214

B. You should tell him to check the log file of ERRORLOG.

Front 215

CertKiller.com is using a SQL Server
2008 instance that contains a database. During monitoring, you notice that the following Transact-
SQL query is running slow:
SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH
(INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510'
With regards to the output, a clustered index exists on the VideoTitle column. Furthermore there is
a nonclustered index on the ReleaseDate column that has the UpcNum and RetailPrice columns.
You also notice that the avg_fragmentation_in_percent is 30 percent, when you explore the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table.
You have recievewd instructions to find the method to resolve the problem.
What should you do?
A. You should consider reproducing the entire index on the VideoTitle table.
B. You should consider deleting the query hint from the query.
C. You should consider changing the query hint to force the optimizer to force an unclustered
index seek.
D. You should consider recreating the index on only the ReleaseDate column.

Back 215

B. You should consider deleting the query hint from the query.

Front 216

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance. You then install an important cluster which allows constant availability on two
cluster-ready nodes. However, you have received instructions from management to make sure
that the service disruption is minimized by setting the cluster to failover.
What should you do?
A. You should consider using the Prevent automatic failback option.
B. You should consider using the Allow failback immediately option.
C. You should consider using the Allow failback only during non-business hours option.
D. You should consider using the Allow failback only during business hours option.

Back 216

A. You should consider using the Prevent automatic failback option.

Front 217

CertKiller.com is using a Microsoft SQL
Server 2008 Analysis Services (SSAS) instance. You have received instruction to deploy a data
mining structure that has the Microsoft Clustering mining model "From Scalable K-Means to Nonscalable
K-Means". You need to modify the attribute of the mining model.
What should you do?
A. You should consider changing SHRINKFILE.
B. You should consider changing MODELLING_CARDINALITY.
C. You should consider changing CLUSTERING_METHOD.
D. You should consider changing INDEXDEFRAG.

Back 217

C. You should consider changing CLUSTERING_METHOD.

Front 218

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 cluster. You have received instructions from management to perform a manual failover on
the cluster.
What should you do? (Choose TWO)
A. You should consider Unplugging the network cable(s) from the active node.
B. You should consider recreating a cluster for a backup to the active node.
C. You should consider using Encrypting File System (EFS).
D. You should consider deleting the shared array from the active node.
E. You should consider unplugging all superfluous cables from the active node.

Back 218

Answer: A,D

A. You should consider Unplugging the network cable(s) from the active node.
D. You should consider deleting the shared array from the active node.

Front 219

CertKiller.com is using a SQL Server
2008 instance. You need to use the Windows System Monitor and process the output of how
many users access the SQL Server instance.
What should you do?
A. You should have the object of SQLServer:General Statistics captured.
B. You should have the object of SQLServer:Exec Statistics captured.
C. You should have the object of SQLServer:Buffer Manager captured.
D. You should have the object of SQLServer:Access Methods captured.

Back 219

A. You should have the object of SQLServer:General Statistics captured.

Front 220

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_Prod. However, recently CK_Prod has
produced deadlock problems. You have received instructions from management to get the
deadlocks on the SQL Server error log.
What should you do? (Choose TWO)
A. You should consider using the SQL Profiler trace.
B. You should consider enabling the Server Auditing for CK_Prod.
C. You should consider setting up the trace flags and reboot the SQL Server instance.
D. You should consider using the Transactions Log.

Back 220

Answer: A,C

Front 221

You work as a database administrator at CertKiller.com. You are working on a SQL Server 2005
database called TestCont and are using a Transact-SQL script to restore TestCont. The Transact-
SQL script is as follows:
RESTORE DATABASE TestCont
FROM contracts_bu_device
WITH CONTINUE_AFTER_ERROR, CHECKSUM, RECOVERY
However, after the restore there were still errors in some page restores in the SQL Server logs.
You have received instructions to repair the database pages as soon as possible.
What should you do?
A. You should consider adding the following:
DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
B. You should consider adding the following:
ALTER DATABASE Contracts SET TORN_PAGE_DETECTION ON
C. You should consider adding the following:
ALTER DATABASE Contracts SET TRUSTWORTHY ON
D. You should consider adding the following:
DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK

Back 221

D. You should consider adding the following:
DBCC CHECKDB (Contracts, REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK

Front 222

CertKiller.com is using a SQL Server
2008 instance. You have received instruction from management to keep a management data
warehouse. It will be used to store functions data through utilizing the data collector. For this, you
need to use a process which gathers and uploads information in the management information
warehouse on different schedules.
What should you do?
A. You should consider developing different SQL Agent jobs.
B. You should consider developing an on-demand non-cached data collection.
C. You should consider developing a cached data collection.
D. You should consider developing a scheduled non-cached data collection.

Back 222

C. You should consider developing a cached data collection

Front 223

You work as a database administrator at CertKiller.com. CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_DB1. The CertKiller.com management would
like the database to conform to the following security requirements:
Only successful and failed logon attempts in log files.
The SQL Server 2008 instance must close if the records are not written to the log files.
You need to set the SQL Server sample to conform to the security requirements.
What should you do?
A. You should consider using the following: sp_configure 'show advanced options', 1;
GO RECONFIGURE; GO
sp_configure 'common criteria compliance enabled', 1;
GO RECONFIGURE GO
B. You should consider using the following:
(STATE=ON); GO
ALTER SERVER AUDIT Srv_Audit WITH (STATE=ON); GO
C. You should consider using the following:
sp_configure 'default trace enabled', 0; GO RECONFIGURE GO
D. You should consider using the following:
sp_configure 'show advanced options', 1;
GO RECONFIGURE; GO
sp_configure 'common criteria compliance enabled', 0;
GO RECONFIGURE GO
E. You should consider adding the following:
CREATE SERVER AUDIT Srv_Audit TO FILE ( FILEPATH ='\\SQL_SERVER\Audit\' ) WITH (ON_FAILURE = SHUTDOWN); GO CREATE SERVER
F. You should consider using the following:
sp_configure 'show advanced options', 0; GO RECONFIGURE;GO

Back 223

A. You should consider using the following: sp_configure 'show advanced options', 1;
GO RECONFIGURE; GO
sp_configure 'common criteria compliance enabled', 1;
GO RECONFIGURE GO

Front 224

CertKiller.com is using a SQL Server
2008 instance. The SQL Server 2008 instance contains a database named CK_DB. You are
planning to collect performance information on the whole examples with the data collector. You
should reserve collected data in CK_DB and to gather and load function data in the management
data warehouse every 12 hours.
What should you do?
A. You should consider developing a cached data collection.
B. You should consider developing an on-demand non-cached data collection.
C. You should consider developing an on-demand cached data collection.
D. You should consider developing a scheduled non-cached data collection.

Back 224

D. You should consider developing a scheduled non-cached data collection.

Front 225

CertKiller.com is using a SQL Server 2008 instance. One morning you have a complaint from a CertKiller.com user that applications on the server is performing very sluggish. However, you do not want to use the table scans get more information because of the poor performance. What should you do?
A. You should consider using the SQLServer:Buffer Cutting utility in the Windows System Monitor.
B. You should consider using the SQLServer:Disk Manager utility in the Windows System Monitor.
C. You should consider using the SQLServer:Databases utility in the Windows System Monitor.
D. You should consider using the SQLServer:Access Methods utility in the Windows System Monitor.

Back 225

D. You should consider using the SQLServer:Access Methods utility in the Windows System
Monitor.

Front 226

CertKiller.com is using a SQL Server 2005 instance. In CertKiller.com there are 15 servers which are used by other administrator at the company. However, they need to comply with the company service level agreements (SLAs) on querying response time. Some of the administrators need information of the method on controlling query response times on the servers. What should you do?
A. You should consider teaching them to use the System Monitor Control Tool.
B. You should consider creating a query that searches the sys.dm_db_partition_stats dynamic management view (DMV) and distribute this search to them.
C. You should consider developing SQL Server Profiler templates, which contain query start times and end times and distribute these templates to the administrators.
D. You should consider developing an XML schema which includes the event and column names of the query response times. That can be captured in SQL Server Profiler.

Back 226

C. You should consider developing SQL Server Profiler templates, which contain query start times
and end times and distribute these templates to the administrators.

Front 227

CertKiller.com is using a SQL Server
2008 instance that contains databases that host two applications for acquiring and selling.
You were busy with the setting of the Resource Governor with the purpose of cutting the maximum
CPU search time permitted through the applications. However, after the setting, you notice that some of the reports of the acquiring application does not execute successfully. You need to
ensure that you alter the Resource Governor settings to permit more CPU time.
What should you do?
A. You should consider permitting the workload group that is utilized by default.
B. You should consider permitting the workload group that clarifies the selling application.
C. You should consider permitting the workload group which is utilized through the acquiring
application.
D. You should consider permitting the classifier function that is used by default.
Answer: C
Explanation:

Back 227

C. You should consider permitting the workload group which is utilized through the acquiring
application

Front 228

CertKiller.com is using a SQL Server
2008 instance that contains a database named CK_DB1. CertKiller.com also contains an
application that makes use of the SELECT command that triggers the population of multiple
tables. This application also makes use of CK_DB1. One morning you have received several
complaints from the users that the application is performing very sluggish. You now need to
control the status of the sample without affecting the application performance.What should you do?
A. You should consider using the Process Monitor Tool.
B. You should consider using the Web Service.
C. You should consider using the SQL Server Resource Governor.
D. You should consider using the Dynamic Management Views.

Back 228

D. You should consider using the Dynamic Management Views.

Front 229

CertKiller.com is using a SQL Server 2008 instance that contains three databases named CK_DB1, CK_DB2 and CK_DB3. Each of these databases runs on different servers.
CK_DB1 and CK_DB2 have an important database that is mirrored. CK_DB3 is a helper and CK_DB1 is considered the principle database. You then implement a patch that requires CK_DB1 and CK_DB2 to be rebooted. However since the patching was done as quickly as possible the database that was online at the time could not use the patch. This database cannot use the patch present which result in that the database does not fail-over to the other partner. What should you have done?
A. You should consider using the patch to the server that implements CK_DB1 before using the patch to the server that implements CK_DB2.
B. You should consider using the patch to the server that implements CK_DB3 before using the patch to the server that implements CK_DB1.
C. You should consider using the patch to the server that implements CK_DB2 before using the patch to the server that implements CK_DB1.
D. You should consider stopping the mirroring session, use the patch to the server that implements CK_DB2 and resume the mirroring session.

Back 229

D. You should consider stopping the mirroring session, use the patch to the server that implements CK_DB2 and resume the mirroring session.

Front 230

CertKiller.com is using a SQL Server
2008 sample named CKSmp1. Furthermore, CKSmp1 contains a database named CK_DB. The
CK_DB uses the bulk-logging recovery mode. You then use the WAN to log ship CK_DB.
Furthermore, CK_DB's indexes are reproduces by a scheduled work. You then make certain that
the log shipping adds the number to the job. You need to minimize the size of the log backups
used by log shipping.What should you do?
A. You should consider using the Full recovery mode on CK_DB.
B. You should consider using the half recovery mode on CK_DB.
C. You should consider deleting the indexes and create new one.
D. You should consider making a compress of the log file backups.

Back 230

D. You should consider making a compress of the log file backups.

Front 231

CertKiller.com is using a SQL Server 2008 instance. CertKiller.com contains a SQL Server 2008 sample named CKSam that contains a database named CK_DB. Furthermore, CK_DB does have a ProductItems table. A Replication topology is needed to copy the ProductItems table to the SQL Server samples included in computers. The only time the ProductItems will be refreshed, is when the computers do not link to the CertKiller.com network. Furthermore, the Products table is from between reconnections frequently. You need to run the Replication topology alongside the Subscription type with the minimum amount of bandwidth usage.
What should you do?
A. You should consider running the Merge Replication topology simultaneously with a Pull Subscription.
B. You should consider running the Merge Replication topology simultaneously with a Push Subscription.
C. You should consider running the Transactional Replication topology simultaneously with a Push Subscription.
D. You should consider running the default Replication Replication topology simultaneously with a Pull Subscription.

Back 231

A. You should consider running the Merge Replication topology simultaneously with a Pull
Subscription.

Front 232

CertKiller.com is using a SQL Server 2008 instance that contains a database named CK_Prod. The database contains a Products table. You have developed a Merge Replication topology and a Publication which owns a 25-day retention period; so that the Products table can be reproduce to the SQL Server instances at remote locations. After a one month break the CertKiller.com user named Mia Hamm, noticed that
CK_Prod does not include her latest data. During the investigation you came upon the following error message: "Replication: expired subscription dropped." Mia Hamm needs the latest data in her database and you should ensure that the information changes are properly doubled. What should you do?
A. You should consider uploading synchronized changes.
B. You should consider reinitializing the publication and produce a new snapshot immediately.
C. You should consider uploading unsynchronized changes and reinitializing the publication.
D. You should consider uploading unsynchronized changes.

Back 232

B. You should consider reinitializing the publication and produce a new snapshot immediately.

Front 233

ABC.com is using a SQL Server 2008
instance that contains a database named ABC_DB. ABC.com contains a lot of customer data
which is processed by the Web application. You need to keep the customer information safe since
it is confidential. This information includes files of data, backups, and log files.
How can this be achieved and still keep the performance and functionality of the web application?
Select one or two.
A. You should consider adding a transaction Log on ABC_DB, and set the information to be
encrypted for a fixed time.
B. Use BitLocker Drive Encryption on the hard drives on the SQL Server.
C. Use EFS (Encrypting File System) on the hard drives on the SQL Server.
D. You should consider enabling the Transparent Database Encryption on ABC_DB and back up
the transaction log.
E. You should consider enabling the Transparent Database Encryption on ABC_DB and enabling
the Transparent Database Encryption on master database.
F. Use cell-level encryption for the specific data that needs to be kept safe.

Back 233

Answer: D
Explanation:
With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have
the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by
using TDE, or the file-level encryption options provided by Windows. TDE is the optimal choice for
bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at
the file level, which is similar to two Windows® features: the Encrypting File System (EFS) and
BitLocker™ Drive Encryption, the new volume-level encryption introduced in Windows Vista®,
both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption,
EFS, or BitLocker.

Front 234

ABC.com is using two SQL Server 2008
sample named ABCSmp1 and ABCSmp2. Furthermore, ABCSmp1 contains a database named
ABC_DB. A ABC.com user named Mia Hamm uses her logon MiaHamm to log on to the database
on ABCSmp1. During the week you have transferred ABC_DB to ABCSmp2. You manually
recreate the Mia Hamm login on ABCSmp2. However, when she tries to login on ABC_DB at
ABCSmp2, she received an error message stating that she is not allowed access. Mia Hamm
needs to access ABC_DB and you need to make it happen.
What T-SQL code should you use? (Each correct option is part of the answer. Choose TWO)
A. Use ABC.com;
B. Use ABC_DB;
C. Use ABCSmp1;
D. Use ABCSmp2;
E. ALTER LOGIN MiaHamm WITH DEFAULT_DATABASE = ABC_DB;
F. CHANGE LOGIN MiaHamm WITH DEFAULT_DATABASE = ABC_DB;
G. ALTER LOGIN MiaHamm WITH PASSWORD = 'pwd1234' UNLOCK;
H. CHANGE LOGIN MiaHamm WITH PASSWORD = 'pwd1234' UNLOCK;
I. CHANGE USER MiaHamm WITH LOGIN = MiaHamm;
J. ALTER USER MiaHamm WITH LOGIN = MiaHamm;
K. ALTER LOGIN MiaHamm ENABLE;
L. CHANGE LOGIN MiaHamm ENABLE;

Back 234

Answer: B,J

B. Use ABC_DB;
J. ALTER USER MiaHamm WITH LOGIN = MiaHamm;

Front 235

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance.
Your junior assistant is curious about on how to move a table, such as ABC_table, from its current
scheme (scheme1) to another scheme (scheme2)
How can you accomplish this in T-SQL?
A. You should consider using the following:
ALTER TABLE Schema1.ABC_Table SWITCH TO Schema2.ABC_table;
B. You should consider using the following:
ALTER AUTHORIZATION ON Schema1.ABC_Table TO Schema2;
C. You should consider using the following:
ALTER SCHEMA schema2 TRANSFER schema1.ABC_table;
D. You should consider using the following:
ALTER SCHEMA schema1 TRANSFER schema2.ABC_table;
E. You should consider using the following:
ALTER USER Schema1 WITH DEFAULT_SCHEMA = Schema2;
F. You should consider using the following:
MOVE ABC_Table FROM SCHEMA schema1 TO schema2
G. You should consider using the following:
MOVE ABC_Table FROM schema1 TO schema2

Back 235

Answer: C
Explanation:
ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.

Front 236

ABC.com is using a SQL Server 2008
instance. ABC.com contains a SQL Server 2008 server named ABC-DB01. There are also SQL
Server Agent jobs on the server. Furthermore the database is backed up at a daily basis. One
morning, the master database on ABC-DB02 went down. You then restore and rebuild the master
database. The ABC.com managers want ABC-DB01 to work as it was in the past.
What steps should you take? (Each correct option is part of the answer. Choose TWO)
A. You should consider rebuilding the entire SQL Server Web Service for the database.
B. You should consider reserving the model database for the database.
C. You should consider reserving the Resource databases for the database.
D. You should consider reserving the tempdb databases for the database.
E. You should consider reserving the master databases for the database.
F. You should consider reserving the msdb databases for the database.
G. You should consider reserving all the Transaction Logs for the database.

Back 236

Answer: B,E

B. You should consider reserving the model database for the database.
E. You should consider reserving the master databases for the database.

Front 237

ABC.com is using SQL Server 2008.
ABC.com contains a SQL Server 2008 server named ABC-DB04. ABC-DB04 will be used to run
the Reporting Services. However, the Reporting Services must be able to run the database
backups and other maintenance job automatically. Because ABC.com has no other SQL systems,
you need to make use of the SQL’s DNS name.
What steps should you take?
A. You should consider using the SQL Server Web Service and the Microsoft Distributed
Transaction Coordinator technology.
B. You should consider using the SQL Server Browser and the SQL Server Web Service
technology.
C. You should consider using the Microsoft Distributed Transaction Coordinator and the SQL
Server Browser technology.
D. You should consider using the Internet Information Services and the Microsoft Distributed
Transaction Coordinator technology.

Back 237

Answer: C

Front 238

ABC.com is using SQL Server 2008 Enterprise Edition. ABC.com contains a database that unfortunately has torn page errors. You need to solve the problem of the torn page errors. What steps should you take? (Each correct option is part of the answer. Choose TWO)
A. By using the newest database backup to restore the full database.
B. By using the newest database backup to restore only the torn page.
C. By restoring the database from the latest snapshot of the database.
D. By restoring the latest database backup and then restore the transaction logs which have been made since the latest backup.
E. By using DBCC CHECKDB.
F. By restoring any transaction logs created since the latest full backup.

Back 238

Answer: B,F

Front 239

ABC.com is using a SQL Server 2008
instance. You are busy with the setting of the Database Mail profile. You want the Database Mail
profile to send reports to all of the users at ABC.com. You then use the sp_send_dbmail stored
procedure to send reports during office hours. You set everything up and it works fine. There are
no complaints from employees. However, after a week employees complain that they have to wait
too long for the daily reports that are sent through the Database Mail system you have set up.
How can you fix this problem to ensure the fastest delivery of these reports?
A. You should consider create another SMTP account that will be used for the delivery reports to
Tess King.
B. You should consider setting a new Database Mail profile that will be used for the delivery
reports to Tess King.
C. You should consider setting a new Database Mail account that will be used for the delivery
reports to Tess King.
D. You should consider increase the priority of the reports that are sent to Tess King.

Back 239

Answer: B

Front 240

ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance contains a database named ABC_DB.
Which service is used when you configure the FILESTREAM data container in the ABC_DB
database?
A. The IIS Service.
B. SQL Server Agent service
C. SQL Server Integration Services
D. Microsoft Search service
E. The SQL Server service.
F. The SQL Server VSS Writer.
G. The Web Service.
H. MSDTC service

Back 240

Answer: E
Only the account under which the SQL Server service account runs is granted NTFS permissions
to the FILESTREAM container.

Front 241

ABC.com is using a SQL Server 2008
instance. You have received instructions to publish an instance of SQL Server Reporting Services
(SSRS) that will keep the old information to the same machine, and as default as possible. What action should you take?
A. Use Internet Information Services to update the SSRS instance.
B. Use Internet Information Services to reinstall the SSRS instance.
C. Use the http.sys listener and update the SSRS instance.
D. Use the http.sys listener and reinstall the SSRS instance.
E. Switch to Mixed mode by updating the SSRS database.
F. Switch to Mixed mode by reinstalling the SSRS database.
G. Switch to Single mode by updating the SSRS database.
H. Switch to Single mode by reinstalling the SSRS database.
I. Switch to SharePoint integrated mode by updating the SSRS database.
J. Switch to SharePoint integrated mode by reinstalling the SSRS database.
K. Switch to Native mode by updating the SSRS database.
L. Switch to Native mode by reinstalling the SSRS database.

Back 241

Answer: I
Explanation:
SharePoint integrated mode is required in this scenario.
To switch modes, you point the server to a native mode report server database. You do not have
to re-install the report server to switch modes.

Front 242

ABC.com is using a SQL Server 2008
instance. You have received instructions from your boss, Miss Tess King, to comply with a new set
of users that will be hired by ABC. The first user is KeithClark. Later a new group of users named
Hirelings will be added as well. They should all be able to:
• view the definitions from all user-defined tables
• read data from all user-defined tables
• read data from all user-defined views
• read data from all user-defined functions
You need to complete this task with the least amount of SQL administrative statements. You start
by created the user KeithClark.
What other steps are required? Select all that apply.
A. DROP ROLE Hirelings;
B. CREATE ROLE Hirelings;
C. GRANT VIEW DEFINITION TO Hirelings;
D. GRANT VIEW ANY DEFINITION TO KeithClark;
E. GRANT SELECT TO Hirelings;
F. EXEC sp_addrolemember Hirelings, 'KeithClark';
G. EXEC sp_addrolemember 'sp_dbdatareader', 'Hirelings';
H. EXEC sp_addrolemember 'db_datareader', 'KeithClark'
I. EXEC sp_addrolemember 'sp_dbddladmin', 'Hirelings';
J. EXEC sp_addrolemember 'Hirelings ', 'KeithClark ';

Back 242

Answer: B,C,E,J
Explanation:
Create a new role.
Give the role the appropriate permissions.
Add KeithClark to the new role.

Front 243

ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. ABC.com has a SQL Server login named
ABC_Tst _Login that maps to a ABC.com learner administrator named ABC_ProdAppUser in
ABC_Prod. You have received instruction from management that you need to restrict
ABC_ProdAppUser so that it only can run stored procedures (current and future). It should not be
allowed to execute other tasks in the database.
What action should you take?
A. You should consider allowing the proper ojbect-level rights to default database.
B. You should consider allowing the proper ojbect -level rights to ABC_Tst_login
C. You should consider allowing the proper ojbect -level rights to ABC_ProdApp_User.
D. You should consider allowing the proper ojbect -level rights to Anonymous User.
E. You should consider allowing the proper database-level rights to default database.
F. You should consider allowing the proper database-level rights to ABC_Tst_login
G. You should consider allowing the proper database-level rights to ABC_ProdApp_User.
H. You should consider allowing the proper database-level rights to Anonymous User.
I. You should consider allowing the proper server-level rights to default database.
J. You should consider allowing the proper server -level rights to ABC_Tst_login
K. You should consider allowing the proper server -level rights to ABC_ProdApp_User.
L. You should consider allowing the proper server -level rights to Anonymous User.
M. You should consider increasing a new role in the default domain group.

Back 243

Answer: G
Explanation:
Right click database
select properties
select permissions
add the user
scroll down to "EXECUTE" then check "GRANT"
This grant execute permissions on all stored procedures.

Front 244

ABC.com is using a SQL Server 2008
database that makes use of the Analysis Services and Reporting Services. However, installed
components at ABC.com adhere to the ABC.com security policy. You have received instruction to
ensure that you adhere to the ABC.com security policy.
What steps should you take? (Each correct option is part of the answer. Choose THREE)
A. Use SQL Server BPA …
B. Use an updated copy of the T-SQL sp-configure stored procedure…
C. Use a newly created policy…
D. Edit the configuration file named..
E. …to analyze your SQL database servers.
F. …to create a script.
G. … RSReportServer.config.
H. … rssrvpolicy.config.
I. ..built on suitable facets.
J. Apply the policies on your SQL Servers.
K. Implement the proposed suggestions.
L. Copy and apply the file to all SQL Servers.
M. Run the script on your SQL Servers.

Back 244

Answer: C,I,J

Front 245

ABC.com is using a SQL Server 2008
instance with failover clustering. You have received instructions from management to encrypt the
connections in the failover cluster.
How can this be accomplished? Select all that apply.
A. By obtaining an appropriate encryption certificate.
B. By obtaining an appropriate encryption license.
C. By installing the encryption certificate on all computers in the domain.
D. By installing the encryption certificate on all servers in the domain.
E. By installing the encryption certificate on all SQL servers in the domain.
F. By installing the encryption certificate on all nodes in the failover cluster.
G. By installing the encryption license in the local group.
H. By installing the encryption license in the no-cluster group.
I. By installing the encryption license in the cluster group.
J. By installing the encryption certificate in a shared folder on the SQL Server.
K. By Installing the encryption certificate on the cluster quorum drive.
(Select two)

Back 245

Answer: A,F
Explanation:
If you deploy SQL Server with a failover cluster, you must install the server certificate with the
FQDN of the virtual server on all nodes in the failover cluster.

Front 246

ABC.com has 12 separate branch offices and
a main office, each office is equipped with their own SQL Server 2008 computer.
A database table named ABC-Sales is used to store point-of-sale transactions. Furthermore, ABCSales
also hosts sales information from all 12 of the ABC.com branch offices to accommodate
customer returns of products. Consequently the data should be refreshed from the main office to
all 12 branch offices every hour, on the hour. To this end you make use of a trigger called
Stats_Trigger to produce sales statistics based on customer sales and buying patterns. Currently
replication between the server in each of the 12 branch offices and a middle server in the main
office occurs in real time. You need to decide which type of replication would be best in this
scenario. You should also eliminate the replication of the data that is produced by the trigger.
What steps should you take? (Each correct answer presents part of the solution. Choose TWO.)
A. You should consider using multiple merge publications, one at every branch office and one in
the main office.
B. You should consider using transactions log between all the servers in the ABC.com domain
C. You should consider using snapshot replication.
D. You should consider using merge replication.
E. You should consider setting the Stats_Trigger trigger on the server in each retail store to use
the NOT FOR REPLICATION option.
F. You should consider setting the Stats_Trigger trigger on the server in each retail store to use
the FOR REPLICATION option.

Back 246

Answer: D,E
Explanation:
In some cases, it is desirable for user activity in a replication topology to be treated differently from
agent activity.
The NOT FOR REPLICATION option allows you to specify that the following database objects are
treated differently when a replication agent performs an operation:
* Foreign key constraints
The foreign key constraint is not enforced when a replication agent performs an insert, update, or
delete operation.
* Check constraints
The check constraint is not enforced when a replication agent performs an insert, update, or delete
operation.
* Identity columns
The identity column value is not incremented when a replication agent performs an insert
operation.
* Triggers
The trigger is not executed when a replication agent performs an insert, update, or delete
operation.

Front 247

ABC.com is using a SQL Server 2008
instance to administrate and maintain their database. The SQL Server 2008 instance contains a
database named ABC_DB that contains a table named ABC_Tbl.
You have to put the output of the entire information from ABC_Tbl to a file. You have received
instructions from management to ensure that your solution will provide the desired output in a
Microsoft Office Open XML document format whilst making sure that the data export process is
protected for reuse.
What utility and what step should you use to accomplish this task? (Each correct option is part of
the answer. Choose TWO)
A. Use the Package Migration Wizard…
B. Use the ISIS…
C. Use the Process Control Tool…
D. Use Process Control Tool…
E. Use the bulk copy program..
F. Use the Microsoft SQL Server Import and Export Wizard…
G. Use the dtexec.exe…
H. Use the DTS Designer…
I. ..with a format file
J. ..with an output file.
K. ..and store the output in a file.

Back 247

Answer: F,K

F. Use the Microsoft SQL Server Import and Export Wizard…
K. ..and store the output in a file.

Front 248

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 instance that contains a database named ABC_Prod. You have received instructions to ensure
that the entire SQL Server samples are set to comply with specific corporate standards. In particular index creation should be forced, naming conventions should be implemented and data fragmentation should be prevented. Which tool and which action should you take? (Each correct option is part of the answer. Choose TWO)
A. Use the Package Migration Wizard.
B. Use the SQL Server Configuration Manager.
C. Use the ISIS.
D. Use the Microsoft SQL Server Management Studio.
E. Use the Process Control Tool.
F. Use the Web Service Tool.
G. Use the bulk copy program.
H. Use the Microsoft SQL Server Import and Export Wizard.
I. Use the dtexec.exe.
J. Use the DTS Designer.
K. Create a configuration package.
L. Create a policy.
M. Create a recovery plan.
N. Create a maintenance plan.

Back 248

Answer: D,L
D. Use the Microsoft SQL Server Management Studio.
L. Create a policy.

Front 249

ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance contains a database named ABC_Prod which make use
of the Fulltext indexes.
One day ABC.com users complain that some searches in the database is not working any longer.
In particular, some searches result an empty resultsets, even though even though the
corresponding key words are indeed present in the database.
What action should you take to resolve this problem?
A. You should consider adding a new full-text index.
B. You should consider changing the full-text index.
C. You should consider repearing the full-text index.
D. You should consider stop and start the MSFTESQL service.
E. You should consider deleting the full-text catalog and rebuild it.
F. Add the appropriate key words to the Fulltext index.

Back 249

Answer: E

E. You should consider deleting the full-text catalog and rebuild it.

Front 250

ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. ABC.com is using Microsoft SQL Server
2005 Analysis Services (SSAS) that is accessed by an Internet Information Services application.
You get complaints from users that say they can’t access the information SQL Server 2008. They
are prompted for authentication.
How should you reconfigure the server so that the users can access the application without being
prompted for a login and a password?
A. You should consider setting the Security\RequireServerAuthentication server configuration to
False.
B. You should consider setting the Security\RequireClientAuthentication server configuration to
False.
C. You should consider setting the Security\RequireClientAuthentication server configuration to
True.
D. You should consider setting the Security\RequireClientAuthentication server configuration to
the Default Value.
E. You should consider setting the Security\RequireServerAuthentication server configuration to
Default value.
F. You should consider setting the Security\RequireServerAuthentication server configuration to
True.

Back 250

Answer: B
Explanation:
By default, Microsoft SQL Server Analysis Services requires clients to be authenticated by the
Microsoft Windows operating system in order to establish a connection with the service. By
default, Microsoft SQL Server Analysis Services will reject all requests from unauthenticated
clients.
To modify the client authentication requirement, the administrator must change the value of the
Security \ RequireClientAuthentication server configuration property.

Front 251

ABC.com is using a SQL Server 2008 server.
Management wanted the backups to be done on a daily basis. Transaction log backups are
performed every afternoon and morning. This worked fine for six months. However, as the content of the log file grew it resulted in a large transaction log file. The size of the transaction log file is
affecting the performance of the server. You have received instructions to take appropriate
measures when doing the backups of the transaction log files.
What is the preferred action in this scenario?
A. Set the database to use the debug model.
B. Decrease the size of transaction log file by using the DBCC SHRINKFILE command
C. The number of transaction log backups should increase. Make a transaction log backup every
hour.
D. Optimize the SQL code by reducing amount the size of the statements.
E. The number of transaction log backups should decrease. Make a single transaction log backup
every day.
F. .Set the database to use the simple recovery model.

Back 251

Answer: C
Explanation:
Increasing the number of transaction log backups decreases the size of the transaction log file.

Front 252

ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance contains a database named ABC_Tester that is used by
the ABC.com’s developers. You need to allow the developers to access the SQL Server Profiler to
get traces which can be used to troubleshoot the application that ABC_Tester uses.
What action can you take to comply with this scenario?
A. You should consider making use of Transaction Logs to grant login access to the developers.
B. You should consider converting the database roles of the developers to a fixed database role.
C. You should consider giving suitable server-level rights to the default SQL Server grant login
access to the developers.
D. You should consider giving suitable server-level rights to the whole SQL Server to grant login
access to the developers.

Back 252

D. You should consider giving suitable server-level rights to the whole SQL Server to grant login
access to the developers.

Front 253

You must configure a SQL Server Agent job to use a proxy account for access to the Windows file
system. The job runs on a SQL Server 2008 Standard Edition server. You have taken the following
steps:
1. Right-click on the Credentials folder and select New Credential.
2. In the New Credential windows enter a Credential name of OSCommand and select a user with
appropriate permissions as the identity and provide a password.
3. Click OK to create the Credential.
When you attempt to create the job step and use the proxy account, you see the Run As option
shown in Exhibit 1. What steps do you need to take to use the proxy account based on the
OSCommand credential?
Select the best answer.
A. Proxies are only supported on Enterprise Edition.
B. You must use the CREATE JOB STEP command since proxies are not available in the GUI
interface.
C. Create a new Operating System (CmdExec) proxy in the Proxies container within the SQL
Server Agent node.
D. The proxy account is configured as the owner of the job and not using the Run As parameter of
a step.

Back 253

C. Create a new Operating System (CmdExec) proxy in the Proxies container within the SQL
Server Agent node.

Front 254

You are the SQL Server administrator in your organization. You've been asked to reconfigure the server named SQL34. You must set the minimum memory to 4096 and the maximum memory to 8192. Optionally, you may configure the backup software so that compression is enabled by default. You execute the following statements:
sp_configure 'backup compression default', 1
go
sp_configure 'max server memory (MB)', 8192
go reconfigure
What have you accomplished? Select the best answer.
A. The minimum and maximum memory settings have been applied.
B. Only the backup compression was enabled because the max server memory parameter was entered incorrectly.
C. None of the objectives were accomplished.
D. The maximum memory is set to 8192 and the backup compression is enabled

Back 254

D. The maximum memory is set to 8192 and the backup compression is enabled.

Front 255

You must restore a database named Sales to
3:17 yesterday afternoon. The current date is Wednesday, June 3, 2009. A full backup is made
every night to a file named day-fback.bak. The transaction log is backed up each day at 12 PM
and is backed up to a file named day-log.bak. Each week, the files are overwritten. Which THREE of the following four commands would accurately restore the database to the desired point-in-time
if run in the proper order?
Choose THREE answers.
A. RESTORE DATABASE Sales WITH RECOVERY;
B. RESTORE LOG Sales FROM DISK='TUESDAY-log.bak' WITH NORECOVERY, STOPAT =
'Jun 2, 2009, 3:17 PM';
C. RESTORE DATABASE Sales FROM DISK='TUESDAY-fback.bak' WITH NORECOVERY;
D. RESTORE LOG Sales FROM DISK='TUESDAY-log.bak' WITH NORECOVERY, UNTIL = 'Jun
2, 2009, 3:17 PM';

Back 255

Answer: A,B,C

Front 256

You manage all the SQL Server 2008
databases in your company. The Prod_details database is the main database accessed by the
company's users. The head office receives data from other branches of the company. After the
data is verified by employees of the audit department, the database is updated with this
information.
Some employees in the audit department complain they cannot update data in certain tables. You
suspect that other database users are holding locks on these tables.
You must identify the oldest transaction in the database and the SQL Server logins associated
with the transaction.
Which statements or functions should you use to obtain the desired results? (Choose two. Each answer represents a part of the solution.)
A. the DBCC OPENTRAN statement
B. the DBCC ROWLOCK statement
C. the USER_NAME function
D. the SUSER_SNAME function
E. the SUSER_SID function
F. the USER_ID function

Back 256

Answer: A,D
A. the DBCC OPENTRAN statement
D. the SUSER_SNAME function

Front 257

James works as an Administrator for Maya Inc. The company has a SQL Server 2008 computer.
James has created an operator with an email and Net Send address. Several alerts have also
been created for which the new operator should be notified. Which of the following stored
procedures should James use to report information about the operators defined for the server?
A. sp_help_operator
B. sp_add_operator
C. sp_update_operator
D. sp_delete_operator

Back 257

Answer: A

Front 258

Inc. The company has a SQL Server 2008 computer
that contains a database named Department. The Department database has a table named
Employees that contains multiple columns. Both clustered and non-clustered indexes are present
in the Employees table. Smith wants to update input rows in the clustered index given in the
Argument column. Which of the following operators will Smith use to accomplish the task with the
least amount of administrative effort?
A. Clustered Index Insert Showplan operator
B. Clustered Index Update operator
C. Clustered Index Delete operator
D. Clustered Index Merge operator

Back 258

Answer: B

Front 259

You work as an Administrator for ABC Inc. The company has a SQL Server 2008 database. A
trigger named trigger_InsertOrders is present in the database, which fires when order data is
inserted into the Orders table. The trigger is responsible for ensuring that a customer exists in the
Customers table before data is inserted into the Orders table. You want to configure the trigger to
prevent it from firing during the data import process. Which of the following Transact-SQL
statements can you use to achieve this goal with the least amount of administrative effort?
Each correct answer represents a complete solution. Choose two.
A. DROP TRIGGER trigger_InsertOrders
B. DISABLE TRIGGER trigger_InsertOrders ON Orders
C. sp_settriggerorder@triggername= 'trigger_InsertOrders', @order='None'
D. ALTER TABLE Orders DISABLE TRIGGER trigger_InsertOrders
E. ALTER TRIGGER trigger_InsertOrders ON Orders NOT FOR REPLICATION

Back 259

Answer: B,D

Front 260

You manage several SQL Server 2008 servers in a large enterprise. The Sales database is
currently housed at the headquarters and is updated by dozens of distribution centers. You want
to implement a solution that will allow copies of the Sales database to be housed at all of the
distribution centers and the headquarters. Each distribution center needs to have information on
other distribution centers, but some latency is acceptable. Data needs to be synchronized on
demand from the subscribers. What type of subscriptions should be used?
A. Transaction
B. Merge
C. Pull
D. Push

Back 260

Answer: C

Front 261

Databases are
stored on the D drive, but it is dangerously close to being full. You have installed a 500 GB drive
as the E drive onto ABC1. Current backups exist for all of the databases. You want to move the
Exams database and transaction log present on the D drive to the E drive. You have detached the
files and copied them to the E drive. However, ABC1 no longer shows the Exams database. You
want to get the database operational as quickly as possible. What should you do?
A. Execute the CREATE DATABASE statement using the FOR ATTACH clause.
B. Restore the database from the backup.
C. Use the sp_reattach system stored procedure.
D. Use the ALTER DATABASE statement to attach the database.

Back 261

Answer: A

Front 262

The company has a SQL Server 2008 computer that contains a database named Sales. Users complain that the execution of queries based on the Sales database takes a long time. Andrew finds that the Sales database contains too much irrelevant information. Andrew wants to shrink the size of the data and log files in the Sales database to decrease the query execution time. Which of the following features of the SQL Server should Andrew use to accomplish the task?
A. The DBCC INDEXDEFRAG statement
B. The DBCC SHRINKFILE operation
C. The DBCC SHRINKDATABASE operation
D. The DBCC DBREINDEX statement

Back 262

Answer: C

Front 263

The server has an operator named DBAs,
which is configured to send an email to a distribution group of all the database administrators in
the IT department. Recently, databases on the server have been developing problems and you
suspect a hardware issue. You want to ensure that if any of the databases on this server receive a
Fatal Error indicating any database integrity suspects, an email will be sent to the DBAs with the
following text: "ABC1 is reporting a fatal error. This requires manual intervention as soon as
possible." What is the easiest way to accomplish this?
A. Create a SQL Server alert.
B. Create a SQL Server trace.
C. Create a SQL Server operator.
D. Create a SQL Server job.

Back 263

Answer: A

Front 264

Which of the following stored procedures is used to grant permission for a database role or user to
use a Database Mail profile?
A. sysmail_add_profile_sp
B. sysmail_add_profileaccount_sp
C. sysmail_add_account_sp
D. sysmail_add_principalprofile_sp

Back 264

Answer: D

Front 265

The Inventory database contains a schema named SKU owned by dbo. Several database objects
are contained in the schema.
You need to transfer ownership of the schema to InvData_owner. You need to do this with minimal
administrative effort and with minimal interference with database operations.
What should you do?
A. Run ALTER AUTHORIZATION
B. Run ALTER SCHEMA.
C. Run DROP SCHEMA and CREATE SCHEMA.
D. Run ALTER USER.

Back 265

Answer: A

Front 266

You manage an instance of SQL Server 2008.
You enable the Resource Governor and install a classifier function.
Users report that they cannot connect to SQL Server.
You need to troubleshoot the problem while Resource Governor is running.
What should you do?
A. Set the database to single user mode.
B. Connect to the instance by using a Dedicated Administrator Connection (DAC).
C. Set the database to restricted user mode.
D. Connect to the instance as a member of the sysadmin fixed server role.

Back 266

Answer: B

Front 267

The server is configured for Windows authentication.
A stored procedure in the AccountDB database calls sp_send_dbmail to send an e-mail. The procedure sends mail successfully when you execute it. Jane attempts to execute the stored procedure and receives the following error:
Permission denied on sp_send_dbmail.
You need to enable Jane to execute the stored procedure.
What should you do?
A. Add Jane to the DatabaseMailUserRole database role in AccountDB.
B. Create a SQLMail profile for Jane.
C. Add Jane to the DatabaseMailUserRole database role in msdb.
D. Create a Database Mail profile for Jane.

Back 267

Answer: C

Front 268

You install a default instance and a named instance of Microsoft SQL Server 2008 on a computer running Windows Server 2008. The computer is a member of an Active Directory domain. You use the Local System account as the SQL Server Agent account on both SQL Server instances. You configure the default instance to listen on the default named pipe and the named instance to listen on an alternate named pipe. You configure a multiserver environment with the default instance as the master server and the named instance as the target server. You create a SQL Server Agent master job to perform automated administrative tasks. The job is able to run on the master server, but not the target server. You review the error logs and determine that SQL Server Agent is not able to connect to the named instance. You need to ensure that the job runs on both the default and named instances. You need to do this with minimal administrator effort. What should you do?
A. Specify a SQL Server alias.
B. Reconfigure SQL Server Agent on both instances to use a domain user account as a service account.
C. Configure the default instance to use the same alternate named pipe as the named instance.
D. Reconfigure SQL Server Agent on both instances to use a local administrator account as a service account.
E. Specify a SQL Server Agent proxy.

Back 268

Answer: A

Front 269

You are the SQL administrator for your company. You manage a SQL Server 2008 instance
named SQL_1. The instance contains two production databases used by your company.
You need to collect performance data on the growth of all the database and log files within both
databases. The data must be collected in the Management Data Warehouse. Your solution needs
to use the same schedule for the collection and upload of the data. Your solution must meet the
requirements while using the least administrative effort.
What should you do?
A. Start the Query Statistics Collection Set.
B. Start the Server Activity Collection Set.
C. Start the Disk Usage Collection Set.
D. Create and start a scheduled, cached custom data collection set.
E. Create and start a scheduled, non-cached custom data collection set.

Back 269

C. Start the Disk Usage Collection Set.

Front 270

ou are in the process of configuring
transaction isolation level to define the locking and row versioning behavior of Transact-SQL
statements. You want to ensure that phantom reads do not occur when you configure the
transaction isolation level.
Which two transaction isolation levels can you configure to accomplish this? (Choose two. Each
correct answer represents a complete solution.)
A. READ UNCOMMITTED
B. READ COMMITTED
C. REPEATABLE READ
D. SNAPSHOT
E. SERIALIZABLE

Back 270

D. SNAPSHOT
E. SERIALIZABLE

Front 271

SQL1 contains a database named Sales. You want to configure database mirroring for the Sales database. You install a new instance of SQL Server 2008 on a server named Server2. You perform a full backup of the Sales database and its transaction logs. What should do to create the mirror database on Server2? (Choose two. Each correct answer presents part of the solution.)
A. Restore the backup of the Sales database with the same name on Server2 by using the WITH NORECOVERY clause.
B. Restore the backup of the Sales database with the same name on Server2 by using the WITH STANDBY clause.
C. Apply the transaction log backup to the Sales database on Server2 by using the WITH NORECOVERY clause.
D. Apply the transaction log backup to the Sales database on Server2 by using the WITH STANDBY clause.

Back 271

Answer: A,C

Front 272

A SQL Server 2008 instance named SQL1 contains
several databases, including a database named Products.
Several users are experiencing problems with the Product_details table of the Products database.
The Product_details table is very large.
You decide to check the integrity of the table using a DBCC CHECKTABLE statement. The
statement you execute must meet the following goals:
· The run time for the statement must be minimized.
· Other users should only be able to read the table's data during the statement's execution.
Which arguments should you use? (Choose all that apply.)
A. REPAIR_FAST
B. TABLOCK
C. PHYSICAL_ONLY
D. ESTIMATEONLY
E. DATA_PURITY

Back 272

Answer: B,C

Front 273

The company
stores all its product-related data in a database named Netfx_data that resides on a SQL Server
2008 server named Server1. A table named Invoice_details exists in the database and contains
the details of all the new invoices generated by the company in the last month. The table was
created by using the following CREATE TABLE statement:
CREATE TABLE Invoice_details (
Invoice_id int NOT NULL,
Customer_id int NOT NULL,
Invoice_date datetime DEFAULT GETDATE () NULL,
Amount_total int NULL,
Serial_num int IDENTITY (1,1) NOT NULL)
A clustered index exists on the Serial_num column, and a composite nonclustered index exists on
the Invoice_id and the Customer_id columns.
An application will be used to execute queries to retrieve all the invoices for a particular customer.
The following query is used by the application to retrieve data:
SELECT Customer_id, Invoice_id, Invoice_date
FROM Invoice_details
WHERE Customer_id = 1234
ORDER BY Invoice_date DESC;
You must ensure optimal performance of the query.
Which action should you perform to achieve this?
A. Create a clustered index on the Customer_id column.
B. Create a nonclustered index on the Customer_id column.
C. Create a nonclustered index on the Invoice_date column.
D. Alter the nonclustered index on the table to include the Invoice_date column.
E. Alter the nonclustered index on the table to remove the Customer_id column

Back 273

D. Alter the nonclustered index on the table to include the Invoice_date column.

Front 274

You are the administrator of a SQL Server 2008, with a report often executed during business time.
There is a stored procedure that is used as the data source for the report.
You get a report from users saying that they receive the data returned by the report but the data is inconsistent.
As the technical support, you check and find that phantom reads cause this problem.
You have to make sure that consistent data is returned by the report while not affecting other users.
Which action should you perform to achieve this goal?
A. You should configure the database for Read Committed Snapshot isolation.
B. You should modify the stored procedure to use the Snapshot isolation level
C. You should configure the database for Auto Update Statistics asynchronously
D. You should modify the stored procedure to use the Repeatable Read isolation level

Back 274

Answer: B

Front 275

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

Back 275

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

When a subscription expires, it must be
reinitialized, because metadata for the subscription is removed

Front 276

You are the administrator of a SQL Server 2008 cluster in a high security environment,
You intend to configure and use encrypted connections for the clustered virtual SQL Server.
You have to install a certificate. The cetification will be used for encryption.
What should you do?
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 276

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 277

You are the administrator SQL 2008 instance with a database named DB1. The company stores clients data in
DB1.
Clients can access their profile data by using a Web application.
You must ensure the security of the customer data, you must ensure that even if the backup media is lost, data
files,
log files and subsequent backups and so on are quite safe.
Which action should you perform to achieve this goal?
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 277

Answer: D

Front 278

You are the administrator of a SQL Server 2008 instance. There is a database developer who is named
UserJack.
UserJack views the definitions of all database objects in a database to read data from all user-defined tables,
views, and table-valued functions. For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions,
but this should be achieved by executing as little Transact-SQL statements as possible.
Which Transact-SQL statements should you execute?
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 278

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

Explanation/Reference:
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 279

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

Back 279

Answer: D

Front 280

You are the administrator of a SQL Server 2008 instance with a database named Finance.
The Finance database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted
daily.
The recovery model of the Finance database is set to Simple.
Business requirements specify that data loss of more than two hours worth of transactions during business
hours is unacceptable.
You need to select a backup strategy that uses the least amount of disk space by adhering to the business
requirements.
What should you do?
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 280

Answer: B

Front 281

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance. The backup strategy for DB1 has the following requirements:
every day at 9:00 full database backup to a file named cash.bak is performed; at 9:15 a transaction log backup
to a file named cash_HHMM.trn is performed every 15 minutes.
DB1 is being used in single-user mode.
Today a user reports that a query deleted some data by accident, the query was executed at 9: 25.
Since the deleted data is quite important, the data has to be restored to its original state. You must achieve this
goal.
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 281

To restore this database to 9:25 AM, you have to :
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 282

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

Answer: D

Front 283

A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be
truncated after you review it.
All information produced by a job must be available for viewing.
Which action should you perform to achieve this goal?
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 283

Answer: C

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 284

You upgraded this instance to SQL Server 2008. You configure the Agent service to use the LocalSystem
account.
There is a file named Orderdata on a remote network share.
A job accesses this Orderdata file by using a CMDExec step. The job step fails to complete execution after the
upgrade.
You have to make sure that the job can complete execution, so you have to configure the job step.
Which action should you perform?
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 284

Answer: C

Because job steps run in the context of a specific Microsoft Windows user, that user must have the permissions
and configuration necessary for the job step to execute.
For example, if you create a job that requires a drive letter or a Universal Naming Convention (UNC) path, the
job steps may run under your Windows user account while testing the tasks.
However, the Windows user for the job step must also have the necessary permissions, drive letter
configurations, or access to the required drive.
Otherwise, the job step fails. To prevent this problem, ensure that the proxy for each job step has the necessary
permissions for the task that the job step performs.

Front 285

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

Back 285

C. ERRORLOG

Front 286

You are the administrator of a SQL Server 2008 instance with a database named DB1.
The DB1 database contains the following stored procedure.
(Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1, "Msg 262, Level 14, State 1,
Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
Which action should you perform?
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 286

Answer: A

Front 287

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 287

Answer: A

Front 288

The Finance database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted
daily.
The recovery model of the Finance database is set to Simple.
Business requirements specify that data loss of more than two hours worth of transactions during business
hours is unacceptable.
You need to select a backup strategy that uses the least amount of disk space by adhering to the business
requirements.
What should you do?
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 288

Answer: B

Front 289

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

Back 289

A. Database snapshots

Front 290

You are the administrator of a SQL Server 2008 with a named Instance01.
Instance01 contains a database which is named Dworks. The bulk-logged recovery mode is used by the
Dworks database.
You logship the Dworks database through a WAN link. A scheduled job rebuilds the indexes of the Dworks
database.
You find that the size of the log backups for log shipping has been enlarged by the job.
You have to reduce the size of the log backup.
Which action should you perform to achieve this goal?
A. You should recreate the indexes.
B. You should compress the log file backups
C. You should make the Dworks database use the Full recovery mode
D. You should make the Dworks database use the Simple recovery mode.

Back 290

Answer: B
Use Compression option for the backup log. This will reduce the size of the database log backups

Front 291

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

Back 291

Answer: C

Front 292

A new SQL Agent job has been created by you. The job includes a Windows PowerShell job step which transfer data between servers by using the SQLCmd utility. If the job fails, you have to make sure that an operator named ReactGroup is informed by receiving an e-mail. What action should you perform to achieve this goal?
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 292

A. First you should enable Notifications. Direct the Notification to the ReactGroup operator on job failure after
you create the ReactGroup operator.

Front 293

There is a management data warehouse that uses the data collector to collect performance data. You intend to
maintain the management data.
You want to implement a data collection process. The process can gather and upload data in the management
data warehouse on different schedules.
Which process should you implement?
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 293

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 294

You notice that the application has a poor performance. On the remote SQL Server instance, you capture a
workload
of the remote instance to a trace table by using SQL Profiler.
You have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server
instance
on a local SQL Server instance.
Which action should you perform to achieve this goal?
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 294

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 295

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

Back 295

Answer: C

Front 296

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

Back 296

Answer: A

Front 297

ABC.com is using SQL Server 2008.
ABC.com contains a SQL Server 2008 server named ABC-DB04. ABC-DB04 will be used to run
the Reporting Services. However, the Reporting Services must be able to run the database
backups and other maintenance job automatically. Because ABC.com has no other SQL systems,
you need to make use of the SQL’s DNS name.
What steps should you take?
A. You should consider using the SQL Server Web Service and the Microsoft Distributed
Transaction Coordinator technology.
B. You should consider using the SQL Server Browser and the SQL Server Web Service
technology.
C. You should consider using the Microsoft Distributed Transaction Coordinator and the SQL
Server Browser technology.
D. You should consider using the Internet Information Services and the Microsoft Distributed
Transaction Coordinator technology.

Back 297

Answer: C

Front 298

ABC.com is using SQL Server 2008
Enterprise Edition. ABC.com contains a database that unfortunately has torn page errors. You
need to solve the problem of the torn page errors.
What steps should you take? (Each correct option is part of the answer. Choose TWO)
A. By using the newest database backup to restore the full database.
B. By using the newest database backup to restore only the torn page.
C. By restoring the database from the latest snapshot of the database.
D. By restoring the latest database backup and then restore the transaction logs which have been
made since the latest backup.
E. By using DBCC CHECKDB.
F. By restoring any transaction logs created since the latest full backup.

Back 298

Answer: B,F

B. By using the newest database backup to restore only the torn page
F. By restoring any transaction logs created since the latest full backup.

Front 299

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. ABC.com has a SQL Agent job that contains a Windows PowerShell job step that uses
the SQLCmd function to move data between servers. The SQL Agent job is working fine for a
couple of week. However, occasionally the job fails.
Your CEO tells you that all employees in ABC must be notified by e-mail if this job is not
successfully run.
What steps should you take? (Each correct option is part of the answer. Choose THREE.)
A. By Enabling the Notifications.
B. By setting the operator as the failsafe operator.
C. By enabling all jobs.
D. By creating the ABCEmployees operator.
E. By Adding a stored procedure to develop the ABCEmployees operator.
F. By Creating Transactions Log and develop the ABCEmployees operator.
G. By directing the Notification to the ABCEmployees operator on job failure.
H. by directing the Notification to the ABCEmployees operator on job success.

Back 299

Answer: A,D,G

Front 300

ABC.com is using a SQL Server 2008 instance. ABC.com contains two SQL Server 2008 computers named ABC-DB1 and ABC-DB2. Both of these computers contain a copy of a database named ABC_DB. You are using transactional replication between ABC-DB1 and ABC-DB2 for ABC_DB. The backups of the databases are as follows:
• Full backup is performed nightly.
• Transaction log backups are performed hourly.
Furthermore, replication delay is less than one minute. ABC_DB becomes corrupt on ABC-DB1 and its state was rendered irreparable. You need to return ABC_DB on ABC-DB1 and ensure that minimal amount of data loss while you copy the data from ABC-DB2 and minimize the impact on the users of these servers.
What steps should you take? (Each correct option is part of the answer. Choose TWO)
A. Detach ABC_DB on ABC-DB2.
B. Make a transactional backup on ABC-DB1.
C. Make a differential backup on ABC-DB1.
D. Make a full backup on ABC-DB1.
E. Make a transactional backup on ABC-DB2.
F. Make a differential backup on ABC-DB2.
G. Make a full backup on ABC-DB2.
H. Restore the latest transactional backups on ABC-DB1.
I. Restore the latest differential backup on ABC-DB1.
J. Restore the latest full backup on ABC-DB1.
K. Copy the ABC_DB from ABC-DB2 to ABC-DB1.
L. Copy the ABC_DB from ABC-DB1 to ABC-DB2.
M. Only one action is required.

Back 300

Answer: G,J
Explanation:
First backup the current database from ABC-DB2 by making a full backup. Restore this backup to
replace the corrupt database on ABC-DB1.

Front 301

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance with failover clustering. You have received instructions from management to encrypt the
connections in the failover cluster.
How can this be accomplished? Select 2 that apply.
A. By obtaining an appropriate encryption certificate.
B. By obtaining an appropriate encryption license.
C. By installing the encryption certificate on all computers in the domain.
D. By installing the encryption certificate on all servers in the domain.
E. By installing the encryption certificate on all SQL servers in the domain.
F. By installing the encryption certificate on all nodes in the failover cluster.
G. By installing the encryption license in the local group.
H. By installing the encryption license in the no-cluster group.
I. By installing the encryption license in the cluster group.
J. By installing the encryption certificate in a shared folder on the SQL Server.
K. By Installing the encryption certificate on the cluster quorum drive.

Back 301

A. By obtaining an appropriate encryption certificate.
F. By installing the encryption certificate on all nodes in the failover cluster

Front 302

You work as a database administrator at ABC.com. ABC.com has 12 separate branch offices and
a main office, each office is equipped with their own SQL Server 2008 computer.
A database table named ABC-Sales is used to store point-of-sale transactions. Furthermore, ABCSales
also hosts sales information from all 12 of the ABC.com branch offices to accommodate
customer returns of products. Consequently the data should be refreshed from the main office to
all 12 branch offices every hour, on the hour. To this end you make use of a trigger called
Stats_Trigger to produce sales statistics based on customer sales and buying patterns. Currently
replication between the server in each of the 12 branch offices and a middle server in the main
office occurs in real time. You need to decide which type of replication would be best in this
scenario. You should also eliminate the replication of the data that is produced by the trigger.
What steps should you take? (Each correct answer presents part of the solution. Choose TWO.)
A. You should consider using multiple merge publications, one at every branch office and one in
the main office.
B. You should consider using transactions log between all the servers in the ABC.com domain C. You should consider using snapshot replication.
D. You should consider using merge replication.
E. You should consider setting the Stats_Trigger trigger on the server in each retail store to use
the NOT FOR REPLICATION option.
F. You should consider setting the Stats_Trigger trigger on the server in each retail store to use
the FOR REPLICATION option.

Back 302

D. You should consider using merge replication.
E. You should consider setting the Stats_Trigger trigger on the server in each retail store to use
the NOT FOR REPLICATION option.

Front 303

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Travel that has a table named ABC_Travel that
keeps data about the entire catalogue of travels. The users at ABC.com search ABC_Tbl on the
Travel_Type column. The Travel_Type column is planned to contain data such as walk, bike, car,
train, bus, ferry, boat, spaceshuttle. However, at the moment the column either contains bus or
NULL.
After some investigation, to your horror, you find out that you have forgotten to build an index on
this table. After examining the hard drive on the server you realize that you cannot afford to use
indexes that require a lot of hard drive space.
A. You should consider developing view that is indexed on the Travel_Type column.
B. You should consider developing a unique index on the Travel_Type column.
C. You should consider developing a default index on the Travel_Type column.
D. Replace the NULL data with the appropriate data such as walk, bike, car, train, bus, ferry, boat,
spaceshuttle.
E. You should consider developing an un-cluttered index on the Travel_Type column.
F. You should consider developing a filtered index on the Travel_Type column.
G. You should consider developing an non-clustered index on the Travel_Type column.
H. You should consider developing a general index on the Travel_Type column

Back 303

Answer: F

Front 304

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 instance. The SQL Server 2008 instance contains a database named ABC_Prod which make use of the Fulltext indexes. One day ABC.com users complain that some searches in the database is not working any longer. In particular, some searches result an empty result sets, even though even though the corresponding key words are indeed present in the database. What action should you take to resolve this problem?
A. You should consider adding a new full-text index.
B. You should consider changing the full-text index.
C. You should consider repearing the full-text index.
D. You should consider stop and start the MSFTESQL service.
E. You should consider deleting the full-text catalog and rebuild it.
F. Add the appropriate key words to the Fulltext index.

Back 304

Answer: E

Front 305

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008 server. Management wanted the backups to be done on a daily basis. Transaction log backups are performed every afternoon and morning. This worked fine for six months. However, as the content of the log file grew it resulted in a large transaction log file. The size of the transaction log file is affecting the performance of the server. You have received instructions to take appropriate measures when doing the backups of the transaction log files. What is the preferred action in this scenario?
A. Set the database to use the debug model.
B. Decrease the size of transaction log file by using the DBCC SHRINKFILE command
C. The number of transaction log backups should increase. Make a transaction log backup every hour.
D. Optimize the SQL code by reducing amount the size of the statements.
E. The number of transaction log backups should decrease. Make a single transaction log backup every day.
F. Set the database to use the simple recovery model.

Back 305

Answer: C
Explanation:
Increasing the number of transaction log backups decreases the size of the transaction log file

Front 306

You are implementing replication. High availability is important. You've chosen to implement
replication with the following options:
· -Immediate Updating Subscribers
· -Publisher
· -Distributor
· -Subscriber
Which one should be changed or removed to allow for high availability?
Select the best answer.
A. Immediate Updating Subscribers
B. Subscriber
C. Distributor
D. Publisher

Back 306

A. Immediate Updating Subscribers

Front 307

You've been asked to run the DTA to locate potential areas for performance improvement in the
Marketing database on SRV3. SRV3 runs SQL Server 2008 Standard Edition and Windows
Server 2008 Standard Edition. You must select the workload for DTA to analyze. Which one of the
following is NOT a valid workload source?
Select the best answer.
A. System Monitor Log
B. A database table with T-SQL command
C. T-SQL script
D. SQL Profiler trace

Back 307

Answer: A

Front 308

You manage all the SQL
Server 2008 instances of the company. For one of your instances, you have created jobs to
perform regular administrative activities.
Some of these jobs in the database fail because the server went down due to a power failure. You
want to analyze these failed jobs. You also want to find out the tasks performed by these failed
jobs.
Which query will you use to achieve the objective?
A. SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 1
B. SELECT job_id, step_id, step_name from msdb.dbo.sysjobactivity
WHERE run_statu-s1 =
C. SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 0
D. SELECT job_id, step_id, step_name from msdb.dbo.sysjobactivity
WHERE run_status-s1 = 0

Back 308

Answer: C

Front 309

You manage all the SQL Server 2008
databases in your company. The Prod_details database is the main database accessed by the
company's users. The head office receives data from other branches of the company. After the
data is verified by employees of the audit department, the database is updated with this
information.
Some employees in the audit department complain they cannot update data in certain tables. You
suspect that other database users are holding locks on these tables.
You must identify the oldest transaction in the database and the SQL Server logins associated
with the transaction.
Which statements or functions should you use to obtain the desired results? (Choose two. Each answer represents a part of the solution.)
A. the DBCC OPENTRAN statement
B. the DBCC ROWLOCK statement
C. the USER_NAME function
D. the SUSER_SNAME function
E. the SUSER_SID function
F. the USER_ID function

Back 309

A. the DBCC OPENTRAN statement
D. the SUSER_SNAME function

Front 310

The company has a SQL Server 2008 computer.
James has created an operator with an email and Net Send address. Several alerts have also
been created for which the new operator should be notified. Which of the following stored
procedures should James use to report information about the operators defined for the server?
A. sp_help_operator
B. sp_add_operator
C. sp_update_operator
D. sp_delete_operator
Answer: A
Microsoft 70-432: Practice Exam
"

Back 310

Answer: A

Front 311

The company has a SQL Server 2008 computer
that contains a database named Department. The Department database has a table named
Employees that contains multiple columns. Both clustered and non-clustered indexes are present
in the Employees table. Smith wants to update input rows in the clustered index given in the
Argument column. Which of the following operators will Smith use to accomplish the task with the
least amount of administrative effort?
A. Clustered Index Insert Showplan operator
B. Clustered Index Update operator
C. Clustered Index Delete operator
D. Clustered Index Merge operator

Back 311

Answer: B

Front 312

The company has a SQL Server 2008 computer
that contains a database named Sales. Users complain that the execution of queries based on the
Sales database takes a long time. Andrew finds that the Sales database contains too much
irrelevant information. Andrew wants to shrink the size of the data and log files in the Sales
database to decrease the query execution time. Which of the following features of the SQL Server
should Andrew use to accomplish the task?
A. The DBCC INDEXDEFRAG statement
B. The DBCC SHRINKFILE operation
C. The DBCC SHRINKDATABASE operation
D. The DBCC DBREINDEX statement

Back 312

C. The DBCC SHRINKDATABASE operation

Front 313

Which of the following stored procedures is used to grant permission for a database role or user to
use a Database Mail profile?
A. sysmail_add_profile_sp
B. sysmail_add_profileaccount_sp
C. sysmail_add_account_sp
D. sysmail_add_principalprofile_sp

Back 313

D. sysmail_add_principalprofile_sp

Front 314

The Inventory database contains a schema named SKU owned by dbo. Several database objects
are contained in the schema.
You need to transfer ownership of the schema to InvData_owner. You need to do this with minimal
administrative effort and with minimal interference with database operations.
What should you do?
A. Run ALTER AUTHORIZATION.
B. Run ALTER SCHEMA.
C. Run DROP SCHEMA and CREATE SCHEMA.
D. Run ALTER USER.

Back 314

A. Run ALTER AUTHORIZATION.

Front 315

You enable the Resource Governor and install a classifier function.
Users report that they cannot connect to SQL Server.
You need to troubleshoot the problem while Resource Governor is running.
What should you do?
A. Set the database to single user mode.
B. Connect to the instance by using a Dedicated Administrator Connection (DAC).
C. Set the database to restricted user mode.
D. Connect to the instance as a member of the sysadmin fixed server role.

Back 315

B. Connect to the instance by using a Dedicated Administrator Connection (DAC).

Front 316

The server is configured for Windows
authentication.
A stored procedure in the AccountDB database calls sp_send_dbmail to send an e-mail. The
procedure sends mail successfully when you execute it. Jane attempts to execute the stored
procedure and receives the following error:
Permission denied on sp_send_dbmail
You need to enable Jane to execute the stored procedure.
What should you do?
A. Add Jane to the DatabaseMailUserRole database role in AccountDB.
B. Create a SQLMail profile for Jane.
C. Add Jane to the DatabaseMailUserRole database role in msdb.
D. Create a Database Mail profile for Jane.

Back 316

Answer: C

Front 317

You install a default instance and a named instance of Microsoft SQL Server 2008 on a computer
running Windows Server 2008. The computer is a member of an Active Directory domain.
You use the Local System account as the SQL Server Agent account on both SQL Server
instances. You configure the default instance to listen on the default named pipe and the named
instance to listen on an alternate named pipe.
You configure a multiserver environment with the default instance as the master server and the
named instance as the target server. You create a SQL Server Agent master job to perform
automated administrative tasks. The job is able to run on the master server, but not the target server. You review the error logs and determine that SQL Server Agent is not able to connect to
the named instance.
You need to ensure that the job runs on both the default and named instances. You need to do
this with minimal administrator effort.
What should you do?
A. Specify a SQL Server alias.
B. Reconfigure SQL Server Agent on both instances to use a domain user account as a service
account.
C. Configure the default instance to use the same alternate named pipe as the named instance.
D. Reconfigure SQL Server Agent on both instances to use a local administrator account as a
service account.
E. Specify a SQL Server Agent proxy.

Back 317

A. Specify a SQL Server alias.

Front 318

You are the SQL administrator for your company. You manage a SQL Server 2008 instance named SQL_1. The instance contains two production databases used by your company. You need to collect performance data on the growth of all the database and log files within both databases. The data must be collected in the Management Data Warehouse. Your solution needs to use the same schedule for the collection and upload of the data. Your solution must meet the requirements while using the least administrative effort. What should you do?
A. Start the Query Statistics Collection Set.
B. Start the Server Activity Collection Set.
C. Start the Disk Usage Collection Set.
D. Create and start a scheduled, cached custom data collection set.
E. Create and start a scheduled, non-cached custom data collection set

Back 318

C. Start the Disk Usage Collection Set.

Front 319

You are the database administrator of your company. You are in the process of configuring
transaction isolation level to define the locking and row versioning behavior of Transact-SQL
statements. You want to ensure that phantom reads do not occur when you configure the
transaction isolation level.
Which two transaction isolation levels can you configure to accomplish this? (Choose two. Each
correct answer represents a complete solution.)
A. READ UNCOMMITTED
B. READ COMMITTED
C. REPEATABLE READ
D. SNAPSHOT
E. SERIALIZABLE

Back 319

D. SNAPSHOT
E. SERIALIZABLE
Answer: D,E

Front 320

You are the database administrator of your company. You configure automatic auditing by using
SQL Server Audit on a server that runs an instance of SQL Server 2008. You want to ensure that
the failed login attempts to the instance are logged in the Windows Security event log.
What should you do to achieve this?
A. Add the SQL Server Agent service account to the Generate security audits policy.
B. Add the SQL Server Writer service to the Generate security audits policy.
C. Add the SQL Server Integration service to the Generate security audits policy.
D. Add the SQL Server service account to the Generate security audits policy.

Back 320

Answer: D Add the SQL Server service account to the Generate security audits policy.

Front 321

SQL1 contains a database named
Sales. You want to configure database mirroring for the Sales database. You install a new
instance of SQL Server 2008 on a server named Server2. You perform a full backup of the Sales
database and its transaction logs.
What should do to create the mirror database on Server2? (Choose two. Each correct answer
presents part of the solution.)
A. Restore the backup of the Sales database with the same name on Server2 by using the WITH
NORECOVERY clause.
B. Restore the backup of the Sales database with the same name on Server2 by using the WITH
STANDBY clause.
C. Apply the transaction log backup to the Sales database on Server2 by using the WITH
NORECOVERY clause.
D. Apply the transaction log backup to the Sales database on Server2 by using the WITH
STANDBY clause.

Back 321

A. Restore the backup of the Sales database with the same name on Server2 by using the WITH
NORECOVERY clause.
C. Apply the transaction log backup to the Sales database on Server2 by using the WITH
NORECOVERY clause.

Front 322

A SQL Server 2008 instance named SQL1 contains several databases, including a database named Products. Several users are experiencing problems with the Product_details table of the Products database. The Product_details table is very large. You decide to check the integrity of the table using a DBCC CHECKTABLE statement. The statement you execute must meet the following goals:
· The run time for the statement must be minimized.
· Other users should only be able to read the table's data during the statement's execution.
Which arguments should you use? (Choose 2)
A. REPAIR_FAST
B. TABLOCK
C. PHYSICAL_ONLY
D. ESTIMATEONLY
E. DATA_PURITY

Back 322

B. TABLOCK
C. PHYSICAL_ONLY

Front 323

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 323

Answer: A

Front 324

The Finance database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted
daily.
The recovery model of the Finance database is set to Simple.
Business requirements specify that data loss of more than two hours worth of transactions during business
hours is unacceptable.
You need to select a backup strategy that uses the least amount of disk space by adhering to the business
requirements.
What should you do?
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 324

Answer: B

Front 325

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

Back 325

A. Database snapshots

Front 326

You are the administrator of a SQL Server 2008 with a named Instance01.
Instance01 contains a database which is named Dworks. The bulk-logged recovery mode is used by the
Dworks database.
You logship the Dworks database through a WAN link. A scheduled job rebuilds the indexes of the Dworks
database.
You find that the size of the log backups for log shipping has been enlarged by the job.
You have to reduce the size of the log backup.
Which action should you perform to achieve this goal?
A. You should recreate the indexes.
B. You should compress the log file backups
C. You should make the Dworks database use the Full recovery mode
D. You should make the Dworks database use the Simple recovery mode.

Back 326

Answer: B
Use Compression option for the backup log. This will reduce the size of the database log backups

Front 327

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

Back 327

Answer: C

Front 328

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 328

Answer: C

Front 329

You configure the SQL Server instance to use the -t1222 and -t1204 trace flags during start. You must make sure that your failure recovery plan performs backup of the use of trace flags.
So what action should you perform to achieve his goal?
A)You should backup the master DB
B)You should backup the default.trc file
C)You should backup the SQL Server registry hive
D)You should back the resource DB

Back 329

SQL Server Config Manager writes startup parameters to the registry. They take effect upon the next startup of the Database Engine. You can override the default startup options temprarily and start an instance of SQL Server by using the following additional start up options.
-T trace#
Indicates that and instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the serve with nonstandard behavior.

Front 330

You configure the SQL Server instance to use the -t1222 and -t1204 trace flags during start. You must make sure that your failure recovery plan performs backup of the use of trace flags.
So what action should you perform to achieve his goal?
A)You should backup the master DB
B)You should backup the default.trc file
C)You should backup the SQL Server registry hive
D)You should back the resource DB

Back 330

SQL Server Config Manager writes startup parameters to the registry. They take effect upon the next startup of the Database Engine. You can override the default startup options temprarily and start an instance of SQL Server by using the following additional start up options.
-T trace#
Indicates that and instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the serve with nonstandard behavior.

Front 331

According to the company requirement, the names of all user-defined stored procedures must contain the prefix us_ on all instances.
Besides this, you make sure that stored procedures that do not contain this prefix cannot be create by you. What should you do?
A)A policy should be created. the policy targets the name of the stored procedure that is evaluated on demand
B)A policy should be created. The policy targets the name of the stored procedure that is evaluated on change
C)A condition should be created. The condition targets the name of the stored procedure hat is evaluated on change
D)A condition should be created. The condition targets the name stored procedure that is evaluated on demand

Back 331

Answer: B

Policy-Based Management is a system for managing

Front 332

According to the company requirement, the names of all user-defined stored procedures must contain the prefix us_ on all instances.
Besides this, you make sure that stored procedures that do not contain this prefix cannot be create by you. What should you do?
A)A policy should be created. the policy targets the name of the stored procedure that is evaluated on demand
B)A policy should be created. The policy targets the name of the stored procedure that is evaluated on change
C)A condition should be created. The condition targets the name of the stored procedure hat is evaluated on change
D)A condition should be created. The condition targets the name stored procedure that is evaluated on demand

Back 332

Answer: B

Policy-Based Management is a system for managing

Front 333

CIO wants you to config FILESTREAM data and the two requirements below must be met:

you must enable FILESTREAM for file I/O streaming access;
remote client computers must be able to have streaming access to FILESTREAM data;

You must make sure that FILESTREAM data is enabled, which service should you configure

a)You should configure SQ Server VSS Writter
b)You should config Distributed Files system
c)You should config SQL Server
d)You should configure SQL Server Full Text

Back 333

Answer C

Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine

Front 334

CIO wants you to config FILESTREAM data and the two requirements below must be met:

you must enable FILESTREAM for file I/O streaming access;
remote client computers must be able to have streaming access to FILESTREAM data;

You must make sure that FILESTREAM data is enabled, which service should you configure

a)You should configure SQ Server VSS Writter
b)You should config Distributed Files system
c)You should config SQL Server
d)You should configure SQL Server Full Text

Back 334

Answer C

Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine

Front 335

User is saying that DB1 meets deadlock problem. As the technical support, you have t capture the deadlock information to SQL Server error log. What should you do?
A)For the AdventureWork DB enable Server Audtiting
B)First you should set the appropriate trace flags as a start up parameter and then restart the SQL Server instance
C)Yu shuld config the data collector and make it capture deadlock graphs
D)You should config a SQL Profiler trace, and make it capture the deadlock graph

Back 335

Answer B
To identify a deadlock, you must first obtain log info. If you suspect a deadlock, you must gather information abut the SPIDs and the resource that are involved in the deadlock. To do this add the -t1204 and the -t3605 startup parameters to SQL Server.
The startup parameter will take effect when SQL Server is stopped and then re-started

T1204 collects info about the process and the resources when the deadlock detection algorithm encounters a deadlock
T3605 writes info from t1204 to SQL Server error log

Front 336

User is saying that DB1 meets deadlock problem. As the technical support, you have t capture the deadlock information to SQL Server error log. What should you do?
A)For the AdventureWork DB enable Server Audtiting
B)First you should set the appropriate trace flags as a start up parameter and then restart the SQL Server instance
C)Yu shuld config the data collector and make it capture deadlock graphs
D)You should config a SQL Profiler trace, and make it capture the deadlock graph

Back 336

Answer B
To identify a deadlock, you must first obtain log info. If you suspect a deadlock, you must gather information abut the SPIDs and the resource that are involved in the deadlock. To do this add the -t1204 and the -t3605 startup parameters to SQL Server.
The startup parameter will take effect when SQL Server is stopped and then re-started

T1204 collects info about the process and the resources when the deadlock detection algorithm encounters a deadlock
T3605 writes info from t1204 to SQL Server error log

Front 337

look at the query
SELECT s.*, i.*
FROM SensitiveTbl AS s
INNER JOIN InsensitiveTbl AS i
ON i.OrganizationName = s.OrganizationName

The following error is returned when you execute the query:
"Msg 468, Level 16, State 9, Line 17 Cannot resolve the collation conflict between 'SQL_Lation1_General_CP1_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation."
The ON clause of the query has to be modified so that it can perform a case-sensitive joins.
A)ON LOWER(i.OrganizationName) = LOWER(s.OrganizationName)
B)ON UPPER(i.OrganizationName) = UPPER(s.OrganizationName)
C)ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CS_AS
D)ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CI_AS

Back 337

Answer C
COLLATE is a clause that can be applied to a DB definition to define the collation, or to a character string to apply a collation cast.
Casting the collation of an expression. You can COLLATE clause to cast a character to a certain collation. Character literal and variables are assigned the default collation of the current db. Column references are assigned the definition collation of the column.

Here one table is Case Sensitive and the other is Case Insensitive. You should use the most "restrictive" collation to make the comparison or you should have the same error

Front 338

look at the query
SELECT s.*, i.*
FROM SensitiveTbl AS s
INNER JOIN InsensitiveTbl AS i
ON i.OrganizationName = s.OrganizationName

The following error is returned when you execute the query:
"Msg 468, Level 16, State 9, Line 17 Cannot resolve the collation conflict between 'SQL_Lation1_General_CP1_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation."
The ON clause of the query has to be modified so that it can perform a case-sensitive joins.
A)ON LOWER(i.OrganizationName) = LOWER(s.OrganizationName)
B)ON UPPER(i.OrganizationName) = UPPER(s.OrganizationName)
C)ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CS_AS
D)ON i.OrganizationName = s.OrganizationName COLLATE SQL_Latin1_General_CP1_CI_AS

Back 338

Answer C
COLLATE is a clause that can be applied to a DB definition to define the collation, or to a character string to apply a collation cast.
Casting the collation of an expression. You can COLLATE clause to cast a character to a certain collation. Character literal and variables are assigned the default collation of the current db. Column references are assigned the definition collation of the column.

Here one table is Case Sensitive and the other is Case Insensitive. You should use the most "restrictive" collation to make the comparison or you should have the same error

Front 339

here is a DB named OrderIn. The OrderIn DB contains a table which is named OrderSend. A column named SendSite which is of the GEOGRAPHY data type has been added. There are no indexes on OrderSend. You have to create an index. What you do first?
A)You must make sure that the SendSite column does not allow NULL values
B)You should define a primary key for the OrderSend table
C)For the OrderSend table, you should create a clustered index
D)First you should copy the OrderSend data to a temporary table then you should truncate the existing OrderSend table

Back 339

Answer B

Spatial indexes can be defined only on a table that has a primary key. The maximum

Primary key metadata cannot be changed while a spatial index is defined on a table

Front 340

here is a DB named OrderIn. The OrderIn DB contains a table which is named OrderSend. A column named SendSite which is of the GEOGRAPHY data type has been added. There are no indexes on OrderSend. You have to create an index. What you do first?
A)You must make sure that the SendSite column does not allow NULL values
B)You should define a primary key for the OrderSend table
C)For the OrderSend table, you should create a clustered index
D)First you should copy the OrderSend data to a temporary table then you should truncate the existing OrderSend table

Back 340

Answer B

Spatial indexes can be defined only on a table that has a primary key. The maximum

Primary key metadata cannot be changed while a spatial index is defined on a table

Front 341

Server has a large DB named DB1. An application which is constantly available uses DB1. You get a report from users complaining that the server has a poor performance. By using DTE you need to improve the performance of the application.
Besides this you must make sure that the action of analyzing the workload will not affect the performance of the production server. What should you do?
A)on the local server enable X_MSVER stored procedure
B)on the remote server enable the X_MSVER stored procedure
C)on the production server use the data.exe utility along with an XML input file
D)You should config a test server that has a similar hardware config. Use the dta.exe utility n the test server along with an XML input file

Back 341

Answer D

dta utility provides a command prompt exec.
dta takes trace files, trace tables, TSQL or XML from DTE

Front 342

Server has a large DB named DB1. An application which is constantly available uses DB1. You get a report from users complaining that the server has a poor performance. By using DTE you need to improve the performance of the application.
Besides this you must make sure that the action of analyzing the workload will not affect the performance of the production server. What should you do?
A)on the local server enable X_MSVER stored procedure
B)on the remote server enable the X_MSVER stored procedure
C)on the production server use the data.exe utility along with an XML input file
D)You should config a test server that has a similar hardware config. Use the dta.exe utility n the test server along with an XML input file

Back 342

Answer D

dta utility provides a command prompt exec.
dta takes trace files, trace tables, TSQL or XML from DTE

Front 343

Today you notice that application that run on the server have poor performance. You doubt that this has something to do with table scans. You have to capture the appropriate info by using Windows System Monitor object. In the options below, which performance object should you use?
A)use SQLServer: Buffer Manager
B)use SQLServer:Memory Manager
C)use SQLServer:Databases
D)use SQLServer:Access Methods

Back 343

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

Full Scans/sec
Number of unrestricted full scans per second. These can be either base-table or full-index scans

Front 344

Today you notice that application that run on the server have poor performance. You doubt that this has something to do with table scans. You have to capture the appropriate info by using Windows System Monitor object. In the options below, which performance object should you use?
A)use SQLServer: Buffer Manager
B)use SQLServer:Memory Manager
C)use SQLServer:Databases
D)use SQLServer:Access Methods

Back 344

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

Full Scans/sec
Number of unrestricted full scans per second. These can be either base-table or full-index scans

Front 345

SSMS executes queries and it has some effect. Now you intend to limit the effect by using the Resource Governor.
You must make sure that queries initiated through SQL Servers NS is less than 20 percent of the CPU utilization, besides this, you must make sure that queries initiated by the mission critical applications can consume 100% of CPU utilization.
So what action should you perform to achieve this goal?
a)alter the default resource pool and set the MAX_CPU_PERCENT option to 80. Then assigned this resource pool to the workload group used by SSMS
b)create a new resource pool and set the MAX_CPU_PERCENT option t 20. Then assign this resource pool to the workload group used by SSMS
c)alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Then assigned this resource pool to the group used by mission critical applications
d)create a new resource pool and set the MAX_CPU_PERCENT option t 20. Then assign this resource pool to the workload group used by mission critical applications

Back 345

Answer B

Front 346

SSMS executes queries and it has some effect. Now you intend to limit the effect by using the Resource Governor.
You must make sure that queries initiated through SQL Servers NS is less than 20 percent of the CPU utilization, besides this, you must make sure that queries initiated by the mission critical applications can consume 100% of CPU utilization.
So what action should you perform to achieve this goal?
a)alter the default resource pool and set the MAX_CPU_PERCENT option to 80. Then assigned this resource pool to the workload group used by SSMS
b)create a new resource pool and set the MAX_CPU_PERCENT option t 20. Then assign this resource pool to the workload group used by SSMS
c)alter the default resource pool and set the MAX_CPU_PERCENT option to 20. Then assigned this resource pool to the group used by mission critical applications
d)create a new resource pool and set the MAX_CPU_PERCENT option t 20. Then assign this resource pool to the workload group used by mission critical applications

Back 346

Answer B

Front 347

You are managing a SQL Server 2008 instance which includes a database called Products for
Company.com. There is 100 GB information in Products database.2 GB of information is
changed everyday on an average. You have set the recovery model of the Products database
Simple. Business requirements clarify that data loss of more than three hours worth of
transactions during business hours could not run normally. You should make sure that choosing a
backup strategy will utilize the least number of disk spaces through adhering to the business requirements. Which is the correct answer?
A. You should run a full database backup once everyday
B. You should run a full database backup once everyday and then run a differential backup
every three hours during business hours.
C. You should run a differential backup each three hours during business hours.
D. You should run a transaction log backup each three hours during business hours.

Back 347

B. You should run a full database backup once everyday and then run a differential backup
every three hours during business hours.

Front 348

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

Back 348

A. You should utilize the SELECT subquery in the INSERT statement to move the deleted rows
from this morning??s database snapshot to the CurrentSpeaker table.

Front 349

You are managing of SQL Server 2008 sample called Test which includes a database called
TestSupport for the Company.com. The ProductItems table is included in TestSupport.You
decide to develop a Replication topology to copy the ProductItems table to the SQL Server
samples included in computers. The ProductItems will be refreshed from TestSupport when the
computers do not link to company network. The Products table on the TestSupport sample is
from between reconnections frequently. In order to run the Replication topology along with the
Subscription type by ensuring that bandwidth usage is minimized.Which is the correct answer?
A. You should run the Merge Replication topology together with a Pull Subscription.
B. You should run the Merge Replication topology together with a Push Subscription.
C. You should run the Transactional Replication topology together with a Push Subscription.
D. You should run the default Replication topology together with a Push Subscription.

Back 349

A. You should run the Merge Replication topology together with a Pull Subscription.

Front 350

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

Back 350

A. You should begin the SQL Server Browser service. Set the service to run automatically.

Front 351

System Data Collection Sets
(def + list)

Back 351

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 352

Disk Usage Collection Set
(def)
part of System Data Collection Sets

Back 352

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.

Collection mode:Non-cached

Front 353

Server Activity Collection Set
(def)
part of System Data Collection Sets

Back 353

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.
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.

Collection mode:Cached (continuous)

Front 354

Query Statistics Collection Set
(def)
part of System Data Collection Sets

Back 354

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.

Collection mode:Cached (continuous)

Front 355

Data Collection Terminology
target
target type
data provider

Back 355

target
An instance of the Database Engine in an edition of SQL Server that supports Data Collection.
target type
The type of target, which has certain characteristics and behavior. For example, a SQL Server instance target has different characteristics than a SQL Server database target.
data provider
A known data source, specific to a target type, that provides data to a collector type.

Front 356

Data Collection Terminology
collector type
collection item
collection set

Back 356

collector type
A logical wrapper around the SSIS packages that provide the actual mechanism for collecting data and uploading it to the management data warehouse.
collection item
An instance of a collector type. A collection item is created with a specific set of input properties and a collection frequency.
collection set
A group of collection items. A collection set is a unit of data collection that a user can interact with through the user interface.

Front 357

Data Collection Terminology
collection mode
management data warehouse

Back 357

collection mode
The manner in which the data is collected and stored. Collection mode can be cached or non-cached. Cached mode supports continuous collection, whereas non-cached mode is intended for on-demand collection or a collection snapshot.
management data warehouse
A relational database used to store collected data.

Front 358

Data Collection Terminology
Data Collector Run-Time Component

A collection set is run in one of the following collection and upload modes

Back 358

The data collector run-time component is hosted by a stand-alone process called Dcexec.exe. This component manages data collection based on the definitions provided in a collection set, and can accept any collection set as input. The data collector run-time component is responsible for loading and executing the SSIS packages that are part of a collection set.

A collection set is run in one of the following collection and upload modes:

Non-cached mode. Data collection and upload are on the same schedule. The packages start, collect and upload data at their configured frequency, and run until they are finished. After the packages finish, they are unloaded from memory.


Cached mode. Data collection and upload are on different schedules. The packages collect and cache data until they receive a signal to exit from a loop control-flow task. This ensures that the data flow can be executed repeatedly, which enables continuous data collection

Front 359

DBCC CHECKDB
NOINDEX option
REPAIR_ALLOW_DATA_LOSS option
REPAIR_FAST option

Back 359

NOINDEX
Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.
REPAIR_ALLOW_DATA_LOSS
Tries to repair all reported errors. These repairs can cause some data loss.
REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.

Front 360

DBCC CHECKDB
REPAIR_REBUILD option
ALL_ERRORMSGS option

Back 360

REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
REPAIR_REBUILD does not repair errors involving FILESTREAM data.

ALL_ERRORMSGS
Displays all reported errors per object. All error messages are displayed by default. Specifying or omitting this option has no effect. Error messages are sorted by object ID, except for those messages generated from tempdb database.

Front 361

DBCC CHECKDB
EXTENDED_LOGICAL_CHECKS option
TABLOCK option

Back 361

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.
TABLOCK
Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.
TABLOCK limits the checks that are performed; DBCC CHECKCATALOG is not run on the database, and Service Broker data is not validated.

Front 362

DBCC CHECKDB
ESTIMATEONLY option
PHYSICAL_ONLY option

Back 362

ESTIMATEONLY
Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. The actual database check is not performed.

PHYSICAL_ONLY
Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.

Front 363

CL needs to implement a replication architecture that maintains a reporting server while also ensuring that any changes to the report server do not get sent back to the publisher. Which replication option would be the best solution?
A)Transactional replication
B)Snapshot replication
C)Merge replication
D)Peer-to-peer replication

Back 363

a

Front 364

CL needs to implement a replication architecture that maintains a reporting server. A small number of changes might be made on the report server when necessary corrections to reports need to be sent back to the publisher. The reporting solutions needs to be always available, even if the publisher is off-line, while also providing the best performance. Which replication options is the best solution?
A)Transactional replication
B)Transactional replication with immediate updating subscribers
C)Merge replication
D)Transactiona replication with queued updating subscriber

Back 364

d correct
Transactional replication with queued updating subscribers allows changes to be made at the subscriber even if the publisher is not available whole also providing the best performance of all the options
-----
A)transactional replication does not allow changes to be sent from the subscriber back to the publisher
B)If the publisher is offline, the subscriber cannot process any changes if configured with immediate updating subscriber, so this solution does not meet the requirements
C)Merge replication could be user to meet all the requirements, but it does not provide better performance than transactional replication with queued updating subscriber