Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

Importing data

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.

EDIT:

I should also point out that there is no test or dev environment for this either...

share|improve this question
1  
Welcome to PMDW hell. Can you expand on the nature of the database, business or the reports? If there are just 5-10 tables that matter in this Poor Man's Data Warehouse and the source system is in good shape, could you Build a Cube from the Operational System? – Mark Storey-Smith Sep 25 '12 at 22:28
@Mark Thanks, it is nice and warm here. Although the bulk of the data exists in the 5-10 tables there are other tables that do contain reporting information. (probably around an additional 10-15 tables) If the data was transformed in to Fact & Dimensions I should think this could be reduced significantly. I can't access the production database directly. (The import process picks up the data from a SFTP site). I wouldn't want to create a cube on this data as it stands as I don't believe all of it is correct. – Barry Sep 26 '12 at 6:15

1 Answer

up vote 0 down vote

I'd recommend:

  • Know your DR strategy; hopefully there is one and be sure to run DBCC CheckDB frequently, and consider change control so you know what you changed when.
  • Configure perfmon to record basic counters - % processor time, buffer cache hit ratio, page life expectancy, user connections, disk i/o, batch request/sec.
  • Enable auditing for failed and successful logins
  • Setup server side trace to record sql activity so you can get a general idea of who is using the server for what. This is a good start, in your case, the Long Running Query template is probably a good place to start; http://msdn.microsoft.com/en-us/library/ff650699.aspx
  • Consider running this script: http://www.brentozar.com/blitz/
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.