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 70 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, among others.
Chapter 1, Getting Started with SQL Server 2008 R2, begins with SQL Server 2008 R2 version's new features and enhancements such as master data services, data-tier applications, and adding the service pack features using Slipstream technology. We will run through the designing best practice approach in upgrading to SQL Server 2008 R2 and take advantage of federated servers enhancements.
Chapter 2, Administrating the Core Database Engine, covers the new SQL Server 2008 R2 feature utility administration and enhancements within DBA tasks such as availability, security, performance monitoring, tuning, and automated administration practices. The recipes also emphasize the usage of SQL Server Agent scheduled jobs for ETL and replication processes.
Chapter 3, Managing the Core Database Engine, enables the user to produce a resilient data platform, which is possible with new features of SQL Server 2008 R2 such as Utility Control point, multi-server management, and implementing central management feature enhancements. The recipes cover the key best practices that combine a streamline of services for deployment, migration, and management of data platform.
Chapter 4, Administering core Business Intelligence Services, emphasizes on the process and procedures in preparing, installing, and implementing scale-out deployment of core business intelligence services—Analysis Services (AS), Reporting Services (RS), and Integration Services (IS), and finally key practices in managing the ETL process efficiently.
Chapter 5, Managing Core SQL Server 2008 R2 Technologies, dives into the implementation phases of SQL Server 2008 R2 new features and essential steps in planning and implementing self-service BI services, SQLAzure connectivity, StreamInsight technologies, and deploying the master data services feature. These recipes involve the planning, design, and implementation of features that are important to the management of the core technologies.
Chapter 6, Availability and Programmability Enhancements, is the biggest among all the chapters, and it covers the key SQL Server 2008 R2 availability enhancements within failover clustering, database mirroring, log shipping, and replication. The recipes also highlight the programmability enhancements such as compressed storage feature, data partitioning solutions, sparse columns, spatial data storage methods, and management of data-tier applications.
Chapter 7, Implementing New Manageability Features and Practices, spotlights the new manageability features and practices such as auditing, security, compression, change tracking, policy-based management, transparent data encryption, implementing best practices analyzer, and PowerShell for SQL Server features.
Chapter 8, Maintenance and Monitoring, is based on the typical day-to-day tasks of a DBA. It will go through the the aspects of maintenance and monitoring the best practices with real world examples. The recipes will help the DBAs to adopt best methods with available technologies to keep up the database functionality at peak performance.
Chapter 9, Troubleshooting, covers the brainier task of administration which helps the users to solve the problems proactively, and this chapter spotlights the essential chores of troubleshooting. The recipes begin with monitoring methods and various available features such as filtered indexes, table hints, and query hints, and perform parallel query processing methods by taking advantage of hardware and system resources.
Chapter 10, Learning the tricks of the trade, emphasizes on the important aspects of configuration, administration, and management of the core data platform.
Appendix, More DBA Manageability best practices, contains a list of DBA Best Practices that are important to handle day-to-day tasks of the SQL Server 2008 R2 environment.
The different editions of SQL Server 2008 R2 adapt the unique performance, runtime, and price requirements of organizations and individuals. The SQL Server 2008 R2 data platform meets the need of handling structured data (transactional consistency) and offers relational capabilities to unstructured data by providing a solution that can store and manage many types of data, including XML, e-mail, calendar, media files, documents, geospatial, and many more, which provides a rich set of services to interact with the data and built-in capabilities of search, query, data analysis, processing, reporting, and integration from different data sources. The data accessibility feature is unlimited, whereby it accesses information on creation to archiving on any device, from a global data-center server to desktop or mobile devices.
A new version of SQL Server is related as "Release To Manufacturing" (RTM) version [(Microsoft SQL Server 2008 R2 (RTM)—10.50.1600] that comprises various enhancements as an end-to-end solution area for data platform. The key to the success of any database platform is dependent upon cost-effective and scalable database solutions. SQL Server 2008 R2 helps achieve these goals to deliver an enterprise class data platform to improve efficiency through better resource utilization, and build confidence in end users to scale out the application solutions.
To commence a scalable and high performance database platform design, identifying which SQL Server features are needed is a key aspect of design decision. The SQL Server installation wizard provides a feature tree for installation of various SQL Server components that includes management tools and connectivity components. The different services for SQL Server are independent of each other and run as dedicated services on a system. It is important to know the different services that are running on the core operating system:
Database Engine: SQL Server and SQL Server Agent are core database engine components that run as services.
SQL Server service: The Server service is useful for data management. It finds services to store and manipulate data based on the application requirement with a variety of backup and restore solutions. The database engine contains advanced security capabilities to lockdown, protect, and audit the data, in parallel with services to ensure high availability is achieved. For high performance and scalability arrangements, the SQL Server service is accountable to host a relational model service, which facilitates any changes to the data to keep up the transactional consistency to ensure that database will revert to a known consistent state in the event of a server or database failure.
SQL Server Agent: To automate essential administrative tasks, SQL Server Agent is essential. This service manages the information about scheduled jobs, monitors SQL procedures (performance objects), and processes alerts.
SQL Server Browser: Browser service works as a listener service to peruse a list of available servers and connects to a correct server instance. SQL Server Browser uses port 1434 to cater to a client request to SQL Server by sending a User Datagram Protocol (UDP) message; the task replies with TCP/IP or named-pipes protocol of installed instances. This is a key service to be used in emergency situations such as connecting to a SQL Server instance using Dedicated Administrator Connection (DAC) endpoints.
SQL Server Integration Services: Extract, Transform, and Load (ETL) activities are highly manageable and programmable using SQL Server Integration Services (SSIS). This service is capable of performing a variety of tasks to enable packages to import or export data from various file sources, manipulate files in operating system folders, or perform complex calculations inside database tables. A set of specialized tasks such as copy SQL Server objects or perform database backups or database maintenance operations can be designed as out of the box solutions using Visual Studio tools for Applications or the Business Intelligence Development Studio tool.
SQL Server Reporting Services: To design and manage a flexible platform for reports and data-distribution, SQL Server Reporting Services (SSRS) and tools are available. It creates a standardized methodology and builds complex reports so that the end user can obtain reports rapidly without having any knowledge of programming language or trying to understand the underlying complexity of the RDBMS platform. The SSRS is capable of configuring a report subscription to set up a schedule to execute the report and send it (by e-mail) to the preferred user distribution channel with their prescribed format specifications. The rendering capability to Portable Document Format (PDF) or Excel (XLS) format or Word format (.DOCX) is a highly advantageous feature for a DBA as it takes advantage of two main components—a report server and report designer.
SQL Server Analysis Services: Decision Support Systems can manage high volumes of data. Blending Business Intelligence features to the system can help us to manage data warehousing issues. The SQL Server Analysis Services (SSAS) is best used to cater the data needs of business users and the ability for IT to manage data efficiently. SQL Server 2008 R2 is built upon highly scalable systems to provide Online Analytical Processing (OLAP) and Data Mining capabilities. The data mart feature with SSAS extends the business analysis to allow the users to find patterns and predictions for their business values by utilizing several built-in mining algorithms.
Full-Text Search Service: Full-Text Search (FTS) consists of full-text indexing and a search engine, which is integrated into the core database engine. FTS is essential to perform a variety of tasks such as gathering data and sending it to the filter-daemon host for processing to 'tokenize' by a full-text gatherer. Once this is completed, SQL Server receives results to index them as an inverted index structure for optimized performance during a search function.
Built on core SQL Server 2008 technologies, the SQL Server 2008 R2 delivers these enhancements by advancing the efficiency to end users on the areas of high availability, enterprise security, enterprise manageability, data warehousing, reporting, scalability, and performance.
SQL Server 2008 R2 meets the large-scale requirements with first-hand enhancements such as two new premium editions and comprehensive additions to existing server editions. The new additions to server and specialized editions of SQL Server 2008 R2 can accommodate the requirement from the lowest level to the highest level of Enterprise needs by offering manageability and straightforward monitoring capabilities.
The installation requirements vary based on the application requirements, and the different editions of SQL Server 2008 R2 which support different features independent to other editions. They offer rich functionality to support mission-critical transactional (OLTP) and Business Intelligence (OLAP) analytical workloads that can be synchronized to access from any type of device. The newest service provision in SQL Server 2008 R2 is SQL Azure, which is a cloud-based relational database service built on SQL Server technologies.
To develop a data management solution to offer performance, scalability, and availability, the selection of SQL Server edition is essential. Though SQL Server 2008 R2 is classified as a marginal change to SQL Server 2008 version, the new editions meet the needs of large-scale data centers and data warehouse deployment. The existing core server edition is coupled with robust and enhanced offerings to enable the highest service levels for mission-critical data loads and provide optimized results. SQL Server editions are classified into three categories: Other, Core, and Premium editions.
1. The Other category of editions is as follows:
SQL Server 2008 R2 Express and Express with Advanced Services (free and lightweight to use)
SQL Server 2008 R2 Compact (standalone and mobile devices)
SQL Server 2008 R2 Workgroup (to host branch applications and provide remote synchronization capability)
SQL Server 2008 R2 Web (secure and scalable platform for websites)
SQL Azure (cloud-based offering with relational data model)
SQL Server 2008 R2 Developer (equivalent to Enterprise edition for builds and test purpose only)
2. The Core category of editions is as follows:
SQL Server 2008 R2 Standard (reliable data management and BI offering for small and medium-sized organizations)
SQL Server 2008 R2 Enterprise (comprehensive data management solution with end-to-end BI solutions for large organizations)
3. The 'new' Premium category of editions is as follows:
SQL Server 2008 R2 Datacenter (highest levels of scalability for mission-critical applications in large-scale data centers)
SQL Server 2008 R2 Parallel Data Warehouse (accelerate data processing by using Massive Parallel Processing (MPP) technology)
The DataCenter edition is designed to distribute the high-performance data platform for mission-critical application workloads, and leverage the maximum OS memory limits and physical server resources to cater to maximum virtualization platform support that is essential for database infrastructure consolidation with maximum ROI.
The key effectiveness of this edition is Application and Multi-Server Management (Utility Control Point) for managing multiple instances (up to 25) of SQL Server services. The DataCenter edition offers key levels of scalability with a support of more than eight processors and up to 256 logical processors and memory support limits up to Operating System maximum.
The ROI on consolidation and virtualization is high with a support on memory limits up to the Operating System maximum. The latest supplement for this edition is StreamInsight, which enables you to process a large volume of events across multiple data-streams with low latency and an advantage of mining the KPIs from the historical data for business effectiveness.
The SQL Server 2008 R2 Parallel Data Warehouse (PDW) is built upon a data warehouse technology that takes advantage to improve the reliability and performance of the instance that is highly scalable and built on a Massive Parallel Processing (MPP) and Symmetric Multi-processing (SMP) architecture to enable better administration, scalability of predictable performance, and reduced risk and lower cost per terabyte storage support with major hardware partner compatibility.
PDW provides the scalability and query performance by running independent servers in parallel, which means adding a CPU or memory capacity to the server is a straightforward process. SMP offers the ease of administration and MPP offers linear improvement in processing power, which is the scalability. To achieve scalable query performance, the appliance distributes data across multiple servers and SQL Server instances, and then uses its massive parallel processing (MPP) engine to execute queries in parallel.
The advanced data warehouse capability and standards of Star Join Queries, Change Data Capture, and integration with other Business Intelligence services such as SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS) are additional benefits. Appliance hardware is a multi-rack system with one control rack that controls appliance operations, and one or more data racks that store user data on multiple compute nodes. When designing your own appliance, you can scale out query performance and expand storage capacity by adding data racks.
The Enterprise Edition is classified as a comprehensive data platform to meet the high demands of online transactional processing and data warehouse capabilities for all kinds of Enterprise-wide solutions.
There are new additions to these two core editions, which revolutionize the comprehensive solutions within the areas of availability, business intelligence, and scalability having built-in security features.
Application and Multi-server management: This feature from the DataCenter edition is a new edition to the Enterprise Edition, which provides a drill-down capability of resources utilization of applications and instances. It provides the user with a central control to set up and enroll instances for policy evaluation and trend analysis of applications and instances to streamline the consolidation efforts management, which supports up to 25 instances.
The Business Intelligence capability within Enterprise Edition includes the following key add-ins:
PowerPivot add-in for Excel provides users to create effective BI solutions by supporting the seamless sharing of data models and reports through Microsoft Office SharePoint 2010 server.
SQL Server Reporting Services Reporting Builder 3.0 is an ad hoc reporting client to accelerate the consistent process from report creation to collaboration to enable the rapid assembly of common business reports using the shared components, for a better scale-out deployment.
SharePoint 2010 Operations Dashboard enables the users to monitor access and utilization of server resource usage, manage the right security privileges, and the ability to develop user-generated solutions.
PowerPivot for SharePoint contributes to the ability to analyze the large volumes of data with a seamless integration with SharePoint Server 2010 to monitor and manage effectively.
Master Data Services: It is a representation of a single version of data across the Enterprise. The master data hub facilitates the centralized approach to define, deploy, and manage master data efficiently in a consistent manner to enable a solution to create, manage, and circulate changes from a single-master view of multiple business entities.
Unified Dimension Model: It is used to integrate and consolidate data from heterogeneous datasources such as SQL Server, DB2, SAP, Oracle, and Teradata.
Scale-up and Scale-out: It features with an advantage of 64-bit technologies, which enables greater scalability with support for non-uniform memory access (NUMA) hardware without any application configuration changes. Hot-Add memory and CPU capability of Windows Server 2008 to scale up without having to stop SQL Server services.
Improvements to Reporting Services with easier authoring and ad hoc reporting support for more data types, better control, and management of reports.
StreamInsight: It processes large volumes with less latency; reduces the development and management costs to the benefit of near real-time event processing as a large-scale data platform. This feature is available for 64-bit and 32-bit platforms as a server and client packages with two different methods of installations—attended and un-attended.
Live Migration: Windows Server 2008 R2 Hyper-V provides greater flexibility with Live Migration. Live Migration is integrated with Windows Server 2008 R2 Hyper-V and Microsoft Hyper-V Server 2008 R2. If the physical host that the VM is migrated to, is already clustered, then the VM will be automatically configured to be highly available and ready for Live Migration. See http://download.microsoft.com/download/4/4/D/44DB08F7-144B-4DF6-860F-06D30C6CE6E4/SQL%20Server%202008%20R2%20Virtualization%20Whitepaper.docx for more details.
The new capabilities of the Standard Edition are as follows:
Backup compression from Enterprise edition is now supported on every SQL Server 2008 R2 edition, with a limitation such as compressed and uncompressed backups cannot co-exist in a media set and previous versions cannot read the compressed backups.
The Standard Edition can be a managed instance for application and multi-server management capabilities.
Operating System:
Windows Server 2008 R2
Windows 7 or Windows Vista
Software: Install the following tools and services:
SQL Server (SQL Server 2008)
SQL Server (SQL Server 2008 R2)
SQL Server 2008 R2 Management Studio
SQL Server 2008 R2 Reporting Services
SQL Server 2008 R2 Business Intelligence Development Studio
SQL Server Configuration Manager
SharePoint 2010 Services
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, and specific experience in Microsoft SQL Server 2008 administration.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "We can include other contexts through the use of the include directive."
A block of code is set as follows:
create table test (
x int not null,
y char(896) not null default (''),Any command-line input or output is written as follows:
DISKPART> select volume=<drive-number>New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "clicking the Next button moves you to the next screen".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.