Lesson Learned #473:Harnessing the Synergy of Linked Server, Python, and sp_execute_external_script

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community – Latest Blogs – .

In an era where data management transcends individual database systems, SQL Server offers a sophisticated feature set that includes Linked Server integration, Python scripting, and the powerful sp_execute_external_script function. The main objective of this approach is to leverage a Python script within SQL Server using sp_execute_external_script connecting to other database outside of SQL Server On-premise, for example, Azure SQL Database or Azure SQL Managed Instance as an alternative to employing the pyodbc library. This method not only streamlines processes but also addresses key concerns in security and network configuration, such as opening ports, which are prevalent when using external libraries for database connections. By focusing on querying a Linked Server, we can achieve seamless data integration and manipulation while maintaining a secure and efficient environment.

Section 1: Unpacking Linked Servers in SQL Server

Linked Servers act as bridges, enabling SQL Server to execute commands and access data across different database systems. This capability is crucial for enterprises managing data across multiple platforms, offering a unified approach to data interaction. Utilizing Linked Servers, SQL Server can effectively communicate with various data sources, ensuring flexibility and scalability in data management.

Section 2: The Power of Python in SQL Server

The integration of Python into SQL Server, particularly through the sp_execute_external_script function, marks a significant advancement in data processing capabilities. This integration allows for the utilization of Python’s comprehensive libraries and analytical prowess directly within the SQL Server environment. It opens doors to sophisticated data analysis, complex transformations, and advanced machine learning applications, all while leveraging the robust security and performance features of SQL Server.

Section 3: Preparing the Groundwork

To embark on this integration, certain prerequisites must be met. This includes enabling SQL Server Machine Learning Services for Python support and configuring a Linked Server for external data access. Detailed steps guide you through this setup process, ensuring a smooth integration.

Section 4: Executing a Practical Use-case

We present a practical scenario where sp_execute_external_script is employed to query data from a Linked Server. The walkthrough covers creating a stored procedure that harnesses Python’s prowess to access and process data from an external database, illustrating the script’s development and execution.

Definition of Linked Server

USE [master]
GO

/****** Object:  LinkedServer [MYSERVER2]    Script Date: 11/01/2024 19:07:42 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYSERVER2', @srvproduct=N'', @Provider=N'MSOLEDBSQL', @datasrc=N'tcp:servername.database.windows.net,1433', @catalog=N'dotnetexample'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSERVER2',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='########'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSERVER2', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Store procedure definition

CREATE PROCEDURE FetchDataFromLinkedServer
AS
BEGIN
    EXEC sp_execute_external_script
        @language = N'Python',
        @script = N'
import pandas as pd
customer_data = my_input_data
OutputDataSet = customer_data
',
        @input_data_1 = N'SELECT TOP 50 ID, TextToSearch FROM [MyServer2].[dotnetexample].[dbo].[PErformanceVarcharNvarchar]',
        @input_data_1_name = N'my_input_data'
		WITH RESULT SETS ((ID INT NOT NULL, TextToSearch VARCHAR(200) NOT NULL));
END

Just we need to call our store procedure to obtain the data from another datasource.