Microsoft Fabric: Import On Premise SQL Server 2022 Data into Microsoft Fabric!!

Рет қаралды 4,381

Күн бұрын

‪@SQLBalls‬ walks us through how to install, setup, and configure a data gateway. Then how to register that data Gateway in Microsoft Fabric. We then use a Dataflow Gen 2 to import data from #SQLServer2022 into #MicrosoftFabric!
Chapters
00:00 Introduction
00:54 Demo - Our SQL Server 2022 Data
01:06 Demo Install on-premises data gateway
01:51 Demo Register data gateway in Microsoft Fabric
03:12 Demo Microsoft Fabric Gen 2 Dataflow import SQL Server 2022 data
05:26 Demo Gen 2 Dataflow Refresh History
05:39 Demo Validate Data import into Data Warehouse
06:49 A Note on Gen 2 Dataflows
Blog that inspired this video!
Bring your Mainframe DB2 z/OS data to Microsoft Fabric by Pratim Dasgupta
techcommunity.microsoft.com/t...
Install an on-premises data gateway
learn.microsoft.com/en-us/dat...
Getting from Dataflow Generation 1 to Dataflow Generation 2
learn.microsoft.com/en-us/fab...
View refresh history and monitor your dataflows
learn.microsoft.com/en-us/fab...
Quickstart: Create your first dataflow to get and transform data
learn.microsoft.com/en-us/fab...
/*******************
/CONNECT WITH US!!
/*******************
Tales from the Field
Twitter: / talesftfield
Linkedin: / 100091910
Blusky: bsky.app/profile/talesfromthe...
Bradley Ball
Twitter: / sqlballs
Linkedin: / sqlballs
Blusky : bsky.app/profile/sqlballs.bsk...
Daniel Taylor
Twitter: / dbabulldog
Linkedin: / danieltaylor3
Blusky: bsky.app/profile/dbabulldog.b...
Neeraj Jhaveri
Twitter: / neeraj_jhaveri
Linkedin: / neerajjhaveri
Blusky: bsky.app/profile/neerajny.bsk...
Josh Luedeman
Twitter: / joshluedeman
Linkedin: / joshluedeman
Blusky: bsky.app/profile/joshluedeman...
Andrés Padilla
Twitter: / nodestreamio
Linkedin: / andrespadillaandrade

Пікірлер: 21

@SQLBalls 8 ай бұрын

Hi @TeamCykelhold, thank you for watching! It depends what you are looking to do. One thing you could do is use traditional Azure Data Factory. The PG added Lakehouse tables and Lakehouse Files to Data Factory so if you need the ability to communicate with private endpoints, to use some of the supported items in ADF, or use data pipelines this may be your go to approach!

@fluuuuuu 5 ай бұрын

Seems strange that ADF in Fabric doesn't support on prem sources as there is no way to use a data gateway or application gateway to ingest the data? Meaning you have to go back to using Azure ADF instead? Just seems like a strange oversight to not have that ability?

@mnhworkdev1652 10 ай бұрын

We'd like to know more about the licensing costs for a datawarehouse. Seems like Fabric is very expensive for small/medium businesses, even with on-prem.

@Tales-from-the-Field 8 ай бұрын

Hi @mnhworkdev1652 , Neeraj Jhaveri did a video for us on this kzfaq.info/get/bejne/aNh1h7Oop7LSno0.html This link is to the part of the video that begins to cover the licensing. Hope this helps, if you have more questions let me know!

@sansophany4952 11 ай бұрын

Thanks for sharing. this is very helpful. I wonder is it possible to do realtime data ingestion into data lakehouse in fabric?

@Tales-from-the-Field 11 ай бұрын

Hi @sansophany4952 @SQLBalls posted a video today showing how you can use real time streaming to write data from an API to a Microsoft Fabric Lakehouse. kzfaq.info/get/bejne/lbh2qcaBp7TNf58.html

@sansophany4952 11 ай бұрын

@@Tales-from-the-Field thank you 🙏

@user-gf4oy6qo4u 3 ай бұрын

Hi, do you have any videos coming about using Azure HCI (private cloud) with an Azure public cloud, as a single pane of glass? Extending Saas offerings on prem?

@Tales-from-the-Field 2 ай бұрын

Hi @user-gf4oy6qo4u sorry we do not have any videos planed on HCI devices at this time. I checked with the team and we do work against a variety of services but not that one.

@dagg497 2 ай бұрын

Adsl gen 2 not being available in Fabrics Warehouse view and only pipeline made me think this weren't an option... Again as with all Azure products, I don't think their File menu system works any good with each Views; Lake/Warehouse/workbooks/File storage having entirely different GUI teams confusing and apparently hiding viable options, tricking you into thinking there' limitations. Not only that but the Workbooks and separete GUI for Databricks has a horrible File explorer and db explorer expanding to the right instead of downward (Good luck where you are three levels down when the screen is full of expanded big menus) lile normally would be more suitable as in Visual Studio or the actual Windows OS

@Tales-from-the-Field 2 ай бұрын

Hi @dagg497 per Bradley, "Hi dagg497, not sure what this comment is to in regard to the video. For the Data Flow Gen 2 you wouldn't really use this to load ADLS Gen 2 as this is a transform mechanism to land data into OneLake, Data Warehouse or Data Lake. Pipelines do have ADLS Gen 2 as a destination and they are used more for data movement."

@vishwassee 8 ай бұрын

i am getting this error after i publish error: GatewayCannotAccessSqlError Couldn't refresh the entity because of an internal error any idea how to fix it?

@Tales-from-the-Field 8 ай бұрын

Oh @SQLBalls, your ears must be burning this morning!

@SQLBalls 8 ай бұрын

Hi @vishwassee, looking at the error I found a similar issue and a solution on the Fabric help forum. It looks like they needed to add a firewall rule in their on premise system to allow them to access the SQL Endpoint. I'm going to get the @Tales-from-the-Field account to post the link to the forum post.

@Tales-from-the-Field 8 ай бұрын

@@SQLBalls here is the link you sent me! community.fabric.microsoft.com/t5/Dataflows/OnPremise-Gateway-cannot-write-to-lakehouse/m-p/3417668 hope this helps @vishwassee

@ricardoabella867 8 ай бұрын

How new data in sql server is shown in one lake, do you have to import again to ms fabric?

@Tales-from-the-Field 2 ай бұрын

Hi @ricardoabella867 per Bradley, "Hi Ricardo, when you import your data you would either load it as files or as a table. If you load it as files you would have to do work to land this as a table, however if you load it to a table you could select a Lakehouse or a Warehouse table. At that point you do not need to do any more work the data is accessable to you."

@TeamCykelhold 9 ай бұрын

interesting, but seems inefficient for streaming data from key onprem tables. Easy enough if you need something updated every few hours, not great for much else (?)

@Tales-from-the-Field 8 ай бұрын

Paging @SQLBalls !

@vishwassee 3 ай бұрын

Hi Is there a way to connect to on-prem SQL server using Fabric Notebook?

@Tales-from-the-Field 2 ай бұрын

Hi @vishwassee per Bradley, "This is a really interesting question. I don't want to say no, but it may not be. The only possible way I could see this working would be if you have an a site to site VPN connecting your on premises to Azure, and there is a network that you have a network gateway set up to that main network, and you have an F64 provisioned and a Managed private endpoint for your workspace that is attached to that network. Provided that you have end to end connectivity and can hit a source using jdbc or pyodbc (probably utilizing SQL Authentication), maybe this would work. Keep in mind I'm not a networking expert, and I do not have the means to set up this envrionment and proof it out..... but MAN do I WISH I could, because if it did work it would be really cool!"