MS-SQL Server - Determination

Determination

D - Develop DBA environment and capture data to analyse later.

E - Express edition is much better for self practice.

T - Transaction Log Backup is important for business critical application.

E - Editions are defined to save the cost as well, so understand your requirement before finalising the product.

R - Roles can be utilised to perform different type of work.

M - Master database is just for SQL Server itself, we don't need to create any personal object on system databases.

I - Isolation levels have some use.

N - Native tools covers almost 100% of requirements.

A - Architecture is different for All these categories

- Services

- Databases

- Configuration

- Logical

- Physical

- Security / Authentication

T - Teamwork helps to have another point of view.

I - Installation must be attended to observe any live issue.

O - Optimisation Techniques belongs to

- Procedures

- Queries

- Configuration

- Space Allocation/Growth

- Maintenance

N - Named Instance can be used for load balance and on the server where resources are less than 30% used.

Additionally,

A Commitment

In this article we will learn about the commitment one (a DBA) should have in their routine day to day activities.

The commitment for....

- Verifying The Backup after getting finished (Automatic/Manual).

- Running Consistency Check (DBCC CHECKDB - at least twice a month)

- Stripping of FULL Backups for Large databases that are more than 10GB. This would help in critical condition where we may have to copy backups to network location.

- Capturing Performance counter data frequently into DBA database to be able to analyse the performance bottleneck.

- Running Index Maintenance on Daily basis during off hours.

- Waittype Analysis - To further more dig into it and analyse that sessions are actually waiting on which wait type.

- Need to ensure the Recovery Models are set as per requirements.

- Need to ensure the number of Virtual logs (User Databases) are as per recommendations.

- Backup of System Databases.

- Providing permissions as per actual requirement.

- Capacity Planning for overall space requirement in next coming years.

- Track All DBA related activities in DBA Database.

- Database Mail must be enabled to send alerts.

- Setting up DDL changes to track everything to help Developers.

- Denying Shrinking of Data files even when large deletion/purging by application team.

- Analyzing the Execution Plan for Long running procedures to be able to optimize from DBA perspective.

- Applying Latest Service Packs and C-Updates/Hotfixes regularly.

- Killing Sessions - we always need to ensure that if any session is required to be killed/cleared to resolve blocking, the SPID must be owned by a DBA.

Also

Need to ensure taking Backup of below mentioned on monthly basis.

- Backup of Logins with Permissions.

- Backup of SQL Agent Jobs.

Please share your valuable thoughts to improve the same.

Best Regards

T. Amit Vaid S.