Join Martin Guidry for an in-depth discussion in this video Data warehouse reference architectures, part of Implementing a Data Warehouse with Microsoft SQL Server 2012.
Now I would like to talk about a tool from Microsoft, used to help us determine the hardware needs of a particular data warehouse. It's called the Fast Track Data Warehouse Sizing tool, fast track data warehouse is often abbreviated down to FTDW. So, if you go to your favorite search engine and search for FTDW sizing tool. You should be able to find it at the Microsoft Download Center. The tool is an Excel spreadsheet that will ask you some questions about your data warehouse.
The number of users. The types of queries they are running. The amount of data you plan to handle and a few other things. You can come in and adjust any of these values. Adjust to whatever is appropriate for your environment. And then, when you scroll all the way down, you get some recommendations from Microsoft. They're recommending 128 CPUs for me. They're recommending a total data storage of 63 terabytes, and then they're going to make some recommendations about the IO, the input and output.
So, they don't recommend a specific type of disk, or a specific number of disk, nor a specific disk subsystem. They instead recommend that I need some disk subsystem. That will handle 25,600 megabytes per second. And then, obviously, I could go to different disk vendors and see who has a system that can handle that. This is a very nice tool. You should definitely take these numbers into consideration when planning your data warehouse. But personally, I wouldn't just accept them blindly.
You probably want to do some of your own testing. To make sure that these numbers will work for your situation. If you're interested in more detail, with how they came up with these numbers, there is a detailed document about that. They call it a reference architecture. Microsoft publishes a recommended architecture for data warehouse considerations. Here, in the reference guides, I will click on SQL server 2012 fast track data warehouse reference guide. It's about an 85 page word document that goes into great detail on recommended architectures for SQL server and most of this information is used as an input into the Excel spreadsheet that automatically makes recommendations for you.
- Typical databases vs. vdata warehouses
- Choosing between star and snowflake design schemas
- Working with appliances and "big data"
- Exploring source data
- Implementing data flow
- Debugging an SSIS package
- Extracting and loading modified data
- Enforcing data quality
- Consuming data in a data warehouse