Configure a self-hosted IR as proxy for an azure SSIS IR in azure data factory with SSISDB on azure DB with private endpoint

Subject:  Configure a self-hosted IR as a proxy for an azure SSIS IR in Azure data factory with SSISDB on azure DB with private endpoint

In this blog we are going to learn how to configure a self-hosted IR as a proxy for an azure SSIS IR in Azure data factory with SSISDB on azure DB with private endpoint, also we will learn 1. How to create VNET TBVNET, 2. How to create two subnets one for azure SQL DB & one for remote jump host, 3. How to create Azure SQL DB, 4. how to create VM and install SSMS on it, 5. How to create a private endpoint for Azure SQL DB, 6. How to connect from jump host and make sure private endpoint working 7. How to create ADF Azure Data Factory, 8. How to create self-hosted IR and test, 9. How to create SSIS IR with self-hosted IR as proxy and VNET, 10. How to create SSIS Package and deploy to SSISDB on Azure SQL, 11. Test in SSISDB in the pipeline.


First of all, we are going to create a virtual network, for creating a virtual network go to the Azure portal, in the Azure portal click on the search bar and search for Virtual Networks and click on the +Create button when you will click on the +Create button a window will open as shown in the below picture, insert the required information ie Subscription, Resource group, Name, and region, click on Review + Create button.


Our Virtual network is ready, and now we are going to create Sub-Net, to create a sub-net go to the Azure portal click on the Subnets at the left side under the Settings tab, click on the subnets it will open a window which is in the below picture, insert the name and let remaining fields as default and click on Save button we will use this sub-net for SQL, and we will create another subnet which we will use for our virtual machine so create the second sub-net as we created earlier.

Our subnets are ready now we have to create our azure SQL database, for creating the database go to the Azure portal and click on the search bar and search for SQL databases, click on SQL databases and Create a new SQL database by clicking the +Create button, a window will open, fill the required information which is Subscription, Resource group, Database name as picture is shown below.

After giving the name the next field is *Server, we have to create the new server by clicking the Create server button, it will open a window as shown in below picture, insert the required information and then click ok. by clicking ok it will create a new server which we will use for access to our SQL database, after creating server the next field is Configure Database, by clicking this you can choose the data size as per your requirement and it will also affect on the cost of your Azure SQL database after configuring the database click on the Review + Create button and then click on create. 



As our Azure SQL Database and Server is created now we have to go to our next step which is to create a new Virtual Machine and install SSMS on it, to create a virtual machine we have to go on the main page of our azure portal, on the top of dashboard click on the Virtual Machines, it will open a window, on that window we have to click on +Create Button and then click on a new machine, select the subscription, resource group, name of the machine then select the operating system and then select the VCPU and then insert the username and password then allow the selected port which is RDP3389, and then click on Disks on the disks page, you can select the OS Disk type and Encryption type as per your requirement then click on networking in the networking tab select the virtual network then select subnet which we created earlier, leave the other fields as default and click on Review + Create button and then click on Create.

Once our virtual machine gets ready the first thing we need to do is go to the resource and click on connect and then click on the RDP, download the RDP file and double click on that it will open some dialog box that we need to click on that and it's going to ask for the user name and password which we have created earlier, by clicking ok it will open and Setup the virtual machine for its first-time use which is shown in the picture below.

Once we logged in to the virtual machine the first thing we need to do is to download and install the SSMS, to install SQL Server Management Studio, first of all, we need to click on the local server, and then we have turned off the IE Enhanced Security Configuration, otherwise, it will bugging every time we open the internet explorer for the download, once it becomes off just close the local server and open the internet explorer and search & download SQL Server Management Studio and click on run it will take some time and will open the installation wizard by clicking on install it will start installing SQL Server Management Studio on your virtual machine.


While our Microsoft SQL Server Management Studio is installing let's make a private endpoint by creating a private endpoint we can connect with the private IP, not with the public IP, for the creating private endpoint go to the Azure portal and click on the Private Endpoint Connection under the Security tab on the dashboard, then click on + Private Endpoint and then fill the required information which is Subscription, Resource group, Instance name and region then click on resources, 


In the resource tab, we need to select Subscription then resource type then resource,s and finally select the Target sub-resource and click next to the configuration.  




In the configuration tab we have to provide the Virtual Network name and the Sub-net which we have created for the SQL separately, after that just click on next to tags and then click on Review + Create to create the private endpoint name. 

 


Once our private endpoint will be created we will have no access to connect this server from any public network so that's why we are creating this virtual machine on the same virtual network so we can connect to it, after installing Microsoft SQL Server Management Studio we need to turn off the public network access, for that we need to go to the Azure portal and click on our server in my case it is techbrothersserver click on the server name then scroll down at the left side of our dashboard click on the firewalls and virtual networks under the security tab and make it, YES to deny public network access and click on save which is shown in the picture below.

Once our virtual networks settings are done, just go to our Virtual Machine and try to connect Microsoft SQL Server Management Studio by providing azure server name, user name, and password.

Next, we have to create an azure data factory, for creating azure data factory we need to go on the azure portal and in the search bar search for the Azure Factories click on that and it will open a window click on + Create and then fill the required fields as shown in the picture below which are Subscription, Resource group, Region, Name, and Version and then click on Git configuration then click on configure git later then click on networking and then click on Review + Create and then finally hit the create button.


Once our Azure Data Factory is created next we have to create self hosted integration runtime (IR), for creating self hosted integration runtime go to the Azure Data Factory Studio click on manage tab which is at the left bottom of the azure data factory studio click on + New and select Azure Self hosted and then click on continue, then select self hosted and then again click on continue then at final page give name of your self hosted IR and then click on create, once it will create that will give you the two options the first one is ''click here to launch the express setup for this computer'' I am going with the second option which is ''download and install integration runtime'' click on the second option and it will start downloading once the download completed right click on the setup file and click on install, once you click on install it will open installation wizard by click on the next then click on the accept agreement and then click on install, after installation when you open it first time it will ask for the keys which you have to copy from Azure Data Factory which the picture shown below, provide the key and click on register it will take some time to register then click on finish, and go to the azure portal you can see the status of self hosted integration runtime in the monitor.



After creating the self-hosted integration runtime we need to create SSIS IR with self-hosted IR as proxy and VNET, for that go to the Azure portal click on the Integration Runtimes click on +New then select Azure-SSIS then click on continue and then fill the required fields as shown in the picture below, fill the required fields and then click on continue,  

On the next window, the first option is ''create SSIS catalog (SSISDB) hosted by azure SQL database server/managed instance to store your projects/packages/environments/execution logs'' in my case we have already created the Azure SQL Database, just click ok that because we have to upload our SSIS packages to SSIS DB that is on our azure SQL Database, click on that then select subscription, Location, Catalog database server endpoint then admin user name, and admin password and then click on continue.    


On the next window, the first option is Maximum parallel executions per node, which means how many jobs you want to run on this node, in my case, I am ok with 2, and then select a subscription, vnet, and subnet which we were created earlier for our private endpoint, at the last we have to select a very important option which is '' Set up Self-Hosted Integration Runtime as a poxy for your Azure-SSIS Integration Runtime'' click on that and select the self-hosted integration runtime and then create new staging storage linked service, if you have already the storage account you select that otherwise you will have to create the storage account first and then will create the staging storage linked service, then select the staging path where our folders will be created while we execute our SSIS packages, then click on Vnet validation once validation becomes completed click on continue and then click on create. 

Meanwhile, our SSIS IR is in process one thing we can do is to create our SSIS packages, so let's go to Visual Studio 2017, click on File then click on new + Project and then select integration services project, name the project and click ok, which is shown in the picture below. 


 
Once our SSIS Project is created let's create the Data flow task, search for OLEDB Source and drag it, double click on OLEDB Source, and create a new connection, provide the server name, username, and password and then select the database and then click on test connection and click ok which is shown in the picture below.

Once our OLEDB source is created let's click on the OLEDB destination and drag it, connect with the OLEDB source and double click on OLEDB destination, and select the same table which has been selected in the OLEDB Source, go to the connection manager and make the ''ConnectByProxy'' True the build the project. Click on the created file path and find the ISPAC file path which we have to use in our Virtual machine which we have already created, just copy the ispac file and paste it to the desktop.  



As our SSIS IR is ready, click on the monitor to see the details of our SSIS IR, and then go to our Virtual Machine and here is our SQL Server Management Studio, go to the integration services catalog, and right-click on the SSISDB folder and create a new folder name it and click ok, once our folder created just click ok that and deploy the package, once you click on deploy the package it will open a window which is shown in the picture below, it will ask you for the ispac file which we have pasted on our desktop, select the ispac file and click next provide the login information click on connect, once its connected click on next and Deploy the package.


Once our deploying completed click on the project and expand it and click on the packages and execute the package, the execution will take some time, keep watching the status of execution. once our package execution is completed let's create a new pipeline, to create a new pipeline go to the Azure portal, go to the author and click on the new pipeline, name the pipeline, and drag ''Execute SSIS Package'' to the working window, go to the setting in the bottom and select the ssisir, select the package location, folder, package, logging level, and then go to the SSIS parameters, in my case I don't have any SSIS parameter so click on the next tab which is connection manager, see all the fields and select as per your requirement and then debug the package.



Video Demo: Configure a self-hosted IR as Proxy for an azure SSIS IR in Azure data factory with SSISDB on azure DB with a private endpoint.









1 comment:

  1. The best thing about intermediary worker is that you don't have to have a worker with uncommon necessities as it functions admirably with both light and substantial machines just as workers. get more info

    ReplyDelete