Database Administration is a crucial and complex task that involves many different skills. A database administrator may be involved in many or all of the following tasks:
- Installing and Maintaing a RDMS
- Creating and modifying Database Objects
- Importing and exporting Data
- Securing and monitoring security for the server and its databases
- Backup, recovery and emergency management
- Maintaining high availablity
- Monitoring and maintaining performance standards
- Managing Applicatons and changes to databases
- Documenting everything
Additionally a DBA might be involved with
- Application Development
- Business Intellegence and Analysis
- Report Generation and distribution
Return to top
Most likely, when you begin a database position the DBMS will already be installed. But you still may have to perform some installations. You may, for instance, need to install a new instance of a DBMS in order to expand current capabilties or in order to test certain applications. You may also need to install new versions of a DBMS for testing before upgrading the current DBMS
The actual crucial tasks for installation occur before you ever load the software onto the computer. These tasks include:
- Determining the Purpose of the new installation
- What tasks, specifically is the new DBMS to perform. Is it for testing? Is it part of a mirroring or replication set up? Is it going to be used locally or will it handle Internet traffic. How many users will it handle? What kind of availablity will it require? Do you have the proper version to meet these needs?
- Determining the System requirments
- Disk space, Memory, CPU speed, Operating System. For SQL Server editions look here for System requirements.
- Determining the necessary settings
- Collation (language and regional settings), which services and tools you need, etc.
- Determining the security environment
- What server OS will it be on. What are the Windows Security setting you will apply? What are the SQL server Settings?
Return to top
A Database administrator may well have to make or edit database objects. You should know how to do this both using the DBMS's GUI (if it has one) and useing SQL.
Some of these objects are:
- Databases
- You will need to know how to create and drop databases. This also includes allocating their primary and seconday files and logs.
- Tables
- Create, Alter and drop tables with a full understanding of the relational model and constraints
- Indexes
- Add, drop and monitor the effectiveness of indexes (see also Performance)
- Views
- Views are stored queries. You may be responsible for creating, altering or dropping views.
- Stored Procedures
- Stored procedures are stored batches of SQL commands (though SQL 2005 also lets you create them in CLR C# or VB. You will need to know how to Create, alter and drop stored procedures and assign exec permissions to them.
- Triggers
- Triggers are like stored procedures but are triggered by an event, usually INSERT, UPDATE, or DELETE. (SQL Server 2005 also allows DDL triggers for events such as CREATE, ALTER, DROP).
- Functions
- Create, alter or drop user defined functions
- Users, schema
- Add or drop users, assign objects to a user's security schema
Return to top
You will also need to learn how to import and export data. You will need to learn how to bulk or batch imports and exports, and how to automate those that recur regualarly. Typically you will need to transform the data into some other format such as delimitted or fixed length text files. Increasingly, you will need to know how to import and export xml.
Return to top
Certainly one of the most important tasks of a DBA is securing the database. This involves at least four levels of security:
- Network
- Securing the network access to the DBMS, also securing the Windows Server (Active Directory accounts etc.)
- DBMS
- Securing the Instances of the Server. What kind of security (Windows or SQL server) Who has access and how much.
- Database Objects
- Granting or revoking permissions on Databases, Tables, stored Procedures, views etc.
- Application
- Managing Applications' access to the database
Return to top
Availability means keeping the DBMS up and running so that the Databases are available for applications and users. Typically in these days of Internet and international access, that means 24 hours a day, 7 days a week. It is a challenge to provide this type of service while maintaining the database by applying service packs and making needed changes. It also means keeping the database available in cases of hardware or software failures, power outages or disasters.
There are several stratagies for dealing with Availability. At the minimum you need to back up the databases regularly. More sophisticated policies include Raid disk set ups, Database Replication and Database Mirroring. Ideally, you should develop a fail over strategy. That is if one DBMS fails it should automatically fail over to another DBMS which will carry on exactly where the other one left off.
Return to top
A DBA also continually monitors the database for performance to see if it is meeting the requirements of its users. (SQL Server ships with several tools to help monitor various aspects of database performance. The DBA can tinker with hardware and can also create indexes and rewrite queries or stored procedures to optimize the speed and efficency of database operations
Return to top
A key task of the DBA is to document everything. This documentation can include:
- Data Dictionary
- This can include the names and descriptions of every object in the DBMS, along with schemas ERDs etc. Some of this documentation can be in the database catalogs themselves.
- Policies and Procedures
- Policies are the sets of rules that every one must follow in relation to the DBMS. Policies include the security policies, nameing conventions and polices, policies for changing database objects, application policies, etc.. Procedures are the steps that must be taken to accomplish some task. Procedures include things like adding or removing a user from the database, reporting security breaches, requesting a change to a database, etc.
- DBS History
- A DBA should record when database objects are created, changed or dropped. There should be a running record of decisions and actions related to the DBMS.
Return to top
In addition a DBA could be called upon to perform a variety of tasks, such as developing applications, analysing data for business trends, or creating reports.
Return to top