March 5, 2007 at 8:49 am
Does anyone have experience in setting up disk layout for data warehouse? How many partitions do we need to create? Is it just 2? 1 for windows and 1 for sql server 2005? What would be the best practices in terms of putting fact and dimension tables? Indexes? Do I just put everything into primary? How about log files where should I put them? Into primary also? Please help. Any suggestion would be greatly appreciated. Thanks in advance.
March 6, 2007 at 3:07 am
There are many things that can be done, but you need to start from the DW requirements documentation and potential SLAs. This should tell you more about how much data you are likely to store, the rate of growth, the number of enquiry users, and expected access times.
When you know what you are aiming for in business terms, you can begin to design your server environment.
If the DW is going to be small (under 100 GB) then you can probably design the server after looking at best practice suggestions on the net. Consider things like a) separate filegroups on separate disks for dimension and fact data,also for aggregates; b) separate the main DW, staging and tempdb on separate disks; c) Run it on a 64-bit server to make best use of memory. etc, etc.
If it is going to be larger than about 100 GB, then there will be enough budget to get professional help on this. If you do not have an established business partner who can help on this, it is worth contacting Conchango. They have some of the best SQL Server DW people in the business working for them, and did a good job on our DW.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 6, 2007 at 5:33 am
You want to separate your data & log files from your OS. We have a raid 10 for our data files & raid 1 for our log files. We also have our OS on raid 1. Actually we have 2 raid 10 for our data files. We put our largest & most active database on its own raid 10 & everything else on the other raid 10. We haven't gone too deep with filegroups. We have all the system databases on their own filegroup & create a user filegroup for anything we create. We have had very good performance & reliability with this configuration.
Obviously it all depends on your needs & funds available.
Hope this helps....
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply