web 2.0

Tips for the Accidental DBA

Author: Michael Ceranski | Posted: 4. August 2009 2

There seems to be a lot of small companies out there who have appointed some unwilling developer as their DBA. Being a DBA comes with a lot of responsibility. For the most part, being a DBA means that you are in charge of making sure that the company's data is secure, backed up and accessible 24x7. So if you are an accidental DBA then I hope this article will help you out.

  1. Know your SLA - SLA stands for service level agreement. In most companies the SLA is determined before a project goes live. The SLA describes the minimum performance criteria that a provider promises to meet while delivering a service. It also outlines any remedial action and any penalties that will take effect if performance falls below the promised standard. In a nutshell this means "how long does the server have to be down before someone loses their job?". In general, all decisions regarding backups, database maintenance and etcetera should revolve around the criteria set forth in the SLA. That is why it is first in this list.
  2. Develop a Disaster Recovery Strategy - If the SLA says that you can only be down for a maximum of an hour in the event of an emergency then you better make sure that you have a restore strategy to fulfill the requirements. There are many high availability technologies to chose from but each one has its limitations.
    • Clustering - A database cluster consists of two or more physical machines (nodes) which utilize a shared disk on a SAN in order to keep the data files accessible at all times. If one of the nodes fail then the drives hosting the SQL data will "failover" to the other node and the SQL services will start up on the secondary node. When the services restart on the secondary node, SQL Server will go through the auto-recovery process and all transactions that were active before the failover will be rolled back. The drawbacks to clustering are that you have a single point of failure at the SAN. Therefore in order to be safe you will probably need SAN level replication. For small companies a SAN may be too expensive to implement and having a secondary SAN for replication may be completely out of the question.
    • Mirroring - There are two different configurations available for mirroring. The first is high-safety mode which uses a two-phase commit. This means that the transaction has to safely be applied on both instances before a transaction is considered complete. The second option is high-performance mode which uses an asynchronous style of transactions. In high-performance mode a transaction is considered complete as soon as it is finished on the principal server. The records will eventually make it to the mirror server but there could be a lag. Mirroring is a good option if you do not have a SAN. In addition, many people use the secondary "mirror server" for reporting purposes. While transactions are being applied to the mirror server the database is is inaccessible, but you can create a database snapshot of the mirrored copy which will be available at all times. Reporting on the snapshot will take some strain off your principal server but you will have to make sure your end users do not require "real-time" data.
    • Log Shipping - Log shipping is similar to mirroring in the fact that transactions are replayed on another server. However, Log shipping only gives you the warm standby option whereas mirroring can do both hot and warm standby depending on the configuration. The one drawback to mirroring is that it allows a single destination database where log shipping can support multiple.
    • Replication - Replication is yet another technology for replaying transactions on a separate server. There are three different kinds of replication which include merge, transactional and snapshot. While replicating data the databases can be accessed on the secondary server for reporting in "real-time". The drawback to replication is that it is not going to provide you with a automatic failover. If the primary server goes down you will need to manually redirect your client applications to the secondary server. For the most part, replication is really not intended to be a disaster recovery tool. It was really meant as a way to distribute data between different servers for reporting and accessibility purposes. You can replicate an entire database, a table or only certain rows from a table. If I was planning a DR strategy I would probably stick with Mirroring or Clustering.
  3. Pick a Recovery Model - Do you need point in time recovery up to the second? If so then you should probably use the full recovery model. Bulk-logged offers a little better performance than Full but it does come with the risk of data loss. Under the bulk logged recovery model, a damaged data file can result in your end users having to manually redo some transactions. The simple recovery model is the best for performance but it only will allow you to recover to the most recent full or differential backup. Simple recovery is ideal for development and test servers but hardly ever used for production databases.
  4. Backup your databases and TEST THEM! - Backup your databases to a separate location. At my company we keep a few days worth of "online" backups on the NAS (do not put backups on the same machine that you are backing up!) and then after a few days they get written to tape. Make sure that you test your backups on a regular basis. Keep track of how long the restore process works and also make sure that if you need point in time recovery that you are familiar with how to apply transaction logs. When an emergency occurs you want to complete confidence in your backups and your restore process.

For an accidental DBA, this list can be a bit overwhelming. If nothing else, make sure you always have valid backups and that you know the restore process cold. There are many other aspects to being a DBA such as index maintenance and performance but none of those tasks are as crucial as having a good backup.

Additional Resources:
MSDN - Database Mirroring and Log Shipping
MSDN - High Availability Solutions Overview

Tags: , ,

DBA | SQL

E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (2) | Post RSSRSS comment feed

Comments

Mathwiz , on 12/17/2009 2:29:14 PM Said:

Mathwiz

Hi Michael

Your Post is certainly helpful.

I am certainly one of them, I hope you can help me out with initial suggestions

I went through your list but lets say that the list applies to Big transaction processing organizations.
The Company that is employing me as a temporary DBA is a university and it basically needs someone to maintain/keep the engines running and to extract reports for financial teams till the original dba recovers so they hired me an experienced developer. And lets face it it will be a good experience for any developer to manage

Its a Windows NT Server 2003, Sql Server 2005 environment
Now my concern is there isnt much of an IT Team they got, and any database related problem would have to be handled by me, i should pray for no problems
BUT

what kindof problems should i be prepared for.?
What are the tasks that i should be expected to accomplish ? in terms of maintaining a database/databases which are maily used to hold university data maybe rangine from financial records like fees, to student history.

there wasnt much of a detailed description given as they only had 1 DBA and he is on leave for some time.

Also please could you point me towards any video links
1)How can i connect Toad to Management Studio
2)For viewing the different process/queries being run by everyone on the database.
3)To perform tasks such as identifying if any query requested by a user is locked by another transaction that the user submitted and it was never committed/rolled back. i.e. how to identify/view deadlocks in the list.
4)How to increase tablespace for data loading or some tasks like that.
5)How to add indexes or anything else that a general dba does in daily tasks please help me out
You can reach me at Lnrganesh at gmail

Michael Ceranski , on 12/17/2009 2:29:14 PM Said:

Michael Ceranski

I will try to give you a few quick answers. If you need more help then leave another comment and I will give you my gmail address so we can chat in more detail.

For point in time recovery make sure you are doing full backups daily and logs every 15-30 minutes. Practice restoring to make sure you know how the process words.

1. Toad is similar to management studio but it is not required to manage a SQL database. It has some wizards which can make life easier but for basic backup/restore and management just stick to management studio.
2. There are a lot of ways to look at the current activity in the instance. Look at sp_who, sp_who2, and the trusty activity monitor. You can also look at DMVS and system views such as sysprocesses.
3. Activity monitor will give you this information. Looking at the blocking and/or status columns. For open transactions you can issue a dbcc opentran. It will give you a list of currently opened transactions.
4. Tablespace is really an oracle concept. You are probably asking how to increase the size of a datafile. To do that, right click on the database in SSMS and on one of the tabs you will see all of the datafiles. Set them to whatever size you desire. In most cases you want large initial sizes to reduce fragmentation.
5. Indexes can be added by either using DML or by management studio. In SSMS you can expand the table and view the indexes. You can also right click on the indexes folder to add new ones.

Good luck and remember the key to being a good DBA is to always double check everything 2-3 times before hitting the OK button. When DBA's make mistakes the penalties are usually extremely high. I suppose that is why most DBA jobs pay more. When developers make mistakes, they generally recompile their code and no one knows any better.

Comments are closed