May 25, 2005

SQL Server ideas from 2072A class

I sent this email to team-members of the Helpdesk/Inventory Project on Monday, after my first day of the Microsoft 2072A class on SQL Server 2000 Administration, taught by Jim Ferguson at New Horizons in Edina:

We just finished my first day of SQL Server class and I wanted to share some ideas about our Asset Nav server config to come. Please share any differences of opinion on this:

1. Use *hardware* RAID controls to create
three partitions: WINDOWS, DATA, TEMP;
Make WINDOWS at least 10-20 gig for all
anticipated patches etc., TEMP 5-10 gig
for all logs (only), the rest to DATA -
not just for Asset Nav but possibly for
other future databases, just in case

2. Install Win03 plus SQL Server on WINDOWS
(standalone, standard) and patch it all.
We should plan/discuss authentication
methods and administrative passwords

3. Setup all new dbs (just AN now) on DATA
partition, separate from the executables.

4. Setup the AN executables on C: (defaults)
and tweak IIS and Win security as needed

5. Use SQL Server Enterprise Manager to set
restrictions on how big the database can
grow (maybe the whole D: drive minus Y)
and where log files land (the E: drive)

6. Setup autoshrink to have SQL Server make
its own decisions about how and when to
"compact" things (sort of like defrags).
Microsoft claims that users won't see a
performance hit. We can always disable it

7. Periodically defrag partition WINDOWS but
almost never DATA - SQL Server can do its
own reallocations for database(s) on D:
and sometimes fragmentation isn't such a
terrible thing with something as random-
access as the typical OLTP database.

8. Periodically cleanup/backup the log files
or they may eventually fill that E: drive

I'm sure other thoughts will emerge over the
coming days, e.g. about backups and upgrades,
but I wanted your feedback on this before we
get the new server hardware and do anything
about allocating drive space or installing. I
think these tweaks will help performance over
time and help us avoid problems downthe road.

-Brad


P.S. Good New Horizons class. The instructor,
Jim Ferguson, obviously knows his stuff and
holds about every Microsoft advanced cert you
can get. Gave a tip or two on what the book
says vs. what his real-world experience was.

Posted by tapli005 at May 25, 2005 8:11 AM