Edit: March 09 - this post is a little out of date, some folks have had trouble as a result. I've tweaked the instructions so you are less likely to have problems from now on! Also, see opensimulator.org/wiki/MSSQL
Over the past week I've made some great progress on my OpenSim experiment. For starters, I've got my server configured in Grid mode, and I have all four five of the main servers running on SQL Server. This did require a bit of development - the inventory server was quite broken for SQL Server, so James and I fixed the code and submitted a patch, but the good news is that this means it'll be in a better state for the rest of you from now on.
If you're committed to switching from SQLite as much as possible then I would recommend configuring your server to run in grid mode. Even if you are working locally, there's something a bit more comforting about seeing messages relevant to each server on each of the console windows, rather than all jumbled up in one console window. Makes it much easier to see bugs and have a think about how to fix them, for starters.
So, where to start? Well, good news and bad news - the good news is that this is possible, the bad news is that it's not simple, and unless you figure out how to export content from a SQLite-based OpenSim, you'll be starting from scratch on your new sim. Before you start, you obviously need a SQL Server to work with. As long as you can install SQL Express on your OpenSim server, you have everything you need, and since SQL Express is a free download, there's no cost involved. Get SQL Express from here, then get SQL Management Studio Express from here - this will give you a tool for running queries and editing your database.
Now, I'm assuming you've done the right thing and already configured your system so you have a Subversion client installed, right? TortoiseSVN is my favourite, with its extremely simple shell integration. So, you have an OpenSim directory and you can grab the latest source code from the Subversion repository - this is pretty important, since the code changes very frequently. Now you will need to head to SQL Management Studio Express and create a database ready to store your OpenSim data. Right-click on the Databases node and and select New Database. Call it OpenSim, accept all defaults and click OK.
The next step is to create a user account for running your OpenSim on SQL Server. Firstly, you need to enable Mixed Mode authentication on SQL Server.
Right-click on your database server in Management Studio and select Properties, then go to the Security tab and select SQL Server and Windows Authentication mode, click OK. Back in Management Studio, expand the Security node and in the Logins section right-click and create a new user, called whatever you like, select SQL Server authentication, give it a strong password and select the OpenSim database as the default database. Click OK.
Then navigate to the OpenSim database and go to the Security, then Users section. In there, create a new user, select your new admin account and make sure it has the db_owner role in the Database role membership section. Click OK and you should be ready to use that account to connect to the database.
Edit: you don't need to create any tables by hand - that happens automatically provided your connection string is working.
Time to edit the Ini file. I recommend heading to the OpenSim documentation on configuration for more in-depth details on this process, but here's an abbreviated version that might help fill in some gaps for you. While there is a mssql_connection.ini file, I also have settings in my main ini file too - I haven't yet dug through the code thoroughly enough to know which ones are used and which ones are not.
Edit: you should not need the mssql_connection.ini any more. Having separate connection details for each server allows you to scale by using different machines for the different servers, hence makes a bit more sense than one central ini.
There's a sample mssql_connection.ini.example file in the OpenSim code repository, so make a copy, rename it mssql_connection.ini and replace the appropriate values with the correct data for your server. As an example:
[mssqlconnection]
data_source=servername\SQLEXPRESS
initial_catalog=OpenSim
persist_security_info=True
user_id=adminuser
password=password
In the main ini file, [EDIT 25th Oct: NOTE that the section below marked asset_database should be set to "grid" - this is very important or you end up using SQLite for asset storage!]
gridmode = True
storage_plugin = OpenSim.Data.MSSQL.dll
storage_connection_string = "Data Source=servername\sqlexpress;Database=OpenSim;User=adminuser;password=password;";
storage_prim_inventories = True
inventory_plugin = OpenSim.Data.MSSQL.dll
inventory_source = "Data Source=servername\sqlexpress;Database=OpenSim;User=adminuser;password=password;";
userDatabase_plugin = OpenSim.Data.MSSQL.dll
user_source = "Data Source=servername\sqlexpress;Database=OpenSim;User=adminuser;password=password;";
asset_database = "grid"
asset_plugin = OpenSim.Data.MSSQL.dll
asset_source ="Data Source=servername\sqlexpress;Database=OpenSim;User=adminuser;password=password;";
The other part of the puzzle is getting your sim running in grid mode, and for that to work you need to be a little more clever with IP addresses and the like, but don't let that put you off. Know your server's IP address, then enter it in the following section with the following port numbers:
grid_server_url = http://192.168.0.1:8001
grid_send_key = null
grid_recv_key = null
user_server_url = http://192.168.0.1:8002
user_send_key = null
user_recv_key = null
asset_server_url = http://192.168.0.1:8003
inventory_server_url = http://192.168.0.1:8004
messaging_server_url = http://192.168.0.1:8006
Notice there are some keys listed in here that are null - you should change these so that you only allow access to people who know both your server's IP AND the appropriate keys to grid up with you. And from looking through the code, it appears that the keys are any valid string value, though I haven't yet tested this out.
You may also want to double-check that the IP address is configured correctly for your region (head to the OpenSim\bin\Regions\default.xml file) and make sure that the IP addresses are set appropriately. Again, check the OpenSim site for a bit more information on this.
Once you have configuration sorted, time to run the servers and keep your fingers crossed! The OpenSim site strongly recommends starting servers in a specific order: UGAIM: UserServer, GridServer, AssetServer, InventoryServer, Messaging Server, and then Region Server (OpenSim.exe). Note that the first time that you fire up the servers in grid mode, you are asked for some information to create some system-specific configuration files. Make sure that when you start up each server you enter the data correctly - the data you will need to enter is exactly the same as that entered in the ini file, so make sure you fill in the fields so that they match. Once you have configured one server, move on to the next. Once you have all five running, you're almost ready to log in!
Here's a handy tip - now you have 6 console windows open, click on one of them on the task bar, then ctrl+click on the other four windows. Right-click on any of them and select Tile Horizontally and you will see all your servers neatly arranged, and you can watch things happening as you log in and do stuff on your sim.
Just before you connect, you need to create a user account, so go to the user console and create a new user account. Then it's time for the client bit - if you're using the SL client, set your loginuri startup switch as before, but this time you'll connect to port 8002, not port 9000, so for example:
"C:\Program Files\SecondLife\SecondLife.exe" -loginuri http://192.168.0.1:8002
Once you launch your client, you should be able to connect to your grid and off you go. Fingers crossed, and I hope it works for you.