> m o ok.c o b e w o w w. w w < ok o B e w! o W m o d fr a o nl ow For your convenience Apress has placed some of the front D matter material after the index. Please use the Bookmarks and Contents at a Glance links to access them. 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 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 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 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 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 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 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 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