For anyone that is thinking about selecting the Data Warehouse edition of SQL Server 2014, I just want to highlight a few things required to install this product and get it up and running.
First off though, what is SQL 2014 DataWarehousing Edition? In short, it is a version of SQL server that is available as an image on an Azure VM, the product seems to be flying a little bit under the radar. In terms of licensing and features, it is closest to Enterprise Edition and is similar to BI Edition. It houses the full stack of BI products, and it also allows for database snapshots like Enterprise Edition. The biggest single difference I can find is that it is optimized to use Azure Storage in the cloud-interesting no? I see its primary purpose as replacing an existing on premise data warehouse, or to function as a starting point for a new data warehouse that will be fairly large.
I won’t go into provisioning a cloud VM in this blog, but if you want more info here’s a link:
http://msdn.microsoft.com/library/dn387396.aspx
Ok, on to some tech points and what to expect:
First and foremost, this edition’s minimum recommended VM size is an A7-whoah!
Pretty steep for a minimum spec, A7 is 8 cores with 56 GBs of RAM. We all know minimum specs are just that, the bare minimum, and usually we end up going larger.
If you are unfamiliar with Azure VM sizing take a look here:
http://msdn.microsoft.com/en-us/library/azure/dn197896.aspx
Second, even to do a basic install, it is going to require that you have several 1 Terabyte Storage locations available for it to harness in Azure Storage. Double Whoah!
When you first login to this VM, you will not be able to connect SSMS to the SQL instance. Instead you are prompted to configure Storage containers for SQL 2014 DW Edition. This can be done in the Portal, or it can be done via Azure PowerShell and is documented quite well here:
http://msdn.microsoft.com/library/dn387397.aspx
In a nutshell it is quite easy to attach the disks through the Portal Application on Azure, you just browse to your VM and click “Attach” at the bottom of the screen. The VM will reboot, and you can then confirm the process in the logs listed in the link above. But as I mentioned earlier, you will know when it is up and running because you will get a login error from SSMS if it is not properly setup. One thing to keep in mind is that LUNS are numbered 0-X not 1-X, I made this mistake when I first read the log and thinking it was complete when I still needed to attach one more disk.
Once you have configured the appropriate number of storage LUNS, you must then use Disk Manager in Windows to format and label them – E: , F:, G:, etc.
Once the SQL Instance finds its required number of storage containers, it will then and only then, allow you to login via SSMS.
So what is going on here? Well, some good stuff in my opinion.
- It is forcing the end user to appropriate several disk locations instead of just using the default c:\ drive to store everything. This is a great optimization because it will spread the disk activity out over multiple LUNS. It also enforces separating the data files from the operating system disk and the page files. Think about how many database systems you have worked on that have this design flaw-a lot of them.
- It is assuming you mean business and it requires a massive amount of storage up front to even install it. Either you need this edition of SQL Server or you don’t. This is not SQL Express or a departmental application server, this is a full size enterprise application that is capable of migrating an on premise DW to Azure.
Even though one might be put off a bit that it requires 4+ terabytes of storage to install, I actually like the fact that it enforces good design and automatically gives some overhead for growth.
No hardware budget excuses this time, a very important point, is that even though it requires you to appropriate 4+ TB’s of storage, YOU ARE NOT BILLED FOR THE STORAGE YOU APPROPRIATE, you are only billed for the storage that you actually fill with data.
Once you understand that, this product starts making more sense. You can design a large storage location, with plenty of room to grow, without having to buy a large storage location. In a traditional on premise environment, this could mean forking over some major cash. If you have never noticed, SANs are not inexpensive, and they take a long time to arrive onsite!
In summary, I am glad that this product is designed the way it is. It enforces good design from the beginning. It is not the correct product for a lot of different applications due to its scale, but for the person or place that wants to migrate or build a true Enterprise size data warehouse in Azure, SQL 2014 DW Edition is perfect.
This was a great read, thanks Greg!