Azure Automation – Cloudy DATA Blog

Next select the Azure Database for PostgresSQL Flexible Server

Now indicate the starting parameters
Please note IF you want to connect using PGAdmin I found issues with version 15 so chose version 14 it is not clear why this is some quick googling suggested it was the version of PGAdmin I was using. So at the bottom of this post I capture the about PgAdmin 4 that I am using as a client. I was able to connect to the server vis psql.
Remember all of your parameters and choose an appropriate account

Note you can stop the server if this is a development test copy which is good for reducing costs…

Do this by navigating to the server and selecting the stop button at the top.

Note give it time to start and stop not like me try and start the server before it has finished stopping!

MS Azure : Identify outbound IP addresses from an MS Azure Web App Service

When setting up an Web App Service on Azure which you are wanting to hook up to an Azure Database you will need to ensure that the IP address of the Web App Service is white listed for the Database Server. This is true of Postgres and SQL Azure in my experience. Now when you upload your project to the Web App Service and connect to an SQL Azure the error message indicates what IP address of the web application is failing. That is not the case when trying to connect to Azure Database for Postgres Flexible Server (my experience at September 2023) so how do we find this. As usual the answer is in Azure documentation but thoroughly buried.

One paragraph states

Regardless of the number of scaled-out instances each has a set number of outbound IP addresses at any given time. Any outbound outbound connection from the App Service app, such as to a back end database, uses one of the outbound IP addresses as the origin IP address. The IP address is selected randomly at runtime,

SO YOUR BACK END SERVICE MUST OPEN ITS FIREWALL TO ALL THE OUTBOUND IP ADDRESSES FOR YOUR APP

So how do we find these outbound IP addresses?
Log into the Azure Portal using the administrator account that has access to the Web App Service you want to identify outbound IP Addresses for.

Select the web app service

Look to Networking within Settings menu

Now several boxes should be presented to you towards the right of your browser window identify the one marked Outbound Traffic.

IP Addresses you are interested in should be presented where the red box is. There is a helpful copy button should you need to quickly copy them.

So that is great but what if you are quickly wanting to identify the outbound IP address of a web app

So go to the windows terminal

and use the nslookup command followed by the domain you wish to identify the IP address of.

Postscript
One point is that I seemed to have difficulty registering the outbound IP addresses and it was repeatedly failing to connect a front end application to the Azure Database for Postgres flexible server – I went through my outbound addresses thoroughly and then stopped and started the server itself. Note the restart option wasn’t working for me so I did a complete stop and then start. After this it seemed to work. I will continue to monitor this but its not a process I perform often so it may be sometime before I can truly establish my issue. It should be noted that nslookup did not initially reveal the IP that worked I still don’t know which of the outbounds are working or whether it was the restart that made it work. I did note that the allow all azure connections option always corrected the issue (so this can be used for testing)

MS SQL Azure – TSQL to identify all Triggers in a database / Drop Trigger TSQL syntax

I had been told that it was better not to use Triggers..

Nonetheless I had to learn the hard way.

So fast forward a period and I’m wanting to remove triggers from a database and I first need to find where I had put them in the first instance.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'

And running this against your database you should see something like this.

And how you drop the triggers

IF OBJECT_ID ('sampledelete2chargehistory', 'TR') IS NOT NULL  
   DROP TRIGGER sampledelete2chargehistory;

Thanks to Stack Overflow – I still get some answers from it over AI

Stackoverflow

MS Azure – SQL Azure – Reliability update 001 (38 days) – using Azure Logic App to execute a Stored Procedure

So a month ago in an non critical application I set up a trigger for a stored procedure to copy records (about 3000) from one table to another 6 times a day.

Approximately 38 days in and I have 100% reliability.

I have firewalled the required IP addresses listed by Microsoft
Azure Logic Apps list of IPs for Firewalls

I will keep running this for testing purposes to identify the reliability and report back

SQL Azure – Using Azure Logic Apps to execute a Stored Procedure in SQL Azure

It is important in a lot of systems to be able to run queries on a regular schedule. Previously I have outlined that this can be done using an automation account. It is also possible to accomplish this using an Azure Logic App to call a Stored Procedure.

The procedure is
Create Logic App
Add items to it

Firstly using SSMS connect to the database and create an example stored procedure.

CREATE PROCEDURE [dbo].[TestInsert]
AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.T0003NLPRSitesTarget
(sitename)
SELECT Sitename from dbo.T0003NLPRSites
END
GO

Next log into your Azure Portal and identify the following icon

Select it and then hit the Add button..

You will be presented with the following screen

Now you should be taken to an overview showing that the logic app has been created

Hit Go to resource

You should be presented with the following screen

Here we choose Recurrence

You will be presented with the following Logic Apps Designer

Here is an example
Interval is like how many times the logic app will run over what Frequency
So if we put 3 in Interval and Frequency Day it would run once every 3 days
At these hours – sets specific times when the logic app will run within the set Frequency
On these days – allows for you to set what days the logic app will run At these hours

To explain the logic app running work from the bottom up. So the below example

On Monday, Tuesday , Wednesday, Thursday and Friday at 9,10,11,12,13,14,15,16 and 17 hours

Every week this will run (If Interval had been set to 3 it would have been every three weeks on Monday,Tuesday,Wednesday, Thursday and Friday at the hours 9,10,11,12,13,14,15,16 and 17 hours it would run.

Now hit next step and then type in execute stored procedure

You will first need to set up the connection to the database and then enter the parameters of the server database name database username and password or your chosen security to connect to the database and then you will be taken to the appropriate item.

You will need to set up the firewall on the server to ensure otherwise the logic app is likely to stop working at somepoint..

You can get a list of the appropriate firewall rules to set up by going here..

Managed connectors outbound IP addresses

So after further testing with the following parameters I discovered that in practice SQL Azure is a bit loose about the hours recurrence option. For example the following

Has been runnnig at 43minutes past the hour. Which is indicative of when I hit a run trigger at..

I will be checking to see when it next runs but I suspect it is going to be 17:43

MS Azure – Web App Service – Notes on creating a staging web front end on an existing Web App Service

At some point in developing sites for clients you will may want a satellite site feeding off the main database but with prototype features on it. Considering that certain levels of web app service come with free staging sites you may as well use these rather than either the free service which may be less well provisioned or a separate paid site that you will have to pay additional money for. The number of slots available will depend on the service you are on.

Beginners guide to setting up a staging slot

Once you have the site up and running you operate it in the same way as a normal service and so you can get the publishing details from the Get Publish Profile.

SQL Azure – Azure Automation Hybrid Worker Setup and RunAs Account

UPDATE January 2024 – For SQL Azure you are better using Logic Apps and whitelisting the IP records of the outbound Logic Apps IPs which are specific to the location you are running you are hosting your logic app
See Link for Logic App setup example
and
Azure Logic App IP addresses to White List

My old notes…
The highs and lows.

You’re flying high – you’ve created a stored procedure that you’ve managed to reference in your automation account within a powershell runbook and you’ve got it nicely churning over on an extremely regular basis. You want to run it daily or weekly but as a test you run it hourly just to ensure that it works before releasing it to production. A day passes ok and you think wow this is really great. Then you forget about it and come back to it a week later. You find that somehow it stopped working at 1 am on Tuesday.

What happened????

Sorry you got hit by a block resulting from dynamic IP change on the runbook in the Automation Account – DAMN.
(there is probably a good reason for having a dynamic IP on automation accounts but damn it was really going too well)

So after quite a lot of investigation I found those in the know seemed to be saying the solution is.
Azure Automation linked to a Hybrid Worker

I kept investigating this and I kept getting directed to Microsoft documentation which although really extensive was just not helping me really get my head around things. It wasn’t until I found Travis Roberts that things started to click… I link to three of his videos below.

So the downsides of Hybrid Worker setup..

Despite being the recommended solution configuration is complicated.

You need to create a VM with a static IP just to run the hybrid worker (and then grant firewall access to the databaseserver for that VM)

That VM needs to have 2 cores and 4Gb of Ram (yes unfortunately not the smallest VM so you could be looking at $25 a month charge)

You need to set up log analytics

It is recommended that log analytics is in the SAME Azure location as the sample database BUT not all Azure locations have log analytics set up… Use UK South! Hands up who has already set up their server and just realised it is in a location that doesn’t allow log analytics.

So I was really struggling with this after my runbook unexpectedly started failing. After several days of reading and searching I found Travis Roberts. Hallelujah for MVPs.

So I haven’t done it yet but I think the procedure can all be defined in here.

1. Setting up an Azure VM with a fixed IP (It can be any VM but Azure is good for me)
Setup VM with static IP
The server you setup will need to have internet connectivity with SSL outbound on port 443

2. Setting up Azure Log Analytics a step by step guide
Setting up Azure Log Analytics a step by step guide

3. Azure Automation Setup and Linking Log Analytics to your Automation account
Set up Azure Automation and link it to Log Analytics

4. Configuring an Azure Automation Hybrid Runbook Worker to run your runbook.
Installation of Hybrid Runbook Worker
Includes information about Runbook Worker Group and
RunAsAccount
You add credentials to the Runbook Worker Group

Firstly a big thank you to Travis Roberts for putting out those videos he has loads of Azure Centric content on his youtube channel which you can find here

CAVEAT I haven’t implemented this myself properly and at the time of writing(December 2021) those videos are a couple of years old now. At the start of one of the Videos Travis indicates that things are changing quickly in the space and expect that there has been changes since these videos. Lets face it this should really be easier to do Azure is just frustrating their users with this complicated configuration.

SQL Azure – Using Azure Automation and Runbooks to Schedule an Azure SQL Stored Procedure

UPDATE January 2024 – I have moved away from Runbooks for scheduling Azure SQL stored procedures towards using logic apps but keep this here as its always good to have alternative methods of completing the same task. Link to Logic Apps Setup

Original Post
Its vital to be able to run stored procedures on a schedule it can be extremely important for doing daily housekeeping.

This site was where I learned how to do this.
How to Schedule an Azure SQL Database’s Stored Procedure

Runbooks sit in Azure Automation and a Runbook consists of a script that performs a task.

Runbooks can be written in Python or Powershell. See the last part of this post for an important additional issue.

"Data Source=01server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=01userid;Password=JK12jl423;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"

# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"

$sqlConnection.Open()
Write-Output "Azure SQL database connection opened"

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection

Write-Output "Issuing command to run stored procedure"

# Execute the SQL command
$sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'
$result = $sqlCommand.ExecuteNonQuery()
Write-Output "Stored procedure execution completed"

# Close the SQL connection
$sqlConnection.Close()

Write-Output "Run completed"

Honestly the hardest part is getting the code setting up the scheduling after that is relatively easy.

WARNING : Runbooks have dynamic IPs so although you can set grant access to an IP your runbook will only run for a certain period before it stops working unless you either have a Hybrid Runbook Worker setup OR you allow Azure services and resources to access this server (which isn’t recommended) The next post I will do is my initial notes on how to configure a Hybrid Runbook Worker.

Category: Azure Automation

Postgres on Azure : also named Azure Database for Postgres Flexible Server : MS Azure