elastic query for update and insert

Question

Source: SQL Server 2008 R2
Destination: Azure SQL Database in an elastic pool

I am migrating multiple databases from source to destination using offline DMS. While migrating these databases some stored procedures are giving errors because of three-part and four-part entries in the stored procedure. for this, I am using external tables to convert it into two-part. but some stored procedures are used for DML commands like insert and update. I want to know how I can convert this DML commands stored procedure in elastic query.

Answer 1

Good day Shubhi Jain < @Shubhi Jain >

I want to know how I can convert this DML commands stored procedure in elastic query.

DO you mean to use DML queries (INSERT, UPDATE, DELETE) on external tables?

These are not supported in Azure SQL Database External table, as documented here:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azuresqldb-current&preserve-view=true&tabs=dedicated

Answer 2

Hi,

We cannot make 1+1 to be 3 but maybe we do not need the result of 1+1 if all we needed is to get 3

If your goal is to migrating multiple databases from source to destination (from SQL Server 2008 R2 to Azure SQL Database in an elastic pool) then we can do it in other ways without using Elastic Queries. We can automate the procedure on database by database and not must to do it manually one by one for example. Moreover, you speak about doing the task offline which give us aa lot more flexibly and options.

for this, I am using external tables... I want to know how I can convert this DML commands stored procedure in elastic query.

So, your original question focused on "how to do X" (use DML command in external tables) but what you really need is "doing Y" (create copy of the Stored Procedure which uses DML queries): This by the way call "The XY problem" and it is very common in forums.

This approach do not fit your needs so, We just need to find the solution which fit your need. For this we might need more information and understand your business logic, or just start with suggestions and check if these fit you in the meantime.

As I understand, the issue is that the SP use three-part and four-part names.

Three parts name means that the query uses entity in the same server (DatabaseName.SchemaName.TableName for example). Four parts name means that the query uses entity in a different server usually (ServerName.DatabaseName.SchemaName.TableName for example). I assume that you use a different database/server (if not then your solution is simply to change the four/three-parts name into two parts name but removing the server/database part.

Without fully understand your business logic and your current system, I cannot advice you on specific solution but maybe one of these can fit your needs if you must migrate to the Azure:

Optional solution: In first glance, if you must migrate to the Azure, then it seems like using "Azure SQL Managed Instance" will fit your needs.

Optional solution: change your entire architecture and remove the need of using multiple databases in different servers by merging all into single database (might fit in some cases)

Optional solution: Chnage your SP and use external application for the task of using data from multiple databases/servers.

Optional solution (recommended in many such cases): Re-design your SP to use elastic database query feature which supports Cross-Database Queries and use sp_execute_remote.

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-remote-azure-sql-database

In any case, the migration will probably not be "click and forget" and you will need to re-design your solution :-)