Microsoft SQL Server Machine Learning Service

What is SQL Server Machine Learning Services with Python and R?

Learn more

Using the AdventureWorks sample database in a practical example

The AdventureWorks (fictitious) company uses a database that stores data about sales and marketing, products, customers and manufacturing. It also contains a view that joins information about the products, such as the product name, category, price, and a brief description.

Currently, AdventureWorks sends the product name and brief description to a marketing firm to develop a campaign to boost sales. The cost for this marketing specialist is significant, and after looking into the capabilities of Generative Pre-trained Transformers (GPT), the company is curious to know if this AI could be used to create starter-text for brochures, automating the first step of the process. GPT-4 is a powerful language model that can generate coherent and creative text from complex input, or prompts.

Instead of hiring a professional writer, they can use SQL Server Machine Learning Services with Microsoft Azure OpenAI Service to automatically generate high-quality starter ad copy based on the product description and other relevant data. This can save time and money on the marketing budget. This approach also has the added benefit of security, since the database stays local, Stored Procedures have high security granularity, and Azure OpenAI Service does not use the information sent for further training.

This process works on SQL Server Machine Learning Services platforms on Windows, Linux, Containers, and Kubernetes clusters, including virtual machines and SQL Server Managed Instance, from version 2019.

During their research, AdventureWorks discovered that it is important to be cautious when generating text using a GPT model. It is possible that the generated text may not always be appropriate or accurate, so it is important to have a system in place for reviewing and approving any changes before they are published.

The proof-of-concept (PoC) for AdventureWorks involves the following requirements:

1. Create a Stored Procedure to accept the model of a given product.
2. Generate marketing brochure text for that product, highlighting its features and benefits, using Azure OpenAI Service’s GPT-4 model.
3. Ensure that the generated text is safe and factual, and that it can be edited before publishing.

Example code

The POC project for AdventureWorks involves the following steps:

1. Install SQL Server Machine Learning Services on a Microsoft Windows, Linux, or Container environment.
2. Create and deploy a model on Azure OpenAI Service, and obtain the model’s name, keys and endpoints.
3. Install the openai Python package using Azure Data Studio or the sqlmlutils package. This enables the code to use OpenAI API in Python scripts.
4. Write a Python script that uses the sp_execute_external_script stored procedure to query the product’s view, send the product name and description as a combined prompt to the Azure OpenAI API. The script should also specify the parameters for the model, such as the engine name (such as `davinci`), the temperature (a measure of randomness), the max tokens (the maximum length of the generated text), and the stop sequence (a token that indicates the end of the text). The script should also handle any errors or warnings from the API and return the generated text as a result set.
5. Execute the Python script from SQL Server Management Studio or Azure Data Studio and review the generated text for each product. Edit the text as needed or re-run the script with different parameters to get different results.
6. Once the result is satisfactory, wrap the code in a stored procedure to take the product’s model number from the user, and ensure the security chain is verified for the Stored Procedure.
7. In the end-user application, display the result for the ad text to the user for editing, and send the edited result to the database for storage.

With that outline, sample code in an Azure Data Studio Jupyter Notebook has been developed for AdventureWorks, allowing descriptive blocks, code, and persistent results display. After testing and editing, the code can be copied to a Transact-SQL script and deployed to testing and then on to production. The end result produces the following starter text to display to the end user for editing and confirmation before being set to the database:

[sic]

Dear Mountain Bike Enthusiast, Are you looking for a bike that can deliver the performance you need without breaking the bank? Look no further than the Mountain-400-W Silver, 38.  This bike is designed to give you the high-level performance you need to tackle any terrain, without the high price tag. With its responsive and maneuverable design, you’ll be able to navigate even the toughest trails with ease.  But it’s not just about performance – the Mountain-400-W Silver, 38 also offers peace-of-mind when you decide to go off-road. With its sturdy construction and reliable components, you can trust this bike to handle anything you throw at it.  So why wait? Order your Mountain-400-W Silver, 38 today and experience the ultimate in off-road performance at a price you can afford. Don’t settle for anything less than the best – choose the Mountain-400-W Silver, 38 and take your mountain biking to the next level.

Other applications of this approach

As you can see, using Microsoft SQL Server Machine Learning Services with Microsoft Azure OpenAI Service has practical applications. In this example we used it to create a marketing brochure text for various products. This is a powerful and creative way to leverage AI for business needs. The resulting text could be sent to an image-generating service (such as DALL-E) to generate images for the brochure. Other data sets in the AdventureWorks database could be used in other applications, such as investigating sales regions and much more.

However, it is important to be cautious and responsible when using AI models, as they may not always produce accurate or appropriate results. You should always review and edit the generated text before publishing it and follow the safety and ethical guidelines of Microsoft Azure OpenAI Service.

Learn more

• What is SQL Server Machine Learning Services with Python and R?
• What’s new in SQL Server Machine Learning Services?
• The Microsoft Azure OpenAI Service.
• Understanding GPTs.

The post Secure your AI using SQL Server Machine Learning with Microsoft Azure OpenAI Services appeared first on Microsoft SQL Server Blog.

For those of you who haven’t met me or read any of my books or blog entries, it’s great to meet you! I’ve been a data professional for over 35 years, worked at a variety of places like NASA, various consulting firms, and here at Microsoft since 2006. I started on the SQL Server team, and then helped ship Microsoft Azure. After that I spent some time in Microsoft Consulting Services, then over to the Machine Learning team in Microsoft Research, and then the Machine Learning and AI team. I’ve rejoined the SQL Server team to help with the inclusion of Apache Spark™, Kubernetes, and the Machine Learning and AI features. I’ll still be blogging at my regular location, and from time to time I’ll chime in here on the SQL Server blog. As I learn something new, I’ll share it. This time I’ve learned about a great new tool the team has put together. Azure Data Studio  is a new tool that you can use to work with SQL Server.

You may be thinking wait – don’t we already have a lot of those? Isn’t that the SQL Server Management Studio (SSMS), or “Data Dude” (SQL Server Tools for Visual Studio) or even Visual Studio Code with the add-in for SQL Server?

Well, yes. And those still work just fine – but Azure Data Studio goes further than those tools. And it does some things differently.

Multi-Source

In Azure Data Studio, you can connect to multiple data systems, not just SQL Server, like Apache Hadoop HDFS,  Apache Spark™, and others. And if you don’t find what you need, you can make more.

Multi-Platform

SQL Server Management Studio is an amazing tool. When I started at Microsoft in 2006, that’s the product I worked on building. I know it VERY well. But it only runs on Windows. Is that a problem? Well, yes, and no. If you live in Windows all day exclusively, then that’s fine. But if you run a Mac or Linux, then you need a tool that runs on those platforms, and Azure Data Studio does just that. In fact, it does it quite well. I’m typing this on a Mac right now (in Azure Data Studio, no less) and it even maps the keyboard to a Mac-like paradigm. You can also run an add-on to map the keyboard to SSMS.

On each platform, when you start Azure Data Studio, it checks itself and all your extensions and gives you the option to update them, regardless of your platform.

Extensibility

Perhaps the biggest argument for a new tool for working with data is that Azure Data Studio has a fully functional extension feature. This means that Azure Data Studio works more as a platform where you can add in new functionality, themes, etc. that Microsoft, vendors, and even you can create extensions to do what you need. Do you need a tool that manages SQL Server Agent? We have that. Need search tools, check scripts, reports, or Jupyter Notebooks? Check. Need something else? Write it. We’ll show you how. If there isn’t something for a platform you regularly work with, contact that vendor and send them the link to create extensions, and have them publish it to the github.

I’m a huge fan of Visual Studio Code. I use it for everything, and Visual Studio Code has LOTS of extensions. And here’s a cool tip: You can use almost all of them in Azure Data Studio. At the end of this post, I’ll show you how to install an extension along with a few of my favorites I always use in my day-to-day coding.

You can also create widgets to show information about your system.  Learn how to do that in this guide.

Environment and Workspaces

If you’re sold on trying out Azure Data Studio, get it installed, and get started setting up.

Workspaces

You can connect to servers and open a directory easily from inside the tool.  The top left two icons will handle that for you. Azure Data Studio also has the concept of Workspaces, which is similar to how you might think about a solution or project in SSMS or Visual Studio. The key is that Workspaces aren’t as rigid as a solution or project. You can include a lot of files located in many places in a single file, such as MyFavoriteFilesAndFolders.code-workspace, that you can simply double-click to open it all at once in Azure Data Studio. Use the File | Save Workspace As menu item to create a Workspace. This feature alone sells me on using this for day-to-day work. Pair that with github integration, and it’s where I spend most of my time.

Task List

As your system works, you’ll see information about what it’s doing and has done in the Task History icon on the left.

Integrated Terminal(s) and output panels

In every operating system, you have a shell (or terminal) of some type that you often need to work with. In Azure Data Studio, you can run those right in the Azure Data Studio environment – and you can run several of them. You can have PowerShell and CMD running, or even the Azure Command-line interface (CLI) shell in the terminal where you can run a quick command.

Setting up your environment

I use Azure Data Studio as my primary work environment for everything from SQL Server code, to Python, R, PowerShell, Apache Spark™, and more. It’s become my go-to integrated development environment (IDE). Here’s how I set mine up.

The first thing I care about is how my environment is laid out, and how it looks. When I work, I like a dark environment with brightly colored fonts. So, my primary theme is dark-plus-syntax. For presenting, which I now do in Azure Data Studio, even replacing PowerPoint in some cases, I use Quiet Light, a built-in theme. You can change those after you install the ones you like by opening the Color Theme picker with File > Preferences > Color Theme. You can open this via Code > Preferences > Color Theme on macOS.

Next, I add in the extensions I want. To do this, click the Extensions icon on the left, and select the Install button on the extension you want from the list. This will either re-install or open a web page. If the former happens, wait a moment while the extension loads, and then click the Reload button to activate it.

If the extension installation opens a web page, then download the .visx file you see on that page. Save that to a location on your hard drive, and then open Azure Data Studio, click the File menu item, then Install Extension from VISX Package. Point to the file you downloaded and when it finishes, click the Reload button next to the extension.

We already have several extensions in the list for you, but here’s a cool trick – you can actually load most extensions on the Visual Studio Code Marketplace. All you have to do is the following:

1. Open the VS Code Marketplace
2. Search for the extension you want
3. Click the icon for that extension to go to its source page
4. On the right-hand side of that page, look for the section marked Resources
5. Click on the Download Extension link
6. Save the file on your hard drive
7. Open Azure Data Studio
8. Click the File menu item, then Install Extension from VISX Package
9. Point to the file you downloaded
10. When the install finishes, click the Reload button next to the extension

Here is a list of the ones I use a lot:

• SQL Server 2019 extension (preview)
• Copy Markdown to HTML
• Docker Explorer
• Docs-markdown
• Excel Viewer
• First Responder Kit
• GitLens
• HCQ
• Kubernetes Support
• Live HTML Previewer
• Markdown Preview Enhanced
• Open in Browser
• Python
• R
• Redgate SQL Search
• Server Reports
• SQL Server Agent
• SQL Server Import
• SQL Server Profiler
• SQLite
• TODO Highlight
• Vscode-reveal
• Whoisactive

I’ll be blogging more about Azure Data Studio as I continue the journey. If you’re ready to get started, you can download Azure Data Studio here.

We welcome feedback and suggestions, just click Help and then Report Issue to file a bug or a feature request.  This puts it right in our tracking system. Be verbose, include screenshots, and most of all add a gif of what you’re doing! It helps us fix things faster.

The post Azure Data Studio – Setting up your environment appeared first on Microsoft SQL Server Blog.