다운로드 사이트
https://powerbi.microsoft.com/en-us/downloads/
Powershell 실행
sqlmon.ps1
#Set-ExecutionPolicy RemoteSigned # Input Parameter # Azure Subscription Login Connect-AzureRmAccount #param #( # [Parameter(HelpMessage="Please Enter Server name of Azure SQL Database...",Mandatory=$True)] # [string]$ServerName #) Do {$ServerName = Read-Host "Please Enter to Azure SQL Database Server Host Name - xxxxx.database.windows.net"} Until($ServerName) # Set an admin login and password for your database $ResourceGroupName = "SQLMonitor" $DatabaseName = "sqlmon" $adminlogin = "sqlmon" $password = "Password1!@#" $LocationInfo = "southeastasia" #============================================= # Create resouce group/databas #============================================= # Create an Azure resource group in location New-AzureRmResourceGroup -Name $ResourceGroupName -Location $LocationInfo # Create an Azure sql database New-AzureRmSqlServer -ResourceGroupName $ResourceGroupName -ServerName $ServerName -Location $LocationInfo -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)) # Create sqlmon Database New-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -RequestedServiceObjectiveName "S0" # Create AdventureWorksLT Database New-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName "AdventureWorksLT" -SampleName "AdventureWorksLT" -RequestedServiceObjectiveName "S0" Account : zetus@naver.com SubscriptionName : 종량제 SubscriptionId : 0383fbb9-91ee-46b5-af49-658cc1ffc99f TenantId : 6378760f-4c71-4ed3-8c50-3808ebd18a23 Environment : AzureCloud Please Enter to Azure SQL Database Server Host Name - xxxxx.database.windows.net: phoenixm ResourceGroupName : SQLMonitor Location : southeastasia ProvisioningState : Succeeded Tags : TagsTable : ResourceId : /subscriptions/0383fbb9-91ee-46b5-af49-658cc1ffc99f/resourceGroups/SQLMonitor ResourceGroupName : SQLMonitor ServerName : phoenixm Location : southeastasia SqlAdministratorLogin : sqlmon SqlAdministratorPassword : ServerVersion : 12.0 Tags : Identity : FullyQualifiedDomainName : phoenixm.database.windows.net ResourceGroupName : SQLMonitor ServerName : phoenixm DatabaseName : sqlmon Location : Southeast Asia DatabaseId : 194bb67e-f3bb-4ae8-8e1a-98bdcf37066e Edition : Standard CollationName : SQL_Latin1_General_CP1_CI_AS CatalogCollation : MaxSizeBytes : 268435456000 Status : Online CreationDate : 2018-08-31 오전 1:41:25 CurrentServiceObjectiveId : f1173c43-91bd-4aaa-973c-54e79e15235b CurrentServiceObjectiveName : S0 RequestedServiceObjectiveId : f1173c43-91bd-4aaa-973c-54e79e15235b RequestedServiceObjectiveName : ElasticPoolName : EarliestRestoreDate : 2018-08-31 오전 2:11:25 Tags : {} ResourceId : /subscriptions/0383fbb9-91ee-46b5-af49-658cc1ffc99f/resourceGroups/SQLMonitor/providers/Microsoft.Sql/servers/phoe nixm/databases/sqlmon CreateMode : ReadScale : Disabled ZoneRedundant : False ResourceGroupName : SQLMonitor ServerName : phoenixm DatabaseName : AdventureWorksLT Location : Southeast Asia DatabaseId : 7ecf681b-ab2c-4643-9779-5e8944eb6f5d Edition : Standard CollationName : SQL_Latin1_General_CP1_CI_AS CatalogCollation : MaxSizeBytes : 268435456000 Status : Online CreationDate : 2018-08-31 오전 1:42:28 CurrentServiceObjectiveId : f1173c43-91bd-4aaa-973c-54e79e15235b CurrentServiceObjectiveName : S0 RequestedServiceObjectiveId : f1173c43-91bd-4aaa-973c-54e79e15235b RequestedServiceObjectiveName : ElasticPoolName : EarliestRestoreDate : 2018-08-31 오전 2:12:28 Tags : ResourceId : /subscriptions/0383fbb9-91ee-46b5-af49-658cc1ffc99f/resourceGroups/SQLMonitor/providers/Microsoft.Sql/servers/phoe nixm/databases/AdventureWorksLT CreateMode : ReadScale : Disabled ZoneRedundant : False |
perfmon
| #Connect $Connection = New-Object System.Data.SQLClient.SQLConnection #Please modify connection string $Connection.ConnectionString = "Server=tcp:phoenixm.database.windows.net,1433;Initial Catalog=sqlmon;Persist Security Info=False;User ID=sqlmon;Password=Password1!@#;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=3600;" $Connection.Open() #Collect performance counters from sys.dm_os_performance_counters $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "EXEC dbo.up_Collect_Perf_Mon_Data" $SqlCmd.Parameters.AddWithValue("@SnapshotIntervalSeconds", 5) | Out-Null $SqlCmd.Parameters.AddWithValue("@MaxIterationCount", 180) | Out-Null $SqlCmd.Connection = $Connection $SqlCmd.CommandTimeout = 0 $SqlCmd.ExecuteNonQuery() $Connection.Close() |
Import Data : SQL Server 를 선택하고
SQL Server 접속 정보입력 : 실제 데이터를 불러올 Select 구문 또는 Procedure 선언
불러온 데이터 확인 : 데이터 변경이 필요하면 편집이 가능
대시보드 편집 : 오른쪽 메뉴에서 그래프, 테이터 선택 및 UI편집 가능
1) Graph, Chart 선택
2) 원본 데이터 선택
3) 상세 편집 가능
이렇게 탄생한 최종 산출물
