Page 36 - MSDN Magazine, July 2017

Using Stored Procedures
for Machine Learning and AI
By encapsulating the machine learning and AI models as part of the SQL Server stored procedure, it lets SQL Server serve AI with the data. There are other advantages for using stored procedures for operationalizing machine learning and AI (ML/AI). These include:
Applications can leverage existing database drivers to con- nect to SQL Server: Many programming languages have database drivers that enable them to connect to SQL Server. These database drivers (OLEDB, ODBC, JDBC, MSPHPSQL and Node.js Driver for SQL Server) are used by application developers to develop cutting-edge applications that “talk” to SQL Server.
In addition, companies might have existing LOB applications that are already operational. By leveraging ML/AI-stored procedures in SQL Server, these LOB applications can easily evolve into intelli- gent applications. With the R or Python code needed to work with the ML/AI models encapsulated in stored procedures, application developers can now leverage their ML/AI stored procedures as is (without requiring new libraries or learning new database access patterns). To the application layer, the intelligent ML/AI stored procedure behaves just like any SQL Server stored procedure.
Being backed by the full spectrum of SQL Server enterprise- ready capabilities: Some considerations include ...
• Where can I host the model for doing inference?
• Which users can access the ML/AI model?
• When the model is used for prediction (aka “inference”), it
might need to access specific data in the database. In what security context should the model and associated R/Python code execute?
• How can I ensure the R/Python code doesn’t use up all the SQL Server resources?
can develop the stored procedure and the R and Python code in one place: Visual Studio. With the availability of SQL Server Data Tools for Visual Studio, R Tools for Visual Studio, and Python Tools for Visual Studio, you can do development of the T-SQL, R, or Python code, check it into a source control system, write unit tests, automate testing, and perform code review, and more. Database developers and data scientists can now work together to develop the ML/AI stored procedures, each focusing on their respective areas of expertise.
Steps to Get Started with
SQL Server, Data Science and AI
There has never been a more exciting time and better opportunity for us as database professionals and developers to work with data sci- ence and AI with SQL Server. You can get started in three easy steps:
Install SQL Server 2016 or SQL Server 2017 CTP2.
When installing SQL Server 2017 CTP2, you select the type of in-database machine learning services that you want to install. You can choose to use R, Python or both. Once SQL Server completes setup, you’ll be able to start using R or Python code as part of stored procedures, as shown in Figure 2.
Note: If you’re using SQL Server 2016, only R-Services (In- Database) will be shown at setup.
You can refer to bit.ly/2qXoyyC for more information on setting up R and Python with SQL Server.
Enable external script. To use R or Python code in the stored procedure, you’ll need to configure SQL Server to allow external scripts. To enable external scripts, you’ll need to run the sp_config- ure and reconfigure commands (after the T-SQL code is successfully executed, you’ll need to restart the SQL Server service):
exec sp_configure 'external scripts enabled', 1 reconfigure with override
SQL Server provides enterprise- ready capabilities from using row-level security to limit the data that can be accessed, to providing database admins with both server and database scoped database audits, to enabling ownership- chaining for SQL Server secur- able, to being able to sign stored procedures with a certificate or asym- metric key, resource governance and more. These enterprise-ready SQL Server capabilities can be used by the ML/AI stored pro- cedures as is, without requiring the data scientist to reinvent the wheel for serving data at scale. Most important, the DBAs today can leverage their existing skills to secure and manage the ML/AI stored procedures.
Mature development tools to develop the ML/AI stored proce- dure: As a database developer, you
32 msdn magazine
Figure 2 Using SQL Server 2017 CTP2 Setup to Install Machine Learning Services (In-Database) Machine Learning