Here we are at the second week's task for the Manhattan Project: switching the user's database to MSSQL instead of SQLite. I'll be honest, there weren't any valuable resources I found for assistance in the creation of this tutorial. Mostly it was a combination of Try / Fail and guessing based on what I saw in the code for the MSSQL dll in the project.
I might say that there weren't resources, but what I mostly mean is no tutorial. There are some .sql files to help get the database started, but that's the extent currently.
Now, things you need to know before I begin:
- If you want to get a better idea of what's going on, what OpenSim is and what it can do for you, check it out at http://www.opensimulator.org.
- The OpenSim tutorials represent a face-value documentation of what worked for me. I would assume that if you follow these instructions they will lead you to success, but I have to apologize ahead of time that I will not be the most valuable resource if for some reason this doesn't work for you. The source changes frequently, and I hate to think how soon I’ll be outdated.
- Be proactive. I'll start each section with a bolded header. If you think you can manage what you need to do just by reading the header, then go for it. Frankly, if you think you can do this without my tutorial, go for it too. I'm adding this tutorial for the sake of helping those who may straggle behind with the project.
With that out of the way, these are what you should have / will need prior to this tutorial:
- An operational OpenSim server, and access to the source.
- An operational SQL Server. This tutorial is designed for someone who is using SQL Server 2005, but should have acceptable parallels to previous or future versions.
And finally before we get started, I am dreadfully making some assumptions, listed now:
- OpenSim server is on the same machine as the MSSQL server.
- SQL Server is operating in mixed mode authentication. OpenSim's MSSQL dll doesn't natively support windows authentication, but it is something that could be added. There may be security reasons that this isn't supported, but currently I am clueless to what they'd be.
Let's Get Started!
1. Create a new SQL Server Database, called OpenSim.
Frankly, the database doesn't have to be new, and it doesn't have to be called OpenSim, but for the sake of segregating the tables for OpenSim, and for keeping things logical, this is what I did.
Open SQL Server Management Studio Express and connect with the authentication you've set up. On the left sidebar you'll see a list of all the different stuff on your SQL Server. Right click where it says Databases and select New Database...
In the resultant New Database window, put OpenSim as the database name and specify an owner, then click Okay.
2. With your new database selected, load and run the various .sql files.
Special thanks to Chris Hart for unearthing these .sql files!
Go ahead. Try and find the .sql files that you need to run to initialize the databases. I'll wait.
Now before you hang yourself from the nearest ceiling fan, here's where they are:
After returning the rope to the garage and the chair to the kitchen, open all of the .sql files in SQL Server Management Studio Express. Towards the top of the program, you'll see a toolbar option with a red exclamation that says Execute. Just to the left of that, make sure that the drop-down list has OpenSim (or whatever you might've named your database) selected. Then for each of the .sql files, hit Execute.
Note: I'm certain not all of these are necessary for migrating just the users database, but I do'em all anyways. Also, one of these files consistently fails, but don't worry, if it's the same one that fails for me it isn't necessary for the user's migration. Hopefully there will be a fix within the community if the plan ever becomes to migrate all databases.
3. Add a new login to your database with db_owner permissions to the newly created database.
Again, thanks to Chris Hart I have learn something new for another day... here's what she has to say for creating a new user (this is specific to SQL Server 2005)
To create a user account for running your OpenSim on SQL Server, you need to firstly 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 try using that account to log in. Makes it a bit more safe than using an account that has control over the entire database server.
Thanks again Chris :)
4. Change the userDatabase_plugin value in the OpenSim.ini to "OpenSim.Data.MSSQL.dll"
Browse to the bin folder of your built OpenSim and locate the OpenSim.ini file that you created in the previous tutorial. Open it up, and search for userDatabase_plugin.
You'll see that one of these isn't commented, and some other options are commented out. Oh so conveniently, the MSSQL option doesn't exist. hoorah. Make a copy of the active line, paste it, then comment one out. Then, on the uncommented line, replace the assigned value with "OpenSim.Data.MSSQL.dll".
You'll end up with something like this:
;userDatabase_plugin = "OpenSim.Data.SQLite.dll"
userDatabase_plugin = "OpenSim.Data.MSSQL.dll"
Save the OpenSim.ini and close it.
5. Copy mssql_connection.ini.example to mssql_connection.ini and update it with your database's information.
Remember when I said this is a face value documentation of what worked for me? Well, it's about to shine through.
After copying the mssql_connection.ini.example to mssql_connection.ini, fill in the information like it were a connection string in ini format. Follow the guidelines provided in the example.
For datasource, if your OpenSim will run on the same computer as the SQL Server, set this to localhost\.
If you are unsure of your SQL Server's instance name, open up SQL Server Management Studio Express and look at the Server Name in the Connect to Server dialog box. This is in the format:
(Computer Name)\(Server Name)
For initial_catalog, set it to the name you gave your new database (for example, OpenSim)
Finally, for user_id and password, use what you created in the earlier steps.
Save out the mssql_connection.ini file and then try running your server.
6. Run your OpenSim.exe, and hope it doesn't crash.
I know the process worked for me, but lord if I know how the whole thing will go down for you. Basically, there are a couple key ways to recognize that your connection was successful:
OpenSim.exe kept running until you got to the Region#: prompt. This means success. The opposite of success is when OpenSim crashes and you see a lot of red text. Sadly, I don't know how helpful I will be if this happens to you.
The master user gets migrated to your SQL Server's Users table. Open up SSMSE (SQL Server Management Studio Express) and browse to Databases > OpenSim > Tables. Right click the users table and select Open Table. You should see some new entries that were generated after OpenSim.exe was run.
Finally, if you recognized these couple successes, whip out your Second Life client (with the modified path to connect to your region) and log in. At this point, I really wish for your success!