Hosting SharePoint Content Databases in SQL Azure

Another great feature of Azure which we can harness for SharePoint farms for is SQL Azure. SQL Azure is basically SQL Server as a service; you never manage the SQL Servers running the service for your databases – it’s all managed for you with you just having a SQL endpoint to consume. Frankly it’s preferable if only because there’s less to manage so less to go wrong, and there’s some nice features like geo-replication across several continents if you want; it’s literally a single mouse-click/PowerShell command to enable.

Important edit: this is currently not officially supported for SharePoint Server, even for Azure-hosted farms. This is likely due to the 1-millisecond SharePoint -> SQL supportability requirement which Azure SQL Database doesn't & won't guarantee. At the time of writing there's no plans to do so either, so please note that despite the fact this configuration by all accounts does work, this article is purely academic.

The point is we can use this epic SQL Server platform for our most precious databases for SharePoint – the content databases, given they tend to be the databases that would benefit from SQL Azure, in just two easy setups.

Technically, using SQL Azure could work for either on-premises SharePoint on Azure-hosted SharePoint – the SharePoint servers just need to be able to see the SQL Azure instance over the network. I would probably recommend use Azure-hosted just as the latency between SPServers and the SQL Azure endpoints will be much lower but the principal is the same.

Anyway; here’s how to mount content databases in SQL Azure for a SharePoint farm. It’s unbelievably easy.

Step 1: Create SQL Azure Database/Server for SharePoint

Once your farm is all up & running (minus SPWebApplication) we first need to setup an empty database in SQL Azure. Create a new SQL Azure database from the portal:

If this is the 1^st time you’ve added a SQL Azure database, you’ll need a new SQL database server. Make sure the collation for the database is “Latin1_General_CI_AS_KS_WS” or SharePoint will reject it when you try & use it.

For your SQL Server, there’s not much to see at first. Just make sure the region is the same as your SharePoint servers to avoid latency.

Once added we can just add the new database to SharePoint like any other content database. If your SharePoint servers aren’t in Azure though you’ll need to manually add firewall rules for the IP address you’ll be connecting with. For Azure-hosted servers that’s not needed by default.

 

Step 2: Connect SharePoint to SQL Azure Database

Now it’s all ready to go you’ll want to add it to a SharePoint web application. Either way you need to grab the connection-string of your new shiny database so SharePoint can use it – in the portal, open the database and click “connection strings”:

This’ll show you various types of connection-strings; we’re interested in the ADO.Net one but in reality we only need the server-name & username.

Either create a new application pointing at this new database or add a new content database to an existing application. I’d recommend the latter just as it’s simpler – add the SQL Azure database info:

…and that’s pretty much it. Wait a while and SharePoint will add all its needed tables & other stuff just like any other database.

The SQL Azure database is ready to be used! If you look in PowerShell you’ll notice that SharePoint knows it’s a SQL Azure database:

Get-SPDatabase | ? { $_.IsSqlAzure -eq $true }

This property doesn’t do much more than highlight the DB is in SQL Azure for the most part; there’s one or two SQL statements that are generated differently and some other stuff but not much worth mentioning.

 

Step 3: Enjoy Epic SQL Azure Features & Uptime

SQL Azure means you don’t need to worry about various things you normally would; patching, uptime, disk-space, etc.

Content databases tend have the highest demands on scaling and SQL Azure definitely makes scaling to these demands very easy indeed.

Performance tasks like increasing maximum database sizes is literally 2-3 mouse-clicks instead of months of planning and provisioning, and scaling up for as much extra load as you can imagine is just a case of selecting the right performance level for your database & saving the change.

Also for availability adding readable replicas in entirely separate continents if you want is trivial:

It’s every DBA’s nightmare really because they’re just relegated to doing not much just because with SQL Azure setups there’s just not much you need to do. Sorry, DBAs – don’t take this personally :)

Cheers,

// Sam Betts

Comments

• Anonymous
  April 11, 2015
  Impossible how easy this is. Please note that you are running in a (currently) unsupported state when hosting your databases like this. Supported is a LAN latency of >1ms and I highly doubt you will have such a latency with Azure SQL: technet.microsoft.com/.../cc298801.aspx Maybe we'll see some changes in that direction with SharePoint 2016, i.e. supported database cloud scenarios.

• Anonymous
  April 11, 2015
  Good point Denins, I would recommend this approach ONLY if the app/frontend servers are going to be hosted in Azure VM's. If not, this is being an IT Cowboy. The network requirements are clear.  > Latency should be no greater than 1 milliseconds because this topology has not been tested. Kind regards,

• Anonymous
  April 12, 2015
  The 1ms is slightly strange limitation; we'd only enforce that if performance problems were being seen and pretty much everything else had been discounted. Actually for this setup, the response times to SQL Azure is around the 1ms mark on average, even on a standard SQL Azure edition.

• Anonymous
  April 13, 2015
  Great article Samuel. Is there any gotcha if crawling this Azure database is required?

• Anonymous
  April 13, 2015
  Thanks! No gotchas as far as I know - should be all 100% working the same.

• Anonymous
  April 15, 2015
  This is great... but just to reduce Hard disks size.. Actually you still need an HA SQL Server to host at least the configDB... So until we are not able to point Azure with the wizard, SQL Server VM (connected to a domain and configured with Windows Authentication) is required. so only benefit is Disk size.

• Anonymous
  August 05, 2015
  Hi there, is there an "official" reference that SQL Azure is not supported for SharePoint Content Databases? Thanks! Max

• Anonymous
  April 20, 2016
  Hi Sam,Can I migrate existing on-prem SharePoint content databases to Azure SQL? If so, how?Thank you

• Anonymous
  June 08, 2016
  Hi can anyone confirm if this is now an officially supported model, or is still officially not supported? Any links would be appreciated.Thanks.

  • Anonymous
    November 01, 2016
    Great post Sam! Any update on official supportability using Azure SQL and SharePoint 2016, any recent testing done on this? Appreciate any feedback!

• Anonymous
  January 16, 2017
  Like others I'm wondering, nearly 2 years on, what the status is... is this supported yet? If not... err... any idea why not? Thanks!Steve