Although real development project work is only in the planning stage, I've achieved a lot since September -
Fixing the obvious -
- Implementing Backups (that's right, there were none!)
- Addressing Index Fragmentation with scripts.
- Fixing massive file fragmentation of live databases (damn that autogrowth setting)
- Getting to grips with virtualisation in a production environment.
- Virtualised SQL Server 2005 on Microsoft’s Hyper-V platform.
- Building my own 13 VM network for the datawarehouse on 4 physical hosts.
- Building a Windows 2008 domain.
- Fun with Group Policy (controlling RDP, Windows Firewall, Windows Updates).
- Implementing VAMT for Product Activation, Windows Updates and firewalls.
- Rewriting worst performing front end sql queries in parameterised stored procedures.
- Improving indexes using the Database Tuning Advisor.
- Reviewed existing systems.
The core product database has suffered from being developed quickly by 1 person.
It's major faults are >
- Passwords stored in PHP code. - Not an easily configurable location, code not portable.
- Application code granted excess permissions - This is due to TRUNCATE present in code.
- Import and Reporting systems sharing the same server.
Statistics show disk activity is 99% write, 1% read.
- Cross db ownership - Live code accesses both application and import database using 3 part object naming i.e. db_name.schema_name.object_name.
- Nested views! - Views that are built from views, that are built from views etc... (Inefficient , difficult to debug, difficult to optimize).
- 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),
- Poor table design - Many tables have lots of columns. Databases badly need some normalization.