Tag Archives: DBA Tasks

What are SQL Server DBA Responsibilities?

Posted on by
8

This is one of the most frequently asked questions by people who are new to SQL Server. Since they are new to SQL Server, their concern is understandable. I was personally asked this question or this list multiple times and most recently, as comments to one of my blogs.

So I decided to list down a list of tasks and responsibilities carried out by a SQL Server DBA. Before further reading, let me tell you that roles and responsibilities of a SQL Server DBA varies from one organization to another as no two organizations IT setup are exactly similar.  For better understanding, the text in the parenthesis (Italicized blue font) is the subject area or chapter that has the topics to carryout the responsibilities…

  • Installation, Administration and Maintenance of SQL Server Instances. (Installing SQL Server)
  • Setup Test, Dev, Staging and Production Environments. (Installing SQL Server)
  • Create Users and assign permissions based on the level of database access the user would need. (Security)
  • Create Linked Servers to SQL Servers and other databases such as Oracle, Access, Informix etc. (Security and General Administration)
  • Design database Backup and Restoration Strategy. (Database Backups and SQL Server Agent)
  • Once created the database Backups, monitor those backups are being performed regularly. (SQL Server Agent)
  • From time to time recover the databases to a specific point of time, as per the requests. (Database Backups and Recovery)
  • Setup High-Availability as part Disaster Recovery Strategy for the Databases. (Failover Clustering, Database Mirroring, Log Shipping and Replication)
  • Troubleshoot various problems that arise in a day-to-day work and fix the issues. (Monitoring SQL Server Error Logs and checking your email alert (if there is one configured))
  • Monitoring and Performance Tuning; Physical Server Level, Database level (Database settings and options) and query tuning. (Creating and maintaining those Indexes, not performing database shrinking, memory settings, monitoring CPU usage and Disk I/O activity etc) 
  • Documenting major changes to the SQL Servers. (General)
  • Apply Service Packs. (General)

Note: These are only some of the roles carried out by a SQL Server DBA. If you have more questions, please let me know through comments.. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

SQL Server Database Backup types and why are they required

Posted on by
Reply

Database Backups are very important and there is not much to say why are they important; that is the only way to restore the data back to a point of time. In this blog we shall learn the different database backup types available in SQL Server. There are 3 basic types of backups (there are other types too) in SQL Server which every aspiring SQL Server Database Admin should be aware. They are

  • Full
  • Differential  and
  • Transaction Log

Let us look at more details about these backup types.

Full Backup: Full backup is perhaps the most basic type of the backup in SQL Server and the important too. Full backup contains the complete database, including the data (in the tables) and other database objects such as Indexes, Stored Procs, Triggers etc.. Using this backup, the database can be restored (or recovered) until the point-in-time when the Full backup was completed. The advantage with Full backups is that they are completely independent of any other backups, unlike the other 2 types of backups. Since a Full backup will contain the entire database, the size of the backup will be proportionate to the actual backup size (when the backup is not compressed, a feature released in SQL Server 2008).  The same is true with the amount of time it takes to perform a Full backup, the larger the database, greater the time. Any kind of database restore should always begin with a Full backup. These backups can be taken on a database with any of the recovery model.

Differential Backup: Differential backups contain all database changes made since the last Full backup. A differential backup taken a day after the Full backup will contain all the database changes during that one day. A differential backup taken 2 days after the Full backup will contain all the changes during those 2 days. Due to this, Differential backups can be called as cumulative in nature. A recent differential backup contains the changes that were already backed up by a previous differential backup. As more and more data is modified, the Differential backups size increases. A Differential backup cannot be restored separately, instead it should always be used along with a good Full backup set (that is, the most recent Full backup preceding the Differential backup). Differential backups can be taken on a database with any of the recovery model.

Transaction Log: Transaction Log backup also called as Log backup contains all the changes that have been made since the last Full backup or a previous Log backup. Transaction log backups are opposite to Differential backups, i.e, you need each and every transaction log backup, before a specific point-in-time. Maintaining and protecting all transaction log backups is vital. A single log backup missing in a chain can break the ability to restore the database to the most recent point-in-time. Unlike Full and Differential backups, the benefit of Transaction log backups is that they can help us restore to a specific point in time. Log backups are generally scheduled to run at a meaningful short interval such as every 10 or 15 minutes. Log backups are available for only those database which have the recovery model set to Full or Bulk-Logged. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Find Databases without recent full backups

Posted on by
Reply

One of the important tasks a SQL Server DBA does is database backup. While maintaining the backups of the important database is important, the need to monitor that the exists a valid Full backups for the databases is equally important. This blog will try to explain how to find the list of databases which do not have a Full backup in the recent few days. (8 days in this example). You can obviously change the value in DATEADD function to suit your choice for # of days to look for.

The code used in this example is found at the end of this blog.

First, I run the script to find the list of databases which do not have a full backup. The very first statement you see in the script (SELECT @@VERSION ) is not required, I included it my example to show the SQL Server Version on which I am executing these scripts. The reason for including it is, a slight change in the script. In SQL Server 2005 and 2008, I am querying sys.databases and in SQL Server 2000 it does not exist, so I would need to use master..sysdatabases.

The query returns list of the databases on my instance which do not have a full backup in the last 8 days.

I then perform a backup of Adventureworks database, which is one of the database missing a full backup. the picture below is the script I run to create a backup of Adventureworks DB.

The next thing I do is run the same script that was run at the beginning to find out the databases without full backups and this time displays only the database Snapshot_Source.

The following is the script that works for SQL Server 2000. Note there is no database missing full backups, indicating I have at least full backup of all databases on that server.. Based on the importance of the data in the databases, other backups types have to be configured and monitored. However, a full database backup would always be the base of any database recovery process and should be as recent as possible to avoid delays in recovery process.

Script used in this example…

SELECT @@VERSION AS [SQL SERVER VERSION]

SELECT NAME FROM sys.databases
WHERE NAME != 'TEMPDB'
AND NAME NOT IN (    SELECT DISTINCT database_name FROM msdb..backupset
WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())
AND  TYPE = 'D' )

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty