Category Archives: Azure Data Platform

SQL Server Agent for Azure SQL Database, Azure Elastic Database Pools & Azure Managed Instance

As you all know, the SQL Server agent is not available in the Azure SQL Database & Elastic database pool. When Microsoft introduced the Azure managed instance, the agent was also included in the Managed instance.
Now most vendors I work with, when they want to deploy their database, they also request a few jobs to be created which will execute various tasks at specific moments during the day. Today I will show you what option you have when you have to do those tasks with Azure SQL database, Azure Elastic Database Pools & Azure Managed Instance.

The main objective I would like to achieve is execute following query on a regular interval of 15 minutes.

declare @orderid int (select top 1 OrderID from dbo.Sales where payed = 0);
update dbo.Sales
set payed = 1
where OrderID = @orderid;

Scenario 1: Azure SQL Database & Elastic Database Pools

As you know, the SQL server agent is not available in Azure SQL Database & Elastic Database Pools, so we will have to use other components inside the azure stack to get this to work.

The option I choose and will show you is executing this command using Azure Data Factory

I start by creating an Azure data factory in my azure Portal, this is quite straightforward you can find a tutorial at https://docs.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal

The thing I will need to create is called a pipeline in ADF, in the GUI on the Azure portal you can click on create pipeline.

After clicking this we get the following screen

When looking at the possible activities, we don’t see a SQLCMD, but under general we can find stored procedure.

As we are SQL people, turning this SQL command into a Stored Procedure is not the hardest thing to do.

CREATE PROCEDURE dbo.UpdateSales
AS
BEGIN
declare @orderid int (select top 1 OrderID from dbo.Sales where payed = 0);
update dbo.Sales
set payed = 1
where OrderID = @orderid;
END

With the stored procedure created, we can now start configuring the pipeline & stored procedure component.

We start by naming the pipeline to something more usefull then pipeline1, let’s call it Update Sales

We then drag the Stored Procedure into the pipeline to the right

We then give the stored procedure component a name, let’s call it exec UpdateSales, and then go to the SQL Account tab

We click on the +new button and we get following screen

We fill in the name, select the type, our Azure SQL Database & the Server name where the database is hosted on.

We choose the DBName & SQL Authentication and give in the SQL user which is going to execute the stored procedure. We test and click finish

After creating the Linked Service, we go to the tab of Stored Procedure and select the stored procedure we want to execute, which is dbo.UpdateSales in our case.

After this our Pipeline is ready, we press validate & publish all.

Now the thing we need to do is schedule this task to run every 15 minutes, in ADF this can be done using a trigger. We open up the pipeline and click on trigger.

And choose new/ edit, we click on new in the following screen.

We fill in the name & fill in the start date in UTC , recurrence every minute and then say every 15 minutes. We want this to run indefinitely so we choose no end date.

We click next to continue

We then click finish & publish the changes

We then wait for the trigger to finish in its schedule & check the Monitor tab on the left, as we can see the stored procedure executed successfully and ran in 8 seconds.

So it is possible to create recurring tasks like in an agent using Azure data factory.

But when presenting the solution to our vendor, he says we will deploy multiple new jobs every month & we only know how to do it to a SQL Server agent & we do not want to do double coding just because you want to use azure SQL databases. Is there another option you can present me where I do not have to change a thing & still be in the cloud? Yes, there is: Azure Managed Instance

Scenario 2: Azure Managed Instance

I start off by connecting to my Azure Managed instance

As you can see we have an agent available here, so we can just create the job with the SQL Command in there

We then schedule the job for every 15 minutes & this all works like an on premise SQL Server.

This means our vendor does not have to change a thing, which means the vendor is now happy & comfortable to move to the Cloud!

Conclusion

We can set up a sort of Agent using Azure Data Factory for Azure SQL Databases & Azure Elastic Database Pools. We can do this by using a pipeline & a trigger with the Stored Procedure Component. This is a workaround but works fine, however it will require some redevelopment from the vendor.
Using managed instances, we do not have to change anything to the code of the vendor and are able to run the jobs on the Managed instance in the cloud.

Next post will be on SSIS packaged and how to handle them with Azure SQL Databases, Azure Elastic Database Pools & Azure Managed instances.

Stay Tuned!

Advertisements
Tagged , , , , , ,

Cross Database Queries in Azure SQL Database, Elastic Database Pool & Managed Instance

When suggesting to move third party applications to the cloud, we generally get a pessimistic response from the vendor. The vendors I work with, usually have a lot of old legacy code in their databases which is holding back the move to the PAAS services. One of those pitfalls for a successful move to Azure is cross database queries.

Today I will give you a scenario of a vendor, who has 2 databases who are interconnected through cross database queries. For the sake of the demo’s I have created 2 azure SQL databases, 2 databases in an azure elastic database pool & 2 databases on an Azure Managed instance.

The query I would like to be able to execute is the following

select
*
from ssd.Orders o
inner join RealtimeAnalyticsCrossQuery.dbo.Customer c on o.CustomerPersonID = c.CustomerID

Let’s see what our options are to get the same results as the on premise solution.

Scenario 1: Azure SQL Database & Elastic Database Pools

When I try to execute the query on an azure sql database server which has the following databases in it

select
*
from ssd.Orders o
inner join RealtimeAnalyticsCrossQuery.dbo.Customer c on o.CustomerPersonID = c.CustomerID

I get the following error

Msg 40515, Level 15, State 1, Line 19
Reference to database and/or server name in ‘RealtimeAnalyticsCrossQuery.dbo.Customer’ is not supported in this version of SQL Server.

The message is quite straightforward; we cannot use cross database queries even if they are on the same server. It is not possible to get around this, so to get these queries to work we will have to rewrite the query.

First thing I tried was using openrowset.

SELECT
*
FROM ssd.Orders o
inner join OPENROWSET(‘SQLNCLI11’,
‘SERVER=stijnpassdemo.database.windows.net;DATABASE=RealtimeAnalyticsCrossQuery;Uid=SQLStijn;Pwd=”*******”;’,‘select * from dbo.Customer’)
on o.CustomerPersonID and c.CustomerID

When executing this query, I get a different error

Msg 40526, Level 16, State 1, Line 5
‘OPENROWSET’ rowset provider not supported in this version of SQL Server.

Message is straightforward, not supported!

Another option is to try and create a synonym for this database we get the following error

CREATE SYNONYM CrossQueryCustomer
FOR RealtimeAnalyticsCrossQuery.dbo.Customer

Then we get the following error

Msg 40515, Level 15, State 1, Line 19
Reference to database and/or server name in ‘RealtimeAnalyticsCrossQuery.dbo.Customer’ is not supported in this version of SQL Server.

When looking in the documentation and as the error clearly says: it is not supported (More Information: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-synonym-transact-sql?view=sql-server-2017)

Windows Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

To get this to work we should use an external data source, let’s try to set it up and see if it works

To create an external data source, we must start off with creating a database master key.

CREATE MASTER KEY — Password is optional

When using Azure SQL Databases, we do not have to give a password. (More information: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-2017)

After creating the database master key, we will need a database scoped credential which will be used to create the external data source. (More information: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-2017)

CREATE DATABASE SCOPED CREDENTIAL [SQLStijn]
WITH IDENTITY ‘SQLStijn’,
SECRET ‘*********’

After creating this database scoped credential, we are able to create the external data source. (More information: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-2017)

CREATE EXTERNAL DATA SOURCE dsCrossQuery
WITH (TYPE RDBMS,
      LOCATION ‘stijnpassdemo.database.windows.net’,
      DATABASE_NAME ‘RealtimeAnalyticsCrossQuery’,
      CREDENTIAL = SQLStijn
     )

After creating the external data source, we are able to create an external table, which then can be queried by aliasing it in the query I wanted to execute. I choose to create a new schema to be able to differentiate external tables from tables on my current azure database. (More Information: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-2017 )

CREATE SCHEMA [CROSSQ]
GO
CREATE EXTERNAL TABLE [CROSSQ].[Customer](
[CustomerID] [int] NOT NULL,
[FirstName] [nvarchar](50NOT NULL,
[MiddleName] [nvarchar](50NULL,
[LastName] [nvarchar](50NOT NULL
)
WITH
(
    DATA_SOURCE = dsCrossQuery,
    SCHEMA_NAME ‘dbo’,
    OBJECT_NAME ‘Customer’
);

After creating the external table, we can alter the syntax of our query which we want to execute and see if we can now query data from the other SQL database.

select
    *
from ssd.Orders o
inner join CROSSQ.Customer c on o.CustomerPersonID = c.CustomerID

When we execute this query, we get our results and are able to do a cross database query using the external table.

Following solution also works for Elastic Database Pools.

However, our third party application vendor is not pleased, he will have to rework all his code to get his database to run on Azure SQL Databases. This will take a few years to develop, which means we will have to keep the on premise servers for those application until the code change, if the code change ever comes. Is there another option we have? Yes, there is, Azure Managed Instances.

Scenario 2: Azure Managed Instance

I’ve set up two databases on my azure managed instance: RealtimeAnalytics & RealtimeAnalyticsCrossQuery.

Let’s see if I can execute the query.

select
*
from ssd.Orders o
inner join RealtimeAnalyticsCrossQuery.dbo.Customer c on o.CustomerPersonID = c.CustomerID

As this is a full instance in the cloud, cross database queries are supported, so the results are there!!!

Awesome! This means we can easily move those legacy cross database query applications to the Azure managed instance without having them chance the code. This greatly increases the chances of moving databases to the cloud.

In the next blogpost, I will be handling SQL Server agent jobs & how to deal with them on Azure SQL Databases & Managed Instances.

Stay Tuned

Tagged , , , , , ,