Chapter 1: SQL Server 2005 Architecture

The days of SQL Server being a departmental database are long gone, and SQL Server can now easily scale to databases dozens of terabytes in size. In this chapter, we lay some of the groundwork that will be used throughout the book. We first discuss how the role of the 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 provides enough information to give you an understanding of how SQL Server operates.

Growing Role of a DBA

The role of the database administrator (DBA) has been changing slowly over the past few versions of the SQL Server product. In 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 2005. 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 the server is performing optimally and promotes database changes from development to QA to production.

Since 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, where you need a separation of power between the person developing the change and the person implementing the change. Other tasks that a Production DBA does are:

• Install SQL Server instances and service packs

• Monitor performance problems

• Install scripts from development

• Create baselines of performance metrics

• Configure the SQL Server optimally

• Create disaster recovery and scalability plans

• Ensure that backups have been run

In a large organization, a Production DBA may fall into the operations department, which would consist of the network 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 migrate the database from one release to the next. The Development DBA typically does not receive calls at two in the morning, unless the Production DBA needs to escalate. Other Development DBA roles may be:

• Model an application database

• Create stored procedures

• Develop the change scripts that go to the Production DBA

• Performance-tune queries and stored procedures

• Possibly create any data migration

• 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 grown due to the increased surface area of SQL Server. In SQL Server 2005, BI has grown to be an incredibly important feature set that many businesses cannot live without. The BI DBA is an expert at these features. He or she is the one who creates your SSIS packages to perform Extract Transform and Load (ETL) processes or reports for users. In many organizations, the role is so large that the BI DBA functions may be broken into smaller subsets, and you may have specialized DBAs to perform tasks such as SSIS or reports. In the world of SQL Server, a BI DBA is responsible for the following types of functions:

• Develop data-migration packages

• Model Analysis Services cubes and solutions

• Work with the analyst to develop KPI measures for Business Scorecard Manager

• Create reports using Reporting Services

• Develop a Notification Services solution

• Create 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 with Analysis Services experts, you may see them 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 we 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 their investment over many Hybrid DBAs instead of specialized roles.

Organizationally, you may see these 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 and performs every DBA function for that product. Such DBAs should also have adequate backup personnel to reduce the organization's risk if the 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.

Industry Trends

We'll get into the SQL Server 2005 features momentarily, but you'll notice a trend as you begin to see the list. Feature after feature will require that a DBA become acclimated to a .NET programming language such as C# or VB.NET to remain effective. For example, if you are a DBA trying to debug a performance problem with a CLR stored procedure, you're going to need to know the language the stored procedure is written in to understand the performance problem. Features like Integration Services and Reporting Services are very much tied to expressions, which are variants of VB.NET.

Each new release of SQL Server since 7.0 has required DBAs to know more things that were traditional concerns of developers only, such as XML. With SQL Server 2005, though, there is a leap forward in the knowledge a DBA must have to be effective. Essentially, if you don't know a .NET programming language, you may be stuck as a Production DBA indefinitely. There are still roles for Production DBAs, but even in such roles, you may be less effective.