If you are a regular Azure VM Image Gallery visitor like me, probably you noticed that we recently introduced a new image called “SQL Server 2012 SP1 for data warehousing on WS 2012”:
That’s really nice, I can deploy in few minutes a fully configured Azure VM as dictated by “Fast Track” architecture best practices! For whom that are not familiar with “Fast Track”, see the link below:
Fast Track Data Warehouse Reference Guide for SQL Server 2012
In short, Microsoft SQL Server 2012 Fast Track is a reference architecture data warehouse solution giving you a step-by-step guide to build a balanced hardware configuration and the exact software setup; in addition to that it provides step-by-step instructions on installing thesoftware and all the specific settings to configure for SQL Server and OS, including disk layout for optimal performances. This is well-known in the on-premise world, and also drives to acquisition and configuration of server hardware, in this case it’s not needed since we are in the Cloud, but specific knowledge is used, and applied, to maximize Azure investment returns.
NOTE: At the moment I don’t see any SQL Server 2014, but I’m sure it will come in once it will be officially released later next year.
As you can read in the side description, this image is optimized for data warehouse up to 400GB and, by default uses an “A6” VM size with 4 CORES and 28GB of RAM:
It’s interesting to note that the recommended size is already reported in the dialog as the default choice, this is what “Fast Track” reference architecture recommend, but you can easily change the VM size and use a bigger “A7” or, eventually, use a smaller one, even if notrecommended.
Hint: When you will be required to insert the Azure storage account to use for the VM creation, I recommend you to use a new one with “Geo-Replication” disabled to save 30% of costs. Why? The reason is simple: since the final VM configuration will use a Windows Server2012 Storage Space, to spread the database data and log files over multiple disks (by default = 4 for A6 size) in striping mode, the asynchronous “Geo-Replication” storage mechanism cannot ensure strict ordering in writing to multiple blobs, then potentially impairing the physical integrity of the database (WAL requirement violation).
For more information on Windows Server 2012 Storage Spaces, see the link below:
Storage Spaces Overview
The remaining part of the configuration does not present anything special, but I want to bring your attention on something that it’s already enabled in Azure VM creation since few months ago:
As you can see, remote Power Shell is enabled by default (and should remain as it is), and this is the key to the fully automated process that will run over the VM after Azure provisioning.
If you want to see a step-by-step procedure, please refer to Buck Woody’s blog post below:
Windows Azure Data Series: SQL Server Fast-Track Data Warehouse (FTDW)
Now, after Azure will complete the provisioning of the VM after few minutes, the work is not done, since the “Fast Track” automated configuration process still have to happen; here you have two choices, described below:
SQL Server Data Warehousing in Windows Azure Virtual Machines
The Power Shell script is really nice, since it will take care of (almost) everything:
Since the script will not do it automatically, if you use Power Shell instead of the Azure Portal, remember to:
Please be aware that the final image will be a standalone server in a workgroup, if you want/need to be a part of a Windows Active Directory Domain, you have to:
Once completed one of the two alternatives above, only the first part of the process is finished, that is the Provisioning phase, a second phase for post-configuration (including SQL Server) is required. It’s interesting to note that this will happen entirely inside the VM by an automated mechanism that is based on a Power Shell script:
As you can see, the creators of this image inserted a schedule task on the guest OS to be ran at system startup and consists of two steps:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -File "C:\Program Files\Microsoft SQL Server\DwIaas\ConfigDwIaaSVM.ps1"
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\setup.exe /ACTION=CONFIGUREIMAGE /IACCEPTSQLSERVERLICENSETERMS /INSTANCENAME="MSSQLSERVER" /Q
IMPORTANT: Do not try to modify that Power Shell script since it’s digitally signed, otherwise execution will fail.
To be honest, there is another trigger action in the scheduled task, but I don’t understand the utility of this EventID 1003:
Now it’s time to give a look to the Power Shell script contained in the folder “C:\Program Files\Microsoft SQL Server\DwIaas”:
IMPORTANT: This is not the list of maximum number of disks supported by Azure VM based on size, it is simply the number of disks recommended by “Fast Track” reference architecture.
…..Waiting on new EMTPY disks size 1023 GB to be attached..., 0 out of 5 are Attached…..
Waiting on new EMTPY disks size 1023 GB to be attached..., 0 out of 5 are Attached
Waiting on new EMTPY disks size 1023 GB to be attached..., 1 out of 5 are Attached
Waiting on new EMTPY disks size 1023 GB to be attached..., 2 out of 5 are Attached
Waiting on new EMTPY disks size 1023 GB to be attached..., 3 out of 5 are Attached
Waiting on new EMTPY disks size 1023 GB to be attached..., 4 out of 5 are Attached
Waiting on new EMTPY disks size 1023 GB to be attached..., 5 out of 5 are Attached
Create new storage pool DwIaasData
Create new virtual disk DwIaasData
Format virtual disk DwIaasData
Create new storage pool DwIaasLog
Create new virtual disk DwIaasLog
Configure SQL Server 'max server memory (MB)' to 26378;
Add startup flag -T1117;
Add “-E” flag to the startup parameter;
Alter default MODEL database recovery model to “SIMPLE”;
Add default locations for data and log files under “C:\Mount\Data” and “C:\Mount\Log”;
Modify the TEMPDB files location to “C:\Mount\Data\” and “C:\Mount\Data\”;
Add more TEMPDB data files in order to be equals to the number of cores (4 for A6 VM size);
Change the TEMPDB data file size to 25GB each, with auto-growth of 1GB;
Now, let’s spend me some words on some interesting configuration parameters shown above:
SQL Server 2008 Trace Flag -T 1117: http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/sql-server-2008-trace-flag-t-1117.aspx
Focus on Fast Track : Understanding the –E Startup: Parameter: http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/04/25/focus-on-fast-track-understanding-the-e-startup-parameter.aspx
File allocation extension in SQL Server 2000 (64-bit) and SQL Server 2005: http://support.microsoft.com/kb/329526
At the end, you will find the report of the installation in a text file called “Summary.txt” under “C:\Program Files\Microsoft SQL Server\DwIaas\Log”. If you are curious about what different optimizations and configuration values are used for different VM sizes, give a look to the XML file “DwIaasConfigGeneral.xml” under “C:\Program Files\Microsoft SQL Server\DwIaas”.
Regarding the SQL Server setup, be aware that *all* SQL Server services will be installed and started automatically: SQL Server database engine, Fulltext, Reporting Services, Integration Services and Analysis Services! If you don’t need some of them, stop and disable. Note that SQL Agent and SQL Browser are stopped. Finally, before using this VM image, be sure to read the recommendations contained in section “Performance Recommendations” at the following link:
SQL Server Data Warehousing in Windows Azure Virtual Machines
That’s all folks!