08 Sep

Under the covers of MSSQL are these roles: sysadmin, datareader, datawriter, security admin, backupadmin etc. In larger organizations there should be three different people with these jobs

1. sysadmin – all access vip, often has windows admin and disk subsystem access as well
2. dbo – vip but only for a specific databases and not system level
3. developer/user – read and/or write to specific objects

Only level 1 qualifies you as a dba. Some dbas don’t know how to function at level 2 and 3. This is ok but not advised. DBAs need some level of automation skills and should be able to relate to developers. They also need to understand why the database structure is the number reason for performance problems.

Regular MSSQL DBA task include:

1. making sure backups are happening
2. monitoring performance
3. monitoring disk space
4. making sure index maintenance is happening
5. setting up new instances
6. consolidating instances

Two more that could fall under either dba or dbo job are

7. making security changes
8. making schema changes

Internally written databases cause more security and schema changes. With internally developed databases also comes maintaining test and qa instances.

Active directory integration will make security updates less frequent. Its a good idea to have a network login decide weather or not a user has access to the data. The network credentials are securely stored and passed to the database that way to avoid having to store plain text passwords. Its called windows authentication or integrated security. SQL authentication is sometimes easier to configure less secure. In the server properties you can turn off SQL authentication entirely.

After security and schema changes, users generally request changes to increase performance. These actions can be taken to increase performance at the system level.

1. more tempdb data files (1 per core)
2. set SQL max memory so the OS does not have to compete for RAM
3. put data files and log files on separate disks
4. put tempdb files on its own disks
5. set initial file sizes large enough so they do not autogrow
6. -E startup param for larger pages (4MB)
7. 1444 for equal file growth and fill
8. eliminate disk communication bottlenecks (upgrade HBAs and SAN switches)
9. turn on data compression and backup compression (SQL 2008+)

At the database level these actions can produce excellent results

1. decrease lock escalation
2. normalize tables
3. remove unused indexes
4. add needed indexes
5. rebuild or reorganize indexes
6. switch to simple recovery model
7. do not auto-close
8. backup->restore->and grow dbs that might have become fragmented at disk level

Leave a comment

Posted by on September 8, 2010 in SQL Admin


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: