70-432-Book1-Long

Front 1

Questions

Back 1

Answers

Front 2

Chapter 1: Lesson 1 -- Determining Hardware and Software Requirements

Back 2

xx

Front 3

(D) Windows Server 2008 Enterprise"

Back 3

"Correct Answers: C and D

Front 4

(D) Windows XP Tablet Edition SP2"

Back 4

"Correct Answer: B

Front 5

Exam Tip

Back 5

SQL Server 2008 Server 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 type, along with several additional features.

Front 6

What edition of Windows Server 2008 is not supported for SQL Server 2008 installations?

Back 6

Windows Server 2008 Server Core is not supported for SQL Server 2008 installations.

Front 7

Which operating systems are supporting for all editions of SQL Server?

Back 7

Windows Server 2003 Standard SP2 or higher, Windows Server 2008 Standard Rc0 or higher.

Front 8

Chapter 1: Lesson 2 -- Selecting SQL Server Editions

Back 8

Understand the differences between SQL Server 2008 Enterprise, Workgroup, Standard, and Express; Understand the role of each service that ships within the SQL Server 2008 data platform.

Front 9

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

Back 9

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 10

Quick Check: Which editions support the entire feature set available within the SQL Server data platform? Of these editions, which editions are not licensed for production use?

Back 10

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

Front 11

(D) Compact"

Back 11

"Correct Answers: B and C

Front 12

(D) SQL Server 2008 Enterprise"

Back 12

"Correct Answer: D

Front 13

Exam Tip

Back 13

For the exam, you need to understand the basic design goals for each edition of SQL Server. You also need to know the feature set, memory, and processor support differences between editions.

Front 14

What are the main Services in SQL Server 2008

Back 14

Service Broker, SSIS (Sql Server Integration Service), SSRS (SQL Server Reporting Services), SSAS (SQL Server Analysis Services)

Front 15

What editions of SQL Server are available?

Back 15

Enterprise, Standard, Workgroup, Express, Compact, Developer, Evaluation

Front 16

Chapter 1: Lesson 3: Installing and Configuring SQL Server Instances

Back 16

xx

Front 17

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

Back 17

You can configure SQL Server to operate under either Windows Only or Windows and SQL Server authentication modes.

Front 18

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

Back 18

Only SQL Server Enterprise supports multiple instances on the same machine.

Front 19

(D) sp_filestream_configure"

Back 19

"Correct Answer: D

Front 20

(D) SQL Server Surface Area Configuration Manager"

Back 20

"Correct Answer: C

Front 21

Exam Tip

Back 21

You will need to know how collation sequences affect the way SQL Server Stores and handles character data.

Front 22

NOTE: Windows Service Control Applet

Back 22

Applications can broadcast a special command, called an enumeration request, across a network to locate any servers running SQL Server that are on the network. Although being able to enumerate servers running SQL Server is valuable in development and testing environments where instances can appear, disappear, and be rebuilt on a relatively frequent basis, enumeration is not desirable in a production environment. 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 23

How many instances does SQL Server support?

Back 23

50 on a single machine

Front 24

What does the collation sequence do?

Back 24

The Collation Sequence controls how SQL Server stores and manages character-based data.

Front 25

Which versions of SQL Server support multiple instances?

Back 25

Enterprise, Developer.

Front 26

CAUTION: Windows Service Control Applet

Back 26

The Windows Service Control applet also has entries for SQL Server services and allows you to change service accounts and passwords. You should never change service accounts or service account passwords using the Windows Service Control applet. 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 27

Chapter 1: Lesson 4: Configuring Database Mail

Back 27

Database mail provide a notification capability to SQL Server instances. In this lesson, you learn about the features of Database Mail and how to configure Database Mail within a SQL Server 2008 instance.

Front 28

Quick Check: What are the two basic components of Database Mail?

Back 28

Database mail uses mail profiles which can contain one or more mail accounts.

Front 29

Quick Check: What are the two types of mail profiles that can be created?

Back 29

Mail profiles can be configured as either public or private.

Front 30

(D) Activity Monitor"

Back 30

"1. Correct Answers: B and C

Front 31

(D) Designate the mail profile as the default"

Back 31

"2. Correct Answers: C and D

Front 32

Lesson Summary:

Back 32

Database Mail is used to send mail messages from a SQL Server instance. To Send mail messages, SQL Server Agent must be running. A mail profile can contain one or more mail accounts. You can create either public or private mail profiles. The mail profile designated as the default profile will be used to send mail messages if a profile is not specified.

Front 33

TIP: Sending Mail

Back 33

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 34

Collation Sequence

Back 34

A combination of the character set that is supported and the settings for how to handle uppercase, lowercase, and accent marks.

Front 35

Data Mining

Back 35

An analytics engine that is part of SSAS which uses applied statistics to make predictions about data.

Front 36

Database Mail

Back 36

Allows mail messages to be sent from a SQL Server Instance

Front 37

Mail Profile

Back 37

A collection of one or more mail account that is used to send messages using Database Mail

Front 38

Case Scenario -- Page 33

Back 38

XX

Front 39

Chapter 2: Lesson 1: Configuring Files and Filegroups

Back 39

Data within a database is stored on disk in one or more data files. Prior to being written to the data files, every transaction is written to a transaction log file. In this lesson, you learn how to design the data files underneath a database, group the files into filegroups to link physical storage into a database, and manage the transaction log. You also learn how to configure the tempdb database for optimal performance.

Front 40

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

Back 40

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

Front 41

Quick Check: What is the purpose of the transaction long?

Back 41

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

Front 42

D. Grant select permission on the database to all users and revoke insert, update, and delete permissions from all users on the database."

Back 42

"1. Correct Answer: C

Front 43

Exam Tip

Back 43

The maximum transaction throughput for any database is bound by the amount of data per second that SQL Server can write to the transaction log.

Front 44

Lesson Summary:

Back 44

"You can define one or more data and log files for the physical storage of a database.

Front 45

Chapter 2: Lesson 2: Configuring Database Options

Back 45

Data within a database is stored on disk in one or more data files. Prior to being written to the data files, every transaction is written to a transaction log file. In this lesson, you learn how to design the data files underneath a database, group the files into filegroups to link physical storage into a database, and manage the transaction log.

Front 46

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

Back 46

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

Front 47

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

Back 47

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 interval when a minimally logged transaction is executing. All types of backups can be executed in the Full recovery model.

Front 48

D. Configure the database in the Full recovery model"

Back 48

"1. Correct Answer: D

Front 49

Lesson Summary:

Back 49

"You can set the recovery model for a database to Full, Bulk-Logged, or Simple.

Front 50

Recovery Options

Back 50

The recovery options determine the behavior of the transaction log and how damaged pages are handled.

Front 51

What are the three recovery models?

Back 51

Full, Bulk-Logged, and Simple..

Front 52

What is the best practice for production database recovery models?

Back 52

Every production database that accepts transactions should be set to the Full recovery model. By placing the database in the Full recovery model, you can maximize the restore options that are possible.

Front 53

What type of backups can you do in the Bulk Recovery Model?

Back 53

Full, Differential and Yes/No minimally logged.

Front 54

What type of backups can you do in the Full Recovery Model?

Back 54

Full, Differential and Transaction Log Backups

Front 55

What type of backups can you do in the Simple Recovery Model?

Back 55

Full, Differential and No transaction logs

Front 56

Chapter 2: Lesson 3: Maintaining Database Integrity

Back 56

xx

Front 57

Quick Check: What checks does DBCC CHECKDB perform?

Back 57

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 58

Quick Check: Which option should be enabled for all production databases?

Back 58

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

Front 59

D. DBCC FREEPROCCACHE"

Back 59

"Correct Answers: A and C

Front 60

Best Practices: Page Verification

Back 60

You should enable the PAGE_VERIFY CHECKSUM option on every production database.

Front 61

Note: Database Mirroring

Back 61

If the database is participating in Database Mirroring, SQL Server attempts to retrieve a copy of the page from the mirror. If the page can be retrieved from the mirror and has the correct page contents, the page is replaced automatically on the principal without requiring any intervention. When SQL Server replaces a corrupt page from the mirror, an entry is written into the sys.dm_db_mirroring_auto_page_repair view.

Front 62

Corrupt Page

Back 62

A page that has become inconsistent due to a failure of the disk subsystem while the page was being written.

Front 63

Filegroup

Back 63

A logical definition for one or more data files. A filegroup defines a storage boundary for objects.

Front 64

Recovery Model

Back 64

A database option that controls the types of backups that can be performed along with the restore options that are possible.

Front 65

Chapter 2 - Case Scenario page 57

Back 65

xx

Front 66

Chapter 3: Lesson 1: Creating Tables:

Back 66

xx

Front 67

Quick Check: How do you design a database?

Back 67

The ruling principle for designing a database is "Put thing where they belong." If the need is to store multiple rows of information that link back to a single entity, you need a separate table for those rows. Otherwise, each table defines a major object for which you want to store data and the columns within the table define the specific data that you want to store.

Front 68

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

Back 68

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

D. A VARBINARY(MAX) column with the FILESTREAM property"

Back 69

"Which options are not compatible with row or page compression? (Choose two. Each forms a separate answer.)

Front 70

Exam Tip:

Back 70

A FILEGROUP designated for FILESTREAM storage is off-line and inaccessible within a Database Snapshot. In addition, you cannot implement Database Mirroring against a database containing data stored with FILESTREAM.

Front 71

Lesson Summary:

Back 71

"* Schemas allow you to group related objects together as well as provide a security container for objects.

Front 72

Note: Creating Schemas

Back 72

The CREATE SCHEMA statement supports the creation of a schema along with the creation of tables and views and the assignment of permissions in a single statement. Creating code within SQL Server is not an obfuscation exercise, nor is it an exercise in trying to figure out the fewest statements you can construct to achieve your goals. Someone else usually has to maintain your code, and saving a couple of extra steps to create a more maintainable script is advisable. Therefore, it is recommended that you do not create tables and view or assign permissions within a CREATE SCHEMA statement. Any CREATE SCHEMA statement that is executed must be in a separate batch.

Front 73

Note: Numeric and Decimal Data Types

Back 73

The data types NUMERIC and DECIMAL are exactly equivalent. Both data types still exist within SQL Server for backwards compatibility purposes.

Front 74

Note: Numeric Precision

Back 74

FLOAT and REAL data types are classified as approximate numerics, or floating point numbers. The value stored within a float or real column depends upon the processor architecture that is used. Moving a database from a server with an Intel chipset to one with an AMD chipset, or vice versa, can produce different results in these columns. If you are utilizing FLOAT and REAL due to the range of values supported, you must account for compounding error factors in any calculation that you perform.

Front 75

Note: Row Size Limitation

Back 75

"If the maximum size of a row in your table exceeds 4,009 bytes, you cannot issue an ALTER statement to either change a column to SPARSE or add an additional SPARSE column. 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. The only workaround to this storage design issue are the following:

Front 76

Storage Space:"

Back 76

"Data Type: BIGINT

Front 77

Storage Space:"

Back 77

"Data Type: DECIMAL (P,S) and NUMERIC (P,S)

Front 78

Storage Space:"

Back 78

"Data Type: FLOAT(N)

Front 79

Storage Space:"

Back 79

"Data Type: INT

Front 80

Storage Space:"

Back 80

"Data Type: MONEY

Front 81

Storage Space:"

Back 81

"Data Type: REAL

Front 82

Storage Space:"

Back 82

"Data Type: SMALLINT

Front 83

Storage Space:"

Back 83

"Data Type: SMALLMONEY

Front 84

Storage Space:"

Back 84

"Data Type: TINYINT

Front 85

Chapter 3: Lesson 2: Implementing Constraints

Back 85

xx

Front 86

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

Back 86

A primary key does not allow NULLs.

Front 87

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

Back 87

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

Front 88

D. COLLATE"

Back 88

"Columns with which properties cannot be sparse columns? (Choose 2. Each forms a separate answer)

Front 89

Exam Tip:

Back 89

The default option for a primary key is clustered. When a clustered primary key is created on a table that is compressed, the compression option is applied to the primary key when the table is rebuilt.

Front 90

Exam tip:

Back 90

Although a NULL does not equal another NULL and NULLs cannot be compared, a unique constraint treats a NULL as it does any other data value. If the unique constraint is defined on a single column, then a single row within the table is allowed to have a NULL within that column, then a single row within the table is allowed to have a NULL within that column. If the unique constraint is defined across more than one column, then you can store NULLs within the columns so long as you do not produce a duplicate across the combination of NULLs and actual data values.

Front 91

Lesson Summary:

Back 91

"* A primary key defines the column(s) that uniquely identify each row in a table.

Front 92

Check Constraint

Back 92

Limits the range of possible values that are allowed in a column.

Front 93

Default Constraint

Back 93

Supplies a value for a column when the application does not specify the column for an INSERT.

Front 94

FILESTREAM

Back 94

A property that enables SQL Server to store files in a specified directory on the operating system while still treating files as part of the database for management, backup and restore purposes.

Front 95

Foreign Key

Back 95

Enforces referential integrity between two tables.

Front 96

Identity Column

Back 96

A column with the IDENTITY property that automatically supplies an incrementing value when rows are inserted into the table.

Front 97

Primary Key

Back 97

Designates the column(s) within a table that uniquely identify every row. A primary key does not allow NULLs

Front 98

Schema

Back 98

An object within a database that every object is owned by. An object cannot be created outside a schema.

Front 99

Schema Collection

Back 99

One or more XML schemas that are loaded into SQL Server as a group. XML schemas are used to validate the contents of XML columns.

Front 100

Sparse Column

Back 100

A property applied to a column that allows NULLs which optimizes storage by not requiring any space for the NULLs stored within the column.

Front 101

Unique constraint

Back 101

Designates the column(s) whose values are required to be unique within the table. A unique index differs slightly from a primary key because columns for a unique index allow NULLs.

Front 102

Caution: Cascading

Back 102

One of the options for a foreign key is CASCADE. You can configure a foreign key such that modifications of the parent table are cascaded to the child table. For example, when you delete a customer, SQL Server also deletes all the customer's associated orders. Cascading is an extremely bad idea. It is very common to have foreign keys defined between all the tables within a database. If you were to issue a DELETE statement without a WHERE clause against the wrong table, you could eliminate every row, in every table within your database, very quickly. By leaving the CASCADE option off for a foreign key, if you attempt to delete a parent row that is referenced, you get an error.

Front 103

Chapter 4: Lesson 1: Index Architecture

Back 103

xx

Front 104

Quick Check: What are the three types of pages within an index?

Back 104

An index can contain root, intermediate and 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 105

Quick Check: What type of structure does SQL Server use to construct an index?

Back 105

SQL Server uses a B-Tree structure for indexes.

Front 106

D. CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName, SKU, ProductID, ListPrice, ShortDescription)"

Back 106

"Correct Answers: B and C

Front 107

Exam tip:

Back 107

If you are creating an index on a sparse column, you should use a filtered index to create the most compact and efficient index possible.

Front 108

Lesson Summary:

Back 108

"* SQL Server creates an index using a B-Tree structure.

Front 109

Note: Index Entry Storage

Back 109

Pages in SQL Server can store up to 8,060 bytes of data. So an index created on a column with an INT data type can store 2,015 values on a single page within an index, whereas an index based on a column with a datetime2 data type can store only about half as many values per page, or 1,007 values per page.

Front 110

Note: Page Splits

Back 110

Keep in mind that rows on an index page are maintained in sorted order, so SQL Server always writes any new entries into the correct sorted location when page splitting. This can cause rows to move between pages, and page splits can occur at any level within the storage structure.

Front 111

Chapter 4: Lesson 2: Designing Indexes

Back 111

xx

Front 112

Quick Check: How does the FILLFACTOR option affect the way an index is built?

Back 112

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

Front 113

Quick Check: What is the difference between and clustered and non clustered index?

Back 113

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

Front 114

D. A filtered, nonclustered index on Color that includes the ProductType and WoodSpecies columns"

Back 114

"1. Correct Answer: D

Front 115

Exam Tip. Online Indexing/Rebuild/Restore

Back 115

Online operations such as online index creation / rebuild or online restore are available only in SQL Server 2008 enterprise.

Front 116

Lesson Summary

Back 116

"* Clustered indexes specify a sort order for data pages in a table.

Front 117

Note: FILESTREAM Data

Back 117

The FILESTREAM_ON clause is used when clustered indexes are created on a table containing FILESTREAM data. If you specify a different filegroup in the FILESTREAM_ON clause than where the FILESTREAM data is currently located, all the FILESTREAM data will be moved to the newly specified filegroup during the creation of the clustered index.

Front 118

Because XML columns contain such large amounts of data -- how can you improve performance of queries again XML Data?

Back 118

You can create a special type of index called an XML Index.

Front 119

How are non-clustered indexes different from clustered indexes?

Back 119

They do no impose a sort order on the table. Because of this (unlike clustered indexes), you can create multiple non-clustered indexes on the same table.

Front 120

How can you create covering indexes for greater than 16 columns and greater than 900 bytes?

Back 120

Indexes can be created using the INCLUDE clause that exceed those limitations.

Front 121

How do filtered indexes work?

Back 121

Only the index keys matching the where clause are added to the index.

Front 122

How do Indexes created using the INCLUDE clause get around the Index size limitations?

Back 122

Included columns become part of the index at only the leaf level. Values from included columns do not appear in the root or intermediate levels of an index and do not count against the 900 byte limit for an index.

Front 123

How does a clustered index determine the physical order of data on a disk?

Back 123

Trick Question: It does not -- it just ensures that the page chain of the index is sorted logically -- allowing SQL Server to traverse directly down the page chain to locate data.

Front 124

How does FILLFACTOR apply to the Root and Intermediate level pages of an index?

Back 124

It doesn't. It only applies at the Leaf level of an index.

Front 125

How does pointers improve the performance of a table?

Back 125

Trick Question: They don't. Although the presence of a handful of forwarding pointers for a table is generally not a concern, having a large number of forwarding pointers can cause severe performance degradation.

Front 126

How is a clustered index special?

Back 126

A clustering index is special because it causes SQL server to arrange the data in the table according to the clustering key.

Front 127

How is a filtered index different from a regular index?

Back 127

It uses a WHERE clause.

Front 128

How many clustered indexes can you have per table?

Back 128

Only one, because a table can only be sorted one way, and a clustering index imposes a sort order on a table.

Front 129

How much data can be contained in a single column using the XML Data Type?

Back 129

2 GB of Data.

Front 130

How much space does SQL Server reserve at the Root and Intermediate levels of an index?

Back 130

It only ever reserves enough space for approximately one additional row to be added.

Front 131

If you don't want to consume space in the database where an index is created, what can you do to prevent this?

Back 131

You can use the SORT_IN_TEMPDB option. Which causes the work tables for the sort operations to be generated in the tempdb database.

Front 132

If you know you're going to be introducing large numbers of leaf-level pages, which in turn will cause page splits on the intermediate levels and potentially the root page -- what command can you use to improve performance?

Back 132

You can use the PAD_INDEX option, which causes the FILLFACTOR to be applied to the intermediate level pages and the root page of an index.

Front 133

The leaf level of a clustered index contains the data -- what does the leaf level of non-cluster index contain?

Back 133

It contains a pointer to the data.

Front 134

What are the columns called that a defined for a clustering index?

Back 134

Clustering key.

Front 135

What are the maximum columns and byte size for a non-clustered index?

Back 135

The same as the limits for clustered indexes, max of 16 columns and 900 bytes.

Front 136

What are the other types of indexes -- besides clustered indexes?

Back 136

The only other type of index is a non-clustered index.

Front 137

What are the restrictions of a filtered index?

Back 137

"1. The must be nonclustered indexes

Front 138

What are the two types of XML indexes?

Back 138

Primary and secondary.

Front 139

What causes the selectivity of an index to increase?

Back 139

An increased number of unique values.

Front 140

What command can you use during a multi-row insert operation to ensure that duplicate values don't cause the entire insert to be rejected (just the offending row)?

Back 140

IGNORE_DUP_KEY option has to be enabled. A warning will be generated, but only those rows that are duplicates will be rejected.

Front 141

What command can you use during the creation of an index to lock the entire table to prevent changes while the index is created?

Back 141

WITH ONLINE = OFF

Front 142

What do you call a table without a clustered index/

Back 142

A table without a clustered index is referred to as a heap.

Front 143

What does FILLFACTOR specify?

Back 143

It specifies the percentage of free space that should be left on the leaf level of an index during creation or rebuild. By leaving space on the leaf level, you can write a small number of rows to a leaf-level page before a page split is required, thereby slowing the rate of fragmentation of an index.

Front 144

What does the FILLFACTOR do to improve database performance?

Back 144

It controls the rate at which page splits occur.

Front 145

What does the ON clause specify in the creation of an index?

Back 145

It specifies the filegroup that the index is created on. However, because the leaf level of a clustered index is the row of data in the table, the table and the clustered index are always stored on the same filegroup.

Front 146

What is special about covering indexes?

Back 146

They can be used to completely satisfy all of the queries for a given tables -- as they contain all of the data for an entire table.

Front 147

What is the component used by SQL Server to determine whether an index should even be used to satisfy a query?

Back 147

Query optimizer.

Front 148

What is the default option in SQL Server for a Primary key?

Back 148

Clustered. Unless you specify otherwise, SQL Server creates a clustered index for a primary key. Likewise, a unique constraint is physically implemented as a unique index. Because a primary key is also unique by default unless it is specified as nonclustered, SQL Server physically implements each primary key as a unique, clustered index.

Front 149

what is the different in table locks between the creation of a clustered vs. non-clustered index?

Back 149

When a table is locked during the creation of a clustered index, it locks the table from both select statements and data modifications. When a table is locked for a non-clustered index, you are allowed selected statements, but you cannot modify the data.

Front 150

What is the main purpose of filtered indexes?

Back 150

To handle the cases where significant skew exists in the data.

Front 151

What is the maximum number of columns that you can define an index with?

Back 151

16 Columns

Front 152

What is the maximum number of non-clustered indexes on a table?

Back 152

1,000 non-clustered indexes per table.

Front 153

What is the maximum size of the index key?

Back 153

900 bytes

Front 154

What is the most important option that can be specified during the creation of an index?

Back 154

FILLFACTOR

Front 155

What is the name given to an index that contains all of the columns of data in a table?

Back 155

Covering index

Front 156

What is the name given to the degree to which values in the column allow you to locate small sets of data?

Back 156

The selectivity of an index.

Front 157

What is the name given to the structure created when an index is created that stores information about the relative distribution of data values within a column?

Back 157

Histogram

Front 158

What is the ONLINE default setting during index creation?

Back 158

Off

Front 159

Which versions of SQL Server support online index creation?

Back 159

Enterprise.

Front 160

Why should you sometimes use covering indexes?

Back 160

If you can construct covering indexes for frequently accessed data, you can increase the response time for queries by avoiding additional reads to the underlying tables.

Front 161

T/F -- Certain tables should never have clustering indexes.

Back 161

False -- Every table should have a clustered index. One of the main purposes of a clustered index is to eliminate forwarding pointers.

Front 162

Chapter 4: Lesson 3: Maintaining Indexes

Back 162

xx

Front 163

Quick Check: What happens when an index is disabled?

Back 163

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 164

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

Back 164

REBUILD defragments all levels of an index. REORGANIZE defragments only the leaf level of the index.

Front 165

D. Use Integration Services to import the data"

Back 165

"Correct Answer: C

Front 166

Lesson Summary:

Back 166

"* You can defragment indexes using either the REBUILD or REORGANIZE options.

Front 167

Balanced Tree (B-Tree)

Back 167

A symmetric, linear structure used to construct an index. A B-Tree provides a compact structure that enables searching very large volumes of data with a small number of read operations.

Front 168

Clustered Index

Back 168

An index that imposes a sort order on the pages within the index. A table can have only one clustered index.

Front 169

Covering Index

Back 169

An index that allows a query to be satisfied by using only the entries within the index.

Front 170

Nonclustered Index

Back 170

An index that does not impose a sort order on the data pages within the table. You can have up to 1,000 nonclustered indexes in a table.

Front 171

Tesselation

Back 171

The process that is used to construct a spatial index. Tessellation counts the cells that a spatial object touches within the four-level grid hierarchy.

Front 172

How do you defragment an index in SQL Server?

Back 172

you use the ALTER INDEX command.

Front 173

How does the REBUILD option of the ALTER INDEX command work?

Back 173

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 recreates 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 174

How does the REORGANIZE option of the ALTER INDEX command work?

Back 174

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

Front 175

What command do you use to enable an index that has been previously disabled?

Back 175

ALTER INDEX REBUILD

Front 176

What does the FILLFACTOR option for an index do?

Back 176

It determines the percentage of free space that is reserved on each leaf level page of the index when an index is created or rebuilt. The extra free space reduces the rate at which page split occurs.

Front 177

What does the FILLFACTOR represent?

Back 177

It represent the percentage full. IE, Fill factor of 75 means that 25 percentage of the space on each leaf-level page is left empty to accommodate future values.

Front 178

What happens to a table when a clustered index is disabled?

Back 178

The entire table becomes inaccessible.

Front 179

What is the automatic mechanism that SQL server uses to reduce page and index fragmentation?

Back 179

Trick question -- none. Because SQL Server does not reclaim space, you must periodically reclaim the empty space in an index to preserve the performance benefits of an index.

Front 180

When defragmenting an index using the ALTER INDEX command, what are your options?

Back 180

REBUILD and REORGANIZE.

Front 181

Why do you need to manage the fragmentation of an index over time?

Back 181

Because the data within an index is stored in a sorted order -- which can move around (causing page splits) or from changes in values.

Front 182

Chapter 5: Lesson 1: Creating and Populating Full Text Indexes

Back 182

xx

Front 183

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

Back 183

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 184

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

Back 184

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

Front 185

D. Create a full text index on the table of product descriptions for the description column and specify CHANGE_TRACKING AUTO."

Back 185

"1. Correct Answers: B and D

Front 186

D. ALTER FULLTEXT INDEX ON <table_name> START UPDATE POPULATION."

Back 186

"Correction Answer C:

Front 187

Exam Tip: Full Text Indexes

Back 187

Prior to SQL Server 2008, you associated the full text catalog with a filegroup only for backup purposes, with the entire contents of the catalog maintained in a directory structure on the operating system. In SQL Server 2008, Microsoft eliminated the external file structure, and the contents of a full text catalog are now stored within the database.

Front 188

Lesson Summary:

Back 188

"* Before creating a full text index, you must create a full text catalog that is mapped to a filegroup to contain one or more full text indexes.

Front 189

Note: Filegroup Placement

Back 189

Although it is possible to store a full text catalog on a filegroup that also contains relational data, it is recommended that you create a separate filegroup for full text indexes to separate the input/output (I/O) against the full text catalog from that of the relational data.

Front 190

In SQL Server 2008, why is the IN PATH clause no longer used with regards to Full-Text indexes?

Back 190

Because the IN PATH clause has been deprecated and should no longer be used because full-text indexes are now stored within the database.

Front 191

Point of differentiation -- Full-text index vs. Relational Index

Back 191

Unlike a relational index, population of a full text index is not an immediate process because the data has to be submitted to the indexing engine, when then applies word breakers, stemmers, language files and stop lists before merging the changes into the index.

Front 192

What are the options in SQL Server for maintaining a list of changes to the indexed data for the change tracking feature of full-text indexing?

Back 192

MANUAL or AUTO

Front 193

What data column types can have full-text indexes created for them?

Back 193

CHAR/VARCHAR, XML and VARBINARY

Front 194

What is a full-text index?

Back 194

It is an index that gives you the ability to query large volumes of unstructured data rapidly.

Front 195

What is the first step in building a full-text index?

Back 195

You create a storage structure. Full text indexes have a unique internal structure that is maintained within a separate storage format.

Front 196

What is the impact of setting CHANGE_TRACKING to OFF in the full-text index engine of SQL Server?

Back 196

Whenever you want to update the index to the reflect the changes in the underlying data, you have to repopulate the index completely.

Front 197

What is the impact of setting the change tracking option to AUTO in the full-text index engine?

Back 197

The SQL Server automatically updates the full text index as the data is modified.

Front 198

What is the impact of setting the change tracking option to MANUAL in the full-text index engine?

Back 198

It means that you are responsible for periodically propagating the changes into the full text index?

Front 199

What is the most common use of the VARBINARY(MAX) column?

Back 199

To store documents using the new FILESTREAM capabilities in SQL Server 2008.

Front 200

What is the name given to the storage format that supports full-text indexes?

Back 200

full text catalog. Each full-text catalog contains one or more full-text indexes.

Front 201

What option is available to the full text index that determines how SQL Server maintains the index when the underlying data changes?

Back 201

CHANGE_TRACKING

Front 202

What parameter is the single column within the table or indexed view that uniquely identifies a row?

Back 202

The KEY INDEX

Front 203

What parameter is used to help you designate the column that contains the filter type for the full text index engine?

Back 203

The TYPE COLUMN

Front 204

What parameter is used to help you designate the language of the data being indexed by the full text index engine?

Back 204

LANGUAGE

Front 205

When creating full-text indexes on which data column types can the full-text engine parse the data directly and build an appropriate index?

Back 205

CHAR/VARCHAR

Front 206

When processing a VARBINARY(MAX) column, what is the other critical thing you have to do?

Back 206

You have to specify a column that designates the type of document so that the full text parser can the appropriate assembly. This allows the full text indexer to appropriately read the document and make the content available to the full-text index.

Front 207

Quick Check: Which predicate is used to perform proximity and synonym searches?

Back 207

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

Front 208

Quick Check: Which predicate performs fuzzy searching by default?

Back 208

FREETEXT and FREETEXTTABLE predicates perform wildcard searches by default.

Front 209

D. FREETEXTTABLE"

Back 209

"1. Correct Answers: A and C

Front 210

D. ISABOUT with a WEIGHT keyword"

Back 210

"2. Correct Answer: D

Front 211

Exam Tip:

Back 211

All search terms used with full text are unicode strings. 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. Make certain that all terms you pas in for full text search are always typed as Unicode for maximum performance.

Front 212

Lesson Summary: Querying Full-Text Data

Back 212

"* The FREETEXT and CONTAINS predicates return a value of True or False, which you can then use in a query similar to an EXISTS clause to restrict a result set.

Front 213

Note: Language Parameters

Back 213

Although you can employ a different language for a query than an index was built upon, you will not automatically improve the accuracy of a full text search. Because stemmers are language-specific, if the index was built with a language different from the language specified in the query, you will not be able to find any inflectional forms of words with your query.

Front 214

Note: Thesaurus Files

Back 214

A thesaurus file exists for each supported language. All thesaurus files are XML files stored in the FTDATA directory underneath your default SQL Server installation path. The thesaurus files are not populated, so to perform synonym searches, you need to populate the thesaurus files. You will learn about thesaurus files in Lesson 3. "Managing Full Text Indexes"

Front 215

Chapter 5: Lesson 2: Querying Full Text Data

Back 215

xx

Front 216

For queries of full-text data that require greater flexibility, which predicate should you use?

Back 216

CONTAINS

Front 217

In addition to CONTAINS and FREETEXT, what are the two additional commands that produce a result set with addition columns of information?

Back 217

CONTAINSTABLE and FREETEXTTABLE

Front 218

In full text index searches -- what keyword is used to do proximity searching?

Back 218

NEAR

Front 219

What are the two commands that SQL Server provides to query full text data?

Back 219

CONTAINS and FREETEXT

Front 220

Why?

Back 220

Because the CONTAINS predicate allows you to (1) Search Word Forms (2) Search for Word proximity and (3) Provide relative weighting to terms

Front 221

Quick Check: What do you configure to exclude words from your index and search arguments?

Back 221

A stop list contains the list of words that you want excluded from a full text index as well as from search arguments.

Front 222

Quick Check: Which type of files enable searching based on synonyms?

Back 222

A thesaurus file allows you to configure synonyms for search arguments

Front 223

D. Parse the inbound query and remove any common words from the search arguments."

Back 223

"1. Correct Answer: A

Front 224

Exam Tip: Stop Lists

Back 224

Although many features of SQL Server operate the same from one version to another, others are enhanced or changed. You can assume that you will have questions on an exam which are designed to test whether you know the change in behavior for the new version. In SQL Server 2005 and prior versions, you configured noise word files that were in the FTDATA directory. In SQL Server 2008, you configure stop lists that are contained within a database in SQL Server. it is very likely that if you have a question concerning the configuration of stop words, the available answers will include both the SQL Server 2005 and SQL Server 2008 behaviors and any of the SQL Server 2005 behaviors would be incorrect answers.

Front 225

Lesson Summary: Managing Full Text indexes

Back 225

"* You manage thesaurus files by editing the language-specific file that is contained within the FTDATA directory for the instance.

Front 226

Full Text Catalog

Back 226

The storage container for full text indexes.

Front 227

Full Text Filter

Back 227

An assembly that is loaded by the full text engine and that allows interpretation of various document formats stored within a VARBINARY (MAX) column.

Front 228

Full Text Index

Back 228

A specialized index for unstructured data. Full text indexes allow unstructured data to be queried across word forms, as well as with respect to work proximity with the data.

Front 229

Stemmer

Back 229

An assembly loaded by the full text indexing engine that produces language specific inflectional forms for verbs within the data being indexed.

Front 230

Stop List

Back 230

A list of words that the full text engine should ignore when the index is populated. A stop list allows you to filter out common terms within your industry or organization so that the index does not become polluted with words you do not want to search for.

Front 231

Thesaurus File

Back 231

An XML file that is created for a specific language for use with a full text index. Thesaurus files allow you to return synonyms of search terms, such as when you search for metal and the index also returns gold, silver, copper, aluminum, etc.

Front 232

Word Breaker

Back 232

A language specific assembly that locates word breaks so that individual words can be tokenized by the full text indexing engine.

Front 233

A thesaurus can contain expansion sets or replacement sets. What is the difference of the two?

Back 233

A replacement set defines a term or set of terms that are replaced in 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 Serve to retrieve a row.

Front 234

Chapter 5: Lesson 3: Managing Full Text Indexes

Back 234

xx

Front 235

What are Stop Lists?

Back 235

They are used by SQL Server to exclude words that you do not want included in a full text index. You exclude words from an index that commonly occur so that valid, targeted results can be returned to validly formed searches.

Front 236

What are the three options for populating a full-text index?

Back 236

Full, Incremental and Update.

Front 237

What are two ways to easily increase the usefulness of a full-text index?

Back 237

Creating thesaurus files and building stop lists to filter out of the index?

Front 238

What command would you issue to initiate the population of a full-text index?

Back 238

ALTER FULLTEXT INDEX

Front 239

What directory is the thesaurus stored in?

Back 239

FTDATA directory

Front 240

What do you have to do in SQL Server before it can begin using language-specific searches that automatically searches for synonyms?

Back 240

You have to define the Thesaurus in SQL Server.

Front 241

What does a thesaurus file enable you to do with full text queries?

Back 241

it allows you to retrieve rows that match the search argument along with synonyms of a search argument.

Front 242

What happens in a full-text index when you use the FULL option?

Back 242

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

Front 243

What happens in a full-text index when you use the INCREMENTAL option?

Back 243

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

Front 244

What happens in a full-text index when you use the UPDATE option?

Back 244

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 245

What type of file is the thesaurus file?

Back 245

It is an XML file

Front 246

What were Stop Lists known as in previous versions of SQL Server?

Back 246

Noise word files.

Front 247

Chapter 6: Lesson 1: Creating a Partition Function

Back 247

xx

Front 248

Quick Check: What data types cannot be used with partition functions?

Back 248

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

Front 249

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

Back 249

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

Front 250

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

Back 250

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

Front 251

D. Database Mirroring"

Back 251

"1. Correct Answer: C

Front 252

Exam Tip: Creating Partition Functions

Back 252

You can partition an existing object after it has been populated with data. 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 recreate 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 253

Lesson Summary: Creating A Partition Function:

Back 253

"* A partition function defines the boundary points for a set of partitions.

Front 254

Note: Code Reuse

Back 254

The definition of a partition function does not provide a clause for an object, column or storage. This means that a partition function is a stand-alone object that you can apply to multiple tables, indexes, or indexed views if you choose.

Front 255

In a partition function how many partitions can you have for an object?

Back 255

1,000 partitions for an object.

Front 256

In a partition function what is the maximum number of boundary points?

Back 256

999 boundary points.

Front 257

In a partition function, where are null values stored?

Back 257

In the left most partition.

Front 258

In the creation of a partition function, how to you insure that any value stored in a column always evaluates to a single partition?

Back 258

You have the partition function map the entire range of data -- with no gaps present. And you make sure than you do not specify duplicate boundary points.

Front 259

In the creation of a partition function, what do you use to define which partition will include a boundary point?

Back 259

The LEFT and RIGHT parameters.

Front 260

What are the two things that every partition function requires?

Back 260

Name and Data Type.

Front 261

What data types can be used fro a partition function?

Back 261

Any native SQL Server data type, except text, ntext, image, varbinary(max), timestamp, xml, and varchar(max).

Front 262

What do you use in the creation of partition functions to specify whether the partition function is RANGE LEFT for RANGE RIGHT?

Back 262

The AS clause.

Front 263

What does a Partition Function define?

Back 263

It defines a set of boundary points for which data will be partitioned.

Front 264

What does Table Partitioning allow for?

Back 264

It allows tables, indexes and indexed views to be created on multiple filegroups while also allowing DBA's to specify which portion of the object will be stored on a specific filegroup.

Front 265

What does the data type do in a partition function?

Back 265

It defines the limits of the boundary points that can be applied.

Front 266

What does the FOR VALUES clause do in a partition function?

Back 266

It is used to specify the boundary points for the partition function.

Front 267

What is Table Partitioning?

Back 267

Table partitioning is a means of splitting large tables across multiple storage structures.

Front 268

What is the three step process for partitioning a table?

Back 268

"1. Create a partition function

Front 269

What must you do to imprecise data types such as real and computer columns to use them in the creation of partition functions?

Back 269

They must be persisted.

Front 270

T/F -- You can use Common Language Runtime (CLR) data types in the creation of Partition Functions?

Back 270

False. You may not.

Front 271

T/F -- You cannot use Transact-SQL user defined types in the creation of Partition Functions?

Back 271

True. You cannot use them.

Front 272

Chapter 6: Lesson 2: Creating a Partition Scheme

Back 272

xx

Front 273

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

Back 273

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

Front 274

Quick Check: How many filegroups can you specify if you use the ALL keyword when defining a partition scheme?

Back 274

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

Front 275

D. Create a job to delete orders that are older than 30 days."

Back 275

"1. Correct Answers: A and C

Front 276

Exam Tip:

Back 276

If you specify the ALL Keyword when creating a partition scheme, you can specify a maximum of one filegroup.

Front 277

Lesson Summary: Create a Partition Scheme

Back 277

"* A partition scheme is a storage definition containing a collection of filegroups.

Front 278

How must a partition scheme be defined?

Back 278

It must be defined in such a way as to contain a filegroup for each partition that is created by the partition function mapped to the partition scheme.

Front 279

What is a Partition Scheme?

Back 279

It defines the storage structures and collection of filegroups that you want to use with a given partition function.

Front 280

Important Filegroups

Back 280

Any filegroup specified in the CREATE PARTITION SCHEME must already exist in the database.

Front 281

Chapter 6: Lesson 3: Creating Partitioned Tables and Indexes

Back 281

xx

Front 282

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

Back 282

A computed column must be PERSISTED

Front 283

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

Back 283

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

Front 284

D. Database snapshots"

Back 284

"1. Correct Answer: B

Front 285

Lesson Summary: Creating Partitioned Tables and Indexes

Back 285

"* The ON clause is used to specify the storage structure, filegroup, or partition scheme to store a table or index.

Front 286

Note: Partial Backup and Restore

Back 286

Partitioning has an interesting management effect on your tables and indexes. Based on the definition of the partition function and partition scheme, it is possible to determine the set of rows which are contained in a given filegroup. By using this information, it is possible to back up and restore a portion of a table as well as to manipulate the data in a portion of a table without affecting any other part of the table.

Front 287

What is the final step in creating a partitioned table or index?

Back 287

The final step in creating a partitioned table or index is to create the table or index on a partition scheme through a partitioning column.

Front 288

Whenever you're creating a Partitioned table or index -- what is the difference that when creating a regular table or index?

Back 288

You include the ON clause, and specify the Partition Scheme -- as opposed to the FILEGROUP you would normally specify there during the creation of regular tables or indexes.

Front 289

Chapter 6: Lesson 4: Managing Partitions

Back 289

xx

Front 290

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

Back 290

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

Front 291

Quick Check: Which operators is used to move partitions between tables?

Back 291

The SWITCH operator is used to move partitions between tables.

Front 292

D. Partitioning"

Back 292

"1. Correct Answer: D

Front 293

Exam Tip - Distributing and partitioning data

Back 293

You need to know that in a SWITCH operation, you cannot move data from on filegroup to another or exchange two partitions with data.

Front 294

Lesson Summary:

Back 294

"* SPLIT is used to introduce a new boundary point to a partition function.

Front 295

Index Alignment

Back 295

An index that utilizes the same partition function as the table against which it is created.

Front 296

Partition function

Back 296

A definition of boundary points that is used to determine how data in a partitioned table is split.

Front 297

Partition scheme

Back 297

A definition of storage that is mapped to a partition function that determines where each portion of data will be stored.

Front 298

Partitioning key

Back 298

The column in the table that is used to partition.

Front 299

How can you ensure that data and corresponding indexes are kept together as a single unit during a restore?

Back 299

Keep them on the same disk partition.

Front 300

What are the two operators available to manage the boundary point definitions of Partitioned Indexes and Partitioned Tables?

Back 300

SPLIT and MERGE

Front 301

What does "aligned" mean with regards to clustered indexes and tables?

Back 301

It means that a table and all of its indexes are partitioned using the same partition functions.

Front 302

What does the MERGE operator do?

Back 302

The MERGE operator eliminates a boundary point from a partition function.

Front 303

What does the SPLIT operator do?

Back 303

The SPLIT operator introduces a new boundary point into a partition function.

Front 304

Why must you partition the clustered index and the table the same way?

Back 304

Because SQL Server cannot store the clustered index in a structure separate from the table.

Front 305

Chapter 7: Lesson 1: Importing and Exporting Data

Back 305

xx

Front 306

Quick Check: The import and Export Wizard is based on which feature of SQL Server?

Back 306

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

Front 307

Quick Check: What are the data formats that BCP supports and the command line switches for each format?

Back 307

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 308

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

Back 308

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

Front 309

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

Back 309

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

Front 310

D. A member of the bulkadmin role"

Back 310

"Correct Answers: A and C

Front 311

D. Move the data from Oracle to SQL Server using the OPENROWSET function."

Back 311

"2. Correct Answer: C

Front 312

Exam Tip: Importing and Exporting Data.

Back 312

Exam Tip: The exam tests you on whether you know which import/export option is most appropriate to a given situation.

Front 313

Lesson Summary: Importing and Exporting Data

Back 313

"* BCP is a lightweight, command-line utility that allows you to import and export data.

Front 314

Note: Enforcing Check Constraints and Triggers

Back 314

When you import data into a table using BCP, triggers and check constraints are disabled by default. If you want to enforce check constraints and fire triggers during the import, you need to use the -h switch. If you do not disable triggers and check constraints during an import, you do no need ALTER TABLE permissions.

Front 315

Bulk Copy Program

Back 315

A command line utility that is used to import data from a file into a table, as well as export data to a file.

Front 316

BULK INSERT

Back 316

A T-SQL command that allows you to import data from a file into a table view.

Front 317

What are the two main differences between the BCP program and the BULK INSERT command?

Back 317

the BULK INSERT command cannot export data and the BULK INSERT is a T-SQL Command and does not need to specify the instance name or login credentials.

Front 318

What does BCP stand for?

Back 318

The Bulk Copy Program

Front 319

What does the -c switch used with BCP specify?

Back 319

It specifies that the data in the file is in a character format.

Front 320

What does the -n switch used with BCP specify?

Back 320

It specifies that the data in the file is in the native format of SQL Server.

Front 321

What does the -T switch do when used with BCP?

Back 321

it specifies a trusted connection and BCP uses the Windows credentials of the account that is executing the BCP command to connect.

Front 322

What does the -U and -P switches do when used with BCP?

Back 322

It allows you to use a SQL Server login, the -U specifies the login name and the -P specifies the password to use.

Front 323

What is one of the biggest drawbacks of the BCP program?

Back 323

That it is a command-line program.

Front 324

What is the most efficient way to import well-defined data in files into SQL Server as well as export tables to a file?

Back 324

The BCP or Bulk Copy Program.

Front 325

What permissions does the account running BCP need if you're exporting data?

Back 325

SELECT permission.

Front 326

What permissions does the account running BCP need if you're importing data?

Back 326

SELECT, INSERT, and ALTER TABLE permissions.

Front 327

What should you use to import data into SQL Server if you need to perform transformations or do error-handling routines during the IO process?

Back 327

SSIS -- SQL Server Integration Services

Front 328

Why do you have to specify connection information for BCP to use?

Back 328

Because it is an application, it does not have database or instance context.

Front 329

Caution: Case Sensitivity

Back 329

All command-line switches for BCP are case sensitive. For example you use -e to specify an error file, yet -E tells BCP to preserve identity values during an import.

Front 330

T/F BCP has been designed over the years to work well for importing data that needs to perform error handling during the import process?

Back 330

FALSE

Front 331

Chapter 8: Lesson 1: Designing Policies

Back 331

xx

Front 332

Quick Check: How many conditions can be checked within a single policy?

Back 332

A policy can check only a single condition.

Front 333

Quick Check: How many facets can be checked within a single condition?

Back 333

A condition can be defined with only one facet.

Front 334

Quick Check: What are the allowed execution modes for a policy?

Back 334

On demand, on schedule, on change - log only, on change - prevent.

Front 335

Quick Check: What are the five objects that are used within Policy Based management?

Back 335

The objects that are used with PBM are facets, conditions, policies, policy targets and policy categories.

Front 336

Quick Check: which object has a property that allows you to mandate checking for all databases on an instance?

Back 336

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

Front 337

D. Check the policies manually against the instance."

Back 337

"1. Correct Answer: C

Front 338

Exam tip>

Back 338

Defining a condition to be used as a policy target is a critical component to well-defined policies. A policy fails during a check if the object does not conform to the criteria and if the property does not exist. For example, attempting to check that the Web Assistant is disabled against a SQL Server 2008 instance fails because the feature does not exist.

Front 339

Lesson Summary:

Back 339

"* You can build policies to enforce conditions across any version of SQL Server

Front 340

Condition

Back 340

The criteria that you want to check for a specified facet.

Front 341

Facet

Back 341

The .NET assemblies that define the behavior and properties available to be checked by a condition.

Front 342

Policy category

Back 342

A container to with one or more policies can be associated.

Front 343

Policy target

Back 343

The instance, database, schema, or object that you want to be checked by a policy.

Front 344

Because you cannot set all policies to enforce compliance, what do you do?

Back 344

You need to check policies manually that cannot be enforce on a regular basis.

Front 345

How do you check or enforce policies?

Back 345

To check or enforce policies, you create a subscription to one or more policies.

Front 346

How do you define a Condition in PMB?

Back 346

You define conditions that you want to check or enforce for a policy by defining criteria for the properties of a facet.

Front 347

If you define a condition using the advanced editor -- how can you execute it?

Back 347

It must be executed manually, a policy that incorporates conditions created in the advanced editor cannot be scheduled.

Front 348

If you don't want to duplicate policies on every instance, create subscriptions to each instance in your environment individually or check compliance for each instance individually -- what are you options?

Back 348

You can configure a Central Management Server insides of SSMS -- and use it to evaluate policies against instances or all instances in the CMS.

Front 349

In designing policies for SQL Server, what are Policy Categories?

Back 349

Policy Categories can be used to group one or more policies into a single compliance unit.

Front 350

In SQL Server 2008, what is another phrase for Policy Based Management?

Back 350

DMF -- Declarative Management Framework.

Front 351

What are policies?

Back 351

Policies are created for a single condition and set to either enforce or check compliance.

Front 352

What are the comparison operators that can be used for properties of type Boolean?

Back 352

<> or =

Front 353

What are the comparison operators that can be used for properties of type string?

Back 353

=, <>, LIKE, NOT LIKE, IN, or NOT IN'

Front 354

What are the execution modes for policies?

Back 354

(1) On-Demand (2) On change, prevent (3) On change, log only (4) On schedule

Front 355

What are the foundations of policies?

Back 355

Conditions are the foundation of policies.

Front 356

What can you do if a condition that you want to check for a facet does not have a specific property?

Back 356

you can use the advanced editor to define complex conditions that compare multiple properties and incorporate functions.

Front 357

What execution modes are available for policies that contain conditions that were defined using the advanced editor?

Back 357

On demand is the only execution mode available for policies containing conditions defined using the advanced editor.

Front 358

What is a Condition in Policy Based Management?

Back 358

In PMB, conditions are the equivalent of a WHERE clause that defines the criteria needing to be checked.

Front 359

What is a Facet in SQL Server?

Back 359

They are the core objects upon which your standards are built. Facets define the type of object or option to be checked, such as database, Surface Area, and login. SQL Server ships with 74 facets, each with a unique set of properties.

Front 360

What is policy targeting?

Back 360

When conditions are used to specify the objects to compare the condition against. You can target a policy at the server level, such as instances that are SQL Server 2005 or SQL Server 2008; you can also target a policy at the database level, such as all user databases or all system databases.

Front 361

What is the "On change, log only" policy execution mode?

Back 361

Checks the policy automatically when a change is made using the event notification infrastructure.

Front 362

What is the "On change, prevent" policy execution mode?

Back 362

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

Front 363

What is the "On Demand" policy execution mode?

Back 363

Evaluates the policy when directly executed by the user.

Front 364

What is the "On schedule" policy execution mode?

Back 364

Creates a SQL Server agent job to check the policy on a defined schedule.

Front 365

What is the default category for policies?

Back 365

Trick question; If not specified, all policies belong to the default category..

Front 366

What is the maximum number of check steps that you can incorporate into a condition?

Back 366

Trick question -- just 1. You cannot have a condition pull a list of objects, iterate across the list of objects, and then apply subsequent checks.

Front 367

What problems does Policy Based Management or DMF (Declarative Management Framework) address?

Back 367

The problem of standardizing your SQL Server instances.

Front 368

What property can be set in a policy category such that when a sysadmin subscribe an instance to the policy category -- all databases that meet the target set are controlled by the policies within the policy category and cannot be overwritten?

Back 368

The Mandate property.

Front 369

What tool can you use to get a list of facet properties?

Back 369

SSMS -- SQL Server Management Studio.

Front 370

What type of data do conditions return?

Back 370

Only true or false.

Front 371

Where are the objects for PBM stored?

Back 371

Within the msdb database.

Front 372

Where can subscription occur?

Back 372

Subscription can occur at either the instance or database level.

Front 373

Who can subscribe a database within an instance to a policy category?

Back 373

The owner of the database.

Front 374

Who can subscribe an instance to a policy category?

Back 374

A member of the sysadmin role.

Front 375

T/F -- A policy subscription to a policy category set to Mandate cannot be overridden by a database owner.

Back 375

True.

Front 376

T/F -- You can check multiple criteria for a single facet property

Back 376

TRUE

Front 377

T/F -- you cannot check more than one property of a facet per condition?

Back 377

False. You can check many properties of a facet within a single condition.

Front 378

T/F -- you cannot define a single condition for multiple facets?

Back 378

True. A single condition can't be defined for multiple facets. For example, you can check all 10 of the properties for the Surface Area Configuration facet in a single condition, but you have to define a second condition to check a property of the Surface Area Configuration for analysis services.

Front 379

T/F -- you cannot export or import Policies and Conditions.

Back 379

False. Policies and conditions can be exported to files as well as imported from files.

Front 380

cc

Back 380

xx

Front 381

cc

Back 381

xx

Front 382

cc

Back 382

xx

Front 383

Chapter 9: Lesson 1: Backing Up a Database

Back 383

xx

Front 384

Quick Check: How can you detect and log corrupt pages?

Back 384

Execute ALTER DATABASE <database name> SET PAGE_VERIFY CHECKSUM

Front 385

Quick Check: What are the four type of backups?

Back 385

You can execute full, differential, transaction log, and file/filegroup backups. A full backup is required before you can create a differential or transaction log backup.

Front 386

D. Back up the transaction log with the TRUNCATE_ONLY option."

Back 386

"1. Correct Answer: C

Front 387

Exam tip:

Back 387

You need to know how to perform each type of backup that can be executed. Backing up and restoring databases and the entire SQL Server environment is a major focus of the exam.

Front 388

Lesson Summary:

Back 388

"* Full backups are the starting point for every backup procedure and recovery process. A full backup contains only the pages within the database that have been used.

Front 389

Best Practice: Avoiding Backup Problems

Back 389

To avoid confusion, it is recommended that you use a unique naming scheme that employs a date and time in the file name so that you can tell when a backup was taken based on the name of the backup file. Because backups are taken to reduce your risk of data loss, it is also never a good idea to include multiple backups in a single file.

Front 390

Best Practice: Decreasing backup times

Back 390

The overhead of compression always wroth it. The amount of time saved for a compressed backup far exceeds the overhead associated with the compression operation. Fortunately, SQL Server has a configuration option called backup compression default that you can set to always have backups compressed regardless of whether you explicitly specify compression.

Front 391

Note: Executing Maintenance Plans

Back 391

Maintenance plans are based upon the tasks within SQL Server Integration Services (SSIS). So, when a maintenance plan executes, it first loads the SSIS engine. Then the .NET Framework interprets the tasks within the package, constructs the necessary backup statements, and executes the code generated.

Front 392

Note: Identifying bad pages

Back 392

It is recommended that you specify the CHECKSUM option to catch bad pages as early as possible. You do not want to encounter any surprises when you need to use the backup to restore a database.

Front 393

Note: Repairing a corrupt page.

Back 393

If the database is participating in a Database Mirroring session, a copy of the corrupt page is retrieved from the mirror. If the page on the mirror is intact, the corrupt page is repaired automatically with the page retrieved from the mirror.

Front 394

Note: Restoring a Striped Backup

Back 394

When SQL Server stripes a backup across multiple devices, all devices are required to successfully restore. SQL Server does not provide any redundancy or fault tolerance within the stripe set. A stripe set is used strictly for performance purposes.

Front 395

Service Master Key

Back 395

The symmetric key for the instance that defined by the service account and used as the root of the encryption hierarchy within an instance.

Front 396

Before ever doing a transaction log backup -- what has to have been done first?

Back 396

You have to have had a full-backup first. After the first backup -- so long as the transaction log chain is not interrupted, you can restore the database to any point in time.

Front 397

Explain a good security strategy for making sure your offsite backups are secure?

Back 397

Store your master keys and certificates in a different offsite storage than your encrypted backup files.

Front 398

Given that completely backing up all data is a critical part of the DBA job, what is a good reason for doing partial backups?

Back 398

If you have sections of your data that are READ ONLY, it doesn't make sense to include them in your regular backup routines. You'd keep the last good copy of the read-only data and only backup those files that are READ_WRITE files.

Front 399

How do you ensure that your backup files are good?

Back 399

You validate backups using the VERIFYONLY command.

Front 400

How do you figure out which types of backups best meet your recovery needs?

Back 400

You start with the recovery requirements and procedures.

Front 401

How do you keep the COPY_ONLY option from affecting the differential change map, and therefore from having no impact on differential backups?

Back 401

Trick question, by it's nature, the COPY_ONLY option does not affect the DCM and therefore, has no impact on the differential backups.

Front 402

How do you manually reset the LSN log back to 1?

Back 402

Trick Question -- you can't. The LSN starts at 1 when the database is created and increments to infinity. An LSN is never reused for a database and always increments. It provides a sequence number for every change made to a database.

Front 403

How do you restore a database to a specific point in time?

Back 403

By using transaction log backups. Based on the sequence number, it is possible to restore one transaction log backup after another to recover a database to any point in time by simply following the chain of transactions as identified by the LSN.

Front 404

How does SQL Server accommodate changes to while ensuring that backups are consistent for restore purposes?

Back 404

It locks the database, blocking all transaction; then it places a mark in transaction log before releasing the lock; it extracts all the pages that aren't empty in the data files and writes them to the backup; it then relocks the database, places a second mark in the transaction logs before re-unlocking the database; It extracts the portion of the log between the marks and appends the backup to account for the transactions that were logged during the backup.

Front 405

How does SQL Server track each extent that has been changed following a full backup?

Back 405

It used the DCM -- a special page in the header of a database called the Differential Change Map.

Front 406

How should you grant permissions for the backups

Back 406

You could grant read/write access on the backup directory directly to the SQL Server service account, but you should instead grant read/write access to the Windows group SQLServerMSSQLUser$<machine_name>$<instance_name>, which contains the SQL Server service account.

Front 407

If you don't specify a directory on the target device during backup, what does SQL Server do?

Back 407

SQL Server performs a backup to disk and writes the file to the default backup directory configured for the instance.

Front 408

If you're doing partial backups, what backup procedure would you use?

Back 408

Filegroup backups, and you'd only backup the filegroups that are READ_WRITE files.

Front 409

In SQL Server, how are the permissions defined for backups?

Back 409

All backups execute under the security context of the SQL Server service account.

Front 410

In the event of a loss of a certificate, can you create a new one -- and are there any consequences?

Back 410

Yes, you can create a new one, but you cannot access any data that was encrypted with the certificate.

Front 411

More Info: Master Keys

Back 411

Chapter 11 has additional information about the use and management of certificates and master keys.

Front 412

Once an instance has been created, what is the command you have to run to initiate the service

Back 412

Trick Question: When you create an instance, the service master key is created automatically.

Front 413

Under what conditions is the service key regenerated?

Back 413

Whenever you change the SQL Server service account or when you change the service account password.

Front 414

What are certificates used for?

Back 414

They are used to encrypt data as well as digitally sign code modules.

Front 415

What are the 5 tasks that are supported by maintenance plans:

Back 415

1. Backing up db's and transaction logs 2. Shrinking databases 3. Re-indexing 4. Updating of statistics 5. Performing consistency checks.

Front 416

What are the four different types of backups?

Back 416

(1) Full (2) Differential (3) Transaction log (4) Filegroup

Front 417

What are the only operations that are not allowed during a full backup?

Back 417

Adding or removing a database file; Shrinking a database

Front 418

What are the parameters required for a backup?`

Back 418

Just 2 -- the name of the database and the backup device.

Front 419

What are the requirements you have to meet when invoking the MIRROR TO clause during backups?

Back 419

(1) All backup devices must be of the same media type (2) Each mirror must have the same number of backup devices (3) WITH FORMAT must be specified in the backup command.

Front 420

What are the two basic parts of the contents of a transaction log?

Back 420

Active and Inactive.

Front 421

What are the two types of key that a certificate has?

Back 421

A public and a private key.

Front 422

What can you do to get things back on tract if you have an LSN gap introduced?

Back 422

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

Front 423

What do Maintenance Plans do for you in SQL Server

Back 423

They provide a mechanism to graphically create job workflows that support common administrative functions such as backup, re-indexing and space management.

Front 424

What does the MIRROR TO clause do during backups with SQL Server?

Back 424

It provides a built in capability to create up to four copies of a backup in a single operation.

Front 425

What does the Service Master key do?

Back 425

Is used as the root of the encryption hierarchy within an instance.

Front 426

What feature was introduced in SQL Server 2005 that allows you to keep a database instance online even when SQL Server encounters a corrupted page?

Back 426

SQL Server 2005 introduced the ability to quarantine corrupt pages while allowing the database to remain online.

Front 427

What happens when a transaction log backup is executed?

Back 427

SQL Server starts with the lowest LSN in the transaction log and starts writing each successive transaction log record into the backup. As soon as SQL Server reaches the first LSN that has not yet been committed (that is, the oldest open transaction), the transaction log backup completes. The portion of the transaction log that has been backed up is then removed, allowing the space to be reused.

Front 428

What happens when differential backup is executed?

Back 428

SQL Server reads the contents of the DCM -- any that have been changed from 0 to 1 since the last full backup (which is what happens when that little corner of the database is changed) are backed up.

Front 429

What happens when SQL Server reads a page and the checksum doesn't match the checksum that is stored for that page?

Back 429

SQL Server returns an error and logs the page into a table in the msdb database

Front 430

What happens when the CHECKSUM option is specified during a BACKUP and a corrupt page is encountered?

Back 430

The default behavior of SQL Server is that if an invalid checksum is encountered during a backup, the backup terminates with an error.

Front 431

What happens when you specify more than one backup device?

Back 431

SQL Server stripes the backup across each of the devices specified.

Front 432

What happens when you use the VERIFYONLY to validate backups?

Back 432

"(1) It calculates a checksum for the backup and compares to the checksum stored in the backup file

Front 433

What is a differential backup?

Back 433

A differential backup captures all extents that have changed since the last full back.

Front 434

What is a DMK?

Back 434

Just as a SMK is used as the root of the encryption hierarchy within an instance, a DMK is the root of the encryption hierarch in a database.

Front 435

What is a filegroup backup?

Back 435

It's a type of backup that allows you to target a portion of a database to be backed up.

Front 436

What is an LSN?

Back 436

It's a Log Sequence Number -- it is the unique number internally assigned to each row of the transaction logs -- specifying a single entry or single change made to the database.

Front 437

What is one way to speed up backups?

Back 437

Compressing the backups reduces the size of the backup by between at 4:1 and 10:1 ratio

Front 438

What is required to do a transaction log backup?

Back 438

The transaction logs must be intact. You cannot have gaps in the LSN sequence -- as a gap would prevent any subsequent transaction log backup from being executed.

Front 439

What is the active portion of the transaction log?

Back 439

It contains all the changes that have not yet been committed.

Front 440

What is the command that tells SQL Server to detect and quarantine corrupted pages?

Back 440

ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM

Front 441

What is the default way that the DMK is encrypted?

Back 441

By default it is encrypted with the SMK.

Front 442

What is the difference between a differential backup and a transaction log backup?

Back 442

A transaction log backup is an incremental backup because it captures any changes that have occurred since the last transaction log backup. A differential backup contains all pages changed since the last full backup. If you did a full backup at midnight and a differential backup every four hours -- at 4 am and 8 am -- then the backup would contain all changes made to the database since midnight -- not just since the last backup at 4 am.

Front 443

What is the first action you should take after an instance is started?

Back 443

To backup the service master key.

Front 444

What is the first thing you should do after the creation of keys for a certificate?

Back 444

Back them up.

Front 445

What is the inactive portion of the transaction log?

Back 445

It contains all the changes that have been committed.

Front 446

What is the key created for each instance of SQL Server -- that has to be backed up to ensure a complete recovery of your instance?

Back 446

Service master key.

Front 447

What is the limiting backup for backup performance?

Back 447

The speed of the backup device.

Front 448

What is the mechanism in SQL Server to protect your database from massive corruption?

Back 448

You are limited to 1,000 corrupt pages in a database. When you reach the corrupt page limit, SQL Server takes the database off-line and places it in a suspect state to protect it from further damage.

Front 449

What is the primary purpose of the differential backup?

Back 449

To reduce the number of transaction log backups that need to be restored.

Front 450

What is the tradeoff's in do a compressed vs. uncompressed backup/

Back 450

The compressed backup will give you a smaller file to write, but it will be tremendously taxing on the processor.

Front 451

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

Back 451

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

Front 452

What must be first done before doing differential backups?

Back 452

You must first have a full back -- as a differential backup has to be applied to a full backup and can't exist until a full backup has been created.

Front 453

What must exist before you can use a differential or transaction log backup?

Back 453

A full backup. It is the basis for recovering a database.

Front 454

What must you first do the DMK before you can back it up?

Back 454

It must be open before you can back it up.

Front 455

What option during the backup would you use if you want a backup, but you don't want to affect the starting point for recovery (say the time of the last full backup)?

Back 455

You'd use the COPY_ONLY option. Sometimes used when you want a copy of the database for a development or test environment -- but you're not backing up the databases in a way that you want to affect the recovery chains.

Front 456

What option of the BACKUP command gives you the ability to control whether an existing backup is overwritten or append to?

Back 456

The INIT/NOINIT options. When you specify NOINIT and you are backing up to a file that already exists, SQL Server appends the new backup to the end of the file. If you specify INIT and the file already exists, SQL Server overwrites the file with the contents of the new backup.

Front 457

What other roles in SQL Server can perform backups?

Back 457

A member of sysadmin server role can back up any database in an instance and members of the db_owner database role can back up their databases. You can also add a user to the db_backupoperator fixed database role to allow the user to back up a database while preventing any other access to the database.

Front 458

What type of value is the LSN?

Back 458

It is an integer value.

Front 459

What would you ever execute a filegroup backup?

Back 459

If you need to reduce the footprint of a backup - as a full-backup can consume a significant amount of space and time.

Front 460

When else should you backup the service master key?

Back 460

Immediately following a change to the service account or service account password.

Front 461

When you do a full backup, what happens to the DCM's?

Back 461

A full backup zeroes out the contents of the DCM.

Front 462

Where should you always store your backups?

Back 462

Offsite

Front 463

Why is it important to have a backup of the DMK?

Back 463

Because you need it to access certificates, asymmetric keys and symmetric keys with in a database.

Front 464

Caution: Backing up individual files

Back 464

Although it is possible to backup a file, it is recommended that your backups are only as granular as the filegroup level. A filegroup is a storage boundary, and when you have multiple files within a filegroup, SQL Server stores data across all the files. However, with respect to the table, index, or partition, the distribution of data across the files is essentially random. Therefore, to recover a database, you need all the files underneath a filegroup to be in exactly the same state.

Front 465

T/F -- a database has to be locked during the backup process and applications have to be locked out of databases until the backup is completely, to ensure that the underlying data doesn't change during the backup itself.

Back 465

False. A backup can be executed concurrently with other database operations.

Front 466

T/F -- A single backup device can contain only one good backup at a time?

Back 466

False. A single backup device can contain multiple backups.

Front 467

T/F -- you can specify up to 8 backup devices during a backup with SQL Server?

Back 467

False, you can specify up to 64 backup devices.

Front 468

Chapter 9: Lesson 2: Restoring Databases

Back 468

xx

Front 469

Quick Check: What is the first operation that should be performed for any restore operation?

Back 469

Back up the tail of the log.

Front 470

Quick Check: Which recovery model always allows you to restore to the point of fail so long as you can back up the tail of the log?

Back 470

The Full recovery model.

Front 471

1. The server that Customers database is running on fails and needs to be replaced. You build a new server and install SQL Server 2008. When you built the new server, you decided that instead of configuring the new server exactly like the old one, you implement a new drive letter and folder structure for data and log files. Which option do you need to use when you restore the Customers database to the new server?

Back 471

"A. Incorrect: While the NORECOVERY option allows you to restore subsequent differential and transaction log backups, the restore fails because SQL Server does not have a valid directory structure to restore the database.

Front 472

Lesson Summary:

Back 472

"* The first step in any restore procedure is to back up the tail of the log.

Front 473

Tip: Restoring an existing database

Back 473

The creation and sizing of all files associated to a database can consume a significant amount of time. If the database already exists, you should just restore over the top of the existing database.

Front 474

How does page contents become inconsistent?

Back 474

When the page checksum does not make the contents of the page or a row is only partially written to the page. Page corruption usually occurs where a disk controller begins to fail.

Front 475

If page corruption restores in an Index, how can you restore the index from backup without executing a full restore?

Back 475

Trick Question: If an index is corrupt, you don't restore it, you drop and recreate the index to restore the corruption.

Front 476

If the original database file is gone, how can you get issue the final transaction log backup so you can start a restore.

Back 476

Trick question. You can't issue the final transaction log backup without the original database.

Front 477

If you encounter corruption in a page of data within a table or primary key and are forced to do a restore to fix the corruption issue -- what can you do short of a full restore?

Back 477

You can do a Page RESTORE.

Front 478

If you have one insert statement that affect 4 databases at the same time, which of the databases is the logging database for the transaction?

Back 478

Trick question, transaction logs are appended for every database that is affected. Transaction logs are database specific.

Front 479

If you know that you're doing to have to apply to transaction log backups or differential backups to a database being restored, what option to you use during the RESTORE of the database?

Back 479

You need to use the NORECOVERY option during RESTORE.

Front 480

If you need to have a database available after RESTORE, what option to you need to specify?

Back 480

RECOVERY

Front 481

If you want the RESTORE of a database to not be in RECOVERY mode, but you still want to be able to issue select statements, what option do you need to use during RESTORE?

Back 481

You need to use the STANDBY option. With you, you can issue SELECT statements against the database will still issuing additional differential and/or transaction long restores.

Front 482

If you want to do a Point-In-Time restore based on the LSN or transaction log MARK, what RESTORE options do you use?

Back 482

The STOPATMARK and STOPBEFOREMARK

Front 483

If you're restoring a database over the top of your only copy -- what option should you always run?

Back 483

With the RESTORE option you should always use CONTINUE_AFTER_ERROR otherwise, if there's a problem with the recovery media, it errors out and stops immediately and you've just corrupted your only good copy.

Front 484

If your files systems on the machine you're restoring don't match, what command might you want to use during RESTORE?

Back 484

You might want to use the MOVE option. The MOVE option provides the ability to change the location of one or more data files when the database is restored.

Front 485

To provide the greatest flexibility for a restore, what is the first step.

Back 485

To issue a transaction log backup against the original database.

Front 486

What are the 5 requirements for a page-level restore?

Back 486

1. The database must be in either the Full or Bulked-logged recovery model. 2. You must be able to create a transaction log backup. 3. A page restore can apply only to a read/write filegroup 4. You must have a valid full, file, or filegroup backup available 5. The page restore cannot be executed at the same time as any other restore operation.

Front 487

What are the two options for the beginning of a restore sequence?

Back 487

Full backup or filegroup backup.

Front 488

What DB role can perform SELECT operations against a database that is being RESTOREd in the EMERGENCY state?

Back 488

those that are member of the db_owner role.

Front 489

What is the 3rd step of restoring a corrupt page?

Back 489

Restore any differential backups with NORECOVERY option.

Front 490

What is the 4th step of restoring a corrupt page?

Back 490

Restore any additional transaction log backups with NORECOVERY option.

Front 491

What is the 5th step of restoring a corrupt page?

Back 491

Create a transaction log backup

Front 492

What is the 6th step of restoring a corrupt page?

Back 492

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

Front 493

What is the first step of restoring a corrupt page?

Back 493

1. Retrieve the PageID of the damaged page.

Front 494

What is the given to the type of restore where you only want to restore/replay a portion of the transaction log?

Back 494

This is referred to as a point-in-time restore.

Front 495

What is the name given to the step in the restore process where you first take a final transaction log backup?

Back 495

It's referred to as backing up the tail of the log.

Front 496

What is the RESTORE option that allows you to specify a date and time for which SQL Server restores?

Back 496

The STOPAT command.

Front 497

What is the RESTORE option that is used to force the restore over the top of an existing database?

Back 497

REPLACE

Front 498

What is the second step of restoring a corrupt page?

Back 498

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

Front 499

What type of database RESTORE is necessary to allow for an online restore?

Back 499

You can only do an online restore of a database that is a filegroup restore.

Front 500

What type of DB RESTORE option can be done that allows databases to stay up and accept transaction and other operations?

Back 500

The ONLINE option.

Front 501

What type of DB RESTORE option can be done that allows only SELECT operations to be done with those in a specify DB Role?

Back 501

the EMERGENCY state.

Front 502

When does Page Corruption occur?

Back 502

When the contents of a page are not consistent.

Front 503

Which is quicker, backing up or restoring?

Back 503

Backing up. Because you backup more than you restore, the Backup/Restore process is optimize for Backing up. Restoring takes approximately 30% longer.

Front 504

Chapter 9: Lesson 3: Database Snapshots

Back 504

xx

Front 505

Quick Check: Prior to reverting a database using a Database Snapshot, what must you do?

Back 505

You must drop all Database Snapshots except the Database Snapshot being used as the source for the restore command.

Front 506

Quick Check: Which two features are incompatible with Database Snapshots?

Back 506

FILESTREAM and full-text indexes

Front 507

D. Distribution"

Back 507

"1. Correct Answers: B and C

Front 508

Exam Tip:

Back 508

You need to know that FILESTREAM is not compatible with Database Snapshots. Although you can create a DS against a database enable for FILESTREAM, you cannot use the Database Snapshot as a source for a RESTORE DATABASE operation.

Front 509

Lesson Summary:

Back 509

"* A Database Snapshot is a point-in-time, read-only, copy of a database.

Front 510

Database Revert

Back 510

the restoration of a database from a database snapshot.

Front 511

Differential Backup

Back 511

Backup that contains all of the extents within a database that have changed since the last full backup.

Front 512

Full backup

Back 512

A backup that captures all of the pages within a database that contain data.

Front 513

Log Sequence Number (LSN)

Back 513

The primary key within the transaction log. The LSN starts at 0 when the database is created, always increments, never repeats, and can never be reset to a previous value.

Front 514

Online Restore

Back 514

A filegroup restore that is performed in SQL Server 2008 Enterprise that occurs while the rest of the database remains online and accessible.

Front 515

Page Corruption

Back 515

A scenario whereby the contents of a data page are inconsistent or have become corrupted, usually due to the failure of a Redundant Array of Inexpensive Disks (RAID) controller or Host Bus Adapter (HBA).

Front 516

Partial Backup

Back 516

A backup operation that backs up only the filegroups that can be written to.

Front 517

Partial Restore

Back 517

A filegroup restore that affects only a portion of a database.

Front 518

Tail Backup

Back 518

The first step in every restore operation. A tail backup executes a final transaction log backup to save all remaining transactions in the log prior to beginning a restore sequence.

Front 519

Transaction Log Backup

Back 519

An incremental backup for a database. Contains all of the transactions that have been committed since the last transaction log backup.

Front 520

Transaction Log Chain

Back 520

A continuous set of transaction log backups that can be applied to a full backup and does not provide any gaps in the transaction log sequence.

Front 521

How does copy-on-write work?

Back 521

The first time a modification to a data page within an extent occurs, SQL Server copies the before image of the extent to the Database Snapshot. When SELECT statements are issues against the DS, SQL retrieves data from the DS for any data that has changed while still pulling data from the source database for any extents that have not changed.

Front 522

How many pages are then in an extent?

Back 522

8 pages

Front 523

What are the most common filegroup restrictions for Database Snapshots?

Back 523

You can't reference filegroups that are offline, defunct or restoring.

Front 524

What are they two requirements for creating a Database Snapshot?

Back 524

"(1) You must include an entry for each data file specified in the source database.

Front 525

What clause do you add to the end of the CREATE DATABASE command to create a snapshot?

Back 525

AS SNAPSHOT OF

Front 526

What do you have to do for FILESTREAM data during the revert process?

Back 526

The source database cannot be enabled for FILESTREAM

Front 527

What happens to the Data in a Snapshot when the source database is gradually modified?

Back 527

Nothing. The Database Snapshot retains the state of the data in the source database the instant the Database Snapshot was created.

Front 528

What happens to the transaction log during a revert?

Back 528

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

Front 529

What is a Database Revert?

Back 529

It returns the source database to the state it was in when the Database Snapshot was created -- for example, if you needed to discard every change that happened within the database since the Database Snapshot was created.

Front 530

what is sparse files?

Back 530

It is an entry in the File Allocation Table. As data is added to the file, the file automatically grows on disk. By using sparse files, the creation time for a Database Snapshot is independent of the size of the source database.

Front 531

What is the Maximum Size of a Snapshot?

Back 531

The size of the source database at the time the snapshot was taken -- since if every single piece of data changed, you'd have made a copy of every single original file.

Front 532

What is the name of a special category of restoring data that can only be performed if you have a Database Snapshot?

Back 532

Database Revert

Front 533

What is the option that must be specified to keep a database online during a restore?

Back 533

Trick question: Both the source and the snapshot are off-line during the revert process.

Front 534

What mechanism does SQL Server use to maintain any changes that occur within the source database?

Back 534

Copy-On-Write

Front 535

What must you do to Full-text catalogs after a revert is performed?

Back 535

They must be dropped and recreated.

Front 536

What operating system feature does SQL Server take advantage of when a Database Snapshot is created?

Back 536

sparse files

Front 537

What restrictions does having a Database Snapshot impose on the original database?

Back 537

You can't drop, restore, or detach a source database that has a Database Snapshot created against it.

Front 538

What versions of SQL Server 2008 support Database Snapshots?

Back 538

Only Enterprise

Front 539

When backing up a Database Snapshot, what option allows you to do a full backup?

Back 539

Trick Question: You can't backup a Database Snapshot

Front 540

When reverting a source database with more than one snapshot, what are the restrictions?

Back 540

Trick question: You can only revert source databases that have just a single Database snapshot.

Front 541

Which Databases cannot have a snapshot created again them?

Back 541

System Databases

Front 542

Which indexes are not supported in Database Snapshots?

Back 542

Full-text indexes are not supported.

Front 543

Why don't you specify a transaction log for Database snapshot?

Back 543

Because they're read-only, you can't write to them and therefore, do not need a transaction log.

Front 544

Caution: Managing FILESTREAM Data

Back 544

Database snapshot is not compatible with FILESTREAM. If you create a Database Snapshot against a database with FILESTREAM data, the FILESTREAM filegroup is disabled and not accessible.

Front 545

T/F: A Database Snapshot must exist on the same machine as the original database?

Back 545

True, because it has to exist on the same instance, and therefore on the same machine. Cannot be on the same machine, but in a different instance.

Front 546

Chapter 10: Lesson 1: Creating Jobs

Back 546

xx

Front 547

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

Back 547

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 548

Quickcheck: What types of job steps can be executed?

Back 548

You can create jobs that execute T-SQL, ActiveX Scripts, OS commands, or executive feels. You can also configure specific tasks for replication, Analysis Services, and Integration Services.

Front 549

D. The SQL Server error log"

Back 549

"1. Correct Answer: B

Front 550

Exam Tip:

Back 550

You need to know where to find information to diagnose the cause of an error in a job step.

Front 551

Lesson Summary: Creating Jobs

Back 551

"* An Operator is an alias for a person, group, or device to which you want to be the target of notifications.

Front 552

Best Practice: Logging Steps

Back 552

Every job step that you create should be configured to log to a file. The most common way to configure logging is to create a new log file in the first step of a job, and then each subsequent job step appends information to the log file.

Front 553

Note: Understanding the Standard Workweek

Back 553

The start and end time for a workday is based on the U.S. Standard workweek of Monday-Friday and does not accommodate any other workweek definition.

Front 554

How can you view the histories of jobs that have been executed?

Back 554

My looking at the log files that were specified in the log steps or by looking at the dbo.sysjobhistory table in the msdb database.

Front 555

If you want to automate a job you've created in SQ; Server, what do you do?

Back 555

You add the job to a Job Schedule.

Front 556

In addition to commands to execute -- what are some other things that Job Steps can be configured with?

Back 556

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 557

In the context of automating SQL Server, what is an operator?

Back 557

It is 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 such as e-mail address, pager number or NET SEND address.

Front 558

What are control flow options?

Back 558

They allow you to specify an action based on either success or failure.

Front 559

What are Job Steps?

Back 559

They are the executive elements within a job in SQL Server.

Front 560

What are some example of control flow options/

Back 560

Quit job reporting success; Quit job reporting failure; Go to next step; Go to a specific step number

Front 561

What are some examples of executable Job Steps

Back 561

T-SQL; Replication Tasks; OS tasks or executable files; Analysis Services Tasks; Integration Services Packages; Active X Scripts

Front 562

What are three different approaches to recording logs?

Back 562

Overwriting and Appending a log file. You can also log step output to a table, but this is not recommended due to the extra overhead of logging to a table versus logging to a text file.

Front 563

What if you don't want the security to be the owner of the job?

Back 563

You can 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 564

What is the default security context for a job step?

Back 564

It is the default security context that corresponds to the login that is set as the owner of the job.

Front 565

Where do you create a job schedule?

Back 565

You can create a Job Schedule through the Manage Schedule dialog box or during the creation of a job.

Front 566

Chapter 10: Lesson 2: Creating Alerts

Back 566

xx

Front 567

Quick Check: What are the three types of alerts that can be created?

Back 567

You can create alerts on performance counters, SQL Server Errors and WMI queries.

Front 568

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

Back 568

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

Front 569

D. Network Monitor"

Back 569

"1. Correct Answer: A

Front 570

Exam Tip:

Back 570

You need to know the types of alerts that can e defined in SQL Server and the response criteria that can be specified for an alert.

Front 571

Lesson Summary: Creating Alerts

Back 571

Alerts enable you to notify operators as well as execute jobs to fix problems when an event occurs.

Front 572

Alert

Back 572

An object that can be created to respond to a change in the system state

Front 573

Job Step

Back 573

An individual unit of work within a job.

Front 574

Operator

Back 574

An Alias for a person, group or device that you want to send a notification to.

Front 575

In SQL Server, what is an Alert?

Back 575

it is the ability to send a notification or perform an action based upon events or conditions that occur either within the SQL Server instance or on the machine hosting your instance.

Front 576

What are the three types of alerts?

Back 576

"1. SQL Server Event

Front 577

What are the two types of responses that can be configured to an alert?

Back 577

"1. Execute Job

Front 578

What is a Performance Condition Alert?

Back 578

They are alerts that are defined against System Monitor counters. I.E. It sends out an alert when the amount of free disk space falls below 15 percent.

Front 579

what is a SQL Server Event alert?

Back 579

It is an alert based on either an error number or an error severity level. When a SQL Server event alert is created, the SQL Server Agent scans the Windows Application event log to look for matches to the event criteria that you have defined. IE; you could fire an alert on an error severity of 22 to notify an operator that a table is suspect.

Front 580

What is a Windows Management Instrumentation alert?

Back 580

An alert for a WMI event allows you to send alerts based on events that occur on the server hosting your SQL Server instance. Anytime an event occurs on the machine (IE, a network card is disconnected, a file is created or deleted, or the registry is written to) a WMI event is raised within Windows. A WMI alert sets up a listener to the WMI infrastructure to fire the alert when the Windows event occurs.

Front 581

Chapter 11: Lesson 1: TCP Endpoints

Back 581

xx

Front 582

Quick Check: What are the three states of an endpoint, and what is the difference between each state?

Back 582

The three states are STARTED, STOPPED, AND DISABLED. An endpoint that is STARTED listens for and allows connections. An endpoint that is STOPPED listens for connection request and returns an error message. An endpoint that is DISABLED does not respond to any request.

Front 583

Quick Check: What are the two parts of an endpoint?

Back 583

Payload and Transport. An endpoint ahs transport defined as either TCP or HTTP and ahs a payload defined as TSQL, SERVICE_BROKER, DATABASE_MIRRORING, or SOAP.

Front 584

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

Back 584

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

Front 585

D. Users do not have CONNECT permissions on the endpoint."

Back 585

"1. Correct Answer: D

Front 586

D. The encryption is set to AES on each endpoint"

Back 586

"Correct Answer: C

Front 587

Exam Tip:

Back 587

TSQL endpoints do not have any additional configuration options beyond the universal TCP settings.

Front 588

Exam tip:

Back 588

You can specify only one TCP endpoint with a payload of DATABASE_MIRRORING for each instance of SQL Server.

Front 589

Lesson Summary: TCP Endpoints

Back 589

"* Endpoints in SQL Server act very similar to firewalls by filtering out any traffic that does not meet allowed formats.

Front 590

Best Practices: Authentication

Back 590

When all Database Mirroring and Service Broker instances reside within a single domain or across trusted domains, you should use Windows authentication. When instances span non-trusted domains, you should use certificate-based authentication.

Front 591

Best Practices: Encryption

Back 591

Use RC4 for minimal encryption strength and best performance. Use AES if you require strong encryption, but not that this algorithm requires more calculation overhead and will affect performance.

Front 592

Best Practices: Port numbers

Back 592

Because port 5022 is the default TCP port number for a DATABASE_MIRRORING endpoint, the 1433 is the default TCP port for TSQL endpoint, you might want to specify a different port number. Not using the default port number helps to foil potential hackers -- or at least makes their job more difficult -- by requiring them to use a port scanner instead of just blindly connecting to port 1433 or 5022 for a denial of server (DoS) attack or other hacking attack.

Front 593

Note: Encryption

Back 593

The communication encryption for endpoints is coded to understand the source and destination of the traffic. If the communication occurs entirely within the SQL Server instance, the traffic is not encrypted because it would introduce unnecessary overhead in the communications. This is especially important with Service Broker, in which many messages are exchanged between queues within a single instance. Traffic is encrypted only when data will be transmitted outside the SQL Server instance.

Front 594

Note: Endpoints on Express Edition

Back 594

If you are creating a Database Mirroring endpoint on SQL Server 2008 express, it supports on the role of WITNESS

Front 595

Besides the mandatory LISTENER_PORT -- what other options do you have for the Endpoint Access Security?

Back 595

The LISTENER_IP argument is an option argument that can provide a very powerful security layer for some types of applications.

Front 596

For endpoints security, what are the two authentication methods you can use?

Back 596

Windows-based authentication or certificates.

Front 597

How can an endpoint specified as a WITNESS participate?

Back 597

As a witness.

Front 598

How can an endpoint specified as ALL participate?

Back 598

In any role.

Front 599

How can an endpoint specified as PARTNER participate?

Back 599

As a principal or as the mirror.

Front 600

If you wanted to create strong algorithms for encryption -- what can you do?

Back 600

You can use AES -- Advanced Encryption Services algorithm.

Front 601

In the context of Endpoint Access -- describe the Endpoint state of DISABLED

Back 601

The endpoint does not listen and does not respond to any connection that is attempted.

Front 602

In the context of Endpoint Access -- describe the Endpoint state of STARTED

Back 602

The endpoint is actively listening for connections and will reply to an application.

Front 603

In the context of Endpoint Access -- describe the Endpoint state of STOPPED

Back 603

The endpoint is actively listening but returns a connection error to an application.

Front 604

In the context of Endpoint Access -- what are the two layers or security?

Back 604

endpoint state and permission.

Front 605

What are the Database Mirroring and Service Broker Endpoints?

Back 605

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

Front 606

What are the different types of payloads?

Back 606

SOAP, TSQL, SERVICE_BROKER, and DATABASE_MIRRORING

Front 607

What are the three roles for an endpoint in Database Mirroring endpoints?

Back 607

PARTNER, WITNESS or ALL

Front 608

What are the two arguments you can specify that are universal for all TCP endpoints?

Back 608

LISTENER_PORT and LISTENER_IP

Front 609

What are the two basic parts of an Endpoint?

Back 609

The transport and the payload.

Front 610

What are the two different types of transports?

Back 610

TCP and HTTP.

Front 611

What are the two universal arguments for TCP endpoints?

Back 611

You are required to specify a port for the endpoint to listen on. If you want, you can configure an IP address that restricts the endpoint to respond to traffic coming only from the specified IP address.

Front 612

What are the valid Transports (either TCP or HTTP) for TSQL, SERVICE_BROKER, DATABASE_MIRRORING and SOAP

Back 612

TCP for TSQL, SERVICE_BROKER and DATABASE_MIRRORING; HTTP for SOAP

Front 613

What does TCP stand for?

Back 613

Transmission Control Protocol.

Front 614

What is an Endpoint in SQL Server?

Back 614

Endpoints control the capability to connect to an instance of SQL Server as well as to dictate the communications methods that are acceptable. Acting very similarly to firewalls on the network, endpoints are a layer of security at the border between applications and your SQL Server instance.

Front 615

What is Endpoint Permission?

Back 615

It is the second level of Endpoint Access security; It is the permission to connect to an 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 616

What is Endpoint State?

Back 616

It is the first level Endpoint Access security; An endpoint can have one of 3 states -- STARTED, STOPPED and DISABLED.

Front 617

What is the benefit of restricting payloads to a valid method of transport?

Back 617

It allows you to filter traffic without needing to authenticate the request. Endpoints operate like firewalls by rejecting requests that are not properly formatted based on the endpoint definition.

Front 618

What is the default encryption algorithm between endpoints?

Back 618

RC4 is the default algorithm.

Front 619

What is the LISTENER_IP?

Back 619

The LISTENER_IP argument is where you specify an IP address for the endpoint to listen on. The default (and less secure setting) is set to ALL -- which means that the endpoint listens for connections sent to any valid IP address configured on the machine. However, the LISTENER_IP argument allows you to limit connection requests to a specific NIC. Requests to other NIC's on the machine will be ignored.

Front 620

What is the port that the TCP for TSQL endpoint is already configured to during installation?

Back 620

TCP Port 1433 or the alternate port number for the instance.

Front 621

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

Back 621

NTML and Kerberos authentications can be specified. You can also specify an option of NEGOTIATE, which causes the specific authentication method to be negotiated between he application and the endpoint.

Front 622

Which of the two arguments is required?

Back 622

LISTENER_PORT

Front 623

Which of the valid Payload Types allows for message forwarding?

Back 623

SERVICE_BROKER endpoints implement arguments related to message forwarding.

Front 624

Chapter 11: Lesson 2: Configuring the SQL Server Surface Area

Back 624

xx

Front 625

Quick Check: How do you configure an instance so that only local connections are allowed?

Back 625

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

Front 626

Quick Check: What do you use to enable or disable features for an instance?

Back 626

The sp_configure system stored procedure is used to enable or disable features.

Front 627

D. SQL Server Installation Center"

Back 627

"Correct Answer: B

Front 628

Exam Tip:

Back 628

SQL Server 2005 provided a utility called the Surface Area Configuration Manager, which does not exist in SQL Server 2008. The functionality that was provided by Surface Area Configuration for Connections is now accomplished using the SQL Server Configuration Manager. The functionality provided by the Surface Area Configuration for Features did not change; the GUI interface to sp_configure was just removed.

Front 629

Lesson Summary: Configuring the SQL Server Surface Area

Back 629

"* The first surface area configuration decision that you make occurs during installation, when you decide whether to force all login access to the instance to use Windows-only credentials

Front 630

How can you avoid exposing yourself to attack with OPENROWSET and OPENDATASOURCE calls?

Back 630

If you need the ability to execute queries across instances, you should be using linked servers which allow Windows credentials to be passed between machines.

Front 631

How do OPENROWSET and OPENDATASOURCE expose you to attack?

Back 631

By allowing applications to embed security credentials into code that spawns a connection to another instance from within SQL Server.

Front 632

In order to connect to a SQL Server instance remotely, what must you do?

Back 632

You must enable the TCP/IP network provider.

Front 633

What are the two features with the great risk for exposing an external interface or ad hoc execution capability?

Back 633

OPENROWSET/OPENDATASOURCE and OLE Automation procedures.

Front 634

What is the biggest potential risk to an instance?

Back 634

It's the use of features that expose an external interface or ad hoc execution capability.

Front 635

Chapter 11: Lesson 3: Creating Principals

Back 635

xx

Front 636

Quick Check: What database principal was created as a replacement for an application role?

Back 636

loginless users are the replacement for an application role.

Front 637

Quick Check: Which logins cannot be used to authenticate to an instance?

Back 637

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

Front 638

D. Add the logins for application owners to the sysadmin role."

Back 638

"Correct answers: A and C

Front 639

D. Add Tina to the sysadmin role."

Back 639

"Correct Answer: C

Front 640

Exam Tip:

Back 640

You can create SQL Server logins mapped to certificates or asymmetric keys. However, a login mapped to a certificate or asymmetric key does not provide a means to authenticate to an instance. Logins mapped to certificates and asymmetric keys are used internally as a security container.

Front 641

Exam Tip:

Back 641

Loginless users are designed to replace application roles. Loginless users provide a much better audit trail than an application role because each user must authenticate to the instance using their own credentials instead of using a generic account.

Front 642

Lesson Summary: Creating Principals

Back 642

"* You can create SQL Server native logins or map Windows accounts to a SQL Server login

Front 643

db_accessadmin

Back 643

Add or remove users in the database

Front 644

db_backupoperator

Back 644

Backup the database but cannot restore a database or view any information in the database

Front 645

db_datareader

Back 645

Issue SELECT against all tables, views, and functions within the database

Front 646

db_datawriter

Back 646

Issue INSERT, UPDATE, DELETE and MERGE against all tables within a database. Members of this role must also be members of the db_datareader role

Front 647

db_ddladmin

Back 647

Execute data definition language statements (DDL)

Front 648

db_denydatareader

Back 648

Prevent SELECT against all tables, views, and functions within the database

Front 649

DB_denydatawriter

Back 649

Prevent INSERT, UPDATE, DELETE and MERGE against all tables within the database.

Front 650

db_owner

Back 650

Owner of a database that has full control over the database and all objects contained within the database.

Front 651

db_securityadmin

Back 651

Manage the membership of roles and associated permissions, but cannot manage membership for the db_owner role

Front 652

How do you create additional instance level roles?

Back 652

Trick Question: You can't create additional roles at an instance level.

Front 653

How do you defeat against brute force attacks on accounts that are the Administrator's or the sa's account?

Back 653

By setting up the security profiles to disable the accounts are a certain number of failed login attempts.

Front 654

How do you defeat against brute force attacks on the Administrators and sa accounts?

Back 654

By renaming the accounts.

Front 655

If you want to temporarily stop users from being able to access a SQL box -- such as during an upgrade -- but you don't want to revoke all their permissions (causing you to have to reestablish the permissions later), what can you do?

Back 655

Disable the logins. ALTER LOGIN <loginname> DISABLE

Front 656

public

Back 656

Default group in every database that all users belong to

Front 657

What are principals?

Back 657

Principals are the means by which you authenticate and are identified within an instance or database.

Front 658

what are the server roles that ship with SQL Server?

Back 658

bulkadmin; dbcreator; diskadmin; processadmin; securityadmin; serveradmin; setupadmin; sysadmin

Front 659

What are the two major categories of Principles?

Back 659

Logins/users and groups. They exist at both the instance and database level.

Front 660

What do members of the buikadmin role do?

Back 660

Administer BCP and Bulk Insert operations

Front 661

What do members of the dbcreator role do?

Back 661

Create databases

Front 662

What do members of the diskadmin role do?

Back 662

Manage disk resources

Front 663

What do members of the processadmin role do?

Back 663

Manage connections and start or pause an instance

Front 664

What do members of the securityadmin role do?

Back 664

Create, Alter, and Drop logins, but can't change passwords

Front 665

What do members of the serveradmin role do?

Back 665

Perform the same actions as diskadmin and processadmin, plus manage endpoints, change instance setting, and shut down the instance.

Front 666

What do members of the setupadmin role do?

Back 666

Manage linked servers

Front 667

What do members of the sysadmin role do?

Back 667

Perform any action within an instance. Members cannot be prevented from accessing any object or performing any action.

Front 668

What is a loginless user?

Back 668

A user in the database that is not associated to a login.

Front 669

What is another way of referring to instance-level roles?

Back 669

Fixed Server roles

Front 670

What is the fundamental principle of SQL Server Security?

Back 670

"No access by default" If you haven't been explicitly been granted permission, you cannot perform an action. You grant access to a database by adding a login to the database as a user by executing the CREATE USER command.

Front 671

What is the SQL Security equivalent of Groups in Windows Security

Back 671

Roles

Front 672

What two accounts on a Windows / SQL box cannot be locked out?

Back 672

On Windows box, the Administrators Account and on the SQL Server, the sa account.

Front 673

What was introduced in 2008 to replace Application Roles in 2005 and why?

Back 673

loginless users -- because application roles made it difficult to do auditing -- when they connected, it was the application that could be seen to be doing things, and not the user that was logged into the application.

Front 674

Why are Fixed Server Roles given their name?

Back 674

Because you can't modify the permission on the role.

Front 675

Chapter 11: Lesson 4: Managing Permissions

Back 675

xx

Front 676

Quick Check: How are principals, securables and permissions related?

Back 676

You can GRANT, REVOKE, or DENY permissions ON a securable TO a principal

Front 677

Quick Check: what is an ownership chain, and how can you have a broken ownership chain?

Back 677

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

Front 678

D. Grant the users SELECT permission on every schema in the database."

Back 678

"Correct answer C:

Front 679

D. REVOKE VIEW DEFINITION"

Back 679

"Correct answer A:

Front 680

Exam Tip:

Back 680

To create a schema owned by another database principal, the user creating the schema must have IMPERSONATE permission on the principal being designated as the schema owner.

Front 681

Lesson Summary: Chapter 11: Lesson 4: Managing Permissions

Back 681

"* You GRANT permissions ON a securable TO a principal

Front 682

Best Practices:

Back 682

It is interesting to note that many developers seem to argue about calling stored procedures in their applications. Instead they want to embed SQL directly into the application. But none of the developers you work with would ever think of writing an application as just a bunch of embedded code. Rather developers spent large amount of time constructing objects that have interfaces and building applications by connecting objects via their interfaces. This development style allows multiple developers to work on a complex application, even when dependent code has not been completed. Stored procedures perform the same function as the APIs that developers use within every application. A stored procedure is nothing more than an API to the database, which means that developers do not even need to know the structure of the database.

Front 683

Can securables be nested?

Back 683

yes -- For example, an instance contains databases; databases contain schemas; and schemas contain tables, views, procedures, functions, and so on.

Front 684

How are securables relevant to managing permissions in SQL Server?

Back 684

Securables are the objects on which you grant permissions.

Front 685

How can stored procedures become your most powerful security mechanism within your database?

Back 685

By using ownership chains.

Front 686

how do you add permissions to an object?

Back 686

With the GRANT statement.

Front 687

How do you prevent access to an object?

Back 687

With the DENY statement. DENY statements override any other permissions listed in the security table -- including the GRANT statement.

Front 688

How do you remove permission entries for objects?

Back 688

Using the REVOKE statement.

Front 689

How does this work?

Back 689

Applications can be built to call stored procedures, which never granted direct access to the underlying tables; therefore the only actions that can be performed are the actions allowed by the stored procedures.

Front 690

In SQL Server, what objects are securables?

Back 690

Every object within SQL Server -- including the entire instance is a securable.

Front 691

In the management of permission, what is impersonation/

Back 691

you can impersonate another principal to execute a command in that users specific user context. To Impersonate, you must have the IMPERSONATE permission granted to your account on the principal that you want to impersonate.

Front 692

What are Certificates?

Back 692

Certificates are keys based on the X.509 standard that are used to authenticate the credentials of the entity supplying the certificate.

Front 693

What are private certificates?

Back 693

They are generated by and used to protect data within an organization.

Front 694

What are public certificates?

Back 694

They are essentially a file that is supplied by a certificate authority that validates the entity using the certificate.

Front 695

What are signatures?

Back 695

They allow you to elevate a user's permission but to provide a restriction such that the elevation occurs only when the user is executing a specific piece of code.

Front 696

What are the Administrative accounts in SQL Server?

Back 696

Members of the sysadmin role, members of the db_owner role, and the sa account.

Front 697

What are the most useful places to employ a signature?

Back 697

To bridge a the gap in a broken ownership chain.

Front 698

What are the two types of Certificates?

Back 698

Public and Private

Front 699

What do permissions do?

Back 699

They provide the authority for principals to perform actions within an instance or database.

Front 700

what is a database master key used for?

Back 700

The database master key is used to protect any certificates, symmetric keys or asymmetric keys that are stored within a database. The database master key is encrypted using Triple DES and User-supplied password. A copy of the database master key is also encrypted using the service master key such that automatic decryption can be accomplished within the instance.

Front 701

What is a service master key.

Back 701

each instance of SQL Server has a service master key that is generated automatically the firs time the instance is started. SMK's are symmetric keys generated from the local machine key and encrypted using the SQL Service account by the Windows Data Protection API. By default The service master key is used to encrypt any database master key that is created within the instance.

Front 702

what is the first layer of security within a database that you should plan for and take advantage of?

Back 702

Schema

Front 703

What is the term given to permissions on the objects within an instance or database?

Back 703

Securables.

Front 704

What other roles are members of the sysadmin role automatically added to?

Back 704

They're automatically members of the db_owner role for every database within the instance.

Front 705

What provides the basis for the encryption hierarchy in SQL Server?

Back 705

Master Keys

Front 706

What provides the containers that own all objects within a database?

Back 706

Schemas

Front 707

Why would SQL Server introduce the concept of a schema?

Back 707

It allows you to drop a user from the database without affecting the names of objects or applications that use the object. Schemas are the only objects that are directly owned by the object - so to drop a user that owns a schema -- you must first change the ownership of the schema to another user.

Front 708

Important: Master Keys

Back 708

A database master key is required to create a certificate that is the basis of a signature.

Front 709

Important: Object Owner

Back 709

Although schemas contain all objects within a database, SQL Server considers the owner of the schema to be the owner of every object within the schema when determining ownership claims.

Front 710

Important: Permission Scope

Back 710

You can assign permissions to any securable. By using higher-level containers such as databases and schemas, you can assign permissions very flexibly. Although you can assign all permissions directly to the lowest-level objects, if a user needs the same permission to access all objects in a schema or database, you can replace dozens or even thousands of separate permissions by granting the permission on the schema or database instead.

Front 711

Chapter 11: Lesson 5: Auditing SQL Server Instances

Back 711

xx

Front 712

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

Back 712

DDL Triggers can audit any DDL command. If the DDL command executes within a transaction, a DDL trigger can be used to rollback the ddl and prevent the change from occurring.

Front 713

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

Back 713

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

Front 714

D. Server audit specification."

Back 714

"Correct Answer: C

Front 715

D. Server audit specification"

Back 715

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

Front 716

Exam Tip

Back 716

An important feature of DDL triggers is the ability to roll back an action. The Policy-Based Management Framework creates DDL triggers for all policies that you configure to prevent an out-of-compliance situation.

Front 717

Lesson Summary:

Back 717

"* DDL Triggers can be created to fire when specific DDL events or events within a group are executed

Front 718

Chapter 11: Lesson 6: Encrypting Data

Back 718

xx

Front 719

Quick Check: What do you need to do to a hash algorithm to increase the complexity when the range of possible encryption value is so small?

Back 719

If the range of possible values to encrypt is small, you need to salt the hash value in order to default brute force attacks.

Front 720

Quick Check: What object is required to implement TDE?

Back 720

You must create a certificate in the master database that is used to encrypt the database encryption key.

Front 721

Create an asymmetric key in the database that is use to encrypt the data."

Back 721

"1. Correct Answer: B

Front 722

D. Create an asymmetric key in the database that is used to encrypt the data."

Back 722

"1. Correct Answer: B

Front 723

Exam Tip:

Back 723

You must back up the certificate used for Tde and store the backup in a safe location. After you encrypt it, you cannot access your data without the certificate.

Front 724

Lesson summary: Chapter 11; Lesson 6: Encrypting Data

Back 724

"* Data can be encrypted within tables using a hash algorithm, a passphrase, a symmetric key, an asymmetric key or a certificate

Front 725

Asymmetric Key

Back 725

A key that conforms to the X.509 standard which can be used to encrypt data within a column

Front 726

Certificate

Back 726

A key that conforms to the X.509 standard used to encrypt data in a column as well as a database encryption key for transparent data encryption. Certificates can be backed up and restored, making them more portable than an asymmetric key.

Front 727

Database audit specification

Back 727

Audit definition that applies at a database scope.

Front 728

Database Master Key

Back 728

Master key for the database that forms the root of the database encryption hierarchy.

Front 729

DDL Trigger

Back 729

Code that executes when a DDL statement, such as CREATE, DROP or ALTER is executed.

Front 730

Fixed Database Role

Back 730

Role within a database included with SQL Server that you cannot change the security definition of.

Front 731

Fixed Server Role

Back 731

Role at an instance level that is included with SQL Server that you cannot change the security definition of.

Front 732

Hash Algorithm

Back 732

A one-way algorithm that encrypts data, but does not allow for decryption.

Front 733

Impersonation

Back 733

The act of assuming the security credentials of another instance or database principal. Logins/Database users that are mapped to a certificate or asymmetric key cannot be impersonated.

Front 734

Loginless user

Back 734

A database user that is not mapped to a login.

Front 735

Ownership chain

Back 735

The list of owners within a module execution claim.

Front 736

Principal

Back 736

1. An object that can own other objects within an instance. The SQL Server object that defines a security access path for a user. (2) The database within a database Mirroring session that is recovered, is online, and allows transactions to be processed against it.

Front 737

Salting

Back 737

Adding a value to a piece of data prior to applying a hash algorithm as a means to obfuscate the data and deter a brute force attack.

Front 738

Securable

Back 738

An object within an instance or database that permissions can be granted on.

Front 739

Server Audit

Back 739

Audit object that defines the location and logging properties for any audit specifications that are defined.

Front 740

Server Audit specification

Back 740

Audit definition that applies at a server scope.

Front 741

Service master key

Back 741

The symmetric key for the instance that defined by the service account and used as the root of the encryption hierarchy within an instance.

Front 742

Signature

Back 742

A digital signature that is applied to a code module allowing an escalation of permissions only when a module is being executed.

Front 743

Symmetric key

Back 743

A two-way key encryption algorithm that uses a single key to both encrypt and decrypt data

Front 744

TCP endpoint

Back 744

The endpoint transport that enables Transact-SQL, Database Mirroring, and Service Broker communications within SQL Server.

Front 745

Of all of the different encryption methods, which provides the best balance between securing the data and performance?

Back 745

Symmetric Key

Front 746

What is the biggest drawback to encrypting a column/

Back 746

It cannot be indexed and you can't search on the column.

Front 747

What is the strongest of the Hash Algorithms?

Back 747

MD5

Front 748

Important: transmitting and storing passwords

Back 748

It is a common misconception that passwords are sent to SQL Server in plaintext and that SQL Server decrypts the password stored to verify if the submitted password matches. SQL Server uses an MD5 hash to handle passwords. When a password is specified for an object, SQL Server applies an MD5 hash and stores the hash value. When you specify a password to access an object, the password is hashed using the same MD5 hash, the hashed password is transmitted in a secure channel and the hash value is compared to the hash stored value. Even an administrator who is running a trace cannot access the password.

Front 749

Chapter 12: Lesson 1: Working with System Monitor

Back 749

xx

Front 750

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

Back 750

You can capture object, counters, and counter instances.

Front 751

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

Back 751

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

Front 752

Quick Check: What types of data can System Monitor capture?

Back 752

System monitor captures numberic data for performance counters that are defined for hardware or software components.

Front 753

D. The amount of time that a processor is in use"

Back 753

"A. Correct: The System:Processor Queue Length indicates the number of processes at a machine level that are waiting for a processor to be allocated.

Front 754

Exam Tip:

Back 754

For the exam, you need to know what various performance counters are for as well as be able to select the appropriate counters to capture to diagnose problems within an instance..

Front 755

Lesson Summary:

Back 755

"* System monitor is used to capture numeric statistics about hardware and software components.

Front 756

Best Practices: Capturing Counter Logs

Back 756

Viewing counters in a graphical interface provided by system monitor is useful only for looking at the immediate state of a system. It is much more useful to capture counters to a log to be used later for analysis. When setting up counter logs, it is recommended that you select counter objects instead of individual counters to ensure you have captured everything necessary for analysis.

Front 757

Best Practices: Specifying an account for the counter log

Back 757

At the bottom of the counter log definition screen you can specify the security credentials that the counter log runs under. You should always configure a counter log to run under a specific account with sufficient permissions to access the applications for which you re gathering counters. Failure to define a specific account is the most common cause of a counter log failing to start. the second most common causes of a counter log failing to start are password expiration, a locked out account, or a deactivated account.

Front 758

What are the 3 types of counters in system monitor?

Back 758

Object, counter and instance.

Front 759

Chapter 12: Lesson2: Working with the SQL Server Profiler

Back 759

xx

Front 760

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

Back 760

You define events, data columns, and filters within a trace.

Front 761

Quick Check: Which events are commonly used to establish a performance baseline?

Back 761

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

Front 762

D. Profiler"

Back 762

"1. Correct Answers: A and D

Front 763

Exam Tip:

Back 763

You need to know which events are used to solve various problems. For example, resolving deadlocks, blocking or stored procedure performance.

Front 764

Lesson Summary: Working with the SQL Server Profiler

Back 764

"* Profiler is the utility that allows you to interact graphically with the SQL Trace API

Front 765

Best Practices: Logging a Trace to a File

Back 765

Although SQL Server is optimized to handle large volumes of data changes, the SQL Trace application programming interface (API) can produce enough events to overwhelm even a server running SQL Server. You should never log trace events to the same instance for which you are capturing events. Because you can possibly overwhelm a server running SQL Server when you are logging events, the best logging solution is to log trace events to a file and then later import the file into the server for analysis.

Front 766

Best Practices: Maximum Trace File Size

Back 766

Setting the maximum file size to 50 MB provides a good trade-off for managing the size and number of trace files. A 50-MB file is small enough to copy or move quickly across any network while also containing a large enough set of events within a single file for analysis.

Front 767

Best Practices: Running Trace Automatically

Back 767

Although you can use Profiler to gather quick traces against an instance, it is much more common to set up an manage traces using code. By running traces via code, you eliminate all the overhead of a graphical tool while also providing a means of unattended tracing through the use of jobs within SQL Server Agent.

Front 768

What are the five most coming used event groups in SQL Server Profiler?

Back 768

Locks, Performance, Security Audit, Stored Procedures, and TSQL.

Front 769

Caution: Correlating a counter log to a trace file

Back 769

You can correlate a counter log with a trace file only if you have captured the StartTime data column in the trace.

Front 770

Caution: Returning results into a Grid

Back 770

Many organizations install the client tools on the server when SQL Server is installed. Although the installation of tools provides utilities for querying and troubleshooting an instance from the server console, you have to account for the overhead of the tools. Profiler can capture a very large number of events in a short amount of time and when loaded into the grid within Profiler can require the use of a large amount of memory. Although grids can present information in an easy-to-understand format, a grid has much more overhead than text-based format.

Front 771

Chapter 12: Lesson 3: Diagnosing Database Failures

Back 771

xx

Front 772

Quick Check: What are the main places to find error and informational messages about the database engine?

Back 772

you can use the SQL Server error log and Windows Application Event log for messages about the database engine. If you are auditing logins and logouts from the instance, you can use SQL Server error log and Windows Security log. The Windows System Event log can also provide hardware and operating system information useful to troubleshooting a database engine issue.

Front 773

Quick Check: What are the three error codes that are raised when you run out of space for a database?

Back 773

You receive a 9002 error when you run out of transaction log space and either an 1101 or 1105 error when the data files are out of space.

Front 774

D. Killed process"

Back 774

"Correct Answers: A and D

Front 775

Exam Tip

Back 775

For the exam, you should know common error codes for space issues, what each error means, and how to fix the problems which generated the errors.

Front 776

Lesson Summary: Diagnosing Database Failures

Back 776

"* SQL Server error log contains configuration information about instance startup, errors, stack dumps, and informational messages about your instance.

Front 777

Best Practice: Out of Space Errors

Back 777

Running out of space is a serious issue that should be avoided at all costs. Most database administrators (DBA's react to problems. Instead, you should be proactively managing your instances to ensure that you do not run out space. You can proactively manage space by creating alerts to notify an operator when the amount of free space has fallen below a specified threshold, usually 10 percent to 15 percent. You can also be notified immediately if a space error occurs by creating alerts in SQL Server Agent.

Front 778

Note: Increasing Transaction Log Space

Back 778

If a transaction log backup does not free up enough space in the log to be reused, then you need to add space to the transaction log by increasing the disk space available. The most common way to increase the disk space available is to add a second log file to the database on a disk volume that has free space.

Front 779

Tip: File Growth

Back 779

Before adding files to filegroups, you should first check if the data files have the auto-grow feature disabled. If auto-grow is disabled and the disk volume still has space, you can increase the space available just by increasing the size of the existing files.

Front 780

Inside of Windows Event Logs, what are the three different event logs?

Back 780

1. System Event log 2. Application Event logs 3. Security Event log

Front 781

What are the four type of logs where you can find error and informational messages related to your server running SQL Server?

Back 781

1. Windows Event logs 2. SQL Server error logs 3. SQL Server Agent logs 4. Database mail logs.

Front 782

What is the most common errors that you encounter?

Back 782

Running out of space for either the data or log files.

Front 783

What's the first thing you should do whenever a transaction log fills up?

Back 783

execute a transaction log backup.

Front 784

Which system db is a special case that needs to be closely watched?

Back 784

tempdb. Running out of space in tempdb causes serious problems for a variety of processes. If tempdb runs out of space, you can affect every database on an instance.

Front 785

Chapter 12: Lesson 4: Diagnosing Service Failures

Back 785

xx

Front 786

Quick Check: Errors in which three databases prevent SQL Server from starting?

Back 786

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

Front 787

Quick Check: What does a device a activation error means?

Back 787

A device activation errors means that SQL Server either cannot find or cannot access a data or log file.

Front 788

D. The SQL Server service account was locked out."

Back 788

"1. Correct Answer: B

Front 789

Exam tip:

Back 789

You should focus on the most common error scenarios, most of which deal with security permissions. You need to know which utilities to use to troubleshoot the errors. You should also know how to rebuild a master database in SQL Server 2008, which is accessed using the new Installation Center even though it still uses setup. Favorite questions for the exam test whether you know the "new" or "improved" way of performing an action in the current SQL Server version vs. the method(s) from the previous version.

Front 790

Lesson Summary: Diagnosing Service Failures

Back 790

"* SQL Server Configuration Manager is used to configure and manage services, protocols, and the SQL Native Client

Front 791

Best Practices: Managing SQL Server Services

Back 791

It is always much easier to remember and enforce administration policies that do not have a lot of exceptions. You can safely change some of the options for SQL Server services using the Windows Services console; however, others cannot be safely changed this way. Therefore, most environments that i have worked in dictate that all changes to a Sql server service must be made using the Sql server configuration manager.

Front 792

Note: Device Activation Errors

Back 792

Anytime you see a device activation error, SQL Server could not access a data or log file for a database. Device activation errors for master and tempdb prevent the instance from starting, whereas device activation errors for any other database only make the database unavailable. You should always investigate any device activation error because you either have a failing disk subsystem or an administrator is improperly shutting down a storage system while it is being used.

Front 793

Note: Experienced SQL Server DBAs

Back 793

Beside the additional years in the job of DBA, the biggest thing that separates someone with experience from someone without experience is that the experience person has managed to survive things that have gone wrong. I'll never admit many of the things I've managed to survive over the decades. By following many of the best practices, sidebars, notes, and cautions you will find in this book, I hope that you can avoid many of the mistakes I and many others have mode over the years.

Front 794

Note: Startup Mode

Back 794

A service with a startup mode of Disabled shows as other in the start mode column in the SQL Server Configuration manager main window.

Front 795

If a SQL Server service is in a stopped stop, where can you look to see what happened?

Back 795

SQL Server error log and Windows application event log

Front 796

if SQL Server starts and then immediately shuts down -- what are you most likely dealing with?

Back 796

An issue with the SQL Service account -- Account deleted, locked out, disabled or expired.

Front 797

If you cannot connect to your SQL Server instance, what is the first thing you do?

Back 797

Check to see whether the SQL Server is running? If the service is in a starting state, SQL Server is in the process of starting up and should be available in a short amount of time. If is stopped -- that's a problem too.

Front 798

If you still can’t get the service only after verifying the SQL Server Service account and the disk permissions -- then what should you look at?

Back 798

Check to see if the startup parameters were changed improperly.

Front 799

If you've checked all the SQL Server Service accounts issue and they're good, but the SQL Server is shutting down right after starting -- what is the next thing to check?

Back 799

It's most likely a permissions issue. When SQL starts up, the master database is first brought online and then the tempdb is re-created. The tempdb database causes a write to occur to the folder that store the data and log files for the tempdb database. if SQL Server cannot re-create the tempdb, the service shuts down.

Front 800

Lastly -- if you still can't get the database up after checking the service account, checking the storage permission and the Startup parameters -- then what do you do?

Back 800

You should start repair Wizard -- you have a corrupt master database.

Front 801

Caution: SQL Server Clustered instances

Back 801

The start mode for all SQL Server services for a clustered installation should be set to Manual. Do not change the start mode for any services in a clustered installation. The services are being controlled by the cluster service, and setting any of the services to an Automatic start mode creates significant problems on a cluster if something restarts.

Front 802

Caution: System Databases

Back 802

SQL Server has four system databases on every instance; master, model, msdb and tempdb. If you have configured replication, you also see a database named distribution. SQL Server has a sixth system database, first introduced in SQL Server 2005, named mssqlsystemresource. The mssqlsystemresource database contains most of the stored procedure, function, DMB, and other code that ships with SQL Server. The mssqlsystemresource database is critical to SQL Server operations and prevents a server running SQL Server from starting. Unfortunately this database is hidden. So, you need to look for device activation errors related to the mssqlsystemresource database as well as the master and tempdb database.

Front 803

Chapter 12: Lesson 5: Diagnosing Hardware Failures

Back 803

xx

Front 804

Quick Check: What errors do you see if there is a fault in either the memory or processor?

Back 804

If you are encountering memory or process problems, you see STOP errors. If there is a memory error encountered when the computer is booting, you see a POST error. Both STOP and POST are accompanied by a blue screen with additional diagnostic information.

Front 805

Quick Check: What errors do you see if your disk storage goes off-line underneath a database?

Back 805

When a disk volume that databases are stored on goes off-line, SQL Server begins logging device activation errors.

Front 806

D. You have a bad memory module in the server"

Back 806

"Correct Answer: C

Front 807

Exam Tip:

Back 807

For the exam, you need to know the most common errors related to the failure of hardware components.

Front 808

Lesson Summary: Diagnosing Hardware Failures

Back 808

"* A severe failure of the disk system that takes a storage volume off-line logs device activation errors and the affected databases become inaccessible.

Front 809

If the errors get sever enough or a volume or an entire array to go off-line -- where would you being to see device activation errors?

Back 809

SQL Server Error log as well as the Windows Application Event Log.

Front 810

If the storage for your database is locally attached -- what can you use to determine the state of disk volumes?

Back 810

Disk Management folder within the Computer Management Console.

Front 811

What is the first indication that you have of a failure in the disk system?

Back 811

Errors logged in the Windows System Event Log or within the logging system for your Storage Area Network (SAN) or Network Attached Storage (NAS) array.

Front 812

What is the first indication that you normally have for memory or processor issues?

Back 812

When a stack dump is generated.

Front 813

What is your first line of defense against disk failure in your SQL Server setup?

Back 813

Your disks should always be in a RAID. Ideally, you'd have an extra disk next to the RAID array, so that when if one disk in the array were to fail, you could take it out, put in the spare disk and spin the whole system back up.

Front 814

Where will you see an error for a stack dump denoted?

Back 814

In the SQL Server error log.

Front 815

CAUTION: S.A.N. and N.A.S. Arrays

Back 815

When your databases are stored on SAN or NAS arrays, you should always use the specialized utilities that ship with your storage array to diagnose and repair any disk errors.

Front 816

Chapter 12: Lesson 6: Resolving Blocking and Deadlocking Issues

Back 816

xx

Front 817

Quick Check: 1. What are the most common lock modes and types that are available?

Back 817

1. The three most common locking modes are shred, exclusive and update. The three lock types are row, page and table.

Front 818

Quick Check: 2. How does a deadlock occur?

Back 818

2. A deadlock requires at least two processes that are both modifying data. Each process acquires an exclusive lock on a resource and then attempts to acquire a shared lock on the same resource exclusively locked by the other process.

Front 819

Quick Check: What are the valid input sources for DTA to analyze?

Back 819

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

Front 820

D. Sp_who2 system stored procedure"

Back 820

"1. Correct Answers: B and D

Front 821

Exam Tip

Back 821

For the exam, you need to know the locks that can be acquired and how lock escalation can lead to either blocking or deadlocking issues. If a block or deadlock occurs, you also need to know how to resolve the problem.

Front 822

Exam Tip:

Back 822

You need to know how each of the tuning options affect the recommendations that DTA makes.

Front 823

Lesson Summary: Chapter 12: Lesson 6: Resolving Blocking and Deadlocking Issues

Back 823

"* The Lock Manager is resonsible for managing the locks that SQL Server uses to maintain data consistency while allowing multiple users to manipulate data concurrently.

Front 824

Best Practices: Handling Deadlocks

Back 824

Deadlocks are transient situations basically caused by bad timing. If the queries that the two sessions were running completed in less time, the deadlock might have been avoided. Likewise, if one process ad been started a small amount of time later, the deadlock might never have occurred. Because a deadlock is a transient locking conflict state, your applications should be coded to detect a 1205 error and then immediately reissue the transaction because it is a very strong possibility that the process will not deadloc the second time the command is executed.

Front 825

Note: Lock escalation

Back 825

It is very common misconception that SQL Server promotes row locks to page locks. Row locks are promoted only to table locks.

Front 826

Counter log

Back 826

A set of numeric values corresponding to various properties of hardware and software that can be captured using System Monitor.

Front 827

Deadlock

Back 827

A transient situation that occurs when two processes that are attempting to modify data acquire competing locks in such as way that does not allow either process to complete.

Front 828

Isolation level

Back 828

Locking behavior definted by the American National standards Institue (ANSI) which SQL Server uses to determine how the Lock Manager hsould behave

Front 829

Lock escalation

Back 829

The process by which the Lock Manager promotes a lock. Locks can be promoted from row to table or from page to table.

Front 830

SQL Trace

Back 830

The event API that SQL Server exposes to allow you to capture information about the opertional state of an instance.

Front 831

Trace Event

Back 831

An action that is executed within SQL Server that is exposed via the SQL Trace API. Examples of events are a file growing, query being executed, or a connection to the instance.

Front 832

Can you have multiple exclusive locks on the same data?

Back 832

No. As the name implies, you can have only one exclusive lock on a resource at a time, and all other process needing to access the resource must wait until the exclusive lock as been released.

Front 833

Can you have multiple read locks on the same data?

Back 833

Yes.

Front 834

Describe the Transaction Isolation Level of READ COMMITTED

Back 834

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

Front 835

Describe the Transaction Isolation Level of READ SERIALIZE

Back 835

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

Front 836

Describe the Transaction Isolation Level of READ UNCOMMITTED

Back 836

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

Front 837

Describe the Transaction Isolation Level of REPEATABLE READ

Back 837

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

Front 838

Describe the Transaction Isolation Level of SNAPSHOT

Back 838

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

Front 839

How does Lock Manager determine which type of lock to acquire?

Back 839

It uses distribution statistics.

Front 840

How does SQL Server Lock Manager determine if a lock is to be acquired on a page?

Back 840

According to the two percent rule -- which is designed to minimize the number of locks needing to be acquired and managed -- if SQL Server determines that more than two percent of the rows on a page will need to be accessed, a page lock will be acquired.

Front 841

How does SQL Server Lock Manager determine if a lock is to be acquired on a table?

Back 841

If more than 2 percent of the pages of a table will need to be accessed, a table lock will be acquired.

Front 842

In most locking situations in SQL Server -- what are the locking modes you deal with?

Back 842

"1. Shared

Front 843

What are Isolation Levels?

Back 843

Isolation Levels affect the way SQL Server handles transactions, as well as the duration of locks acquire.

Front 844

What are the five isolation levels that SQL Server has.

Back 844

"1. READ UNCOMMITTED

Front 845

What are the scopes that locks have?

Back 845

"1. Session

Front 846

What are the three different lock types that can be acquired?

Back 846

"1. Row

Front 847

What if the distribution statistics by Lock Manager are inaccurate, or if there are no distribution statistics?

Back 847

Lock Manager can promote locks to table level locks. IE. Row lock to table lock, Page lock to Table lock.

Front 848

What is a shared lock?

Back 848

It is a lock acquired for read operations to prevent the data being read from changing during the read.

Front 849

What is an exclusive lock?

Back 849

It is a lock acquired on a resource that is being modified and is held until the modification is complete.

Front 850

What is an update lock?

Back 850

An update lock is a hybrid of a shared and exclusive lock. Although an update lock is acquired for any update, update locks can be acquired during any action that requires SQL Server to first locate the piece of data to be modified. An update lock starts out by acquiring a shared loc on resource until it finds the piece of data that needs to be modified, the shared lock is then changed to be an exclusive lock while the data is being changed.

Front 851

What is Lock Manager?

Back 851

An internal process that determines the appropriate lock to acquire, how long to retain the lock and arbitrates when processes are allowed to modify data such that read are always consistent.

Front 852

What mechanism does SQL Server use to maintain data consistency for multiuser access?

Back 852

locking

Front 853

What type of algorithm is the Lock Manager use to determine the order in which to execute locks?

Back 853

FIFO algorithm -- first in, first out. Each process that executes a command needs to acquire a lock. The locks being requested are queued up by the Lock Manager in the order that the request is made. So long as the requested resource does not have a lock or has a lock that does not conflict with the lock being requested, the Lock Manager grants the lock request.

Front 854

Why?

Back 854

Because read operations cannot introduce data inconsistencies, you can have multiple shared locks on the same resource at the same time.

Front 855

Quick Check: 1. What are the objects that are used for a Resource Governor implementation?

Back 855

1. Resource Governor relies on a user-defined classifier function in the master database to assign a connection to a workload group. Each workload group is assigned to a resource pool that manages CPU and memory resources.

Front 856

Quick Check: 2. What resources can Resource Governor control?

Back 856

2. Resource Governor can be used to manage CPU and memory resources.

Front 857

D. A trace file containing SP:StmtCompleted events"

Back 857

"Correct Answers: A, C, and D

Front 858

Exam Tip:

Back 858

You need to know the resources that Resource Governor can control as well as how to test and trouble shoot a classification function.

Front 859

Lesson Summary: Chapter 13: Lesson 1: using the Database Engine Tuning Advisor

Back 859

"* DTA is used to analyze a query workload against a database to make recommendations on structures to create or drop, which might improve performance.

Front 860

Note: Connection Classification

Back 860

Classification occurs at the time a connection is created. Therefore, the only way you can limit resources is based on properties of the connection. You cannot limit the resource consumption of individual queries or even types of queries.

Front 861

Note: Default Resource Pools

Back 861

If a classifier function is not associated to Resource Governor, or the classifier function does not exist, returns NULL, or returns a nonexistent workload group, the user session is associated to the datault resource pool.

Front 862

Note: Resource Allocation

Back 862

All connections running within a resource pool are treated with equal weight, and SQL Server balances the resources available to the resource pool across all requests currently executing within the pool.

Front 863

Caution: Limiting Resources

Back 863

Even with Resource Governor enabled, SQL Server still seeks to maximize the resources available to all concurrently executing requests. If you set a maximum limit for a resource pool, connections assigned to the resource pool can use more resources than the configured maximum. If other sessions that are executing do not need all the resources, any amount of free resource is allowed to be used by any session, even if that causes the session to exceed the resource limits of its assigned resource pool.

Front 864

Lesson Summary: Chapter 13: Lesson 2: Working with Resource Governor:

Back 864

"* Resource Governor is used to limit the CPU, memory, or both allocated to one or more connections.

Front 865

Quick Check: 1. What is the difference between sys.dm_db_index_operational_stats and sys.dm_db_index_physical_stats?

Back 865

1. Sys.dm_db_index_physical_stats returns fragmentation statistics for each index, and sys.dm_dm_index_operational_stats returns locking, latching and access statistics for each index.

Front 866

Quick Check: 2. Which DMV can you use to retrieve execution statistics for each connection currently executing a command?

Back 866

2. The sys.dm_exec_requests DMV returns one row for each currently executing command.

Front 867

D. Sys.dm_db_missing_index_details"

Back 867

"1. Correct Answer: C

Front 868

D. Limit the memory utilization for marketing users."

Back 868

"1. Correct Answer: B

Front 869

Exam Tip:

Back 869

For the exm, you need to know the purpose of the main set of DMVs and how to use each of them to diagnose and troubleshoot performance issues.

Front 870

Lesson Summary: Chapter 13: Lesson 3: Using Dynamic Management Views and Functions.

Back 870

"* The sys.dm_db* DMVs provide general space and index utilization information.

Front 871

Terminology Conventions:

Back 871

For simplicity, the entire set of instrumentation code that is available within SQL Server is referred to collectively as DMV's, regardless of whether you are using a view or a function.

Front 872

More Info: Wait Types

Back 872

Although wait statistics are an extremely valuable pice of information for diagnosing performance issues, in the almost 10 years since detailed information has been available, Microsoft still has not documented the wait types, all of which have been extremely cryptic names. The best resource available for understanding wait types and how to resolve issues uncovered by wait types.

Front 873

Chapter 13: Lesson 4: Working with Performance Data Warehouse

Back 873

xx

Front 874

Quick Check: 1. Which features is the Performance Data Warehouse based on?

Back 874

1. The Performance Data Warehouse is built upon the Data Collector infrastructure. Data collection is based on SSIS packages and SQL Server Agent jobs.

Front 875

Quick Check: 2. What collector types are available in SQL Server 2008?

Back 875

2. SQL Server 2008 ships with T-SQL Query, SQl Trace, Query Activity, and Performance Counter collector types.

Front 876

D. Implement Microsoft System Center Operations Manager 2007."

Back 876

?

Front 877

Exam Tip:

Back 877

For the exam, you need to know what the purpose of the Performance Data Warehouse is, the components that data collection is based upon, and the information that can be collected.

Front 878

Lesson Summary: Chapter 13: Lesson 4: Working with Performance Data Warehouse

Back 878

"* The Data Collector is a new infrastructure component available in SQL Server 2008 that is based on SSIS packages and SQL Server Agent jobs.

Front 879

Classification function

Back 879

A function in the master database that assigns a workload group to each connection.

Front 880

Collection item

Back 880

A query or performance counter that you want to track in the Performance Data Warehouse

Front 881

Collection set

Back 881

A group of collection items that are combined together and managed by a Data Collector

Front 882

Collection target

Back 882

The instance, database or objct that a collection set is executed against.

Front 883

Data collector

Back 883

The Data Collector is a new infrastructure component available in SQL Server 2008 that is based on SSIS packages and SQL Server Agent jobs.

Front 884

Data provider

Back 884

the connection libraries used by the Data Collector to connect to collection targets.

Front 885

Dynamic Management Function (DMF)

Back 885

A function which ships with SQL Server that provides configuration, object or diagnostic information.

Front 886

Dynamic Management View (DMV)

Back 886

A view which ships with SQL Server that provides configuration, object, or diagnostic informaiton.

Front 887

Resource Pool

Back 887

The object within a Resource Governor configuration that defines the CPU and memory resources that can be allocated by a workload group.

Front 888

Workload File

Back 888

A file or table which contains SQL statements for DTA to analyze.

Front 889

Workload group

Back 889

An object used by Resource Governor to associate a connection to a resource pool.

Front 890

Chapter 14: Lesson 1: Designing Windows Clustering

Back 890

xx

Front 891

Quick Check: 1. What is the main difference between a standard cluster and a majority node set cluster?

Back 891

1. A standard cluster uses a shared quorum database. A majority node set cluster maintains a separate quorum database on each node that is synchronized across all nodes. The majority of nodes (more than 50 percent) must be online for a majority node set cluster to function.

Front 892

Quick Check: 2. What are some examples of cluster resources?

Back 892

2. Cluster resources can be hardware, software, or networking. Some examples are IP addresses, network names, disk mounts, and Windows services.

Front 893

Quick Check: 3. How many network connections does a node need for clustering? Why?

Back 893

3. Each node needs at least two network connections: One connection is used for public communications to applications on the network, and the other is used for private internal communications within the cluster.

Front 894

Quick Check: 4. How does the health check within a Windows cluster work?

Back 894

4. The basic health check that is performed is called a LooksAlive test. This test consists of each node pinging the others.

Front 895

Quick Check: 5. Which types of disk configuration are supported for clustering?

Back 895

5. Clustering supports basic disks. Dynamic disks are not supported. Disks must also be external to each node within the cluster, so disks mounted locally within a cluster are not visible to any resource within a cluster.

Front 896

D. Windows Server 2003 Enterprise Edition"

Back 896

"1. Correct Answer: C

Front 897

D. Windows Server 2003 Enterprise edition with a majority node set cluster"

Back 897

"2. Correct Answer: A

Front 898

D. Terminal Services service"

Back 898

"3. Correct Answer: B

Front 899

Lesson Summary: Chapter 14: Lesson 1: Designing Windows Clustering

Back 899

"* You build a standard cluster using a single quorum database stored on a shared disk array. You build a majority node set cluster with a copy of the quorum database on all nodes within the cluster.

Front 900

Best Practices: Microsoft Distributed Transaction Coordinator (MS DTC)

Back 900

MS DTC, which you need to add to every Windows cluster you build, ensures that operations requring enlisting resources such as COM+ can work in a cluster. It has been recommended that you always configure MS DTC to use a disk that is different from the quorum disk or any disk used by SQl Server or other applications. We find this to generally be a waste of very limited disk resources. If you are running applications in a cluster that make very heavy use of MS DTC, you need to dedicated a disk for MS DTC operations. If you are not running applications that require COM+, you can safely configure MS DTC within the cluster group adn set its dependencies to Quorum drive.

Front 901

Best Practices: Private Network Connection

Back 901

"You should configure the following items on the private network connection:

Front 902

Note: Cluster Analysis Warnings

Back 902

Because of the way Virtual Server handles disk resources internally, you can receive some warnings when a cluster is configured. This is normal and does not affect the operation of your cluster. As long as you do not receive an error (the progress bar turns red), your configuration succeeded, and you have a fully functional cluster.

Front 903

Note: Disk Initialization

Back 903

When you select Disk Management on Node2, the Initialize and Convert Disk Wizard does not appear because the disks already have a signature written to them. You do not need to format the disks because you already performed this step when you configured Node1. You also do not need to specify drive letters because Node2 picks them up from the cluster after you configure it.

Front 904

Note: Local Quorum

Back 904

The location of the quorum is %SystemRoot%\Cluster\QoN.%ResourceGUID%$\%ResourceGUID%$\MSCS. A share is created on each node that is named \\%NodeName%\%ResourceGUID%$. You should not modify this directory or changethe permissions on this directory or share in any way.

Front 905

Note: Operating System

Back 905

Check with your storage area network (SAN) vendor to determine whether your nodes can be booted from the SAN. If your nodes cannot be booted from the SAN, or if you are using direct attached storage, you must install the operating system on an internal hard drive that you use to boot the node. Installing the operating system on an internal hard drive on each node is the most common configuration.

Front 906

Note: Remote Procedure Call (RPC)

Back 906

All health checks within a cluster use the remote procedure call (RPC) service. If the RPC service is unavailable or has been disabled, all health checks within a cluster fail. You must ensure that the RPC service is enabled and set to start automatically on all nodes within a cluster.

Front 907

Note: SCSI adapter ID for node2

Back 907

Each node must use a different SCSI adapter ID. Because Node1 is configured with a SCSI adapter ID of 6 for each SCSI adapter, you must configure Node2 with a SCSI adapter ID of 7 for each node.

Front 908

Note: Specifying a Quorum

Back 908

When you configure a cluster on physical hardware, the disk that the New Server Cluster Wizard selects by deafault as the quorum is the first disk added to Node1 that is not a locally attached disk. Virtual Server selects the first drive letter in alphabetical order. You can use the Cluster Configuration Quorum dialog box to specify a local quorum that is used when building a single node cluster for testing. This dialog box is also wher eyou can change the type of lcuster from teh standard cluster you are building to a majority node set cluster by choosing Majority Node Set from the drop down list. If you chose Majority Node Set form this drop-down list, the New Server Cluster Wizard creates a quorum database on each node in the cluster.

Front 909

Note: Virtual Server

Back 909

You can use Virtual Server and Microsoft Virtual PC to simulate hardware configurations. Unlike Virtual PC, Virtual Server supports Windows clustering and you can use it to build a SQL Server failover cluster.

Front 910

Caution: Antivirus Software

Back 910

Antivirus software ahs become very prevalent on database servers. In a cluster environment, you need to configure the antivirus scanning so that it does not interfere with cluster operations. You must exclude the MSCS directory and all the directories containing data files from scanning. During a failover, the disks are mounted on the node that a group is failing over to, which triggers the antivirus software to begin scanning the disk. If the antivirus software begins scanning a database file before SQL Server can open it, the recovery of the database is delayed until the file has been fully scanned. Because database files are normally very large, scanning can add a considerable amount of time to the failover process.

Front 911

Caution: Basic Disks

Back 911

Follow the prompts in the dialog box to set up the disks. Make absolutely certain that you do not convert the disks to dynamic. Clustering supports only basic disks; if you convert the disks to dynamic disks, you cannot configure your cluster and will have to start at the beginning with new disks.

Front 912

Caution: Encrypted Operating System

Back 912

To support encryption of the file system in a cluster configuration, Kerberos must be enabled, and the computer accounts, along with the cluster service account, must be trusted. If you choose to encrypt the file system, you must also account for the performance degradation that all read and write operations incure because of encrypt/decrypt processes.

Front 913

Caution: Initial Configuration

Back 913

It is critical that you be very careful with the order in which you start and stop Node1 and Node2 during the subsequent steps in this practice. If you ever run both Node1 and Node2 at the same time, before you configure the cluster you will corrupt the disks adn not be able to complete the steps. You must check and double-check the state of Node1 and Node2 before stopping or starting either one.

Front 914

Important:

Back 914

A complete discussion of Virtual Server is beyond the scope this book. You can find step-by-step instructions for performing each of the actions required to configure the base environment in the Virtual Server documentation. If you have physical hardware capable of clustering, you can perform the practices on this hardware by skipping the steps specific to configuring the Virtual Server environment.

Front 915

Important: Compatible hardware

Back 915

The most frequent cause of autages for a cluster is hardware that has not been certified for clustering. To ensure that the hardware you are deploying is certified for clustering, it must appear in the Windows Catalog. The entire hardware solution must specifically designate that it is certified for clustering, so you need to ensure that you check the clustering categories of the Windows Catalog (which can be found at www.microsoft.com/whdc/hcl/default.mspx).

Front 916

Important: Lesson Practices

Back 916

You use Virtual Server for all the practices in this chapter. To follow the steps in the practices, you have to create three virtual machines using Virtual Server, and you must install all three machines ith Windows Server 2003 Standard edition SP2 and later or Windows Server 2008 Standard and later. You should configure one of the virtual machines as a domanin controller, and the other two machines as member servers in the domain. You need to allocate 512 MB of memory to the two virtual machines that you configure as member servers, and configure the domain controller with 192 MB of RAM. To meet the hardware requirements for this Virtual Server configuration, you need a minimum of 1.5 GB of RAM on the host machine, and the disk drives should be at least 7200 RPM for reasonable performance.

Front 917

More Info: Windows Server Versions

Back 917

At the time of writing, Windows Server 2008 was just being released onto the market. The exercises in this chapter, as well as detailed Windows clustering information, are based primarily on Windows Server 2003, with information from Windows Server 2008 incorporated where available. If you are deploying Windows Server 2008, please refer to the Windows Server 2008 documentation for details on clustering features.

Front 918

Chapter 14: Lesson 2: Designing SQL Server 2008 Failover Cluster Instances

Back 918

xx

Front 919

Quick Check: 1. Which types of Windows accounts and groups can you use with a SQL Server cluster instance?

Back 919

"1. Domain users and domain groups must be used with SQL Server failover cluster instances. The SID for accounts and groups used must be resolvable across all nodes in the cluster. The SID for a local account or group cannot be resolved across machines.

Front 920

Quick Check: 2. With how many clustered instances can a single drive letter be used?

Back 920

2. Although a clustered instance can address multiple drive letters, you can configure a given drive letter for only a single instance. This configuration prevents the possibility of having SQL Server running on one node while a different node has ownership of the disk resources required by the clustered instance.

Front 921

Quick Check: 3. What are the two health checks performed in a cluster, and which operations are executed?

Back 921

3. The LooksAlive check executes every 5 seconds by default and issues a ping from all nodes to the IP address of the SQL Server clustered instance. The IsAlive check executes every 60 seconds by default, connects to the SQL Server clustered instance, issues SELECT@@SERVERNAME, and must receive a valid result set.

Front 922

D. Database snapshots"

Back 922

"1. Correct Answer: C

Front 923

D. Replication"

Back 923

"1. Correct Answers: B and D

Front 924

D. Windows 2003 Enterprise edition"

Back 924

"2. Correct Answer: D

Front 925

D. Replication"

Back 925

"2. Correct Answer: D

Front 926

D. Windows Server 2003 Datacenter edition with SQL Server 2008 Datacenter"

Back 926

"3. Correct Answer: A

Front 927

Lesson Summary:

Back 927

"* You can configure SQL Server as either single or multiple-instance clusters.

Front 928

Best Practices: Balancing Security with Manageability

Back 928

Security best practices woud create a domain-level group for each type of service and for each SQL Server clustered instance installed. Management simplicity would create a domain-level group for each of the three services, and all SQL Server failover cluster instances wouldspecify the same set of domain groups. You need to determine where to balance a very secure (but highly complex) domain group scheme with a less complex (but less secure) domain group scheme.

Front 929

Best Practices: SQL Browser Service

Back 929

SQL Server 2008 installs a service called SQL Broweser. If you have installed named instances in a cluster, the SQL Browser service must be running to resolve these names. If you do not have named instances, you should disable the SQL Browswer service.

Front 930

Note: Prerequisites

Back 930

You must have already installed .NET Framework 2.0 SP1 on both nodes in the cluster before proceeding with this practice. If you are configuring a Windows Server 2003 cluster, you also need to download and install the KB937444 hotfix.

Front 931

Cluster group

Back 931

A container that combines multiple cluster resources into a logical management unit.

Front 932

Cluster name

Back 932

The DNS resolvable name of the cluster

Front 933

Cluster node

Back 933

A piece of hardware that is capable of running a Windows cluster. It can be a physical server that is listed in the clustering section of the Windows Catalog, or a machine partition within an Enterprise-level piece of hardware (for example, Unisys ES7000)

Front 934

Cluster resource

Back 934

A hardware, software, or network resource that can be configured and managed within a cluster.

Front 935

Majority node set cluster

Back 935

a cluster that stores a local copy of the quorum database on each node in the cluster. This type of cluster is supported only on Windows Server 2003 and later.

Front 936

Quorum database

Back 936

Contains all the operational configuration information for the cluster. Also called Quorum.

Front 937

Standard cluster

Back 937

A cluster that is built with a single quorum database stored on a shared disk array.

Front 938

Caution: Service Startup

Back 938

The start-up type for SQL Server clustered services should be Manual. The Windows cluster needs to have control over the services. If you change the start-up type to Automatic, you will cause errors with the cluster operations.

Front 939

Important: Cluster Group Specification

Back 939

Unlike the previous three versioins of SQL Server, the SQL Server 2008 setup routine does not allow you to specify an existing cluster group to install services to, even though the group specified dows not contain a SQL Server instance. So to get around this problem, make sure that you do not have a group already created with the same name as you want when installation completes.

Front 940

Chapter 15: Lesson 1: Database Mirroring

Back 940

xx

Front 941

Quick Check: 1. What are the three Database Mirroring roles, and what functions do they serve?

Back 941

1. The principal database currently serves the database to applications. The mirror database is in a recovering state and does not allow connectionsa nd the optional witness server is an instance of SQL Server that is used for automatic failure detection and failover from a primary to a mirror database.

Front 942

Quick Check: 2. What are the three operating modes for Database Mirroring?

Back 942

2. The three operating modes are High Availability operating mode, High Performance operating mode, and High Safety operating mode.

Front 943

Quick Check: 3. Which mode is not recommended for normal operations?

Back 943

3. High Safety operating mode is not recommended for normal operations because its synchronoous transfers have a high-performance impact without the benefit of automatic failover.

Front 944

D. Monitor"

Back 944

"1. Correct Answer: B

Front 945

D. Service multiple Database Mirroring sessions"

Back 945

"2. Correct Answers: B and D

Front 946

D. ROLE set to PARTNER"

Back 946

"3. Correct Answers: B and D

Front 947

Exam Tip:

Back 947

For database mirroring, you must configure the principal, mirror, and witness endpoints on different SQL Server instances

Front 948

Lesson Summary:

Back 948

"* Database Mirroring involves databases in three different roles. The database in the principal role is available for connections and enables data modifications. The database in the mirror role is inaccessible to connections and receives transactions issued at the principal. The witness is defined for a SQL Server instance and is used with the High Availability operating mode to arbitrate a failover.

Front 949

Best Practices: High Safety Operating Mode

Back 949

Because High Safety operating mode's synchronous transfer can affect application performance does not offer the benefit of automatic failover, this operating mode isn ot recommended for normal operations. You should configure a Database Mirroring session in High Safety operating mode only when you need to replace the existing witness server. After you have replaced or recovered the witness, you should change the operating mode back to High Availability operating mode.

Front 950

Note: GUI Alternative

Back 950

SSMS has a GUI interface to configure Database Mirroring endpoints and to configure, fail over, pause, resume, and reconfigure the operating mode of a Database Mirroring session. You can access theis GUI by right-clicking a database, selecting Properties, and then selecting the Mirroring page in the Properties dialog box.

Front 951

Note: Retrieving Endpoint Information

Back 951

You can retrieve endpoint information by querying the sys.database_mirroring_endpoints dynamic management view.

Front 952

Note: Serving the Database

Back 952

When an instance has a database that allows transactions to be processed against it, it is said to be "serving the database."

Front 953

Note: Specifying an Endpoint Name

Back 953

Specify Mirroring as the endpoint name, which standardizes the naming convention for these types of endpoints so that you can distinguish them easily from other types of endpoints.

Front 954

Note: Split Brain Problem

Back 954

if the mirror were allowed to determine that it should serve the database by itself, it could introduce a situation whereby the database would be accessible to transactions on more than one server. This is referred to as a "split-brain problem."

Front 955

Note: Transient Operating States

Back 955

The principal and mirror roles are transient operating states within a Database Mirroring session. Because the databases are exact equivalents and are maintained in synch with each other, either database can take on the role of principal or mirror at any time.

Front 956

Caution: Filestream Data

Back 956

You need a version of AdventureWorks database from a previous edition of SQL Server, because the SQL Server 2008 version of AdventureWorks database contains FILESTREAM data, and the Database Mirroring is not compatible with FILESTREAM data.

Front 957

Caution: Ping Test Limitation

Back 957

A database can become inaccessible because of a runaway transaction or other operations. However, Database Mirroring does not detect these failures; only a failure of the ping test is considered a failure. You also have to balance carefully the number of mirroring sessions that are configured on a single instance. Each database participating in a mirroring session pings the other database every couple of seconds to determine whether it is still connected and available. Both the principal and the mirror must ping the witness every couple of seconds to determine whether it is still connected and available. The failure of ping test causes a failover in High Availability operating mode. Having a large number of databases participating in mirroring sessions on single server increases the possibility of an anomalous failover because of a saturation of the network.

Front 958

Caution: Port Numbers

Back 958

If you instances are installed on the same server,t heport number needs to be different for each enpoint to not create a conflict on the TCP/IP stack.

Front 959

Important: Database-Level vs. Server-Level Roles

Back 959

Principal and mirror roles occur at a database level and must be defined within SQL Server 2008 instances that are running either SQL Server 2008 Standard or Enterprise. However, you define the witness role at an instance level. The instance of SQL Server 2008 that you use for the witness server can be running any edition, including SQL Server 2008 express, which is why you refer to a principal or mirror database but a witness.

Front 960

Important: Hotstandby: Witness must be online

Back 960

Automatic failover can occur only if the witness server is online. If the witness is offline, automatic failover does not happen. This means that you can use High Availability operating mode to provide a hot standby server only when the witness server is online. Otherwise, you have a warm standby configuration.

Front 961

More Info: Endpoints

Back 961

For more information about defining endpoints, please refer to Chapter 8, "Designing SQL Server Endpoints."

Front 962

Chapter 15: Lesson 2: Initializing Database Mirroring

Back 962

xx

Front 963

Quick Check: 1. What is the process for preparing a database to participate in a Database Mirroring session?

Back 963

1. Change the recovery model to Full; back up the primary database; restore to the instance hosting the mirror database with the NORECOVERY option; and then copy all system objects such as logins, linked servers and so on.

Front 964

D. Restore the database with NORECOVERY"

Back 964

"1. Correct Answers: B and D

Front 965

D. Bulk-logged recovery model"

Back 965

"2. Correct Answer: A

Front 966

D. Manual failover"

Back 966

"3. Correct Answers: B and C

Front 967

Exam tip:

Back 967

Database Mirroring cannot be configured against databases that have a FILESTREAM filegroup

Front 968

Lesson Summary -- Chapter 15: Lesson 2: Initializing Database Mirroring

Back 968

"* You must set all databases that participate in database Mirroring to the Full recovery model to ensure that all transactions are applied to the mirror.

Front 969

Best Practices: Full Recovery Model Required

Back 969

Because Database Mirroring cannot be configured against a database that has a FILESTREAM filegroup, it is strongly recommended that you separate all FILESTREAM data into a dedicated database.

Front 970

Note: Finding Database Mirroring Endpoints

Back 970

"If you do not remember the endpint addresses, you can retrieve them using one of two different mechanisms:

Front 971

Note: Full Recovery Model Required

Back 971

You cannot configure Database Mirroring if the participating databases are not in the Full Recovery model. And you cannot change the recovery model of a database participating in Database Mirroring.

Front 972

Note: GUI Alternative to configuring Database Mirroring

Back 972

You can right-click the AdventureWorks database on INSTP, select the Mirroring tab, and configure a Database Mirroring session using the GUI

Front 973

Note: Initialising the Mirror

Back 973

Instead of performing a backup to initialize the mirror, I always use the last full backup of the primary database and then apply all subsequent transaction logs. After all log backups are taken, I execute a final transcation log bacups to capture all remaining transactions and then initiate Database Mirroring. An alternative method uses Log Shipping to maintain the two databases in synchronization and as the initialization mechanism for Database Mirroring. In this case, you might still ahve to apply at least one tansaction log backup before you can initiate the Database Mirroring session.

Front 974

Note: Using SSIS Tasks to Transfer Objects

Back 974

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

Front 975

Quick Check: 1. How do you fail back from a forced failover?

Back 975

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

Front 976

Quick Check: 2. Which server objects are your primary concern in the event of a failover?

Back 976

2. The instance containing the mirror database must also have all the logins present that are required for applications and users to connect to the database. If the appications use linked servers, they also need to exist to prevent application failures. Other server objects such as SSIS packages and jobs are not as critical during the failover and can generally be addressed shortly after a failover after all applications are online.

Front 977

D. Replication"

Back 977

"1. Correct Answer: A

Front 978

D. Log Shipping"

Back 978

"2. Correct Answer: B

Front 979

D. Snapshot replication"

Back 979

"3. Correct Answer: C

Front 980

D. Log Shipping"

Back 980

"4. Correct Answer: A

Front 981

D. Manual failover"

Back 981

"1. Correct Answers: A and D

Front 982

D. Manual failover"

Back 982

"2. Correct Answers: B and D

Front 983

Lesson Summary -- Chapter 15: Lesson 3: Designing Failover and Failback Strategies

Back 983

"* Database Mirroring can take care of the failover from the principal to the mirror, automatically promote the mirror and emote the failed partner, and even perform an automatic incremental resynchronization in some cases. However, this capability is wasted unless you are ensuring that logins, linked servers, and other supporting objects are present on the mirror at the time of failover.

Front 984

Best Practices: Falling Back

Back 984

Copy in paragraph from 472

Front 985

Database Mirroring

Back 985

A SQL Server 2009 high-availability technology configured between principal and mirror database (and an optional witness server) which maintains the close synchronization of data and the database schema and also offers that option of automatic failover.

Front 986

Database Mirroring role

Back 986

Defines the operating state of each participant in a Database Mirroring session. There are three possible roles: principal, mirror and witness.

Front 987

Database Mirroring session

Back 987

A principal database, mirror database, and optional witness server confirgured to exchange data using one of the three available operating modes.

Front 988

Database partner

Back 988

Refers to a member of a pair of principal and mirror databases participating in a Database Mirroring sessioin.

Front 989

Endpoint

Back 989

A connection mechanism that is used by any process needing to access the SQL Server engine to process transactions. For Database Mirroring, you create TCP endpoints on every instance involved in the Database Mirroring session.

Front 990

High Availability operating mode

Back 990

The Database Mirroring operating mode that requires a principal, mirror, and witness. Synchronously transfers data from the principalto the mirror, and enables automatic failure detection and automatic failover so long as the witness server is accessible when the failure occurs.

Front 991

High Performance operating mode

Back 991

The Database Mirroring operating mode that requires only a principal and a mirror, asynchronously transfers data from the principal to the mirror, and allows only a manual failover.

Front 992

High Safety operating mode

Back 992

The Database Mirroring operating mode that requires only a principal and mirror, synchronously transfers data from the principal to the mirror and allows only for a manual failover.

Front 993

Mirror

Back 993

The database within a Database Mirroring session that is in a recovering state, does not allow any connections, and is receiving changes from the principal.

Front 994

Mirror failover

Back 994

The process whereby the mirror database is promoted to the principal and recovered. This process also automatically demotes the principal to become the mirror within the Database Mirroring session.

Front 995

Operating mode

Back 995

The configuration that governs how a Database Mirroring session synchronizes transactions and which failover options are available. You can choose from three operating modes: High Availability, High Performance, or High Safety.

Front 996

Principal

Back 996

1. An object that can own other objects within an instance. The SQL Server object that defines a security access path for a user. (2) The database within a database Mirroring session that is recovered, is online, and allows transactions to be processed against it.

Front 997

Transparent Client Redirect

Back 997

The process that describes the functionality built into the new MDAC connection library that ships with Visual Studio 2005 and allows principal and mirror connections to be cached in the connection object. Failure of the principal causes this code to redirect a client connection to the mirror without developer intervention or custom coding.

Front 998

Witness server

Back 998

The arbiter within the High Availability operating mode, also know as a witness. The purpose of the witness is to guarantee that the database annot be served on more than one instance at the same time.

Front 999

Chapter 16: Lesson 1: Overview of Log Shipping

Back 999

xx

Front 1000

Quick Check: 1. What are the modes that a standby server can be in?

Back 1000

1. Standby Mode and No Recovery Mode.

Front 1001

Quick Check: 2. What are the three components of log shipping and what are their purpose?

Back 1001

2. The primary databse is accessible an accepts transactions. The secondary database has a continous chain of transaction logs appliaed to keep it up to data with the primary. The monitor instance sends alerts when the log shipping session is out of sync.

Front 1002

D. Standby"

Back 1002

"1. Correct Answer: B

Front 1003

Exam Tip:

Back 1003

Log shipping relies on transaction log backups. If the recovery model is changed to Simple, log shipping ceases to funtion. Similarly, issuing a BACKUP LOG…WITH TRUNCATE_ONLY command also breaks log shipping.

Front 1004

Lesson Summary -- Chapter 16: Lesson 1: Overview of Log Shipping

Back 1004

"* Log shipping is an automated way to apply a continuous chain of transaction log backups to one or more standby servers.

Front 1005

Note: Terminology Conventions

Back 1005

Log shipping operates between databases, so a primary server and secondary server do not actually exist. It is entirely possible to log-ship one database from ServerA to ServerB and then log-ship a different database from ServerB back to ServerA. When we refer to a primary or primary server, we are specifically referring to the server running SQL Server that contains the primary database within a particular log shipping session. When we refer to a secondary or secondary server, we are specifically referring to the server running SQL Server that contains the secondary database within a particular log shipping session. When we refer to a standby or standby server, we are specifically referring to the server session. For brevity, we use the terms primary and secondary/standby throughout the remainder of this chapter. Please keep in mind that these terms refer to a particular database, and any other object referenced for either a primary server or a secondary server appies to the instance that is hosting the specific databse.

Front 1006

Caution: Log Shipping Scope

Back 1006

the scope of log shipping is limited to a database. Therefore, log shipping does not take into account any cross-databsae dependencies that might exist. For example, if you have a databse that contains customers and a database that contains orders for those customers, your applications are maintaining the integrity of the data by the way transactions are handled. However, log shipping breaks this integrity because it operates by using transaction log backups, and SQL Server does not allow multidatabase transactions. it is possible that even if you had both customer and orders databases participating in log shipping sessions with backkups and restores operating at the same interval, upon recovery, orders could exist without a corresponding customer or customers could exist without all their orders. As a rule, if you ahve an environment set up that prevents you from using foreign keys to enforce referential intergrity (such as when you place customers and orders in separate databases instead of separate tables within the same database), SQL Server cannot ensure that data willr emain linked across databses.

Front 1007

More Info:

Back 1007

It is possible to implement log shipping between two databases within the same SQL Server instance. However, this is not a recommended practice for production environments, because if you lose the instance, you have lost both your primary and standby databases. A log shipping configuration confined within a single instance is for testing and learning purposes only.

Front 1008

Chapter 16: Lesson 2: initializing Log Shipping

Back 1008

xx

Front 1009

Quick Check: 1. Changing the primary database into which recovery model breaks log shipping?

Back 1009

1. Changing the recovery model of a database to simple breaks the transaction log chain and does not allow transaction log backups to be executed.

Front 1010

Quick Check: 2. On which type of backup is log shipping based?

Back 1010

2. Transaction log backups.

Front 1011

Quick Check: 3. Which operation cannot be performed against the secondary databse to continue to apply transaction log backups?

Back 1011

3. The database cannot be recovered, so you cannot issue RESTORE DATABASE…WITH RECOVERY

Front 1012

D. DDL Triggers"

Back 1012

"1. Correct Answer: B

Front 1013

Exam tip:

Back 1013

SSIS has a task that you can use to transfer logins from one instance to another.

Front 1014

Lesson Summary -- Chapter 16: Lesson 2: initializing Log Shipping

Back 1014

"* A database must be in either the Full or Bulk-logged recovery model to participate in log shipping.

Front 1015

Best Practices

Back 1015

This process ensures that you minimize the time required to initialize log shipping by not having to wait for another full backup to complete. It also ensures that you minimize the disk space consumed.

Front 1016

Best Practices: Configuring Intervals

Back 1016

"The scheduling interval for the backup job and copy job dictates the data loss that you can expect in the event of aprimary server failure. For example, if you configure backups to occur evry 15 minutes, but only copy backups every 45 minutes, you could potentially lose one hour of backups if the primary server fails. This is because the backup could complete just after the copy job finishes, which means that the copy job did not pick up the last 15 minutes of transactions and will not copy them for another 45 minutes, you could potentially lose on hour of backups if the primary server fails. This is because the backup could complete just after the copy job finishes, which means that the copy job did not pick up the last 15 minutes of transactions and will not copy them for another 45 minutes.

Front 1017

Best Practices: Data Loss Exposure

Back 1017

The general rule of thumb for data loss exposure is that it should be twice the interval of the transaction log backups. For example, if transaction log backups are executed every five minutes, the data loss exposure within a log shipping environment is considered to be 10 minutes. This interval accounts for the time it takes to complete a transaction log backup and copy it to the secondary. On system that experience a very high transaction volume, transaction log backups can seem to occur almost continuously because itmight take almost as long to complete a backup as the interval on which the backup is scheduled.

Front 1018

Best Practices: Leveraging SSMS

Back 1018

SSMS provides a single interface for configuring all the components or log shipping. Although the interface can perform all the configuration, most database administrators (DBAs) prefer to use Transact-SQL to set up log shipping. Fortunately, you can use SSMS to construct the log shipping configuration and generate a script for the implementation.

Front 1019

Best Practices: Restoring a full backup during configuration.

Back 1019

It is generally not recommended to have log shipping generate a full backup during the configuration of the session. This can have a very big impact on an existing environment, particularly if you have databses in excess of about 10 GB in size. When you configure log shipping, you will ahve existing backups already. (Note that you should always have backups of your databases.) To initialize log shipping in a typical production environment, you generally follow the steps outlined in this section.

Front 1020

Note: Built-in or Custom-Coded

Back 1020

The only difference between using custom-coded log shipping and using the built-in components that ship with SQL Server involves variances related to business requirements. Custom-coded log shipping generally accounts for additional requirements of a specific business environment. Both methods operate on the same principles: continuously restoring transaction logs to a secondary database. To simplify the explanation of log shipping, we devote the remaining two lessons in this chapter solely to the built-in components that ship with SQL Server 2008.

Front 1021

Note: Remapping users

Back 1021

To remap a databse user with a SQL Server login correctly, you need to execute the ALTER LOGIN command after the secondary database is brought online. Windows logins do not need to be remapped because the SID is globally unique and is always mapped correctly.

Front 1022

Caution: Maintenance Plans

Back 1022

It is very common for maintenance plans to be configured to back up databases. The maintenance plans generally specify that all user databases should be backed up by the plan, so an administrator does not have to worry about reconfiguring the maintenance jobs when databases are added or removed Most maintenance plans that are created will include the database on which you just configured log shipping, which leads to having multiple transaction log backups running against the primary database. The maintenance plan creates on set of transaction log backups, and the log shipping jobs create another set of transaction log backups. Each set is only part of what needs to be restored. The combination of both sets of backups is required to ensure that the database is up to date because each transaction log backup contains only the data that has changed since the last transaction log backup. Before setting up log shipping, you should change any maintenance plans that perform transaction log backups to exclude the primary database. Full and differential backups do not affect log shipping.

Front 1023

Caution: SQL Server Agent

Back 1023

Log shipping relies on jobs being run by the SQL Server Agent service. If the SQL Server Agent service is not running, log shipping is not running. If the jobs for log shipping are disabled or do not run, log shipping is not running.

Front 1024

Important: Filestream

Back 1024

The AdventureWorks database contains a FILESTREAM filegroup. Before restoring the AdventureWorks database to the secondary instance, you must ensure that FILESTREAM has been enabled.

Front 1025

Chapter 16: Lesson 3: Designing Failover and Failback Strategies

Back 1025

xx

Front 1026

Quick Check: 1. Which restore option is used to bring a database online?

Back 1026

1. The WITH RECOVERY options brings a database online, making it accessible to connections.

Front 1027

Quick Check: 2. What are the two reasons that would require you to take an additional outage and failback to the original primary after it is repaired and back online?

Back 1027

"2. The only reasons that you would need to fail back to the original primary are the following:

Front 1028

D. Database Mirroring"

Back 1028

"1. Correct Answer: A

Front 1029

D. Replication"

Back 1029

"2. Correct answer: D

Front 1030

D. Log shipping"

Back 1030

"3. Correct Answer: D

Front 1031

D. Send an event notification"

Back 1031

"4. Correct Answer: C

Front 1032

D. EXECUTE ALTER LOGIN"

Back 1032

"1. Correct Answer: B

Front 1033

D. SQL Server Agent jobs"

Back 1033

"Correct Answer: B

Front 1034

D. Store the backup of your database master key in a different directory from your database backups."

Back 1034

"6. Correct Answers: A and C

Front 1035

D. sp_change_users_login"

Back 1035

"7. Correct Answer: B

Front 1036

E. Primary -- Database Mirroring; secondary -- log shipping"

Back 1036

"8. Correct Answer: D

Front 1037

Lesson Summary -- Chapter 16: Lesson 3: Designing Failover and Failback Strategies

Back 1037

"* Detecting a failure and failing over is a manual process for log shipping.

Front 1038

Best Practices: Failback from a Standby

Back 1038

Whenever possible, you should ensure that performance or fault tolerance does not degrade if an application needs to fail over to the secondary database. This eliminates any technical reason for needing to fail back to the originial primary. If you do not need to fail back to the original primary, you can reverse the direction of log shipping after the failed server is back online. By not faving to fail back, you eliminate an additional outage on the application.

Front 1039

Backup job

Back 1039

A scheduled job in SQL Server Agent that create the transaction log backups for a log shipping

Front 1040

Copy job

Back 1040

A scheduled job in SQL Server Agent that copies transaction log backups from the primary to the secondary within a log shipping session.

Front 1041

Log shipping session

Back 1041

The combination of a primary database and a secondary database whereby a continuous chain of transaction log backups is taken against the primary database and restored to the secondary database.

Front 1042

Monitor instance

Back 1042

The instance that is configured to perform periodic health checks against a log shipping session.

Front 1043

Primary database

Back 1043

The database that is recovered and accepting transactions within a log shipping session.

Front 1044

Primary server

Back 1044

The server hosting the instance of SQL server that is hosting the primary database.

Front 1045

Restore job

Back 1045

A scheduled job in SQL Server Agent that restores transaction log backups to the secondary.

Front 1046

Secondary database

Back 1046

The database that is unrecovered (NO RECOVERY or STANDBY) and ahs transaction log backups from the primary continuously restored to it.

Front 1047

Secondary server

Back 1047

The server hosting the instance of sQL Server that is hosting the secondary database.

Front 1048

Standby database

Back 1048

See secondary database

Front 1049

Standby server

Back 1049

See secondary server

Front 1050

Transaction log chain

Back 1050

A continuous set of transaction log backups that can be applied to a full backup and does not provide any gaps in the transaction log sequence.

Front 1051

Chapter 17: Lesson 1: Overview of Replication

Back 1051

xx

Front 1052

Quick Check: 1. What are the three methods of replication?

Back 1052

1. Snapshot, transactional, and merge.

Front 1053

Quick Check: 2. What are the five agents used in replication?

Back 1053

2. Snapshot Agent (snapshot.exe), Log Reader Agent (logread.exe), Distribution Agent (distrib.exe), Merge Agent (replmerg.exe), and Queue Reader Agent (qrdsvc.exe).

Front 1054

Quick Check: 3. What are the three types of data conflicts?

Back 1054

3. Insert of a duplicate primary key, update conflict, and update of a nonexistent row.

Front 1055

Quick Check: 4. What are the two roles that a database can have within a replication architecture?

Back 1055

4. Publisher or subscriber.

Front 1056

Quick Check: 5. What are the core components of replication?

Back 1056

5. One or more articles are combined into a publication that forms the basis of a replication session. The articles can optionally have filters applied to them.

Front 1057

D. Peer-to-peer replication"

Back 1057

"1. Correct Answer: A

Front 1058

D. Transactional replication with queued updating subscribers."

Back 1058

"2. Correct Answer: D

Front 1059

Exam Tip:

Back 1059

Although replication has the capability to apply filters to articles, this capability is not used in high-availability architectures. A high-availability architecture is mainly concerned with maintaining a complete and coherent copy of the data on a separate instance of SQL Server.

Front 1060

Lesson Summary: Chapter 17: Lesson 1: Overview of Replication

Back 1060

"* One or more articles can be combined into a publication to form the definition of the data set that will be moved by the replicaiton engine.

Front 1061

Note:

Back 1061

Data moving within replication is referred to as the publication or set of data. It is always within the context of the subset of the data that you have defined to be replicated based on the articles selected and any row or column filters applied. This is a unique characteristic of replication, whereby a portion of a database can be made highly available.

Front 1062

Note: Applying a Snapshot

Back 1062

"By sefault, a snapshot applies the table structure, primary key and clsutered index, unique constraints, and data to the subscriber. All other objects related to a table, such as check constraints and foreign key constraints, are not sent. You can override default snapshot behavior by modifying the article properties.

Front 1063

Note: Cycle of an Agent

Back 1063

Replication can be configured to run in either a continuous or a scheduled mode. In a scheduled mode, the replication agent is run on a periodic basis. When configured in a continuous mode, the replication engine is constantly running. In either case, the replication engine always runs in a cycle of determining whether changes exist to be replicated, moving the changes to the subscriber, and then acknowledging receipt of changes. This process is referred to as a cycle of the replicaiton agent. In a schedule mode, the cycle of an agent is more obvious because the agent starts, performs some work, and then shuts down. The cycle is less obvious in continuous mode because the agent never shuts down, but instead it starts another cycle as soon as the previous one is completed. The cycle of an agent is a very important concept for understanding a variety of scenarios that can occur -- the most important of which are data conflicts.

Front 1064

Note: Learning Replication

Back 1064

The practices in this chapter configure a replication environment using the settings that I have always recommend when learning replication. You configure the publisher, subscriber and distributor all within the same instance, which has the effect of replicating between two databases on the same instance. This is the most straightforward way to learn replication because it eliminates the two most common causes of configuration issues in replication: Security and Connectivity.

Front 1065

Note: Snapshot Folder

Back 1065

The snapshot folder is a directory location that you specify when you configure replication, which serves as the default location for a snapshot. When you create a publication, you can override the location of the snapshot folder for the specific publication.

Front 1066

Note: Service Accounts

Back 1066

The replication engine uses named accounts for security. Therefore, the service account that SQL Server and SQL Server agent are running under must be either local or domain accounts. You cannot use localsystem for the service account.

Front 1067

Caution: Minimally Logged Transactions

Back 1067

"If a database is participating in replication, you must be extremely careful with Bulk-Logged and Simple recovery models. When a database is placed in the Bulk-logged or Simple recovery model, minimally logged transactions can be executed. These types of transactions only log page allocations and deallocations into the transaction log; they do not fire triggers.

Front 1068

Chapter 17: Lesson 2: Transactional Replication

Back 1068

xx

Front 1069

Quick Check: 1. Name two options for performing transactional replication.

Back 1069

1. Immediate updating subscribers and queued updating subscribers.

Front 1070

Quick Check: 2. name two architectures for performing transactional replication.

Back 1070

2. Peer-to-peer and bidirectional.

Front 1071

Quick Check: 3. What special transaction is now available for transactional replication that provides timing statistics?

Back 1071

3. Tracer tokens

Front 1072

Quick Check: 4. What are the two procedures that are used to validate whether a publisher and subscriber are synchronized?

Back 1072

4. sp_publication_validation and sp_article_validation

Front 1073

D. Transactional replication in a queued updating subscriber configuration"

Back 1073

"1. Correct Answer: C

Front 1074

Exam Tip:

Back 1074

You can configure a transactional publication with both immediate updating subscriber and queued updatting subscriber options. The queued updating subscriber option can be used as a failover mechanism when the publisher is not available.

Front 1075

Lesson Summary -- Chapter 17: Lesson 2: Transactional Replication

Back 1075

"* Transactional replication uses the Log Reader Agent to move transactions from the transaction log on the publisher to the distribution database. The Distribution Agent then moves transactions from the distribution database to each subscriber.

Front 1076

Best Practices:

Back 1076

Validation can cause a lot of overhead, particularly in environments with large numbers of subscribers to a single publication or with publications that contain a large number of articles. It is most common to perform a row count-only validation on a daily basis while reserving the row count and binary checksum validation for a weekly execution.

Front 1077

Note: Cleaing up the distribution database

Back 1077

the Distribution Agent does not directly clean up entries in the distribution database that have been written successfully to all subscribers. A separate job (referred to as the cleanup agent) runs on a periodic basis to remove transactions that have been sent to all subscribers. This job is separated for performance purposes and enables the Distribution Agent to sent transactions to subscribers at a much higher rate than it takes for the Log Reader Agent to write transactions to the distribution database. This processing architecture ensures that the Distribution Agent does not become a bottleneck int he process, even when it must handle sending transactions to many subscribers.

Front 1078

Note: UDFs

Back 1078

You include the UDFs within the publicaiton because there are tables within the AdventureWorks database that have constraints defined with UDF. If they are not either created already on the scriber or added to the publication, a failure occurs when the snapshot is applied.

Front 1079

Caution: Agent Security Settings

Back 1079

it is not recommended to run the replication agents under the SQL Server Agent service account. The replication agent accounts should have minimal permissions in your environment, and the SQL Server Agent servcie account elevates those permissions. SQL Server Books Online has several very good articles about setting replication security. You are explicitly violating the security best practices in the very specific examples in this chapter, and you're doing this for a very good reason. This chapter is about learning the basics of replication and how it can fit into your environment. The purpose of this chapter is to become familiar with the replication engine before hyou take the step of deploying it across multiple instances with varying security requirements and locking down all the access. If you do not understand the basic operation of the engine before applying all the security to the agents, you will have a hard time understanding whether any errors you encounter are because of a situation that you introduced, a misconfiguration, or security blocking access. You should not run replicaiton agents with this configuration in a production environment.

Front 1080

Chapter 17: Lesson 3: Merge Replication

Back 1080

xx

Front 1081

Quick Check: 1. Which two tables allow merge replication to determine the changes that need to be synchronized between publisher and subscriber?

Back 1081

1. Msmerge_contents and Msmerge_genhistory

Front 1082

Quick Check: 2. What feature makes merge replication more resilient to outages?

Back 1082

2. The merge engine uses the metadata to determine the changes that need to be applied. Because both publisher and subscriber maintain a full history of all changes and the metadata is stored within the same database participating in replicaiton, backup / restore processes keep the metadata syunchronized with the data being replicated. This ensures that the merge engine can recover, even from a restore operation, and incrementally resynchronize itself.

Front 1083

(question of 543)

Back 1083

XX

Front 1084

Lesson Summary -- Chapter 17: Lesson 3: Merge Replication

Back 1084

"* The merge engine provides a very powerful and resilient engine for distributing changes because it was designed because it was designed for mobile, disconnected users.

Front 1085

Agent profile

Back 1085

A set of configuration options that are applied to a replication agent.

Front 1086

Article

Back 1086

The most granular unit of a publication. Articles can be defined for tables, views, stored procedures and functions.

Front 1087

Column filter

Back 1087

Specifies filtering criteria to be applied to columns in a table or view that are replicated. This enables only a subset of columns to be replicated from publisher to subscriber.

Front 1088

Conflict resolver

Back 1088

Predefined code that is employed with merge replication or transactional replication with the queued updating subscribers option to ensure that a single coherent piece of data is maintained throughout the replication architecture if changes to the same row of data are applied at multiple locations within a replication cycle.

Front 1089

Data conflict

Back 1089

A situation that is created because of the replication engine moving changes on an asynchronous basis and different users making changes to the same row of data on both the publisher and subscriber.

Front 1090

Distribution Agent

Back 1090

The process responsible for applying snapshots for snapshot and transactional replication, as well as moving incremental changes to subscribers participating in transactional replication.

Front 1091

Distributor

Back 1091

The instance of SQL Server that contains the distribution database. Also the location where the majority of the processing overhead in a replication architecture occurs.

Front 1092

Dynamic filter

Back 1092

A row filter that is evaluated based on parameters passed from each subscriber.

Front 1093

Generation

Back 1093

A batch identifier used in merge relication to identify batches of changes on both publishers and subscribers.

Front 1094

Join filter

Back 1094

A row filter that is based on join criteria between two articles within a publication.

Front 1095

Log reader Agent

Back 1095

The executable responsible for polling the transaction log on the publisher for changes and writing any changes to the distribution database.

Front 1096

Merge Agent

Back 1096

The executable responsible for packaging changes to be exchanged between publisher and subscriber, as well as detecting and resolving any conflicts.

Front 1097

Merge replication

Back 1097

A method of replication that is based on tracking changes in a set of metadata tables internal to both publisher and subscriber. Designed primarily for mobile, disconnected applications that exchange changes in both directions.

Front 1098

Publication

Back 1098

The unit of exchange between publisher and subscriber that contains one or more articles.

Front 1099

Publisher

Back 1099

The designated master within a replication architecture. The publisher is considered to contain the master copy of the data.

Front 1100

Queue Reader Agent

Back 1100

The executable responsible for flushing changes made on a subscriber back to the publisher and resolving any conflicts. Used only with the queued updating subscriber option.

Front 1101

Reinitialization

Back 1101

The process by which a snapshot is regenerated and reapplied to a subscriber; it wipes out any data on the subscriber and resynchronizes it to the publisher.

Front 1102

Replication topology

Back 1102

A business diagram that shows the way data flows in a replication architecture.

Front 1103

Replication watermark

Back 1103

The last LSN that was read by the Log Reader Agent.

Front 1104

Republisher

Back 1104

A subscriber that is also configured as a publisher.

Front 1105

Row filters

Back 1105

The criteria used to restrict the set of rows that are replicated for an article.

Front 1106

Snapshot Agent

Back 1106

The executable responsible for generating a snapshot.

Front 1107

Snapshot folder

Back 1107

A directory used to store all the data generated by the snapshot process.

Front 1108

Snapshot replication

Back 1108

A method of replication that sends a full copy of the data defined within a publication from poublisher to subscriber.

Front 1109

Subscriber

Back 1109

A database designated to receive changes from a publisher. Can be configured to enable changes to be sent back to a publisher, if desired.

Front 1110

Subscription

Back 1110

A contract between publisher and subscriber that defines the publication that will be sent to a subscriber.

Front 1111

Tracer token

Back 1111

A specialized transaction that is used to log the latency between each component of transactional replication.

Front 1112

Transactional replication

Back 1112

A method of replication that is used to send data in one direction, from a publisher to a subscriber. This method propagates incremental changes applied to the publisher.

Front 1113

Validation

Back 1113

A process that is used to determine whether the publisher and subscriber are synchronized.