Azure Runbook

Needing to setup a process for on-demand and scheduled refresh of Non-Prod Databases from Prod, all of which were hosted in different Subscriptions. I set about Googling to see if anyone had posted about similar, Et Voila, I found a post by mohammad_belbaisi on the MS Azure blog page:

how-to-copy-an-azure-sql-database-to-a-different-server-or-subscription-using-az

This gave me the jist of what I needed, all I had to do was add an additional step to suit my purpose better: to drop the target database if it existed. Setting this up, I discovered Webhooks. I cover that toward the end of the post.

To start off, I created the Sql Login & User required on the Source Server:

--Create login and user in the master database of the source server.
CREATE LOGIN [dbcopy] WITH PASSWORD = 'xxxxxxxxxxxxxxxx'  
GO  
CREATE USER [dbcopy] FOR LOGIN [dbcopy] WITH DEFAULT_SCHEMA=[dbo];  
GO  
ALTER ROLE dbmanager ADD MEMBER [dbcopy];  
GO  

Change the session to the database that is to be copied
--Create the user in the source database and grant dbowner permission to the database.
CREATE USER [dbcopy] FOR LOGIN [dbcopy] WITH DEFAULT_SCHEMA=[dbo];  
GO  
ALTER ROLE db_owner ADD MEMBER [dbcopy];  
GO  
        

Then, on the Target server, a Login with the same name and SID had to be created. To get the SID, query the sysusers table, you'll get something along the lines of this: 0x01060000000000640000000000000000EDF1BE913FD3784EA4C15370099F0BAA

SELECT [sid] FROM sysusers WHERE [name] = 'dbcopy'        

Then on the Target server, create the login, same name & SID

CREATE LOGIN [dbcopy] WITH PASSWORD =  'xxxxxxxxxxxxxxxx'   SID = 0x01060000000000640000000000000000EDF1BE913FD3784EA4C15370099F0BAA
GO  
CREATE USER [dbcopy] FOR LOGIN [dbcopy] WITH DEFAULT_SCHEMA=[dbo];  
GO  
ALTER ROLE dbmanager ADD MEMBER [dbcopy];  
GO  
        

Once you've done that, the next step is to create an Automation Account in Azure.

Provide the required information, ensure the Subscription chosen is the correct one for hosting the Runbooks.

Once that’s done, go to the Modules tab and import the sqlserver module

Select “Browse from gallery”, click on the “click here….”

Type in sqlserver, hit enter, and then select the sqlserver module.

*As all the other modules were 5.1, I selected 5.1 for the sqlserver module.

Now we move on to creating the Credential.

Open the Automation Account page for the account you created in the ‘Automation Account’ step above. In there expand ‘Shared Resources’ and go to ‘Credentials’ and add a Credential the same name and password as the Login you created in the first step ‘Sql Login’.

Once that’s done, we need to create a variable to hold the name of the database to be copied and the Source & Target Sql Servers. Whilst this can be done in the Portal, I’m going to move onto using Visual Studio Code. VS Code is much simpler to use and far easier when we get to the code that’s going to be run by the Runbook.

Open VS Code, ensure you have the Azure extension installed, and expand the Subscription where you’ve hosted the Runbook and create he variables that will be used.

Right click on ‘fx variables’ and choose create, and enter the relevant values.

Here you can see the parameters that I’ve created for Source & Target, an important point to remember is that the value provided for the Target server must be the Fully Qualified Domain name (fqdn)

Once the variables are created, now move on to creating the runbook. Rick click Runbooks and select create. Now start with entering a step to get the parameter values from the parameters we just crated in the previous step.

I then check if the database exists….

If it does, I drop it….

Then, I take a 30 second sleep, to allow the drop to be completed. Then start the process for copying down the database….

Once you’ve got your script as you want it, and you may want a step for dropping the Users that were copied down form the Source, and create new Users in the Target, but when you’re ready, right click the runbook and either upload as draft or publish? runbook. I’ve uploaded as draft.

I then run it…

You’ll see a new page open in your VS Code, that tracks the Runbook as it runs…..if it succeeds, then publish it.

Now when we go back to the Azure portal, we’ll see the Runbook showing as published.

We’ve come back to the portal, as we can’t create a schedule in the Azure Automation of VS Code. So either we get with New-AzAutomationSchedule to create a schedule and then link it using Register-AzureAutomationScheduledRunbook or we do it in the portal.

Create your schedule….

Then go back to your Runbook, and link it to the schedule

At the scheduled time, you should see your database being copied down.

Here's the interesting bit: Webhook

You can also call the Runbook by way of a Webhook, which you can do using an application or script.

*Beware: a Webhook can be called by anyone.

You can see it running and when it completes? in the Runbooks Jobs -> Schedules tab.

Incidentally, a quick way to export the code you’ve got in your Runbook, is to use the export button that’s displayed in the Runbook page.

You can also quickly view the code being run by using the view button