{"id":94384,"date":"2022-05-24T16:00:30","date_gmt":"2022-05-24T16:00:30","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94384"},"modified":"2022-05-20T19:51:44","modified_gmt":"2022-05-20T19:51:44","slug":"azure-synapse-link-for-sql-from-production-to-a-data-warehouse-in-a-few-clicks","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/azure-synapse-link-for-sql-from-production-to-a-data-warehouse-in-a-few-clicks\/","title":{"rendered":"Azure Synapse Link for SQL: From production to a data warehouse in a few clicks"},"content":{"rendered":"

The Link<\/strong> technology in Synapse<\/strong> is used to synchronize data from different data sources to Synapse Analytics<\/strong>. Synapse Link for CosmoDB<\/strong> and Synapse Link for Dataverse<\/strong> were already released. Now it\u2019s time for a new and very important one: Azure Synapse Link for SQL<\/strong>.<\/p>\n

The Azure Synapse Link for SQL<\/strong> can be used with Azure SQL Database<\/strong> and SQL Server 2022<\/strong> to synchronize tables using these databases as sources and a Synapse Dedicated Pool as a destination.<\/strong><\/p>\n

When using Synapse Link<\/strong>, you are not really creating a Data Warehouse<\/strong>, but you are executing a big step in this direction.<\/p>\n

The process between production and the Data Warehouse<\/strong> is called ETL<\/strong> \u2013 E<\/strong>xtract, T<\/strong>ransform and L<\/strong>oad. You must execute these three steps: E<\/strong>xtract from production, T<\/strong>ransform the extracted data for the data warehouse structure, and L<\/strong>oad the data into the data warehouse.<\/p>\n

Sometimes these steps can be executed in a different sequence and many times it\u2019s more complex with even more steps. The steps can also be expressed as an ELT<\/strong> process, Extract, Load and Transform. As you may notice, the difference is the sequence of the steps. In this case, you first load the data, and, after that, you transform the data.<\/p>\n

The Load process is usually done to a staging area, where you will execute the transformation of the data to the format required by a data warehouse.<\/p>\n

Synapse Link<\/strong> doesn\u2019t execute any transformation on the data. It extracts the data from the source and loads it into the destination. In summary, using Synapse Link, <\/strong>you create a staging environment using very simple steps to transfer the data.<\/p>\n

The staging environment is important because it reduces the workload over the production environment. If you transform the data before loading, the production environment would be affected by the transformation workload. On the other hand, loading the data to a staging environment and leaving the transformation for later, the transformation workload will be executed over the staging environment.<\/p>\n

Another benefit of this staging environment is the possibility to use this environment for reporting, instead of run reports over production. In some cases, you may would like to use Synapse Link<\/strong> specially for that, even if you are not planning the further transformations into a data warehouse yet.<\/p>\n

\"Diagram\n\nDescription<\/p>\n

Besides relieving the production environment from the transformation workload, the staging environment can also help in scenarios with multiple data sources with similar structures. For example, imagine a company with a main office and many different branches. Each branch has its own database, but the data stored has the same schema, for the same business.<\/p>\n

In this scenario, Azure Synapse Link for SQL<\/strong> can be used to synchronize the data from all the branches to a centralized staging environment. This becomes an additional benefit of Azure Synapse Link for SQL<\/strong> and the staging area it can easily create.<\/p>\n

Azure Synapse Link for SQL basic elements<\/h2>\n

The structure of Azure Synapse Link for SQL<\/strong> uses two key elements which are always present in the different scenarios where you can implement it.<\/p>\n

Change Feed:<\/strong> The change feed is a new feature included in Azure SQL Database<\/strong> and SQL Server 2022<\/strong> to provide support to Azure Synapse Link for SQL<\/strong>. You may already know SQL Server <\/strong>has two different features to allow the control of changes in a production environment: the Change Data Capture<\/strong>, also known as CDC<\/strong> and Change Tracking<\/strong>. However, Microsoft decided to implement a third method to ensure the workload over the production environment would be as light as possible. This is the purpose of the Change Feed<\/strong>.<\/p>\n

Integration Runtime<\/strong>: The IR<\/strong> is the same one used for pipeline execution. The IR<\/strong> is responsible for the start\/stop operations on the source database. It can be used as an autoresolved IR<\/strong> or a self-hosted IR<\/strong>, according to the scenario which will be implemented.<\/p>\n

Azure Synapse Link for SQL Scenarios<\/h2>\n

Azure Synapse Link for SQL<\/strong> can be implemented in 3 different scenarios:<\/p>\n

Azure SQL Database<\/strong>: The fact both services are online inside Azure makes the implementation easier. Azure SQL Database makes some steps easier.<\/p>\n

SQL Server 2022 in an Azure VM<\/strong>: SQL Server 2022 requires some additional steps for the use of Azure Synapse Link for SQL. The fact it\u2019s installed in an Azure VM makes the process easier, allowing use of an Autoresolved integration runtime. This scenario worked well during my tests, but it\u2019s not confirmed yet as a fully supported scenario. Officially, for SQL Server 2022, a self-hosted integration runtime is required.<\/p>\n

SQL Server 2022 on premises<\/strong>: This is the scenario requiring the most work. Since the server is on premises, you will need to use a self-hosted integration runtime installed on premises to send the data to the landing zone in the cloud. The services in the cloud will not access the on-premises environment. The self-hosted IR will be the responsible for start\/stop operations, and the SQL Server on premises will send the data to the cloud.<\/p>\n

Azure Synapse Link for SQL Architecture<\/h2>\n

The basic architecture is always the same: an integration runtime is used to control the start\/stop of the Link on the source database. The source database needs to support the change feed feature, it means it needs to be an Azure SQL Database<\/strong> or SQL Server 2022<\/strong>.<\/p>\n

The source database will send all the new or changed records to a landing zone. The Synapse Workspace will load the data from the landing zone to the target database.<\/p>\n

\"Diagram\n\nDescription<\/p>\n

If the source database is an Azure SQL Database<\/strong>, the landing zone will be internal to the Synapse Workspace<\/strong>. There will not be access to the landing zone. The Azure SQL Database<\/strong> will need to have a system managed identity which will be used to access and save the data in this internal landing zone.<\/p>\n

Internally, Synapse Analytics<\/strong> has an Ingestion Service<\/strong> responsible for loading the data from the landing zone to the Dedicated SQL Pool<\/strong><\/p>\n

\"Diagram\n\nDescription<\/p>\n

If the source database is a SQL Server 2022<\/strong>, the landing zone will be explicitly created as an ADLS Gen 2 Account<\/strong>. During the start of the link the integration runtime (IR) will create a database scoped credential to allow SQL Server to access the ADLS Gen 2 Account<\/strong>. This credential will be using a SAS token to authenticate to the ADLS Gen 2 Account<\/strong>.<\/p>\n

The start\/stop process is executed by the IR, which will connect to the source database using the authentication provided on the linked service.<\/p>\n

The authentication to the source SQL can use SQL Standard Authentication<\/strong> or Managed Identity<\/strong>. If it uses managed identity, the Azure AD<\/strong> user will need to receive access the source SQL.<\/p>\n

\"Diagram\n\nDescription<\/p>\n

Finally, if the source SQL is on premises, the IR will need to be a self-hosted IR in the on-premises environment.<\/p>\n

\"Chart,<\/p>\n

Provisioning the Azure Synapse Link for SQL<\/h2>\n

The Azure Synapse Link for SQL<\/strong> provisioning is done using Synapse Studio<\/strong>. Some steps are slightly different depending on the provisioning scenario.<\/p>\n

    \n
  1. On the Synapse Studio<\/strong> left toolbar, click the Integrate<\/em> button<\/li>\n
  2. Click the Add<\/em> button \u2018+\u2019<\/em><\/li>\n<\/ol>\n

    \"Graphical<\/p>\n

      \n
    1. Click the Link Connection<\/em> menu item<\/li>\n
    2. On the New Link Connection<\/em> window select Azure SQL<\/em> as the source type<\/li>\n<\/ol>\n

      \"Graphical<\/p>\n

        \n
      1. On Source Linked Service<\/em>, select New<\/em> to create a new linked service<\/li>\n<\/ol>\n

        Linked Services<\/strong> are used by Synapse Analytics<\/strong> to establish a connection to external services. Every linked service uses an integration runtime (IR) to execute actions on the connected service.<\/p>\n

        \"Graphical<\/p>\n

          \n
        1. On the Name<\/em> textbox, type the name AdventureSQL<\/em><\/li>\n
        2. Choose the integration runtime to be used. For Azure SQL, the auto-resolved runtime works, you don\u2019t need to use a self-hosted one.<\/li>\n<\/ol>\n

          \"Table\n\nDescription<\/p>\n

            \n
          1. Select the Azure SQL<\/strong> you will be connecting to.<\/li>\n<\/ol>\n

            You can select the Azure SQL<\/strong> from a subscription you have access to, or you can manually insert a connection string. The result for the service will be the same. This creates an interesting level of flexibility:<\/p>\n