I have recently started a new job and part of my remit is to try to rescue the Management Information (MI) Data Warehouse. I use the term Data Warehouse very loosely here!
The server setup is:
- Windows Server 2008
- MS SQL Server 2008 R2 Standard Edition
The disks split in to 3 drives:
C: (OS) & System Databases
D: Log Files (Approx 120GB Free)
E: Data Files (Approx 1.3TB Free)
These are the observations I have made regarding the database:
- The schema is exactly the same as the equivalent production database schema. So rather than extract & transform the data in to a Data Warehouse style schema it is basically a copy of production that is used for reporting.
- The tables do not have any indexes, primary keys or foreign keys.
- Most information required for reporting is located in 5-10 core tables. These tables range from 10-15 million rows to 1.6 billion rows.
- There are no stored procedures or views. All reports use embedded SQL (most of these reports are Excel docs.)
- I have found some tables that contain duplicate rows, that shouldn't be there.
- There are hundreds of logins with access that no one seems to know who they are or what they need them for.
The data is imported using batch files and OSQL. It is slow, clunky and prone to failure (It has failed 4 times and I have only been there for 2 and half weeks) The logging is also poor.
So apart from all that, it is perfect...
I need to find a way to fight my way out of this mess but I am not sure how to go about it.
Ideally, I want to be able to:
- migrate the import process to use staging tables and SSIS
- modify the existing schema to include referential integrity
- create indexes to improve performance
- create the reports in SSRS instead of random Excel docs dotted around
- lock down the access (or at least perform some sort of audit)
The main issue at the moment is the performance. I have created a new filegroup on drive D: (where the log files are stored) and placed a few non clustered indexes on there. I am being slightly cautious as I don't want to increase the import times as these are taking too long as it is!
I wanted to partition the larger tables but partitioning is not included in Standard, it is an Enterprise feature.
I realise that this is a pretty huge task and I am not looking for a magic fix but a little guidance on how to attack this would be a great help.
I should also point out that there is no test or dev environment for this either...