How to Ingest SQL Server On premise data to Microsoft Fabric Lakehouse Object

Hello and welcome to this Microsoft Fabric tutorial

In this tutorial, I'm going to show you how to ingest data into the Synapse Data Engineering Lakehouse object of Microsoft Fabric from online environments such as SQL Server

So, let's get started. Now, in order for you to be able to try this out, you need a free Microsoft Fabric account

which you can easily create. My case, I have my own personal tenant, so I am properly logged in

Now, when I click on this icon, I can see my login details and of course, I'm using

the trial version, which allows me to try different objects within the Microsoft Fabric technology

Now, before we dive in fully, I'm going to head over to my SSMS, the SQL Server Management System

So, in the SSMS, I need to provide my server name and the authentication, such as the Windows that is associated with my personal laptop

I can even use the SQL Server authentication. Of course, all this will set up when I install SQL Server on my local system

So I can use the system administrator and then I can put in my password and just click on connect

So you can see I'm connected to the SQL Server instance and of course you can see the Object Explorer

And I can click on this database to expand and see all the databases within my SQL Server

And of course I want to point to the sales data database and we actually want to connect to this fact sales table

Now I'm just going to click on new query. let's see all the records within the fact sales table that we actually ingest across in the cloud

service of microsoft fabric now by default the sql server management system going to point to the

master system database now we need to use our own sales data and just click on execute and then we

are right now within the sales data database now we can just write a simple select all state names

to fit all the records in the fact sales table so click on execute so these are all the records we

have 55 552 rows and that's fine so let's go back to the microsoft fabric now in microsoft fabric

the first thing i want to do is to create a personal workspace now to do that at the bottom

left and corner of the screen you're going to see this microsoft fabric icon so click on that

uh we'll point to rbi now in the power bi we're going to create workspace click on workspaces and

click on new workspace now we need to give name for the new workspace we're going to call it lake

house all other things are optional you can even click on this advanced and you can add users and

and groups to the workspace and of course you have to specify the license board I actually using the trial version so which is available for 60 days so click on apply all right so we need to create the workspace so the next thing we going to do

is to switch to the synapse data engineering component of the microsoft fabric so click on

data engineering and then we have the lake house so just click on lake house preview and then we

we can just give it a name let's call it SQS server and then we click on create

we are now within the SQS server lake house that we just created now we can go

ahead and fetch data using different kind of method or channels I'm gonna click

on get data I can upload the files directly if anyone set up new data

pipeline and use data flow gen 2 and the data flow gen 2 is exactly the same thing as power

query that is embedded in Microsoft Excel and Microsoft Power BI however this is online cloud

based power query service okay it has some more functionality than the desktop versions so I'm

to click on new data flow gen 2 right now we are in the data flow 1 so this is a familiar environment

the power query okay now because we actually want to get the data from sql server you can click on

input from sql server from here or click on get data and choose sql server database now we need

to provide the name for the server we can even optionally provide the database name and of course

we need to provide the connection credentials however we do not have any power bi data gateway

already settled now in order to be able to connect to on-premise data set we need to create a power bi

data gateway so i'm just going to cancel this i'm going to come back to this one later i'm going to

close this and i'm just going to exit so i'm going to come back to this bottom left here click on that

and choose power bi i'm going to see this icon to download so click on that and they want to choose

data gateway now we can see we can connect to on-premises data sources with power bi

gateway okay now we can easily download the download standard mode now i have it downloaded

already so it's actually here so what you need to do is to go ahead and install so you just double

click on that we can see the on-premises data gateway installation it's quite easy so it's

going to be installed to this destination which is fine just go ahead and click on i accept the

terms of use and privacy statements so click on install so you're going to actually click on this

yes to make changes to your personal device so just go ahead and click on yes that's fine

so you can see installing your on data gateway just wait for some few seconds for the installation to conclude now it fully installed on our personal laptop now you

can see installation was successful now we need to provide the email address to

use with this gateway now the email address must be the same email address

that we use you know in it that is connected to our tenant so I'm just

to come back here now this is going to be my email address excel jones

now this is going to be the email address my own personal email address

or my tenant so just go ahead and click on sign in

right so you can see you are signing as blah blah blah

and you're ready to register the gateway now that's probably fine so

register a new gateway on this computer let's click on next so i need to provide a name for the

on-premises data gateway so let's just call it sql server microsoft fabric okay now we need to

specify the recovery key now i'm just going to type in just like my password but i can actually

remember easily so we confirm the key and then when you're good to go click on

configure so we can see on-premises data and gateway the gateway SQL server

Microsoft Fabric is online and ready to be used so we can see the version of the

gateway so everything is working pretty fast power automate and of course the

power bi default environment now my tenant is actually registered in the

United Kingdom South so I can see all other things like the service settings

diagnostic network connectors and of course the recovery key so go ahead and

close this tab so I'm just going to click on get data and I want to point to

SQL server database so I need to provide a name for my server now when I swipe

over to the SSMS I can see the server name at the top here so I'm just going

come back to the Microsoft Fabric and type in my server name and of course I can optionally provide

the name of the database which is in this case sales data I can even write in a SQL statement

in the power query online just like I've done lots of videos on writing SQL in power query that

is embedded in Excel now I'm just going to scroll down for the connection credentials I'm just going

point to the data gateway so click on this drop down I can see the on-premises

SQL server Microsoft fabric that I just created now that's pretty fine and of

course for the authentication kind they can use the windows and of course

can specify the area but let's just click on this drop down you can even use

in a basic and I just going to type in SA and then for the password I going to just type in my password and click on next

All right, so we can see get data, choose data. So these are all the tables within the sales data database

That's amazing. Now I'm just going to check this box So you can see the preview of the fact sales table in it, which is awesome

Now what I'm going to do is just click on Create Amazing

So we can see the data in Power Query Online That is super cool

So that is the extraction from the on-premise SQL Server To the cloud service Power Query using the Gen2 connector

So now we can just perform different kind of information that we want to do here

Now, probably I want to change this date time data type to date data types

I'm going to click on this icon and I'm going to choose date. And of course, we can see the steps being recorded in the applied steps and everything is fine

So we just go ahead and need to publish to the Nick house

okay so when i click on this icon i can see we can publish right now or we can even publish later

so let's go ahead and publish right now all right so this is the lake house workspace that we created

so we can see the data flow one flow two and of course we can see the sql server we can see the

default data set and for we can see the sql server sql endpoints now in the sql server endpoint we can

write sql statement against the data set in the lake house so when i click on that i can see the

fact sales table that is ready to be used within the lake house that is super amazing and of course

you can just click on this new query to begin to write new query you can even click on new visual

query and create your measures let's just play around and write some few queries against the

data set so click on new query and that's you know the same thing as clicking on this

new query in the ssms so back here we can just write our simple select

from and then we can copy and even graph just like we do in ssms just grab that and then click on run

and that's amazing so we have our record in working pretty far this is basically how we

can connect to datasets on SQL Server on-premise environment to leak house in Microsoft Fabric

I hope you enjoyed this video if you do please like share comment and

and support this channel thank you and bye for now Cheers