Take the 2-minute tour ×
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.

Hello and thank you for reading my post. I work for a company that houses many gigabytes of data in SQL Server. The company is in the process of taking a huge step forward to re-architect and re-organize this data.

The following facts can be summarized about the environment.

  1. There are approximately 30 SQL Server instances that contain a total of around 250 databases.
  2. The system as a whole consists of hundreds one off programs that created individual databases and UI's, reports etc..
  3. As time went along each new database started to need data from the other databases and linked servers were created making these all intertwined creating a data path that is difficult to decode and maintain.
  4. There are about 10 core types of data mixed into this surrounding 10 major applications.
  5. The published data is relatively static needing monthly pushes to the release version of the data.


The following approach is currently being researched:

  1. Create a new database that will house all data into normalized structures
  2. Create SSIS packages to move the data up to the new database.
  3. Create another set of SSIS packages to maintain the data by finding the best path for the data and doing incremental updates to the new database.
  4. Over time rewrite applications using the new data model and retire the associated SSIS packages.
  5. The proposed structure of the new database ( per the data architect ) would create several tables that have upwards of 10 billion rows each.
  6. With this proposed model it would require many joins between these huge tables and others. One query could join up to 15-20 tables. These will all be delivered to a website front end with Web API Restful services where the business requires a 1 second response.

Questions

Will SQL possibly be able to live up to this performance wise? Those huge tables will have a small record size consisting of about 8 fields that are integers and 2 text fields.

Should the company be looking at more of a big data solution like Hadoop? Where the architecture looks more appropriate there is no internal knowledge of anything except SQL Server which is version 2012.

Thank you for any insight you are able to provide.

share|improve this question

closed as too broad by Jon Seigel, Mark Storey-Smith, dezso, RolandoMySQLDBA, Kin Oct 9 '13 at 20:32

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
This is an interesting question, but you need -- literally -- an entire team of people to analyze the requirements of this complex system. To document the existing system would be hundreds of pages. I don't think we can give you any kind of reasonable answer in this format. –  Jon Seigel Oct 9 '13 at 1:06
    
I appreciate your response. There is a small team of professionals tasked with different aspects of the system. The main objective of my post was to spark some similarity with other VLDB implementations. More specifically I posed the question to the potential performance issues with joining tables with that many rows. At this point I am doing a feasibility study as this is extremely complex. –  user29167 Oct 9 '13 at 1:18
    
VLDB? Since when? Sorry, 10 billion small rows are not VLDB these days when servers can have half a terabyte of ram and more. –  TomTom Oct 9 '13 at 5:12
    
What about the approach that is being researched is better than what they have at the moment? Linked server databases don't justify rewriting every application in the entire business. This question is not very specific. –  sa555 Oct 9 '13 at 8:40
1  
Many gigabytes? So, if you buy a 1TB blade it all fits into memory? Why would you prefer distributed file system over in-memory DB? –  Damir Sudarevic Oct 9 '13 at 11:52