Connecting to SQL mdf file without requiring SQL Server installed

[NickThissen]

Hi,

I have been developing an application for a while (in C#.NET) using an Access database. The reason for an Access database is simple: there's just a single database for the entire application and the database is stored in a simple file (mbb file). The end user doesn't need any additional programs installed for my application to work.

Recently however I have expanded my application quite a bit, and after doing some calculations on the amount of data I will be gathering, I got to quite a large number... This is probably an overestimate, but in the worst case scenario my database will grow by about 2GB per month. As far as I know Access can only handle 2GB (a bit less due to system tables) so that is out of the question. So, I am now trying to switch to SQL Server instead.

However, I don't want my end users to have SQL Server management studio installed. SQL Express is fine (I can let that install automatically with my application's installer, right?) but obviously the end user should not need anything more than that.

Is it possible to use an SQL Server database similar to how I have been using my Access database? In other words: the database is a simple file (I think an mdf file for SQL Server?) that I simply deploy with my application (I can place it in the application's AppData folder or something) and point to that file via the connection string.

I have been trying this for a while, but I don't have a lot of experience with SQL Server. The only experience I have is with existing databases that other people have installed on my work laptop for me.

I have managed to create an MDF file (along with a log file) via SQL Server Management Studio 2008 (I do have this, but my end users shouldn't need it) but I am unable to connect to it. I've tried various connection strings, a few from www.connectionstrings.com, as well as adding the MDF file to my solution in which case Visual Studio wants to generate either the Entity Framework classes or a DataSet or something, and in this case it also gives you a connection string. But none of them work, I keep getting different errors, some mention being simply unable to connect, some mention not finding the database, etc...

Example connection string I've tried:

Data Source=.\SQLEXPRESS;AttachDbFilename=%PROJECT%\Database1.mdf;Integrated Security=True;User Instance=True

(In my code, the '%PROJECT%' is replaced by the project directory; the resulting path is correct)

My question basically is: how do I create a SQL Server database MDF file which I can deploy with my application (SQL Express can be a prerequisite if required) to a known location and connect to that database file?

Thanks!

[si_the_geek]

I'm afraid I can't help with the code based details, but some comments on the general points:

This is probably an overestimate, but in the worst case scenario my database will grow by about 2GB per month.

That is huge potential growth... if that isn't just a wild guess, it hints at bad database design (lack of normalisation).

As far as I know Access can only handle 2GB (a bit less due to system tables) so that is out of the question. So, I am now trying to switch to SQL Server instead.

Access based (and other file based) databases tend to have significant issues long before you reach the theoretical 2 GB limit... many people have problems beyond 1 GB.

SQL Server Express (assuming you use the latest version) can handle up to 10 GB (safely right up to that limit), which isn't that much higher based on your growth prediction - so you could reach the limit of that too relatively soon.

However, I don't want my end users to have SQL Server management studio installed.

They don't need it.

SQL Express is fine (I can let that install automatically with my application's installer, right?)

Correct.

[NickThissen]

I don't think it's bad database design. There is simply a lot of data being gathered. The application reads the log file from a first person shooter game, which lists all kinds of things such as players taking damage, getting killed, what people are saying, etc. I am storing all of these events. For example, when a player takes damage, I store this in a 'Damage' table that lists the ID of the shooting player, the ID of the receiving player, the ID of the weapon used, the amount of damage dealt, the location of the hit (head, arm, body, etc) and finally the map and gametype on which it happened. The end goal is to get detailed statistics about each player, which they can they view ingame. One could then see the top 5 of their best weapons (most damage dealt, most kills made, etc), the number of kills they made with a certain weapon, etc.

With roughly 10 kills per player per game, 4 hits per kill and 15 players per server, that is already about 50 records per player per game, or 750 records per game. A game takes 5-10 minutes, you do the math...
Then I'm not even counting that I also store the details of each player that ever joined the server (for the one test server I am running at the moment, there were about 11.000 different players in 1.5 month!)

If 10GB is the max for SQL Server then yeah, that might be a problem... Still, 2GB per month is probably an overestimate, but say 1GB is still a lot, that would mean only 9-10 months of running time is possible... That's not TOO bad (in 9-10 months not many people will still be playing this game, and people can of course clear their database if they want to).



EDIT
I quickly recalculated what I did before, roughly, and it's something like this:

Assuming a game server has players for about 18 hours per day (at night it will mostly be empty, but there might be players in other timezones).
If a game takes 10 minutes on average, that is 18*60 / 10 = 108 games per day.

With an average of 15 players, each getting 10 kills, that is 150 kills per game. Each kill means 5 records in the database (4 because it takes 4 hits on average for a kill, and 1 for the actual kill), so we have 150*5 = 750 records per game.

With 108 games per day, that boils down to 108*750 = 81.000 records per day, or 2.430.000 records per month.

I am not even counting the player details themselves which I also store. Of course some players will already exist in the database so not every player is a new record, but assuming 50% are new players we have 15*108 * 50% = an additional 810 records per day or 259.200 records per month.

Let's assume this is already an overestimate, and that we have 2.000.000 new records per month. If my estimate of 2GB per month is correct that would be about 1KB per record. I'm not sure how accurate that is but it sounds reasonable, no?

[si_the_geek]

There are ways to accurately calculate database size based on the number of records in a table (except for variable length text based fields), but I think we can go for the simpler option of an educated guess... Assuming each field in the Damage table is a 32-bit Integer data type (can store values up to +/-2.1 billion), each record will be 7 fields * 4 bytes = 28 bytes. For 2.43 million records per month, that is roughly 68 MB per month.

Based on that (and assuming each record for the player details aren't too big) you should be able to keep adding data for a long time, and when you reach a limit you can simply move some of the older data to an "archive" copy of the database, which can actually be used by your program at the same time as the "current" one.


The damage table could me made to store a little bit less data (by moving the shooting or receiving player, plus the map and gametype, to a separate table). However this is unlikely to be significant, and would take extra effort in your code etc... so it is probably not worth it, unless you particularly want to make the "current" database store as much data as possible.


Anyhoo... hopefully somebody will come along soon with some help on your code issues.

[NickThissen]

The Damage table contains (from memory, I might be forgetting some details):
- 7 integers (ID's of shooting player, receiving player, weapon, map, gametype, the damage dealt and the unique record ID)
- 2 strings (type of damage and location of damage).
Not sure how large strings are (is the size a fixed reserved size or does it depend on the contents?) but they will probably make it a bit larger size per record.

The kills table is basically the same with some additions.

The player details contain the GUID (string), some datetimes (first and last seen dates) and a separate table PlayerNames contains the names of each player (players can use multiple names).


I don't see how moving the shooting/receiving player, weapon, map and gametype to a separate table helps. I am simply storing their ID's, not the full details of course. The details (such as weapon name, map name, etc) are in different tables which don't change (unless new maps are introduced or something).

So 1KB per record is probably way too large?

If that is the case, that's interesting, if your 68MB per month is closer to the real value then I might not even need to switch to SQL Server, then Access would probably do just fine.
The problem is that is kind of hard to estimate how many records will be in the database. The estimates I gave above (10 kills per player, 15 players per server for 18 hours a day, etc) are all just guesses based on my experience with the game. The only real data I have (and that is for one single server, so not much of an example) is that there were 11.000 different players on the server in a timespan of 1.5 months, but the data for individual players isn't that important (only how many times they shoot eachother) so that's not really useful.

[si_the_geek]

The size of strings depends on the data type you use.

If it is a variable length data type (such as VarChar in SQL Server, or Text in Access) then the size varies based on the actual data length (but uses some extra space so that it can tell how many characters there are). It varies by database system etc, but a reasonable estimate to use is 1 byte per character, plus 4 bytes to store the length.

If it is a fixed length data type (such as Char in SQL Server), it will always take the specified size (so Char(10) means it will take 10 bytes).

However, presumably there is a limited list of possible values for each of those two fields, so why not use a separate table for the values (along with a reference number), and just store the smaller reference number (4 bytes) in the damage table?


Regarding the moving of some fields to a separate table, as I said above it is debatable whether it is worth the extra effort - and it is based on size rather than convenience or speed.

It would allow a size reduction of 3 fields per record in the Damage table. However it would also give an increase of 5 fields (in the other table) per unique combination of values, and how the two balance out depends on the amount of repetition in the data - if there is a reasonable amount (each combination used twice or more on average), the separate table would give a size reduction. If you can also use it for the Kills table, the chances of repetition are presumably higher.


An .mdb database may be acceptable, but I would recommend switching now if you think the database size might ever get above 1 GB, or that you may have multiple users connecting to the same database at some point. For file based databases, either of those things lead to significantly increased chances of speed and corruption issues.