With the 2010/1d release, Windows users will now have a choice between using SQLite or SQL Server for the PureCM server. We are currently beta testing the MySQL database for Linux and Mac users so this should become available in the next six months. This blog will help you decide whether to stick with SQLite or switch over to SQL Server. I will also look at the differences between running the server on Windows, Linux or Mac using SQLite.

If you are using the native database (pre 2010/1 release) then we recommend you upgrade to SQLite or SQL Server as soon as possible. Both of these databases have better performance than the native database and are more scalable – so it is a win-win. Details on how to upgrade can be found in the PureCM Knowledge Base.

SQL Server or SQLite

SQL Server has better management tools and scales better for large teams. So if you have over 30 developers using PureCM we definitely recommend you migrate to SQL Server. If you have over 10 developers then it will depend on the developer usage. If all of the developers are doing frequent submits, merges or workspace creations then SQL Server may give you a performance boost.

If you have less than 30 developers then you need to establish whether using SQL Server will be more expensive. If you already have a SQL Server license then great, otherwise you will need to work out how much this will cost. PureCM can be configured to work with SQL Server Express – but this will not work out of the box. We plan to support SQL Server Express properly in a future release.

If SQL Server is not expensive then you need to decide whether the scalability and management benefits of SQL Server outweigh the performance benefits of using SQLite.

I installed the PureCM Server on a test Windows machine running on the LAN. I got some performance statistics using SQL Server and SQLite when submitted and creating a workspace for about 10,000 files totalling about 1.2 GB of data.

As you can see SQLite is about twice as quick when submitting data but only about 20% quicker when creating a workspace.

Windows, Linux or Mac Server

If you have decided that SQLite is the best fit for you – then you might also want to consider which OS should host the PureCM Server. Here are some performance statistics when using the PureCM GUI on Windows 7 to submit and create a workspaces for servers running the different Operating Systems.

So the server OS made no difference for creating a workspace (database reads) but Ubuntu was significantly quicker (over 50%) when submitting the file adds (database writes) when compared to Windows.

Windows, Linux or Mac Clients

Finally you might be interested to know the performance differences between clients running PureCM. Here are the statistics when running the PureCM SQLite Server on a Windows Server 2005 machine over the LAN. This is using pcm (the PureCM command line client) with the same set of files (10,000 files totalling 1.2 GB of data).

As you can see, Mac and Linux are pretty much neck and neck – but Windows is significantly slower. But when you put it in context, taking just over 3 minutes to create a workspace with 10,000 files totalling about 1.2 GB of data is impressive. So regardless of which OS you choose I am confident PureCM’s performance will beat any other version control tool.

PureCM currently uses its own database which has developed over the years to become a robust, secure, and flexible solution for most customer needs. There has been demand, however, from customers (existing and potential) to provide the capability to work with more established and well known SQL databases. We plan to have the option for customers to select from many of the most popular variants, but initially we will develop a SQLite solution followed shortly by SQL Server.

SQLite will become the default database type, but the option for PureCM's native database will still be there. So for existing customers there is no need to change to using SQL if they don't want to. A Migration facility will be available to convert existing databases though.

Advantages of using SQL databases include ..

Familiarity - Administrators already have the knowledge for administering sql databases.
Backup - Trusted and established methods for backup of data.
Interface tools - Easier access to data without use of PureCM.

We're implementing SQLite initialy due to its global cross-platform availability, ease of use (Zero-Configuration) and cost (Its free). This makes a good starting point from which expand.

We see SQL Server as a high priority for the large number of Microsoft Visual Studio development teams. As well as being able to integrate the PureCM client with Visual Studio, using Microsoft's SQL Server at the backend makes a lot of sense.

Additional database engines available to UNIX/Linux are also on the radar and will follow after SQL Server. More on this later.

In terms of usability, there should be no change as far as the PureCM user is concerned. The tool will look and feel exactly the same, with no performance disadvantages.

We aim to support SQLite Databases in the 2010-1 release of PureCM.