Deleteme_Ipad_Sort-List2

Front 1

Permissions prior to SQL Server 2005

Back 1

All permissions are granted directly to objects within a DB.

Front 2

Permission in SQL 2005 and later

Back 2

Define multiscope to which you can assign permissions. Because you grant permissions on a securable you can assign permissions to a securable at any scope.

Front 3

Cmd that allows users to view metadata in a DB

Back 3

GRANT VIEW DEFINITION to <user>

Front 4

Ownership Chains

Back 4

Each object within a DB as an owner associated to it. You can also build objects that reference other objects within a DB. The owner of each object that is referenced in a calling stack forms an ownership chain as code transits from on object to the next within the calling stack. So long as the owner of the object and any other objects that it references have the same owner you have an intact ownership chain.

Front 5

First Sql server specific virus

Back 5

slammer Trojan

Front 6

Biggest potential threat to an instance

Back 6

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 7

Features that should have been disabled unless specifically needed

Back 7

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 8

Use of OLE Automation procedure

Back 8

Exist within SQL Server to provide some basic interoperability with previous versions.

Front 9

CLR uses

Back 9

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

Front 10

OLE

Back 10

Object linking and embedding.

Front 11

OPENROWSET and OPENDATASURCE

Back 11

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

Front 12

CDOC

Back 12

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 13

SQL Server and Surface

"Configuration Manager"

Back 13

Exists only in 2005. The functionality of it is provided by SQL Server Config Manager.

Front 14

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

Back 14

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 15

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

Back 15

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

Front 16

Which logins cannot be used to authenticate to an instance

Back 16

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

Front 17

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

Back 17

Loginless users are the replacement for an application role.

Front 18

Impersonations

Back 18

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 19

cmd to accomplish impersonation

Back 19

{ EXEC / EXECUTE } AS <context_specification>

[;]

<context_specification>::=

{ LOGIN / USER } = 'name'
[ WITH { NO REVERT / COOKIE INTO @varbinary_variable } ]
/ CALLER

Front 20

master key

Back 20

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

Front 21

How many SMK?

How many DMK?

Back 21

How many DMK?

SMK - single service master key for the entire instance

DMK - database master key within each DB

Front 22

Service master key

Back 22

Generated auto first time the instance is started.

Symmetric generated from the local machine key and encrypted using SQL Server Service account.

Front 23

Database master key must be generated explicitly using what cmd

Back 23

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <Pass>

Front 24

DMK usage

Back 24

To protect ant certificates, symmetric keys, or asymmetric key stored in a DB.

Encrypted using Triple DES

Front 25

Certificates

Back 25

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 26

Public Certs

Back 26

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

Front 27

Priv Certs

Back 27

Generated by and used to protect data within an organization.

Front 28

Signatures

Back 28

Allow you to 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 29

The process of signing code digitally

Back 29

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 30

You are deploying a new server within Wide World Importers that will be running a SQ: server 2008 instance in support of a new application. Because of the feature support that is needed you will be installing SQL Server 2008 Enterprise. Which operating system will support your installation? (Choose all that apply)

a)Win 200 Server Enterprise SP4 or up

b)Win 2003 Server Enterprise

c)Win 2003 Server Enterprise SP2

d)Win Serve 2008 Enterprise

Back 30

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 31

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 31

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 Express is supported on Win XP Tablet Edition SP2, but it is not the optimal operating system to use

Front 32

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 32

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

Front 33

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

Back 33

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 34

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 34

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 35

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 35

Correct - D

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

Front 36

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

Back 36

Only SQL Server enterprise supports multiple instances of the same machine.

Front 37

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

Back 37

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

Front 38

Wide world importers will be using the new FILESTREAM 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 38

Correct - d

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

Front 39

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 39

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 40

As part of the implementation of the new Web based booking system at Margie'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 Services

B) Database Mail

C) Microsoft Visual Studio. NET code libraries

C) Activity Monitor

Back 40

Correct - B, A

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 41

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?(Select 2)

Back 41

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 42

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

Back 42

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 43

What is the purpose of the transaction log?

Back 43

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

Front 44

You have a reference database name OrderHistory, which should not allow any data to be modified. How can you ensure, with the least amount 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 44

C) Set the database to READ_ONLY

Front 45

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 45

You would execute the following cmd:

ALTER DATABASE <database name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

Front 46

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

Back 46

You can create full, differential, 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 47

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 47

D) Correct

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

Front 48

Which option should be enabled for all production databases?

Back 48

You should set the PAGE_VERIFY CHECKSUM option for all production databases.

Front 49

What checks does DBCC CHECKDB perform?

Back 49

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 50

Which cmd are executed when you run the DBCC CHECKDB command?

A) DBCC CHECKTABLE

B) DBCC CHECKIDENT

C) DBCC CHECKCATALOG

D) DBCC FREEPROCCACHE

Back 50

A and C

DBCC CHECKDB executes DBCC CHECKTABLE

DBCC CHECKALLOC

DBCC CHECKCATALOG

Front 51

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

Back 51

You can designate columns as SPARSE to optimize the storage of NULLs. You can apply the FILESTREAM property 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 52

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 52

A and C

ROWGUIDCOL

IDENTITY

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 53

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

Back 53

A primary key does not allow NULLs.

Front 54

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

Back 54

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 55

Columns with which properties cannot be sparse columns?(2)

A) FILESTREAM

B) NULL

C) NOT FOR REPLICATION

D) NULL

Back 55

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 56

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

Back 56

SQL Server uses a B-tree structure for indexes.

Front 57

What are the three type of pages within an index?

Back 57

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 58

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 58

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 determining 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 59

What if two indexes have at least one column in common?

Back 59

SQL Server can join the two indexes to satisfy a query.

Front 60

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

Back 60

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

Front 61

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

Back 61

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

Front 62

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 of 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 62

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 63

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

Back 63

REBUILD de-fragments all levels of an index

REORGANIZE de-fragments only the leaf level of an index

Front 64

What happens when an index is disabled?

Back 64

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

Front 65

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 65

Correct C) By disabling the indexes prior to the load, you avoid all the overhead required to maintain indexes. 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 66

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

Back 66

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

Front 67

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

Back 67

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 68

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 68

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 69

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

Back 69

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 70

Which predicate performs fuzzy searching by default?

Back 70

FREETEXT and FREETEXTTABLE predicates perform wildcard searches by default

Front 71

Which predicate is used to perform proximity and synonym searches?

Back 71

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

Front 72

You want to search for two terms based on proximity within a row. Which full text predicates can be used to perform proximity searches?(2)

A) CONTAINS

B) FREETEXT

C) CONTAINSTABLE

D) FREETEXT

Back 72

A and C

Front 73

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 73

D

Front 74

What data types cannot be used with partition functions?

Back 74

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

Front 75

What is the max number of partitions allowed for a table?

Back 75

1000

Front 76

What is the max number of boundary points allowed per table?

Back 76

999

Front 77

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

Back 77

You can specify exactly one filegroup when using the ALL keyword.

Front 78

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

Back 78

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

Front 79

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

Back 79

A computed column must be PERSISTED.

Front 80

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

Back 80

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

Front 81

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

Back 81

SPLIT add new boundary points

MERGE removes boundary point

Front 82

Which operator is used to remove a boundary point?

Back 82

The SWITCH operator is used to move partitions between tables

Front 83

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

Back 83

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

Front 84

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

Back 84

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

Front 85

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

Back 85

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

Front 86

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

Back 86

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

Front 87

You want to import data into the Orders table. The table has triggers and check constrains that you want to be 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 87

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 don't need ALTER TABLE permissions.

Front 88

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 OPENROWSET function

Back 88

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

Front 89

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 89

Answer: A

Front 90

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 90

Answer: F

Front 91

How do you detect and log corrupt pages?

Back 91

Execute

ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM

Front 92

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 92

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 full backup 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 93

Which two features are not compatible with DB snapshots?

Back 93

FILESTREAM

full text indexes

Front 94

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

Back 94

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

Front 95

What are the two parts of an endpoint?

Back 95

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 96

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

Back 96

STARTED - listens for and allows connections

STOPPED - listens for connections and returns error messages

DISABLED - does not respond to any request

Front 97

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

Back 97

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

Front 98

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

Back 98

NTML

Kerberos

NEGOTIATE

Front 99

What are the two universal arguments for TCP endpoints?

Back 99

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

Front 100

msde

cdoc

Back 100

Microsoft database engine

cross database ownership changing

Front 101

OPENROWSET and OPENDATASOURCE

Back 101

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

Front 102

SQL Server and Surface

"Configuration Manager

Back 102

Exist only in 2005. The functionality of it is provided by SQL Server Config Manager.

Front 103

cmd to accomplish impersonation

Back 103

{ EXEC / EXECUTE } AS <context_specification>

[;]

<context_specification>::=

{ LOGIN / USER } = 'name'
[ WITH { NO REVERT / COOKIE INTO @varbinary_variable } ]
/ CALLER

Front 104

How many SMK?

How many DMK?

Back 104

SMK - single service master key for the entire instance

DMK - database master key within each DB

Front 105

Certificates

Back 105

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 106

What are the minimum hardware requirements for SQL Server 2008 32 bit?

Back 106

Processor: P3 or higher
Processor Speed: 1 GHz or more
Memory: 512 MB

Front 107

What are the minimum hardware requirements for SQL Server 2008 64 bit?

Back 107

Processor: Itanium, Opteron, Athlon or Xeon/Pentium with EM64t support
Processor Speed: 1.6 GHz or more
Memory: 512 MB

Front 108

The operating systems supported for all editions of SQL Server are?

Back 108

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

Front 109

The operating systems supported for SQL Developer, Evaluation and Express are?

Back 109

Windows XP Professional SP2 or higher
Windows Vista Home Basic or higher

Front 110

SQL Server Express is supported on?

Back 110

Windows XP Home Edition SP2 or higher
Windows XP Home Reduced Media Edition
Windows XP Tablet Edition SP2 or higher
Windows XP Media Center 2002 SP2 or higher
Windows XP Professional Reduced Media Edition
Windows XP Professional Embedded Edition Feature Pack 2007 SP2
Windows XP Professional Embedded Edition for Point of Service SP2
Windows Server 2003 Small Business Server Standard Edition R2 or higher

Front 111

What version of Server 2008 is SQL 2008 not supported on and why?

Back 111

SQL Server 2008 is not supported on Windows Server 2008 Server Core. Windows Server.

2008 Server Core is not supported because the .NET Framework is not supported on Server.

Core. SQL Server 2008 relies on .NET Framework capabilities to support FILESTREAM.

SPATIAL, and DATE data types, along with several additional features.

Front 112

What editions is SQL Server 2008 available in?

Back 112

Enterprise
Standard
Workgroup
Express
Compact
Developer
Evaluation

Front 113

How do the developer and evaluation editions differ from the enterprise edition?

Back 113

They contain the same functionality, but cannot be run in a production environment.

Front 114

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

Back 114

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

Front 115

How many CPUs are supported by the Standard, Workgroup, Express and Compact editions?

Back 115

Standard: 4
Workgroup: 4
Express: 1
Compact: 1

Front 116

What database size is supported by the Standard, Workgroup, Express and Compact editions?

Back 116

Standard: Unlimited
Workgroup: Unlimited
Express: 4 GB
Compact: 4 GB

Front 117

What RAM size is supported by the Standard, Workgroup, Express and Compact editions?

Back 117

Standard: Unlimited
Workgroup: Unlimited
Express: 1 GB
Compact: 1 GB

Front 118

Which versions among Standard, Workgroup, Express and Compact allow the use of SQL Server Management Studio?

Back 118

Standard: Yes
Workgroup: Yes
Express: Separate Download
Compact: No

Front 119

Which versions among Standard, Workgroup, Express and Compact allow for full text search?

Back 119

Standard: Yes
Workgroup: Yes
Express: Advanced services only
Compact: No

Front 120

Which versions among Standard, Workgroup, Express and Compact allow for partitioning?

Back 120

Standard: No
Workgroup: No
Express: No
Compact: No

Front 121

Which versions among Standard, Workgroup, Express and Compact allow for parallel operations?

Back 121

Standard: No
Workgroup: No
Express: No
Compact: No

Front 122

Which versions among Standard, Workgroup, Express and Compact allow for multiple instances?

Back 122

Standard: No
Workgroup: No
Express: No
Compact: No

Front 123

Which versions among Standard, Workgroup, Express and Compact allow for database snapshots?

Back 123

Standard: No
Workgroup: No
Express: No
Compact: No

Front 124

Which versions among Standard, Workgroup, Express and Compact allow for scalable shared databases?

Back 124

Standard: No
Workgroup: No
Express: No
Compact: No

Front 125

Which versions among Standard, Workgroup, Express and Compact allow for index views?

Back 125

Standard: No
Workgroup: No
Express: No
Compact: No

Front 126

Which versions among Standard, Workgroup, Express and Compact allow for log compression?

Back 126

Standard: No
Workgroup: No
Express: No
Compact: No

Front 127

Which versions among Standard, Workgroup, Express and Compact allow for clustering?

Back 127

Standard: 2 nodes
Workgroup: No
Express: No
Compact: No

Front 128

Which versions among Standard, Workgroup, Express and Compact allow for database mirroring?

Back 128

Standard: single-thread
Workgroup: No
Express: No
Compact: No

Front 129

Which versions among Standard, Workgroup, Express and Compact allow for online operations?

Back 129

Standard: No
Workgroup: No
Express: No
Compact: No

Front 130

Which versions among Standard, Workgroup, Express and Compact allow for resource governor?

Back 130

Standard: No
Workgroup: No
Express: No
Compact: No

Front 131

Which versions among Standard, Workgroup, Express and Compact allow for backup compression?

Back 131

Standard: No
Workgroup: No
Express: No
Compact: No

Front 132

Which versions among Standard, Workgroup, Express and Compact allow for hot add memory/CPU?

Back 132

Standard: No
Workgroup: No
Express: No
Compact: No

Front 133

Which versions among Standard, Workgroup, Express and Compact allow for Data Encryption?

Back 133

Standard: Limited
Workgroup: Limited
Express: Limited
Compact: Password-based only

Front 134

Which versions among Standard, Workgroup, Express and Compact allow for Change Data Capture?

Back 134

Standard: No
Workgroup: No
Express: No
Compact: No

Front 135

Which versions among Standard, Workgroup, Express and Compact allow for Data Compression?

Back 135

Standard: No
Workgroup: No
Express: No
Compact: No

Front 136

Which versions among Standard, Workgroup, Express and Compact allow for Policy-based management?

Back 136

Standard: Yes
Workgroup: No
Express: No
Compact: No

Front 137

Which versions among Standard, Workgroup, Express and Compact allow for Performance Data Collection?

Back 137

Standard: Yes
Workgroup: No
Express: No
Compact: No

Front 138

Which versions among Standard, Workgroup, Express and Compact allow for CLR?

Back 138

Standard: Yes

Front 139

Which versions among Standard, Workgroup, Express and Compact allow for XML?

Back 139

Standard: Native
Workgroup: Native
Express: Native
Compact: Stored as text

Front 140

Which versions among Standard, Workgroup, Express and Compact allow for Spatial Data?

Back 140

Standard: Yes

Front 141

Which versions among Standard, Workgroup, Express and Compact allow for Stored procedures, triggers and views?

Back 141

Standard: Yes
Workgroup: Yes
Express: Yes
Compact: No

Front 142

Which versions among Standard, Workgroup, Express and Compact allow for Merge Replication?

Back 142

Standard: Yes
Workgroup: Yes
Express: Subscriber only
Compact: Subscriber only

Front 143

Which versions among Standard, Workgroup, Express and Compact allow for Transactional Replication?

Back 143

Standard: Yes
Workgroup: Subscriber only
Express: Subscriber only
Compact: No

Front 144

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact have an Import/Export Wizard?

Back 144

Standard: Yes
Workgroup: Yes
Express: NA
Compact: NA

Front 145

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact have a Package Designer?

Back 145

Standard: Yes
Workgroup: Yes
Express: NA
Compact: NA

Front 146

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support Data Mining?

Back 146

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 147

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support Fuzzy grouping/lookup?

Back 147

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 148

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support term extraction/lookup?

Back 148

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 149

Regarding SSIS ,which versions among Standard, Workgroup, Express and Compact support OLAP processing?

Back 149

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 150

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support Microsoft Office Integration?

Back 150

Standard: Yes
Workgroup: Yes
Express: Advanced Services Only
Compact: NA

Front 151

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support report builder?

Back 151

Standard: Yes
Workgroup: Yes
Express: Advanced Services Only
Compact: NA

Front 152

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support Scale-out reporting?

Back 152

Standard: No
Workgroup: No
Express: No
Compact: NA

Front 153

Regarding SSRS ,which versions among Standard, Workgroup, Express and Compact support Data-driven subscriptions?

Back 153

Standard: No
Workgroup: No
Express: No
Compact: NA

Front 154

Regarding SSAS-OLAP support ,which versions among Standard, Workgroup, Express and Compact have support Linked measures/dimensions?

Back 154

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 155

Regarding SSAS-OLAP support ,which versions among Standard, Workgroup, Express and Compact have support Perspectives?

Back 155

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 156

Regarding SSAS-OLAP support, which versions among Standard, Workgroup, Express and Compact have support for Partitioned cubes?

Back 156

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 157

Regarding SSAS-Data Mining support, which versions among Standard, Workgroup, Express and Compact have support for Time series?

Back 157

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 158

Regarding SSAS-Data Mining support, which versions among Standard, Workgroup, Express and Compact have support for Parallel processing and prediction?

Back 158

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 159

Regarding SSAS-Data Mining support, which versions among Standard, Workgroup, Express and Compact have support for Advanced mining algorithms?

Back 159

Standard: No
Workgroup: No
Express: NA
Compact: NA

Front 160

In addition to the core database engine technologies, SQL Server 2008 Enterprise supports Service Broker for asynchronous processing.

Back 160

summary

Front 161

Which core components of SQL, which run as services, require dedicated service accounts?

Back 161

Database Engine and SQL Server Agent.

Front 162

What is a collation sequence?

Back 162

A collation sequence defines the character set that is supported, including case sensitivity, accent sensitivity, and kana sensitivity.

Front 163

What are the two authentication modes that you can set for SQL Server authentication?

Back 163

Windows Only (integrated security) or Windows and SQL Server (mixed mode).

Front 164

Why can you not use the Windows Service Control Applet to change service accounts or service account passwords?

Back 164

SQL Server Configuration Manager needs to be used, because it includes the code to regenerate the service master key that is critical to the operation of SQL Server services.

Front 165

What is an enumeration request and when would you disable responses?

Back 165

By disabling enumeration responses by setting the Hide Instance to Yes, you prevent someone from using discovery techniques to locate servers running SQL Server for a possible attack.

Front 166

What is database mail?

Back 166

Database mail enables a computer running SQL to send outbound mail messages. It uses SMTP rather than MAPI.

Front 167

What are the two types of profiles that you can configure within Database Mail?

Back 167

Either a public or private profile. A public profile can be accessed by any user with the ability to send mail. A private profile can be accessed only by those users who have been granted access to the mail profile explicitly.

Front 168

How is the SQL Server Agent related to Database Mail?

Back 168

Database Mail utilizes the services of SQL Server Agent to send messages as a background process. If SQL Server Agent is not running, messages will accumulate in a queue within the Msdb database.

Front 169

What are the two file types that SQL uses?

Back 169

Data and Transaction log files. Data files are responsible for the long-term storage of all the data within a database. Transaction log files are responsible for storing all the transactions that are executed against a database.

Front 170

When you create a database, it should have three files and two filegroups; what are they?

Back 170

You should have a file with an .mdf extension within a filegroup named PRIMARY, a file with an .ndf extension in a filegroup with any name that you choose, and the transaction log with an .ldf extension.

Front 171

After you create a database, why should you mark the second database as the default filegroup?

Back 171

By changing the default filegroup, you ensure that any objects you create are not accidentally placed on the primary filegroup and that only the system objects for the database reside on the primary filegroup--reduces the possibility of introducing corruption.

Front 172

What is the command to change the default filegroup?

Back 172

ALTER DATABASE <database name> MODIFY FILEGROUP <filegroup name> DEFAULT

Front 173

What determines the maximum transaction throughput for any database?

Back 173

The amount of data per second that SQL Server can write to the transaction log.

Front 174

What is the FILESTREAM feature?

Back 174

FILESTREAM feature allows you to associate files with a database. The files are stored in a folder on the operating system, but are linked directly into a database where the files can be backed up, restored, full-text-indexed, and combined with other structured data.

Front 175

What is the filestream.hdr file?

Back 175

It is a system file that is created to manage the files subsequently written to the filestream folder.

Front 176

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

Back 176

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

Front 177

In addition to temporary objects, what does SQL Server use the tempdb for?

Back 177

Worktables used in grouping/sorting operations, worktables to support cursors, the version store supporting snapshot isolation level, and overflow for table variables. You can also cause index build operations to use space in tempdb.

Front 178

Every database in SQL server has a recovery model property; which recovery models are available?

Back 178

Full

Bulk-logged

Simple

Front 179

In the full recovery model, what changes are logged to the transaction logs and how long are the changes retained in the transaction logs?

Back 179

All changes made, using both data manipulation language (DML) and data definition language (DDL), are logged to the transaction log. Changes are retained in the transaction log indefinitely and are removed only by executing a transaction log backup.

Front 180

What are the operations that are performed in a minimally logged manner with the database set in the Bulk-logged recovery model?

Back 180

BCP

BULK INSERT

SELECT. . .INTO

CREATE INDEX

ALTER INDEX. . .REBUILD

Front 181

How does the simple recovery model differ from the full recovery model?

Back 181

A database in the Simple recovery model logs operations to the transaction log exactly as the Full recovery model does. However, each time the database checkpoint process executes, the committed portion of the transaction log is discarded. A database in the Simple recovery model cannot be recovered to a point in time because it is not possible to issue a transaction log backup for a database in the simple recovery model.

Front 182

What is the command to set the recovery model of a database?

Back 182

ALTER DATABASE database_name SET RECOVERY {FULL / BULK_LOGGED / SIMPLE }

Front 183

What types of backup (Full, Differential, Trans log) are available for a database in the Full recovery model?

Back 183

Full: Yes
Differential: Yes
Transaction Log: Yes

Front 184

What types of backup (Full, Differential, Trans log) are available for a database in the Bulk Logged model?

Back 184

Full: Yes
Differential: Yes
Transaction Log: Yes/No minimally logged

Front 185

What types of backup (Full, Differential, Trans log) are available for a database in the Simple recovery model?

Back 185

Full: Yes
Differential: Yes
Transaction Log: No

Front 186

What is the PAGE_VERIFY CHECKSUM?

Back 186

Allows you to discover and log damaged page files (default configuration of SQL does not check for damaged pages).

Front 187

What happens when a damaged page is encountered?

Back 187

If PAGE_VERIFY CHECKSUM is not enabled, database could go offline. If enabled, an 824 error is returned to the calling application and logged to the SQL Server error log and Windows Application Event log and the ID of the damaged page is logged to the suspect_pages table in the msdb database.

Front 188

What happens if AUTO_CLOSE is enabled?

Back 188

When the last connection to a database is closed, SQL Server shuts down the database and releases all resources related to the database. When a new connection is made to the database, SQL Server starts up the database and begins allocating resources.

Front 189

What happens if AUTO_SHRINK is enabled?

Back 189

SQL Server periodically checks the space utilization of data and transaction log files. If the space checking algorithm finds a data file that has more that 25 percent free space, the file automatically shrinks to reclaim disk space.

Front 190

What happens if AUTO_CREATE_STATISTICS is enabled?

Back 190

SQL Server automatically creates statistics that are missing during the optimization phase of query processing.

Front 191

What happens if AUTO_UPDATE_STATISTICS is enabled?

Back 191

Updates out-of-date statistics during query optimization.

Front 192

What happens if AUTO_UPDATE_STATISTICS_ASYNCH is enabled?

Back 192

Controls whether statistics are updated during query optimization or if query optimization continues while the statistics are updated asynchronously.

Front 193

What is the CHANGE_TRACKING database option that SQL Server 2008 introduced? How do you specify how long to keep tracking information?

Back 193

Change tracking is a lightweight mechanism that associates a version with each row in a table that has been enabled for change tracking. Each time the row is changed, the version number is incremented. The CHANGE_RETENTION option and the AUTO_CLEANUP option control how long to keep tracking information.

Front 194

What are the three states that a database can be set to in order to control access?

Back 194

ONLINE
OFFLINE
EMERGENCY

Front 195

What access is granted to a database in an EMERGENCY state?

Back 195

Can only be accessed by members of the db_owner role and the only command allowed to be executed is SELECT.

Front 196

How can you control the ability to modify data for an online database? What impact does this have on transaction log files?

Back 196

By setting the database to READ_ONLY or READ_RIGHT. A database in READ_ONLY cannot be written to and SQL removes any transaction log file that is specified for the database. If changed to READ_WRITE, SQL recreates the transaction log file.

Front 197

What three option settings control user access to a database?

Back 197

SINGLE_USER
RESTRICTED_USER
MULTI_USER

Front 198

A database set to RESTRICTED_USER only allows access to: ?

Back 198

Members of the db_owner, dbcreator and sysadmin roles.

Front 199

What happens if multiple users are using the database when you change the mode to SINGLE_USER or users conflict with the allowed set for RESTRICTED_USER? What option can you add to control this?

Back 199

The ALTER DATABASE command is blocked until all the non-allowed users disconnect. ROLLBACK IMMEDIATE forcibly rolls back any open transactions along with disconnecting any nonallowed users. Using ROLLBACK AFTER <number of seconds> waits for the specified number of seconds before rolling back transactions and disconnecting users.

Front 200

What is the benefit of a parameterized query?

Back 200

It can reuse cached query plans more frequently and avoid the time required to build a query plan.

Front 201

How can you force SQL Server to read every page from disk and check the integrity?

Back 201

Use the DBCC CHECKDB command.

Front 202

When DBCC CHECKDB is executed, what actions are performed?

Back 202

Checks page allocation within the database.

Checks the structural integrity of all tables and indexed views.

Calculates a checksum for every data and index page to compare against the stored checksum.

Validates the contents of every indexed view.

Checks the database catalog.

Validates Service Broker data within the database.

Front 203

What commands does DBCC CHECKDB execute?

Back 203

DBCC CHECKALLOC, to check the page allocation of the database.

DBCC CHECKCATALOG, to check the database catalog.

DBCC CHECKTABLE, for each table and view in the database to check the structural integrity.

Front 204

If a database is participating in a database mirroring session and a corrupt page is found using PAGE_VERIFY CHECKSUM, what happens?

Back 204

SQL Server automatically retrieves a copy of the page from the mirror, replaces the page on the principal, and logs an entry in the sys.dm_db_mirroring_auto_page_repair view.

Front 205

If your application accesses multiple databases or you are creating an application with multiple databases that do not need to be stored on separate instances for increased capacity, what should you do?

Back 205

You should combine the objects into a single database and use schemas to separate groups of objects.

Front 206

What are the nine numeric data types in SQL Server 2008?

Back 206

TINYINT
SMALLINT
INT
BIGINT
DECIMAL(P,S) and NUMERIC(P,S)
SMALLMONEY
MONEY
REAL
FLOAT(N)

Front 207

What is the range of values and how much storage space is required for the data type TINYINT?

Back 207

Values: 0 to 255
Space: 1 byte

Front 208

What is the range of values and how much storage space is required for the data type SMALLINT?

Back 208

Values: -32,768 to 32,767
Space: 2 bytes

Front 209

What is the range of values and how much storage space is required for the data type INT?

Back 209

Values: -2^31 to 2^31-1
Space: 4 bytes

Front 210

What is the range of values and how much storage space is required for the data type BIGINT?

Back 210

Values: -2^63 to 2^63-1
Space: 8 bytes

Front 211

What is the range of values and how much storage space is required for the data type DECIMAL(P,S) and NUMERIC(P,S)?

Back 211

Value: - 10^38 +1 through 10^38 - 1
Space: Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17

Front 212

What is the range of values and how much storage space is required for the data type SMALLMONEY?

Back 212

Value: - 214,748.3648 to 214,748.3647
Space: 4 bytes

Front 213

What is the range of values and how much storage space is required for the data type MONEY?

Back 213

Value: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Space: 8 bytes

Front 214

What is the range of values and how much storage space is required for the data type REAL?

Back 214

Value: - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
Space: 4 bytes

Front 215

What is the range of values and how much storage space is required for the data type FLOAT(N)?

Back 215

Value: - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
Space: Depends on the value of n; if n's value is between 1-24, is has a precision of 7 digits and requires 4 bytes; if n is between 25-53, the precision is 15 digits and requires 8 bytes.

Front 216

What are the two parameters of decimal data types?

Back 216

Precision and scale. The precision indicates the total number of digits that can be stored both to the left and to the right of the decimal. The scale indicates the maximum number of digits to the right of the decimal point.

Front 217

What are the four character data types in SQL Server 2008? How much storage space is associated with each one?

Back 217

CHAR(n) - 1 byte per character defined by n, up to a maximum of 8,000 bytes.

VARCHAR(n) - 1 byte per character stored up to a maximum of 8,000 bytes.

NCHAR(n) - 2 bytes per character defined by n, up to a maximum of 4,000 bytes.

NVARCHAR(n) - 2 bytes per character stored up to a maximum of 4,000 bytes.

Front 218

What are the 6 time and date data types in SQL Server 2008?

Back 218

SMALLDATETIME

DATETIME

DATETIME2

DATETIMEOFFSET

DATE

TIME

Front 219

What are the range of values, the accuracy and storage space associated with SMALLDATETIME?

Back 219

Range: 01/01/1900 to 06/06/2079
Accuracy: 1 minute
Storage: 4 bytes

Front 220

What are the range of values, the accuracy and storage space associated with DATETIME?

Back 220

Range: 01/01/1753 to 12/31/9999
Accuracy: 0.00333 seconds
Storage: 8 bytes

Front 221

What are the range of values, the accuracy and storage space associated with DATETIME2?

Back 221

Range: 01/01/0001 to 12/31/9999
Accuracy: 100 nanoseconds
Storage: 6 to 8 bytes

Front 222

What are the range of values, the accuracy and storage space associated with DATETIMEOFFSET?

Back 222

Range: 01/01/0001 to 12/31/9999
Accuracy: 100 nanoseconds
Storage: 8 to 10 bytes
DATETIMEOFFSET allows you to store a time zone.

Front 223

What are the range of values, the accuracy and storage space associated with DATE?

Back 223

Range: 01/01/0001 to 12/31/9999
Accuracy: 1 day
Storage: 3 bytes

Front 224

What are the range of values, the accuracy and storage space associated with TIME?

Back 224

Range: 00:00:00.0000000 to 23:59:59.9999999
Accuracy: 100 nanoseconds
Storage: 3 to 5 bytes

Front 225

What are the three binary data types in SQL Server 2008?

Back 225

BIT

BINARY

VARBINARY

Front 226

What are the range of values and storage space associated with BIT?

Back 226

Range: Null, 0, 1
Storage: 1 bit

Front 227

What are the range of values and storage space associated with BINARY?

Back 227

Range: Fixed-length binary data
Storage: Up to 8,000 bytes

Front 228

What are the range of values and storage space associated with VARBINARY?

Back 228

Range: Variable-length binary data
Storage: Up to 8,000 bytes

Front 229

What is the XML data type?

Back 229

It allows you to store and manipulate Extensible Markup Language (XML) documents natively. You are limited to 2 GB per document as well as 128 levels within a document.

Front 230

What is an XML Schema Collection used for?

Back 230

When an XML Schema Collection is applied to an XML column, the only documents allowed to be stored within the XML column must first validate to the associated XML schema collection.

Front 231

What are the two spatial data types that SQL Server 2008 supports?

Back 231

GEOMETRY - based on Euclidian geometry and is used to store points, lines, curves, and polygons

GEOGRAPHY - based on an ellipsoid and is used to store data such as latitudes and longitudes.

Front 232

What are the 7 different geometric objects that a GEOMETRY column can contain?

Back 232

1. Point - Has x and y coordinates, with optional elevation and measure values.

2. LineString - A series of points that defines the start, end, and any bends in the line, with optional elevation and measure values.

3. Polygon - A surface defined as a sequence of points that defines an exterior boundary, along with zero or more interior rings. A polygon has at least three distinct points.

4. GeometryCollection - Contains one or more instances of other geometry shapes, such as a Point and a LineString.

5. MultiPolygon - Contains the coordinates for multiple Polygons.

6. MultiLineString - Contains the coordinates of multiple LineStrings.

7. MultiPoint - Contains the coordinates of multiple Points.

Front 233

What is the HIERARCHYID data type?

Back 233

The HIERARCHYID data type is used to organize hierarchical data, such as organization charts, bills of materials, and flowcharts. The HIERARCHYID stores a position within a tree hierarchy.

Front 234

What are the 7 properties that you can apply to a column?

Back 234

1. nullability

2. COLLATE

3. IDENTITY

4. ROWGUIDCOL

5. FILESTREAM

6. NOT FOR REPLICATION

7. SPARSE

Front 235

What database property sets the nullability property?

Back 235

ANSI_NULL_DEFAULT

Front 236

How can you insert a value into an identity column explicitly?

Back 236

By using the SET IDENTITY_INSERT <table name> ON command.

Front 237

How do you change the next value generated for an identity column by modifying the seed?

Back 237

Use the DBCC CHECKIDENT command.

Front 238

What is the ROWGUIDCOL property used for?

Back 238

Used mainly by merge replication to designate a column that is used to identify rows uniquely across databases. The ROWGUIDCOL property is used to ensure that only a single column of this type exists and that the column has a UNIQUEIDENTIFIER data type.

Front 239

What data type do columns that have the FILESTREAM property applied have?

Back 239

VARBINARY(MAX). The column maintains a 16 byte identifier for the file.

Front 240

Can you implement database mirroring against a database containing data stored with FILESTREAM?

Back 240

NO

Front 241

How is a FILEGROUP designated for FILESTREAM storage treated in a database snapshot?

Back 241

It is off-line and inaccessible.

Front 242

What is the SPARSE option used for?

Back 242

When a NULL is stored in a column designated as SPARSE, no storage space is consumed. However, non-NULL values require 4 bytes of storage space in addition to the normal space consumed by the data type. The column must allow nulls.

Front 243

What can you not apply the SPARSE property to?

Back 243

- Columns with the ROWGUIDCOL or IDENTITY property
- TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, or user-defined data types
- A VARBINARY(MAX) with the FILESTREAM property
- A computed column of a column with a rule or default bound to it
- Columns that are part of either a clustered index or a primary key
- A column within an ALTER TABLE statement

Front 244

What is the maximum size a row can be before you cannot issue an ALTER statement to either change a column to SPARSE or add an additional SPARSE column? Why?

Back 244

4,009 bytes. During the ALTER, each row is recomputed by writing a second copy of the row on the same data page. Because two copies of a row that exceed 4,009 bytes would exceed the 8,018 bytes allowed per page, the ALTER TABLE statement fails.

Front 245

How can you make a column SPARSE if the row size is more than 4,009 bytes?

Back 245

- Reduce the data within a row so that the maximum row size is greater than 4,009 bytes
- Create a new table, copy all the data to the new table, drop the old table, and then rename the newly created table
- Export the data, truncate the existing table, make the changes, and import the data back into the table

Front 246

If you do not want to incur the overhead of making the calculation of a Computed Column at runtime, what can you specify?

Back 246

The PERSISTED property. If a computed column is PERSISTED, SQL Server stores the result of the calculation in the row and updates the value anytime data that the calculation relies upon is changed.

Front 247

What is row-level compression? What is the limitation?

Back 247

Allows you to compress individual rows to fit more rows on a page, which reduces the storage space for the table because you don't need to store as many pages on a disk. Because you can uncompress the data at any time, in order for it to succeed, you cannot use compression to store more than 8,060 bytes in a single row.

Front 248

What commands do you use to have SQL Server compress new data added to a heap?

Back 248

BULK INSERT or INSERT INTO...WITH (TABLOCK) statement

Front 249

Do compression settings for a table pass to any nonclustered indexes or indexed views created against a table?

Back 249

NO

Front 250

Why can you not compress VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data?

Back 250

They store data in specialized structures outside the row; any data stored outside the row cannot be compressed.

Front 251

How many columns can a standard table in SQL 2008 have?

Back 251

1,024 columns

Front 252

What are wide tables?

Back 252

Tables that exceed 1,024 columns (up to a max of 30,000columns) by using a column set definition; data stored in any row still cannot exceed 8,019 bytes unless you have a VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) column defined for the table.

Front 253

Where are temporary tables stored?

Back 253

The tempdb database.

Front 254

What are the two types of temporary tables and how are they designated?

Back 254

Local or global. Local is designated by a name beginning with # and is visible only within the connection that created it. A global is designated by a name beginning with ## and is visible across all connections to the instance.

Front 255

What is a table variable?

Back 255

A table variable is a memory resident structure that is visible only within the connection that declared the variable and is deallocated after the code which declared the variable completes.

Front 256

What is a clustered primary key?

Back 256

A clustered primary key, the default SQL Server behavior, causes SQL Server to store the table in sorted order according to the primary key.

Front 257

When a clustered primary key is created on a table that is compressed, when is compression applied to the primary key?

Back 257

When the table is rebuilt.

Front 258

How is a unique constraint similar to a primary key?

Back 258

Neither allows duplicates, though a unique constraint allows NULLs. A unique constraint treats NULLs as it does any other data value; if a unique constraint is defined on a single column, then a single row within the table is allowed to have a NULL within the column.

Front 259

What is a default constraint?

Back 259

A default constraint allows you to specify a value that is written to the column if the application does not supply a value. SQL SERVER WRITES THE DEFAULT VALUE TO A COLUMN ONLY IF THE APPLICATION DOES NOT SPECIFY THE COLUMN IN THE INSERT STATEMENT.

Front 260

What is a check constraint?

Back 260

A check constraint limits the range of values within a column.

Front 261

How do check constraints at a column level differ from check constraints at a table level?

Back 261

Column-level check constraints cannot reference any other column in the table; table-level check constraints can reference any column within a table, but not other tables.

Front 262

What structure does SQL Server use to build and maintain indexes?

Back 262

A balanced tree (B-tree). A B-tree is constructed of a root node that contains a single page of data, one or more optional intermediate level pages, and one or more optional leaf level pages.

Front 263

How many bytes of data are in a SQL Server data page? How many can be used for actual data?

Back 263

A data page is 8,192 bytes, 8,060 bytes can be used to store data.

Front 264

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

Back 264

You should use a filtered index.

Front 265

What is the maximum number of columns that can be used to define an index?

Back 265

16

Front 266

What is a clustering key?

Back 266

The column(s) defined for a clustered index.

Front 267

What is special about a clustered index?

Back 267

It causes SQL to arrange the data in the table according to the clustering key.

Front 268

How many clustered indexes can you have for a table?

Back 268

Only one, since a table can only be sorted one way.

Front 269

What is a heap?

Back 269

A table without a clustered index.

Front 270

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

Back 270

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

Front 271

When is the FILESTREAM_ON clause used?

Back 271

When clustered indexes are crated on a table containing FILESTREAM data.

Front 272

If you specify a different filegroup in the FILESTREAM_ON clause than where the FILESTREAM data is currently located, what happens?

Back 272

All the FILESTREAM data will be moved to the newly specified filegroup during the creation of the clustered index.

Front 273

What restrictions doe nonclustered indexes have?

Back 273

Max size of 900 bytes in the index key, a maximum of 16 columns and a table is limited to 1,000 nonclustered indexes.

Front 274

Where does the pointer at the leaf level of a nonclustered index point if a clustered index exists on the table; if one does not?

Back 274

If a clustered index exists on the table, the leaf level of the nonclustered index points to the clustering key; if a clustered index does not exist, it points to the row of data in the table.

Front 275

What is a covering index?

Back 275

An index that is constructed such that SQL Server can completely satisfy queries by reading only the index.

Front 276

What is the benefit of using the INCLUDE clause with indexes?

Back 276

Included columns become part of the index only at the leaf level; values do not appear in root or intermediate levels and do not count against the 900 byte or 16 column restrictions.

Front 277

What does the query optimizer do?

Back 277

It is a component that decides whether or not to use an index to satisfy a query based on the distribution statistics that are stored for the index.

Front 278

What is meant by the selectivity of an index?

Back 278

The degree to which values in a column allow you to locate small sets of data; as the number of unique values within a column increases, so does the selectivity of an index. The query optimizer chooses the most selective indexes to satisfy queries.

Front 279

What is a filtered index?

Back 279

An index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table.

Front 280

What restrictions do filtered indexes have?

Back 280

The must be a nonclustered index.

They cannot be created on computed columns.

Columns cannot undergo implicit or explicit data type conversion.

Front 281

What is FILLFACTOR used for?

Back 281

It is used to control the rate at which page splits occur; it specifies the percentage of free space that should be left on the leaf level of an index during creation or rebuild.

Front 282

What is the PAD_INDEX option used for?

Back 282

It causes the FILLFACTOR to be applied to the intermediate-level page(s) and the root page of an index.

Front 283

What is the SORT_IN_TEMPDB option used for?

Back 283

Normally, during index creation, the work tables SQL uses for sorting the values prior to building the B-tree are created in the same database as the index. The use of this option causes them to be created in the tempdb database.

Front 284

What is the IGNORE_DUP_KEY option used for?

Back 284

In indexes designated as unique, multi-row inserts with a duplicate in one row would cause the entire transaction to roll back; this option rejects only the row with a duplicate.

Front 285

Index can be created either online or offline, how is this done and what does it mean?

Back 285

It is done using WITH ONLINE = ON / OFF. When off, SQL locks the entire table, preventing any changes until the index is created. When on, SQL allows changes to the table during creation of the index by using the version store within the tempdb database.

Front 286

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

Back 286

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

Front 287

What versions of SQL Server 2008 support online index creation, rebuild or online restore?

Back 287

Only SQL server 2008 enterprise.

Front 288

What is a primary XML index?

Back 288

A primary XML index is built against all the nodes within the XML column. The primary XML index is also tied to the table by maintaining a link to the corresponding row in the clustered index. Therefore, a clustered index is required before you can create a primary XML index.

Front 289

What is a secondary XML index?

Back 289

Secondary indexes can be created on PATH, VALUE, or PROPERTY. A primary XML index is first required, because secondary XML indexes are built against the data contained within the primary XML index.

Front 290

What is a spatial index?

Back 290

It is an index created against a spatial column that it types as either geometry or geography.

Front 291

What is the decomposition process by which spatial data is converted from a 2D system to a linear structure?

Back 291

Tessellation.

Front 292

What is the BOUNDING_BOX parameter?

Back 292

The BOUNDING_BOX parameter defines the maximum and minimum x, y coordinates that are considered when constructing the grid hierarchy and tessellating the rows of geometry data.

Front 293

What are the three types of XML indexes that you can create?

Back 293

PATH, VALUE and PROPERTY.

Front 294

How do you defragment an index?

Back 294

You use the ALTER INDEX statement.

Front 295

What are the two options you can use when you defragment an index?

Back 295

You can either REBUILD or REORGANIZE.

Front 296

What does the REBUILD option in an ALTER INDEX statement do?

Back 296

The REBUILD option rebuilds all levels of the index and leaves all pages filled according to the FILLFACTOR setting of an index. If you rebuild the clustered index, only the clustered index is rebuilt. However, rebuilding the clustered index with the ALL keyword also rebuilds all nonclustered indexes on the table. The rebuild of an index effectively re-creates the entire B-tree structure, so unless you specify the ONLINE option, a shared table lock is acquired, preventing any changes until the rebuild operation completes.

Front 297

What does the REORGANIZE option in an ALTER INDEX statement do?

Back 297

The REORGANIZE option removes fragmentation only at the leaf level. Intermediate-level pages and the root page are not defragmented during a reorganize. REORGANIZE is always an online operation that does not incur any long-term blocking.

Front 298

How do you disable an index?

Back 298

ALTER INDEX { index_name / ALL }
ON <object>
DISABLE [ ; ]

Front 299

What happens when an index is disabled?

Back 299

When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index as data in the table changes, and the index cannot be used to satisfy queries. If a clustered index is disabled, the entire table becomes inaccessible.

Front 300

How do you re-enable an index that was disabled?

Back 300

It must be rebuilt using the REBUILD option:

ALTER INDEX { index_name / ALL }
ON <object>
REBUILD [ ; ]

Front 301

In SQL Server 2008, where are the contents of a full text catalog stored?

Back 301

In the database, unlike previous versions, where it was stored in a directory on the operating system.

Front 302

What does the ACCENT_SENSITIVITY option provide?

Back 302

It allows you to configure whether the full text engine considers accent marks when building or querying a full text index.

Front 303

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

Back 303

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

Front 304

What columns can be used to create a full text index?

Back 304

CHAR

VARCHAR

XML

VARBINARY

Front 305

What are word breakers?

Back 305

Assemblies that locate breaks between words within the data being indexed for a full text index.

Front 306

What are stemmers?

Back 306

Stemmers conjugate verbs so that your queries can locate information even across multiple verb tenses.

Front 307

What are stop words?

Back 307

In the creation of a full text index, stop words are common words that you would not normally search upon (e.g. the, a, an).

Front 308

How many full text indexes can a table or index view have?

Back 308

Only one, though it can be created on multiple columns.

Front 309

What is the CHANGE_TRACKING option for a full text index used for?

Back 309

It determines how SQL maintains the index when underlying data changes are made. The options are AUTO, OFF or MANUAL.

Front 310

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

Back 310

A full text catalog.

Front 311

What columns can you create a full text index on?

Back 311

CHAR/VARCHAR, XML, and VARBINARY.

Front 312

What are the four commands that SQL Server uses to query full text?

Back 312

CONTAINS

FREETEXT

CONTAINSTABLE

FREETEXTTABLE

Front 313

What is the difference between CONTAINS and FREETEXT and CONTAINSTABLE and FREETEXTTABLE?

Back 313

CONTAINS and FREETEXT return a True/False value used to restrict a result set, and CONTAINSTABLE and FREETEXTTABLE return a result set that can be used to extend query functionality.

Front 314

Are all search terms used with full text Unicode strings or non-Unicode strings?

Back 314

Unicode?

Front 315

What happens if you pass a non-Unicode string to a full-text query?

Back 315

If you pass in a non-Unicode string, the query still works, but it is much less efficient because the optimizer cannot use parameter sniffing to evaluate distribution statistics on the full text index.

Front 316

What features does the CONTAINS query allow you to search on?

Back 316

Search word forms

Search for word proximity

Provide relative weighting to terms

Front 317

How do CONTAINS AND FREETEXT differ in fuzzy searching?

Back 317

It is the default in FREETEXT, while you have to specify that you want it in CONTAINS.

Front 318

What are the two options you can use with FORMSOF?

Back 318

INFLECTIONAL, which causes the full text search engine to consider word stems (e.g. searching for driven also searches for drove, drive, driving).

THESAURUS which produces synonyms for the search terms.

Front 319

What type of file are Thesaurus files and where are they located?

Back 319

XML; the FTDATA directory underneath your default SQL Server installation path.

Front 320

What does ISABOUT perform?

Back 320

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

Front 321

When is the thesaurus file used?

Back 321

After it is defined, FREETEXT and FREETEXTTABLE use it automatically; CONTAINS and CONTAINTSTABLE use it if specified with the FORMSOF THESAURUS option.

Front 322

What are the two set types that a thesaurus file contain?

Back 322

A thesaurus can contain expansion sets or replacement sets. A replacement set defines a term or terms that are replaced within the search argument prior to the word breaker tokenizing the argument list. An expansion set defines a set of terms that are used to expand upon a search argument. When an expansion set is used, a match on any term within the expansion set causes SQL Server to retrieve the row.

Front 323

What setting specifies whether the thesaurus is accent sensitive?

Back 323

The diacritics setting.

Front 324

Where are Stop lists contained in SQL 2008, how does this differ from SQL 2005?

Back 324

In 2008, they are stored in the database. In 2005, they were stored in the FTDATA directory.

Front 325

What are the options for populating a Full text index?

Back 325

- FULL Reprocesses every row from the underlying data to rebuild the full text index completely.

- INCREMENTAL Processes only the rows that have changed since the last population; requires a timestamp column on the table.

- UPDATE Processes any changes since the last time the index was updated; requires that the CHANGE_TRACKING option is enabled for the index and set to MANUAL.

Front 326

How do you reload the English thesaurus file?

Back 326

EXEC sys.sp_fulltext_load_thesaurus_file 1033

Front 327

How do you manage thesaurus files?

Back 327

You edit the language specific file that is contained in the FTDATA directory for the instance.

Front 328

What commands do you use to build a list of stop words for a full text index?

Back 328

CREATE FULLTEXT STOPLIST and ALTER FULLTEXT STOPLIST

Front 329

What is the process for partitioning a table, index or indexed view?

Back 329

1. Create a partition function.

2. Create a partition scheme mapped to a partition function.

3. Create the table, index or indexed view on the partition scheme.

Front 330

What data types cannot be used with partition functions?

Back 330

You cannot use text, ntext, image, xml, varbinary(max), varchar(max), or any CLR data types.

Front 331

What is the maximum number of partitions allowed for a table?

Back 331

The maximum number of partitions for a table is 1,000.

Front 332

What is the maximum number of boundary points allowed for a partition function?

Back 332

The maximum number of boundary points for a partition function is 999.

Front 333

What is the AS clause used for in a partition function?

Back 333

The AS clause allows you to specify whether the partition function you are creating is RANGE LEFT or RANGE RIGHT.

Front 334

What is the FOR VALUES clause used for in a partition function?

Back 334

It is used to specify the boundary points for the partition function. If the partition function is created as RANGE LEFT, then the boundary point is included in the left partition. If the partition function is created as RANGE RIGHT, then the boundary point is included in the right partition.

Front 335

Where are NULL values stored in a partition?

Back 335

They are always stored in the leftmost partition until you explicitly specify null as a boundary point and use the RANGE RIGHT syntax, in which case nulls are stored in the rightmost partition.

Front 336

How do you partition an existing object after it has been populated with data?

Back 336

To partition an existing table, you need to drop the clustered index and re-create the clustered index on the partition scheme. To partition an existing index or indexed view, drop the index and re-create the index on a partition scheme. You will want to be very careful when partitioning existing objects that already contain data, because implementing the partition will cause a significant amount of disk input/output (I/O).

Front 337

What happens if more filegroups are included in the partition scheme than there are partitions?

Back 337

Any excess filegroups will not be used to store data unless explicitly specified by using the ALTER PARTITION SCHEME command.

Front 338

If you specify the ALL keyword when creating a partition scheme, what is the maximum number of filegroups you can specify?

Back 338

1

Front 339

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

Back 339

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

Front 340

How do you create a partitioned table or indexed view?

Back 340

Instead of using the ON clause to specify a filegroup, you specify a partition scheme.

Front 341

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

Back 341

The computed column must be persisted.

Front 342

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

Back 342

The SPLIT operator is used to introduce a new boundary point. The MERGE operator is used to remove a boundary point.

Front 343

Which operator is used to move partitions between tables?

Back 343

The SWITCH operator is used to move partitions between tables.

Front 344

What are the two purposes of the NEXT USED clause?

Back 344

1. It adds a new filegroup to the partition scheme, if the specified filegroup is not already part of the partition scheme.

2. It marks the NEXT USED property for a filegroup (The filegroup that is marked with the NEXT USED flag is the filegroup that contains the next partition that is created when a SPLIT operation is executed.).

Front 345

What is aligned storage?

Back 345

When a table and all its indexes use the same partition function and the same partition scheme. This ensures that if a single partition is backed up or restored, the data and corresponding indexes are kept together in a single unit.

Front 346

What requirements does the SWITCH operator have?

Back 346

-- The data and index for the source and target tables must be aligned.

-- Source and target tables must have the same structure.

-- Data cannot be moved from one filegroup to another.

-- Two partitions with data cannot be exchanged.

-- The target partition must be empty.

-- The source or target table cannot participate in replication.

-- The source or target tables cannot have full text indexes or a FILESTREAM data type defined.

Front 347

If you are exporting data using BCP, the account that BCP is running under needs what permissions?

Back 347

Only SELECT

Front 348

If you are importing data using BCP, the account that BCP is running under needs what permissions?

Back 348

SELECT, INSERT and ALTER TABLE

Front 349

In BCP, what does the first argument specify?

Back 349

The table or query that BCP operates upon.

Front 350

In BCP, what does the second argument specify?

Back 350

Can be set to in, out or queryout. When in, BCP imports the contents of the file specified. When out, BCP exports the entire contents of the table into the file specified.

Front 351

In BCP, what does the third argument specify?

Back 351

The file that is the source or target of the BCP command.

Front 352

How do you use queryout with BCP?

Back 352

You replace the name of the table with a query delimited by double quotes.

Front 353

What do the -n and -c switches specify in BCP?

Back 353

They are mutually exclusive. -n specifies that the data in the files is in the native format of SQL Server. The-c switch specifies that the data is a character format.

Front 354

In BCP, what does the -S switch specify?

Back 354

The instance name to connect to.

Front 355

In BCP, what does the -T switch specify?

Back 355

Designates a trusted connection and BCP uses the windows credentials of the account that is executing the BCP command to connect.

Front 356

In BCP, what does the -U switch specify?

Back 356

login name

Front 357

In BCP, what does the -P switch specify?

Back 357

password

Front 358

In BCP, what does the -h switch specify?

Back 358

Enforces check constraints and fire triggers during the import.

Front 359

How does the BULK INSERT command differ from BCP?

Back 359

BULK INSERT cannot export data and is a T-SQL command, thus does not require the instance name or login credentials.

Front 360

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

Back 360

SSIS

Front 361

What three data types can the Import and Export Wizard not work with?

Back 361

Hierarchy, geography and geometry.

Front 362

What permission is required by BCP to use the -h option other than SELECT and INSERT?

Back 362

ALTER TABLE

Front 363

Where are all the objects for Policy Based Management stored?

Back 363

Within the msdb database.

Front 364

How can you get a list of the facets available?

Back 364

By querying the dbo.syspolicy_management_facets table.

Front 365

What are the five objects that are used within Policy Based Management?

Back 365

Facets, conditions, policies, policy targets, and policy categories.

Front 366

What are the allowed execution modes for a policy?

Back 366

The policy execution modes are On demand, On schedule, On change, Log only, and On change, prevent.

Front 367

Which object has a property that allows you to mandate checking for all databases on an instance?

Back 367

Policy categories allow you to mandate checking of all databases within an instance.

Front 368

How many facets can be checked within a single condition?

Back 368

A condition can be defined on only one facet.

Front 369

How many conditions can be checked within a single policy?

Back 369

A policy can check only a single condition.

Front 370

What is a facet?

Back 370

Facets define the type of object or option to be checked, such as database, Surface Area and login.

Front 371

If you define a condition using the advanced editor, can a policy that incorporates the condition be scheduled?

Back 371

No, it must be executed manually.

Front 372

What are the four execution modes that a policy can be set for?

Back 372

-- On demand Evaluates the policy when directly executed by a user.

-- On change, prevent Creates data definition language (DDL) triggers to prevent a change that violates the policy.

-- On change, log only Checks the policy automatically when a change is made using the event notification infrastructure.

-- On schedule Creates a SQL Server Agent job to check the policy on a defined schedule.

Front 373

What effect does the policy category mandate property have when applied to a database?

Back 373

When a policy category is set to Mandate and a sysadmin subscribes the instance to a policy category, all databases that meet the target set are controlled by the policies within the policy category. A policy subscription to a policy category set to Mandate cannot be overridden by a database owner.

Front 374

What is the mandate property setting for the default category?

Back 374

Mandate enabled.

Front 375

What security context do all backup execute under?

Back 375

Under the SQL Server service account.

Front 376

What databases can a sysadmin backup?

Back 376

All the databases in an instance.

Front 377

What databases can a db_owner backup?

Back 377

Only their databases.

Front 378

What database role can you add a user to in order to allow them to back up a database while preventing any other access to the database?

Back 378

db_backupoperator

Front 379

What are the four types of backups you can perform in SQL Server 2008?

Back 379

Full
Differential
Transaction Log
Filegroup

Front 380

What does a Full Backup contain?

Back 380

All pages within a database that contain data; pages that do no contain data are no included in the backup.

Front 381

What is the limiting factor for the speed of a backup?

Back 381

The performance of the device where the backup is being written.

Front 382

What are the backup procedure steps that SQL Server performs?

Back 382

1. Locks the database, blocking all transactions

2. Places a mark in the transaction log

3. Releases the database lock

4. Extracts all pages in the data files and writes them to the backup device

5. Locks the database, blocking all transactions

6. Places a mark in the transaction log

7. Releases the database lock

8. Extracts the portion of the log between the marks and appends it to the backup

Front 383

What are the only operations not allowed during a backup?

Back 383

Adding or removing a database file and shrinking a database.

Front 384

What are the only two parameters required for a backup?

Back 384

The name of the database and the backup device.

Front 385

What happens when you specify more than one backup device? What is the max number you can have?

Back 385

SQL Server stripes the backup across each of the devices specified. The max is 64.

Front 386

What effect does the MIRROR TO clause have on a backup?

Back 386

When you include the MIRROR TO clause, SQL Server retrieves the page once from the database and writes a copy of the page to each backup mirror.

Front 387

What requirements do mirrored backups have?

Back 387

-- All backup devices must be of the same media type.

-- Each mirror must have the same number of backup devices.

-- WITH FORMAT must be specified in the backup command.

Front 388

Why is a compressed backup always worth the processing overhead?

Back 388

The amount of time saved far exceeds the overhead associated with the compression operation.

Front 389

What do the INIT/NOINIT options of a BACKUP command control?

Back 389

Whether an existing backup file is overwritten or appended to.

NOINIT = append

INIT = overwrite

Front 390

What does the CHECKSUM option of a BACKUP command control?

Back 390

When CHECKSUM is specified, SQL Server verifies the page checksum, it if exists, before writing the page to the backup. Also, a checksum is calculated for the entire backup that can be used to determine if the backup has been corrupted.

Front 391

What is the default behavior for errors encountered during a backup?

Back 391

STOP_ON_ERROR - if an invalid page checksum is encountered during backup, the backup terminates with an error.

Front 392

How do you continue to backup past an invalid checksum?

Back 392

Use the CONTINUE_PAST_ERROR option.

Front 393

What is Log Sequence Number (LSN)?

Back 393

A unique number assigned to each row of a transaction log. It begins with one and increments to infinity, never repeating.

Front 394

What does the active portion of the transaction log contain? The inactive?

Back 394

The active portion contains all changes that have not yet been committed. The inactive portion contains all the changes that have been committed to the database.

Front 395

What happens if an LSN gap is introduced into the transaction log backup?

Back 395

You must create a full backup before you can start backing up the transaction log.

Front 396

What does a differential backup capture?

Back 396

All extents that have changed since the last full backup.

Front 397

How are the changes since the last full backup tracked?

Back 397

By using a special page in the header of a database called the Differential Change Map (DCM). A full backup zeroes out the contents of the DCM.

Front 398

When an extent is changed, how does the change appear in the DCM?

Back 398

The bit corresponding to the extent is set to 1.

Front 399

What does the COPY_ONLY option in a backup allow?

Back 399

It allows you to create a backup but does not affect the database state or set of backups in production. e.g. a full backup with COPY_ONLY set does not zero out the DCM and a transaction log backup with COPY_ONLY does not remove transactions from the transaction log.

Front 400

What is a Filegroup backup?

Back 400

It allows you to target a portion of the database to be backed up (reducing time and the footprint of the backup).

Front 401

What is a partial backup?

Back 401

Partial backups back up the primary filegroup, all read/write filegroups and any explicitly specified read-only filegroups. In this way, you eliminate backing up read-only filegroups, which would not change.

Front 402

How do you perform a partial backup?

Back 402

By specifying the READ_WRITE_FILEGROUPS option:

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS [,<file_filegroup_list>] TO <backup_device>

Front 403

What command to you execute to have SQL Server detect and quarantine corrupted pages?

Back 403

ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM

Front 404

Is there a limit to the number of corrupt pages in a database?

Back 404

Yes, SQL puts a limit of 1000 corrupt pages on a database. Once you reach the limit, the database is taken offline and places it in a suspect state to protect it from further damage.

Front 405

What are maintenance plans and what tasks are supported?

Back 405

They provide a mechanism to graphically create job workflows that support common administrative functions. The tasks supported are:

-- Backing up of databases and transaction logs
-- Shrinking databases
-- Re-indexing
-- Updating of statistics
-- Performing consistency checks

Front 406

What are maintenance plans based on?

Back 406

SSIS

Front 407

What is a service master key?

Back 407

A service master key is created automatically the first time than an instance is started. It is regenerated each time that you change the SQL Server service account or password.

Front 408

How do you backup the service master key?

Back 408

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'

ENCRYPTION BY PASSWORD = 'password'

Front 409

What are database master keys (DMKs)?

Back 409

DMKs are created prior to the creation of a certificate, symmetric key or asymmetric key. A DMK is the root of the encryption hierarchy in a database.

Front 410

How do you backup a DMK?

Back 410

BACKUP MASTER KEY TO FILE = 'path_to_file'

ENCRYPTION BY PASSWORD = 'password'

Front 411

What must you do before you backup the DMK?

Back 411

The DMK must be open:

USE <database name>;

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<SpecifyStrongPasswordHere>';

Front 412

By default, what is the DMK encrypted with?

Back 412

The service master key.

Front 413

How do you backup just the public key of a certificate?

Back 413

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'

Front 414

How do you backup a certificate (both the private and public keys)?

Back 414

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'

[ WITH PRIVATE KEY

(FILE = 'path_to_private_key_file' ,
ENCRYPTION BY PASSWORD = 'encryption_password'
[ , DECRYPTION BY PASSWORD = 'decryption_password' ] ) ]

Front 415

How do you validate a backup?

Back 415

RESTORE VERIFYONLY FROM <backup device>

Front 416

What checks does SQL perform when a backup is validated?

Back 416

-- Calculates a checksum for the backup and compares to the checksum stored in the backup file.

-- Verifies that the header of the backup is correctly written and valid.

-- Transits the page chain to ensure that all pages are contained in the database and can be located.

Front 417

What does the NO_TRUNCATE option allow?

Back 417

It allows you to backup the transaction log without writing to the master data file.

Front 418

Where is the LSN of the last modification stored at the database level, the data file level and the data page?

Back 418

At database level, the LSN of the last modification to the db is stored in the header of the master data file. At data file level, the LSN of the last change to a page within the file is stored in the header of the data file. At data page level, the LSN of the last change to the data page is stored.

Front 419

What is a dirty page?

Back 419

A modified page in the buffer pool. Every dirty page tracks the LSN in the transaction log that corresponds to the change that modified the page in the buffer pool.

Front 420

What happens to dirty pages when SQL server executes a checkpoint?

Back 420

All dirty pages in the buffer pool are written out to the data files.

Front 421

What happens during the checkpoint process?

Back 421

During the checkpoint process, SQL Server compares the LSN of the dirty page in the buffer pool to the LSN of the data page on disk.

If the LSN of the data page on disk is equal to or less than the LSN of the dirty page in the buffer pool as well as equal to or less than the LSN for the data file, the page on disk is overwritten with the page from the buffer pool.

If the LSN of the dirty page is greater than the page on disk or the data file containing the page, the page in the buffer pool is overwritten by the page on disk.

When the checkpoint process finishes writing dirty pages to the data files, the largest LSN written to each file is written into the header of the file.

In addition, the largest LSN written across the entire checkpoint process is written to the header of the master data file. SQL Server ensures that the LSN for every page within a file is equal to or less than the LSN for the file and that the LSN for every file within a database is equal to or less than the LSN for the database.

The final step in the process is to clear the flag on each dirty page affected by the checkpoint that designates the page has changed.

Front 422

What are the two phases of restart recover. When does restart recovery take place?

Back 422

UNDO and REDO. It takes place when SQL server is started.

Front 423

What happens during REDO phase of restart recovery?

Back 423

All committed transactions in the transaction log are flushed to disk.

Front 424

What happens during the UNDO phase of restart recovery?

Back 424

During UNDO, which occurs after REDO, the transaction log is examined and any transactions that are still open are invalidated, ensuring that they cannot be written to disk.

Front 425

What is rolling forward?

Back 425

After UNDO phase completes, the database is rolled forward: SQL Server reads the last LSN recorded in the transaction log, increments the LSN and writes the new LSN into the header of every data file within the database, ensuring that transactions older than the roll-forward point cannot be written to the data files.

Front 426

What do all restore sequences begin with?

Back 426

Either a full backup or filegroup backup.

Front 427

After the database or filegroup being restored has been brought back online, can you apply additional differential or transaction log backups to the database?

Back 427

No.

Front 428

During a RESTORE command, what happens if the database does not already exist within the instance?

Back 428

It is created.

Front 429

What does the REPLACE option during a RESTORE command control?

Back 429

Forces the restore over the top of an existing database.

Front 430

Why does a restore take about 30% longer than the backup?

Back 430

SQL is optimized to backup and can pull pages into the backup regardless of order. During a restore, the pages must be placed back into the database in sequential order.

Front 431

What do the first pages within a database store?

Back 431

The structural information about the db, such as the list of pages allocated to the db.

Front 432

During a RESTORE command, what does the RECOVERY option provide?

Back 432

After the RESTORE operation completes, the db is online and accessible.

Front 433

During a RESTORE command, what does the NORECOVERY option provide?

Back 433

The RESTORE operation completes, but the database is left in a RECOVERY state, such that subsequent differential and/or transaction log backups can be applied.

Front 434

During a RESTORE command, what does the STANDBY option provide?

Back 434

Allows you to issue SELECT statements against the database while still issuing additional differential and/or transaction log restores. If you restore a db with the STANDBY option, an additional file is created to make the db consistent as of the last restore that was applied.

Front 435

During a RESTORE command, what does the MOVE option provide?

Back 435

It allows you to change the location of db files during the restore.

Front 436

What is the first step in any restore operation?

Back 436

You should issue a transaction log backup against the original db. This is referred to as backing up the tail of the log.

Front 437

What is the syntax for restoring a full or differential backup?

Back 437

RESTORE DATABASE { database_name / @database_name_var }

[ FROM <backup_device> [ ,...n ] ]
[ WITH {[ RECOVERY / NORECOVERY /
STANDBY = {standby_file_name / @standby_file_name_var } ]
/ , <general_WITH_options> [ ,...n ]
/ , <replication_WITH_option>
/ , <change_data_capture_WITH_option>
/ , <service_broker_WITH options>
/ , <point_in_time_WITH_options‚ RESTORE_DATABASE>
} [ ,...n ]
]

<general_WITH_options> [ ,...n ]::=

--Restore Operation Options

MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'

[ ,...n ] / REPLACE / RESTART / RESTRICTED_USER

Front 438

What is the syntax for restoring a transaction log backup?

Back 438

RESTORE LOG { database_name / @database_name_var }

[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH {[ RECOVERY / NORECOVERY /
STANDBY = {standby_file_name / @standby_file_name_var } ]
/ , <general_WITH_options> [ ,...n ]
/ , <replication_WITH_option>
/ , <point_in_time_WITH_options‚ RESTORE_LOG>} [ ,...n ] ]
<point_in_time_WITH_options, RESTORE_LOG>::=
/ { STOPAT = { 'datetime' / @datetime_var }
/ STOPATMARK = { 'mark_name' / 'lsn:lsn_number' }
[ AFTER 'datetime' ]
/ STOPBEFOREMARK = { 'mark_name' / 'lsn:lsn_number' }
[ AFTER 'datetime' ]

Front 439

During a transaction log restore, what does the STOPAT command allow?

Back 439

Allows you to specify a date and time to which SQL Server restores.

Front 440

During a transaction log restore, what do the STOPATMARK and STOPBEFOREMARK commands allow?

Back 440

Allow you to specify either an LSN or a transaction log MARK to use for the stopping point in a restore operation.

Front 441

How does the primary filegroup state affect the state of the db.

Back 441

The state of a db equals the state of the primary filegroup.

Front 442

To take advantage of the online restore capabilities of SQL Server 2008 Enterprise, what type of restores can you do?

Back 442

Either a file or filegroup restore, which does not include the primary filegroup. A full restore affects the entire database, which includes the primary filegroup (which sets the state of the db).

Front 443

What do you do if page corruption occurs in an index?

Back 443

Drop and recreate the index.

Front 444

What are the requirements for a page restore?

Back 444

-- The database must be in either the Full or Bulked-logged recovery model.

-- You must be able to create a transaction log backup.

-- A page restore can apply only to a read/write filegroup.

-- You must have a valid full, file, or filegroup backup available.

-- The page restore cannot be executed at the same time as any other restore operation.

Front 445

What is the syntax to restore a page?

Back 445

RESTORE DATABASE database_name
PAGE = 'file:page [ ,...n ]' [ ,...n ]
FROM <backup_device> [ ,...n ]
WITH NORECOVERY

Front 446

What are the steps to restore a page to a database?

Back 446

1. Retrieve the PageID of the damaged page.

2. Using the most recent full, file, or filegroup backup, execute the following command:

RESTORE DATABASE database_name
PAGE = 'file:page [ ,...n ]' [ ,...n ]
FROM <backup_device> [ ,...n ]
WITH NORECOVERY

3. Restore any differential backups with the NORECOVERY option.

4. Restore any additional transaction log backups with the NORECOVERY option.

5. Create a transaction log backup.

6. Restore the transaction log backup from step #5 using the WITH RECOVERY option.

Front 447

What is a best effort restore?

Back 447

If the backup media is damaged, SQL server will continue past the source of damage and continue restoring as much of the database as possible. After the restore, the db is set to EMERGENCY mode.

Front 448

What do you need to specify to restore from backup media that has been damaged?

Back 448

CONTINUE_AFTER_ERROR for a RESTORE DATABASE or RESTORE LOG command.

Front 449

What versions of SQL is database snapshot available?

Back 449

Only SQL Server 2008 Enterprise

Front 450

What happens if you create a database snapshot against a database with FILESTREAM data?

Back 450

The FILESTREAM filegroup is disabled and not accessible.

Front 451

What is the syntax to create a database snapshot?

Back 451

CREATE DATABASE database_snapshot_name
ON
(NAME = logical_file_name,
FILENAME = 'os_file_name') [ ,...n ]
AS SNAPSHOT OF source_database_name

Front 452

What are the requirements to create a database snapshot?

Back 452

-- You must include an entry for each data file specified in the source database

--the logical name of each file must match the name in the source database exactly

Front 453

What restrictions exist for a database snapshot?

Back 453

-- You cannot back up, restore, or detach a Database Snapshot.

-- The Database Snapshot must exist on the same instance as the source database.

-- Full text indexes are not supported.

-- FILESTREAM is not supported, and any FILESTREAM data is inaccessible through the Database Snapshot.

-- You cannot create a Database Snapshot against a system database.

-- You cannot drop, restore, or detach a source database that has a Database Snapshot created against it.

-- You cannot reference filegroups that are off-line, defunct, or restoring.

Front 454

How much space on disk does SQL Server allocate for a database snapshot?

Back 454

SQL Server takes advantage of sparse files -- essentially an entry in the file allocation table and consumes almost no space on disk. As data is added to the file, the file automatically grows on disk.

Front 455

How does a database snapshot act from an application perspective.

Back 455

It looks and acts like a read-only database to any queries.

Front 456

How much data does a database snapshot contain at the time of creation?

Back 456

None.

Front 457

How many pages are in an extent?

Back 457

8 pages; SQL Server reads and writes extents.

Front 458

What is the copy-on-write mechanism?

Back 458

The means by which SQL Server manages changes that occur within the source database of a database that has a database snapshot. The first time a modification is made to a data page within an extent occurs in the source db, SQL Server copies the before image of the extent to the Database snapshot.

Front 459

What is the max size of a database snapshot?

Back 459

The amount of data that existed in the source database at the time of creation.

Front 460

What is a database revert?

Back 460

A special category of restoring data when you have a database snapshot. If you need to revert only a row or portion of a db, you can use and INSERT, UPDATE, DELETE or MERGE statement. SQL Server also allows you to revert the entire db using the snapshot, if necessary.

Front 461

What is the syntax to revert a db from a database snapshot?

Back 461

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

Front 462

What restrictions are there on reverting a source database?

Back 462

-- Only a single Database Snapshot can exist for the source database.

-- Full-text catalogs on the source database must be dropped and then re-created after the revert completes.

-- Because the transaction log is rebuilt, the transaction log chain is broken.

-- Both the source database and Database Snapshot are off-line during the revert process.

-- The source database cannot be enabled for FILESTREAM.

Front 463

What two features are incompatible with Database snapshots?

Back 463

FILESTREAM and full text indexes.

Front 464

Prior to reverting a database using a Database snapshot, what must you do?

Back 464

You must drop all database snapshots except the database snapshot being used as the source for the RESTORE command.

Front 465

What is a job step?

Back 465

The execution elements within a job.

Front 466

What type of job steps can be executed?

Back 466

Transact-SQL (T-SQL)

Replication tasks

Operating system tasks or executable files

Analysis Services tasks

Integration Services packages

ActiveX scripts

Front 467

What is the default security context for a job step?

Back 467

It corresponds to the login that is set as the owner of the job. You can also override the security context by specifying a proxy account that the SQL Server agent uses for the job step based on credentials assigned to the proxy account.

Front 468

What options can a job step be configured with?

Back 468

Logging

Notification to an operator

Retry settings that specify the number of times to retry a step as well as the number of minutes between retries

Control flow logic

Front 469

What control flow options are available for a job?

Back 469

Quit job reporting success

Quit job reporting failure

Go to next step

Go to a specific step number

Front 470

What are the three logging options that are available to a job?

Back 470

Log to a file that is overwritten each time, append to a file or log to a table (not recommended due to overhead).

Front 471

In addition to any logging configured for a job step, what log information is gathered?

Back 471

SQL Server logs information into the dbo.sysjobhistory table in the msdb database for each job step that is executed within the job. Some of the information recorded is:

-- job step
-- status
-- execution date and time
-- duration
--if an error occurs, the number, severity, and text of the last error message generated

Front 472

If a job fails, where can you look to diagnose the problem?

Back 472

The first place to look is in the job history, which can be accessed from SQL Server Management Studio (SSMS) by right-clicking a job and selecting View History. You can also look in the logging files that are configured for each job step. In some cases, you might find additional information in the Microsoft Windows event logs.

Front 473

What is an Operator for a job?

Back 473

An alias for a person, group or device. Operators are used to send notifications when jobs fail or an alert is generated. For each operator, you specify a name along with contact information.

Front 474

What are the three types of SQL Server Agent alerts?

Back 474

-- A SQL Server event

-- A performance condition alert

-- A windows management instrumentation (WMI) event

Front 475

What are the two response actions that can be configured for an alert?

Back 475

You can have an alert send a notification or execute a job in response to the alert condition.

Front 476

What are the two basic parts of a TCP endpoint?

Back 476

A transport and a payload

Front 477

Endpoint can be of two different transports, what are they?

Back 477

TCP and HTTP

Front 478

What values can an Endpoint payload have?

Back 478

SOAP, TSQL, SERVICE_BROKER and DATABASE_MIRRORING

Front 479

What are the valid combinations of Endpoint Transport and Payload?

Back 479

TCP TSQL

TCP SERVICE_BROKER

TCP DATABASE_MIRRORING

HTTP SOAP

Front 480

What do TCP endpoint do?

Back 480

They reject requests that are not properly formatted based on the endpoint definition.

Front 481

What are the two layers of endpoint access?

Back 481

The first layer is the endpoint state; the second layer is permission to connect to the endpoint (an application must have a login created in SQL Server that has the CONNECT permission granted on the endpoint before the connection is allowed through the endpoint).

Front 482

What are the three states than an endpoint can have?

Back 482

Started - endpoint is actively listening for connections and will reply

Stopped - endpoint is actively listening but returns a connection error to an application

Disabled - endpoint does not listen and does not respond to any connection attempted

Front 483

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

Back 483

LISTENER_PORT -Required

LISTENER_IP -Optional

Front 484

What is the default TCP port for database mirroring?

Back 484

5022

Front 485

What is the default TCP port for TSQL?

Back 485

1433

Front 486

What is the default setting for LISTENER_IP?

Back 486

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

Front 487

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

Back 487

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

Front 488

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

Back 488

Windows-based authentication or certificates.

Front 489

How do you specify windows-based authentication in Database mirroring and service broker endpoints?

Back 489

By selecting the NTLM, KERBEROS or NEGOTIATE option. The NEGOTIATE option causes the instances to select the authentication method dynamically.

Front 490

When should you use certificate-based authentication for endpoints?

Back 490

When instances span non-trusted domains.

Front 491

What is the default encryption algorithm used by SQL when encrypting communication between endpoints? What stronger algorithm can you specify?

Back 491

RC4

AES - more overhead, will affect performance

Front 492

How many TCP endpoints with a payload of DATABASE_MIRRORING can you specify for each instance?

Back 492

Only one.

Front 493

What third argument do Database mirroring endpoints offer?

Back 493

You can specify that an endpoint is a PARTNER, WITNESS or ALL.

PARTNER - can participate only as the principal or as the mirror.

WITNESS - can participate only as a witness

ALL - can function in either role

Front 494

What is the TSQL to create a database mirroring endpoint?

Back 494

CREATE ENDPOINT [Mirroring]

AS TCP (LISTENER_PORT = 5022)

FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED);

ALTER ENDPOINT [Mirroring] STATE = STARTED;

Front 495

What additional argument is available for Service-Broker endpoints?

Back 495

The MESSAGE_FORWARDING option, which enables messages destined for a different broker instance to be forwarded to a specified forwarding address. The options are ENABLED and DISABLED.

Front 496

If MESSAGE_FORWARDING in a service broker endpoint is set to ENABLED, what additional option can you apply?

Back 496

MESSAGE_FORWARDING_SIZE, which specifies the max amount of storage to allocate for forwarded messages.

Front 497

What does DMV stand for?

Back 497

Dynamic management view.

Front 498

If you want to be able to connect to a SQL Server instance remotely, what needs to be enabled?

Back 498

The Transmission Control Protocol/Internet Protocol (TCP/IP) network provider.

Front 499

What are the two SQL features that pose the greatest security risk?

Back 499

OPENROWSET/OPENDATARESOURCE and OLE Automation procedures.

Front 500

How do you enable and disable SQL Server features?

Back 500

By using sp_configure.

Front 501

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

Back 501

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 (XPs)

xp_cmdshell