Microsoft SQL Server 2008: Management and Administration

Microsoft SQL Server 2008: Management and Administration

by Ross Mistry, Hilary Cotter

View All Available Formats & Editions

If you need to deploy, manage, or secure Microsoft SQL Server 2008, this is the complete, fast-paced, task-based reference you've been searching for. Authored by a world-class expert on SQL Server in the enterprise, this book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft's flagship database platform. SQL Server


If you need to deploy, manage, or secure Microsoft SQL Server 2008, this is the complete, fast-paced, task-based reference you've been searching for. Authored by a world-class expert on SQL Server in the enterprise, this book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft's flagship database platform. SQL Server MVP, Ross Mistry presents proven techniques for SQL Server 2008 installation, upgrades, backup/restore, data transfer, indexing, high availability, security, and much more. He draws on extensive testing in high-profile production environments to offer step-by-step solutions and powerful tips you won't find anywhere else. Every chapter begins with a section identifying SQL Server 2008's most significant new improvements, and concludes with a convenient summary of best practices. Each chapter also outlines the benefits of leveraging Windows Server 2008.

Product Details

Publication date:
Product dimensions:
5.40(w) x 8.20(h) x 1.90(d)

Read an Excerpt


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 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 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 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.

Main SiteToronto, Ontario
SQL Server NameTOR-SQL01\Instance01
Secondary SiteSan Francisco, California
SQL Server NameSFC-SQL01\Instance01
Disaster Recovery SiteNew York, New York NYC-SQL01\Instance01

© Copyright Pearson Education. All rights reserved.

Meet the Author

Ross Mistry, MVP, MCTS, MCDBA, MCSE Ross Mistry is a seasoned professional in the Silicon Valley and a technology advocate with more than a decade of experience in the computer industry. As a principal consultant and partner with Convergent Computing (CCO), Ross designs and implements SQL Server, Active Directory, and Exchange solutions for Fortune 500 organizations with a global presence. Some of the organizations in which Ross Mistry has taken on the role of lead global Microsoft architect include: Network Appliance, Ross Stores Dress for Less, CIBC, Gilead Sciences, Solectron, The Sharper Image, 2Wire, Infinera, and Wells Fargo’s small business Ecommerce site.

Ross had the opportunity to work with SQL Server 2008 two years prior to the product release. When he is not focused on his SQL Server specialties– high availability, migrations, and security–his attention turns to SQL Server development and Business Intelligence. Ross is an author, co-author, and technical editor of more than 10 books. Many of the books have been bestsellers. Recently, he coauthored Windows Server 2008 Unleashed and SQL Server 2005 Management and Administration. He was a contributing writer on Hyper-V Unleashed, Exchange Server 2007 Unleashed, and SharePoint Server 2007 Unleashed. Ross also took on the role of technical editor for SQL Server 2005 Unleashed and SQL Server 2005: Changing the Paradigm. In addition to being an author and consultant, Ross is a public speaker who conducts seminars on Microsoft topics around the world. He frequently speaks at local SQL Server user groups and international conferences. Most recently, he spoke at the SQL Server PASS Community Summit in North America, SQL Server Europe PASS, SQL Server Connections, and the

Microsoft campuses in San Francisco and the Silicon Valley. As a SQL Server Microsoft Most Valuable Professional (MVP), Ross is heavily involved with the SQL Server community and assists by responding to questions in newsgroups, writing articles for magazines, and blogging for networkworld. com and to IT managers on Microsoft’s TechNet Community Hub site. His blog site is

Ross' blog can be viewed at and he can reached at

Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >