New with SharePoint 2016 is the hidden ability to use Azure SQL PaaS as a content database. While the product is still in preview I have found that the sites spawned in this database respond fairly well and function correctly. My server architecture is SharePoint installed to Azure IaaS with a SQL database also installed to an IaaS virtual machine. The Azure SQL PaaS only hosts a content database and is hosted in the same Azure data center as my SharePoint farm.
First you must create a new database using the collation Latin1_General_CI_AS_KS_WS — Be aware, this is different from the default of SQL1_Latin. You cannot change the collation after spawning and SharePoint will not allow you to use anything but Latin1_General_CI_AS_KS_WS so make sure this is correct! For my test I only used 5 DTUs. While the initial creation of the content database was slow the upload, download and creating of sites was fast enough for a proof of concept.
Next step is optional but you should create a new SQL user for your SharePoint install. If this is just a proof of concept you can use your master account and skip these steps.
Run the following against your master database:
CREATE LOGIN sp WITH PASSWORD=N’spPassword!’;
CREATE USER sp FOR LOGIN sp;
EXEC sp_addrolemember ‘loginmanager’, ‘sp’;
Run the following against your newly created database:
CREATE USER sp FOR LOGIN sp WITH DEFAULT_SCHEMA = db_owner;
EXEC sp_addrolemember ‘db_owner’,’sp’;
Now switch over to your SharePoint 2016 farm and run the following command. Replace the highlighted text with your own values. When prompted for credentials use your database user or the user you created in sql earlier.
New-SPContentDatabase -Name AzureDatabaseName -WebApplication (Get-SPWebApplication http://webapp) -DatabaseServer FullAzureDatabaseURL -DatabaseCredentials (Get-Credential)
This step took 26 minutes for me using a 5 DTU basic server. After this is created spawn some sites in this content database by bringing your original content database offline in SharePoint content database settings and creating a new site collection. You are now using Azure PaaS SQL as your content database!
Please post your findings below!
