Mirroring SQL Server database to Fabric
Authors: Kal Yella , Anu Venkataraman
Fabric Mirroring ingests and replicates data continuously in near real-time from sources such as Azure Cosmos DB, Azure SQL Database, Snowflake into Microsoft Fabric. However, it is currently restricted to the above data sources. This blog explains how we can extend Fabric mirroring to an on-prem SQL Server database as a source, using a combination of SQL Server Transactional replication and Fabric Mirroring.
Transactional replication in SQL Server is a mechanism for synchronizing data between databases in near real time. Transactional replication begins with a snapshot of the source database object’s schema and data. After this initial snapshot has been applied at the target, subsequent data changes (inserts, updates, deletes) made at the source database (Publisher) are delivered to the target database (Subscriber) as they occur. This ensures near real-time synchronization.
There are three agents involved in this replication process:
- Snapshot Agent that prepares the initial snapshot.
- Log Reader Agent that monitors the transaction log and copies transactions marked for replication into the distribution database.
- Distribution Agent that applies all these changes to the target database.
Let’s understand this process with a simple example. Let’s use a typical ‘Employee’ table for the purpose of this example. We will demonstrate how changes (inserts, updates, deletes) made to this Employee table in an On-prem SQL server flow through Transactional replication and Fabric Mirroring to eventually land in OneLake.
Example Setup:
Transactional replication from SQL Server to SQLDB:
- Sample data preparation: Create a Table called Employee in the source SQL Server and insert some sample data into it.
Note: We are using SQL Server 2022 for this example. However, you can use any SQL Server version higher than SQL Server 2012. Please check the following documentation for supported configurations and versions while replicating to Azure SQL Database: Replication to Azure SQL Database – Azure SQL Database | Microsoft Learn. Also, ensure that replication components are installed in your SQL Server instance. Follow the link below if you don’t have replication components already installed: Install SQL Server Replication – SQL Server | Microsoft Learn.
Create Employee table in your user database using the syntax below:
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
DateOfBirth DATE,
HireDate DATE,
JobTitle NVARCHAR(50),
Salary DECIMAL(18, 2)
);
GONow, insert some sample data into this table:
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
INSERT INTO Employee (FirstName, LastName, DateOfBirth, HireDate, JobTitle, Salary)
VALUES (
'FirstName' + CAST(@i AS NVARCHAR(50)),
'LastName' + CAST(@i AS NVARCHAR(50)),
DATEADD(DAY, -@i, GETDATE()), -- Date of Birth is @i days before today
DATEADD(DAY, -@i/2, GETDATE()), -- Hire Date is @i/2 days before today
'JobTitle' + CAST(@i AS NVARCHAR(50)),
30000 + (@i * 10) -- Salary increases with each record
);
SET @i = @i + 1;
END;2. Create Transactional Publication: Let’s create a Transactional Publication using the table created above.
You can modify the scripts provided below to create this publication. Enable the replication on your source database in on-prem SQL Server using the following syntax. Replace the [DB Name] with your source database name.
use master
exec sp_replicationdboption @dbname = N'[DB Name]', @optname = N'publish', @value = N'true'
GO
Add Transactional Publication on your source database using the following commands, again after replacing the [DB Name] with your source database name.
use [DB Name]
exec sp_addpublication @publication = N'Employee', @description = N'Transactional publication of Employee table', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'Employee', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
Now, add the Employee table as an article to this publication.
use [DB Name]
exec sp_addarticle @publication = N'Employee', @article = N'Employee', @source_owner = N'dbo', @source_object = N'Employee', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Employee', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboEmployee', @del_cmd = N'CALL sp_MSdel_dboEmployee', @upd_cmd = N'SCALL sp_MSupd_dboEmployee'
GO
Note:
You can also alternatively follow the steps in the link below to create the publication using GUI:
Tutorial: Configure Transactional Replication – SQL Server | Microsoft Learn
You should now see the Publication showing up under Local Publications in Management Studio, as below:
You can also right-click on it and click on ‘View Snapshot Agent Status’ to ensure that the snapshot has been created as below:
3. Create Azure SQL DB Subscription: Add an Azure SQL DB subscriber to the publication created above. You can use the following syntax for this purpose. Update the subscriber, destination_db, subscriber_db, subscriber_login, and subscriber_password variables with appropriate values below.
-----------------BEGIN: Script to be run at Publisher -----------------
use [DB Name]
exec sp_addsubscription @publication = N'Employee', @subscriber = N'yoursqlsrv.database.windows.net', @destination_db = N'your subscriber db', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'Employee', @subscriber = N'yoursqlsrv.database.windows.net', @subscriber_db = N'your subscriber db', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'your login', @subscriber_password = 'your password', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20240607, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher -----------------
You should now see the subscription created under Local Publications in Management Studio, as below:
You will eventually see the Employee table created in your Azure SQL DB with the data exactly matching with that from the Employee table in your source database.
You are now ready to configure Fabric Mirroring from the Employee table in Azure SQL DB.
Configuring Fabric Mirroring from Azure SQL Database
In this section, we’ll explore how to use Fabric Mirroring to mirror data from Azure SQL Database to Fabric OneLake.
Follow through the pre-requisites and steps detailed in the official documentation to configure Fabric mirroring from Azure SQL DB.
- Enable System Assigned Managed Identity (SAMI):
As detailed in the official documentation, prior to configure mirroring to connect to Azure SQL DB, navigate to Azure SQL Server Identity section under Security and enable System Assigned Managed Identity (SAMI) by selecting the status to ‘On’ as shown in Figure 2a. SAMI must be a primary ID (if you have more than one identity such as User assigned managed identities assigned to the server).
Note: If you have only enabled system assigned managed identity (SAMI) and if you don’t have any User assigned managed identities, then the SAMI will be your primary identity by default.
2. Adjust Network Settings:
In the Networking section, if public network access is disabled, you won’t be able to mirror your Azure SQL DB to Fabric at this time. Enable ‘selected networks’ and ‘allow azure services to connect to the server’ as shown in the Screenshot below.
Note: This allows access to your Azure SQL database from all networks in Azure, including the networks that are outside your tenant. Please ensure that you are taking additional precautions to protect your databases, such as ‘Allow only Entra ID authentication to your database’.
To create an Entra ID user in your database, you can run the following script in your source DB.
create user [fabric_user_name_here] from external provider
alter role db_owner add member [fabric_user_name_here]
Note: To successfully configure Mirroring for Azure SQL Database, the principal used to connect to the source Azure SQL Database needs to be granted CONTROL or db_owner permissions.
3. Enable Required Settings in Fabric Admin Portal
Open the Fabric Admin Portal, make sure you have this setting enabled.
4. Configure Mirroring in Fabric
Navigate to your Fabric Workspace. Click on New and More options. In the Options, navigate to Data Warehouse section and choose Mirrored Azure SQL Database as shown in Figure below:
If this is the first time you are configuring mirroring to connect to Azure SQL Server, select Azure SQL Database under New connection, add connection details and click next.
You can add the source server, database and other connection details and click Next. As the fixed-database role “db_owner” is defined at the database level, it is important to provide database name in addition to server to successfully configure mirroring.
Note: Ensure that you are entering the source server and database that you selected as a subscriber for Transactional replication.
When the connection details are configured successfully, choose the Employee table to mirror from the list, and click connect. Then configure destination mirrored database name (Ex: WWI), this will create a mirrored database in Fabric Warehouse. This will start the mirroring process. Navigate to the SQL analytics endpoint and query the mirrored database/table.
Validation: Running a count statement to validate if it matches with the source on-prem DB.
You can monitor the status of replication and time of replication by navigating to Monitor replication.
Mirroring in Action
- To demonstrate replication, we will run a Data Manipulation Language (DML) query in the source database in on-prem SQL Server to insert a record and validate if it’s mirrored in OneLake.
Run the following command in your on-prem SQL Server:
SET identity_insert Employee ON
Insert into [dbo].[Employee]
(EmployeeID, FirstName, LastName, DateOfBirth, HireDate, JobTitle, Salary)
Values (1001, 'John', 'Doe', '2021-12-12', '2021-12-12', 'Admin', 50000)
You can confirm that this record has been replicated to Azure SQL DB, by right-clicking on the publication and clicking on the ‘View Log Reader Agent Status’.
To verify that this record is replicated to OneLake, navigate to the ‘Monitor Mirroring’ section. Check the latest mirrored time and the number of rows replicated, which should now include the newly inserted record, totaling 1001 rows.
Next, navigate to Lakehouse SQL endpoint to further validate the data.
2. Alter the table by adding a new column TermDate to the Employee table in the source database.
Alter table [dbo].[Employee] add TermDate dateAfter a few minutes, refresh the Lakehouse SQL endpoint, and you should see the TermDate column mirrored in OneLake.
This setup ensures that data changes in your on-premises SQL Server are continuously mirrored to Microsoft Fabric, providing a robust and scalable solution for near real-time data integration and analytics.
For more details on limitations and additional configuration options, refer to the official Microsoft documentation: Limitations for Fabric mirrored databases from Azure SQL Database (Preview) – Microsoft Fabric | Microsoft Learn
