Running remote stored procedure with DDL locally Sorry if my question is redundant, I searched Stack Exchange and the internet for hours but do not have a definitive answer to my question.

Database: SQL Server 2017 + Azure

The goal is to build a BI infrastructure

I have multiple (20+) database servers (clients) which all have one common database in the cloud (Azure). All these clients should be exactly the same when these are on the same DB version. To make life easier when doing an update (almost weekly, tuning), I want my 20+ databases to update automatically.

I thought of the following solution:


I create a table in the Azure DB which has the most recent client DB version (e.g. v1.2) check
If I have an update script, I will put this as a stored procedure on the Azure DB. check
I schedule a task on the client which checks the most recent client DB version once a day and compares whether an update is needed. check
If yes -> run the "update": this is the stored procedure on the Azure DB which have to run on the client DB. oh no


Here I get into trouble, running the stored procedure which resides on the Azure DB obviously runs the stored procedure on the Azure DB. I don't know how I can run the stored procedure of the Azure DB on the client DB.

Motivation: I thought it would be clever to do it this way so I only have to change the content of the stored procedure and all clients would update itself when getting a connection with the Azure DB. 

Also, is this the correct way of tackling this problem? Any help is much appreciated!

If you need more information, please let me know.
I don't have access to Azure to verify this all works without tweaking, but here is something you can try.

Here is some information on creating a linked server to Azure that might help you - I have not tested that part - https://stackoverflow.com/a/41145784

On Azure, create the stored procedure that contains the commands you want to execute on each client machine.  My example simply creates a TestUpdateTable and inserts the current timestamp.  I'm sure your stored procedure would be much more involved.

drop procedure if exists [dbo].[UpdateClient];
go
create PROC [dbo].[UpdateClient]
AS
begin transaction
drop table if exists dbo.TestUpdateTable;
create table dbo.TestUpdateTable (TextCol varchar(max));
insert into dbo.TestUpdateTable(TextCol) 
    select convert(varchar(30),sysdatetime());
commit


In the following example, I'm using a linked server connection to a server I call SqlCompare.  I'm also referencing a database called SCUTILITY on that linked server.  This is where I put the central stored procedure.  This simulates what would be in Azure.

On each client machine, run the following dynamic TSQL

Declare @cmd nvarchar(max)

DROP PROCEDURE IF EXISTS dbo.UpdateClient;

SET @cmd = (
        SELECT *
        FROM openquery(SqlCompare, 'exec(''use scutility;select object_definition(object_id(''''dbo.UpdateClient''''))'')')
        )

PRINT @cmd

EXEC (@cmd)
GO


The intent is to drop the existing local client UpdateClient stored procedure first.  Then retrieve the text definition of the Azure stored procedure by using object_definition and put the results in a variable (@cmd).  Issuing EXEC (@cmd) will create the remote stored procedure locally on each client.

Now, execute the UpdateClient stored procedure locally on the client machine.

EXEC dbo.UpdateClient

The execution of the UpdateClient stored procedure just created the table TestUpdateTable and populated it with the current timestamp.  Again, your stored procedure would be more involved.

Now, you could issue select * from TestUpdateTable on the client machine and you should get a result back