Professional Microsoft SQL Server 2008 Administration

Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort

• Publisher: Wrox
• Year: 2011
• ISBN:
  9780470440377
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

SQL Server 2008 is how-to guide for experienced DBAs. Tutorial-based, this book will get you over the learning curve of how to configure and administer SQL Server 2008. Whether you're an administrator or developer using SQL Server, you can't avoid wearing a DBA hat at some point. The book is loaded with unique tips and workarounds for the most difficult SQL Server admin issues, including managing and monitoring SQL Server, automating administration, security, performance tuning, scaling and replications, clustering, and backup and recovery. A companion website is also available.

Chapter 1

SQL Server 2008 Architecture

The days of SQL Server being merely a departmental database are long gone. SQL Server can now easily scale to databases dozens of terabytes in size. (For details see the results of the Winter survey at www.microsoft.com/sql/prodinfo/compare/wintercorp-survey.mspx.) In this chapter, we lay some of the groundwork that will be used throughout the book. We first discuss how the role of the database administrator (DBA) has changed since some of the earlier releases of SQL Server, and then quickly jump into architecture and tools available to you as an administrator. This chapter is not a deep dive into the architecture but it provides enough information to give you an understanding of how SQL Server operates.

The Expanding Role of a DBA

The role of the database administrator has been changing slowly over the past few versions of the SQL Server product. Beginning with SQL Server 2005, this slow transition of the DBA role has been accelerated immensely. Traditionally, a DBA would fit into one of two roles: development or administration. It's much tougher to draw a line now between DBA roles in SQL Server 2008. In addition, the new role of Business Intelligence DBA is on the rise. As lines blur and morph, DBAs have to quickly prepare themselves to take on different roles. If you don't position yourself to be more versatile, you may be destined for a career of watching SQL Server alerts and backups.

Production DBA

Production DBAs fall into the traditional role of a DBA. They are a company's insurance policy that the production database won't go down. If the database does go down, the company cashes in its insurance policy in exchange for a recovered database. The Production DBA also ensures that the server is performing optimally, and he or she promotes database changes from development to quality assurance (QA) to production. Other tasks performed by a Production DBA include the following:

• Install SQL Server instances and service packs.
• Monitor performance problems.
• Install scripts from development.
• Create baselines of performance metrics.
• Configure the SQL Server optimally.
• Configure/implement high availability plans.
• Create\implement disaster recovery and scalability plans.
• Ensure that backups have been run.

Since the release of SQL Server 2000, there has been a trend away from full-time Production DBAs, and the role has merged with that of the Development DBA. The trend may have slowed, though, with laws such as Sarbanes-Oxley, which require a separation of power between the person developing the change and the person implementing the change. In a large organization, a Production DBA may fall into the operations department, which would consist of the network administrators and Windows-support administrators. Placing a Production DBA in a development group removes the separation of power that may be needed for some regulatory reasons. It may create an environment where “rush” changes are immediately put into production, without proper inspection and auditing.

Development DBA

Development DBAs also play a very traditional role in an organization. They wear more of a developer's hat and are the development staff's database experts and representatives. This administrator ensures that all stored procedures are optimally written and that the database is modeled correctly, both physically and logically. He or she also may be the person who writes the migration processes to upgrade the database from one release to the next. The Development DBA typically does not receive calls at 2:00 A.M. Other Development DBA tasks may be as follows:

• Model an application database.
• Create stored procedures.
• Develop the change scripts that go to the Production DBA.
• Performance-tune queries and stored procedures.
• Create data migration plans and scripts.
• Serve as an escalation point for the Production DBA.

The Development DBA typically would report to the development group. He or she would receive requests from a business analyst or another developer. In a traditional sense, Development DBAs should never have modification access to a production database. They should, however, have read-only access to the production database to debug in a time of escalation.

Business Intelligence DBA

The Business Intelligence (BI) DBA is a new role that has evolved due to the increased capabilities of SQL Server. In SQL Server 2005, BI grew to be an incredibly important feature set that many businesses could not live without. The BI DBA is an expert at these features.

BI DBAs may have specializations, just like normal SQL DBAs. A Production BI DBA will perform the same functions as the Production DBA: installs, service packs, deployments, high availability, performance tuning, and backups. The only difference is that the Production BI DBA will be paying closer attention to SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and perhaps Proclarity, Business Scorecard Manager, and Performance Point Servers.

Development BI DBAs specialize in the best practices, optimization, and use of the BI toolset. In a small organization, he or she may create your SSIS packages to perform Extract Transform and Load (ETL) processes or reports for users. In a large organization, developers create the SSIS packages and SSRS reports. The Development BI DBA is consulted regarding the physical implementation of the SSIS packages, and Analysis Services (SSAS) cubes. Development BI DBAs may be responsible for the following types of functions:

• Model\consult regardingAnalysis Services cubes and solutions.
• Create reports using Reporting Services.
• Create\consult around ETL using Integration Services.
• Develop deployment packages that will be sent to the Production DBA.

Organizationally, the BI DBA most often reports to the development group. In some cases, Analysis Services experts may report to the analyst group or the project management office. In some small organizations, the BI DBA may report directly to an executive such as a CFO.

Hybrid DBA

The most exciting role for a DBA is a hybrid of all the roles just mentioned. This Hybrid DBA is very typical with smaller organizations but is becoming popular with larger organizations as well. An organization with high turnover may want to spread its investment over many Hybrid DBAs instead of relying on specialized roles.

Organizationally, you may see Hybrid DBAs reporting directly to the product organization or to a specialized DBA group. No matter where these DBAs report, each typically has a slate of products that he or she supports, performing every DBA function for that product. Organizations that rely on Hybrid DBAs should have adequate backup personnel to reduce the organization's risk if a Hybrid DBA leaves the company. Also, this DBA should never install his or her own changes into production. Ideally, for regulatory reasons and for stability, the DBA's backup DBA should install the change into production. That way, you can ensure that the DBA who installed the script didn't make ad hoc changes in order to make the change work. We cover much more about this change-management process in Chapter 10.

The only role of a Hybrid DBA that's questionable is development of stored procedures. In most organizations where we see this role, the Hybrid DBA does not develop stored procedures. Instead, he or she creates difficult stored procedures or tunes the ones causing issues. The developer working on the application develops his or her own stored procedures and then provides them to the Hybrid DBA to package and proof. The main reason for this is that the DBA is too taxed for time, working on other functions of the database.

New Things You Need...

Table of contents

• Cover
• Title Page
• Copyright
• About the Authors
• Credits
• Acknowledgments
• Introduction
• Chapter 1: SQL Server 2008 Architecture
• Chapter 2: SQL Server 2008 Installation Best Practices
• Chapter 3: Upgrading SQL Server 2008 Best Practices
• Chapter 4: Managing and Troubleshooting the Database Engine
• Chapter 5: Automating SQL Server
• Chapter 6: Integration Services Administration and Performance Tuning
• Chapter 7: Analysis Services Administration and Performance Tuning
• Chapter 8: Administering the Development Features
• Chapter 9: Securing the Database Engine
• Chapter 10: Change Management
• Chapter 11: Configuring the Server for Optimal Performance
• Chapter 12: Optimizing SQL Server 2008
• Chapter 13: Monitoring Your SQL Server
• Chapter 14: Performance Tuning T-SQL
• Chapter 15: Indexing Your Database
• Chapter 16: Replication
• Chapter 17: Database Mirroring
• Chapter 18: Backup and Recovery
• Chapter 19: SQL Server 2008 Log Shipping
• Chapter 20: Clustering SQL Server 2008
• Index
• Advertisement