SQL Server 2025: Mirroring for SQL Server in Fabric | Data Exposed

Key Takeaways

Mirroring is now available in public preview for SQL Server. Yes, SQL Server, including your previous in-market versions and SQL Server 2025. Learn all about them and how they're different this week on Data [Music] Exposed. Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed. Today I'm joined by AJ uh from the SQL product group. And AJ, you have been avoiding the show for years and years. So I'm so glad to have you on the show. Uh why don't you kick us off by telling us a little bit about you. Yeah, thanks. And I'm actually privileged to be in this show and I was waiting for the right moment and looks like today is that moment. And I have been with Microsoft and SQL for a little over 25 years. I've seen all flavors of SQL from SQL Server on premises to all our offerings in the cloud and now I'm excited to be part of the SQL and fabric journey. So, which is one of the key topics that we're going to talk about today. Yeah, absolutely. And for folks tuning in, uh this is part of our SQL Server 2025 series, but this also applies if you're not on SQL Server 2025 or not even thinking about it. Uh this topic is around mirroring. So before we get into, you know, kind of what's happening with SQL Server uh and mirroring, I'd love if you could share a little bit about where we're at with the mirroring journey. Yeah, absolutely. Right. So mirroring is basically a way for customers to bring data from various data sources uh to fabric so that they can do analytics in the cloud and uh AI on top of that. We have had support for other products like Azure SQL, Snowflake, Oracle, etc. And now we are excited to bring mirroring for SQL Server which is one of the most widely deployed databases in the world. Awesome. Great. And I know we've had folks on and we've talked about it in Azure SQL database and Azure SQL manage instance, but what are we doing in the SQL Server space? And it's a little bit different, right? Absolutely. Since SQL server runs outside the cloud in most customer scenarios in their own environments or even in fact in other hyperscalers, it's it's important that we build the right technology so that the customers can get full value from fabric. Right? So as you can see in this diagram um once uh SQL server is integrated with the fabric ecosystem customers can then bring their data near real time and replicate any changes happening on the source transactional systems to the cloud and then leverage the full fab value of fabric by leveraging various services like data factory data pipelines realtime insights and powerbi awesome Awesome. Cool. So, let's get let's get right into SQL Server. How does it work for kind of older versions of SQL Server today? Yeah, let's look briefly dig into the architecture of how the in market versions mirroring works, right? So, what you see here is on the left side is your SQL server environment. It could be running anywhere uh outside Azure or uh even in Azure VMs and then on the right side you have the fabric uh one lake. Right? The key pieces here are basically in in for inmarket versions we rely on the change gap data capture or CDC technology which takes the initial snapshot of your database that you enable for mirroring and then once that initial snapshot is sent to what is called the landing zone on the right side by the mirror service uh any incremental changes that happen which are tracked and change tables on the source are also pushed to uh pulled by the on premises data gateway to one lake and thereby uh near near realtime data is available in one lake in an analytics ready format which is typically delta perk files. This feature we uh we launched in public preview at the Microsoft build conference a few days back and every customer should be able to use this from the fabric portal. Awesome. That's great. Okay. Okay, so this is for inmarket but with SQL server 2025 since we're kind of building it ourselves and we can have a little more control on how we implement it. Uh are we doing anything different? Uh there are some brave different but before I do that I I'd like to quickly show you how the works right. So let's take a look at that. So in this demo we are going to look at an adventure works database. uh and you can see that I have it a 2022 instance restored here right and then we will look at replicating or mirroring these four tables the products table from your SQL server to your fabric I have set up an on-remise data gateway and you can see that I'm already connected to fabric uh and we will be using this gateway which will act as your interface between your on-rem environment and fabric next if I switch to the fabric workspace here I would go select the new item and if you scroll down to the get data section you will pick the mirror SQL server database uh and then here you will have to specify your connection string to your source system where I put my server name which is your named instance and the database I'd like to mirror right then you will see that I have to provide the gateway I had just created let me go ahead and pick that gateway and then next I'll have to provide an authentication so that this can log into my SQL server instance I'm going to use a domain account, a Windows domain account for this. Let me enter the domain username as well as the password and then click connect. So once I do that, I will be able to see all the tables in the database that I have specified adventure works and you also have the option at the bottom to mirror future tables in case you have tables later. So let me give a new name for the mirrored database. So this is the uh mirror copy that's created in fabric. Uh as you can see now I have clicked create mirror. So it's provisioning the instance and then starting the initial snapshot. Let's go ahead and refresh this. It's it's running now. And you can see this it has taken the initial snapshot and pushed all the changes uh to these four tables. Now if I go back to my source, you can see that it has created some change data capture tables which keeps track of which tables are being mirrored and what changes are happening. You can also see a trace I started to see the behind the scenes stored procedures we run to enable mirroring for the tables uh in scope. Right now let's go ahead and query this mirror data set. Right? So by default we create an analytics endpoint in fabric for your mirror database. I'm connected to that and then I expand that you will see a browser experience very similar to what you see ins SMS where you can interact with this mirrored database just like you would with any SQL database using your well-known SQL query. In this case I'm leveraging the power of copilot uh to generate the SQL query for me. So you can see that I'm asking it to show me the number of products uh by a model and it generated a TSQL for me and I can go ahead and just copy that query over and then run it and voila, I have the results here. So as simple as um getting started is very quick and you can see that within a few minutes you have all your data that you're interested in on the fabric side in the mirror as a mirrored database in an analytics ready format. So I'm here's another example of a different query where I'm just asking it to tell me how many data is there in the product models table. Uh I'm going to go ahead and now do some changes on the source. Here I'm going to insert 25 new rows on the product model table and the CDC table will keep track of those changes. Now we can see the row count for the product model went up by 25 rows and you have now 153 rows there. Now if I go back to my analytics endpoint and uh query that table I should be uh here I'm going to run the select query against the table and when I run that query and if I scroll down you can see all the new rows that have been added to this mirrored copy. So it's near real time you should get the data here uh very quickly. The latency depends on your network connection and the amount of data you're pushing but it should be there fairly quickly. And there you go. this is how you would do mirroring for inmarket versions of SQL. Awesome. It's great to see and it's it's great to see that we're kind of taking advantage of the existing tech we have with CDC. Uh that being said, I know there are some challenges there and so in 2025, we want to make it better. Uh so what's different about mirroring for SQL Server 2025? Uh great question. So let's look at some of the differences between how we uh set up this mirroring between 22 25 and even compare it with what we have for Azure SQL database or manage instance. The main difference is for 2025 we rely on the change feed technology uh which is different from CDC. Uh this is the same technology that is also used for mirroring in Azure SQL. Uh the other key difference is now mirroring supports managed identity which is a common customer ask. So the managed identity if folks remember is available through the arc agent. So one of the requirements for 2025 mirroring is to set up arc agent and enable system assign managed identity. This way you can have a more secure and managed experience. Uh you don't need to rely on CDC. So we we have our own internal change feed. So you don't need to enable CDC on your source. And finally, OPDG here just acts as a control plane uh to do the actual authentication to fabric. But all the data movement is handled directly by the change feed. So very very similar to what's available for address SQL. Now if you look at briefly at the architecture, very similar to what we saw for the inmarket versions. Let me highlight just the key differences. First one as I mentioned is there's an arc agent in the mix which is handling the managed identity uh experience and uh the second big difference is the OPDG handles um just the control plane that the publishing of the data from SQL server 2025 to the landing zone follows a push model unlike a pull model in the previous versions. So in this case, SQL server directly uses change feed and writes it to your landing zone and then the mirror service takes it from the landing zone and uh sends it to the one lake in the analytics ready format. So these these are the key differences between the inmarket version and what's coming for the next version. And this is also available in public preview. In case folks missed it or did not hear, we did announce public preview of 2025 itself in the build conference and this feature is also available as part of that. Awesome. And one question before we get into the demo is uh you know we did it differently in SQL Server 2025 and now you said we're using push model using the change feed. Is this faster? Is it more performant? Is it what why why do we do that? Yeah, great question. Obviously with a newer version which we are able to go and redesign and build a completely new architecture which works at cloud scale right so whatever you see here is the same thing what many customers are already using with Azure SQL so that gives the best benefit in terms of uh capabilities feature sets uh no limitations like what CDC has today so those are some of the key benefits of uh uh using the change fee technology Mojio or CDC. Awesome. Cool. Okay. All right. Well, let's take a look. Yep. Let's see how this works. Right. Um, again, here I'm using the Adventure Works uh database in a 2025 instance. Uh, if you remember for the other one, we used the products table. Now, I'm going to use the customer and address table. So, let's go ahead and uh leverage these tables and uh set up mirroring for that. uh very similar from a customer experience standpoint, from a fabric standpoint that that you will see shortly. I'll go back to the fabric workspace. Select new item. I'll scroll down to the same mirrored SQL server database section and select that and now connect to my 2025 instance. Um and uh I already created the connection string. So I'm just going to pick the 2025 connection string uh with the same OPDG which I had set up previously. Now once I connect you can see the tables in my 2025 instance that I'd like to mirror. I selected all of them and give it a name for the mirrored database that I want to call it. It doesn't have to be the same name as your source. It can be a different name. And now you can see that it's running. So it's taking an initial snapshot and you can see it has pushed all the changes to the one lake. And now I'll be able to query this using the analytics endpoint. Uh before I do that, you can see that if I expand my source, there are no CDC tables here like we saw in the other case. Instead, you'll see that by running this new DMVs against the change feed, you can see that uh this will help you see the underlying uh log records that are being processed by change feed. And the SP health change feed procedure shows you all the tables that have been enabled for mirroring. uh some of the columns here like for example the destination column shows your landing zone location and if I scroll to the right you can see a row for each table here which I've enabled for mirroring uh and then if I keep going further it keeps it shows you all the log records that have been uh enabled so this is helpful in case uh you want to troubleshoot the mirroring performance latency etc. Now let's go ahead and connect to the analytics endpoint. And when I expand it, I can see the tables I've enabled. Uh just like uh we saw in the other version, very similar experience. I'm going to use co-pilot again to run a query against this tables and I'm going to ask it to uh write a query which will give me what are the top customers by sales ordered by sales. Right? So once the qu copilot generates the query uh we copy it over and then run this and you have the results and just like what you would have in the source. Now the other thing you could do in the same uh fabric query editor is you can connect to other warehouse too right so now this is the beauty of bringing data from different sources and being able to interact with this data no matter though they are in different sources right here I've asked uh I'm going to ask it a query which will join both these mirrored databases remember these are two distinct sources one coming from 2022 one from 2025 completely different technologies but both are in one lake. Since it's in one lake and uh they are in the same workspace, uh the copilot is smart enough to figure out a join. It understands a schema and metadata and generates a join appropriate joins and I'm able to join the data across this uh one lake data stores. Right? So that's the beauty of this. Right? Now you can see that uh in the same workspace I have two different data source and I can even extend this further by creating a lakehouse which is like a logical database container and set up shortcuts to this and take it even to the next level by creating lakehouse data agents and build powerway on top of that. So again once the data is in one lake uh you can uh your imagination can run wild. Awesome. Cool. It's great to see this and it's great to see how easy it is to set up too. it's not uh super complicated and just kind of works and then you can join across all these different SQL servers uh that are bringing that data into one link and maybe it's not even SQL server you also have fabric SQL database or you got an Azure SQL database you know as long as it's landing in one lake that's kind of the beauty uh so totally agree with you um AJ you know kind of as we wrap any tips or tricks or ideas for folks who are maybe just getting started with SQL server mirroring yeah I mean uh I mean we are using well-known SQL. I mean as you saw through the demos everything is done through a simple fabric experience and even from a query language we are using TSQL. So it's it's wellnown and nothing new to learn from that aspect. The key things from best practices please do take a look at the URL aka.ms/sql server mirroring. We have a tutorial there on how to set it up and get started uh and the best practices in terms of how do you get the best performance and uh also some troubleshooting tips. Awesome. Great. Well, AJ, thanks so much for coming on the show. I learned a lot. It was great to see the demos. I'm sure our viewers did as well. If you watched this episode and you liked it, go ahead give it a like. Leave us a comment. Let us know what you think of the SQL Server mirroring options. And we hope to see you next time on Data Exposed. [Music]