2008

4 months ago, I changed Job, seeking more a role with more sql development rather than focusing on administration. The dba role is interpreted very differently by smaller companies and as I wanted a more challenging environment I moved on.

Although real development project work is only in the planning stage, I've achieved a lot since September -

Fixing the obvious -

1. Implementing Backups (that's right, there were none!)
2. Addressing Index Fragmentation with scripts.
3. Fixing massive file fragmentation of live databases (damn that autogrowth setting)

Virtualisation -

1. Getting to grips with virtualisation in a production environment.
2. Virtualised SQL Server 2005 on Microsoft’s Hyper-V platform.

Networking -

1. Building my own 13 VM network for the datawarehouse on 4 physical hosts.
2. Building a Windows 2008 domain. 
3. Fun with Group Policy (controlling RDP, Windows Firewall, Windows Updates).
4. Implementing VAMT for Product Activation, Windows Updates and firewalls.

Development -

1. Rewriting worst performing front end sql queries in parameterised stored procedures.
2. Improving indexes using the Database Tuning Advisor.
3. Reviewed existing systems.

Existing systems -

The core product database has suffered from being developed quickly by 1 person.
It's major faults are >

1. Passwords stored in PHP code. - Not an easily configurable location, code not portable.
2. Application code granted excess permissions - This is due to TRUNCATE present in code.
3. Import and Reporting systems sharing the same server.
   Statistics show disk activity is 99% write, 1% read.
4. Cross db ownership - Live code accesses both application and import database using 3 part object naming i.e. db_name.schema_name.object_name.
5. Nested views! - Views that are built from views, that are built from views etc...  (Inefficient , difficult to debug, difficult to optimize).
6. Adhoc queries - SELECT statements embedded in php code where stored procedures would increase simplicity (for web developer), ensure query parameterization (for performance) and increase security (reduce chances of an injection attack),
7. Poor table design - Many tables have lots of columns. Databases badly need some normalization.