SharePoint 2010: to RBS or not to RBS

By Henning Eiben in SharePoint 1 Comment

… that’s the question.

Joel Olsen recently wrote a blog post about the pros and cons of using Remote Blob Storage (RBS) (formerly know as External Blob Storage – EBS). This post also contains a bunch of links to various resources from Microsoft and other vendors of RBS technology.

The bottom line is, that using RBS can greatly improve performance when working with large documents in SharePoint, while the actual data-size is much lower than storing those documents in the SharePoint content-database.

On the other side RBS is would be the wrong choice, when mostly small documents or listdata is stored in SharePoint, because in this case the overhead of moving the data out of SQL-server and into the filestream is too costly. Back in 2006 Microsoft already published a paper called “To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem”, which concludes that files smaller than 256 KB would be best stored in the database, while files larger than 1 MB are most efficiently sored in the filesystem. Everything in between “depends”.

Trimming SQL databases

By Henning Eiben in Programming 0 Comment

Update 2012-06-15: I just realized, that although I shrink the logfile after changing the recover-model, I actually should truncate the logfile. This way all stored transactions are getting pruged from the logfile. I updated the last script accordingly.

The other day I already cleaned out some cache files to free up some space on my dev-machine. Since I’m on a roll I thought I just take a look at my SQL databases.

Recently I picked up a classic-thread around DBA’s. At a client the database stopped working, because the server ran out of disk-space. Obviously the databases are set to full recovery-model, but apparently the logfiles where never backed up. Consequently the logfiles consumed all available disk-space.

OK, we’re talking a dev-machine here, so I don’t really care about data-security. So I don’t care about full recovery-models for my databases.

So let’s take a look at the current databases.

SELECT [name] AS [DatabaseName],
CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel]
FROM master.dbo.sysdatabases
where CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery'))='full'

This will give us a list of all databases with full recovery-model. Changing the recovery model for a particular database is actually quite easy.

ALTER DATABASE test SET RECOVERY SIMPLE

But that’s not all, you actually need to shrink the logfile as well.

DBCC SHRINKFILE (N'test_log' , 1)

When you have more than just a couple of databases, you might want to speed thinks up a little.

SELECT 'ALTER DATABASE ' + [name] + ' SET RECOVERY SIMPLE;'
FROM master.dbo.sysdatabases
WHERE CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery'))='full'
SELECT 'USE [' + sysdatabases.[name] +']; DBCC SHRINKFILE (N''' + master_files.[name] + ''',0, TRUNCATEONLY);'
FROM sys.master_files
  INNER JOIN master.dbo.sysdatabases ON
    master_files.database_id = sysdatabases.dbid
WHERE CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ sysdatabases.[name] + '', 'Recovery'))='full'
AND [type]=1

This will give you all the sql statements needed to modify all your databases.

Running SQL Express and SQL Standard simultaneous

By Henning Eiben in Programming 0 Comment

On a simple dev-machine I would usually only install the SQL express edition. While this is sufficient for local development for simple databases, this doesn’t give me a GUI to manage my database.

Espically in bigger projects I might want to work with the database directly, or maybe I want to look or edit a database on an separate server. This doesn’t work at all with the express edition.

OK, so instead I install the regular edition of SQL Server. This gives me the Management Studio to easily manage my databases, but unfortunately this denies some features of the express edition, which only available in the express edition. One feature is attaching standalone mdf files as database. This is a feature needed especially for web-application, because a lot of ASP.Net data is stored in such an attached mdf file.

A common scenario for SQL 2005 was to have both express and the “regular” edition installed side-by-side. Beasue these editions come each with their own installer and seem to be considered separate products, this install does cause some problems. For example you will most likely not be able to install neither SP2 for the express edition nor the regular editions.

SQL 2008 seems to relief this pain a lot, because the installer offers to install the regular edition as well as express edition. This allows to install both editions and run them side by side.