How to setup Azure on-prem Data Gateway

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
hi everyone today i want to show you how to access your on-prem sql database from azure uh as you can see on this screen i've got a diagram it helps you better to understand how this works from architectural perspective assume you've got an on-prem sql database and you want to use its data in a logic app but it's [Music] you can't have any other resources in on azure like function app or whatever for accessing your database from azure you need two different resources one on on-prem environment and other one on cloud environment so as you see in the diagram uh you need to install on-prem data gateway on a machine on your network to access your on-prem database and on the azure side you need to uh configure on-prem data gateway resource and then using that data gateway resource you can access your database or even other on-prem resources which is supported by on-prem data gateway uh let's quickly have a look how it works but before getting to that let's just search for install azure on-prem data gateway and look at the microsoft document here if you want to see a step-by-step instruction for installing on-prem data gateway you can look at this document and it gives you a step-by-step tutorial about installing the on-prem data gateway and then configuring that on the azure site uh but we can do here you can quickly uh install the on-prem data gateway and configure it on azure so you can save some time by watching this video but as a first step what you need to do is downloading the on-prem data gateway installer which is here in this link in on-prem data gateway documentation and you can download the exa file from this link once it's downloaded you can just double click that first screen accept the terms and condition installed yes i don't want to spend too much time on installing this because uh all the steps been explained completely in the microsoft document so just quickly install the gateway the two important things about installing and configuring gateway is uh the first one is uh in future step in the on-prem data gateway installation it asks for your credential to log into azure and at this step you need to use account and bear in mind that you need to use the same account when you want to configure your gateway resource on azure that's the first important point the next one is uh if you are installing your on-prem data gateway in a specific region let's say australia east in this case then you need to use the same region for configuring the data gateway on azure resource as well this is two important things if you forget or ignore these two steps definitely you can't configure your data gateway properly so that's the step i talked about it it's asking for email address i enter my email address and if you click sign in the sign into azure dialog box pops up and you need to log in with an account so this is the account i've created earlier for logging into data gateway this installed just started with dg for data gateway and i just selected account to put your password in here click sign in next step asking are you registering a new data on this computer select yes and then next and you need to put in a name for that say demo data gateway and need to enter a recovery key if anything happens to your data gateway and you want to reinstall it or you want to recover it you need to know what this gateway recovery key is so i'll just enter something here you need to confirm that and here we'll use this region to connect the gateway to cloud services it's set to australia east so i'm happy with this region but if you want to change the region you can click on the change region and then select your desired region here and bear in mind as i mentioned earlier you need to log in with the same account that we use the dj account to azure portal and then set up the azure data gateway resource in australia is otherwise it won't come up in the list of the gateway i'll show you that in a bit so let's click on configure there you go the gateway demo data gateway is online and ready to be used uh we are done with the installation of the on-prem data gateway agent uh what we need to do is configuring the data gateway resource on azure as well so go to the portal i've logged in with dj account data gateway account the account that i used to set up the on-prem agent yeah i click on add and data gateway select on-prem data gateway click on create you need to enter a name for that say demo data gateway select your subscription select the resource group that you want to create this data gateway in and you need to pick up a region here as you see the australia is australia is selected here so the on-prem agent is coming up here data i selected different regions say australia southeast you see no instance found message is displayed and we can't configure our data gateway resource on azure so we just need to configure it in the same region select the demo data gateway here and click create okay the gateway is created now go here to the resource group it will show up here after deployment quickly go to resource it shows you that the gateway is configured and online so next step is accessing your sql database from here from azure but before that if i go to my sql server management studio i've created a sample database a test database and added a table there the person table and added two dummy records here it's just a given name and surname so what we want to do we want to create a logic app and fetch these tables data on the azure site so if we go back to [Music] azure click on marketplace open logic app here choose logic app click on create select your subscription the resource group that you want to create your logic app here this is the logic app name i want to call it data gateway demo now when i created in australia yes press on review and create it's creating a blank logic for us go to resource goes to the logic app designer select blank logic app this is a logic app graphical user interface designer you can design your logic app using this tool or you can use arm template for provisioning your logic app it's up to you but for demo purposes here what we are doing is using the graphical designer uh each logic app has a trigger and a couple of different actions for trigger here we just want to select the http request trigger when http request is received and add a new step and then select sql server and then we select get rows in the authentication type we select windows authentication and we need to give a name for the connection so it tries to create a connection between logic app and your database so let's put it here zip1 correction as you can see here you've got a bunch of fields that you need to uh fill in in the gateway connection gateway you see your gateway name so you select the gateway name here for the sql server name uh you need to put in your server name so if i go to the sql server management studio this is my server name desktop and gg vt6 tv that's our sql server name come back here look at that back what's the sql database name let's test what's the username and password maybe this bit confusing when you are looking into the document uh it depends if you are testing this data gateway on your local machine and you just want to test it like what i want to do i'll show you later you can grab a user from existing user and use it but if it's a production version you want to use this in a real environment you need to create a service account in azure active directory and join your server to the active directory and then uh logging using that user but for demo purposes what i would do i go here if you look at the security section in your database in the user section for all databases definitely you've got the dbo user so if i double click on dbo it tells you what is the login name for that dbo user so that's the windows user or the login name which is used for windows authentication to access this database in this server so what i need to do is just copy this and put it here and just need to enter my password for this user here as well and click on create there you go function is created now we can fetch data from confirm sql database okay it's asking what's the server name can pick that up from the down list what's the database name you can pick it up from here what's the table name again it's populated here in the dropdown select table name and say i wanna have top five record from this table as we have only two records uh you should be able to see that two records the dummy record that i've created earlier just save that and we can press on wrong perfect run was successful and if we check the result in the results section we've got adam smith with id1 and android with id2 and these data coming from this person table this one as we saw earlier and using on-prem data gateway agent and on-prem data gateway resource on azure we are able to access on-prem databases and a couple of other different on-prem resources like p-stock server or etc you can have a complete list of them in microsoft documentation so just quickly show you in the resource group now we have a data gateway resource go back to the diagram this is this resource and we've got a logic app and its connection to the data gateway and on the on-prem side we've got this on-prem data gateway agent which is this one and then these two components communicate to each other and pull data from on-prem sql database and serve it to uh different azure resources and in this demo it was a logic app thanks for watching this video hopefully uh helps you to configure your data gate very quickly and properly uh one more time again just bear in mind to use the same service account or same account when you are configuring your data gateway agent and data gateway resource on the azure as well as the region that you are configuring that in next video i'll show you how you can provision the connection i mean the sql connection we have it here using arm template so you can automate your uh automate your process using on template other than doing that manually thank you guys
Info
Channel: OzApp Builder
Views: 498
Rating: 5 out of 5
Keywords:
Id: jK0iXIU5PMw
Channel Id: undefined
Length: 21min 11sec (1271 seconds)
Published: Sat May 22 2021
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.