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.
- There are approximately 30 SQL Server instances that contain a total of around 250 databases.
- The system as a whole consists of hundreds one off programs that created individual databases and UI's, reports etc..
- 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.
- There are about 10 core types of data mixed into this surrounding 10 major applications.
- The published data is relatively static needing monthly pushes to the release version of the data.
The following approach is currently being researched:
- Create a new database that will house all data into normalized structures
- Create SSIS packages to move the data up to the new database.
- 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.
- Over time rewrite applications using the new data model and retire the associated SSIS packages.
- The proposed structure of the new database ( per the data architect ) would create several tables that have upwards of 10 billion rows each.
- 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.
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.