Pro SQL Server 2012 Administration

494 Pages·2012·52.89 MB·English

www.finebook.ir
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
www.finebook.ir
Contents at a Glance
Contents..................................................................................................................vii
About the Authors................................................................................................xxiii
PART 1: Introducing Microsoft SQL Server 2012......................................................1
■ Chapter 1: New Features Overview......................................................................3
■ Chapter 2: Pre-Installation Considerations........................................................15
■ Chapter 3: Choosing a High-Availability Solution..............................................33
PART 2: Getting Started..........................................................................................59
■ Chapter 4: Installing and Upgrading..................................................................61
■ Chapter 5: Post-Installation...............................................................................99
PART 3: Administering Microsoft SQL Server 2012..............................................125
■ Chapter 6: Multi-Server Administration...........................................................127
■ Chapter 7: Managing Security Within the Database Engine.............................159
■ Chapter 8: Working with Database Objects.....................................................183
■ Chapter 9: Indexing for Performance...............................................................223
■ Chapter 10: Managing Backups.......................................................................257
■ Chapter 11: Restore and Recovery Strategies.................................................283
■ Chapter 12: Automating Routine Maintenance................................................319

v
www.finebook.ir
PART 4: Troubleshooting and Tuning....................................................................353
■ Chapter 13: Monitoring Your Server................................................................355
■ Chapter 14: Auditing SQL Server......................................................................389
■ Chapter 15: Extended Events Interface............................................................403
■ Chapter 16: Managing Query Performance......................................................421
■ Chapter 17: Secrets to Excelling as a Professional DBA..................................451
■ Chapter 18: What’s Next?................................................................................463
Index.....................................................................................................................471

vi
www.finebook.ir
C H A P T E R 1

■ ■ ■

New Features Overview
The release of Microsoft SQL Server 2012 has introduced many new features that increase scalability,
manageability, availability, programmability, and security across the enterprise. With many
organizations focused on consolidation and virtualization, this couldn’t have come at a better time. As
the demand for data keeps growing and security and compliance keep tightening, the role of the
database administrator (DBA) has become an increasingly critical part of the organization. It is
important for every DBA to have a good understanding of the tools available to help maintain a highly
available, secure environment.
This book will cover the techniques you need to understand to implement and manage a successful
database environment. After a brief overview of some of the enhancements, you will learn how to make
intelligent decisions when choosing an installation or upgrade path. You will also learn how to manage a
secure and consistent database environment by implementing policies across the organization. By
learning how to automate tedious administrative tasks, you can focus on more important tasks, like
performance tuning. Finally, we will look at what the future holds for database administration, as well as
give you the resources necessary to excel as a DBA.
This chapter presents an overview of several new features available in SQL Server 2012.Although this
chapter will not cover every detail of every new feature available in SQL Server 2012, it will provide a
brief introduction to many of the major enhancements. We dig deeper into many of these features as we
progress through the book.
Availability Enhancements
Availability is becoming a greater concern with many organizations wanting to achieve four and five
nines. Since achieving four nines allows for less than one hour of downtime per year and achieving five
nines allows for less than six minutes per year, living up to this expectation is not an easy task. There
have been improvements in many areas to help achieve this goal, including the addition of the new
AlwaysOn functionality.
Online Index Rebuilds
SQL Server 2005 introduced the ability to rebuild an index online with a few limitations that have
remained consistent through SQL Server 2008 R2. First, you could not rebuild an XML or Spatial (SQL
2008 or later) index online. Second, you could not rebuild an index online if the index contained the
large object datatype columns xml, varchar(max), nvarchar(max), varbinary(max), image, text, or ntext.
Prior to SQL Server 2012, if you needed to do online index maintenance on indexes with these
limitations, your only option was to reorganize the index.
Starting with SQL Server 2012, you can now perform online index rebuilds for indexes containing
xml, varchar(max), nvarchar(max), or varbinary(max) data types. You still cannot rebuild indexes online

3
www.finebook.ir
CHAPTER 1 ■ NEW FEATURES OVERVIEW

that contain image, text, or ntext data types, but these data types are deprecated and should be replaced
with the new (max) data types anyway. The enhancements made to online index rebuilds could easily
span multiple categories, as rebuilding indexes online allows for more flexible management capabilities.
You will find this to be true with several features discussed throughout the chapter.
Indirect Checkpoints
When a checkpoint occurs, SQL Server writes the dirty pages in memory to disk to provide a consistent
point for database recovery. Although checkpoints occur automatically, based either on workload or
triggered by certain actions, such as a database backup, there are a few things you can do to influence
when they occur. Prior to SQL Server 2012, you could set an instance wide recovery interval setting using
sp_configure or manually issue a database checkpoint using the T-SQL CHECKPOINT command. The
default behavior for SQL Server is to try to maintain a recovery time of one minute. Increasing the
recovery interval setting causes SQL Server to take checkpoints less often, whereas decreasing the
recovery interval will cause checkpoints to occur more often.
New to SQL Server 2012 is the ability to override the instance level recovery interval at the database
level using the TARGET_RECOVERY_TIME option of the ALTER DATABASE command. The recovery
time can be set using seconds or minutes. For example, to change the recovery time for the
AdventureWorks 2008R2 database to five minutes, you can issue the following command:
ALTER DATABASE AdventureWorks2008R2
SET TARGET_RECOVERY_TIME = 5 MINUTES
Typically, you will want to keep the default behavior; however, if you run into performance issues
because of numerous checkpoints or you would like a faster recovery time, you have the option. Keep in
mind that changing the target recovery time can lead to performance issues and may not increase your
recovery time at all if your workload has long running transactions.
Clustering
Clustering has always been a major factor when determining a high availability strategy and Microsoft
continues to add features to improve this technology. In SQL Server 2012, Microsoft has added support
for multi-subnet clustering, an improved and more flexible failover policy, as well as the placement of
tempdb on a local drive. Being able to place the tempdb on a local drive can offer major performance
gains especially if you add solid state drives into the equation. Multi-subnet clustering has been
supported on Windows since Windows Server 2008; however, it was not supported in SQL Server. Now
that multi-subnet clustering is available in SQL Server 2012, this opens the door to a native
geographically dispersed clustering solution within SQL Server. You can find more on multi-subnet
clustering at http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx. In addition, SQL
Server 2012 uses the Microsoft cluster service (MSCS) as a key component in the new AlwaysOn feature.
AlwaysOn
AlwaysOn is the big new high availability/disaster recovery feature in SQL Server 2012. In fact, it was
initially referred to as HADRON (High Availability Disaster Recover Always On). AlwaysOn basically
provides the best of both worlds in clustering and database mirroring. AlwaysOn uses clustering
technology for fail-over, while also keeping multiple mirrored copies of the database. AlwaysOn removes
the disk as a single point of failure formerly encountered with clustering, as well as provides new
features, such as a read-only copy of the database, which was not previously allowed by database
mirroring. Obviously, AlwaysOn gives DBAs many additional options in terms of availability and
recovery, so we cover it in greater detail in chapter 3.
4
www.finebook.ir
CHAPTER 1 ■ NEW FEATURES OVERVIEW

Manageability Enhancements
There have been some nice features added in SQL Server 2012 to enhance your management
capabilities. You may not easily find many of the new features from within the GUI, but they open
several options from an administrative perspective. For example, you can now use the plan cache as a
work load for the Database Engine Tuning Advisor. You can use many of the new features, such as the
Extended Events GUI, to attain more insight into your servers. In addition, you can use features, such as
Contained Databases, to attain more granular control of your environment.
Extended Events
Although Extended Events have been available since SQL Server 2008, Microsoft has added an Extended
Events user interface in SQL Server 2012 to ease usability. In addition to the standard user interface,
Microsoft has also included a Wizard, as shown in Figure 1-1, to further help with configuration. Prior to
SQL Server 2012, Extended Events had a very sharp learning curve and parsing the xml data was
unpleasant to say the least. The addition of these features should help bring the power offered by
Extended Events to the DBA for daily monitoring and troubleshooting. Chapter 15 provides further detail
about Extended Events.

Figure 1-1. Extended Events New Session Wizard
5
www.finebook.ir
CHAPTER 1 ■ NEW FEATURES OVERVIEW

Database Restores
SQL Server Management Studio has been enhanced in several areas, when it comes to restoring
databases as well. First, Microsoft has added an easy way to check for and repair corruption in database
pages, as shown in Figure 1-2. The repair pages grid displays any records that appear in the
suspect_pages table in the msdb database. You can also execute a DBCC CHECKDB WITH
PHYSICAL_ONLY command against the database to populate the grid. Finally, you can simply select the
Add button to populate the grid manually.

Figure 1-2. Page Restore – General Page
Another interesting new feature is the visual backup timeline, as shown in Figure 1-3.
6
www.finebook.ir
CHAPTER 1 ■ NEW FEATURES OVERVIEW

Figure 1-3. Backup Timeline
The backup timeline shows you when your backups were taken, as well as the type of backup. You
can move the slider along the timeline at any point within the transaction log backup to create a
STOPAT datetime parameter for the restore command. Chapter 11 provides more information about
restoring databases.
Contained Databases
A contained database is a new feature in SQL Server 2012 that eases many of the issues related to
external database dependencies, such as logins or references to other databases. A contained database
has everything it needs to function properly within the database itself. A fully contained database has no
external dependencies, making movement between servers without breaking functionality easy.
However, fully contained databases will not be completely implemented in SQL Server 2012. Although
databases can only be partially contained using SQL Server 2012, this is a huge step in the right direction
when manually moving databases between servers or when databases automatically move using
features, such as AlwaysOn. Chapter 8 has additional detail about contained databases.
Programmability Enhancements
Several programming enhancements have been added to SQL Server 2012. The main focus of this book
is database administration, but having a good grasp on T-SQL and keeping up with the new commands
is important for every DBA. SQL Server 2012 contains several new T-SQL capabilities, ranging in
everything from completely new functions to additions to existing constructs, such as the ability to use
the new THROW statement in a TRY/CATCH block. Since we do not discuss T-SQL specifically
7
www.finebook.ir
CHAPTER 1 ■ NEW FEATURES OVERVIEW

throughout the rest of the book, this section introduces you to some of the new programming
enhancements found in SQL Server 2012.
Functions
Several new functions are available in SQL Server 2012 to help make coding T-SQL an easier task. There
are new functions that you may already be familiar with, such as IIF, already available in many other
Microsoft applications. A good number of the functions provide new options when working with date
and time values, whereas others provide new capabilities when parsing and converting data. The
following is a list of new functions available in SQL Server 2012.
• CHOOSE. This function returns an item from a list based on a specified index. The
data type that is returned is based on the data type with the highest precedence
within the list. The first parameter is the index of the value within the list you wish
to return. The remaining n number of values constitutes the list. For example,
SELECT CHOOSE (2, 'ValueA', 'ValueB', 'ValueC') will return “ValueB.”
• IIF. The IIF function takes three arguments. The first is a Boolean expression
followed by two possible values. If the expression equates to true, the first
expression is returned; if the expression is false, the second value is returned. For
example, SELECT IIF (1 > 2, 'ValueA', 'ValueB') returns “ValueB,” since the
expression in the first argument is false.
• CONCAT. The CONCAT function returns a concatenated string based on a list of
values. The input values are converted to strings, and NULL values are converted
to empty strings. For example, SELECT CONCAT (1, '- Example ', 'Concat - ',
NULL, GETDATE()) returns “1- Example Concat - Sep 28 2011 12:17PM.”
• FORMAT. This function returns a value based on the format supplied to the
function. The FORMAT function takes numeric or date and time value as the first
parameter and returns a value based on the format string of the second
parameter. The format argument is based on the .Net framework format strings.
You can provide an optional third parameter to specify the culture in which to
format. For example, SELECT FORMAT(555231234,'###-##-####','en-US') will
return “555-23-1234,” SELECT FORMAT(10,'C','en-US') will return “$10.00,” and
SELECT FORMAT(GETDATE(),'MM/dd/yy','en-US') will return “09/28/11.”
• PARSE. The PARSE function converts a string value to a numeric or date and time
data type. You can optionally specify the culture to use for the translation. If the
translation is not possible, the function returns an error. For example, SELECT
PARSE ('28 de julio de 2011 ' AS DATETIME USING 'es-ES') returns “2011-07-28
00:00:00.000” using the Spanish culture; however, SELECT PARSE ('28 de julio de
2011 ' AS DATETIME USING 'en-US') fails using the English culture.
• TRY_PARSE. TRY_PARSE converts a string value to a numeric or date and time
data type. You can optionally specify the culture to use for the translation.
TRY_PARSE is very similar to PARSE; however, if the translation is not possible, the
TRY_PARSE returns NULL instead of an error. For example, SELECT TRY_PARSE
('28 de julio de 2011 ' AS DATETIME USING 'en-US') will return a NULL value.
• TRY_CONVERT. This function converts a data type to another specified data type.
If the conversion is not possible, the function returns a NULL value. Just like the
CONVERT function, you can optionally specify a style in which to convert the
data. The query SELECT TRY_CONVERT (INT, '1^234') will return a NULL value,
and SELECT TRY_CONVERT (INT, '1234') will return the integer value 1234.
8
www.finebook.ir