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:

clip_image001

If this is the 1st 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.

clip_image002

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”:

clip_image003

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.

clip_image004

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:

clip_image005

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

clip_image006

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.

clip_image008

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

clip_image009

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