MS SQL Server Administration: Introduction and Server Roles

A DBA (database administrator) should focus on keeping the MS SQL server running as well as doing it fast. Server and software configuration is the key responsibility of a database administrator. Just installing the server software doesn’t make the process complete. Pre-installation decisions such as number of servers, layout of the drive, and schemes for replication are very important. SQL server’s major role towards proper sizing & configuration of the hardware must also be determined.

Issues in Post-installation

Well, space management happens to be one of the most vital issues after server installation. An administrator may choose to adopt an approach of first setting the server & then leaving it like that as the SQL server can automatically either grow or reduce a database. Rather, the administrator must follow any of the numerous appropriate strategies so as to monitor & notify server space.

Backups

Of course, the DBA is primarily responsible to take regular backups and certainly this is something a DBA just cannot afford to ignore. In fact, lack of such backups might spell disaster when a crisis occurs in which situation even the best SQL expert cannot help (if there isn’t any backup). While there are tools and wizards to help during such situations, that doesn’t take away the need for a DBA to understand what exactly the wizard has opted to do.

Recovery

It is very important to know the performance of recovery steps. Though for recovery of the database up to the very last known good state, essential tools come available with SQL Server Enterprise Manager, the DBA understanding the performance is always desirable.

Server Environment

An SQL Server Administration DBA must learn the server environment which changes with time. And, it is better to maintain the service pack installation log as well as the security accounts.

Monitoring Aids

Obviously, performance on every database has to be monitored actively and the performance should be optimized for the server so as to not only run but run fast. Internal SQL server tools such as Enterprise Manager and Query Analyzer help a DBA regarding this task similar to external tools.

Server Roles of MS SQL Administration

Roles

A new feature, Roles, perform similar to the Windows NT local group. One of the several roles that are supported by the MS SQL Server is server roles while the others are database & application roles.

Server Roles

Server roles are defined at the server level that lie outside the users databases. These server roles are fixed; it cannot be added, modified or deleted. Users can just be added as a member of the server roles.

Types

There are seven types of fixed server roles of MS SQL Server Administration:

Let us have a look at the roles of each of them in brief:

• All the activities in the MS SQL Server can perhaps be performed by the sysadmin server role members & the overall database functions tend to be totally controlled by them.

• Server admin server role members can change the server configuration parameters as well as shutting down of the SQL server.

• Setup admin server role members are capable of adding or removing linked servers, manage replication & extended stored procedures. A few system stored procedures such as sp server option tend to be executed via this role.

• Server logins creation and management along with auditing & reading error logs can be done by the security admin server role members.

• Managing the processes which run in the SQL Server is performed by the process admin server role members.

• Creation, alteration & resizing of the database are the key functions of the dbcreator server role members.

• Diskadmin server role members manage the disk files.