Introducing Migration Assistant for SQL database in Fabric (Preview)

Key Takeaways

Migration from SQL Server to SQL Database in Fabric is now in public preview from the Fabric portal. Learn all about it and see a demo this week on Data Exposed. Hi, I'm Anna Hoffman and welcome back to Data Exposed. Today I'm joined by Neil Ball. Welcome back to Data Exposed, Neil. Today we are talking about SQL Database in Fabric and specifically some new and exciting capabilities that you've all been asking us for since the product became available and that is migrations to SQL Database in Fabric. So, I'm excited to be here with Neil to talk about how this is now in public preview. So, Neil, thanks so much again for coming on the show. Tell us a little bit more about migrations to Fabric and what the team has built. Yeah, so as you said, there has been a lot of customers and users asking for this feature and what we have done is so I've built an end-to-end experience in Fabric portal where user can by few clicks can migrate their database in on-premise SQL easily to the SQL Database in Fabric. So, I'm just going to give you a quick walk-through of the design and then jump into the demo. Awesome. So, the workflow here is like you have a SQL Server, let's say SQL 2025 and you want to migrate to Fabric SQL. So, what we are going to do is uh use logical migration where you want to move the schema first and once you move the schema, you can go ahead and bring the data. In order to do do so, so you we need a DACPAC of the source database and you can create a DACPAC by connecting to SQL Server Management Studio or run SQL package.exe and once you have the DACPAC, you can upload to the Fabric migration assistant and this Fabric migration assistant is going to extract the schema objects like table, view, stored procedures and apply to the SQL Database in Fabric. Once the data once the schema has moved, the next is the data and for data we are going to use copy job which is available in Fabric and you are going to connect to SQL and use the copy job which is going to use a data gateway. So, you need a data gate a machine with a data gateway installed and it can connect to the SQL Database. You can select all the tables which you want to migrate and it can bring all the data to Fabric and then you are good to go uh to kind of go live [clears throat] with the new data in Fabric SQL. So, I have a quick demo for this. So, in this demo, I have uh I have a workspace which is Java demo and I go ahead and click migrate. So, when I go ahead and click migrate, it will give me all the options which is relevant to migrating to Fabric and one of them which is newly added is SQL Server in preview. Now, you go next and you select the DACPAC file. So, I have already I have a DACPAC java.dacpac and I'm going to upload this DACPAC and create the schema. And before I do that, I need to provide a new database name. So, I'm going to name it Java demo data exposed. So, this wizard is going to create the database and then it's going to up upload [clears throat] import the schema from the DACPAC. So, as a first step, as you see at the bottom, it's going to create the database and then it will going to extract one object after another and then translate that object and keep applying. Once it is applied, you can see the migration assistant on the right and here you can at first you can go ahead and export the data so that you can go ahead analyze offline and also you can hover on the migrated objects and see how many objects has been converted successfully which is 98% in this case and the objects which needs to fix. To fix the object, you can use fix query errors which can use the co-pilot or you can take manual >> [laughter] >> sorry, you can use manual operations to fix the issues. Once you have fixed the issues, you can go ahead and make transformation for the other objects. In this case, I'm going to create the non-clustered indexes. Now, after all these operations are done, you acknowledge you have fixed the issues, then you go ahead and prepare for copy. So, you're going to disable the constraints and the indexes and all that before copying the data. Now, once the source database is ready, you go ahead and create copy job. You give a name to the copy job. In this case, I'm going to name it copy data Java data exposed and I'm going to select the source database. So, it takes few few seconds to spin up the copy wizard and then the SQL Database is a SQL Server 2025. So, I'm going to give the name of the SQL Server and the database name which is Java and then I need to select the data gateway. I already have configured the data gateway and then authenticate using the Windows authentication. Then select the tables which you want to migrate and do a full copy or incremental based on the requirement. This migration is going to copy row by row of all your data objects which you have selected. It it depends the speed and the performance and throughput depends on your compute size, your network availability and the size of the data. Once the data is migrated successfully, it it you will get an acknowledgement with everything has succeeded and you can go back to the wizard and acknowledge that the step has been completed. So, the my copy step is complete. Now, my final step is finalize the copy which is going to bring back the schema constraints, the indexes and all of that which has been disabled will be re-enabled. When I go ahead and bring back all these indexes and constraints, you can go ahead and acknowledge it and the data migration is complete. So, this is a quick overview of the migration experience which have launched in Fabric portal and we would really like you to try it out and just move your data to Fabric with using the copy and the DACPAC experience. Awesome. Cool, thanks so much, Neil. That was super smooth experience. I'm really excited. I know we learned a lot from what some of the other migration workloads in Fabric have done. So, it's really great to have that out there in public preview. A couple questions that might come up from folks that I have for you is one, are there any like gotchas or limitations that people might want to be aware of before they get started? Yeah, so currently this experience supports a DACPAC file of 20 MB and based on the kind of testing and the the kind of other kind of operations we have seen, it can hold around 100k objects. So, if you have a database size of 100k objects and less than 20 MB, it should work for you. Awesome, very cool. Another thing we've been hearing a lot about is kind of like this live connectivity aspect of a migration. I'd love to get your take like maybe just first just like what does that mean and two, if that's something we currently support or are thinking about supporting in the future. Yeah, so in this demo you would have seen that I have to upload the DACPAC. So, one of the prerequisite is you need to connect separately like in Management Studio or through SQL package and create a DACPAC. So, the live connectivity is from the Fabric portal you can connect to the source and then get all the schema definitions and everything. Right now, it's not available in the preview. We are actively working on it and and evaluating and understanding the requirements. So, yeah, watch out in the space and we might have something come in in next few quarters. Okay, awesome, great. So, it's great to hear that this experience is available today for people to go try out. We're also thinking about already thinking about future enhancements for how we can make it easier. So, that's awesome, Neil. Thanks so much. I learned a lot. I think viewers probably did too. I think a lot of people are going to be really excited about this. So, if you're watching this episode, go ahead give it a like, leave us a comment, let us know what you think. Go try it out and if you hit any snags, feel free to to let us know and we'll put some links in the description for you to learn more and we hope to see you next time on Data Exposed.