Best practice for creating SQL Server databases for a data warehouse
I'm about to create 2 new SQL Server databases for our开发者_运维技巧 data warehouse:
- Datawarehouse - where the data is stored
- Datawarehouse_Stage - where the ETL is done
I'm expecting both databases to be able 30GB and grow about 5GB per year. They probably will not get bigger than 80GB (when we'll start to archive).
I'm trying to decide what settings I should use when creating these databases:
- what should the initial size be?
- ...and should I increase the database size straight after creating it?
- what should the auto-growth settings be?
I'm after any best practice advice on creating those databases.
UPDATE: the reason I suggest increasing the database size straight after creating it, because you can't shrink a database to less than its initial size.
•what should the initial size be?
45gb? 30 + 3 years grow, especially given that this fits on a LOW END CHEAP SSD DISC ;) Sizing is not an issue if your smallest SSD is 64GB.
...and should I increase the database size straight after creating it?
That would sort of be stupid, or? I mean, why create a db with a small size jsut to resize IMMEDIEATLEY after, instead of putting the right size into the script in the first step.
what should the auto-growth settings be?
This is not a data warehouse question. NO AUTOGROW. Autogrow fragemnts your discs.
Make sure you format the discs according to best practices (64kb node size, aligned partitions).
精彩评论