Azure資源的優化與節費
50 min readMar 20, 2024
雲端資源的成本優化可以分為兩類:
- 成本規避(Cost avoidance)
- 調整成正確的資源(Right sizing)
成本規避是指刪除真的不需要的資源。而Right sizing就需要我們做功課，研究是否真的適用並調整成我們的系統所需要的。在IT領域中，包含雲端運算都有一些限制性條件 — 三重限制。絕大部分的情況中，這個三重限制無法三個面向都同時達成。我們也將使用OKR(Objectives and Key Results)方式來完成組織的雲端節費目標。
接下來我們將使用Azure Monitor workbook來辨識可以節費的資源。Azure Advisor也能自動的協助我們建議我們可以調整的雲端規模的可能性。然而成本優化是有一好不會有兩好，所有我們也會討論成本優化的好與壞。
真實世界的三重限制
做過專案管理的人都知道，要完成一項專案的目標組織會遇到以下三重限制
- 品質
- 成本
- 速度(時間)
用老闆的話來說就是"又快、又好、又便宜"，但通常這是不可能的。會使用雲端運算的組織通常都希望是"快"，因為不會有地端機房的Lead time存在。但會在"快與品質"之間掙扎。
因為在雲端上的"品質"意味著系統/服務的"可靠性、可用性與一致性"。而這樣就是說要使用較高等級與冗餘的雲端資源。
而成本與組織的IT預算相關，而預算也與組織要達成的業務目標也相關。如果預算越少，我們可以達成業務目標品質也可能變差。當然我們也可以使用技術人員的時間來彌補預算的不足，但是完成時間又會增加。
使用OKR
關於OKR的概念，本文不會解釋太多。網路上有需多大神的文章都在解釋這一塊，有興趣的讀者可以去查閱。本文在這裡只會討論實際的範例。
使用過OKR的人都知道，OKR的特性是"可信的、可持續與可控的"。如果員工不信OKR這一套，哪OKR也只是一種紙上作業，永遠不會達成組織想要的目標。OKR適合雲端財務管理的原因之一是它們都是快節奏的結果論，可能是每季或半年一次的目標達成。例如:
- 目標: 節省30%的月報處理時間
關鍵成果:
1.調整正確的運算資源 — 例如調整成較大的資源
2. 使用Auto-sacling的技術 - 目標: 節省25%的非生產環境費用
關鍵成果:
1. 關閉不必要的資源
2. 在周末時自動關閉所有非生產環境資源
3.共用PaaS資源
使用KPI — 標籤覆蓋率
在每一項的雲端資源費用最終都要找到人/單位來負責，也就是chargeback/showback。所以對雲端資源給予標籤是最重要的，不然就沒人要負這一筆帳了。例如以下範例為每個單位目前對於資源覆蓋率的百分比，目標應該是越高越好。
另外一個成本規避的KPI範例是尋找沒有在使用的HDD，也就是沒有VM掛載的。這個目標應該是零(如下圖)。
企業也有可能是微軟的EA客戶，哪麼企業就可以使用Azure Hybrid Benefit來將EA的license用在Azure上。既然組織都已經有EA了，哪麼就應該好好的使用這一項資源。哪我們應該找出哪些單位沒有將組織的EA license套用在VM中(如下圖)。這一個目標應該是100%。
企業如果對資料有分級分類，哪麼我們應該就很容易判定資料應該儲存在甚麼樣的層級(hot/cool/archive)。我們應該檢視組織目前的storage account是被歸類在哪種層級之中，並且努力將不會經常使用的資料放在cool/archive tier(如下圖)。
運用Azure Advisor recommendations
這是一項Azure提供的免費服務，能讓我們對雲端資源做成本優化。我們可以在這裡設定針對某個系統在subscription/resource groups中設定一個成本優化目標。該服務會給於我們的建議範例通常有:
- 調整或關閉使用率低的VM
- 使用Stand storage來儲存managed disk snapshots
- 調整使用率低的PostgreSQL servers
- 針對閒置的Azure Cosmos Db container採取某些處置
- 調整Data Explorer resource大小
- 購買RI
使用客製的Azure Workbooks的使用優化目標
雲端資源的使用優化是指圍繞"刪除、調整規模和重新設計"雲端中的工作負載和服務進行的活動，最終目標是節錢。以下是幾個我們可以在Azure中設定的目標範例。
98%的資源需要有標籤
就跟傳統的地端機房一樣，沒有資產標籤我們就不知道誰該為這筆資產買單，雲端資源也是組織的資產需要有人買單。在Azure中的資產標籤最佳實踐是使用Azure Policy來強制雲端資源一定要有資產標籤。使用客製化的workbook來檢視有標籤與沒標籤的比率。
第一步到Azure Monitor中的建立新的workbook:
在Advanced Editor中點選Gallery Template:
copy以下的代碼貼到Gallery Template
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "51aa3a9b-14e0-4c22-a60d-abdbf8813f00",
"version": "KqlParameterItem/1.0",
"name": "Subscription",
"type": 6,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"includeAll": true,
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"defaultValue": "value::all",
"value": [
"/subscriptions/f48affae-430d-4570-b20e-2ab1c49e4366",
"/subscriptions/b2a328a7-ffff-4c09-b643-a4758cf170bc"
]
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "param-sub"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": "Target - 98% of all your Resources must be tagged"
},
"name": "rg-info"
},
{
"type": 1,
"content": {
"json": "Tagging plays pivotal role for allocating and tracking the budget for each application. When following best practices for tag management, tags can be the basis for applying your business policies with Azure Policy or tracking costs with Cost Management.\r\n\r\nThe goal is to have 98% of all azure resources must have all the required tags. \r\n\r\nThe report below shows 5 key data. First two are Tagged vs. Untagged Resource Groups as well Resources in a Pie chart format. Second two tables display more information about Tagged vs. Untagged Resource Group Names. The last table list all Untagged Resources.\r\n\r\nConsider using Azure Policy to place mandatory Tag rules such as Enforce Tags on Resource Groups and inheritance on the Resources.\r\n\r\nhttps://docs.microsoft.com/en-us/azure/azure-resource-manager/management/tag-policies\r\n",
"style": "info"
},
"name": "rg-info"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers\r\n| where type =~ 'microsoft.resources/subscriptions/resourcegroups'\r\n| extend TagBool = iff(tags != '' and tags != '[]', \"Tagged\",\"Untagged\")\r\n| summarize count() by TagBool\r\n//| project resourceGroup, tags\r\n",
"size": 0,
"title": "Tagged vs. Untagged Resource Groups",
"exportFieldName": "resourceGroupTag",
"exportParameterName": "resourceGroupTag",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
],
"visualization": "piechart"
},
"customWidth": "50",
"name": "rg-tag-query1"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "Resources | extend TagBool = iff(tags != '' and tags != '[]', \"Tagged\",\"Untagged\") | summarize count() by TagBool\r\n\r\n",
"size": 0,
"title": "Tagged vs. Untagged Resources Count",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
],
"visualization": "piechart",
"tileSettings": {
"showBorder": false,
"titleContent": {
"columnMatch": "TagBool",
"formatter": 1
},
"leftContent": {
"columnMatch": "count_",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"graphSettings": {
"type": 0,
"topContent": {
"columnMatch": "TagBool",
"formatter": 1
},
"centerContent": {
"columnMatch": "count_",
"formatter": 1,
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
}
},
"customWidth": "50",
"name": "rg-tag-query2"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers\r\n| where type =~ 'Microsoft.Resources/subscriptions' \r\n| extend SubscriptionName=name \r\n| join ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | where tags !~ '' and tags != '{}'\r\n| extend resourceGroupName=id, RGLocation=location, RGTags=tags) on subscriptionId\r\n| project resourceGroupName, RGTags, RGLocation, SubscriptionName\r\n",
"size": 0,
"title": "Tagged Resource Groups",
"noDataMessageStyle": 3,
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"customWidth": "50",
"name": "rg-tag-query3"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers\r\n| where type =~ 'Microsoft.Resources/subscriptions' \r\n| extend SubscriptionName=name \r\n| join ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | where tags =~ '' or tags =~ '{}'\r\n| extend resourceGroupName=id, RGLocation=location) on subscriptionId\r\n| project resourceGroupName, RGLocation, SubscriptionName\r\n",
"size": 0,
"title": "Untagged Resource Groups",
"noDataMessageStyle": 3,
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"customWidth": "50",
"name": "rg-tag-query4"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "resources | where tags == ''\r\n| project Name=id, subscriptionId\r\n",
"size": 3,
"title": "Untagged Resources",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
],
"gridSettings": {
"formatters": [
{
"columnMatch": "$gen_group",
"formatter": 13,
"formatOptions": {
"linkTarget": "Resource",
"showIcon": true
}
},
{
"columnMatch": "Name",
"formatter": 5
},
{
"columnMatch": "subscriptionId",
"formatter": 5
}
],
"rowLimit": 1000,
"filter": true,
"hierarchySettings": {
"treeType": 1,
"groupBy": [
"subscriptionId"
],
"expandTopLevel": true,
"finalBy": "Name"
}
}
},
"name": "tags-q1"
}
]
},
"name": "Tagging",
"styleSettings": {
"showBorder": true
}
}
],
"fallbackResourceIds": [
"azure monitor"
],
}Apply並且存檔後會出現類似以下畫面。
而在上述的腳本中的這一段(如下)，我們可以依據本身的需求再客製化:
ResourceContainers\r\n| where type =~ 'Microsoft.Resources/subscriptions'
\r\n| extend SubscriptionName=name \r\n| join ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where tags =~ '' or tags =~ '{}'\r\n
| extend resourceGroupName=id, RGLocation=location) on subscriptionId\r\n
| project resourceGroupName, RGLocation, SubscriptionName\r\n調整低於使用率的VM
Azure Advisory的建議是根據我們的VM在Azure中使用CPU/memory/network等過去7天的使用率來進行評估的。並且經過演算法的評估之後給與我們相應的SKU或instance數量。Azure Advisor會確定最適合和最便宜的成本而不影響效能，並產生正確調整大小的建議。
我們一樣選擇空白的workbook並選擇Gallery template。將以下代碼copy過去。
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "51aa3a9b-14e0-4c22-a60d-abdbf8813f00",
"version": "KqlParameterItem/1.0",
"name": "Subscription",
"type": 6,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"includeAll": true,
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"defaultValue": "value::all",
"value": [
"/subscriptions/f48affae-430d-4570-b20e-2ab1c49e4366"
]
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "param-sub"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": " Target - Right-size underutilized virtual machines"
},
"name": "rg-info"
},
{
"type": 1,
"content": {
"json": "Right-sizing is a key lever to reduce cost and optimize resources. It’s an exercise to find out the smallest VM instance that supports your workload requirements. Azure Advisor automatically evaluate your cloud footprint and analyze CPU, Memory and Network utilization in past 7 days. Then using algorithms, it calculates appropriate SKU or Instance count. Azure advisor determines the best fit and the cheapest costs with no performance impacts and generates the recommendation to right-size. Use the workbook 2 which provides list of right-size recommendations for your Virtual Machines and Virtual Machine Scale Sets.",
"style": "info"
},
"name": "rg-info"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "advisorresources\r\n| where type == \"microsoft.advisor/recommendations\"\r\n| where tostring (properties.category) has \"Cost\"\r\n| where properties.impactedField has \"Compute\" or properties.impactedField has \"Container\" or properties.impactedField has \"Web\"\r\n| project AffectedResource=tostring(properties.resourceMetadata.resourceId),Impact=properties.impact,resourceGroup,Action=tostring(properties.extendedProperties.recommendationMessage),AdditionaInfo=properties.extendedProperties,subscriptionId,Recommendation=tostring(properties.shortDescription.problem),name",
"size": 0,
"title": "Azure Advisor Cost recommendations",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
],
"gridSettings": {
"formatters": [
{
"columnMatch": "subscriptionId",
"formatter": 15,
"formatOptions": {
"linkTarget": null,
"showIcon": true
}
}
],
"filter": true,
"hierarchySettings": {
"treeType": 1,
"groupBy": [
"Recommendation"
],
"expandTopLevel": true
}
}
},
"name": "target2-q1"
}
]
},
"name": "Tagging",
"styleSettings": {
"showBorder": true
}
}
],
"fallbackResourceIds": [
"azure monitor"
],
}apply並完成編輯後會出現類似如下畫面。
其中以下的代碼內容我們可以根據需求進行修改
advisorresources\r\n
| where type == \"microsoft.advisor/recommendations\"\r\n
| where tostring (properties.category) has \"Cost\"\r\n
| where properties.impactedField has \"Compute\" or properties.impactedField has \"Container\" or properties.impactedField has
\"Web\"\r\n| project AffectedResource=tostring(properties.resourceMetadata.resourceId),Impact=properties.impact,resourceGroup,
Action=tostring(properties.extendedProperties.recommendationMessage),AdditionaInfo=properties.extendedProperties,subscriptionId,
Recommendation=tostring(properties.shortDescription.problem),name調整低於使用率的SQL DB
Azure Advisor 會給我們使用率低的SQL DB的大小調整建議，看是DTU(Database Transaction Unit)或SKU(Stock Keeping Unit)或是刪掉沒再動的。
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "51aa3a9b-14e0-4c22-a60d-abdbf8813f00",
"version": "KqlParameterItem/1.0",
"name": "Subscription",
"type": 6,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"includeAll": true,
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"defaultValue": "value::all",
"value": [
"/subscriptions/f48affae-430d-4570-b20e-2ab1c49e4366",
"value::all"
]
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "param-sub"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": "Target - Right-size underutilized SQL databases"
},
"name": "rg-info"
},
{
"type": 1,
"content": {
"json": "When ",
"style": "info"
},
"name": "rg-info"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "advisorresources\r\n| where type == \"microsoft.advisor/recommendations\"\r\n| where tostring (properties.category) has \"Cost\"\r\n| where properties.impactedField has \"Data\" or properties.impactedField has \"Sql\"\r\n| project AffectedResource=tostring(properties.resourceMetadata.resourceId),Impact=properties.impact,resourceGroup,AdditionaInfo=properties.extendedProperties,subscriptionId,Recommendation=tostring(properties.shortDescription.problem),name",
"size": 0,
"title": "Azure Advisor Cost recommendations - Data",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
],
"gridSettings": {
"filter": true,
"hierarchySettings": {
"treeType": 1,
"groupBy": [
"Recommendation"
],
"expandTopLevel": true
}
}
},
"name": "target4-q1"
}
]
},
"name": "Tagging",
"styleSettings": {
"showBorder": true
}
}
],
"fallbackResourceIds": [
"azure monitor"
],
}
其中這一段可以根據需求進行修改
advisorresources\r\n| where type == \"microsoft.advisor/recommendations\"\r\n
| where tostring (properties.category) has \"Cost\"\r\n
| where properties.impactedField has \"Data\" or properties.impactedField has
\"Sql\"\r\n| project AffectedResource=tostring(properties.resourceMetadata.resourceId),
Impact=properties.impact,resourceGroup,AdditionaInfo=properties.extendedProperties,
subscriptionId,Recommendation=tostring(properties.shortDescription.problem),name在Azure Hybrid Benefit套用在Windows/Linux VM中
Azure Hybrid Benefit可以除了將我們的Windows License套用在Azure VM中，現在RHEL/SLES也能夠套用了。我們可以用Portal或PowerShell的方式來啟用Azure Hybrid Benefit。如果是既有的VM:
點選該VM →Configuration →Licensing →Yes(如下畫面)
如同上面的範例，將以下代碼copy到template中。
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "51aa3a9b-14e0-4c22-a60d-abdbf8813f00",
"version": "KqlParameterItem/1.0",
"name": "Subscription",
"type": 6,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"includeAll": true,
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"defaultValue": "value::all",
"value": [
"/subscriptions/f48affae-430d-4570-b20e-2ab1c49e4366"
]
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "param-sub"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": "Target - Enable Azure hybrid benefit for Windows and Linux virtual machines"
},
"name": "rg-info"
},
{
"type": 1,
"content": {
"json": "Virtual machines deployed from pay-as-you-go images on Azure incur both an infrastructure fee and a software fee. Azure Hybrid Benefit for Windows Server allows you to use your on-premises Windows Server licenses and run Windows virtual machines on Azure at a reduced cost. Now Azure also offers BYOL for Red Hat Enterprise Linux (RHEL) and SUSE Linux Enterprise Server (SLES) virtual machines. With this benefit, your RHEL or SLES subscription covers your software fee. You pay only infrastructure costs for your virtual machine. Use the workbook 3 to list out your Windows and Linux virtual machines which does not have Hybrid Benefit enabled. Then use the CLI or Azure Portal to enable the hybrid benefit to optimize the savings. ",
"style": "info"
},
"name": "rg-info"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers | where type =~ 'Microsoft.Resources/subscriptions' | extend SubscriptionName=name | join ( resources | where type =~ 'microsoft.compute/virtualmachines' and tostring(properties.storageProfile.osDisk.osType) == 'Windows' and tostring(properties.['licenseType']) !has 'Windows' | extend WindowsId=id, VMName=name, VMLocation=location, VMRG=resourceGroup, OSType=tostring(properties.storageProfile.imageReference.offer), OsVersion = tostring(properties.storageProfile.imageReference.sku), VMSize=tostring (properties.hardwareProfile.vmSize), LicenseType = tostring(properties.['licenseType'])) on subscriptionId \r\n | order by VMSize asc \r\n | project WindowsId,VMName,VMRG,VMSize, VMLocation,OSType, OsVersion,LicenseType, SubscriptionName",
"size": 0,
"title": "Windows VMs without Azure Hybrid Benefit enabled",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"name": "target3-q1"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers | where type =~ 'Microsoft.Resources/subscriptions' | extend SubscriptionName=name | join ( resources | where type =~ 'microsoft.compute/virtualmachines' and properties.storageProfile.imageReference.publisher == 'suse' or properties.storageProfile.imageReference.publisher=='RedHat' and tostring(properties.['licenseType']) != 'SLES_BYOS' and tostring(properties.['licenseType']) != 'REHL_BYOS' | extend WindowsId=id, VMName=name, VMLocation=location, VMRG=resourceGroup, OSType=tostring(properties.storageProfile.imageReference.offer), OsVersion = tostring(properties.storageProfile.imageReference.sku), VMSize=tostring (properties.hardwareProfile.vmSize), LicenseType = tostring(properties.['licenseType'])) on subscriptionId \r\n | order by VMSize asc \r\n | project WindowsId,VMName,VMRG, VMLocation,OSType, OsVersion,LicenseType, VMSize, SubscriptionName",
"size": 0,
"title": "Linux VMs without Azure Hybrid Benefit enabled",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"name": "target3-q2"
}
]
},
"name": "Tagging",
"styleSettings": {
"showBorder": true
}
}
],
"fallbackResourceIds": [
"azure monitor"
],
}畫面會像以下畫面
其中的這一段代碼可以依據需求進行修改
ResourceContainers | where type =~ 'Microsoft.Resources/subscriptions'
| extend SubscriptionName=name | join ( resources
| where type =~ 'microsoft.compute/virtualmachines' and properties.storageProfile.imageReference.publisher ==
'suse' or properties.storageProfile.imageReference.publisher
=='RedHat' and tostring(properties.['licenseType']) != 'SLES_BYOS' and tostring(properties.['licenseType']) != 'REHL_BYOS'
| extend WindowsId=id, VMName=name, VMLocation=location, VMRG=resourceGroup,
OSType=tostring(properties.storageProfile.imageReference.offer),
OsVersion = tostring(properties.storageProfile.imageReference.sku),
VMSize=tostring (properties.hardwareProfile.vmSize),
LicenseType = tostring(properties.['licenseType'])) on subscriptionId \r\n
| order by VMSize asc \r\n
| project WindowsId,VMName,VMRG,
VMLocation,OSType, OsVersion,LicenseType, VMSize, SubscriptionName在Azure Hybrid Benefit套用在SQL DB/Management instance/SQL VMs
Azure Hybrid Benefit除了可以將License套用在IaaS上，也可以套用在PaaS中。
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "51aa3a9b-14e0-4c22-a60d-abdbf8813f00",
"version": "KqlParameterItem/1.0",
"name": "Subscription",
"type": 6,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"includeAll": true,
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"defaultValue": "value::all",
"value": [
"/subscriptions/f48affae-430d-4570-b20e-2ab1c49e4366",
"value::all"
]
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "param-sub"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": "Target - Enable Azure hybrid benefit for SQL databases, Managed Instance and SQL virtual machine"
},
"name": "rg-info"
},
{
"type": 1,
"content": {
"json": "Azure Hybrid benefits for SQL Server allows to use SQL Server licenses with Software Assurance to reduce the base rate for the SQL Database Managed Instance and SQL Virtual Machines. Azure is the only cloud provides ability to apply license to fully managed PaaS product. Use Azure Hybrid benefits for SQL server with Azure Hybrid benefit for Windows Server to get maximum savings.",
"style": "info"
},
"name": "rg-info"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers\r\n| where type =~ 'Microsoft.Resources/subscriptions' \r\n| extend SubscriptionName=name | join (resources | where type =~ 'Microsoft.Sql/servers/databases' and name != 'master' and tostring(properties.['licenseType']) == 'LicenseIncluded' \r\n| extend SQLDBID=id,SQLName = name, SQLRG = resourceGroup, SKUName=sku.name, SKUTier=sku.tier, SQLLocation = location, LicenseType = tostring(properties.['licenseType']), StorageAccountType=tostring(properties.['storageAccountType'])) on subscriptionId \r\n| project SQLDBID,SQLName,SQLRG, SKUName, SKUTier, SQLLocation, LicenseType, StorageAccountType, SubscriptionName\r\n",
"size": 0,
"title": "Azure SQL Databases not using Hybrid Benefit",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"name": "target5-q1"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers\r\n | where type =~ 'Microsoft.Resources/subscriptions' \r\n | extend SubscriptionName=name \r\n | join (resources | where type =~ 'Microsoft.Sql/managedInstances' and tostring(properties.['licenseType']) == 'LicenseIncluded'\r\n | extend ManagedInstance=id, SQLRG=resourceGroup, SQLLocation=location, LicenseType = tostring(properties.['licenseType'])) on subscriptionId \r\n | project ManagedInstance,SQLRG, SQLLocation, LicenseType, SubscriptionName\r\n ",
"size": 0,
"title": "Azure SQL Managed Instances not using Hybrid Benefit",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"name": "target5-q2"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "ResourceContainers | where type =~ 'Microsoft.Resources/subscriptions' | extend SubscriptionName=name | join ( resources | where type =~ 'Microsoft.SqlVirtualMachine/SqlVirtualMachines' and tostring(properties.['sqlServerLicenseType']) != 'AHUB' | extend SQLID=id, VMName = name, VMRG = resourceGroup, VMLocation = location, LicenseType = tostring(properties.['sqlServerLicenseType']), OSType=tostring(properties.storageProfile.imageReference.offer), SQLAgentType = tostring(properties.['sqlManagement']), SQLVersion = tostring(properties.['sqlImageOffer']), SQLSKU=tostring(properties.['sqlImageSku'])) on subscriptionId \r\n | order by id asc \r\n | project SQLID,VMName,VMRG, VMLocation, SQLVersion, SQLSKU, SQLAgentType, LicenseType, SubscriptionName\r\n | where SQLSKU != \"Developer\" and SQLSKU != \"Express\"",
"size": 0,
"title": "SQL Virtual Machines not using Hybrid Benefit",
"showExportToExcel": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"crossComponentResources": [
"{Subscription}"
]
},
"name": "target5-q3"
}
]
},
"name": "Tagging",
"styleSettings": {
"showBorder": true
}
}
],
"fallbackResourceIds": [
"azure monitor"
],
}
依需求可客製的代碼
ResourceContainers\r\n| where type =~ 'Microsoft.Resources/subscriptions' \r\n
| extend SubscriptionName=name | join (resources
| where type =~ 'Microsoft.Sql/servers/databases' and name != 'master' and
tostring(properties.['licenseType']) == 'LicenseIncluded' \r\n| extend
SQLDBID=id,SQLName = name, SQLRG = resourceGroup, SKUName=sku.name,
SKUTier=sku.tier, SQLLocation = location, LicenseType = tostring(properties.
['licenseType']), StorageAccountType=tostring(properties.['storageAccountType']))
on subscriptionId \r\n| project SQLDBID,SQLName,SQLRG, SKUName, SKUTier,
SQLLocation, LicenseType, StorageAccountType, SubscriptionName安全/效能/可靠度與成本的取捨
我們在本文的一開頭就已經說得很清楚，想達到雲端服務的”又快、又好、又便宜”幾乎不可能的。如果可以，你現在不會來看這一篇文章，應該被請去當某個地方的大神了。所以雲端中的高品質服務都與成本有關係。重要的是這些雲端服務是否達成了組織的業務目的。例如，預計投資在雲端中的每一元可以帶來10倍的利潤，哪麼在雲端中的高品質服務就有其價值。
成本與安全
系統安全性是在雲端託管代碼的最重要的面向。 從本質上講，公有雲可以透過Internet 存取，並且保護系統所需的各種服務，但這會做增加解決方案的成本。
例如組織將電商網站放在Azure中，這個電商網站為了符合PCI DSS的規範需要將以下服務啟用:
- Azure Firewall
- SQL Server的加密功能
- WAF
- Microsoft Defender
而這些都會增加組織的成本，但卻減低了被攻擊的風險。
成本與效能
高效能意味著成本的增加，但也減少資料處裡的時間。看是用金錢換取時間或用時間換去金錢。例如組織每個都要跑資料龐大的月報，無論上層的SQL語法或代碼或演算法寫得再好，沒有相對應的硬體資源支援不會有好的效能表現得。
成本與可靠性
這代表組織對內外部利害關係人或客戶承諾。可能是基於SLA/RPO/RTO之類的指標。但這一類的指標要求越高，冗餘的IT組件可能越多。這時就看該系統是否為組織的核心系統。如果該系統掛掉了，整個組織就無法運作哪就是核心系統。但就是核心新統，組織可能也可以忍受部分的損失。因為太高的可靠度代表對於雲端的ROI不符合比率。
