Friday, January 10, 2020

Data Platform Tips 44 - Azure Automation to manage Azure SQL Database


Image result for azure automationAzure Automation is a cloud based service to manage your Azure environments in an automated way. You can manage the Azure SQL Database through Azure Automation long-running, manual, error-prone, and frequently repeated tasks can be automated to increase reliability, efficiency for your organisation.



a) Logon to the Azure Portal.

b) Create a new Automation Service on the Azure Portal.






b) Let us create a Runbook to execute a command over the Azure SQL Database. This can be done by importing the "How to use a SQL Command in an Azure Automation Runbook" from the Runbook gallery.










c) Import the Runbook with a Runbook name.


















































d) Now you can see SQLCommandAutomation Runbook created.






e) Now we need to provision a Azure SQL Database if one doesn't exist already and make sure to allow Azure Services to access the database and create the "recipes" table and enter few records.

CREATE TABLE recipes (

  recipe_id INT NOT NULL,

  recipe_name VARCHAR(30) NOT NULL,

  PRIMARY KEY (recipe_id),

  UNIQUE (recipe_name)

);



INSERT INTO recipes

    (recipe_id, recipe_name)

VALUES

    (1,'Tacos'),

    (2,'Tomato Soup'),

    (3,'Grilled Cheese');











f) Let us create a credential with the username and password that is used to access the Azure SQL Database.


























g) Now let us execute the runbook by providing the required parameters and clicking on Start. The script will provide the count of rows from the recipes table.


h) You can also schedule the runbook to run for weekly, monthly, specific days of the week or days of the month, or a particular day of the month.

No comments:

Post a Comment