{"id":640,"date":"2020-11-30T21:38:53","date_gmt":"2020-12-01T05:38:53","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=640"},"modified":"2020-11-30T21:38:53","modified_gmt":"2020-12-01T05:38:53","slug":"seasons-of-serverless-challenge-1-azure-functions-and-azure-sql-database-serverless","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/seasons-of-serverless-challenge-1-azure-functions-and-azure-sql-database-serverless\/","title":{"rendered":"Seasons of Serverless Challenge 1: Azure Functions and Azure SQL Database serverless"},"content":{"rendered":"

\"Cover<\/a><\/p>\n

 <\/p>\n

Have you heard about the Seasons of Serverless challenge? Join the festive virtual potluck<\/a> by creating solutions to challenges leveraging Azure serverless computing. Throughout the next seven weeks we’ll be sharing a solution to the week’s challenge that integrates Azure SQL Database serverless<\/a> with Azure serverless compute.<\/p>\n

Challenge 1: The Perfect Holiday Turkey \ud83e\udd83<\/h2>\n

The first week features the North America tradition of cooking a turkey.\u00a0 The challenge calls for converting a brine recipe based on the input weight of a turkey and recommends leveraging an Azure Function to generate the recipe.\u00a0 Below is a high level view of the solution we created, utilizing an Azure Function and Azure SQL Database serverless.\u00a0 The completed example can be found on GitHub<\/a>.<\/p>\n

\"Azure<\/a><\/p>\n

\n

Creating an Azure Function in VS Code<\/h3>\n

Using the VS Code Azure Functions extension and the “Azure Functions: Create New Project” command, we’re creating a new Azure Function locally in VS Code in Python with the HTTP trigger template.\u00a0 This function will receive HTTP GET requests with a turkey size in pounds (lbs) through a query string parameter.\u00a0 The body of the function is defined in the main(req: func.HttpRequest)<\/strong> function in the file __init__.py<\/strong>.\u00a0 Dependencies for our function that need to be installed to run the function are detailed in the file\u00a0requirements.txt<\/strong>, where we want to begin by adding a line for pyodbc<\/em>. pyodbc<\/a> is an open-source module for Python that enables connecting to ODBC databases, including Azure SQL and Microsoft SQL Server.<\/p>\n

\"function<\/a>Our Python function will utilize 3 internal functions,\u00a0getSqlConnection, getIngredients,\u00a0<\/strong>and\u00a0generateHttpResponse<\/strong>.\u00a0 The first,\u00a0getSqlConnection,<\/strong> implements the initial connection of the pyodbc driver to our database based on the connection string retrieved from the application settings. Since we are implementing our project on the serverless tier of Azure SQL database, we need our function to be able to gracefully handle instances when our database has autopaused.\u00a0 Login events are one of the events that trigger autoresume<\/a>, so our function needs to try the pyodbc.connect() call again if a database connection error is encountered.<\/p>\n

def getSqlConnection(sqlConnectionString):\r\n    i = 0\r\n    while i < 6:\r\n        logging.info('contacting DB')\r\n        try:\r\n            sqlConnection = pyodbc.connect(sqlConnectionString)\r\n        except pyodbc.DatabaseError:\r\n            time.sleep(10) # wait 10s before retry\r\n            i+=1\r\n        else:\r\n            return sqlConnection<\/pre>\n
Once the database has successfully connected, we will ask the database to send us back the ingredients. One of the many developer-friendly features of Azure SQL is how quickly it can create JSON in query results, so we have quick work of implementing handling our database results:<\/p>\n
def getIngredients(sqlConnection, turkeySize):\r\n    logging.info('getting ingredients')\r\n    results = []\r\n    sqlCursor = sqlConnection.cursor()\r\n    sqlCursor.execute('EXEC calculateRecipe '+str(turkeySize))\r\n    results = json.loads(sqlCursor.fetchone()[0])\r\n    sqlCursor.close()\r\n    return results<\/pre>\n
The full code for the Azure Function __init__.py<\/strong> is available here<\/a>.<\/p>\n
\n
Creating and Building the Azure SQL Database<\/h3>\n
\"Azure<\/a>Azure SQL database serverless<\/a> is a compute tier for SQL databases that automatically pauses and scales based on the current workload. Similar to Azure Functions, not only does Azure SQL database serverless have the capability to automatically scale with your workload, but you can build your solution locally once and deploy it to one or more instances.<\/p>\n
Starting from the Create SQL Database<\/a> pane in the Azure Portal, we provide a new database name and can leverage an existing or new server.\u00a0 The option to configure Compute + storage<\/strong> should be adjusted to select the Serverless<\/strong> compute tier.\u00a0 When using another Azure resource with an Azure SQL database, such as Azure Functions, it is important to permit network access between them.\u00a0 This option is commonly referred to as “Allow Azure services and resources to access this server” and is set at the server level<\/a>.<\/p>\n
 <\/p>\n
SQL Database Projects in Azure Data Studio<\/h4>\n
\"create<\/p>\n
After provisioning a serverless tier Azure SQL database through the Azure Portal, we start by creating the database structures in Azure Data Studio<\/a> with the SQL Database projects extension<\/a>.\u00a0 To begin, we create a new database project in Azure Data Studio.\u00a0 The database schema we’ve designed is relatively simple, we need a table to store the ingredients<\/strong> for the given recipe and a stored procedure to return the recipe<\/strong> scaled based on an input turkey size.\u00a0 We create these objects in the database project by right-clicking the project or desired folder in the project and selecting “Add Table” and “Add Stored Procedure”.<\/p>\n
 <\/p>\n
CREATE TABLE [dbo].[Ingredients]\r\n(\r\n  IngredientName NVARCHAR(200) NOT NULL PRIMARY KEY,\r\n  UnitofMeasure NVARCHAR(20) NOT NULL,\r\n  RatioToTurkeyLBs DECIMAL(10,4) NOT NULL\r\n);\r\nGO\r\n<\/pre>\n
As previously mentioned, we want our stored procedure to return JSON to the Azure Function. No problem! Using FOR JSON PATH<\/em> in our stored procedure query bundles the rows into a JSON array.<\/p>\n
\"query<\/a><\/p>\n
CREATE PROCEDURE [dbo].[calculateRecipe]\r\n\u00a0\u00a0@turkeySize DECIMAL(10,5)\r\nAS\r\nBEGIN\r\n\u00a0\u00a0SELECT IngredientName, UnitofMeasure,\r\n\u00a0\u00a0\u00a0\u00a0cast(round((RatioToTurkeyLBs*@turkeySize),2) as decimal(6,2)) AS IngredientAmount\r\n\u00a0\u00a0FROM Ingredients\r\n\u00a0\u00a0FOR JSON PATH\r\nEND<\/pre>\n
After adding both of these objects, we’re well on our way to creating our serverless database! We do have 1 more thing to add to our database project – the recipe.\u00a0 By adding a “Post-Deployment Script” to our project, we’re adding some T-SQL that will execute after the project has published to the database.\u00a0 We can add a script that inserts the values for the ingredients to the Ingredients table.<\/p>\n
DELETE FROM INGREDIENTS;\r\nGO\r\n\r\nINSERT INTO INGREDIENTS (INGREDIENTNAME, UNITOFMEASURE, RATIOTOTURKEYLBS)\r\nVALUES ('Salt', 'cups', 0.05), ('Water', 'gal', 0.66),\r\n    ('Brown sugar', 'cups', 0.13), ('Shallots', 'each', 0.2),\r\n    ('Garlic', 'cloves', 0.4), ('Whole peppercorns','tbsp', 0.13),\r\n    ('Dried juniper berries', 'tbsp', 0.13), ('Fresh rosemary','tbsp', 0.13),\r\n    ('Thyme', 'tbsp', 0.06), ('Brine time','hours', 2.4),\r\n    ('Roast time','minutes', 15);\r\nGO<\/pre>\n
\"publish<\/a>To deploy these objects to our Azure SQL database, we will build and publish the project to the serverless tier database we created in Azure.\u00a0 Right-clicking on the database project in Azure Data Studio\u00a0 After publishing the project from Azure Data Studio to the Azure SQL database, the database is all set!\u00a0 You might have noticed some new files in the folder under bin\\Debug are created during project build.\u00a0 The .dacpac<\/em> file is a data-tier application, a file that contains the database objects we created and can be published one or many times.\u00a0 In future challenges we will dig into how to leverage automation to deploy our database solutions as we will do for the Azure Function below.<\/p>\n
 <\/p>\n
\n
Deploying the Azure Function<\/h3>\n
The Azure Function that we created earlier in this post in the local VS Code environment can be deployed to Azure directly from our GitHub repository.\u00a0 This will enable continuous deployments to Azure from our future code changes.\u00a0 We will create an Azure Function App in the Portal to publish our code to on the Python runtime stack, and once it is created we need to download the publish profile from the overview page. Treat the publish profile like a password – it allows privileged access to manage the function and we’re going to use it to allow GitHub to publish our code as a function.\"Publish<\/a><\/p>\n
 <\/p>\n
Before we leave the Function App in the Azure Portal, we should add the Azure SQL serverless connection string to the Application settings<\/strong> such that the function can access it through an environment variable.\u00a0 In a Python Azure Function this is written o<\/em>s.environ[“serverlessdb”]<\/em> for the application setting named “serverlessdb”.\u00a0 The full connection string for the value is in the format:<\/span><\/p>\n
DRIVER={ODBC Driver 17 for SQL Server};SERVER=yourservernamehere.database.windows.net;PORT=1433;DATABASE=yourdatabasenamehere;UID=sqladmin;PWD=yourpasswordhere<\/pre>\n
\"Azure<\/a><\/p>\n
 <\/p>\n
This method stores the password for a database user in clear text and it is visible to anyone with access to this pane in the Azure Portal. You are encouraged to consider additional security in full scale implementations, such as Azure Key Vault or utilizing limited permission SQL users (seen in the sample<\/a>).<\/p>\n
Want to test your Azure Function locally? Place the same application setting for the database connection string in the local.settings.json <\/strong>file under “Values”.<\/p>\n
GitHub Action<\/h4>\n
We need to add the contents of the publish profile to our GitHub repository as a secret (under Settings>Secrets) for AZURE_FUNCTIONAPP_PUBLISH_PROFILE<\/em>.\u00a0 This value can now be used in GitHub actions to access the Azure Function App.\u00a0 The Azure Functions action<\/a> handles the deployment for us and even provides samples for several runtimes, including Python.\u00a0 To leverage their sample<\/a>, add the code to the .github\/workflows folder in your project and update the env<\/em> variables at the top of the script.\u00a0 In our instance, it’s in a file named main.yml<\/strong><\/a>.\u00a0 The action leverages a cloud environment to:<\/p>\n