SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DBA Administration Tasks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/28/2006 :  06:01:43  Show Profile  Reply with Quote
I wanted to come up with a list of tasks for the management of a system (in our case both SQL and IIS/Web). Whatever I was typing into Google wasn't working as all the hits on "administration" were to do with using the GUI tools to create new databases / web sites, rather than "look after" existing ones.

Here's my list, I'd appreciate any other suggestions please. My "times" are deliberately vague (e.g. "periodically", "within a reasonable time") to allow for actual frequency to be decided case-by-case


Backup all (non-junk) databases

Check that all backups have actually run; check that tape backup has picked up the files within reasonable time. Ideally transfer the backup files to a different location on the LAN immediately after they are created; check that such copies are working correctly

ZIP SQL and IIS log files periodically, purge after reasonable period

Export database log tables, ZIP periodically; purge stale log/audit data after a reasonable time.

(this includes database tables specifically used for Logging purposes, and also Audit tables; these are exported using BCP for long
term "just in case" storage, where-after the data can be deleted and the database space released, enhancing performance. Also includes MSDB backup history tables)

Review slow running queries. Adjust indexes or recompile as necessary. If required make proposal for reprogramming/redesign.

Tune hardware and software to improve performance when considered appropriate (usually make a proposal first)

As part of that profile database performance as appropriate

Optimise SQL indexes and free space, update statistics, check database integrity.

Monitor web site and SQL Server for downtime, establish cause of downtime, escalate (e.g. to ISP) if required. Automated, failsafe, monitoring is assumed.

Check files (such as database, backups, SQL & IIS log files) for unexpected growth

Examine web logs for evidence of hacking attempts etc. or other unusual behaviour.

Audit security using other checks too.

Check SQL and Event Logs

React to issues in SQL logs - such as allocating space, detecting failing hardware

Install software patches / service packs

Tests of Redundancy:

If there are Fail-over servers perform checks that they are up to date, and instigate fire-drill procedures for checking viability of disaster recovery periodically. (Also applies when any other form of Disaster Recovery plan exists)

Periodically Test that backup files can be restore on a different server; perform DBCC CHECKDB on the restored database. (Ideally this will be automated and performed for every single backup made).

Kristen

Edited by - Kristen on 09/28/2006 15:42:37

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 09/28/2006 :  15:31:07  Show Profile  Visit MichaelP's Homepage  Reply with Quote
"If there are Fail-over servers perform checks that they are up to date, and instigate fire-drill procedures for checking viability of disaster recovery periodically. (Also applies when any other form of Disaster Recovery plan exists)"

Make sure "testing of backups" is included in this DR fire drill. So many people do backups, but they never practice restoring and validating that they have a good backup. A backup that isn't tested isn't a backup.

Overall, that list looks pretty good.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/28/2006 :  15:43:26  Show Profile  Reply with Quote
Thanks MichaelP, good point about test-restores of backups.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000