Chapter 1 - Planning and Installing SQL Server 2012 1
a.
Lesson 1: Planning Your Installation . . . . . .
. . . . . . . . . . .. . . . . . . . . . . . . 2
i. Evaluating
Installation Requirements 2
ii. Designing
the Installation 7
iii. Planning
Scale Up versus Scale Out Basics 8
iv. Shrinking
and Growing Databases 9
v. Designing
the Storage for New Databases 13
vi. Remembering
Capacity Constraints 15
vii. Identifying
a Standby Database for Reporting 15
viii.
Identifying Windows-Level Security and
Service-Level Security 15
ix. Performing
a Core Mode Installation 17
x. Benchmarking
a Server 19
xi. Lesson
Summary 23
·
All editions of SQL Server 2012 have a minimum
RAM requirement of 1 GB except the Express editions, which have a minimum RAM
requirement of 512 MB.
·
SQL Server 2012 Enterprise, Business
Intelligence, and Web editions of SQL Server 2012 can be run only on Windows
Server 2008 R2 SP1 and Windows Server 2008 SP2 operating systems using SQL
Server 2012 Datacenter, Enterprise, Standard, and Web editions. You can run the
x86 version of these SQL Server 2012 editions on the x86 version of Windows
Server 2008 SP2, but not on the x64 versions.
·
The x64 versions of SQL Server 2012 require an
AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, or Intel
Pentium IV with EMT54 support with a minimum processor speed of 1.4 GHz and a
recommended speed of 2.0 GHz or faster. The x86 versions of SQL Server 2012
require a Pentium III–compatible processor or newer that has a minimum speed of
1.0 GHz and a recommended speed of 2.0 GHz or faster.
·
Scaling up involves adding better hardware;
scaling out involves adding more SQL instances.
·
Databases are configured to autogrow by default.
You can configure a database to shrink automatically, but this is likely to
lead to index fragmentation.
·
Databases use primary files (.mdf), secondary
files (.ndf), and transaction log files (.ldf).
·
When SQL Server 2012 is deployed on Windows
Server 2008 R2, it uses a virtual account, which is a locally managed account,
by default. You can configure SQL Server 2012 to use an existing managed
service account.
·
SQL Server 2012 can be installed on Windows
Server 2008 R2 SP1 in the Server Core configuration, although not all features
are supported.
·
You can use SQLIO to benchmark the I/O subsystem
prior to SQL Server 2012 deployment, and you can use SQLIOSim to simulate read,
write, checkpoint, backup, sort, and read-ahead activities.
b.
Lesson 2: Installing SQL Server and Related
Services . . . . . . . . . . . . . . . 26
i. Configuring
an Operating System Disk 26
ii. Installing
the SQL Server Database Engine 27
iii. Installing
SQL Server 2012 from the Command Prompt
33
iv. Installing
SQL Server Integration Services 34
v. Enabling
and Disabling Features 36
vi. Installing
SQL Server 2012 by Using a Configuration File
39
vii. Testing
Connectivity 40
viii.
Lesson Summary
52
·
The volume that hosts the operating system needs
a minimum of 4 GB of available space to host the temporary SQL Server 2012
installation files.
·
If you do not enter a product key, SQL Server
2012 will be installed as an evaluation edition.
·
You can install up to 50 instances of the
Database Engine on a single server.
·
You can install only one instance of SSIS on a
server.
·
You can add features to an existing installation
by using Windows Explorer, by using Setup.exe from the command line, or by
invoking SQL Server Setup through SQL Server Installation Center.
·
You can remove features from an existing
installation by using Setup.exe from the command line or by using Programs And
Features in Control Panel.
·
You must create firewall rules to enable remote
connections to SQL Server 2012 features.
·
You can verify connectivity to a remote instance
of SQL Server 2012 features by using SQL Server Management Studio or SQLCMD.
Chapter 2 - Configuring and Managing SQL Server Instances 61
a.
Lesson 1: Configuring SQL Server Instances . . .
. . . . .. . . . . . . . . . . . . . . 62
i.
Instance-Level Settings 62
ii.
Database Configuration and Standardization 68
iii.
Distributed Transaction Coordinator 71
iv.
Configuring Database Mail 72
v.
Lesson Summary
78
·
You can configure instance-level settings such
as fill factor, minimum memory use, maximum memory use, processor, and I/O
affinity through the Server Properties dialog box.
·
You can use the sp_configure stored procedure to
configure minimum server memory, maximum server memory, fill factor, and I/O
affinity. You can configure processor affinity by using sp_configure, although
ALTER SERVER CONFIGURATION is the preferred method.
·
Processor affinity ties an instance to specific
processors rather than to all processors on the host server.
·
A maximum memory strategy assigns each instance
a maximum amount of memory. A minimum memory strategy assigns each instance a
minimum amount of memory and allows instances to use available extra free
memory as necessary.
·
The model database serves as a template for all
new databases. Use the ALTER DATABASE statement to modify the model database.
·
MSDTC is required when transactions run across
multiple servers. You should configure it as a cluster resource prior to
installing the Database Engine on a cluster.
·
Database Mail enables an instance to send email
messages. You can enable Database Mail by using the sp_configure stored
procedure.
b.
Lesson 2: Managing SQL Server Instances . . . .
. . . . . . . . . . . . . . . . . . .
.80
i.
Installing Additional Instances 80
ii.
Deploying Software Updates and Patch
Management 84
iii.
Configuring Resource Governor 86
iv.
Using WSRM with Multiple Database Engine
Instances 91
v.
Cycle SQL Server Error Logs 93
vi.
Lesson Summary
96
·
SQL Server 2012 supports up to 50 instances on a
single host and up to 25 instances on a failover cluster.
·
You must update all features associated with a
SQL Server 2012 instance at the same time。
·
Shared features must be updated to the most
recent update.
·
Run a software update with the /InstanceName
parameter to update a specific instance; run the update with the /AllInstances
parameter to update all instances.
·
Resource Governor enables you to manage
processing and memory resources within a Database Engine instance.
·
You cannot use Resource Governor to manage Analysis Services,
Integration Services, or Reporting Services.
o
What’s for? - to place limits on the consumption
of CPU and memory
resources within a SQL Server 2012 Database Engine instance
o
How to enable/disable it
§
SSMS
§
T-SQL
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER RESOURCE GOVERNOR DISABLE;
GO
o
Resource pools represent a subset of the
physical resources available to a Database Engine instance of SQL Server. You assign resources to a resource pool by
specifying either a minimum or maximum value for both the processor and memory
resources. SQL Server has two built-in resource pools: the internal and default
resource pools. You can add additional user-defined resource pools as
necessary. For example, to create a resource pool named poolAlpha and assign it
a minimum CPU allocation of 20%, use the following statement:
CREATE RESOURCE POOL
poolAlpha
WITH (MIN_CPU_PERCENT
= 20);
GO
---to apply the values
ALTER RESOURCE GOVERNOR RECONFIGURE;
o
Workload groups function as containers for
session requests that share similar classification criteria. Workload groups
are assigned to resource pools. There are two built-in workload groups: the
internal group and the default group. It is also possible to create
user-defined workload groups. For example, use the following to create a
groupBeta workload group name associated with the poolAlpha pool:
CREATE WORKLOAD GROUP
groupBeta
USING
poolAlpha;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
o
Resource Governor Classification allocates
incoming sessions to a workload group based on session properties. You can
create classifier functions as a way of customizing the classification logic.
The example is available in the book.
·
Windows System Resource Monitor enables you to
manage and allocate processor and memory resources across instances.
·
You can use the sp_cycle_errorlog stored
procedure to cycle the SQL Server error log.
·
You can use sp_cycle_agent_errorlog from the
msdb system database to cycle the SQL Server Agent error log.
Chapter 3 - Configuring SQL Server 2012 Components 105
a.
Lesson 1: Configuring Additional SQL Server
Components . . . . . . . . .106
i.
Deploying and Configuring Analysis Services 106
ii.
Deploying and Configuring Reporting
Services 108
iii.
Deploying and Configuring SharePoint
Integration 112
iv.
Configuring SQL Server Integration Services
Security 114
v.
Managing Full-Text Indexing 116
vi.
Configuring FILESTREAM 118
vii.
Configuring FileTables 120
viii.
Lesson Summary
123
·
An Analysis Services instance in multidimensional
and data mining mode supports OLAP databases.
·
An Analysis Services instance in tabular mode
supports the new tabular modeling feature.
·
If you perform a file-only Reporting Services
deployment, you must run the Reporting Services Configuration Manager. (If you
install Reporting Services by using the files-only installation option, you
must run Reporting Services Configuration Manager)
·
FILESTREAM enables you to store BLOB objects in
the file system.
·
FileTables are special types of tables that
enable you to store files and directories directly in the database. These files
and directories can be accessed through the Windows file system.
·
Analysis Services and Reporting Services can be
enhanced with SharePoint integration. In previous versions of SQL
Server (2005, 2008, and 2008 R2), by default when you installed SQL Server all
users in the Users group had access to the Integration Services service. When
you install the current release of SQL Server, users do not have access to the
Integration Services service. The service is secure by default. After SQL
Server is installed, the administrator must grant access to the service.
o
Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for
modifying certain settings in the registry.
o
In the Component
Services dialog,
expand the Component Services > Computers > My Computer > DCOM Config
node.
o
Right-click Microsoft
SQL Server Integration Services 11.0, and then click Properties.
o
On the Security tab, click Edit in the Launch
and Activation Permissions area.
o
Add users and assign appropriate permissions, and then click Ok.
o
Repeat steps 4 - 5 for Access Permissions.
o
Restart SQL Server Management Studio.
o
Restart the Integration Services Service.
b.
Lesson 2: Managing and Configuring Databases . .
. .. . . . . . . . . . . . . .125
i.
Designing and Managing Filegroups 125
ii.
Configuring and Standardizing Databases 128
iii.
Understanding Contained Databases 128
iv.
Using Data Compression 131
v.
Encrypting Databases with Transparent Data
Encryption 135
vi.
Partitioning Indexes and Tables 137
vii.
Managing Log Files 140
viii.
Using Database Console Commands 141
ix.
Lesson Summary
146
·
Filegroups are collections of database files
that enable you to implement partitioning of tables and indexes.
·
Configure the model system database as a
template when standardizing databases on an instance.
·
Contained databases are databases that have no
dependencies on the Database Engine. This makes it easy to move databases
between instances and to cloud-based deployments such as SQL Azure.
·
Row-level compression modifies data types to reduce the amount of
storage space used. Page-level compression uses dictionary compression techniques and provides
greater space savings, but at the cost of CPU usage.
·
Transparent Data Encryption (TDE) enables you to
encrypt an entire database. The database will remain encrypted even when backed
up.
·
Transaction log truncation depends on the
configured recovery model. You can force a checkpoint by using the CHECKPOINT
statement.
Chapter 4 Migrating, Importing, and Exporting 153
a.
Lesson 1: Migrating to SQL Server 2012 . . . . .
. . . . . . . . . . . . . . . . . . . .154
i.
Upgrading an Instance to SQL Server 2012 154
ii.
Migrating a Database to a SQL Server 2012
Instance 161
iii.
Copying Databases to Other Servers 164
iv.
Migrating SQL Logins 170
v.
Lesson Summary
173
·
You can upgrade from SQL Server 2005, SQL Server
2008, and SQL Server 2008 R2 if the appropriate service packs have been
applied, you are upgrading within the same processor architecture, and you are
attempting a supported edition upgrade path.
·
You can migrate a database from one instance to
another instance by using the detach and attach method. You can’t detach a
database that is being mirrored or replicated or that has a snapshot.
·
You can copy a database to another instance by
using the Copy Database Wizard or by backing up and then restoring the
database. The advantage of using the Copy Database Wizard is that it also
enables you to migrate database metadata, such as logins, to the new instance.
·
You can migrate SQL logins by using the Generate
A Script function in SQL Server Management Studio
b.
Lesson 2:
Exporting and Importing Data . . . . . . . . . . . . . . . . . . . . . . . .175
i.
Copying and Exporting Data 175
ii.
Using the SQL Server Import and Export
Wizard 176
iii.
Using BCP to Import and Export Data 178
iv.
Importing Data by Using BULK INSERT 179
v.
Importing Data by Using OPENROWSET(BULK) 180
vi.
Using Format Files 180
vii.
Preparing Data for Bulk Operations 181
viii.
SELECT INTO
182
ix.
Lesson Summary
184
·
The bcp utility is a command-line utility you
can use to export data from a database or to perform a bulk import of data into
a preexisting table.
·
The BULK INSERT Transact-SQL statement enables
you to import data from a file into a preexisting table.
·
You can use the OPENROWSET(BULK) function to
import data from an OLE DB data source.
·
You can use the Integrations Services Import and
Export Wizard to migrate data to and from OLE DB providers. The wizard enables
you to save an import or an export task as an Integration Services package,
which you can execute later according to a schedule.
·
The SELECT INTO statement enables you to create
a new table on a database hosted on the current instance based on the results
of a SELECT query.
·
You use a format file when you are performing a
bulk import of data from a file that uses a fixed-length or fixed-width field.
Chapter 5 SQL Server Logins, Roles, and Users 193
a.
Lesson 1: Managing Logins and Server Roles . . .
. . . . . . . . . . . . . . . . . .194
i.
SQL Logins 194
ii.
Server Roles
201
iii.
User-Defined Server Roles 203
iv.
Credentials
204
v.
Lesson Summary
206
·
SQL Server logins allow access at the instance
level. You can create SQL Server logins that are mapped to local or
domain-based user accounts or to local or domain-based security groups. You can
also create SQL Server logins that are authenticated by SQL Server or that use
certificate or asymmetric keys for authentication.
·
Nine fixed server roles are associated with a
SQL Server 2012 Database Engine instance. You cannot alter the permissions
assigned to eight of these roles, but you can modify the membership of these
roles.
·
You can alter the permissions assigned to the
public fixed server role, but you cannot modify the membership of the public
fixed server role.
·
User-defined server roles are a feature new in
SQL Server 2012. You can assign customized permissions to user-defined server
roles.
·
You can grant or deny access on the basis of SQL
Server login.
b.
Lesson 2: Managing Users and Database Roles . .
. . . . . . . . . . . . . . . . .209
i.
Database Users 209
ii.
Database Roles
211
iii.
Contained Users
216
iv.
Least Privilege
218
v.
Application Roles 218
vi.
Lesson Summary
221
·
In uncontained databases, database user accounts
map to existing SQL logins.
·
In contained databases, database user accounts
do not need to map to existing SQL logins.
·
Orphaned users are users in uncontained
databases in which there is no corresponding SQL Server login.
·
There are nine fixed database-level roles. You
cannot alter the permissions assigned to these roles.
·
You can create flexible database-level roles and
assign custom permissions to these roles so you can be more specific with the
assignment of permissions rather than using the more general fixed
database-level roles.
·
An application role is a special role used by an
application to access a database. Application roles are secured by passwords.
Chapter 6 Securing SQL Server 2012
229
a.
Lesson 1: Managing Database Permissions . . . .
. ... . . . . . . . . . . . . . . .230
i.
Understanding Securables 230
ii.
Assigning Permissions on Objects 232
iii.
Managing Permissions by Using Database
Roles 233
iv.
Protecting Objects from Modification 236
v.
Using Schemas
236
vi.
Determining Effective Permissions 238
vii.
Lesson Summary
239
·
A securable is an item for which you can assign
permissions. Securables can be contained within other securables.
·
A scope is a container. Permissions assigned at
the scope level are inherited by objects within that scope.
·
The GRANT, DENY, and REVOKE statements are used
to manage permissions on objects.
·
Roles
enable you to simplify the management of permissions. Assign permissions to
roles and then add principals to roles.
·
Fixed database roles have fixed permissions.
Flexible database roles enable you to assign custom permissions.
·
Schemas enable you to collect objects to simplify
the process of assigning permissions.
b.
Lesson 2: Troubleshooting SQL Server Security .
. . . . . . . . . . . . . . . . . 241
i.
Troubleshooting Authentication 241
The most common authentication
problem is a security principal being unable to access the Database Engine
instance, or a specific database in the case of contained databases, because of
a forgotten password. Forgotten passwords must be reset, which is usually
triggered by a call to the service desk.
When SQL Server authentication is
enabled, Group Policy controls how long an account is locked out, how many
failed logins trigger a lockout, and the duration over which the logins must
occur to trigger a lockout. These factors depend on the configuration of the
following three Group Policy items, found in the Computer
Configuration\Policies\Windows Settings\Security Settings\Account
Policies\Account Lockout Policy Group Policy node:
·
Account Lockout Duration The length of time an
account is locked out
·
Account Lockout Threshold The number of failed
logins that must occur to trigger a lockout
·
Reset Account Lockout Counter After The duration
in which the failed logins must occur to trigger a lockout
The default configuration of
Active Directory does not define an account lockout policy.
ii.
Troubleshooting Certificates and Keys 244
iii.
Troubleshooting Endpoints 245
iv.
Using Security Catalog Views 246
v.
Lesson Summary
247
·
Group Policy items determine account lockout
settings.
·
The ALTER LOGIN Transact-SQL statement can
unlock locked SQL Server-authenticated logins and force password changes.
·
Windows authentication mode disables SQL Server
authentication mode. Mixed authentication mode uses both Windows authentication
and SQL Server authentication.
·
Verify the expiry dates of certificates when
troubleshooting certificate-based security. Use the sys.certificates catalog
view to view certificate properties.
·
Use the sys.endpoints catalog view to view
endpoint information.
·
Use the sys.server_principals catalog view to
determine whether a principal is disabled.
c.
Lesson 3: Auditing SQL Server Instances . . . .
. . . . . . .. . . . . . . . . . . . . .250
i.
Using SQL Server Audit 250
ii.
Configuring Login Auditing 262
iii.
Using c2 Audit Mode 263
iv.
Common Criteria Compliance 264
v.
Policy-Based Management 264
vi.
Lesson Summary
270
·
SQL Audit enables you to track specific actions
on the instance or database level.
·
SQL Audit can write audit data to the Windows
Security or Windows Application log. Audit data can also be written to a normal
file.
·
An audit can be configured so that the instance
shuts down in the event of an audit failure.
·
Action groups and actions determine which
activity is audited.
·
You can create a server or database audit
specification only after a server audit has been configured.
Chapter 7 Mirroring and Replication
279
a.
Lesson 1: Mirroring Databases . . . . . . . .. .
. . . .. . . . . . . . . . . . . . . . . . .280
i.
Database Mirroring 280
ii.
Mirroring Prerequisites 281
iii.
Configuring Mirroring with Windows
Authentication 285
iv.
Configuring Mirroring with Certificate
Authentication 288
You use certificate-based authentication when configuring
mirroring between instances when a domain-based account is not used as the
service account for SQL Server. You cannot configure certificate-based
authentication for mirroring by using SQL Server Management Studio; you must
perform this operation by using Transact-SQL.
v.
Changing Operating Modes 290
vi.
Role Switching and Failover 291
vii.
Monitoring Mirrored Databases 292
viii.
Upgrading Mirrored Databases 294
ix.
Lesson Summary
298
·
A database must use the full recovery model
before it can be mirrored.
·
Only one principal and one mirror instance can
participate in a mirroring session.
·
High-safety mode, also known as synchronous
mode, commits transactions when they are synchronized on both partners. This
mode has higher transaction latency.
·
High-performance mode, also known as
asynchronous mode, has lower transaction latency because transactions are
committed before partner synchronization. This mode can lead to data loss if
the primary fails.
·
The principal, mirror, and witness instance must
be running the same version of SQL Server when configuring a new mirroring
session.
·
If the SQL Server service is not associated with a domain-based account
but a local account or virtual account, you must configure certificate-based
authentication.
·
Automatic failover is possible only if the
mirroring session is in high-safety mode with a witness.
b.
Lesson 2: Database Replication . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . .300
i.
Replication Architecture 300
ii.
Replication Types 302
iii.
Snapshot Replication 303
iv.
Transactional Replication 307
v.
Peer-to-Peer Transactional Replication 309
Peer-to-peer replication provides a scale-out
and high-availability solution by maintaining copies of data across multiple
server instances, also referred to as nodes. Built on the
foundation of transactional replication, peer-to-peer replication propagates
transactionally consistent changes in near real-time. This enables applications
that require scale-out of read operations to distribute the reads from clients
across multiple nodes. Because data is maintained across the nodes in near
real-time, peer-to-peer replication provides data redundancy, which increases
the availability of data.
Consider a Web application. This can benefit
from peer-to-peer replication in the following ways:
·
Catalog queries and
other reads are spread across multiple nodes. This enables performance to
remain consistent as reads increase.
·
If one of the nodes in
the system fails, an application layer can redirect the writes for that node to
another node. This maintains availability.
·
If a node requires
maintenance or the whole system requires an upgrade, each node can be taken
offline and added back to the system without affecting the availability of the
application.
Although peer-to-peer replication enables
scaling out of read operations, write performance for the topology is like that
for a single node. This is because ultimately all inserts, updates, and deletes
are propagated to all nodes. Replication recognizes when a change has been
applied to a given node and prevents changes from cycling through the nodes
more than one time. We strongly recommend that write operations for each row be
performed at only node, for the following reasons:
·
If a row is modified
at more than one node, it can cause a conflict or even a lost update when the
row is propagated to other nodes.
·
There is always some
latency involved when changes are replicated. For applications that require the
latest change to be seen immediately, dynamically load balancing the
application across multiple nodes can be problematic.
Peer-to-peer replication includes the option
to enable conflict detection across a peer-to-peer topology. This option
helps prevent the issues that are caused from undetected conflicts,
including inconsistent application behavior and lost updates. By enabling
this option, by default a conflicting change is treated as a critical error
that causes the failure of the Distribution Agent. In the event of a conflict,
the topology remains in an inconsistent state until the conflict is resolved
manually and the data is made consistent across the topology.
vi.
Merge Replication 311
vii.
Replication Monitor 315
viii.
Controlling Replication of Constraints, Columns,
and Triggers 317
ix.
Heterogeneous Data 318
x.
Lesson Summary
320
·
Snapshot replication enables complete refreshes
of all data rather than refreshing on an incremental basis. This is suitable
when data changes infrequently and small volumes must be replicated.
·
Transactional replication is suitable when a
subscriber database must be up to date with the publisher but does not need to
update the publisher.
·
Merge replication is suitable for distributed
server applications when conflict is possible.
·
Peer-to-peer replication is a form of
transactional replication by which nodes can read and write changes, but you
must partition data to avoid conflicts. You cannot use row or column filtering with peer-to-peer
replication, because the entire data on a node is published to all other nodes.
·
You must use a shared folder to publish
snapshots if distribution and merge agents are running at the subscriber
instance rather than at the publisher instance.
·
Replication Monitor enables you to monitor
replication and configure replication alerts.
·
SQL Server 2012 can subscribe to an Oracle
publication and can function as a publisher to Oracle and IBM DB2 transactional
and snapshot replication.
Chapter 8 Clustering and AlwaysOn
327
a.
Lesson 1: Clustering SQL Server 2012 . . . . . .
. . . . . . . . . . . . . . . . . . . . .328
i.
Fulfilling Edition Prerequisites 328
ii.
Creating a Windows Server 2008 R2 Failover
Cluster 332
iii.
Installing a SQL Server Failover Cluster 334
iv.
Multi-Subnet Failover Clustering 338
v.
Performing Manual Failover 339
vi.
Troubleshooting Failover Clusters 340
vii.
Lesson Summary
344
·
A Windows Server Failover Cluster must be
created prior to installing a failover cluster instance.
·
Windows Server 2008 Enterprise and Datacenter
editions and Windows Server 2008 R2 Enterprise and Datacenter editions can
function as host operating systems for failover cluster instances.
·
To install a failover cluster instance, first
run advanced cluster preparation on all nodes and then run advanced cluster
completion on the node that has control of the shared storage device.
·
Multi-subnet failover clusters have nodes on
separate TCP/IP subnets.
·
Use the Failover Cluster Manager console or the
Move-ClusterGroup PowerShell cmdlet to perform manual failover.
·
In the event of hardware failure, evict the
failed node from the cluster and then join it after it is repaired before
reinstalling SQL Server.
b.
Lesson 2: AlwaysOn Availability Groups . . . . .
. . . . . . . . . . . . . . . . . . . .346
i.
What Are AlwaysOn Availability Groups? 346
ii.
Meeting Availability Group Prerequisites 347
iii.
Configuring Availability Modes 347
iv.
Selecting Failover Modes 349
v.
Configuring Readable Secondary Replicas 352
vi.
Deploying AlwaysOn Availability Groups 353
vii.
Using Availability Groups on Failover Cluster
Instances 360
viii.
Lesson Summary
364
·
The AlwaysOn Availability Groups feature is an
alternative to database mirroring.
·
Availability groups are supported in production
on SQL Server 2012 Enterprise edition only.
·
An AlwaysOn availability group can have one
primary and four secondary replicas.
·
You must create mirroring endpoints either
before or during the availability group creation process.
·
An availability group replica can contain
multiple databases.
·
You can configure secondary replicas to be
available to read-only queries.
·
Failover occurs on a per-replica basis.
·
Synchronous-commit mode involves higher
transaction latency but allows manual and automatic failover.
·
Asynchronous-commit mode minimizes transaction
latency, is suitable for geographically dispersed clusters, but only supports
forced failover.
·
You can perform availability group failover by
using SQL Server Management Studio, the ALTER AVAILABILITY GROUP Transact-SQL
statement, or the Switch-SqlAvailabilityGroup PowerShell cmdlet.
·
You can have only one listener per availability
group.
Chapter 9 Troubleshooting SQL Server 2012
371
a.
Lesson 1: Working with Performance Monitor . . .
. . .. . . . . . . . . . . . . .372
i.
Getting Started with Performance Monitor 372
ii.
Capturing Performance Monitor Data 374
iii.
Creating Data Collector Sets 376
iv.
Lesson Summary
377
·
Performance Monitor captures numeric statistics
about hardware and software components.
·
Counters are organized into a three-level
hierarchy: counter object, counter, and counter instance.
·
A counter object must have at least one counter.
·
A counter can have zero or more instances.
·
You capture counter logs with Performance
Monitor to perform analysis.
·
Use Performance Monitor to capture metrics for
Windows Server and SQL Server.
b.
Lesson 2: Working with SQL Server Profiler . . .
. . . . . . . . . . . . . . . . . . .379
i.
Capturing Activity with SQL Server Profiler 379
ii.
Understanding SQL Trace 384
iii.
Reviewing Trace Output 385
iv.
Capturing Activity with Extended Events Profiler
385
v.
Lesson Summary
387
·
Profiler is the utility that enables you to
interact graphically with the SQL Trace application programming interface
(API).
·
SQL Trace exposes events that can be captured to
audit actions, monitor an instance, examine baseline queries, and troubleshoot
performance issues.
·
You can specify the columns of data you want to
capture for a given event.
·
Trace output can be limited by applying filters.
·
Use SQL Trace for large and long-running traces.
·
Use SQL Server Profiler to define traces and
then script them for SQL Trace.
c.
Lesson 3: Monitoring SQL Server . . . . . . . .
. . . . . . . . . . . . . . . . . . . .
. .389
i.
Monitoring Activity 389
ii.
Working with Activity Monitor 392
iii.
Lesson Summary
393
·
Use dynamic management objects for real-time
monitoring and troubleshooting.
·
Use Activity Monitor for easy access to
performance information.
·
The sys.dm_db_* DMVs provide general space and
index usage information.
·
The sys.dm_exec_* DMVs return information about
currently executing queries and queries that are still in the query cache.
d.
Lesson 4: Using the Data Collector Tool . . . . .
. . . . . . . . . . . . . . . . . . . .395
i.
Capturing and Managing Performance Data 395
ii.
Analyzing Collected Performance Data 399
iii.
Lesson Summary
401
·
The data collector can query DMVs and DMFs to
retrieve detailed information about the operation of the system.
·
The data collector can retrieve performance
counters that provide metrics about the performance of both SQL Server and the
server.
·
The data collector can capture SQL Trace events
that have occurred.
·
Use a central management data warehouse to
capture historical performance information.
·
Use a data collection to gather performance data
for SQL Server instances.
e.
Lesson 5: Identifying Bottlenecks . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . .403
i.
Monitoring Disk Usage 403
ii.
Monitoring Memory Usage 405
iii.
Monitoring CPU Usage 406
iv.
Lesson Summary
407
·
Monitor an instance of SQL Server periodically
to confirm that memory usage is within typical ranges.
·
Disk I/O is frequently the cause of bottlenecks
in a system.
·
Monitor an instance of Microsoft SQL Server
periodically to determine whether CPU usage rates are within normal ranges. A
continually high rate of CPU usage can indicate the need to upgrade the CPU or
add multiple processors. A high CPU usage rate can also indicate a poorly tuned
or designed application. Optimizing the application can lower CPU usage
Chapter 10 Indexes and Concurrency
417
a.
Lesson 1: Implementing and Maintaining Indexes .
. . . .. . . . . . . . . . . .418
i.
Understanding the Anatomy of a Balanced Tree
(B-Tree) 418
ii.
Understanding Index Types and Structures 420
iii.
Designing Indexes for Efficient Retrieval 423
iv.
Understanding Statistics 428
v.
Creating and Modifying Indexes 430
vi.
Tracking Missing Indexes 437
vii.
Reviewing Unused Indexes 437
viii.
Lesson Summary
440
·
Most SQL Server indexes are balanced tree
(B-Tree) structures, with the exception of PRIMARY XML indexes and columnstore
indexes.
·
Clustered indexes organize the data in a table
in the logical order of the clustering keys.
·
A covering index can improve performance by
containing all the data necessary to satisfy the query without requiring
additional data access. Although clustered indexes are always covering indexes,
they often are not the most efficient. [Although every clustered index is a covering index (that
is, the query can be satisfied only by accessing the index), a clustered index
is often not the most efficient index. A clustered index by definition must
return the entire row of data. A covering non-clustered index might require
only a subset of columns from the table to satisfy the query. Because the
non-clustered index has more rows per page than a clustered index, less disk
I/O is required to satisfy the query.]
·
The query optimizer requires indexes and
statistics to create optimal execution plans.
·
SQL Server automatically maintains statistics on
your behalf; however, sometimes manually created or updated statistics can be
beneficial.
·
Although indexes can improve SELECT performance,
having too many indexes to maintain can result in decreased performance of
INSERT, UPDATE, and DELETE statements.
b.
Lesson 2: Identifying and Resolving Concurrency
Problems . . . . . . . . .442
i.
Defining Transactions and Transaction Scope 442
ii.
Understanding SQL Server Lock Management 442
iii.
Using AlwaysOn Replicas to Improve
Concurrency 449
iv.
Detecting and Correcting Deadlocks 450
v.
Using Activity Monitor 452
vi.
Diagnosing Bottlenecks 453
vii.
Using Reports for Performance Analysis 454
viii.
Lesson Summary
457
·
Transactions determine how long SQL Server will
hold locks; this impacts concurrency.
·
SQL Server uses dynamic lock management to
improve concurrency.
·
Read Committed is the default transaction
isolation level.
·
The Read Committed Snapshot Isolation database
option can limit reader/writer blocking.
·
SQL Server provides many tools to help identify
and resolve blocking and deadlocking.
Chapter 11 SQL Server Agent, Backup, and Restore 465
a.
Lesson 1: Managing SQL Server Agent . . . . . .
. .. . . . . . . . . . . . . . . . . .466
i.
Executing Jobs by Using SQL Server Agent 466
ii.
Managing Alerts
471
iii.
Managing Jobs
474
iv.
Monitoring Multi-Server Environments 481
v.
Lesson Summary
484
·
SQL Server Agent is disabled by default.
·
Each Database Engine instance has a separate SQL
Server Agent service.
·
The account used for the SQL Server Agent service must be a member of
the sysadmin fixed server role.
·
SQLAgentUserRole enables members to have permissions on jobs
they own; SQLAgentReaderRole
enables members to view the properties and history of jobs; and SQLAgentOperatorRole
enables members to execute and modify the schedule of all local jobs. (SQL Server 2005 introduces the following msdb database
fixed database roles, which give administrators finer control over access to
SQL Server Agent. The roles listed from least to most privileged access are:
o SQLAgentUserRole
o SQLAgentReaderRole
o SQLAgentOperatorRole
)
·
Operators can be notified by email, net send, or
pager; the fail-safe operator is contacted if the configured operator cannot be
contacted.
·
A job is a collection of job steps, which are
independent tasks and can include command-line commands, Transact-SQL
statements, and Windows PowerShell scripts.
·
You can configure schedules for jobs.
·
Jobs can be triggered by performance conditions.
·
You can monitor job status and history by using
the Job Activity Monitor.
·
Multi-server environments enable you to run jobs
across multiple servers.
·
In multi-server environments, target servers
report to master servers.
b.
Lesson 2: Configuring and Maintaining a Backup
Strategy . . . . . . . . . .487
i.
Understanding Backup Types 487
ii.
Backing Up System Databases 491
iii.
Backing Up Replicated Databases 492
iv.
Backing Up Mirrored Databases 493
v.
Backing Up AlwaysOn Replicas 493
vi.
Using Database Checkpoints 494
vii.
Using Backup Devices 495
viii.
Backing Up Media Sets 497
ix.
Performing Backups 497
x.
Viewing Backup History 501
xi.
Lesson Summary
502
·
Full database backups back up all database
objects, system tables, and data.
·
Differential backups back up all data that has
changed since the last full backup.
·
Transaction log backups back up all transaction
log data that has been generated since the last transaction log backup.
·
File and filegroup backups enable you to back up
large databases.
·
Copy backups enable you to perform backups
without affecting the existing backup sequence.
·
The system databases use the simple recovery
model by default.
·
You should back up the master and msdb databases
regularly. You should back up the model database when you make changes to it.
o
It is important to back
up model or msdb after any operation that modifies the
database.
o
The model database is the template used by Microsoft SQL Server when
you create a user database. The entire contents of the model database, including database options, are copied to the new
database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always
exist on a SQL Server system. Newly created user databases use the same
recovery model as the model database. The default is user configurable.
·
You can view backup history by using the log
file viewer (SSMS|Management|SQL
Server Logs, or SQL Server Agent|Error Logs) with a filter by setting event to
backup, or by querying msdb.dbo.backupset.
c.
Lesson 3: Restoring SQL Server Databases . . . .
. . . . . . . . . . . . . . . . . . .504
i.
Restoring Databases 504
ii.
Performing File Restores 508
iii.
Performing Page Restores 509
iv.
Restoring a Database Protected with Transparent
Data
v.
Encryption
511
vi.
Restoring System Databases 511
vii.
Restoring Replicated Databases 512
viii.
Checking Database Status 512
ix.
Lesson Summary
514
·
A full backup–only strategy enables you to
restore only to the point at which the full backup was taken.
·
A full and differential backup strategy enables
you to restore to the point at which the last differential backup was taken.
·
A full and transaction log strategy, or a full,
differential, and transaction log strategy enables you to restore to a specific
transaction or point in time.
·
You should perform a tail-log backup of the
current database, if possible, prior to attempting a restore operation.
·
The RESTORE WITH RECOVERY option returns a
database to normal operation.
·
The RESTORE WITH NORECOVERY option recovers the
database, but the database cannot be accessed.
·
The RESTORE WITH STANDBY option leaves the
database in read-only mode.
·
Only SQL Server 2012 Enterprise edition supports
online file and page restores.
·
When performing an online file restore, you must perform a
backup after performing the file restore and transaction log restore to ensure
that the database is consistent.
·
You can rebuild the master, msdb, and model
databases by using setup.exe.
