Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Azure SQL Database has a new “serverless” mode in preview that eliminates compute costs when not in use. In this post, I’ll show how you can set up a serverless database instance, and access data stored in it from R.

I’m working on a demo that I’ll be giving at several upcoming conferences, and for which I’ll be needing data in a database. Normally, I’d use a database installed on my local machine or in a virtual machine in the cloud, but this time I decided to go a different route: serverless.

I need the database to be around for a few months, but I’ll only be accessing it occasionally while I develop the demo and, later, present it live a few times. I need the database to be fairly large and fast (both of which rule out installing it on my laptop), and I’d prefer not to have to pay so much for the cloud resources while I’m not using it. (Yes, as a Microsoft employee I do have access to Azure services, but our department is cross-charged for our consumption and our spend is scrutinized like any other expense.) I considered using specialized cloud-data services, but for this talk I needed something that looked and felt like a traditional database.

I could always run a database in a virtual machine, but this time I decided to try Azure SQL Database. Azure SQL Database runs just like an ordinary SQL Server instance on a named server, but without the need to create or manage any associated VM. Even better, I discovered that it now has a “serverless” pricing tier, which gets me several benefits:

• It will automatically scale its available compute resources up to the maximum number of vCores (virtual cores) I specify. The more cores in use, the more I am charged for compute. A minimum compute capacity (again, as specified) will always be provided while the database is running.
• If the database is inactive for a period I specify, it will automatically be paused. While paused, I only pay for storage, but not for any compute. When I next need to access data, the database automatically restarts on demand.

The chart below illustrates this process for an Azure SQL Database with a minimum of 1 vCores and a maximum of 4 vCores; the green lines show when and at what rate compute cycles are billed, depending on the actual compute demand (orange line) on the database.

## Creating the database

Setting up the Azure SQL Database instance is pretty simple, and covered in detail here. From the Azure Portal, create a “SQL Database” instance (in the Databases section) and watch out for these steps during the creation process:

• You will be asked to choose a name for your new server: choose carefully here, as that will form the public URL you will use to access the server later.
• To set up serverless operation, click the “Configure database” option under “Compute + storage” and choose the Serverless compute tier.

• With the serverless compute tier selected, you will then configure the capacity and performance of the database. These choices will determine the running cost.
• The maximum and minimum vCores determines the processing speed and memory allocated to the database. The default setting for minimum vCores is 0.5, and that’s the best choice if you want to minimize operating costs. You can change these settings after the database is created, too.