{"id":5080,"date":"2015-04-30T19:03:04","date_gmt":"2015-04-30T18:03:04","guid":{"rendered":"http:\/\/andreas-wolter.com\/sql-server-in-microsoft-azure-wie-man-durch-flexibilitaet-leistung-gewinnt-und-zugleich-kosten-spart\/"},"modified":"2017-12-01T19:14:46","modified_gmt":"2017-12-01T18:14:46","slug":"sql-server-in-microsoft-azure-how-to-gain-performance-by-flexibility-and-save-costs-at-the-same-time","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/sql-server-in-microsoft-azure-how-to-gain-performance-by-flexibility-and-save-costs-at-the-same-time\/","title":{"rendered":"SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-18cf83952c87ad6aef0f9462672cbe3b\">\n#top .av-special-heading.av-av_heading-18cf83952c87ad6aef0f9462672cbe3b{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-18cf83952c87ad6aef0f9462672cbe3b .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-18cf83952c87ad6aef0f9462672cbe3b .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-18cf83952c87ad6aef0f9462672cbe3b av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >SQL Server in Microsoft Azure: How to gain performance by flexibility and save costs at the same time<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>This is the first time that I am tackling Microsoft Azure in an article. (Microsoft.com: <a href=\"http:\/\/azure.microsoft.com\/en-us\/overview\/what-is-azure\/\" target=\"_blank\" rel=\"noopener\">What is Microsoft Azure?<\/a>)<\/p>\n<p>A plethora of <a href=\"http:\/\/azure.microsoft.com\/en-us\/services\/preview\/\" target=\"_blank\" rel=\"noopener\">services<\/a> is provided by Microsoft Azure by now, of which the hosting of virtual machines running on an SQL Server service is one. This is what we call <em>IaaS<\/em> (<em>Infrastructure as a Service<\/em>).<!--more--><\/p>\n<p>&#8211; Here, this service model will be explained in more detail and, among others, compared to the PaaS approach: <a href=\"http:\/\/blogs.msdn.com\/b\/hanuk\/archive\/2013\/12\/03\/which-windows-azure-cloud-architecture-paas-or-iaas.aspx\" target=\"_blank\" rel=\"noopener\">Which Windows Azure Cloud Architecture? PaaS or IaaS?<\/a><\/p>\n<p><u>&#8211; <\/u>A nice comparative graph is available in this blog article:<br \/>\n<a href=\"http:\/\/robertgreiner.com\/2014\/03\/windows-azure-iaas-paas-saas-overview\/\" target=\"_blank\" rel=\"noopener\">Windows Azure IaaS vs. PaaS vs. SaaS<\/a><\/p>\n<p>After determining that the concept IaaS makes sense for part of one\u2019s own environment, the issue of the <strong>SQL Server Systems configuration <\/strong>will be next. In the Azure Portal ready-made images are available that will facilitate access especially for starters.<br \/>\n&#8211; In fact, with only 7 clicks it is possible to set up a Windows Server including a licensed SQL Server when using a <strong>template from the gallery<\/strong>.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-417a3fa33135bcfd7d110d95c09ef0ed\">\n.avia-image-container.av-av_image-417a3fa33135bcfd7d110d95c09ef0ed img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-417a3fa33135bcfd7d110d95c09ef0ed .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-417a3fa33135bcfd7d110d95c09ef0ed av-styling- avia-align-center  avia-builder-el-2  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5055 avia-img-lazy-loading-not-5055 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM.png\" alt='' title='1504_Azure_Create_VM'  height=\"649\" width=\"1200\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM.png 1200w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM-600x325.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM-300x162.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM-768x415.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM-1030x557.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM-705x381.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Create_VM-450x243.png 450w\" sizes=\"(max-width: 1200px) 100vw, 1200px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>However, for the productive application of SQL Server some more efforts are required, as the standard templates merely contain a data disc in which the operating systems is located. \u2013 Yet there are several reasons as to why one should not run one\u2019s SQL Server here: data integrity and IO performance.<\/p>\n<p>Therefore, Microsoft also provides so-called \u201coptimized\u201d images \u2013 for OLTP or OLAP scenarios \u2013 (highlighted by red frame in the screenshot) that immediately come with 15 more data discs making a total of 16.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-bc2815582047b86e785a9ea8dbbd7237\">\n.avia-image-container.av-av_image-bc2815582047b86e785a9ea8dbbd7237 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-bc2815582047b86e785a9ea8dbbd7237 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-bc2815582047b86e785a9ea8dbbd7237 av-styling- avia-align-center  avia-builder-el-4  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5064 avia-img-lazy-loading-not-5064 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server.png\" alt='' title='1504_Azure_VM_Selection_SQL_Server'  height=\"678\" width=\"1000\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server.png 1000w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server-600x407.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server-300x203.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server-768x521.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server-705x478.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Selection_SQL_Server-450x305.png 450w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>1) Option 1, the \u201ctraditional approach\u201d <\/strong>hence is: <strong>several data discs and a machine with corresponding support\/CPU power<\/strong>.<\/p>\n<p>The 15 data discs (additionally to the OS disc) in this case are pre-configured for SQL data and SQL logs in 2 storage pools of 12 and 3 discs each.<\/p>\n<p>The maximum IOPS also depend on the available CPU cores. One should not expect linear performance increases anyway.<\/p>\n<p><strong>The problem<\/strong> here is: <strong>You lose almost any flexibility<\/strong>, i.e. in terms of configuration level (<strong>performance<\/strong>) and ultimately also in terms of <strong>pricing<\/strong>.<\/p>\n<p>Because in order to use the total of 16 data discs it is necessary to consistently operate one of the 8-core VM sizes (A4, A7, A8, A9, D4, D13, D14, G3, G4 and G5). It is only possible to scale upwards.<\/p>\n<p>&#8211; If you need more than 16 additional data carriers you will have to apply a VM with 16 or 32 cores (G-series), which will enable you to apply up to 32 or 64 data discs besides the OS disc.<\/p>\n<p>In terms of pricing, this will definitely set the limit downwards.<\/p>\n<p>This, however, makes it more difficult to reach the <strong>goal of saving costs through cloud-based systems<\/strong>.<\/p>\n<p>Yet the great strength of the cloud-based approach is ideally only when requiring a specific performance (or service) to request and receive it, and when not needed, to not leave it idly \u201cactivated.\u201d For you only pay for what you \u201csubscribe\u201d to, which in this case is not necessarily what you actually use.<\/p>\n<p><strong>Our ideal system, thus, should be maximally scalable<\/strong>, both <strong>upwards and downwards<\/strong>.<\/p>\n<p>This will probably become clearer if you look at the charts with the current (status: 30 April 2015) <strong>virtual machines and their performance core sizes<\/strong>.<\/p>\n<p>At the moment, the standard tier comprises 3 series: A, D and G, with the G-series being that of the greatest power \u2013 and hence the most expensive.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-d4b6f7ea8117f10dd55f4168f978dd3e\">\n.avia-image-container.av-av_image-d4b6f7ea8117f10dd55f4168f978dd3e img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-d4b6f7ea8117f10dd55f4168f978dd3e .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-d4b6f7ea8117f10dd55f4168f978dd3e av-styling- avia-align-center  avia-builder-el-6  el_after_av_textblock  el_before_av_image '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5066 avia-img-lazy-loading-not-5066 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A.png\" alt='' title='1504_Azure_VM_Sizes_A'  height=\"274\" width=\"839\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A.png 839w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A-600x196.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A-300x98.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A-768x251.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A-705x230.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_A-450x147.png 450w\" sizes=\"(max-width: 839px) 100vw, 839px\" \/><\/div><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-d9c117ee8c3b23b9f30f036bd2383d9a\">\n.avia-image-container.av-av_image-d9c117ee8c3b23b9f30f036bd2383d9a img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-d9c117ee8c3b23b9f30f036bd2383d9a .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-d9c117ee8c3b23b9f30f036bd2383d9a av-styling- avia-align-center  avia-builder-el-7  el_after_av_image  el_before_av_image '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5068 avia-img-lazy-loading-not-5068 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D.png\" alt='' title='1504_Azure_VM_Sizes_D'  height=\"190\" width=\"839\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D.png 839w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D-600x136.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D-300x68.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D-768x174.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D-705x160.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_D-450x102.png 450w\" sizes=\"(max-width: 839px) 100vw, 839px\" \/><\/div><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-a8de77e547a2d7095b85b7644feb175b\">\n.avia-image-container.av-av_image-a8de77e547a2d7095b85b7644feb175b img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-a8de77e547a2d7095b85b7644feb175b .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-a8de77e547a2d7095b85b7644feb175b av-styling- avia-align-center  avia-builder-el-8  el_after_av_image  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5070 avia-img-lazy-loading-not-5070 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G.png\" alt='' title='1504_Azure_VM_Sizes_G'  height=\"127\" width=\"839\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G.png 839w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G-600x91.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G-300x45.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G-768x116.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G-705x107.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_VM_Sizes_G-450x68.png 450w\" sizes=\"(max-width: 839px) 100vw, 839px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>You can see the number <strong>of dedicated CPU cores<\/strong>, the <strong>size of the working memory<\/strong>, the <strong>size of the temp disc<\/strong>, and, very important for the scalability: the <strong>number of the maximally permitted data carriers<\/strong> besides the OS disc itself.<\/p>\n<p>Per data disc you get up to 500 IOPS. To receive more IOPS, thus, you need more data discs \u2013 but also more CPU cores. However, when using a machine with 16 data discs it will hardly be possible to scale downwards in times of low utilization rates. An A2, for example, will thus be unreachable for some kind of minimum operation. If you need more data discs to be able to accommodate IO peaks you will restrict yourself further and will have to continually pay for the most expensive machines.<\/p>\n<p><strong>Are there any alternatives?<\/strong> How to be <strong>flexible in order to save costs<\/strong> and at the same time switch to higher machines (\u201c<strong>scale-up<\/strong>\u201d) if necessary, and on the other hand restrict your machines to minimal CPUs during nighttime or on weekends (\u201c<strong>scale-down<\/strong>\u201d)?<\/p>\n<p><strong>1) Saving data files directly on the Azure Blob Storage<\/strong><\/p>\n<p>The obvious advantage here is that data discs are not required \u2013 and these are what significantly limit scaling options. Instead of the data discs, the SQL Server database files are stored directly in the Blob storage.<\/p>\n<p>The database creation can look something like this:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-552c6f410e638accab426ea7630ee5dd\">\n.avia-image-container.av-av_image-552c6f410e638accab426ea7630ee5dd img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-552c6f410e638accab426ea7630ee5dd .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-552c6f410e638accab426ea7630ee5dd av-styling- avia-align-center  avia-builder-el-10  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5057 avia-img-lazy-loading-not-5057 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_Blob.png\" alt='' title='1504_Azure_DB_on_Azure_Blob'  height=\"394\" width=\"687\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_Blob.png 687w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_Blob-600x344.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_Blob-300x172.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_Blob-450x258.png 450w\" sizes=\"(max-width: 687px) 100vw, 687px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>This option is supported since SQL Server 2014.<br \/>\nHere, too, you get 500 IOPS per file, with a general limit of 20.000 per storage account.<\/p>\n<p>Here you can find a detailed example of the setup including a code:<br \/>\n<a href=\"http:\/\/blogs.msdn.com\/b\/igorpag\/archive\/2013\/10\/23\/create-a-sql-server-2014-database-directly-on-azure-blob-storage-with-sqlxi.aspx\" target=\"_blank\" rel=\"noopener\">Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI<\/a><\/p>\n<p>The disadvantage of this option is, in my view, the complexity. The approach to creating the <em>Shared Access Signature<\/em> required for accessing the <em>Blob container<\/em> is not really trivial.<\/p>\n<p><strong>3) Storing data files on an Azure File Share<\/strong><\/p>\n<p>Since May last year, the Azure file service (<a href=\"http:\/\/blogs.msdn.com\/b\/windowsazurestorage\/archive\/2014\/05\/12\/introducing-microsoft-azure-file-service.aspx\" target=\"_blank\" rel=\"noopener\">Introducing Microsoft Azure File Service<\/a>) has been available as <a href=\"http:\/\/azure.microsoft.com\/de-de\/services\/preview\/\" target=\"_blank\" rel=\"noopener\">preview feature<\/a>.<\/p>\n<p>In addition to \u201creal\u201d directories, this service also supports releases on the basis of SMB 2.1.<\/p>\n<p>Here we get a maximum of 1000 IOPS per share. I.e. for the same amount of IOPS only half as many Data Files are required as for the direct access to Azure Blobs.<br \/>\nIt is important to start by activating the preview feature for a storage account.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-a974cb4249e85daee8438265dad31cd3\">\n.avia-image-container.av-av_image-a974cb4249e85daee8438265dad31cd3 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-a974cb4249e85daee8438265dad31cd3 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-a974cb4249e85daee8438265dad31cd3 av-styling- avia-align-center  avia-builder-el-12  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5061 avia-img-lazy-loading-not-5061 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services.png\" alt='' title='1504_Azure_Storage_Account_Services'  height=\"886\" width=\"989\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services.png 989w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services-600x538.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services-300x269.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services-768x688.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services-705x632.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_Storage_Account_Services-450x403.png 450w\" sizes=\"(max-width: 989px) 100vw, 989px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Next, you create the necessary shares per PowerShell and distribute your database files on them.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-629a2c0a7d5ccbf00b9a4bd0b71c063e\">\n.avia-image-container.av-av_image-629a2c0a7d5ccbf00b9a4bd0b71c063e img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-629a2c0a7d5ccbf00b9a4bd0b71c063e .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-629a2c0a7d5ccbf00b9a4bd0b71c063e av-styling- avia-align-center  avia-builder-el-14  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-5059 avia-img-lazy-loading-not-5059 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_FileShare.png\" alt='' title='1504_Azure_DB_on_Azure_FileShare'  height=\"387\" width=\"722\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_FileShare.png 722w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_FileShare-600x322.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_FileShare-300x161.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_FileShare-705x378.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/12\/1504_Azure_DB_on_Azure_FileShare-450x241.png 450w\" sizes=\"(max-width: 722px) 100vw, 722px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>The limit of a maximum of 20,000 IOPS per storage account also applies here.<\/p>\n<p>But in my view, the access is significantly easier.<br \/>\nA disadvantage of this option is that you have to make sure that the SQL Server service starts only when the shares are available via network.<br \/>\nAside from occasionally occurring access problems that can probably be attributed to the preview status this combination, as I see it, is the easiest one to administrate \u2013 once you got used to the automatically controlled delayed service start.<\/p>\n<p>The Azure File service is currently offered with a 50% discount and is thus at around 20% below the Azure Blob storage Prices.<\/p>\n<p><strong>The advantage of the latter two options<\/strong> is evident: you are not bound to a particular configuration level of the system but you can start your SQL Server on a machine with far more or also far less CPU cores at certain times.<\/p>\n<p><strong>One final remark on the transaction log: <\/strong><\/p>\n<p>There is only one log file per database (several log files would be written to sequentially and not bring any performance advantages). There, the immediate benefit is that file shares deliver 1000 IOPS instead of 500 IOPS. If this is not sufficient, only the traditional approach combined with Windows Server Storage Spaces remains, unfortunately: striping of several data discs for the transaction log with the according scalability disadvantage.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>I hope this article made somewhat tangible what to me is the biggest advantage of the Cloud-based approach through the example of SQL Server. As soon as you get used to the Cloud\u2019s \u201cservice concept\u201d and <strong>leave behind<\/strong> <strong>traditional thinking patterns<\/strong> like \u201cI need an x-core machine\u201d you can build very cost- and performance-efficient systems by combining different services, such as, as demonstrated above, virtual machines and file services.<\/p>\n<p>Of course, IOPS are not always the ultimate performance indicator. I have chosen them over MB\/sec for simplifying reasons alone and without taking into account the request-size. In general, the values are to be understood based on 4-K sequential reading requests. This applies to all storage mechanisms that have been addressed here and should therefore suffice for the purpose of comparability.<\/p>\n<p>Those who are interested in further dealing with this topic are welcome to join the <strong>free<\/strong> one-day <strong>SQL Server conference<\/strong> <a href=\"http:\/\/www.sqlsaturday.com\/409\/eventhome.aspx\" target=\"_blank\" rel=\"noopener\"><strong>SQLSaturday Rheinland<\/strong><\/a> on 13 June in Sankt Augustin.<\/p>\n<p>On 12 June, the day before, there will also be a free PreCon, <a href=\"https:\/\/msevents.microsoft.com\/cui\/EventDetail.aspx?culture=de-DE&#038;EventID=1032623158\" target=\"_blank\" rel=\"noopener\">Hybrid IT Camp: Azure Szenarien &#038; die eigene flexible Infrastruktur f\u00fcr jedermann<\/a> (\u201c<em>Azure scenarios &#038; individual, flexible infrastructures for everybody<\/em>\u201d) (Short Link: <a href=\"http:\/\/bit.ly\/sqlsat409hybridit\" target=\"_blank\" rel=\"noopener\">http:\/\/bit.ly\/sqlsat409hybridit<\/a>), which I will be running with Patrick Heyde (<a href=\"http:\/\/blogs.technet.com\/b\/patrick_heyde\/\" target=\"_blank\" rel=\"noopener\">Blog<\/a>\/<a href=\"https:\/\/twitter.com\/patrickheyde\" target=\"_blank\" rel=\"noopener\">Twitter<\/a>) from Microsoft.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Below is a list for<strong> further reading<\/strong>:<\/p>\n<ul>\n<li><a href=\"http:\/\/azure.microsoft.com\/en-us\/documentation\/articles\/azure-subscription-service-limits\/\" target=\"_blank\" rel=\"noopener\">Azure Subscription and Service Limits, Quotas, and Constraints<\/a><\/li>\n<li><a href=\"http:\/\/azure.microsoft.com\/en-us\/pricing\/details\/storage\/\" target=\"_blank\" rel=\"noopener\">Azure Storage Pricing<\/a><\/li>\n<li><a href=\"http:\/\/blogs.msdn.com\/b\/igorpag\/archive\/2013\/10\/23\/create-a-sql-server-2014-database-directly-on-azure-blob-storage-with-sqlxi.aspx\" target=\"_blank\" rel=\"noopener\">Create a SQL Server 2014 Database directly on Azure Blob storage with SQLXI<\/a><\/li>\n<li><a href=\"http:\/\/blogs.msdn.com\/b\/windowsazurestorage\/archive\/2014\/05\/12\/introducing-microsoft-azure-file-service.aspx\" target=\"_blank\" rel=\"noopener\">Introducing Microsoft Azure File Service<\/a><\/li>\n<li><a href=\"http:\/\/azure.microsoft.com\/en-us\/documentation\/articles\/storage-dotnet-how-to-use-files\/\" target=\"_blank\" rel=\"noopener\">How to use Azure File storage with PowerShell and .NET<\/a><\/li>\n<li><a href=\"http:\/\/azure.microsoft.com\/en-us\/services\/preview\/\" target=\"_blank\" rel=\"noopener\">Microsoft Azure Preview features<\/a><\/li>\n<li><a href=\"http:\/\/blogs.msdn.com\/b\/igorpag\/archive\/2014\/06\/10\/best-practices-amp-disaster-recovery-for-storage-spaces-and-pools-in-azure.aspx\" target=\"_blank\" rel=\"noopener\">Best Practices &#038; Disaster Recovery for Storage Spaces and Pools in Azure<\/a><\/li>\n<li><a href=\"http:\/\/blogs.technet.com\/b\/dataplatforminsider\/archive\/2014\/09\/12\/new-vm-images-optimized-for-transactional-and-dw-workloads-in-azure-vm-gallery.aspx\" target=\"_blank\" rel=\"noopener\">New VM Images Optimized for Transactional and DW workloads in Azure VM Gallery<\/a><\/li>\n<\/ul>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Have fun on Azure cloud<\/p>\n<p>Andreas<\/p>\n<p>P.S.: A big \u201cthank you\u201d goes to Patrick Heyde for his valuable tips and mentoring in Microsoft Azure \u2013 I, too, had to get used to a new way of thinking \ud83d\ude42<\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-19  el_after_av_textblock  el_before_av_one_full '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-20  el_after_av_hr  el_before_av_social_share  first flex_column_div av-zero-column-padding  '     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-22  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/sql-server-in-microsoft-azure-how-to-gain-performance-by-flexibility-and-save-costs-at-the-same-time\/&#038;t=SQL%20Server%20in%20Microsoft%20Azure%3A%20How%20to%20gain%20performance%20by%20flexibility%20and%20save%20costs%20at%20the%20same%20time\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=SQL%20Server%20in%20Microsoft%20Azure%3A%20How%20to%20gain%20performance%20by%20flexibility%20and%20save%20costs%20at%20the%20same%20time&#038;url=https:\/\/andreas-wolter.com\/en\/?p=5080\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=SQL%20Server%20in%20Microsoft%20Azure%3A%20How%20to%20gain%20performance%20by%20flexibility%20and%20save%20costs%20at%20the%20same%20time&#038;url=https:\/\/andreas-wolter.com\/en\/sql-server-in-microsoft-azure-how-to-gain-performance-by-flexibility-and-save-costs-at-the-same-time\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-23  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"This is the first time that I am tackling Microsoft Azure in an article. (Microsoft.com: What is Microsoft Azure?) A plethora of services is provided by Microsoft Azure by now, of which the hosting of virtual machines running on an SQL Server service is one. This is what we call IaaS (Infrastructure as a Service).","protected":false},"author":4,"featured_media":5077,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[124,97,43,64],"tags":[315,26,179],"class_list":["post-5080","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-conferences-en","category-performance","category-sqlpass-en","tag-azure-en","tag-performance-en","tag-sqlsaturday-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5080","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=5080"}],"version-history":[{"count":3,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5080\/revisions"}],"predecessor-version":[{"id":5083,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5080\/revisions\/5083"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/5077"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=5080"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=5080"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=5080"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}