How to configure Plastic SCM with MS SQL Server

Plastic SCM can be configured to use different database backends to store data and metadata. This article will explain how to configure a SQL Server backend.

By default and on Windows, Plastic SCM works with a Firebird embedded database, but it can be changed to work with other databases like SQL Server.

Supported SQL Server versions

Plastic SCM supports SQL Server 2005 or higher, basically due to new transaction isolation levels introduced with 2005. It can be configured to work with both SQL Server and SQL Server Express editions, but remember the latter has some size constraints. For instance, if you prefer SQL Server Express edition over Firebird, you can use it in your laptop (remember, we support distributed development and replication) to work with Plastic SCM while you're disconnected.

Reasons to switch to SQL Server

There's not a rule of thumb here. My answer will always be that it depends on you! I mean, if your company or your sysadmin is more used to SQL Server, then go to SQL Server. Some companies prefer to use a single corporate SQL Server and integrate all their data there, and that is perfectly possible with Plastic SCM. You can use standard SQL Server tools to query the databases, run back-ups, and so on. Maybe you and your team are more used to set up and back up SQL Server than Firebird or MySql, and you prefer to have your data there. Plastic SCM's performance with SQL Server is excellent, something to keep in mind when your team grows, or if you need Plastic SCM to go faster. Using a dedicated SQL Server machine for the database and another server for Plastic SCM will increase performance under heavy load scenarios. As you know, upon installation, our software comes with an embedded Firebird database set up, which is pretty good for an average number of users, but we recommend teams to switch to Firebird Server, SQL Server, or MySql as the team grows.

Configuring the Plastic SCM server to work with SQL Server

Plastic SCM's database backend configuration is set up on a file named db.conf, located in the server directory (where the plasticd server is installed). It's a pretty simple xml file which tells the database backend to use and how to connect to it. If you've installed the Plastic SCM server on Windows, the file won't exist and you'll have to create a new one. Linux users will always have a db.conf file.

Note: The line <ConnectionString>....</ConnectionString> must be on one complete line, it has been split here for the sake of visual clarity.

So, if you're on Windows, create a new file db.conf and write the following content:

<DbConfig>

  <ProviderName>sqlserver</ProviderName>

  <ConnectionString>SERVER=beardtongue\SQLEXPRESS;

    User Id=sa;Pwd=master;DATABASE={0};</ConnectionString>

</DbConfig>

This is the configuration I use for my laptop. As you can see, I'm using a SQL Server Express server (beardtongue\SQLEXPRESS instance in the connection). If you want to connect to a regular SQL Server you can use the following configuration:

<DbConfig>

  <ProviderName>sqlserver</ProviderName>

  <ConnectionString>SERVER=MORDOR;User Id=sa;Pwd=masterpwd;DATABASE={0};</ConnectionString>

  <DatabasePath>d:\repositories</DatabasePath>

</DbConfig>

This is the configuration of one of our internal servers. Of course, remember to replace our examples with your own server and authentication data!! Note that in the second sample, I've introduced DatabasePath, which tells SQL Server the location to place the data and log files for Plastic SCM repositories. If you don't specify, SQL Server will use its default location.

Starting up Plastic SCM

Once the db.conf file has been correctly set up, you have to restart the Plastic server. On start-up it will connect to the new database and create the database's repositories, workspaces, and rep_1 (the default repository), if they're not there.

Using built-in Windows authentication

So far, I've introduced how to connect using built-in SQL Server authentication which is basically telling Plastic to use a given user and password. If you want to use built-in Windows authentication you'll have to modify your db.conf in the following way:

<DbConfig>

  <ProviderName>sqlserver</ProviderName>

  <ConnectionString>SERVER=beardtongue\SQLEXPRESS;

    trusted_connection=yes;DATABASE={0};</ConnectionString>

</DbConfig>

Note again that the lines <ConnectionString>...</ConnectionString> must both appear on one line in your actual code.

Then, restart Plastic SCM. The key is replacing the user and password data by trusted_connection. There's an important tip to remember here -- since you'll normally be running the Plastic SCM server under the system account on Windows, you'll have to make sure that account has rights to access your SQL Server database under trusted connection. You can always change Plastic SCM's service configuration (using the services applet in your Administrative Tools on the Control Panel) to make it run under different credentials.

Troubleshooting the new connection

Setting up a SQL Server backend should be really easy, but you know problems can always show up! If this is the case, remember to check the loader.log.txt file at the server's install directory which contains the server's log. The most common connection problems to check are:

  • You incorrectly typed the server.
  • The user and password are not correct (integrated security).
  • The account running the Plastic SCM server doesn't have rights to connect to SQL Server (trusted connection).

Configure SQL Server memory usage

SQL Server is a memory hog! It will try to eat as much memory as possible -- up to 2GB. On a dedicated server this shouldn't be a problem, but if your server has to run not only SQL Server, but also some other services (including the Plastic server!!) then you can end up in trouble! Just to give you an example, I run SQL Server Express in my 1.5GB RAM laptop to host Plastic SCM repositories. The system performs great if I limit SQL Server to 300 or 400Mb ... but if you let it grow as much as it can ... it will grow and overall system performance will be awful, including disc access (which is key for the Plastic SCM client when it has to write on your workspace). So, how can you limit SQL Server memory? If you're using SQL Server Express you'll have to do it with the sp_configure stored procedure. If you use a regular SQL Server, you'll be able to configure it from the admin application. Running the stored procedure is simple:

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE



USE master

EXEC sp_configure 'max server memory (MB)', 300

RECONFIGURE WITH OVERRIDE

 

This limits it to 300Mb.

Check out the following links for more information:

Migrating from a different database backend

So far, I've told you how to configure Plastic SCM to work with a SQL Server backend, assuming you are going to start up from a clean slate. But what if you need to migrate your current Firebird repositories into SQL Server? Then your SQL Server experience will definitely help. Plastic SCM uses standard databases and database structures, so you'll just have to import the data using a standard migration tool (a quick search on google will help, see also http://www.dbnetcopy.com/dbnetcopy/default.aspx) or using the built-in SQL Server migration tool. We also have our internal migration tool available -- although it is not fancy and beautiful :-( ... just a useful little command line utility.

Finish transaction

Well, we're done! As you can see, there's no magic involved, and setting up the SQL Server database is pretty simple. Try it yourself and see which backend fits your project best.