Our SQL Gateways are a great way to get your data from your on-premise system into Popdock. SQL Gateways are restricted to a single database within your system, meaning only data from that single database will be available in your connector. This means that you need a connector for each database you want to connect to, meaning one gateway per database. Fortunately, the SQL Gateway can have multiple versions of itself hosted on the same server. This allows you to create connectors for all of your SQL databases thus, bringing your data to you inside of Popdock. This article documents the process needed to do so.
An established SQL Gateway with all the included prerequisites.
- This can be found in our SQL Gateway documentation
1. Go into your SQL data gateway folder inside the file browser and move your appsettings.json file to the desktop (The path is Local Disk (C:) -> Program Files (x86) -> Popdock -> SQL Server data gateway)
2. You will then right-click on the SQL Server data gateway folder and create a copy of this folder. Rename the copy to SQL Server data gateway [database name] to distinguish it from the original gateway.
3. Once steps 1 and 2 have been done, go to wherever you have the SQL Server gateway installer within your system. It should be in Downloads by default but it also depends on how software is managed in your system. Then, run the installer.
4. In the installer, click Next > on the bottom and then select Modify and click the Next > button again. From here, fill in the information for your server and authentication method as you did for your original gateway but change the database to match which one you want to connect to. (Tip: You can use the same token for this gateway as you did for the other one. This is recommended so as to not create confusion on your connectors. After you have done all that, click the Install button to finish the installation.
5. Once the new gateway has been installed, go into the original SQL Server data gateway folder, and transfer that appsettings.json file to the folder of the new gateway we created. This will bring over the connection settings for the new gateway into the new folder to separate it from the original.
6. Open the IIS and go to Application Pools and create a new application pool for your new gateway. You will want to name it something relating to the new gateway (i.e. SQLGatewayDBNAME). You need to create the new application pool as they are typically restricted to one application per pool. If you put the application for the new gateway into the original application pool, neither gateway will work.
7. Once you have this created, select the Sites dropdown on the left side and dropdown the Default Web Site or the site you have dedicated to your gateways. Right-click on this site and select Add Application. When the menu pops up, fill in the corresponding information to the new gateway. Name the application to match your application pool and then use the Select… button to select your application pool for the new gateway. For the physical path, click the button with the three dots and set the path to the new gateway folder. (The path should be Local Disk (C:) -> Program Files (x86) -> Popdock -> SQL Server data gateway – [database name])
8. Once this has been done, browse to the site to verify that you get the gateway confirmation message and copy the URL.
9. After you get the message, log into Popdock, go into Connectors, and click Add Connector. Select SQL Server as the connector you want to add and change the dropdown to the Gateway option with the authentication method you used. Enter the URL you copied from the gateway confirmation screen and enter your credentials for the authentication method you chose. Select validate and then click connect. A screen will appear with all the available lists and tables within that database. To finish creating the connector, you need to select at least one item from the list to finish the creation (you can always add more later using the Add Lists and Add Tables buttons within the connector). Once done, your connector will be created.
10. Repeat steps 3-9 for all of the databases that you wish to have connectors for.
Questions? Feel free to reach out to us at email@example.com