Microsoft SQL Server 2008 R2 Administration Cookbook


Microsoft SQL Server 2008 R2 Administration Cookbook
eBook: $32.99
Formats: PDF, PacktLib, ePub and Mobi formats
$28.04
save 15%!
Print + free eBook + free PacktLib access to the book: $87.98    Print cover: $54.99
$54.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Overview
Table of Contents
Author
Reviews
Support
Sample Chapters
  • Provides Advanced Administration techniques for SQL Server 2008 R2 as a book or eBook
  • Covers the essential Manageability, Programmability, and Security features
  • Emphasizes important High Availability features and implementation
  • Explains how to maintain and manage the SQL Server data platform effectively

Book Details

Language : English
Paperback : 468 pages [ 235mm x 191mm ]
Release Date : May 2011
ISBN : 1849681449
ISBN 13 : 9781849681445
Author(s) : Satya Shyam K Jayanty
Topics and Technologies : All Books, Enterprise Products and Platforms, Microsoft Servers, Cookbooks, Enterprise, Microsoft, Microsoft SQL Server

Table of Contents

Preface
Chapter 1: Getting Started with SQL Server 2008 R2
Chapter 2: Administrating the Core Database Engine
Chapter 3: Managing the Core Database Engine
Chapter 4: Administering Core Business Intelligence Services
Chapter 5: Managing Core SQL Server 2008 R2 Technologies
Chapter 6: Improving Availability and enhancing Programmability
Chapter 7: Implementing New Manageability Features and Practices
Chapter 8: Maintenance and Monitoring
Chapter 9: Troubleshooting
Chapter 10: Learning the Tricks of the Trade
Appendix: More DBA Manageability Best Practices
Index
  • Chapter 1: Getting Started with SQL Server 2008 R2
    • Introduction
    • Adding SQL Server 2008 R2 Service Pack features using Slipstream technology
    • Designing a best practice approach to upgrading to SQL Server 2008 R2
    • Working with Data-Tier applications
    • Designing and adopting SQL Server 2008 R2 solutions
    • Designing applications to use federated servers
    • Chapter 2: Administrating the Core Database Engine
      • Introduction
      • Designing automated administration practices
      • Implementing Security feature enhancements
      • Implementing Availability feature enhancements
      • Implementing, Monitoring, and Tuning for performance
      • Administering SQL Server workloads with Resource Governor
      • Designing SQL Server Agent scheduled jobs for ETL processes
      • Troubleshooting multi-server instances with utility administration
      • Administering SQL Server Replication processes
      • Chapter 3: Managing the Core Database Engine
        • Introduction
        • Implementing Central Management feature enhancements
        • Designing Multi-server management from SQL Server 2008 R2
        • Managing the Utility Control Point data warehouse database
        • Implementing Utility & Non-utility collection sets
        • Designing and refreshing a Scalable Shared database
        • Managing SQL Server Replication processes
        • Implementing security for SQL Server Agent jobs management
        • Multiserver Management Using Utility Explorer
        • Chapter 4: Administering Core Business Intelligence Services
          • Introduction
          • Preparing and installing SQL Server Analysis Services
          • Implementing Scale-Out of SQL Server Analysis Services
          • Administering SQL Server Reporting Services
          • Implementing Scale-Out deployment of SQL Server Reporting Services
          • Preparing and installing SQL Server Integration Services
          • Managing the ETL process efficiently
          • Chapter 5: Managing Core SQL Server 2008 R2 Technologies
            • Introduction
            • Planning and implementing Self-Service Business Intelligence services
            • Implementing Microsoft StreamInsight Technologies Platform
            • Implementing SQL Azure connectivity features
            • Installing and configuring a Master Data Services Solution
            • Designing and deploying framework to use Master Data Services
            • Chapter 6: Improving Availability and enhancing Programmability
              • Introduction
              • Preparing new Failover Cluster features
              • Installing and configuring Failover Cluster Services
              • Recovering and troubleshooting Failover Cluster Failure
              • Implementing Database Mirroring features and performance enhancements
              • Managing Database Mirroring in a Failover Cluster environment
              • Managing Log Shipping scenarios in a Database Mirroring environment
              • Improving Replication scalability at multiple sites
              • Implementing compressed storage features for tables and indexes
              • Designing a storage solution for unstructured data and new collations
              • Designing data partitioning solutions for tables and indexes
              • Implementing sparse columns and wide table features
              • Designing spatial data storage methods
              • Deploying and managing data-tier applications
              • Chapter 7: Implementing New Manageability Features and Practices
                • Introduction
                • Implementing auditing and service level security features
                • Managing server-level securable and database-level permissions
                • Implementing backup compression for a VLDB environment
                • Designing change tracking methodology
                • Implementing Policy-Based Management features
                • Implementing and running PowerShell for SQL Server features
                • Implementing the SQL Server Best Practices Analyzer
                • Designing Transparent Data Encryption features
                • Chapter 8: Maintenance and Monitoring
                  • Introduction
                  • Implementing performance baseline practices
                  • Monitoring resource usage with Extended Events
                  • Implementing Management Data Warehouse features
                  • Designing maintenance tasks on a mission-critical environment
                  • Implementing Piecemeal restore strategies
                  • Planning and designing of a Disaster Recovery environment
                  • Implementing sustainable index maintenance practices for a VLDB & 24/7 environment
                  • Configuring a manageable database consistency and integrity process for multiple databases and a VLDB environment
                  • Configuring a server storage system for scalability
                  • Configuring SQL server storage architecture for quick recovery
                  • Managing a storage system for analyzing bottlenecks
                  • Chapter 9: Troubleshooting
                    • Introduction
                    • Implementing systematized monitoring methods for data platform optimization
                    • Designing a filtered indexes and statistics feature
                    • Implementing table hints for advanced query tuning performance
                    • Implementing query hints for optimized performance
                    • Designing sustainable locking methods to improve concurrency
                    • Implementing parallel query processing methods
                    • Implementing the plan guide to enhance compile-time and
                    • run-time execution plans
                    • Configuring and managing storage systems for optimized defragmentation processes
                    • Building best usage processes of Dynamic Management Views
                    • Implementing a benchmarking framework on multiple instances
                    • Chapter 10: Learning the Tricks of the Trade
                      • Introduction
                      • Automated administration across an enterprise
                      • Administrating resource health policies
                      • Administrating multiple Analysis Services using Tools
                      • Managing events capture to troubleshoot problems

                      Satya Shyam K Jayanty

                      Satya Shyam K Jayanty is an Independent consultant working as a Principal Architect and Director for D Bi A Solutions Limited based in the UK. He has a Master's degree in Economics and Commerce from Osmania University, Hyderabad (India) and an MBA – Systems from the University of the West of England. Satya started his career in the IT industry in 1992, and he has been working with SQL Server (from version 4.2) for more than 15 years and has been an accomplished Microsoft SQL Server MVP for the last six years. Satya is a frequent speaker and SME volunteer at Microsoft Tech-Ed, SQL PASS, SQL Bits, Scottish Area SQL Server user group. He maintains a blog at http://www.sqlserver-qa.net and http://www.sql-server-performance.com.

                      Code Downloads

                      Download the code and support files for this book.


                      Submit Errata

                      Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                      Errata

                      - 7 submitted: last submission 31 Oct 2012

                      Errata type: Code | Page number: 69

                      Please use this TSQL code instead of the TSQL code given on page 69:

                      SELECT r.session_id, r.status, r.start_time, r.command, s.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.status='running' SELECT s2.dbid,s1.sql_handle,(SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2+1 , ((CASE WHEN statement_end_offset =-1 THEN (LEN(CONVERT(nvarchar(max),s2.text))* 2) ELSE statement_end_offset END)- statement_start_offset)/ 2+1))AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle)AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset; USE AdventureWorks2008R2; GO SET SHOWPLAN_ALL ON; GO SELECT Person.Contact.ContactID, Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.EmailAddress, HumanResources.Employee.Gender, HumanResources.Employee.BirthDate FROM HumanResources.Employee INNER JOIN Person.Contact ON HumanResources.Employee.ContactID=Person.Contact.ContactID GO SET SHOWPLAN_ALL OFF; GO SELECT TOP 10 wait_type, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('LAZYWRITER_SLEEP','SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE', 'CHECKPOINT_QUEUE','CLR_AUTO_EVENT','WAITFOR', 'BROKER_TASK_STOP','SLEEP_TASK','BROKER_TO_FLUSH') ORDER BY wait_time_ms DESC

                       

                      Also, use the following image instead of the image on page 69:

                      Errata type: Content | Page number: 69

                      Please insert this text after the image mentioned in the previous errata:

                      Based on the workload and transaction generation on my machine from the result, the top 10 wait types are presented, for instance PAGEIOLATCH_SH occurs when a particular processes is waiting on a latch for a buffer that is in I/O request and _SH represents request in shared mode. Heavy numbers represent a problem with disk subsystem and for further analysis look at PERFMON counters for Memory – Pages/Sec, Physical Disk – Avg. Disk Queue Length and Processor - %Processor time. As we can observe wait type FT_IFTS_SCHEDULER_IDLE_WAIT which is used as a background task process by full-text search requests which indicates it is waiting for work to do. At this point in time we can safely ignore the outcome unless there are complaints from the Users about application search functionality which uses Full-text search service. Further information on detailed information and interpretation of these wait types can be obtained from SQL Server Books Online that are published by Based on the workload and transaction generation on my machine from the result, the top 10 wait types are presented, for instance PAGEIOLATCH_SH occurs when a particular processes is waiting on a latch for a buffer that is in I/O request and _SH represents request in shared mode. Heavy numbers represent a problem with disk subsystem and for further analysis look at PERFMON counters for Memory – Pages/Sec, Physical Disk – Avg. Disk Queue Length and Processor - %Processor time. As we can observe wait type FT_IFTS_SCHEDULER_IDLE_WAIT which is used as a background task process by full-text search requests which indicates it is waiting for work to do. At this point in time we can safely ignore the outcome unless there are complaints from the Users about application search functionality which uses Full-text search service. Further information on detailed information and interpretation of these wait types can be obtained from SQL Server Books Online that are published by Microsoft. The results from sys.dm_os_wait_stats DMV produces another wait type SQLTRACE_INCREMENTAL_FLUSH_SLEEP value which indicates the internal trace, SQL trace or PROFILER process with a high value in number, which can be related to corresponding I/O problems on the server. Note: In order to get accurate information on the statistics of wait types based on the defined period of time, you can execute theDBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) statement on that SQL Server instance.

                       

                      Errata type: Image | Page number: 335

                      Replace the image after step 5 with the following image:

                       



                       


                      Errata type: Content | Page number: 336

                      Replace the step 8  with the following step:

                       

                      8.       At the screens Reorganize Index, Update Statistics, Back Up Database (Full), Back UP Database (Differential) and Back up Database (Transaction Log) at the Database(s) selection drop-box choose the These Databases button to select the user databases. Also ensure to select the Job Schedule carefully by avoiding any overlapping for each task depending upon the maintenance window of opportunity that we may have in the environment i.e., low traffic hours on database.

                      Errata type: Content | Page number: 360

                      Replace numbered bullet 4 with the following:

                      4.       The recommendation is to have L2 cache per CPU have a minimum of 2 MB to improve memory. (L2 cache setting is hardware specific, verify with your vendor for further clarifications).

                      Errata type: Content | Page number: 361

                      Replace numbered bullet 7 with the following:

                      7.       Place transaction logs on RAID1+0 for sequential workloads.

                      Errata type: Content | Page number: 405

                      Replace numbered bullet 7 with the following:

                      7.       With the help of Storage experts in your organisation or your hardware vendor check the Queue Depth parameter value settings. The drastic changes in Storage side offer benefits to cater resource intensive I/O. Depending upon HBA model, the default value of Queue Depth is 32.  For best practice information review http://blogs.msdn.com/b/joesack/archive/2009/01/28/sql-server-and-hba-queue-depth-mashup.aspx blog post.

                      Sample chapters

                      You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                      Frequently bought together

                      Microsoft SQL Server 2008 R2 Administration Cookbook +    Open Text Metastorm ProVision® 6.2 Strategy Implementation =
                      50% Off
                      the second eBook
                      Price for both: £42.80

                      Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                      What you will learn from this book

                      • Discover best practice solutions for upgrading to SQL Server 2008 R2
                      • Employ the new enhancements in SQL Server 2008 R2
                      • Build a strategic multi-server management
                      • Automate administration tasks
                      • Implement the inter-operability of SQL Server Analysis Services and SQL Server Reporting Services with SharePoint 2010 services
                      • Manage and administer core SQL Server 2008 R2 Business Intelligence Services
                      • Implement and manage essential Availability and Programmability enhancements
                      • Implement best practices and build troubleshooting techniques on a multi-instance and multi-database environment

                      In Detail

                      Microsoft SQL Server is a powerful relational database engine, widely used to store and maintain data in Enterprises of various levels – be they small, medium, or large. SQL Server delivers a rich set of integrated services and reporting features that enable you to do more with your data such as query, search, synchronize, report, and analyze. SQL Server 2008 R2 accelerates the capabilities to scale database operations and is a highly scalable data platform.

                      This practical cookbook will show you the advanced administration techniques for managing and administering a scalable and high-performance SQL Server 2008 R2 system. It contains over 85 practical, task-based, and immediately useable recipes covering a wide range of advanced administration techniques for administering a high-performance SQL Server 2008 R2 system and solving specific administration problems. It shows how the SQL Server 2008 R2 system can be taken further.

                      Packed with reusable, real-world recipes, the book starts by providing an overview of the server and specialized editions of SQL Server 2008 R2 technologies, features, and solutions. Next it covers database administration and management principles with real-world examples. The book then dives deep into topics such as administration of core Business Intelligence Services, management of core SQL Server 2008 R2 technologies, availability, security, and programmability, implementing new manageability features and practices, and maintenance and monitoring, amongst others.

                      <!--Session data-->

                       

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      Administer a scalable and high-performance SQL Server 2008 R2 system using advanced administration techniques

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      <!--Session data-->

                      Approach

                      This book offers practical, task-based, and immediately usable recipes covering a wide range of advanced techniques for administering a high-performance SQL Server 2008 R2 system. In addition to its cookbook style, which ensures the solutions are presented in a clear step-by-step manner, its explanations go into great detail, which makes it good learning material for everyone who has experience in SQL Server and wants to improve. The book is designed in such a way that you can either read it chapter by chapter or refer to recipes that you want in no particular order.

                      Although the book is focused on specific Microsoft SQL Server 2008 R2, most of the concepts and explanations are also applicable to SQL Server 2008.

                      <!--Session data-->

                       

                      <!--Session data-->

                      Who this book is for

                      If you are an experienced database administrator and database architect who wants to design, administer, and manage a scalable and high-performance SQL Server 2008 R2 system, then this book is for you. The book assumes that you have a good understanding of database management systems, specifically experience in If you are an experienced database administrator and database architect who wants to design, administer, and manage a scalable and high-performance SQL Server 2008 R2 system, then this book is for you. The book assumes that you have a good understanding of database management systems, specifically experience in Microsoft SQL Server 2008 administration.

                      <!--Session data-->

                       

                      <!--Session data-->

                      Code Download and Errata
                      Packt Anytime, Anywhere
                      Register Books
                      Print Upgrades
                      eBook Downloads
                      Video Support
                      Contact Us
                      Awards Voting Nominations Previous Winners
                      Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                      Resources
                      Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software