Set SharePoint Content Database in Read-Only Mode

This a quick & easy one; I couldn’t find any MSFT material on how to do this as easily as I thought I might, hence this article.

A simple question: how do you set a SharePoint content database to read-only? You may’ve seen the setting “Read-Only” in Central Administration and wondered “how do I make it say ‘yes’?”. Well the answer is surprisingly simple – you set it as read-only in SQL Server - SharePoint then picks up on this change and then disables any operations that would allow users anything more than read access to that content databases – for all sites in it.

The following is supported in all versions of SharePoint from 2007 service-pack 2 (SP3 is the only currently supported patch-level at the time of writing) to SharePoint 2013.

Setting a Content DB to Read-Only

Here we have a typical content database, in read-write mode as we’re accustomed to.

Let’s change that in SQL Server Management Studio. Right-click on the database in question & go-to the options section:

Change this property to “True” and select OK. You’ll be prompted with this message:

This is warning you any open connection will be dropped. SharePoint should be fine as it’ll just reconnect transparently thanks to some ADO.Net SQL connection abstraction trickery but as SQL Server doesn’t know this so it’s warning you just in case. If you’re worried about this, change the read-only property in a maintenance window.

Confirm the change; SQL won’t restart, neither will SharePoint (although there’ll be a very small pause as SharePoint re-establishes new connections to SQL Server). That’s it, you’re done!

Just for the record, if you don’t have SSMS installed you can just run the same in a direct command of course:

 

ALTER DATABASE [WSS_Content] SET READ_ONLY WITH NO_WAIT

GO

Checking Read-Only Mode

You should now see this in Central Admin:

That’s pretty much it. SharePoint will now disable anything that’s related to modifying the database on any sites in that content database. More info on exactly what happens to a site in read-only mode can be found @ https://technet.microsoft.com/library/dd793608.aspx

 

Cheers!

// Sam Betts