Monitoring windows docker containers using Application Insights Status Monitor

There is severe shortage of tools for monitoring windows containers in general and specifically from inside running container OS. Steps outlined below will allow you to get basic OS health information (perfmon counters) as well as application level monitors from ASP.NET application.

Overview of the steps a below

  1. Create application insights resource in Azure
  2. Install and enable Application Insights status monitor into container
  3. Modify configuration file to add additional monitoring counters
  • Create application insights resource in Azure


New-AzureRmApplicationInsights -ResourceGroupName artisticcheese -Name appinsights -Location SouthCentralUS -Kind web

After resource is created find instrumentation key for it to supply to status monitor during runtime


PS Azure:\> Get-AzureRmApplicationInsights -Name appinsights -ResourceGroupName artisticcheese | select InstrumentationKey

InstrumentationKey
------------------
3c069cb6-fc1a-4bab-974c-0a4245ae7f1b

  • Install Application Insights Status Monitor inside container

Application Insights Status Monitor is distributed via WebPI installer which is dependent on UI part for installation which obviously is not going to work neither for server core installation or windows docker container. Below is extracted necessary parts to make it work in windows container (shall work in server core install as well)

 

  • Test application and integration

If you onboarded Azure AppInsights successfully you shall be able to see your live container under Live Metric Stream. It will show your current vital status of your container like memory use/CPU and Request information in real time.

ApplicationFrameHost_2018-03-12_15-55-49

Make couple of hits to application to populate Application Insights data in Azure.


PS C:\> Invoke-WebRequest http://172.17.228.181/Service.svc/qod | select Content

Content
-------
Sustenance! Your health is always the best prescription. 

If you go to any of the captured transactions now you shall be able to see breakdown of entire pipeline (including call to a backend dependency)

ApplicationFrameHost_2018-03-13_10-13-17

API which is being used (quote of the day) is limited to 10 hits an hour, continue hitting on web service 10 more time and you shall be start seeing failures in calls reflected inside AppInsights as well

ApplicationFrameHost_2018-03-13_10-19-13

ApplicationFrameHost_2018-03-13_10-20-01

  • Modify configuration file for adding additional monitoring data

You can add additonal perfmon data to be logged to application insights portal in addition to default set for ASP.NET. Open ApplicationInsights.config file in root of your application and check commented part under <Add Type=”Microsoft.ApplicationInsights.Extensibility.PerfCounterCollector.PerformanceCollectorModule, Microsoft.AI.PerfCounterCollector”> tag

You can check how this works by checking repo (https://github.com/artisticcheese/artisticcheesecontainer/tree/master/appinsightscontainer). Image is also published to docker hub as artisticcheese/appinsights. Since I put my appinsights instrumentation key there it’s not going to help much but you can clone my repo and put your own key and see how it works.

 

Advertisements

Step by step process of setting up geographically distributed SQL HA cluster on Azure

Prerequisites:

2 VMs setup with SQL 2016 in different geo regions and 2 different VNETs.

Instructions:

Create connection between 2 VNETs. There is 2 ways to do that. VNET peering and VNET gateway. Example below is using VNET peering

  • Go to CanadaCentral VNET and click on peering and click “Add”

  • Create VNET peering between CanadaCentral and US West Central

  • Do the same from US West Central VNET

  • Verify connectivity between subnets by logging to SQLCanada and testing connection to port 1433 of SQLWestCentral
PS C:\Users\cloudadmin&amp;gt; Test-NetConnection 10.1.0.6 -port 1433

ComputerName : 10.1.0.6
RemoteAddress : 10.1.0.6
RemotePort : 1433
InterfaceAlias : Ethernet
SourceAddress : 10.0.4.4
TcpTestSucceeded : True

Enable SQL Always On High Availability Group

Create failover cluster

  • Install Failover cluster tools and features on Windows on both SQL servers
PS C:\Users\cloudadmin&amp;gt; Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

Success Restart Needed Exit Code Feature Result
------- -------------- --------- --------------
True No Success {Failover Clustering, Remote Server Admini...
  • Install DNS service on both nodes (external DNS service can be used as well)
PS C:\Users\cloudadmin&amp;gt; install-windowsfeature -Name dns -IncludeManagementTools
WARNING: The following recommended condition is not met for DNS: No static IP addresses were found on this computer. If
the IP address changes, clients might not be able to contact this server. Please configure a static IP address on this
computer before installing DNS Server.

Success Restart Needed Exit Code Feature Result
------- -------------- --------- --------------
True No Success {DNS Server, DNS Server Tools, Role Admini...
  • Open DNS management tool on SQLCanada and create a new primary zone

Name it anything you want, in my case it’s “cluster.local”

Enable “non secure dynamic updates”

Right click on properties of newly created zone and switch to “Zone Transfers Tab” and click to allow transfer “to any server”

Go to Virtual Network of CanadaCentral and click on DNS servers tab/Choose custom and enter internal IP addresses of SQLCanada server and SQLWestCentral

Do the same for USWestCentral VNET

Reboot both SQL servers for new DNS servers to take effect

Create DNS zone on SQLWestCentral, right click on “Forward Look up zone/New Zone”, choose “Secondary as Type”

Enter zone name

Put IP address of SQLCanada

Finish wizard and you will see zone with records in it

  • Open failover cluster manager on SQLCanada

  • Choose “Create Cluster”

  • Enter server name in wizard when prompted

  • Run through verify cluster configuration wizard
  • If everything is successful, choose a cluster name

  • Wizard shall be completed now

Configure cloud witness

  • Create storage account in any region in Azure

  • Go to “Access Keys” and copy storage account name as well as Key

  • Go back Failover Cluster Manager, right click on cluster and choose “Configure Cluster Forum Settings”

  • Choose “Select Quorum Witness” in wizard

  • Choose “configure a cloud witness”

  • Paste in values you copied from storage account settings page earlier. Finish wizard

Configure SQL HAG

  • On both SQLWestCentral and SQLCanada server add the same DNS suffix in properties for computer

  • Configure static IP addresses on both computers and in Azure portal

Example below from SQLWestCentral

  • Add SQLWestCentral server to Failover cluster. Go to Failover Cluster Manager and choose “Add node”

  • Run the validation steps and make sure there are no errors reported (ignore Active Directory errors)
  • Add node to cluster. End result shall show both nodes online and healthy

Create certificates for mirroring endpoint

Since servers are not domain joined you would need to use certificate based authentication between instances.

  • On SQLCanada server executed following TSQL
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
  • Create Encrypted certificate in master database
USE master;
CREATE CERTIFICATE SQLCanada
WITH SUBJECT = 'HOST_A certificate for database mirroring',
EXPIRY_DATE = '11/30/2113';
GO
  • Delete existing endpoint if it exists
SELECT name
FROM sys.database_mirroring_endpoints;
DROP endpoint Hadr_endpoint
  • Create new endpoint based off certificate
CREATE ENDPOINT Endpoint_Mirroring
 STATE = STARTED
 AS TCP (
 LISTENER_PORT=7024
 , LISTENER_IP = ALL
 )
 FOR DATABASE_MIRRORING (
 AUTHENTICATION = CERTIFICATE SQLCanada
 , ENCRYPTION = REQUIRED ALGORITHM AES
 , ROLE = ALL
 );
 GO
  • Export certificate and copy to server SQLWestCentral
BACKUP CERTIFICATE SQLcanada TO FILE = 'C:\sqlcanada.cer';
  • Perform similar steps above on SQLWestCentral (drop existing endpoint on it first with `DROP endpoint hadr_endpoint`)
USE master;
--Create the database Master Key, if needed.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; 
GO
-- Make a certifcate on sqlwestcentral server instance.
CREATE CERTIFICATE sqlwestcentral
WITH SUBJECT = 'HOST_B certificate for database mirroring',
EXPIRY_DATE = '11/30/2113';
GO
--Create a mirroring endpoint for the server instance on sqlwestcentral .
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE sqlwestcentral
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
--Backup sqlwestcentral certificate.
BACKUP CERTIFICATE sqlwestcentral TO FILE = 'C:\sqlwestcentral.cer';
GO
--Using any secure copy method, copy C:\sqlwestcentral.cer to SQLcanada.
  • On SQLCanada create login for SQLWestCentral
USE master;
CREATE LOGIN SQLWestCentral_login
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
  • Create user for that login
USE master;
 CREATE USER SQLWestCentralUser FOR LOGIN SQLWestCentral_login;
 GO
  • Associate certificate you exported from SQLWestCentral login with this user
USE master;
CREATE CERTIFICATE SQLWestcentral_cert
AUTHORIZATION sqlwestcentraluser
FROM FILE = 'C:\sqlwestcentral.cer'
GO
  • Grant CONNECT permission to login to remote mirroring endpoint
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQLWestCentral_login];
GO
  • Perfom similar steps on SQLWestCentral
USE master;
--On SQLWestCentral, create a login for SQLCanada.
CREATE LOGIN SQLCanada_login WITH PASSWORD = 'AStrongPassword!@#';
GO
--Create a user, SQLCanada_user, for that login.
CREATE USER SQLCanada_user FOR LOGIN SQLCanada_login
GO
--Asscociate this SQLCanada with the user, SQLCanada_user.
CREATE CERTIFICATE SQLCanada_cert
AUTHORIZATION SQLCanada_user
FROM FILE = 'C:\sqlcanada.cer';
GO
--Grant CONNECT permission for the server instance on SQLcanada.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQLCanada_login
  • Run following TSQL on both servers to allow local system account access to create High Availabilty group
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO
  • Go to SQL Server configuration manager and restart SQL server, then right click on server and go to properties/AlwaysOn High Availability on both servers

  • Configure SQL Server to run as “Local System” on both nodes

  • Open SSMS and restore your DB as usual

  • Go to Always On High Availability setting and choose “New Availability Group Wizard”

  • Select DB

  • Click “Add Replica”

  • Accept defaults on next screen

  • If everything is correct your new HAG as well Database SQLHA shall appear under Always On High Availability on both servers

  • You can monitor health of high availability group by right clicking on group and choosing “Show Dashboard”

  • You can use script below to verify connectivity and health of replication. It periodically checks same query executed against sql cluster

import-module sqlserver
function QueryDB{
$mirrorConnString = "Data Source=sqlcanada;Failover Partner=sqlwestcentral;Initial Catalog=sqlha;user id=contained_user;password=A1234567890!;"
(Invoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ConnectionString $mirrorConnString -AbortOnError:$false).Column1 + " " + (Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM ID" -ConnectionString $mirrorConnString).Count
}
while ($true)
{
QueryDB
start-sleep 3
}