MS SQL Server Database Administration

Every business at a certain stage of its maturity comes to realize the vital importance of proper management of its business data. The Database Administration team within the IT Department is directly responsible for the overall health of business data.

It is obvious that certain level of skills and experience must be present within the DBA team to produce expected quality results. Also, certain resources and effort must be allocated and put in place to bring the Database Administration function to its proper industrial level.

This page describes major activities the DBA team must perform and should be viewed as high level management guide only.

 

Major Database Administration responsibilities include:

  • Installing and maintaining database servers,

  • Managing and monitoring Access Security,

  • Performance Monitoring and Tuning,

  • Disaster Recovery Planning and Testing,

  • Application Software Development Support,

  • Data Management,

  • Third party products support,

  • General Production Operations monitoring & support,

  • Maintaining and publishing of up to date database related documentation.

1.  Installing and maintaining database servers.

  This activity is not performed every day, but is a very important one and requires multiple skills and certain level of experience. It includes:

  • Identification and documentation of actual need for a database server,

  • Sizing up a proper hardware to reflect required data capacity, availability, expected user activity and network traffic,

  • Choosing appropriate operating system and database server version,

  • Installation and configuration of both – operating system and database software for optimal operations and performance,

  • Timely assessment, planning, testing and application of upgrades, service packs and patches recommended by operating system and database software vendors.

2.  Managing and monitoring Access Security.

Access to the business data must be properly controlled and maintained to ensure its proper use and quality. The DBA Team is responsible for implementation of data access controls within a framework of organization level security policies in cooperation with Security Administration Officer. The activity includes:

  • Identifying and documenting data access requirements by various users and applications,

  • Planning and defining appropriate Domain and local user groups,

  • Planning and defining SQL Server logins and database roles,

  • Planning and implementing formal procedures for granting / revoking required level of access and monitoring user activities.

3.  Performance Monitoring and Tuning.

 This activity takes a longer view to ensure adequate performance of database parts of business applications while accumulated business data grows over time. With regular and systematic performance monitoring, potential “bottlenecks” can be identified and resolved in a manner that minimizes disruptions to  normal business operations.

  • A performance baseline that includes major performance measurement counts should be created for each database application,

  • Regular performance traces should be taken and compared to original baseline and any performance degradation cases should be investigated in detail.

  • Identified problematic areas should be documented and solutions examined and implemented.

4.  Disaster Recovery Planning and Testing.

DRP is an organization wide continuous process of planning, testing, learning, improvement and implementation. The DBA team must be deeply involved in this very important activity including:

  • Close coordination with application of DRP,

  • Choosing appropriate hardware and tools to be used,

  • Planning and testing executions and more.

5.  Application Software Development Support.

When business applications are being developed in-house, it is crucial for the DBA team members to be involved in all stages of application life cycle:

  • Business data analysis,

  • Design of application’s logical and physical data models,

  • Creating and maintaining database objects,

  • Writing/reviewing of applications’ data access code to ensure its efficiency and correct use of database resources,

  • Performing and coordinating of software and database change migrations, various testing cycles, maintaining test data and more.

It is responsibility of the DBA team to set up and enforce various database related standards.

 

6.  Data Management.

  Not all business data manipulation activities can be fully automated. These include various ad hoc user requests not already covered by existing software systems, like external data loading/extraction for marketing purposes, for example. In most cases this type of activities naturally fall into DBA team hands to be processed in a timely and accurate manner.

 

7.  Third party products support.

To ensure continuous availability and general health of databases used by third party software products, it is responsibility of the DBA team to perform regular diagnostics, monitoring and maintenance tasks in a view to early detect and identify possible performance degradation and alert related software vendor. Also, these databases must be included into organization’ Disaster Recovery Planning and testing activities.

 

8.  General Production Operations monitoring and support.

  • Monitoring SQL Server logs,

  • Ensuring all “houskeepeng” tasks completed on time and with expected results,

  • Responding to the Help Desk calls and more.

9.  Maintaining and publishing of up to date database related documentation.

All above activities when done properly, generate considerable amount of documentation, which is to be useful in the time of need must be kept up to date, backed up and its location well known. While MS SQL Server as a product includes many features that are self documented (like maintenance plans and database properties, for example), there are plenty of scripts and NT jobs that are executed regularly and on ad hoc basis. Having proper Operation Guides (run books) related to database/applications operations is the aim worth considering.