Last weekend I visited New York City (NY) and Edison (NJ) to attend database architecture meeting with a big environmental technology firm. It was very interesting to meet CEO and few of the lead database administrators. Lots of database related things were discussed.
1) Database Backup
a. Schedule Backup Routines
b. Backup medium and redundancy
c. SQL Server 2005 T-SQL improvement with regards to backup
2) Index Optimization
a. Implementing Primary Key on non-standard columns for improving performance in certain cases
b. Index statistic optimization
3) Maintenance Tasks
a. Index Re-building
b. Database Consistency Check alerts
a. Snapshot replication optimization (use of new copy database feature)
b. Log-shipping failover set up
5) Architecture Designs
a. Setup Replications over database mirroring
b. Database Normalization advantages and performance
c. TempDB optimization
d. Datafile (.mdf) and Logfile (.ldf) filegroup placement
e. Foreign key relations
f. Re-factoring database schemas based on statistics collected from profiler
g. Analysis of hardware (disk, CPU, connections etc)
f. Security of Database
6) System Tables
a. Use of system tables and views to retrieve system related information
It was really interesting to meet all prominent personality of biggest technology firm in one room. I was provided tour of their laboratory and was provided admin privileges to their complete database farm (yes it is really farm, not few computers) for the days, I was in their facility. CEO invited me to their home and I had most delicious home made food in US.
I just received email from CEO that they see performance improvement after implementing few suggestions and their backup is safe on redundant disks. They also restored them with point in the time restoration of logs.
I wonder how many companies have proper backup system (tested with restoring data frequently).
Reference : Pinal Dave (https://blog.sqlauthority.com)