SQL Server administration best practices
Divya Kalra &
Narayana Vyas Kondreddi
Best practices for system administration in a Microsoft SQL Server 7.0 / 2000 environment, including regular maintenance tasks.
No industry today can do away without engaging in a working and efficient data protection plan. Data being the life and blood ofany enterprise, protecting it becomes an inevitable task. All it needs for corporate data to be safe and secure is - a sound and wise investment in a backup and restore strategy and its implementation. If an organization considers data important, then it must focus on data protection and be willing to bear the costs associated with it. The elements of cost for such a strategy include:
* Timeinvested in Planning
* Trained personnel
* Backup and restore hardware/media
* Backup and restore software
* Scheduled testing and validation of recovery plans
SQL Server utilizes a structure called a backup device to manage backups. These are logical names that point to physical files on the local hard disk or a network share. The backup devices allowed by SQL Server are tape,disk, and pipe (Note: Backups can also be written to and restored from physical files directly, without creating backup devices).
After all the introduction of why and how of backups, let's get to the core basics. We all know that backups are a must and they are the crux of an enterprise that cares about their data. Let me quote from an article I once read:
"If a DBA maintains proper backupsand can guarantee recovery of data up to the point required by the business process, they have done the job they were hired for. A solid backup plan is the first thing a DBA is required to do. If a DBA does absolutely nothing else in your company, he/she has earned their money by providing a solid backup plan and protecting your data. Every other activity is a simple bonus on top of this."
Theplanning and implementation of backup and recovery plans, the steps involved and guidelines are discussed under the following sections:
* Creating a solid backup plan
* Determine where to store backups
* Things to keep in mind
* Determine when to backup databases
* Restoring databases
* Creating a solid disaster recovery plan
* Tips to save your life as a DBA
Creating asolid backup plan <top>
Nothing strikes fear into the hearts of managers, users and administrators than a server crashing and data becoming corrupted. A few short years ago, this would have been relatively important. But nowadays, it is absolutely vital and is one of the most serious things that can happen to a company. This is because, the data contained in the databases represents thecompetitive advantage of a company and its entire lifeblood. Losing key data can be catastrophic to a company.
Therefore, it is absolutely imperative that the DBA constructs a solid and reliable backup strategy so that in the event of a disaster the data can be recovered. The main questions that need to be answered while coming up with a backup strategy are:
1. How frequently does the datachange in our system?
2. What is the downtime allowed for the production server in case of failure?
3. For determining the maintenance window, what is the time of the day, week and month when the database server is likely to have minimum activity - i.e. minimum updates?
4. How much is the enterprise willing to invest in data backup strategies? This is directly answered by addressing thequestion - how crucial is the data? Is it mission critical?
5. How much data loss is acceptable to the company?
6. How do we plan to recover the data in case of a failure?
Determine where to store backups <top>
As we learnt before, SQL Server can back up to hard disk, tapes or named pipe devices. This question above pertains to making a decision as to what media does a company want to...