While poking around in Azure, looking to set up a BI Demo VM , I noticed that Microsoft had added a few SQL Server-oriented images to their catalog. VMs labeled “SQL Server… for Data Warehousing”!
There was one for SQL Server 2012 (SQL Server 2012 SP1 for Data Warehousing on WS 2012) and one for the current CTP2 version of SQL Server 2014 (SQL Server 2014 CTP2 Evaluation for Data Warehousing on WS 2012)!
My curiosity piqued, I ran (figuratively) to the Bings, and sure enough! There it was: confirmation, including some nice guidelines on configuration of VMs for DW purposes.
My favorite factoids:
- Use an A6 VM for the SQL 2012 image, an A7 for 2014. (This was well-timed for me because I was about to put the 2014 on an A6…)
- Use page compression for Data Warehouses up to 400GB
- Use one file per filegroup for best throughput (this prevents multilevel disk striping), and for Data Warehouses under 1GB you should need only one filegroup
- However, you can look at using multiple file groups to store staging data separately from production, to separate low-latency data from high-latency, to run parallel data loads, and more.
Just be aware that the SQL Server 2014 Azure image will expire at the beginning of August, as that product moves along the path to RTM.
