Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-09-28 : 06:01:43
|
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) databasesCheck 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 correctlyZIP SQL and IIS log files periodically, purge after reasonable periodExport 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 longterm "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 appropriateOptimise 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 growthExamine web logs for evidence of hacking attempts etc. or other unusual behaviour.Audit security using other checks too.Check SQL and Event LogsReact to issues in SQL logs - such as allocating space, detecting failing hardwareInstall software patches / service packsTests 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 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-09-28 : 15:31:07
|
| "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. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-28 : 15:43:26
|
| Thanks MichaelP, good point about test-restores of backups. |
|
|
|
|
|
|
|
|
DBA Administration Tasks