SQL TIPS: MSSQL DBA Check List
08SEP
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
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
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
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+)
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
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