Translation (iv)--the Power of Python and SQL Server 2017

The Power of Python and SQL Server 2017

Original link: https://www.red-gate.com/simple-talk/sql/sql-development/power-python-sql-server-2017/

Powerful features of Python and SQL Server 2017

Python is a new version of SQL Server 2017. Its main purpose is to allow Python-based machine learning in SQL Server, but it can use much more than that, as well as any Python libraries or frameworks. To provide a possible example, Hitendra shows how to safely use this feature to provide a smart application cache, where SQL Server can automatically display when a data change triggers a cache refresh.

SQL Server 2017 has added its advanced analytics extension, now known as the Machine Learning Service, which allows SQL Server to execute Python scripts in tsql through the Python machine learning service. This basically provides a way for a database programmer to pass data directly to Python. This is not limited to the effectiveness of providing machine learning data analysis functionality, because Python has many readily available modules and frameworks to solve many problems, such as performing a large number of computations and data structures, profiling, network operations, database operations, network operations, or local/network-based file system operations. Obviously, many of them are the best in the middleware, but in the database system, there are many times, direct communication with the external system directly is more convenient, rather than relying on external processes to poll the data source to perform the task. This makes sense when there is no need to have such a solution in the database or data tier, when it does not provide any security issues.

Now, we will try to demonstrate an example of using Python in an advanced analysis extension, which shows how a database triggers an external process to execute the activity of the data provided as a parameter. 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 when the task is initiated by an event in the database. Tasks may include:

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

2. Leverage local platform resources, such as file systems, networks, or GPUs.

3. Use a common data format, such as JSON, XML, or YAML, to build real-time integration between one or more systems.

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

Of course, there are few potential downsides.

1. If your use of Python requires Internet access, there is a risk that secure data must be guaranteed to be shared unexpectedly on the internet. Any Internet access must be strictly regulated by the Internet.

2. Allow the execution of Python scripts on the server through enable external scripting to expose security risks.

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

When you weigh these pros and cons, Python can still play a useful role if it minimizes risk. As an example, let's consider how to use Python to build the data caching system used by the application layer.

Sample Solution Cache

Caching data can be an effective way to improve application performance. On the storage overhead of the cache, we can gain useful performance gains in the face of database-like connection traffic and the high resource consumption of the database in the face of duplicate queries. When we build our cache infrastructure, the common problem we face is when to flush the cached content. After a certain interval, we tend to adopt a simple method of rebuilding the cache. However, this is very inefficient. It is a better practice to flush the cache when the data changes and refresh only the content that has changed. We can do this when the data is created, updated, or deleted. There are a number of tools and frameworks that can resolve refresh problems, but they encounter problems with how to determine what has changed in the data and when the change occurred. Databases are best suited to do these things.

For our caching system, it can be provided here, and we will limit ourselves to the Microsoft stack to prevent Python itself.

Microsoft SQL Server (CPT)

The service proxy used to isolate the transactional database.

Python executes a script that updates the cache on HTTP (Python 3.5 can be executed from the Anaconda Distribution library)

. Net 4.5.2

Using the ASP.NETMVC sample Web UI

The ASP. Webapi encapsulates the cache storage for our sample solution.

The following is a graphical representation of the sample solution cache system:

WebApplication provides a user interface to read and update data.

Restful. In our sample cache storage solution, use ASP to build a cache application. Net WebAPI2, whose content type is JSON. The Http-get operation provides data from a local cache (a static collection).

SQL Server (CPT) is a database server with a

Transdb OLTP database, busy processing transactions.

The Cacher proxy database executes the Python script execution, enabling the Enable external scripting option to enable script execution. Refers to Microsoft. DOC: External script supports server configuration options.

The Service Broker is a reliable SQL Server messaging framework that helps bridge cache proxies and TRANSDB. Receiving messages through the cache proxy can be processed to update the cache.

Python is the integrated scripting language for the SQL (CPT) database system.

The architecture of the solution

In our solution, we will cache the product type name of the entity in restful. The cache application and WebApplication will have a function to create a new product type entry, and from restful. The cache is read.

Prerequisite

By the way, we need to consider some prerequisites and more information.

1.CacheDB managed SQL instance must have Python's machine learning Service installed

2. To execute a python script with tsql in Cachedb, you should run SQL Service mssqllaunchpad or SQL Server Launchpad. Refers to Microsoft. Net: Microsoft Machine Learning Services

3. Enable external script execution for sp_configure, please refer to Microsoft. DOC: External script supports server configuration options.

The 4.TransDB and Cacher managed environments should have a service proxy endpoint created on their instance, and if they are managed independently on two different SQL instances, each instance should have its own endpoint.

The 5.TransDB and Cacher databases should have proxies enabled. Refers to Microsoft. Technet: How to: Activate a service proxy message delivery in a database.

6

.

. NET Applications

WebApplication has two main MVC operations, uses HTTP keyword post to update new entities in Transdb, and returns another operation that returns a list of product types from the cache with an HTTP keyword.

There are two ways to do this, one is to update the cache with the newly added entity product type with the HTTP keyword post, and the other is to get all the cached product types from the local cache Restful.cache .

For our sample solution, both applications reside in a separate application pool identity under IIS to ensure application security. However, for the actual system implementation, the managed environment can be a separate Web server in a local area network or an Internet environment .

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

The Abc\webapp_svc and abc\cacheragent_svc.abc\ cacheragent_svc service accounts allow python scripts in SQL to reach the application over HTTP to flush the cache.

ABC\WEBAPP_SVC users use a Web application that has an authorization rule pattern to allow access to RESTful. Cache the application.

SQL databases and service proxies

The OLTP database transdb some objects, including tables, stored procedures, and service proxy objects.

For our example, the process updateproducttype updating the activation process of the ProductType table with the new record and Acknowledgeproducttypecache process is The cacheintegration queue , which receives a message from the target acknowledgment processing , that is, from the cacher database. It can also handle any exception and log these exceptions in it

Cacheintegrationerror table.

More information about Service Broker can be found at Microsoft. doc:sql Server Service Broker.

For our example solution,Transdb is a source database that creates an update cache message when a new ProductType record is created, a message that executes an action because it has a updatemessage message type, a Cacheintegration the contract to send a message with the Cachesource service against the database. The service has a cachequeue, which is used by the service proxy component to perform reliable messaging. The tocachetarget route has the information to deliver the message to the destination.

To eliminate any opportunity to increase transaction processing time and to avoid the security risk of remaining data in the transactional database, we will use a proxy database called the Cacher database to isolate the cache update process 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. When the update message arrives, theCacher database plays the role of the agent to perform a cache refresh. It updates the cache by executing a python script.

To hide your own database are :

The 1.CacheLog and cacheintegrationerror tables, which track records when the cache is refreshed, and record any errors that may occur during the cache refresh.

The 2.PerformCacheUpdate process receives incoming messages from Transdb through the service proxy. If the type of the message is Updatemessage, then it executes another procedure , Updatewebcache, which executes the python script.

The execution results of the A.updatewebcache process are saved in a table variable and then inserted into the Cachelog table at the end of the message session .

B. The process also ends the conversation when the received message has an error or end message type, and the Exception log is written to the Cacheintegrationerror table on the error type .

The 3.UpdateWebCache program extracts the ID and name as parameters from the incoming XML message and embeds the values in the Python script text. The script execution result set is a structured table of type Upddatecachelog.

Cacher's service proxy object, mainly updatemessage message type and cacheintegration contract is the same as TRANSDB,Cachequeue has a name of The perfomcacheupdate activation process, which is named Cachetarget, and the routing information about the TRANSDB service Cacheservice and endpoint addresses.

For our sample solution, set the maximum Queue reader to 1 for two database queues. This can be increased if needed, for example, if data modifications are very high, you need to increase the cache refresh rate.

Service Proxy Endpoint

For our solution, the database runs on the same instance, so they all use the same service proxy endpoint to send and receive messages.

However, if we want to host the database on a single instance, then the service account for each SQL instance should have a service proxy endpoint. and two SQL instances should allow sending messages to each other's endpoints. You can use the following set of TSQL commands to complete the authorization and grant of a connection. Note that in the messaging infrastructure, one sender and the other is the receiver, as mentioned earlier, if the SQL instance is part of the sender and receiver, then each instance should have its own process identity. The following picture shows how each SQL Server runs according to its own identity.

This is the SQL code that authorizes and grants endpoints to connect to TRANSDB SQL Instance service account [identity] in the SQL instance of the Cacher database .

Similarly, here is the code that authorizes and grants the endpoint connection to the SQL Instance service account of Cacher in the SQL instance of the TRANSDB database [identity].

Python script

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

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

There are a few things to note when using Python scripts in SQL Server.

1. We can write a sequential script, or group them into a method, as we did in this solution. Alternatively, we can create an inline class or create a package and use the PIP command to import them in Python at the command prompt.

2.CPT MS SQL version, import statements can only be imported into the package scope place, so we can note that the Import Request import statement exists internal method Updatecache, import the statement import the giant panda exists in the script in the last line of the script.

3. Method Updatecache The output object is immediately converted to an array to represent the panda in this way. Dataframe can convert an object to a data structure, and SQL Server can easily interpret it as a table with rows and columns.

4. The data structure assigned to the Outputdataset object is provided by SQL Server in the TSQL execution context.

5. Last line of the program dbo. Updatewebcache, with RESULT sets (as TYPE dbo. Updatecachelog); There is 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 result sets from the received data structure. Another approach is to build a mapped column structure within Python and in the result set.

Database security

Transdb is an OLTP database that we do not want any security vulnerabilities to the system's attacks, so in our sample solution approach, such a 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 stay on the SQL instance where the Machine learning service is installed. Each of these two SQL instances can have a separate service account identity, which is authorized to connect to the service proxy endpoint only for a specific port. Another way to secure authentication communication is to use a certificate. For service proxy endpoint authorization, refer to Microsoft Microsoft.Technet:How to:allow Service Broker Network Access by Using certificates (Transact-SQL) for more details.

All components are put together

After putting all the components in place, here is our web application, which allows us to create a new producttype and use restful HTTP calls to list the same product types from the refresh cache. Behind the wall are the components that manage the data and the cache in front-end applications that are invisible.

Conclusion

Applications such as e-commerce and healthcare can benefit from a good cache implementation. By extending the use of our familiar technologies, we can get an easy-to-maintain solution without having to learn new frameworks or features.

Our sample solutions meet our needs

When data is created or modified through one of the OLTP transactions, the system refreshes the network-based caching system to read the data.

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

It can draw a security boundary between the transaction and the cache system via HTTP to keep the data in the OLTP database.

It enables a minimal monitoring feature , and cache logs and exception logs can be further enhanced to build the management console.

When using the service proxy messaging component, when asynchronous message processing occurs, the solution has the flexibility to trigger or reach a network-based system. In other words, the database is integrated with the SQL Service proxy message, which, based on the received data, performs an operation to fetch or send data to external external systems that are outside the data tier.

By using service proxy messaging, isolating external systems from triggering events within a dedicated database helps to ensure the transaction and data of the OLTP database.

Translation (iv)--the Power of Python and SQL Server 2017