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 http://www.networkworld.com/community/mistry.
Ross' blog can be viewed at http://www.networkworld.com/community/mistry and he can reached at Ross@cco.com.
Introduction Introduction
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 1...
