Browsed by
Month: July 2016

Windows Azure Pack PowerShell SQL Resource Provider Add SQL Server Hosting Group to Plan

Windows Azure Pack PowerShell SQL Resource Provider Add SQL Server Hosting Group to Plan

This post equates to the 5th in the series of blog posts for Azure Pack, where we have gone over the process of obtaining, adding, and removing the core components of Azure Pack being the User, Plans and Subscription objects, then we moved onto the process of Adding & removing the SQL Server hosting groups, and there dependent SQL servers in the SQL Resource Provider. So in this post we will complete the circle on the administrative side by adding the newly created SQL Server group to a Plan.

Unlike the past posts where the concepts were quite self-explanatory when attaching the SQL Server hosting group to a Plan, it needs to be completed in a two-step process, the first is to associate the SQL Resource Provider to the Plan, then once it has been attached there is a requirement to allocate the actual resources by using a Quota.

From here your users will be able to create their own databases on the presented SQL Servers.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Add SQL Server Group to Plan

$ResProviderName = ‘sqlservers’

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

$ResourceProvider = Get-MgmtSvcResourceProvider -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -Name $ResProviderName

Add-MgmtSvcPlanService -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -InstanceId $ResourceProvider.InstanceId -ServiceName $ResourceProvider.Name -PlanId $plan.ID

 

# Add SQL Quota to Plan

$ServiceName = ‘sqlservers’

$QuotaList = New-MgmtSvcQuotaList

$ResProvider = Get-MgmtSvcResourceProvider -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -Name $servicename

$SQLQuota = Add-MgmtSvcListQuota -QuotaList $QuotaList -ServiceName $ServiceName -ServiceInstanceId $ResProvider.InstanceId

$sqlquotares = Add-MgmtSvcQuotaSetting -Quota $SqlQuota -Key Editions -Value ‘[{“displayName”:”Laptop”, “groupName”:”Laptop”, “resourceCount”:”1″, “resourceSize”:”1024″, “resourceSizeLimit”:”1024″, “offerEditionId”:”081313063701″, “groupType”:null}]’

Update-MgmtSvcPlanQuota -AdminUri $AdminAPI -Token $token -QuotaList $QuotaList -PlanId $plan.Id -DisableCertificateValidation:$cert

 

# Remove SQL Server group from plan

$ResProviderName = ‘sqlservers’

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

remove-MgmtSvcPlanService -adminuri $adminapi -token $token -disablecertificatevalidation:$cert -ServiceName $ResProviderName -PlanId $plan.id

 

# Get list of Services attached to Plan

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

$plan.ServiceQuotas.settings.Value

I hope this helps and makes sense, please let me know via twitter @steve_hosko if you would like to know any more information around this.

Good Luck

Steve

Windows Azure Pack PowerShell SQL Resource Provider Add Servers & Hosting Group

Windows Azure Pack PowerShell SQL Resource Provider Add Servers & Hosting Group

Here we are again to go over some more commands for automation of Azure Pack, and in this case the addition and or removal of new/old SQL servers to your existing infrastructure, in my previous posts I have gone over the Get, Add, and Removal of Users, Plans, and Subscriptions which are the core components of Azure Pack. For this post I will go over the process of obtaining, the SQL Server Hosting Group, in this case it is a standalone group as Microsoft has not exposed the ability to add a highly available group via PowerShell.

Once we have spent our time working on the SQL Server Hosting group we will move on to adding a new SQL server to the Group, along with the removal of the SQL Server.

Much like the last posts I have included the first 33 lines where we obtain all of the required details for Azure Pack to execute the commands.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Get SQL Server Hosting Group

Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert

 

# Remove SQL Server Hosting Group

$SQLSvrGroupName = ‘Laptop’

$SQLSvrGroup = Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -GroupName $SQLSvrGroupName

Remove-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -ServerGroupId $SQLSvrGroup.GroupId

 

# Add SQL Server Hosting Group

$GRPNAME = ‘Laptop’

Add-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -GroupName $GRPNAME -ResourceGovernorEnabled

 

# Get SQL Server

Get-MgmtSvcSqlHostingServer -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert

 

# Remove SQL Server

$Svrname = ‘desktop-ojt0lg6’

$sqlsvr = Get-MgmtSvcSqlHostingServer -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert | Where-Object {$_.Name -eq $Svrname}

Remove-MgmtSvcSqlHostingServer -AdminUri $adminapi -token $token -DisableCertificateValidation:$cert -HostingServerId $sqlsvr.ServerId

 

# Add SQL Server

$cred = Get-Credential -UserName ‘SA’ -Message ‘Enter the SA Password for the SQL Server’

$GRPNAME = ‘Laptop’

$SQLSize = 1024

$SQLCPU = 4

$SQLMEM = 4

$SQLIOPS = 2000

$SQLMAXResourcePools = 10

$SQLSVRName = ‘desktop-ojt0lg6’

$svrgrp = Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -token $token -DisableCertificateValidation:$cert | Where-Object {$_.GroupName -eq $GRPNAME}

Add-MgmtSvcSqlHostingServer -adminuri $adminapi -Token $token -DisableCertificateValidation:$cert -SqlUser $cred -Name $SQLSVRName -TotalSpaceMB $SQLSize -ServerGroupId $svrgrp.GroupId -NumberOfCpuCores $SQLCPU -TotalMemoryGB $SQLMEM -SupportedIopsPerVolume $SQLIOPS -MaximumResourcePools $SQLMAXResourcePools

This is where I will leave you on this post, but stay turned for future posts around adding the SQL Server hosting group to a Plan.

Good Luck

Steve

Windows Azure Pack PowerShell sample commands – Remove Commands

Windows Azure Pack PowerShell sample commands – Remove Commands

To close out the series of the simple sample commands for Azure Pack the first being to Get the Second to Add and this one will go over the process of Removing objects from Azure Pack. Much like the first two posts the first 33 lines are the same which obtains the connection details for your Azure Pack environment, along with an authentication token.

Once we have obtained these details we will have the starting point to remove each of the objects we created in the second blog post, this needs to be completed in reverse where the Subscription is required to be removed before the Plan can be removed. Once both have been removed then the User object can be removed.

You will note that for both the subscription and the plan we need to obtain the Subscription ID and Plan ID respectively. Rather than typing these in I have added the command to obtain it from the name of the Subscription and the Plan.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Remove Subscription

$SubscriptionName = ‘Steve’

$subscriptionID = (Get-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert | Where-Object {$_.SubscriptionName -eq $SubscriptionName}).SubscriptionID

Remove-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -SubscriptionId $subscriptionID

 

# remove Plan

$planName = ‘steve’

$planid = (Get-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -DisplayName $planName).id

Remove-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -PlanId $planid

 

# remove User

$name = ‘Steve’

Remove-MgmtSvcUser -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -Name $name

This concludes the Blog posts on the Simple administrative tasks for Azure Pack, if you have followed each of the blogs that we have found, created and deleted each of the 3 object types, but once the Subscription is associated to a user there are no resources allocated to it. In the next group of Blog posts we will go over the process to allocate resources to the Plan/Subscription. But until then.

Good Luck.

Steve

Windows Azure Pack PowerShell sample commands – Add Commands

Windows Azure Pack PowerShell sample commands – Add Commands

To follow on from my last post where I detailed the process of obtaining the basic details from Azure Pack, for this post I will detail the process to Add objects into Azure Pack, for example new user’s, plans & subscriptions.

In the PowerShell script below, you’ll note that the 33 lines are identical to the first 33 lines of the previous post, this ensures that all of the variables we are using will be accessible, as always with WordPress sites make sure that you check the quotation makes when copying and pasting into the ISE. The last 13 lines we create a user object, the email account supplied does not need to exist when using the native Azure Pack tenant portal authentication, this should be swapped out to ADFS or equivalent authentication solutions when in production.

You will also note that when creating the subscription we obtain a variable called $Plan which we then use the ID from for the new Subscription, this saves us the requirement of remembering the ID which looks something like this: 1fc6dc79 which isn’t very easy to recall.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Add new User

$Email = ‘steve@laptop.com’ #user email address

$name = ‘Steve’ # Name of user

Add-MgmtSvcUser -AdminUri $AdminAPI -Email $Email -name $name -Token $token -DisableCertificateValidation:$cert

 

# Add Plan

$displayname = ‘steve’ # Name of the Plan

Add-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -DisplayName $displayname

 

# Add Subscription

$SubscriptionName = ‘Steve’ # Name of the Subscription

$PlanName = ‘Steve’ # Name of the Plan

$Email = ‘steve@laptop.com’ #user email address

$uname = ‘Steve’ # Name of user

$plan = get-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -DisplayName $Planname

Add-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -AccountAdminLiveEmailId $Email -AccountAdminLivePuid $uname -PlanId $plan.id -FriendlyName $SubscriptionName

 

Like previously this isn’t an exhaustive list of commands but a starting point.

Good Luck Steve