Lesson Learned #469:Implementing a Linked Server Alternative with Azure SQL Database and C#

[Jose_Manuel_Jurado]

In scenarios where direct Linked Server connections are not feasible, such as between Azure SQL Database and an on-premise SQL Server, developers often seek alternative solutions. This blog post introduces a C# implementation that simulates the functionality of a Linked Server for data transfer between Azure SQL Database and SQL Server, providing a flexible and efficient way to exchange data.

Overview of the Solution

The proposed solution involves a C# class, ClsRead, designed to manage the data transfer process. The class connects to both the source (SQL Server) and the target (Azure SQL Database), retrieves data from the source, and inserts it into the target database.

Key Features

1. Connection Management: ClsRead maintains separate connection strings for the source and target databases, allowing for flexible connections to different SQL Server and Azure SQL Database instances.

2. Data Transfer Control: The class includes methods to execute a SQL query on the source database, retrieve the results into a DataTable, and then use SqlBulkCopy to efficiently insert the data into the target Azure SQL Database.

3. Error Handling: Robust error handling is implemented within each method, ensuring that any issues during the connection, data retrieval, or insertion processes are appropriately logged and can be managed or escalated.

Implementation Details

Class Properties

• SourceConnectionString: Connection string to the source SQL Server.
• TargetConnectionString: Connection string to the target Azure SQL Database.
• SQLToExecuteFromSource: SQL query to be executed on the source database.
• TargetTable: Name of the target table in Azure SQL Database where data will be inserted.

Methods

• TransferData(): Coordinates the data transfer process, including validation of property values.
• GetDataFromSource(): Executes the SQL query on the source database and retrieves the results.
• InsertDataIntoAzureSql(DataTable TempData): Inserts the data into the target Azure SQL Database using SqlBulkCopy.

Error Handling

The methods include try..catch blocks to handle any exceptions, ensuring that errors are logged, and the process can be halted or adjusted as needed.

Usage Scenario

A typical use case involves setting up the ClsRead class with appropriate connection strings, specifying the SQL query and the target table, and then invoking TransferData(). This process can be used to synchronize data between different databases, migrate data, or consolidate data for reporting purposes.

For example, we have in our on-premise server the table PerformanceVarcharNVarchar that we need only the top 2000 rows and we need to compare with the table PerformanceVarcharNVarchar in our Azure SQL Database. 

The first thing that we are going to perform is to create the temporal table, of course, we could create a normal table. 

DROP TABLE IF EXISTS [##__MyTable__]
CREATE Table [##__MyTable__] (ID INT Primary Key)

Once we have created the table we are going to call our ClsRead with the following parameters:

        static void Main(string[] args)
        {
            ClsRead oClsRead = new ClsRead();

            oClsRead.SourceConnectionString = "Server=OnPremiseServer;User Id=userName;Password=Pwd1!;Initial Catalog=DbSource;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest";
            oClsRead.TargetConnectionString = "Server=tcp:servername.database.windows.net,1433;User Id=username1;Password=pwd2;Initial Catalog=DBName;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest";
            oClsRead.SQLToExecuteFromSource = "Select TOP 2000 ID from dbo.PerformanceVarcharNVarchar";
            oClsRead.TargetTable = "[##__MyTable__]";
            oClsRead.TransferData();
        }

If everything has been executed correctly, we could execute, queries like this one:

select * from [##__MyTable__] A 
INNER JOIN PerformanceVarcharNVarchar B 
ON A.ID = B.ID

Conclusion

While a direct Linked Server connection is not possible from Azure SQL Database, the ClsRead class provides a viable alternative with flexibility and robust error handling. This approach is particularly useful in cloud-based and hybrid environments where Azure SQL Database is used in conjunction with on-premise SQL Server instances.

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Microsoft.Data.SqlClient;

namespace LinkedServer
{
    class ClsRead
    {
        private string _sSourceConnectionString = "";
        private string _sTargetConnectionString = "";
        private string _sSQLToReadFromSource = "";
        private string _sTargetTable = "";

        public string SourceConnectionString
        {
            get
            {
                return _sSourceConnectionString;
            }
            set
            {
                _sSourceConnectionString = value;
            }
        }

        public string TargetConnectionString
        {
            get
            {
                return _sTargetConnectionString;
            }
            set
            {
                _sTargetConnectionString = value;
            }
        }

        public string SQLToExecuteFromSource
        {
            get
            {
                return _sSQLToReadFromSource;
            }
            set
            {
                _sSQLToReadFromSource = value;
            }
        }

        public string TargetTable
        {
            get
            {
                return _sTargetTable;
            }
            set
            {
                _sTargetTable = value;
            }
        }

        // Constructor por defecto
        public ClsRead() { }

        public void TransferData()
        {
            // Check that all properties are set
            if (string.IsNullOrEmpty(SourceConnectionString) ||
                string.IsNullOrEmpty(TargetConnectionString) ||
                string.IsNullOrEmpty(SQLToExecuteFromSource) ||
                string.IsNullOrEmpty(TargetTable))
            {
                throw new InvalidOperationException("All properties must be set.");
            }

            try
            {
                DataTable TempData = GetDataFromSource();
                InsertDataIntoAzureSql(TempData);
            }
            catch (Exception ex)
            {
                // Handle the exception as necessary
                Console.WriteLine("Error during data transfer: " + ex.Message);
                // You can rethrow the exception or handle it according to your application's needs
                throw;
            }
        }

        private DataTable GetDataFromSource()
        {
            DataTable dataTable = new DataTable();
            try
            {
                using (SqlConnection connection = new SqlConnection(SourceConnectionString))
                {
                 using (SqlCommand command = new SqlCommand(SQLToExecuteFromSource, connection))
                 {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dataTable.Load(reader);
                    }
                 }
                }
            }
            catch (Exception ex)
            {
                // Handle the exception as necessary
                Console.WriteLine("General Error: Obtaining data from Source.." + ex.Message);
                // You can rethrow the exception or handle it according to your application's needs
                throw;
            }
            return dataTable;
        }

        private void InsertDataIntoAzureSql(DataTable TempData)
        {
            try 
            { 
                using (SqlConnection connection = new SqlConnection(TargetConnectionString))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = TargetTable;
                        bulkCopy.BatchSize = 1000;
                        bulkCopy.BulkCopyTimeout = 50;
                        bulkCopy.WriteToServer(TempData);
                    }
                }
            }
            catch (Exception ex)
            {
                // Handle the exception as necessary
                Console.WriteLine("General Error: Saving data into target.." + ex.Message);
                // You can rethrow the exception or handle it according to your application's needs
                throw;
            }
        }
    }
}