Free your On-Premises Data with Data Connectors

Lately it seems that it’s all about cloud and mobile. Every technical conference or user group you attend, you hear speakers talking about leveraging the cloud to augment mobile solutions. Each new or exciting piece of technology seems to be squarely aimed at helping you move your application data to the cloud and consuming the data from various mobile platforms.

All this while, however, a voice inside your head may be screaming – “What about my existing data?” If you are an enterprise developer, you most likely are dealing with a huge amount of data that resides on-premise in your SQL Server, Oracle or any other relational database. And for lots of situations, you have legitimate concerns about moving all your sensitive, on-premise data to the public cloud. Relax. There’s nothing wrong in your data storage strategy, since you control the data and have a host of internal applications that depend on the data. However, you could use a bridge to the new world – why can’t your on-premise data be consumed from mobile devices? How can you easily expose your data to the cloud and offer it as a service?

Enter Data Connectors in Telerik Backend Services. Now, Backend Services is a whole BaaS offering in the cloud – it manages your data, authenticates users and sports cross-platform push notifications and many other features. But Data Connectors is a real exciting part in Backend Services that securely bridges to your on-premise data, opening it up for consumption and updates from any platform, mobile or web. You get to keep your data in-house and enjoy a free service layer that bridges to the modern devices. Enticing isn’t it? Sure you can go look up documentation or the overview, but developers like to see things in action – SQL Server data exposed as a service through Data Connectors. That’s what we’ll do in this article – buckle up.

The Data

First, let’s get a handle on the on-premise data that we want to manage and expose. Data Connectors can work with the following data storage relational databases: Microsoft SQL Server, Oracle DB, MySQL and PostgreSQL. Let’s pick the commonly used SQL Server – I have a SQL Server instance running locally, and have added the famous (or infamous?) NorthWind Database to it, as seen below.

NorthwindDB

The Northwind database offers up a realistic collection of related tables filled with data. Let’s say we want to deal with the Employee table – the data (as shown below) needs to be exposed for consumption.

EmployeeTable

What are Data Connectors?

Let’s officially define what we are about to do here. With Telerik Data Connectors, your on-premise data is directly exposed to the cloud. It is not a one-time data transfer to Backend Services or some kind of periodical synchronization. Your data continues to live only in your data store.

Once you define what part of the data you want to expose, Telerik Backend Services will create REST endpoints for accessing the data. Whenever those endpoints are invoked, the request will be automatically piped to your data store. The response from the data store will be transformed to the standard Telerik Backend Services response format and sent back to the client.

In short – your data gets exposed as a service that you do not have to write! Your data stays in your on-premise database and you get fine-grained control over what you expose. The goal is REST-based endpoints with standard data in JSON format, which can be consumed from any platform. Oh, and CRUD operations come free!

Backend Services Project

First, and not surprisingly, you need a Backend Services project. Backend Services is a part of the Telerik Platform – so head out to http://platform.telerik.com and sign in through your preferred identity provider. If this is your first time, sign up for a free trial and look around at all the other tooling available.

Create an App, then create a Backend Services project, as shown below – yes, you can name it after your favorite comic.

ProjectCreate

Next up, go to Configure | Services on left menu and add a Data Connector to your project. A Data section appears in your project.

DataConnectorStart

Make note of the API Key for your Backend Services project – it’s unique and you will need it down the line.

APIKey

Click on Data | Data Connectors and then the Create a Data Connector button. The Data Link Server screen appears – choose Setup New Data Link Server and hit the Download button.

DataLinkServerDownload

The Data Link Server

Clearly, there is some magic going on under the covers – and that’s the Data Link Server. Think of it as a polished Web API project that sits on top of your data. You do have to host it yourself and it is a Windows only setup. There are detailed instructions on how to host the Data Link Server, but it boils down to it being hosted on IIS (Internet Information Services) on the same machine which houses your relational database, unless you want to incur data transport costs.

You are going to obviously need IIS to host the Data Link Server and the IIS Manager to get started. If you don’t have it, it is easy to add from Add/Remove Windows Features dialogue, as below.

InstallIS

Once you are in IIS Manager, go ahead and create a new Website, as below:

CreateNewWebsite

Give your website a name, point to a physical path (this will be empty for now) and keep it open on Port 80, as below.

WebsiteSetup

Now that you have an empty website, let’s import the contents of the Data Link Server by choosing the zipped download from Backend Services project, as shown below.

ImportApp

ChooseZip

Follow the steps in the dialogue, until you see the success message, like the one below.

InstallationSuccess

You have now successfully hosted the Data Link Server! You can treat the server as a black box and should never have to touch it, but you can look through what’s inside if curious. Look in the physical path directory, and you will see a standard Web API project, as below.

DataLinkServerInternals

Back in IIS Manager, make sure to check the website bindings – I like it running on ports 80 or 9090. If you set up everything correctly, you should be able to Browse to the site from IIS. It runs on LocalHost locally, but should also be accessible from the specific IP address that your machine is running on. Upon browsing to the site, you should get a JSON download – when it says ‘Result: OK‘, your Data Link Server is all set. Congratulations!

DownloadJson

ActualJson

Security Setup

When you are opening up your on-premise database to be a cloud service, you are bound to have security concerns, which need to be addressed. The Data Link Server is the middle-man between your database and Telerik Backend Services, and communication back and forth needs to be secured. By default, the Data Link Server works over HTTP. But it is highly recommended that you configure an SSL certificate so that Telerik Backend Services can communicate with the Data Link Server over HTTPS. Since the Data Link Server is just an IIS application, the standard ways of establishing SSL over IIS would work.

Additionally, you could set up a secure authentication mechanism between Telerik Backend Services and your instance of the Data Link Server. This authentication is built-in – you simply need to configure it with a secret key known to both parties. This key is automatically produced by your Backend Services project and can be regenerated; simply drop it in your Data Link Server, and voila – you have authenticated communication.

Also, the Data Link Server needs to access your SQL Server instance, needless to say. So while you are dealing with security, it may be a good idea to set up secured data access, lest you see authorization errors down the line. The Data Link Server is essentially an ASP.NET Web API application, which can run with or without user impersonation, as configured in IIS. Accordingly, the NetworkService or specified ASP.NET Identity also needs access to your SQL Server. I personally like my IIS applications running in their own AppPool with pass-through authentication. Accordingly, you’ll notice that I’m setting up the IIS APPPOOL\<AppName> as a login option in SQL Server and granting the account appropriate permissions as per server roles, as shown below.

SQLSecurity1

SQLSecurity2

Remote Access

Your next challenge would be setting up remote access for the Data Link Server. Telerik Backend Services needs to be able to reach the URL where the Data Link Server is hosted, and this needs to be a public link out on the Internet. Normally, once you host the Data Link Server for your enterprise, you would have a production URL with DNS mapping, but you don’t have that luxury when first trying things out in IIS Express. Sure, you can enable remote access for your IIS application, but it would still not be truly public unless you have a static IP address.

Enter NGrok – a wonderful service suggested by the brilliant Ed Charbeneau. While NGrok can do lots of other things, the one that you would be interested in is the ability to create a dedicated tunnel from the public Internet to a port on your local machine. This essentially means you can make a public URL off of anything running on your Localhost and access it from anywhere. How cool!

NGrok

So, go ahead and get yourself NGrok and then run the following from command line:

$ <File Location>/ngrok 80

This opens up your port 80 to being an NGrok tunnel and your locally hosted Data Link Server on IIS will have an URL like http://<GUID>.ngrok.com. Now we’re all set with remote public access, exactly what we needed for Backend Services to reach our machine.

Data Connectors Configuration

Now that your Data Link Server is all set up and publicly accessible, the rest would be easy like a walk in the park. Head back to Telerik Backend Services portal and into your project – Click on Data | Data Connectors and then the Create a Data Connector button. This time, you can choose Use Existing Data Link Server and the configuration screen comes up, as below.

DataConnectorConfig

The important pieces of information to plug in for configuration are the type of database you are connecting to, the public URL of your Data Link Server and the Connection String to your database. Notice that for the connection string, I simply used the name of my SQL Server instance and pointed to my exact database, with pass through authentication. If you did set up everything correctly, try hitting the Test button to check communication between Backend Services and your Data Link Server. If it takes too long, there are problems reaching your Data Link Server and you should re-check all settings. You’re golden once the test connection succeeds – you can then Save the Data Connector and see it listed as available in your Backend Services project, as below.

AddedDataConnector

Data Mapping

Once you have your Data Connector configured, it’s time to have fun with your data – SQL Server data in the cloud. A Type in Backend Services is like a database table, and you should create some mappings between your data in SQL Server with its corresponding representation in Backend Services. In your project, click on Data | Types and then choose Create Type from a Data Connector. Make sure to point to your newly created Data Connector and then choose a table entity from the dropdown, as shown below – yes this is coming straight from your SQL Server database!

ChooseSourceTable

Give your SQL table a name in Backend Services and then move forward with some data mapping. This mapping is between SQL Server table (source) and corresponding entity (destination) in Backend Services – you can choose which columns you want as well as their data types, as shown below. Granular control is nice, isn’t it?

DataMapping

Your Data in the Cloud

Once you are done with data mapping, save your Type and then browse to it. The SQL Server table data is available directly in your cloud portal, as seen below. Keep in mind, nothing is being hosted for you in Backend Services – this is rather a view to your on-premise data in SQL Server, but with full edit capabilities!

SQLDataInBackend

The end result of using Data Connectors should be a REST endpoint through from which you can easily consume the data. Let’s get to work on that…oh wait, it’s already done! Simply navigate to https://api.everlive.com/v1/<your-api-key>/<entity-name>, as seen below in my browser. Your on-premise SQL Server data available as JSON through a REST endpoint – without having to write a line of code!

RESTAPIData

Keep in mind, this REST endpoint isn’t just for data consumption – it supports full CRUD operations back on your on-premise data. Try it with your favorite HTTP tool, like Fiddler.

Having a REST endpoint is great, but it is not always feasible to work at such low level. Wouldn’t it be nice if there were SDKs that offered wrapper functionality? Glad you asked. Since you mapped on-premise data through a Data Connector, Backend Services treats it like any other cloud-based entity. And this means you can consume or work with your RESTful data through any of the available SDKs:

  1. .NET SDK
  2. JavaScript SDK
  3. Andoid SDK
  4. iOS SDK

These Backend Service SDKs cover any app on any platform – your data powering modern apps!

Conclusion

I know this was a long read, but hopefully this article shows you the value of Data Connectors.You no longer have to feel stifled with your on-premise relational data. Without writing any code and with just a little bit of configuration, you can use Data Connectors to expose your data as a modern, cloud-based RESTful service. And you have complete freedom to work with the data for any type of application, across any platform. Flexibility is a good thing!

Want to see more? There will be plenty of .NET and mobile love as we embark on TelerikNext this summer. If you are an enterprise developer concerned with legacy data or software in the modern cloud/mobile world, join us for an extended hands-on Workshop on ‘Modern .NET Apps‘. Use promo code BASU for a nifty registration discount and see you in Boston.

Comments