你属于哪一类型的DBA(转)

此文译自国外技术论坛 http://www.databasejournal.com ，对SQL Server DBA的工作做了很详尽的介绍，对从事DBA相关工作的人有比较好的参考作用。

原文地址：http://www.databasejournal.com/features/mssql/article.php/3767451/What-Kind-of-DBA-Are-You.htm

What Kind of DBA Are You? 你属于哪一类型的DBA

By Gregory A. Larsen

 

There are many types of DBAs. Some DBAs work in the developer arena, while others are heavy into performance and tuning, and still other DBAs fall into the operational aspects of managing SQL Server. There are a number of different tasks a DBA might perform. depending on the environment in which they work. To help identify all the different kinds of tasks a DBA might perform. I have compiled a list.

My list covers many different types of tasks. I have categorized these tasks into 7 different areas: Configuration, Database Standards, Database Design, Development, Maintenance, Monitoring, and Planning. For each task, I’ve listed a short description of the duties associated with that task. In some environments, a single DBA might perform. each of these tasks but in other environments, these tasks might be shared by many different individuals. If you are contemplating being a DBA, or are currently a DBA then these are the kinds of tasks you might expect or should be performing.

有很多种类的DBA，有的DBA从事的是开发人员的工作，有的DBA则侧重于性能调优，还有的DBA从事SQL Server的运行与管理。同样有很多种DBA可能会涉及的任务，具体的任务取决于DBA工作的环境。为了帮助辨别所有DBA可能涉及到任务，我编制了以下清单。

这个清单包括所有类型的任务。这些任务可以归类到7个不同的方面：配置，标准化，设计，开发，维护，监控和计划。对每一项任务，我列出了一个关于该任务职责的简短描述。在某些环境，可能需要DBA来完成所有涉及到的任务；在另外一些环境，这些任务可能会被不同的人来承担。如果你立志当一名DBA，或者现在正从事于DBA的工作，清单里面的任务正是你所要关注的。

 

Configurations Tasks:

SQL Server Machine Configuration

DBAs should configure the physical machine for each new SQL Server installation. The DBAs will work with application staff to identify fault tolerance and performance requirements. These requirements will be used to develop the physical setup requirements. In some environments, the DBA will perform. the actual installation of hardware and installing the operation system. In other environments, they might just provide information to the systems administration staff in how to configure the hardware associated with a SQL Server machine.

Installing SQL Server Instances

The DBA will be responsible for installing SQL Server software and additional instances on a SQL server machine. DBAs should consider how each installation should be setup. When you have multiple instances and machines, you should consider building a scriptable installation so all installations are consistence across all instances. This consistency will help minimize the maintenance aspects of managing all your instances.

配置工作：

SQL Server 机器配置

DBA应该为每一个新的SQL Server安装配置物理机器。他们将和应用开发人员共同鉴定机器容错能力和性能需求，然后据此形成物理机器环境的搭建需求。在某些环境，DBA需要进行实际的硬件安装和操作系统的安装。而有的环境DBA只需提供配置SQL Server机器的信息给系统管理人员。

安装SQL Server实例

DBA将负责安装SQL Server软件和SQL Server实例。他们将会考虑如何来装这些实例。当你有很多实例和机器，你应该考虑创建一个脚本的安装使所有安装的实例保持一致。保持一致性将有助于最小化维护和管理实例的工作。

 

Tasks Related Database Standards:

Develop Databases Standards

In order to maintain consistency across an organization the DBA should develop database standards. The DBA is responsible for developing and communicating these standards across the organization. Standards should not be developed in a vacuum, so the DBA should facilitate database standard discussions with application developers in an organization. Standards are an evolving process, once developed they need to be maintained. The DBA should ensure that as new releases of application technology and database versions come out that the standards are reviewed and modified appropriately to meet the needs of the ever changing environment of information technology.

Review Database Designs for Standard Compliance

Once standards are in place a DBA needs to make sure each new database, or enhanced database is reviewed to make sure it meets the database standards. If databases don’t meet the standards then the DBA should play the traffic cop role, and make sure a database either meets the standards, or some form. of exception process is performed to document why a particular database does not meet the standards.

与数据库规范化相关的任务

开发数据库规范

为了保持整个机构的一致性，DBA应该制定数据库规范。DBA负责开发这些规范并在整个机构中交流。规范不应该是一句空头话，DBA需要推动在机构里与应用开发人员讨论数据库规范。规范化是一个进化的过程，一旦开发出来，就应该保持下来。随着应用开发技术和数据库版本的升级，为了满足正在变化的环境和信息技术需求，DBA应该确保评审和修订这些规范。

为规范化评审数据库设计

一旦规范开始执行，DBA需要确保每一个新的数据库或者是更新的数据库被评审以遵守数据库规范。如果数据库不符合规范，DBA需要扮演交警的角色，来确保数据库符合这些规范，或者构建一个例程来记录不符合规范的数据库的原因。

 

Database Design Tasks:

Data Model Review

Data models are typically one of the first steps in designing a new database. The DBA should review these models. This review process will acquaint the DBA with the data that will be contained in the database. This review process stimulates discussion about how the data in the database will be processed and loaded. This information will be helpful with architectural decisions on how the data will be stored, read, shared and managed. This review will help ensure data is appropriately integrated into the enterprise database environment.

Physical Database Design

In some environments DBA’s will also be called upon to perform. actual databases design. In other environment applications, programmers/contractors will propose the design of a new database. In either case, the person developing the database will need to meet with the business analysts to help define business data and processing requirements. From this discussion, DBAs will develop or assist in the development of a physical database design. The database design needs to meet the data requirements for inserting, update, deleting, and exporting data.

Database Security Design

This task looks at applications and how they authenticate to SQL Server to gain access to the data the application will be using. Here the DBA would determine the most appropriate authentication method. SQL Server has two different authentication methods: Windows Authentication and SQL Server authentication. The DBA should work with the application developers and business analyst to determine what kind of users will be using the database, and where the applications will live. The DBA needs to determine if Windows Groups can be used to simplify security and how SQL Server logins and database roles should be used to facilitate securing SQL Server data. The DBA should consider developing an approach that simplifies managing security and provides the most flexible architecture for the long term.

Index Design

One of the key things each application needs to achieve is optimum performance. The DBA will work with the developers/business analyst to determine how tables are joined, and queried. From these discussions, appropriate indexes will be designed. The DBA needs to help refine the index design decisions overtime. They can do this by monitoring index usage and identifying missing indexes during the development lifecycle. This ongoing monitoring will help ensure appropriate indexes have been designed to help maintain optimal query performance.

Backup/recovery planning

When databases are being designed and developed, the DBA needs to determine backup/recovery requirements as well as disaster recovery requirements. From these requirements, the DBAs will need to design a backup and disaster recovery plan to meet the requirements. Once the database is actually created and backups are being taken, the DBA should perform. restore tests to ensure that the backup and recovery strategy is working.

数据库设计任务

数据模型评审

通常构建数据模型是设计一个新数据库的首要步骤之一。DBA应该评审这些模型。评审过程将使DBA熟悉数据库所要包含的数据。这些评审将引发关于如何处理和装载数据的讨论。这些信息将有助于决定数据的存储，读取，共享和管理的架构，也有助于确保数据被恰当的集成到企业数据库环境。

物理数据库设计

某些环境需要DBA进行数据库设计，有些应用开发环境，开发人员或者承包商会自行设计新数据库。不论什么情况，数据库开发者都要满足业务分析需求，帮助定义业务数据和处理需求。从这些讨论，DBA将开发数据库物理设计或者为此提供协助。数据库设计需要满足插入，更新，删除和导出需求。

数据库安全设计

这项任务来自应用程序，如何决定应用程序访问SQL Server的权限。DBA将决定最合适的身份验证方式。SQL Server有两种不用的身份验证方式：windows身份验证和SQL Server身份验证。DBA将和应用程序开发人员和业务分析人员来共同确定数据库的使用者以及应用程序的部署环境。是否Windows组的身份验证能够用于简化安全性，还是SQL Server登录和数据库角色能够加强安全性，DBA需要对此作出决定。从长远来看，DBA应该考虑开发一个能够简化安全性管理和提供灵活的架构的方法。

索引设计

每一个应用程序都需要优化性能，DBA将与开发人员或者业务分析人员共同决定表的连接和查询方式。从这些讨论中，恰当的索引将会被设计。通过监控索引的使用和鉴定缺少索引的地方，DBA帮助优化索引设计。这些监控将有助于确保正确的设计索引以帮助优化查询性能。

备份/恢复计划

当数据库完成了设计和开发，DBA需要制定备份与恢复需求以及灾难恢复需求。DBA将设计备份和灾难恢复计划。在数据库创建完成，备份方案开始实施的时候，DBA应该进行恢复测试以保证备份恢复策略有效。

 

Development Tasks:

Building Automated/Scheduled Database Load and Data Transfer Routines

Not all data in a database is entered into the database through data entry screens. DBAs need to be well versed in methods of loading and extracting data from a database. They need to be able to design and build automated processes that extract, load and transfer data. Therefore, they need to understand how to use tools like SSIS, BCP, BULK INSERT, SQL AGENT jobs, FTP, etc.

Database Code Reviews

As applications are being built the DBAs should meet with application programmers to have code reviews. These code reviews will help ensure code meets standards and use best practices. Ideally, these code reviews should be performed early on in the development lifecycle to minimize the effort required to make any changes that the code review might identify.

Building and Managing Automated Data Extraction, Transformation, and Load Routines

The DBA can be called upon to build data extraction, transformation and load routines (ETL). To accomplish this, the DBA would work with business analyst to define business requirements for the ETL process. From these requirements, SSIS packages, scripts and SQL Agent jobs would be built to run the required ETL process routinely.

开发任务

创建自动化或者定时ETL程序

并不是所有的数据都是通过键盘操作输入数据库的，DBA需要非常熟悉数据装载和抽取方法。他们需要能够设计和创建自动化的ETL程序。因此，他们需要熟悉相关工具如SSIS，BCP，BULK  INSERT，SQL AGENT jobs，FTP等等。

数据库代码评审

当应用程序完成创建后，DBA应该与应用开发人员一起评审代码。评审代码将有助于确保代码符合规范以及应用最佳实践。更为理想的是，如果这些评审工作在开发周期的早期开始执行，将使评审工作中的更改最小化。

创建和管理ETL程序

为开发ETL程序，DBA将与业务分析人员一起定义ETL程序业务需求。因此DBA将创建SSIS包，脚本以及数据库代理作业。

 

Maintenance Tasks:

Implementing Database Changes

The DBA should implement a change management process for database code and schema changes. This change management process would allow you to capture and track changes to stored procedures, views, trigger, table definitions, etc. over time. Ideally, these changes would be implemented into a source code repository. The change management process should consider not only how to implement changes but also how to back out changes should any change adversely affect stability of a database/application.

Routine Database Maintenance

The DBA needs to make sure all databases are being maintained and running optimally. To ensure this the DBA will need to run routine maintenance tasks. Here is a partial list of some of those tasks: reorg indexes, update statistics, defrag databases, and/or shrink databases.

Mentoring/Training

DBAs are typically the experts in querying data for a database. DBA staff should provide training and mentoring as needed to junior DBAs and application programmers. The goal of this task is to help staff gain the necessary skill set required to build efficient T-SQL code. This training will ensure that programmers write code that performs well. The DBA should also help ensure that secure coding practices are deployed so SQL Injection attacks are minimized. The DBA should educate developers in how to ensure steps are taken to properly edit user entered data to minimize any possible SQL injection attacks.

维护任务

实现数据库变更

DBA将管理数据库代码和架构变更。这些变更管理会跟踪存储过程，视图，触发器，表定义等的变更。变更管理关注变更的细节，另外如果变更对数据库产生不利影响，还能随时取消。

例行的数据库维护

DBA需要确保所有的数据库能够得到最优的维护和运行。为此，DBA需要运行例行的维护任务。以下列出部分任务：重组索引，更新统计，数据库碎片整理，收缩数据库。

指导/培训

通常DBA是查询数据库的专家。他们应该为初级DBA和应用开发人员提供培训和指导。其目的是帮助相关人员写出高校的T-SQL代码。另外DBA要参与应用程序的部署以减少SQL注入攻击的威胁。为减小SQL注入攻击的可能性，DBA应该教育开发人员如何采取正确的步骤保证安全的用户访问。

 

Monitoring Tasks:

Performance Monitoring

DBAs should monitor the performance of the databases they manage. This performance monitoring will encompass a number of different activities. First monitoring should capture performance measurements (statistics) when applications are first introduced into the environment. These statistics should then be used to develop a performance benchmark for an application. Each time enhancements are made to an application the DBA should gather new statistics and compare the results against the baseline. Any difference in the newly gathered performance statistics and the baseline statistics should be noted. Secondly, the DBA should be keeping an eye on server wide performance related indicators such as memory usage, buffer cache, object locks, application connections, etc. When something looks out of place this should trigger some analysis/investigation to determine what has caused these performance indicator changes. Another type of performance monitoring task a DBA might take on would be to help out with an application performance audit. This type of performance works would assess the performance status of an established application. The DBA would use different tools to gather performance information. This performance information is then reviewed by the DBA and application staff to suggest changes that will improve performance

Query Tuning and optimization

DBAs should be constantly looking for query tuning and optimization opportunities in the production environment. When poorly performing queries are identified, the DBA should work with the application programmers to determine why a query is running slow. This process would then develop strategies for improving the slow running queries.

Monitor Databases, Instances, and Database Servers for Availability

This task routinely monitors databases, instance and databases services to make sure applications can connect to them. This task promotes the idea of a proactive monitoring method to ensure database services are available as needed. Automated notification tools should be used to help the monitoring effort. Hopefully by monitoring instance availability, a DBA can resolve any issues prior to them being noticed by database users.

Monitor Error and Event Logs

SQL Server’s LOG directory and the Windows’ Event log should be scanned routinely by the DBA for each SQL Server instance, and machine. The goal of this monitoring activity is to identify abnormal error messages. When abnormalities are found in any of the logs files then an investigation should be undertaken to determine why these events occurred. All findings should be documented and communicated to the appropriate staff.

SQL Agent Job Failure Monitoring

At least once a day, and possibly more often, the DBAs should review SQL Agent jobs for failures. The monitoring can be done manually or automatically. All jobs failures should be investigated so the appropriate actions can be taken to resolve any failures found.

监控任务

性能监控

性能监控包括许多方面。第一，在引入应用程序的时候监控将捕捉性能指标（统计），这些统计将用于应用程序的性能基准。DBA应该收集新的性能统计并与基准比较。任何新收集的性能指标统计数据和基准数据的差异都要记录在案。第二，DBA要留意服务器的性能指标如内存使用率，缓冲，对象锁定，程序连接等等。如果有不正常，应该立即分析和调查原因。DBA的另一项性能监控任务是评估应用程序的性能状态。DBA使用各种工具收集性能信息。DBA和应用程序开发人员将据此改进程序的性能。

查询调优

DBA应该在生产环境里不断的寻找查询调优的机会。在鉴定有问题的查询之后，DBA应该与应用程序开发人员一起探讨查询慢的原因。然后采取相应策略。

监控数据库，实例，以及数据库服务器的可用性

这项任务监控数据库，实例和数据库服务以确保应用程序能够连接到数据库，同时确保数据库服务随时可用。自动化的消息工具将用来帮助监控。通过监控数据库实例的可用性，DBA可以在数据库的使用者之前发现并解决故障。

监控错误和事件日志

DBA应该定期扫描每一个SQL Server实例和机器的SQL Server的日志目录和Windows的事件日志。目的是鉴定错误消息。当在日志文件中发现不正常的情况的时候，应该立即着手调查其原因。所有原因都应该记录下来并且与相关人员进行交流。

SQL代理作业失败监控

DBA应该至少每天一次或者更频繁的审查失败的SQL代理作业。这项监控可以手动执行也可以自动执行。所有失败的作业都应该被调查。

Planning Tasks:

Capacity Planning

This task identifies the amount of space needed for new databases and the growth rate for existing databases. Capacity planning takes into account the amount of new data added daily, monthly and/or yearly to ensure there is enough disk space available to handle the growth rate of a database. Grow rates are calculated by gathering disk space statistics periodically for existing databases, or best guess growth rates for new database.

Database Architecture Planning

The DBA needs to develop a database architecture for their environment. This architecture needs to promote sharing and securing data, as well as connecting to databases. One of the items of this effort is to make sure there are common, flexible, and supported ways to connect to SQL Server databases. Another aspect of this architecture is to make sure you design a security model that supports your security requirements and is flexible enough to be adapted and maintain over time. This item also ensures other management issues, such as backups, maintenance, and troubleshooting are all done using common tools and processes. Using consistent processes helps minimize effort when managing multiple database servers and instances.

SQL Server Upgrade planning

Microsoft comes out with a new release of SQL Server every 3 years or so. The DBAs need to consider this and then develop a plan for upgrading and/or installing new versions of SQL Server as they are released by Microsoft. This planning effort will help identify the requirements and steps/approaches necessary to migrate from one version of SQL Server to another.

计划任务

容量计划

这项任务鉴定新数据库的使用空间以及旧数据库的增长率。容量计划考虑每天，每月或每年的数据增量，确保有足够的空间来应对数据量的增长。增长率是根据定期收集的磁盘空间统计数据来计算的。或者为新数据库估计最佳增长率。

数据库体系结构计划

DBA需要为他们的环境开发数据库体系结构。该结构要能提升数据访问和安全性。其中重要的一点是确保正常的，灵活的和有效的连接数据库。

SQL Server升级计划

微软大约每三年发布一次SQL Server的新版本。DBA需要制定一个升级和安装计划。这个计划将有助于制定不同版本的迁移方案。

 

What Kind of DBA Are You?

This list of tasks covers the spectrum of DBA Tasks. Not all DBAs will perform. all of these tasks. If you are a developer/designer of databases you probably only perform. a subset of these tasks. If you are an operational DBA that covers installing, backing up and disaster recovery then you most likely perform. a completely different set of tasks than developer/designer types. If you work in a small organization then you might perform. a lot of these, but may not have time to perform. all of these tasks. What kind of DBA are you?

你属于哪一类型的DBA呢？

这个DBA任务清单概括了所有DBA的工作。并不是所有的DBA都要参与这些工作。如果你是一个数据库开发或者设计人员，很可能只涉及部分工作。如果你是一个管理维护的DBA，你的工作将会覆盖安装，备份以及灾难恢复，完全不同于数据库开发人员的工作。如果你在一家小公司上班，那么很有可能你要执行大部分的任务，而没有时间完成所有的任务。你属于哪一类DBA？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25819997/viewspace-702067/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25819997/viewspace-702067/