May 25, 2012 at 9:39 am
We currently have a EDW that is around 800 GB. It is running on a VM (16GB, 4core). We are anticipating an additional 4 TB of data coming in in the next couple of months. I realize one of the key factors would be the design, but assuming the design is right,
1. Would SQL Server be able to handle 4TB of data? (We would actually have two copies of the database with 2TB each. One would be active while we refresh the second one every week and alternate the active DB).
2. What kind of hardware is optimal to handle databases this size.
I understand that there is no one straight forward answers to these questions. I am more looking for your personal experience and also if you could point me to any resources online that would help me educate myself to arrive at the right solution.
Thanks again for your time
May 25, 2012 at 2:16 pm
You mention design and I think that's where you need to start when working with a VLDB. I administer multiple TB+ databases in my environment and the key is understanding the usage profile of your database/application and building a design to support it from day one.
The database I have in mind is 2TB with a weekly growth rate of ~50GB. We designed this database with an active/inactive/archive model in mind; namely, active data is one one drive, inactive data is on a separate drive, and archived data is on yet another drive. This is built on top of the horizontal partitioning feature available in SQL Server and uses a number of custom stored procedures to facilitate movement of data based on application requirements.
This is mostly seamless aside from the data file movement (done during off-hours on scheduled maintenance days) and provides a scalable (and most importantly from our perspective, transparent) method of balancing server performance with application requirements.
Obviously hardware is important but you'd be surprised how far good design gets you. We run this (and several similar) database applications on VMs similar to the one you described (albeit with slightly more RAM) and are able to meet all SLAs in terms of performance, downtime, and deliverables.
Long story short yes, SQL Server can handle large amounts of data, but you need to spend the up-front time on database and application design to avoid pitfalls down the road.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply