Errata

static IP address should be 10.10.10.60 not 10.10.10.40

Note from the Author or Editor:
Page xxiii
Change
10.10.10.40
to
10.10.10.60

The sentence reads "Windows Vista SP2 x86 is a 32-bit operating system that supports only the x86 versions of SQL Server 2012 Developer and SQL Server 2012 Express editions."

The chart below the sentence states Windows Vista SP2 x86 also supports SQL Server 2012 x86 Standard. Also confirmed on the following URL:
http://msdn.microsoft.com/en-us/library/ms143506.aspx

Note from the Author or Editor:
Page 3 change

"Windows Vista SP2 x86 is a 32-bit operating system that supports only the x86 versions of SQL Server 2012 Developer
and SQL Server 2012 Express editions."

to

"Windows Vista SP2 x86 is a 32-bit operating system that supports only the x86 versions of SQL Server 2012 Developer and SQL Server 2012 Express editions.

The component is certainly supported on Windows Server Core according to the reference provided on page 17: http://msdn.microsoft.com/en-us/library/hh231669(v=sql.110).aspx

Note from the Author or Editor:
Delete bullet
SQL Client Connectivity SDK

There seems to be a general error throughout the early part of the book with the Domain name. Should it be Contso.com or Contoso.com ? This error reappears in multiple places. The Domain Controller instructions use 'Contso.com'. So what should it be ?

Note from the Author or Editor:
Global
Change
contso
to
contoso

"For example, to install the SQL Server Database Engine, Replication, and Full-Text Search features, use the following syntax:
Setup.exe /q /ACTION=Install /FEATURES=SQL..."

By using FEATURES=SQL it would also install the Data Quality Server feature as stated on the previous page and confirmed on http://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature

Note from the Author or Editor:
page 34
Change
"to install the SQL Server Database Engine, Replication, and Full-Text Search features"
to
"to install the SQL Server Database Engine, Replication, Data Quality Server and Full-Text Search features"

At the ninth point there is a refer to Figure1-29 about the Database Engine Configuration, but the figure refers to the Server Configuration.

Note from the Author or Editor:
Page 47
Change
Database Engine Configuration page shown in Figure 1-29,
to
Server Configuration page shown in Figure 1-29,

Lesson 1 Question 1 lists the correct answer as B, e.g. B. Correct: The Enterprise edition requires a minimum of 1 GB of RAM. The question on page 24 reads "What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?"

The correct answer should be for recommended (4 GB), not required (1 GB). The correct answer is D.

Either the question should be changed to replace "recommended" with "required", or the answer should be updated with correct information.

Note from the Author or Editor:
Page 24 change:
"What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?"
to
"What is the minimum required amount of RAM for SQL Server 2012 Enterprise?"

The sentence
"When SQL Server 2012 is run on a 32-bit operating system, the lowest minimum memory value you can allocate is 64 MB"
should read
"When SQL Server 2012 is run on a 32-bit operating system, the lowest maximum memory value you can allocate is 64 MB"
The sentence
"If SQL Server 2012 is run on a 64-bit operating system, the lowest minimum memory value you can allocate is 128 MB"
should read
"If SQL Server 2012 is run on a 64-bit operating system, the lowest maximum memory value you can allocate is 128 MB"

Note from the Author or Editor:
Page 64

Change

"When SQL Server 2012 is run on a 32-bit operating system, the lowest minimum memory value you can allocate is 64 MB"

to

"When SQL Server 2012 is run on a 32-bit operating system, the lowest maximum memory value you can allocate is 64 MB"

&

Change

"If SQL Server 2012 is run on a 64-bit operating system, the lowest minimum memory value you can allocate is 128 MB"

to

"If SQL Server 2012 is run on a 64-bit operating system, the lowest maximum memory value you can allocate is 128 MB"

/sq in last command at bottom of page should be /qs

Note from the Author or Editor:
Page 85
<package_name>.exe /sq
to
<package_name>.exe /qs

Location for rule described in exercise as
"Computer Configuration\Windows Settings\Security Settings node"
This should read as
"Computer Configuration\Policies\Windows Settings\Security Settings node"

Note from the Author or Editor:
Page 95

Change
"Computer Configuration\Windows Settings\Security Settings node"

to

"Computer Configuration\Policies\Windows Settings\Security Settings node"

Answer to #5 shows "A" using sp_configure but page 79 has that option under "B" so don't match.

Note from the Author or Editor:
Page 79
Change

5. Which stored procedure do you use to enable Database Mail on an instance?
A. sp_monitor
B. sp_configure
C. sp_rename
D. sp_depends

to

5. Which stored procedure do you use to enable Database Mail on an instance?
A. sp_configure
B. sp_rename
C. sp_monitor
D. sp_depends

configuring the Analysis Services service account
as contoso\asaccount, and with contoso\kim_akers as the Analysis Services Administrator
account

Setup.exe /q /IAcceptSQLServerLicenseTerms /Action=install /Features=AS
/ASSERVERMODE=MULTIDIMENSIONAL /INSTANCENAME=ASMulti /ASSVCACCOUNT=NetworkService
/ASSYSADMINACCOUNTS=contoso\kim_akers

The service account requested is contoso\asaccount. The statement use NetworkService as service account.

Note from the Author or Editor:
Page 106
Change
/ASSVCACCOUNT=NetworkService
to
/ASSVCACCOUNT=Contoso\kim_akers

There is an errata entry already but the correction itself is wrong:
USE master
GO
ALTER DATABASE Litware2012 ADD FILE (
NAME = FileStrmFile,
FILENAME = 'C:\FSTRM'
TO FILEGROUP FileStreamFileGroup )

*SHOULD BE*

USE master
GO
ALTER DATABASE Litware2012 ADD FILE (
NAME = FileStrmFile,
FILENAME = 'C:\FSTRM') <--------- *NOTE THE CLOSING PARENT*
TO FILEGROUP FileStreamFileGroup

Note from the Author or Editor:
Change:

USE master
GO
ALTER DATABASE Litware2012 ADD FILE (
NAME = FileStrmFile,
FILENAME = 'C:\FSTRM'
TO FILEGROUP FileStreamFileGroup )

to


USE master
GO
ALTER DATABASE Litware2012 ADD FILE (
NAME = FileStrmFile,
FILENAME = 'C:\FSTRM')
TO FILEGROUP FileStreamFileGroup

In SQL Server 2012 the contained database feature is currently available only in a partially contained state

http://msdn.microsoft.com/en-us/library/ff929071.aspx

Note from the Author or Editor:
Page 129
Change
"SQL Server 2012 supports contained databases and Partially Contained Databases (Partial-CDBs),"
to
"SQL Server 2012 supports Partially Contained Databases (Partial-CDBs),

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

Should be: ( missing ' = ' )

ALTER INDEX indexName
ON tableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION=ROW)

Thank you!

Note from the Author or Editor:
Page 132
Change
"ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)"
to
"ALTER INDEX indexName ON tableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION=ROW)"

Page number references for Chapter 4: Lessons 1 and 2 are incorrect.

Note from the Author or Editor:
Change

Lesson 1: Migrating to SQL Server 2012 142
to
Lesson 1: Migrating to SQL Server 2012 154

&

Lesson 2: Importing and Exporting Data 162
to
Lesson 2: Importing and Exporting Data 175

Code sample for sp_detach_db to detach the Planets database uses the database name [SpaceElevator], not [Planets]

Note from the Author or Editor:
Page 162

Change
For example, to detach the Planets database
to
For example, to detach the SpaceElevator database

Broken link.
MORE INFO: IMPROVING BULK IMPORT PERFORMANCE
You can learn more about improving the performance of bulk import operations at http://msdn.microsoft.com/en-us/library/ms190421(SQL.110).aspx.

Note from the Author or Editor:
Page 182

Change

http://msdn.microsoft.com/en-us/library/ms190421(SQL.110).aspx
to
http://msdn.microsoft.com/en-us/library/ms190421(SQL.105).aspx

"5. Use the bcp utility to export data from the Person.Contact table in the AdventureWorks2012 database..."
There is no such table in that database.
The reference is likely to the Person.Person table.

Note from the Author or Editor:
Page 184
Change
export data from the Person.Contact table
to
export data from the Person.Person table

"DENY CONNECT SQL "contoso\domain_user_b";" should be "DENY CONNECT SQL TO "contoso\domain_user_b";"

Note from the Author or Editor:
Page 201
Change
DENY CONNECT SQL "contoso\domain_user_b";
to
DENY CONNECT SQL TO "contoso\domain_user_b";

Mentions to create SQL Server authenticated login, but doesn't state to also enter a password as other steps in the practice do. You must designate a password for a SQL Server account.

Note from the Author or Editor:
Page 205
Change: Use SQL Server Management Studio to create a SQL Server?authenticated login named sql_user_b.

to

Use SQL Server Management Studio to create a SQL Server?authenticated login named sql_user_b with the password Pa$$w0rd

Link for FIXED DATABASE-LEVEL ROLES is broken.
Likely should be changed to
http://msdn.microsoft.com/en-us/library/ms189121(SQL.110).aspx

Note from the Author or Editor:
Page 213
Change
http://msdn.microsoft.com/en-us/library/ms189612(SQL.110).aspx.
to
http://msdn.microsoft.com/en-us/library/ms189121.aspx

The statement "When granting, denying, or revoking permissions from a securable, you must perform one operation at a time." is not correct. The code sample supplied to demonstrate this
DENY ALTER ON [Person].[Address] TO [GAMMA-ROLE]
DENY INSERT ON [Person].[Address] TO [GAMMA-ROLE]
DENY UPDATE ON [Person].[Address] TO [GAMMA-ROLE]
can be replaced with the combined operation
DENY ALTER,INSERT,UPDATE
ON [Person].[Address] TO [GAMMA-ROLE]

Note from the Author or Editor:
Page 236

Change
"When granting, denying, or revoking permissions from a securable, you must perform one operation at a time."
to
"When granting, denying, or revoking permissions from a securable, you should perform one operation at a time."

Category A collection of categories that should be enforced together...
Should be
Category A collection of policies that should be enforced together...

Note from the Author or Editor:
Page 264

Change

Category A collection of categories that should be enforced together...

to

Category A collection of policies that should be enforced together...

"Each correct answer presents a complete solution"
Should be
"Each correct answer presents part of a complete solution"

Note from the Author or Editor:
Page 271

Question 4

Change

(Each correct answer presents a complete solution. Choose all that apply.)

to

(Each correct answer presents part of a complete solution. Choose all that apply.)

Answer Lesson 2, Question 2: Answer is incomplete

Item A. is also correct. The sys.sql_logins catalog view inherits the is_disabled column from sys.server_principals.
http://technet.microsoft.com/en-us/library/ms174355.aspx

This:
"A. Incorrect. You can use the sys.sql_logins catalog view to determine whether a SQL Server?authenticated login is configured to use a password policy and has an expiration date. You cannot use this catalog view to determine whether a login is disabled"

Change to this:
"A. Correct. You can query the sys.sql_logins catalog view to determine whether a login is disabled."

Note from the Author or Editor:
Page 275
Change
2. Correct Answer: C
to
2. Correct Answer: A and C

Change
A. Incorrect. You can use the sys.sql_logins catalog view to determine whether a SQL Server?authenticated login is configured to use a password policy and has an expiration date. You cannot use this catalog view to determine whether a login is disabled.
to
A. Correct. You can use the sys.sql_logins catalog view to determine whether a SQL Server?authenticated login is configured to use a password policy and has an expiration date. Through inheritance this view also displays whether a login is disabled.

The question for 3.2 reads:
"2. Which step should you take to ensure that both successful and failed audits are logged on the default instance on SYD-SQL-A?"
This question is about logging both success and failure in audits, an important feature of auditing that is enabled through configuring the Audit Object Access policy for both Success and Failure.
However, the answer is about auditing both success and failure in LOGINS, which is not the question as asked.

Note from the Author or Editor:
Page 272
Change
"Which step should you take to ensure that both successful and failed audits are logged on the default instance on SYD-SQL-A?"
to
"Which step should you take to ensure that both successful and failed logins are audited on the default instance on SYD-SQL-A?"

"3. Run DBCC CHECKDB on the new principal server": there is no "new" principal server as no upgrade nor failover has occured yet.

Note from the Author or Editor:
Page 295

change
Run DBCC CHECKDB on the new principal server.
to
Run DBCC CHECKDB on the principal server.

Points A, C and D refers to SQL Server Agent account, but mirroring use SQL Server Service accounts. The same is for answers to this question

Note from the Author or Editor:
Page 298
In Question 2 change three instances of SQL Server Agent service account
to
SQL Server service account

Page 324
In the answers to question 2, change

SQL Server Agent account (A) and SQL Server Agent service account (B) and (D) to SQL Server service account.

"Right-click the SQL Server service related to the instance on which you want to enable AlwaysOn Availability Groups."
SHOULD READ
"Right-click the SQL Server service related to the instance on which you want to enable AlwaysOn Availability Groups and select Properties."

Note from the Author or Editor:
Page 354
Change
"Right-click the SQL Server service related to the instance on which you want to enable AlwaysOn Availability Groups."
To
"Right-click the SQL Server service related to the instance on which you want to enable AlwaysOn Availability Groups and select Properties."

"You can have only one listener per availability group."
This contradicts what is stated on page 358:
"If you need more than one listener for an availability group, it is possible to create additional listeners by using Windows PowerShell or the Failover Cluster Manager console."

Note from the Author or Editor:
Page 364
Change
You can have only one listener per availability group.
to
Each availability group listener must have a unique DNS name

The first sentence reads "Every counter has one or more instances associated with it." Later, in the Lesson Summary on page 377 it reads "A counter can have zero or more instances.".

A counter can indeed have zero or more instances. Page 373 is incorrect and page 377 is correct.

Note from the Author or Editor:
Page 373
Change
Every counter has one or more instances associated with it.
to
Every counter has zero or more instances associated with it.

Question on page 387 has SQL:StmtCompleted as A
Answer on page 411 has SQL:StmtCompleted as C

Note from the Author or Editor:
Page 387 and 388
Change

A. SQL:StmtCompleted
C. SQL:BatchComplete

to

A. SQL:BatchComplete
C. SQL:StmtCompleted

The link in the box is wrong. It doesn't lead to a page about ACID but to a page about isoloation levels (identical to the link on page 446 btw)

Note from the Author or Editor:
Page 442
Change
http://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx
to
http://technet.microsoft.com/en-us/magazine/hh750281.aspx

"Alternatively, if the blocking_session_id column of sys.dm_waiting_tasks or sys.dm_exec_requests has a value greater than zero, this indicates blocking and also indicates which session is blocking."

There is no such DMV sys.dm_waiting_tasks. Perhaps you mean sys.dm_os_waiting_tasks.

Note from the Author or Editor:
Page 449
Change
sys.dm_waiting_tasks
to
sys.dm_os_waiting_tasks

It is stated "You cannot create an index at this time." however the answer on page 463 says to look at the missing index view to ensure all appropriate indexes are created.

Note from the Author or Editor:
Page 459
Remove sentence

You cannot create an index at this time.

Truncate Log On Checkpoint no longer exists in SQL Server 2012. You should use Simple Recovery Model instead.

Note from the Author or Editor:
Page 488
Delete "You cannot use this method if the Truncate Log On Checkpoint option is enabled."

The correct answer is actually C, not B.
The text explanation for B is also incorrect - it should read
"The ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] statement configures auditing for backup and restore operations at the database level"
The text explanation for C is accuate and correct, but it needs to be identified as the correct answer.

Note from the Author or Editor:
Page 555

Change
Question 3 Correct Answer: B
to
Question 3 Correct Answer: C

&

B. Correct. The ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] statement configures auditing for backup and restore operations at the instance level.

to

B. Incorrect. The ALTER DATABASE AUDIT SPECIFICATION [DATABASE_SPEC] statement configures auditing for backup and restore operations at the database level.

&

C. Incorrect. The ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] statement configures auditing for backup and restore operations at the instance level.

to

C. Correct. The ALTER SERVER AUDIT SPECIFICATION [INSTANCE_SPEC] statement configures auditing for backup and restore operations at the instance level.