xSkrape for SQL Server, Visual Studio Extension - Quick Start

---

Okay, Why?

There are lots of ways to get data into databases - do we really need another? I believe the answer is "yes" if the alternative can do three important things:

• Reduce code. Less code can go hand-in-hand with faster turn-around on development and less maintenance in the long term.
• Lower latency. Rather than have jobs that move data around, why not access the data in its live form?
• Easily integrated in a variety of solutions. This leads to more flexibility and architectural choices.

xSkrape addresses each of these points:

• Reduce code. The xSkrape script generator lets you describe what you want declaratively and it'll do the work of creating a wrapper function that can be used in your T-SQL as if it were a table or view. The amount of effort required is remarkably small, in part due to the power and flexibility of the XS.QL language. Reduced code = reduced overall cost, even when you go beyond the free benefits of xSkrape.
• Lower latency. From web API's that return JSON, to extracting data from Excel files stored in your organization, to extracting bits and pieces from flat files - access data exactly as it sits. Our goal is to reduce the "E" in "ETL"!
• Easily integrated in a variety of solutions. xSkrape concepts (e.g. XS.QL) can be used in SQL Server, .NET, and Excel. Within SQL Server, the effort of moving between hosting models (described below) is trivial. Look for future articles where we will discuss other advantages over other ETL approaches, and new components that will work hand-in-hand with xSkrape to address the entire ETL stack.

How Does It Work?

xSkrape for SQL Server is similar to other SQL products such as Microsoft's geospatial libraries that add functionality through CLR functions. Our library is rather tiny - mainly because its heavy-lifting is off-loaded to an application layer. This has the benefit of not requiring you to bloat your databases with dozens of assemblies.

There are two ways to use xSkrape for SQL Server: use our copy of the application layer (xSkrape-hosted), or install your own copy of the application layer (self-hosted). Both hosting approaches have different billing methods, but both billing methods share the concept of using xSkrape credits. These credits are associated with your free xSkrape.com on-line account.

Why use credits? This offers you more flexibility: start by using xSkrape-hosting and switch over to self-hosting at your leisure without losing anything - there are many variations of what you can do with credits. In fact, we award 250 free credits every month, making xSkrape-hosting effectively free for low-to-moderate usage. If you elect to self-host, every SQL machine that uses the xSkrape libraries is charged 50 credits for the first month of use, and 300 credits every month after that. This means self-hosting can be free, too, for the first month of familiarization. Credit packs can be purchased on-line, as needed.

You can find your 36-character client keys on the My Account - Queries and Keys page:

There are pros and cons for each hosting approach. For self-hosting:

Pros

• Unlimited usage, per machine, per 30 days. With xSkrape-hosting, billing is per request, where self-hosting is time-based.
• Control over scaling and versioning. If you need more horsepower in the application layer, feel free to add more compute resources. That type of control extends to versioning: you can decide when (or when not to!) upgrade.
• It supports the preferred security model. With xSkrape-hosting you either need to store your client key inside your SQL objects or in an environment variable present on each SQL box that communicates with your application layer. With self-hosting there's another option: centralized key storage in a configuration file.
• No need to send queries to us. With xSkrape-hosting, we necessarily need to broker your xSkrape queries. This means we can't handle file-system access (currently), and for some, security considerations might preclude using xSkrape-hosting.

Cons

• More components to maintain in your environment. xSkrape-hosting involves zero additional management on your part aside from using the script generator tool to enable your databases for xSkrape access.
• For infrequent use, self-hosting can be more expensive. Our usage rates are published here. (Truth be told, "more expensive" is still quite inexpensive compared to other options.)
• xSkrape-hosting gives you updates to XS.QL automatically. As xSkrape evolves, you gain access to new capabilities immediately.

What About Security?

CLR Assemblies

Some organizations might prefer not to use CLR libraries based on policy. Does this need to disqualify xSkrape? You could alternatively identify one or more dedicated "ETL databases" where an exception is made for xSkrape. You can also use xSkrape .NET components as found on NuGet. (xSkrape-hosted and self-hosted.) Watch for more .NET tools that can be used from SSIS that leverage xSkrape technology.

Furthermore, changes coming in SQL 2017 will have CLR assemblies facing a higher security hurdle, as noted here. The important take-away is: "Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database." We do sign our two deployed assemblies with certificates, meaning you can follow this best practice under SQL 2017. (The need for "UNSAFE" for xSkrape comes from the fact it does access external resources, by design.)

SSL

Communication with xskrape.com in the xSkrape-hosted scenario uses SSL. By default, self-hosting will only use SSL if port 443 is selected and SSL is already set up on 443 - but you can manually configure any installed host site / port to support SSL. After https has been added as a valid binding, simply change http://hostname:port to https://hostname:port.

Keys

You've got a few options when it comes to securing your client keys:

• Embed in objects. This is one of the options with the script generator, largely because it's easy: we can get you a functional object right away. However: leaving keys in your objects is only viable if you have a very high level of trust and control over your databases. (Even if you do, the other options are generally better, especially since they aren't overly hard.)
• Use environment variables. This works for both xSkrape and self-hosted. For both, the environment variable is XSKRAPE_CLIENT_KEY. For xSkrape-hosted, it should be set on all machines that are running your xSkrape-enabled databases and be "visible" to the user your SQL Server instance is running as. For self-hosted, it should be set on the application host and visible to the user that you set when you installed the application host. Environment variables have the advantage of scoping either by machine or user, meaning you can effectively hide it from all but the identity that needs it.
• Use config file. This only applies to self-hosting. When you installed your application host, you could supply your key and it's stored in the web.config file for the application host. (If you need to change it, that's where you'd look.)

As far as the key itself and having to remember it each time you want to generate an object: we offer a "vault" to store it in, protected by a password of your choice. To use this feature, select "Manage Your xSkrape.com Account", supply the client key you wish to encrypt, and a password to encrypt it with:

We store the key on your machine in encrypted form, so your password is used in the code generator in place of needing to supply your client key. Note: when you generate code with the option to include your client key in the generated object, it will be exposed there regardless of whether it was vaulted or not. (We recommend using the option to not embed your key in generated objects!)

What's the Quickest Way to Start?

After you've run the "enable" tool on a database, you can test it right away:

Next, use our examples! The intention is if you click on these links:

... you should arrive at parameters that can generate a fully-working object. Click "Generate" to create the script for a SQL wrapper:

... then click "Execute" to commit the object to the database. After that, click "Test" to try it out. If we could retrieve tabular data in the test, we'll show it in a grid to preview the result.

A typical follow-up step would be to parameterize our generated object. We might for example generate the following object using the URL "https://www.quandl.com/api/v1/datasets/WIKI/MSFT.csv?sort_order=desc&collapse=daily&trim_start=2017-10-01":

This is fine if we wanted the object to always get data for the MSFT stock symbol, but we might adjust the object to accept any symbol:

Now we could use it in some larger query like this:

Some General Q&A

Q: Can I have more than one application host with self-hosting?

A: Technically, yes. You'll need to embed the host URL in your objects (currently), so whether you do that dynamically or statically is up to you. For billing, consider this example topology:

Are you billed based on the count of machines along the bottom? Actually, no: it'd be based on the lines that connect to your application hosts. (It just happens with one host, the count matches the number of connecting SQL machines.)

Q: How can I get in touch with questions, suggestions, etc.?

A: Drop us a line on our contact page.

Q: Are there any limitations on usage?

A: Our license agreement terms outline any conditions we have.

Q: Where can I find more examples?

A: We have some other examples published from our "older" script generator, which is conceptually very similar.