August 27, 2007 at 6:19 am
I work in a very small IT shop (3 people) that uses SQL Server 2005 Standard Edition. We are responsible for producing reports for the company. I would not consider our databases critical to the operation of the company, with the exception of possibly 1 or 2 of them.
Two of us in the group are database software developers, but we are fairly new to SQL Server. We are not DBAs by any stretch of the imagination and we have no DBA support for SQL Server.
I'm looking for advice from some DBAs on what database tasks we might want to perform (e.g., check log files?) on a regular basis to ensure the health of SQL Server.
What I want to prevent is a situation where a database or SQL Server is not available because we were not aware of a growing problem that finally caused a database to become unavailable.
Also, can someone help us on a simple backup strategy? I don't believe there is any need for transactional backups, and that one backup at the end of the day would be sufficient.
Thanks.
August 27, 2007 at 8:27 am
Backups - Use a maintenance plan. I'd set up daily or weekly full backups and then log backups every hour or so for critical databases, maybe daily on others. You'll need to test this a bit to get an idea of disk space needs. Better to have log backups and not need them than the other way around.
Disk space - Be sure you have enough. Preferably < 1/2 the total. Use autogrow for databases, but watch the space free/used inside the database. You don't want auto-grows except in an emergency. Instead make periodic adjustments (quarterly or so) to handle the space you need. This is where many DBAs have issues when they run out of space.
Benchmark - Do a monthly benchmark of the server performance to start, then maybe move to every 4-6 months. Get an idea of CPU, often run queries' performance, time to return data, etc.
DO NOT SHRINK THE DB.
Run the optimization and indexing jobs weekly. You might need them more often, but you've have to analyze the changes in various tables to be sure. Go with the defaults for now. If there are DBCC errors (be sure DBCC CHECKDB is run), call PSS. Don't mess around with critical data.
Most DBAs don't recommend Raid 5. Get on R1 or R10. Watch for hardware failures. Can't always predict them, but if you lose a drive, you should know about it.
Patch, but be sure you test the SQL patches on your apps before moving them to production.
August 27, 2007 at 11:10 am
Thanks for your advice, Steve. It's much appreciated!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply