Powerful features of Python and SQL Server 2017

Python is a new feature of SQL Server 2017. It is primarily intended to allow Python-based machine learning in SQL Server, but it can be used with any Python library or framework. To provide a possible example, Hitendra shows how to safely use this feature to provide smart application caching, where SQL Server can automatically indicate when data changes to trigger a cache refresh.

MS SQL Server 2017 has implemented a Python script in tsql with a "machine learning Service using Python" by enabling SQL Server to add to its advanced analytics extension, now known as the Machine Learning service. This basically provides a way for a database programmer to pass data directly from Python. This usefulness is not limited to providing machine learning capabilities for data analysis, because Python has many easy-to-use modules and frameworks to solve many problems, such as the use of data structures to perform a large amount of computational work, graphical processing for analysis, network operations, database operations, network operations, or local/ The file system operation of the network. Obviously, many of them do best in terms of middleware, but in database systems, it is sometimes easier to communicate directly with external systems rather than relying on external processes to perform tasks by polling the data source. This is not a problem if there is one such solution in the database or data tier, and no security issues are provided.

Here, we will try to demonstrate an example of using Python in Advanced Analytics extension to show how the database triggers an external process to perform activities on the data provided as parameters. This is to consider security, data reliability, and transaction response time issues.

Python's use case

Some tasks can be done more easily by invoking Python scripts from SQL instead of relying on middleware. This is especially the case in which the event initiates a task in the database. Tasks may include

1. Send or receive data to a network-based system via TCP/HTTP/SOAP.

2. Use local platform resources, such as file system, network, or GPU.

3. Build real-time integration between one or more systems by using a common data format, such as Json,xml or YAML.

4. Generate data or files by communicating with external applications.

Of course, there are few potential drawbacks.

1. If you use Python to require Internet access, there is a risk that the data that must be kept secure may be accidentally shared by the Internet. Any Internet access must be carefully monitored by the network.

2. Allow the execution of Python scripts on the server through "Enable external scripting" to expose the security risk.

3. Resource-intensive Python scripts on the same server can affect the performance of ongoing transactions on large OLTP systems.

By measuring these advantages and disadvantages, it seems that python can sometimes play a useful role if the risk can be minimized. As an example, let's consider how we can use Python to build a data caching system for the supply layer.

Caching Sample Solutions

Caching data can improve the performance of your application. At the expense of the cache's storage cost, we can gain useful performance gains when encountering chat network traffic with the database and high resource consumption when the database is facing duplicate queries. When we build our cache infrastructure, we face frequently asked questions about when to flush cached content. We tend to adopt a simple solution that rebuilds the cache after a certain interval of time. However, this is very inefficient. Refreshes the cache better when the data changes, only refreshes the changed content. When we create, update, or delete data, we get real-time access to real-time. There are many tools and frameworks that can be used to resolve refresh issues, but they are subject to how to determine when data changes and when changes occur. The database is the best that all can do.

For the caching system we provide here, we will limit ourselves to the Microsoft stack to prevent Python itself.

• · Microsoft SQL Server (CPT)
• Service Broker isolates the transaction database.
• Python executes scripts that can update the cache via HTTP (Python 3.5 executables and libraries of the Anaconda release)
• ·. Net4.5.2
• ASP. NET MVC for our sample web UI
• The ASP. NET Webapi encapsulates the cache storage for our sample solution.

Here's a diagram of our sample solution cache system:

• The WebApplication provides a user interface for reading and updating data.
• • The Restful.cache application in our sample cache storage solution was built using ASP. NET WebAPI2, and its content type is JSON. The Http-get operation transmits data from a local cache (a static collection).
• · MSSQL Server (CPT) is a database server.
• Transdb OLTP database, busy processing transactions.
• The Cacher proxy database executes the Python script execution, and the script execution enables the "External scripting Enabled" option to open. See Microsoft.doc: Enable server configuration options for external scripting.
• Service Broker is a reliable messaging framework for SQL Servers that helps bridge cacher-agent and Transdb. You can process cacher-agent received messages to update the cache.
• · Python is an integrated scripting language for database systems that use SQL (CPT).

The architecture of the solution

In our solution, we will cache the entity "Product type name" in the Restful.cache application, and WebApplication will have the ability to create a new product type entry and read from Restful.cache.

Conditions

In addition, there are some prerequisites and some information we need to consider.

1. The SQL instance hosting Cachedb must have the "machine learning Service with Python" installed

2. To execute a python script in cachedb using TSQL, you should run SQL Service mssqllaunchpad or SQL Server Launchpad. See Microsoft.Net:Microsoft Machine Learning Services

3. To enable external script execution using sp_configure, see Microsoft.doc: Enable server configuration options for external scripts

sp_configure ' external scripts enabled ', 1;

RECONFIGURE;

4.TransDB and Cacher managed environments should create a Service Broker endpoint on their instance, and if the endpoints are hosted independently on two different SQL instances, each instance should have its own endpoint.

The 5.TransDB and Cacher databases should have proxies enabled. See microsoft.technet: How to: Activate Service Broker message delivery in a database

6.ALTER DATABASE transdb SET enable_broker;

GO

ALTER DATABASE cachedb SET enable_broker;

GO

. NET application

WebApplication has two major MVC actions; One uses the HTTP verb post to update a new entity in Transdb, and the other uses an HTTP verb get to return a list of product types from the cache.

Restful.cache has two ways of doing this, using the HTTP verb post to update the cache for the newly added entity product type, and the other to get all the cached product types from the local cache.

For our sample solution, both applications are hosted under individual application pool identities in IIS to protect application security but for actual system implementations, the managed environment can be a single Web server in an intranet or Internet environment.

The Restful.cache authorization rule only has two service accounts to handle HTTP requests, i.e.

ABC \ WEBAPP_SVC and ABC \ Cacheragent_svc. The ABC \ CACHERAGENT_SVC service account allows Python scripts in SQL to reach the application via HTTP to flush the cache.

The ABC \ WEBAPP_SVC user is used by WebApplication with authorization rule mode to allow access to the Restful.cache application.

SQL database and Service proxy

The OLTP database Transdb has several objects, including tables, stored procedures, and Service Broker objects.

For our purposes, process Updateproducttype updates the ProductType table with new records, and the Acknowledgeproducttypecache process is the activation process for cacheintegration queues. The acknowledgement from the target is received from the Cacher database when the message is being processed. It also handles exceptions, if any, and records them in the Cacheintegrationerror table.

For more information about Service Broker, please visit the Microsoft.DOC:SQL Server Service Broker

For our sample solution, Transdb is the source database that creates the update cache message when the new ProductType record is created, the message that performs the action, it has the Updatemessage message type, The cacheintegration contract sends the Cachesource service to the target database. The service has cachequeue, which is used by Service Broker components to perform reliable messaging. The Tocachetarget route has the information to deliver the message to its destination.

To eliminate any chance of increasing transaction processing time and to avoid any security risks to the rest of the data in the transactional database, we will de-cache the update process by using the proxy database named Cacher database in our sample solution. The Service Broker messaging infrastructure will help connect the TRANSDB and Cacher databases, and event-based message processing will enable us to update cache storage residing on a network-based system. The Cacher database is acting as a proxy role to perform a cache refresh when an update message arrives. It updates the cache by executing a python script.

1.CacheLog and Cacheintegrationerror tables to track when the cache is refreshed and has a record of any errors that may occur during the cache refresh process.

2. The performcacheupdate process receives incoming messages from TRANSDB through Service Broker. If the type of the message is updatemessage, then it executes another procedure Updatewebcache, executing the python script.

A.1. The execution results of the Updatewebcache procedure are saved in the table variable and then inserted into the Cachelog table at the end of the message conversation.

B. When the received message has an error or end message type, the procedure ends the dialog and writes the exception log to the Cacheintegrationerror table on the error type.

1. The Updatewebcache process extracts the ID and name from the incoming XML message passed as a parameter and embeds the values in the Python script text. The script execution result set is a structured table of type Upddatecachelog.

The Service Broker object for Cacher, mainly the Updatemessage message type and the cacheintegration contract, is the same as TRANSDB. Cachequeue has an activation process called perfomcacheupdate, a service named Cachetarget that has information about TRANSDB service Cacheservice and endpoint addresses.

For our sample solution, the maximum Queue reader for the database queue is set to 1. If necessary, you can increase this, for example, if the data is modified very high, and you need to increase the cache refresh rate.

Service Proxy Endpoint

For our solution, the database is hosted on the same instance, so both use the same service Broker endpoint to send and receive messages.

However, if we are to host the database on a single instance, then the service account for each SQL instance should have a services broker endpoint. And all two SQL instances should have permissions to allow messages to be sent to each other's endpoints. The authorization and grant of the connection can be done through the following TSQL command set. Note that in the messaging infrastructure, there is one sender and the other is the receiver, as mentioned, if the SQL instance is part of the sender and receiver, then each instance should have its own process identity. Shows how each SQL Server runs under its own identity.

This is the SQL code used to authorize and grant endpoint connections to TRANSDB SQL Instance service account [identity] in the SQL instance of the Cacher database.

ALTER AUTHORIZATION on Endpoint::servicebrokerendpoint to [Abc\transdb_svc]

GO

GRANT CONNECT on Endpoint::servicebrokerendpoint to [Abc\transdb_svc]

GO

Again, here is the code for authorizing and granting endpoints to connect to the cache SQL Instance service account [identity].

ALTER AUTHORIZATION on Endpoint::servicebrokerendpoint to [Abc\cacheragent_svc]

GO

GRANT CONNECT on Endpoint::servicebrokerendpoint to [Abc\cacheragent_svc]

GO

Python script

This is the Python script text, which is saved as a string in the TSQL variable @updatecache. It has a logical Updatecache method that performs an HTTP post call to Restful.cache by passing a data object that has a name and ID field that is received as an input parameter. It receives a JSON object and returns it to the caller as the output of the method.

At the end of the script, the returned object is converted to an array, so it can be structured as a SQL result.

DECLARE @UpdateCache NVARCHAR (MAX) = N '

Import pandas as PND #data structure package

def updatecache (Name,id):

Import requests as HTTP #http request Package

#Perfom HTTP POST to update cache

HttpRequest = Http.post (Http://localhost/RESTful.Cache/ProductType/UpdateCache, {name:name, id:id})

Cachelog = Httprequest.json ()

Return Cachelog

#Update cache and build log element

Log = [Updatecache (' + @Name + ', ' + CAST (@Id as VARCHAR (10)) + ')]

#Return data frame i.e. table structure from SQL

Outputdataset = PND. DataFrame (Data=log)

‘;

There are some notable things to be aware of when using Python scripts in SQL Server.

We can write a sequential script or group them into the methods we've done in this solution. Alternatively, we can create an inline class or create a package and import them in Python using the pip command at a command prompt.

2. In this CPT version of MS SQL, the import statement only imports the package within the scope it is placed in, so we can notice that the import Request Imports statement exists in Importcache. The import statement imports the panda line at the end of the script at the top of the import script.

3. The output object of the Updatecache method is converted to an array immediately, so pandas. Dataframe can convert an object into a data structure, and SQL Server can easily interpret it as a table with rows and columns.

The data structure assigned to the Outputdataset object is available in the TSQL execution context of SQL Server.

5. Last line of the program dbo. Updatewebcache,with RESULT sets (as TYPE dbo. Updatecachelog); Has a user-defined table type dbo. Updatecachelog, which helps maintain the order of the underlying columns and avoids any mismatches that occur during the generation of a result set from the received data structure. Another approach is to build a mapped column structure in Python and result sets.

Database security

Transdb is an OLTP database that we do not want to have any security vulnerabilities to the system, so through our sample solution, this database can be hosted on a SQL instance that does not have the machine learning service installed. Cacher is a proxy that can reach a network-based system, so it can remain on the SQL instance where the Machine learning service is installed. Two SQL instances can have a separate service account identity that is authorized to connect only to the Service Broker endpoint for a specific port. Another way to secure authenticated communications is to use a certificate. For Service Broker endpoint authorization, see microsoft.technet: How to: Allow Service agent network access by using certificates (Transact-SQL) for more details.

All components are put together

After placing all the components, our webapplication allows us to create a new producttype and list the same product type from the refreshed cache through restful HTTP calls. There are components behind the wall that manage data, and caching is not visible to front-end applications.

Conclusion

Applications such as e-commerce, medical e-governance, etc. can benefit from a good cache implementation. by extending the use of our familiar technologies, we can get easy-to-maintain solutions without the cost of learning new frameworks or features.

Our sample solutions are in line with what we need

L. When data is created or modified by one of the OLTP transactions, the system refreshes the network-based caching system for read access.

L. It can use asynchronous events to flush the cache, near real-time. This does not affect the performance of the original transaction.

L. can draw a security thread between the transaction and the cache system via HTTP to protect the data in the OLTP database.

• Minimal monitoring is enabled; A cache log and exception Log can further enhance the build management console.

L. uses Service Broker messaging components, and when asynchronous message processing occurs, the solution is flexible enough to trigger or reach a network-based system. In other words, the database is integrated with the SQL Service broker message and, based on the received data, performs an action to fetch or send the data to a system other than the external data tier.

L. Triggers events within a private database by using a service proxy messaging external system to help protect the transactions and data of the OLTP database.

Powerful features of Python and SQL Server 2017