Complete SQL Server database migration

[amaaiia]

I have a SQL Server database called db_info with tables, views, stored procedures, functions, synonyms, etc., and linked servers that point to other SQL Servers and MySQL servers.

I have some stored procedures that perform operations with these components and with the linked servers, i.e., the SPs take data from external servers. These SPs are called by external ad hoc reports.

I want to migrate the SQL Server database db_info to Fabric and be able to call those SPs in Fabric. In other words, I need to migrate and expose the same information so that it can be exploited by my external reports.

I have identified at least two problems:
1. I don't know how to handle linked servers to SQL Server and MySQL databases. I'm not going to migrate these; I need the Fabric SPs (from the lakehouse, I guess) to be able to access those databases in a virtualized way, but as far as I know, theese connectors (SQL Server and MySQL) are not supported yet by shortcuts.
2. Some db_info SPs perform operations to create and insert data into temporary tables, and I don't think this can be done in a lakehouse SP.

Any ideas on how can I migrate SQL Server db to Fabric, expose the data and SP, and be able to perform the same operations?

Thanks.

[nielsvdc · Accepted solution]

Hi @amaaiia, you could think about the following.

1. Consider migrating your db_info database to a Warehouse instead of a Fabric SQL Database. Warehouses also support stored procedures and temporary tables.
2. For the SQL Server data that you now extract with linked servers, consider using mirroring the data to Fabric.
3. For the MySQL server data you could also consider using open mirroring. But if that's too complecated you could also consider using pipelines or this notebook fabric-toolbox/samples/open-mirroring/mysql Mirroring/mysqlmirroring.ipynb at main · microsoft/fabri...

Unfortunately we cannot yet create shortcuts in Warehouses (Vote for the idea), so you have to maybe have to use pipelines to get all the data together into the same Warehouse.

Considerations 2-3 also work when you still choose to use Fabric SQL Database

Hope it helps figuring this one out.

[v-dineshya]

Hi @amaaiia ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @nielsvdc , Thank you for your prompt response.

Hi @amaaiia , could you please try the proposed solution shared by  @nielsvdc   ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh