Microsoft SQL Server 2008: Management and Administration

IntroductionIntroduction\ SQL Server 2008 is Microsoft’s latest data platform providing data management and analytical solutions for the enterprise. The platform is trusted, ensures business continuity, and is more predictable and more scalable than ever before. Although similar to SQL Server 2005, SQL Server 2008 offers a tremendous number of new features and functionality for database administrators, developers, and business intelligence architects.\ This book is designed to be the ultimate guide for database administrators as it provides detailed guidance in the areas of planning, installation, management, administration, security, high availability, monitoring, and performance tuning of a SQL Server environment. Moreover, this book includes industry best practices, tips, and step-by-step instructions based on real-world examples.\ Some of the classic and new SQL Server 2008 topics covered in the book include: installation, upgrade and migration strategies, Policy Based Administration, Resource Governor, encryption, failover clustering, database mirroring, authorization, hardening, consolidation and virtualization, maintenance plans, monitoring, performance tuning, troubleshooting, log shipping, PowerShell scripting, replication, creating packages and transferring data, indexes, full-text catalogs, and backing up and restoring databases.\ The book is also based on Microsoft’s latest award-winning server operating system—Windows Server 2008. As a result, not only will readers gain knowledge about SQL Server 2008, but they will also have the opportunity to understand the advantages of running SQL Server 2008 on Windows Server 2008.\ What Is in This Book?\ This book is organized into five parts, with each part made up of several chapters focusing on core SQL Server 2008 elements. The parts and chapters of the book are detailed in this section.\ Part I: Installing, Administering, and Managing the Database Engine\ The first part of the book begins by providing an overview of SQL Server 2008, including planning and installing the new platform. After you get SQL Server 2008 installed, the majority of your time will be spent managing and administering the new SQL Server infrastructure. Therefore, the remainder of Part I consists of chapters dedicated to SQL Server 2008 administration and management tasks.\ Chapter 1: Installing or Upgrading to the SQL Server 2008 Database Engine\ With the recent release of SQL Server 2008, organizations are eager to migrate to the new and improved database platform. However, many organizations feel challenged when trying to establish the best strategies for moving forward. This chapter focuses on the various SQL Server 2008 migration strategies that are available. It answers the question once and for all whether or not organizations should upgrade from a previous version or perform a new SQL Server 2008 installation from scratch and then conduct a migration.\ Other topics highlighted in this chapter include: supported migration methodologies, hardware requirements, using the SQL Server 2008 Upgrade Advisor, supported legacy versions of SQL Server, and best practices, tips, and common pitfalls to look out for to achieve a successful migration. Moreover, this chapter describes the benefits associated with running SQL Server on Windows Server 2008, and it also includes upgrade strategies for moving to the latest server operating system.\ Chapter 2: Administering the SQL Server 2008 Database Engine\ After SQL Server 2008 is installed, it is necessary to configure and administer the server. This chapter focuses on administering the core features and components associated with the Database Engine. Topics include administering the SQL Server properties pages, Database properties pages, Database Engine folders, and the SQL Server Agent. Managing server and database configuration settings—such as memory, processor performance, auditing, compression, database files, and autogrowth—is also covered in depth.\ Chapter 3: Creating Packages and Transferring Data with Integration Services\ A common database administrator task is transferring data or databases between source and target environments. This chapter focuses on importing, exporting, and transforming data and databases via SQL Server Management Studio and Integration Services. The chapter also covers how packages are created, saved, and executed as well as the management of the Integration Services component.\ Chapter 4: Managing and Optimizing SQL Server 2008 Indexes\ Similar to an index found in a book, an index in SQL Server is utilized for fast retrieval of data from tables. This chapter explains index concepts, ways to design the appropriate index strategy to maximize performance, creating indexes with SQL Server Management Studio, and how to create indexes with Transact-SQL. The chapter also introduces new SQL Server 2008 index topics, such as creating spatial and filtered indexes, and shares best practices on implementing, managing, and optimizing indexes.\ Chapter 5: Administering SQL Server 2008 Full-Text Search\ With the data explosion and the ever-increasing amount of data being stored in its native format, full-text search is playing an increasingly important role in databases today. This chapter discusses the new features in SQL Server full-text search and provides step-by-step instructions on how to implement full-text search on your tables, and best practices for full-text search.\ Chapter 6: SQL Server 2008 Maintenance Practices\ For SQL Server to perform at optimal levels, a DBA should conduct routine maintenance on each database. This chapter focuses on best practices associated with maintaining databases within the SQL Server Database Engine.\ The discussion includes creating maintenance plans to check database integrity, shrink databases, reorganize indexes, and update statistics. Additionally, this chapter provides recommendations on daily, weekly, monthly, and quarterly maintenance practices to be conducted on SQL Servers.\ Chapter 7: Backing Up and Restoring the SQL Server 2008 Database Engine\ Backing and restoring databases is one of the most critical duties of a DBA. It is important that the DBA understand the concepts associated with SQL Server backups, therefore, in the event of a disaster they can restore the database to the point of failure. This chapter covers the new backup compression feature, the importance of backups, creating a backup and recovery plan, storage architecture, transaction log files, recovery model, the various types of backups, backing up with SSMS, automating backups with a maintenance plan, backing up full-text catalogs, creating database snapshots, and of course, best practices.\ Part II: SQL Server 2008 Security Practices\ Part II of SQL Server 2008 Management and Administration is dedicated to SQL Server security. The first two chapters cover hardening techniques for both SQL Server 2008 and Windows Server 2008 and also discuss administering security and authorization. The next two chapters describe the new features, Policy Based Management, and encryption.\ Chapter 8: Hardening a SQL Server Implementation\ SQL Server is regularly targeted by hackers because it is a repository of sensitive data for organizations. If an organization’s system is breached, hackers can gain access to confidential information including, but not limited to, credit card numbers, social security numbers, and marketing information. As such, it is imperative that database administrators secure both the SQL Server implementation and the data residing in it. This chapter provides an overview of how to harden a SQL Server implementation based on industry best practices so that vulnerabilities and security breaches are minimized.\ Some of the security and hardening topics that the chapter delves into include: choosing the appropriate authentication mechanism, hardening the SA account, enforcing strong passwords, leveraging the configuration tools to lock down a SQL Server, configuring the Windows Server 2008 advanced firewall for secure access, selecting the correct service account, and applying security templates with Active Directory.\ Chapter 9: Administering SQL Server Security and Authorization\ After the SQL Server installation is hardened, the next step involves administering security and granting authorization to the SQL Server environment. Chapter 9 is all about security administration topics: creating logons, granting access and authorization, understanding SQL Server roles, administering password policies, endpoint authentication, SQL Server and database principals, role-based security, and user and schema separation.\ Chapter 10: Administering Policy Based Management\ Enforcing best practices and standardization on large SQL Server installations was extremely difficult in previous versions of SQL Server. To ensure standardization, SQL Server 2008 introduces Policy Based Management, which allows a DBA to define policies that can be applied to one or more SQL Server instances, databases, and objects. Policy Based Management works on SQL Server 2000 and all its successors.\ New concepts, components, terminology, and reporting with Policy Based Management as well as best practices are discussed in Chapter 10. The reader’s attention is then turned to real-world step-by-step examples of how to implement Policy Based Management in their environment.\ Chapter 11: Encrypting SQL Server Data and Communications\ Organizations and DBAs are facing excessive pressure from regulatory agencies to ensure that mission-critical data stored within SQL Server is encrypted. Not only is it important to ensure that data stored within SQL Server is encrypted, but it is equally important to ensure that data in transit is encrypted, database encryption can be implemented without making changes to an application, data can be encrypted at the cell level, and finally, SQL Server volumes can also be encrypted.\ Chapter 11 shares many strategies, tips, and best practices on how to leverage the encryption-based technologies included in SQL Server 2008 and Windows Server 2008 for end-to-end data protection. Among these strategies are: leveraging transparent data encryption, integrating security with a Hardware Security Module (HSM), using certificates to encrypt data in transit, and encrypting SQL Server volumes with BitLocker.\ Part III: SQL Server 2008 High-Availability Alternatives\ DBAs typically feel compelled to choose just the right technologies to achieve high availability when designing a SQL Server infrastructure. Some of the challenges they face are illustrated in their questions: Should I use failover clustering, database mirroring, log shipping, or replication? Which alternative provides the best protection? How does Windows Server 2008 impact my decision? This part of the book will alleviate pressure and concerns by providing DBAs with best practices and tips on how to design and choose the right SQL Server 2008 high-availability alternative to meet their organization’s needs.\ In this part, the chapters aim to teach DBAs how to select the appropriate HA technology when designing and implementing SQL Server 2008. Technologies include failover clustering, log shipping, peer-to-peer replication, and database mirroring, based on Windows Server 2008.\ Chapter 12: Implementing and Managing Failover Clustering\ The main focus of Chapter 12 is on how to design, configure, and manage a single-instance or multiple-instance high-availability failover cluster with SQL Server 2008.\ The chapter is also based on Windows Server 2008. As a result, it includes the new features and prerequisites associated with implementing failover clustering with SQL Server 2008 on Windows Server 2008. Step-by-step procedures for both the Windows Server 2008 failover cluster and SQL Server 2008 failover cluster are also provided.\ Chapter 13: Implementing and Managing Database Mirroring\ Chapter 13 deals with configuring and managing database mirroring so that organizations can enhance the availability of their SQL Server databases, increase business continuity, and maintain a hot standby of their database in another geographic location. The chapter includes detailed step-by-step instructions for configurations of all three database mirroring modes: high availability, high protection, and high performance.\ This chapter also includes best practices from the field, case studies, and discussions of how to integrate database mirroring with other high-availability alternatives such as failover clustering and how to recover from a failed server.\ Chapter 14: Implementing and Managing Log Shipping\ The focus of Chapter 14 is on configuring and managing log shipping. This chapter supplies step-by-step instructions on how to configure and maintain one or more warm standby databases, typically referred to as secondary databases. Like the other high-availability chapters in this part, this chapter includes real-world examples and industry best practices.\ Chapter 15: Implementing and Managing SQL Server Replication\ SQL Server Replication is another way of distributing data from a source SQL Server to either one or more target SQL Servers. This chapter focuses on replication components and provides a prelude to the different types of replication scenarios that a database administrator can manage, such as snapshot, merge, and transactional replication. Step-by-step replication configurations, including the peer-to-peer replication scenario, a new form of high availability, are also presented.\ Part IV: Monitoring and Troubleshooting SQL Server 2008\ Part IV of this book covers monitoring, troubleshooting, and performance-tuning techniques to ensure that SQL Server 2008 is optimized and performing at the highest possible levels.\ Chapter 16: Managing Workloads and Consumption with Resource Governor\ Unfortunately, with the previous releases of SQL Server, there was no way to effectively mitigate performance issues associated with SQL Server workloads. Thankfully, with SQL Server 2008, Microsoft introduced Resource Governor, which provides persistent performance to end users and applications by giving Database Administrators the potential to define resource limits and priorities on different SQL Server workloads. This chapter focuses on introducing, implementing, and configuring Resource Governor from a database administrator perspective.\ Chapter 17: Monitoring SQL Server 2008 with Native Tools\ SQL Server 2008 includes a tremendous number of native tools that should be leveraged in order to monitor a SQL Server database and infrastructure. This chapter first teaches a DBA how to use native tools to gain quick insight into a SQL Server system. It then focuses on how to leverage the new SQL Server audit functionality to monitor events and SQL Server activity and how to configure database mail. The final technologies described in this chapter are Performance Studio, and Performance and Reliability Monitoring, which are new monitoring technologies introduced with SQL Server 2008 and Windows Server 2008.\ Chapter 18: Proactively Monitoring SQL Server 2008 with System Center Operations Manager 2007\ Database Administrators and IT managers are under constant pressure to ensure that their SQL Server systems are operating at optimal levels. This can be very challenging when managing a large SQL Server infrastructure. It is recommended for DBAs to leverage Operations Manager 2007 to proactively monitor and provide a comprehensive view of the health of a SQL Server infrastructure.\ The discussion in this chapter focuses on the Operations Manager components, installing the SQL Server management pack, and on how to use the operations console to effectively monitor a SQL Server infrastructure. By gaining the upper hand in monitoring and managing a SQL Server infrastructure, Database Administrators can achieve the agility necessary to be successful and focus on other tasks.\ Chapter 19: Performance Tuning and Troubleshooting SQL Server 2008\ After SQL Server 2008 is placed in operation and is being monitored, it is important to take action on the findings. It is often difficult to anticipate real-world loads during the development phase of application deployment; thus, it is critical to adjust the parameters of the SQL Server 2008 platform to optimize the performance after it is deployed. Frequently, DBAs need to troubleshoot the performance of SQL Server 2008 to address problems that are uncovered by monitoring.\ This chapter focuses on how to tune, optimize, and troubleshoot the performance of the SQL Server 2008 system. Specific tools and components include Windows Server 2008 System Monitor, performance thresholds, SQL Server Profiler, Database Engine Tuning Advisor, Query Analysis, and Extended Events.\ Part V: SQL Server 2008 Bonus Content\ The final part of the book offers bonus content, including leveraging PowerShell to administer SQL Server and implementing Hyper-V to virtualize a SQL Server infrastructure.\ Chapter 20: Administering SQL Server 2008 with PowerShell\ SQL Server 2008 introduces support for Windows PowerShell. PowerShell is a new command-line shell and scripting language for automating server administration. Microsoft is beginning to integrate this new scripting language into all of their server products. This chapter discusses how to install PowerShell, provides an overview of the language and how it is integrated into SQL 2008, and then dives into how PowerShell can be leveraged to undertake common SQL Server tasks.\ Chapter 21: Consolidating and Virtualizing SQL Server 2008 with Hyper-V\ Tremendous efforts are being made in the IT industry and the world to sustain the environment. Going green by consolidating and virtualizing servers is a great way to simplify management, minimize data center costs, reduce power consumption, and make the world a better place for future generations. By consolidating SQL Server instances and leveraging Windows Server 2008 Hyper-V for consolidating and virtualizing SQL Server databases and instances, organizations can reduce the number of SQL Server systems within the infrastructure. This chapter focuses on how to plan and implement Hyper-V and create virtualized guest sessions so that SQL Server systems can be consolidated and virtualized. In addition, SQL Server virtualization considerations are also discussed.\ Sample Databases\ To facilitate running the steps introduced throughout the book, all of the examples are based on either the AdventureWorks2008 or Customer database. Both of these database samples can be downloaded directly from Microsoft’s SQL Server sample website at http://www.codeplex.com/SqlServerSamples. The website and downloads also include the step-by-step instructions on how to install the sample databases on SQL Server 2008.\ The exact link to the OLTP Adventureworks2008 sample can be found at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407. Be sure to choose the correct MSI file based on the 2008 schema and the processor type of your system.\ The Customer database can be downloaded from the sample Integration Services Product Samples website at http://www.codeplex.com/MSFTISProdSamples. The package sample name is “Execute SQL Statements in a Loop Sample Package” and the customer data which needs to be imported into a newly created Customer database is located in Customer.txt file.\ In addition, the book is based on SQL Server 2008 Enterprise Edition running on Windows Server 2008 Enterprise Edition. The following elements were used for the scenarios of the book.\ \ \ Element\ Description\ \ \ Domain\ Companyabc.com\ \ \ Domain Controller\ TOR-DC01.companyabc.com\ \ \ Main Site\ Toronto, Ontario\ \ \ SQL Server Name\ TOR-SQL01\\Instance01\ TOR-SQL01\\Instance02\ \ \ Secondary Site\ San Francisco, California\ \ \ SQL Server Name\ SFC-SQL01\\Instance01\ SFC-SQL02\\Instance02\ \ \ Disaster Recovery Site\ New York, New York NYC-SQL01\\Instance01\ NYC-SQL02\\Instance02\ \ \ © Copyright Pearson Education. All rights reserved.

Introduction 1 Installing or Upgrading to the SQL Server 2008 2 Administering the SQL Server 2008 Database Engine 3 Creating Packages and Transferring Data with 4 Managing and Optimizing SQL Server 2008 Indexes 5 Administering SQL Server 2008 Full-Text Search 6 SQL Server 2008 Maintenance Practices 7 Backing Up and Restoring the SQL Server 2008 8 Hardening a SQL Server Implementation 9 Administering SQL Server Security and Authorization 10 Administering Policy Based Management 11 Encrypting SQL Server Data and Communications 12 Implementing and Managing Failover Clustering 13 Administering and Managing Database Mirroring 14 Implementing and Managing Log Shipping 15 Implementing and Administering SQL Server Replication 16 Managing Workloads and Consumption with 17 Monitoring SQL Server 2008 with Native Tools 18 Proactively Monitoring SQL Server 2008 with System 19 Performance Tuning and Troubleshooting SQL Server 2008 20 Administering SQL Server 2008 with PowerShell 21 Consolidating and Virtualizing SQL Server 2008 with Hyper-V Summary Best Practices Index