Configure SQL Server database backend

Configure SQL Server database backend

Monday, August 25, 2008 Pablo Santos backend 3 Comments

Plastic 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 Plastic works with a Firebird embedded database but it can be changed to work with other databases like SQL Server.

Supported SQL Server versions
Plastic supports SQL Server 2005 or higher, basically due to new transaction isolation leves introduced with 2005.

Plastic can be configured to work with both SQL Server and SQL Server Express editions, but remember the latter has some size constraints.

For instance, you can use SQL Server Express edition in your laptop (remember Plastic supports distributed development and replication) to work with Plastic while you're disconnected if you prefer it better than Firebird.

Reasons to switch to SQL Server
There's not a rule of thumb here. My answer will always be: 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 this is perfectly possible with Plastic.

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 then you prefer to have your data there.

Plastic performance with SQL Server is very good too, something to keep in mind when your team grows or if you think you need Plastic to go faster.

Using a dedicated SQL Server machine for the database and another server for Plastic will increase performance under heavy load scenarios.

As you know upon installation Plastic comes with a embedded Firebird database set up, which is pretty good for a number of users but we recommend teams to switch to Firebird Server, SQL Server or MySql as the team grows.

Configuring Plastic server to work with SQL Server
Plastic database backend configuration is set up on a file named db.conf, located in the server directory (where the plasticd server is installed). It is a pretty simple xml file which tells the database backend to use and how to connect to it.

If you've installed the Plastic 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 splitted here for the sake of 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>

Which 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>

Which is the configuration of one of our internal servers.

Of course remember to replace by your own server and authentication data!!

Note that in the second sample I've introduced DatabasePath which tell SQL Server the location to place its data and log files for Plastic SCM repositories. If you don't specify it SQL Server will use its default location.

Starting up Plastic
Once the db.conf file has been correctly set up, you've to restart the Plastic server.

On start up it will connect to the new database and create the databases 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>

And restart Plastic.

The key is replacing the user and password data by trusted_connection.

There's an important tip to remember here: since you'll be normally running Plastic 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 service configuration (using the services applet at 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 a really easy problem, 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 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 it 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 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 do and overall system performance will be really bad, including disc access (which is key for Plastic client when it has to write on your workspace) and so on.

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 proc. If you use a regular SQL Server you'll be able to configure it from the admin application.

Running the stored proc 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

To limit it to 300Mb.

Please consider the following links for more information:

http://msdn.microsoft.com/en-us/library/ms180797.aspx

http://msdn.microsoft.com/en-us/library/aa196734.aspx

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1299068,00.html#

http://www.teratrax.com/articles/sp_configure_max_memory.html

Migrating from a different database backend
So far I've just told how to configure Plastic to work with a SQL Server backend assuming you are going to start up from a clean situation.

But what if you need to migrate your current Firebird repositories into SQL Server?

Then your SQL Server experience will definitely help: Plastic 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 like 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 :-( but just a useful and ugly command line utility) so if you are in a hurry just let us know.

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 check which backend fits better on your project.

Updated articles are maintained in the knowledge base of Codice Software at: http://www.plasticscm.com/infocenter/technical-articles.aspx

Pablo Santos

I'm the CTO and Founder at Códice.
I've been leading Plastic SCM since 2005. My passion is helping teams work better through version control.
I had the opportunity to see teams from many different industries at work while I helped them improving their version control practices.
I really enjoy teaching (I've been a University professor for 6+ years) and sharing my experience in talks and articles.
And I love simple code. You can reach me at @psluaces.