Enable logging to Log Analytics Workspace on Azure SQL Managed Instances using PowerShell

Enable logging on Azure SQL Managed Instances using PowerShell

 

Just a quick post to share an easy way to enable logging on your Azure SQL managed Instances using a simple PowerShell script:

#Log analytics workspace resource group name
$OMSRG = “prd-rg01”
#Log analytics workspace name
$OMSWSName = “log-prd01”
#Get Log analytics workspace ID
$WS = Get-AzureRmOperationalInsightsWorkspace
-ResourceGroupName $OMSRG -Name $OMSWSName
$WSId = $WS.ResourceId
$SQLInstanceNAme = “prd-msql01”
$RGName = “prd-rg01”
#Get SQL managed instace server
$SQLMI = Get-AzureRmSqlInstance -Name $SQLInstanceNAme -ResourceGroupName $RGName
$SQLMIID = $SQLMI.Id
$sqlserverdiagname = $SQLInstanceNAme+”-diag”
#Enable diagnositic settings to Log analytics for the SQL instance.
Set-AzureRmDiagnosticSetting -ResourceId $SQLMIID -WorkspaceId $WSId -Enabled $true -Name $sqlserverdiagname
#Get Managed SQL instance DB names
$SQLManagedInstanceDBS = Get-AzureRmSqlInstanceDatabase -InstanceName $SQLInstanceNAme -ResourceGroupName $RGName
#iterate through each DB to enable logging to the log analytics workspace.
foreach ($db in $SQLManagedInstanceDBS)
{
$SQLMIDBID=$db.Id
$diagname=$db.name+”-diag”
$SQLMIDBID
$diagname
Set-AzureRmDiagnosticSetting-ResourceId $SQLMIDBID-WorkspaceId $WSId-Enabled $true-Name $diagname
}
#It can take a while for the portal to show the config change to check this with PS just run this commnad
#Add the resource ID of Managed instance server or DB and it will show you what is enabled and the workspace it is configured to use.
#Get-AzDiagnosticSetting -ResourceId
Please follow and like us:

Demo of Honeycomb.io – Awesome Observability

 

 

 

Honeycomb.io provides real-time system debugging, distributed tracing for microservices,and logging, alerting, dashboards, and observability for services. Architecting, planning, configuring, deploying, and maintaining an internal equivalent would cost orders of magnitude more than using Honeycomb’s service.

I wanted to improve the visibility of our Azure hosted platform and thought I would give Honeycomb a go.

I decided to get something simple setup and managed to go from having no account to being able to search data from our NGINX server in much less than 5 minutes which I thought was pretty impressive!

I have a CentOS VM hosted in Azure which runs NGINX, I wanted to be able to search through its logs without having to SSH into the server.

I decided to setup a Free Trial of Honeycomb which is just a matter of simply signing up with your work email and verifying that address.

Once confirmed I was presented with options of what data I wanted to ingest:

For this, I chose NGINX and was presented with instructions of how to install the service on my server:

The above install instructions installed a binary file but I found I got a “honeytail – command not found” error. So I used the following instead (which I found here):

wget -q https://honeycomb.io/download/honeytail/linux/honeytail_1.733_amd64.deb && \
echo ‘bd135df2accd04d37df31aa4f83bd70227666690143829e2866de8086a1491d2 honeytail_1.733_amd64.deb’ | sha256sum -c && \
sudo dpkg -i honeytail_1.733_amd64.deb

Once installed, the next step should run honeytail and send the data into your honeycomb account.

The first error I got using the instructions above was “permission denied” so I had to use it using “sudo” – This will obviously depend on your setup

The next error complained about it missing the required option “–nginx.conf=”

The final error I got was “log_format” missing in given config. You can find this in your nginx.conf, mine was “upstreamlog” but again this will depend on your environment.

In the end, the command I ran was the below:

sudo honeytail –parser=nginx \
–writekey=xxxxxx1111111111xxxxxxxxxxx \
–nginx.conf=/etc/nginx/nginx.conf \
–dataset=”NGINX Logs” \
–nginx.format=upstreamlog \
–file=/var/log/nginx/access.log –backfill

Now this server doesn’t get much traffic so there wasn’t loads of data but within about a second the data was in my honeycomb account and I had an email to confirm this.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I liked how it had parsed it and displayed the schema of your data:

From here you can query any of your data in real-time. It really lets you ask questions on the fly that look more like business intelligence queries, over arbitrary keys and values.

The nearest product I have used like this is probably Sumo Logic, which is similar in the real-time query and sharing these queries amongst teams etc.

However, I have to say Honeycomb was far better!

It just felt like it was built by people who care and I am sure it will continue to grow as we try to get away from traditional “monitoring” metrics.

I was amazed by how quick I managed to get this simple demo configured. Even with a couple of issues (Which I am sure were more to do with me / my environment, than Honeycomb) it still took less than 5 minutes from start to finish!

Grab yourself a Demo account here and have a go yourself.

Hope this was helpful!

Any questions just get in touch via Twitter

Also, from the Honeycomb side – CEO Charity Majors

 

Please follow and like us: