# Switching from mySQL to MSSQL



## shuggans (Nov 29, 2011)

Hello all,
I a have a dilemma on my hands.  MY application uses mySQL, and everything woks great.  I planned to list mysql server as a requirement for my application, so that I didn't have to worry about licensing issues with distribution (my app is commercial, not GPL).  My app uses libmysql.dll to connect to the mysql database instance, which, come to find out, must be licensed to distribute as well says Oracle. 

So:
I planned to switch everything over to MSSQL express, which is totally free to distribute and use in commercial applications.  I am used to mySQL server, not MSSQL.  Upon installing MSSQL server, I discovered it uses local authentication (Windows username and pass)... Is there a way to set up MSSQL server to function like mySQL server?  ex: requiring my connection string to use username, pass, DB name, and table name?  The DB is accessed by both my app and an IIS site runnign on the same server.

Does anyone know of any good tutorials or sample walkthroughs of building a vb.net app that connects to and uses MSSQL server?  

Thanks so much guys


----------



## W1zzard (Nov 29, 2011)

you could use ODBC, or a proxy app that's a separate process that forwards mysql requests and open source that proxy (i'm no lawyer so no guarantees that this is legal)


----------



## shuggans (Nov 29, 2011)

Lol,
I have thought about stuff like that.  BUT, for the purposes of streamlining the install process, if I switch to MSSQL, I can include the installer for MSSQL, and set up the database how it needs to be as it installs, instead of a preconfigured db to be set up before installign my app


----------



## W1zzard (Nov 29, 2011)

will sqlite work for you ?


----------



## Disparia (Nov 29, 2011)

Change Server Authentication mode to "SQL Server and Windows Authentication mode". It'll then ask you to set up an account like root, called "sa". From there you can set up other accounts.

Don't know what your app does, but if there's a chance it'll find itself on machines with MSSQL already installed, a slick installer would have an option to use the existing instance or create a new one


----------



## Easy Rhino (Nov 29, 2011)

sqlite is a preferred choice if this is not an enterprise program. most users dont want to have an entire db setup on their system using resources when the program itself is not being used. plus with sqlite you dont have any licensing crap.


----------



## shuggans (Nov 29, 2011)

I have never used sqlite, and have only heard the name one other time until now.  Would it be easier to integrate into an installer, etc. than MSSQL express?  I need it for my application, and also to be accessed from a web page at the same time(shared w/ an IIS site on the same server).  This application is meant for one dedicated server.  The database is just a simple, single table db.  mySQL works great, and I love it.  Just the licensing kills.  they want over 3k a yr for me to distribute any of their gpl software commercially, even just the connectors.


----------



## Easy Rhino (Nov 29, 2011)

if it is a single table db then don't waste resources using mysql. sqlite was pretty much made for the exact reasons you are describing.


----------



## Disparia (Nov 29, 2011)

Single table! Could have mentioned that earlier 


SQLite About Page:



> SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()


----------



## shuggans (Nov 30, 2011)

So if I switch to SQLite, can I access that DB from both my app and a php or asp.net site on the same server?


----------



## Disparia (Nov 30, 2011)

I don't do enough in ASP.net to give specifics, but I would imagine it that it's entirely possible.


In PHP there is the db specific SQLite3 class:

http://us2.php.net/manual/en/book.sqlite3.php


Or you could use the PDO class which would provide an abstraction layer. Probably not that useful in this case, but I'll put it out there anyway. Maybe after a few versions and your app's scope has grown, you'll want to support SQLite, MSSQL, and MySQL 

http://us2.php.net/manual/en/book.pdo.php


How it handles multiple connections without a server process: http://www.sqlite.org/faq.html#q5


----------



## shuggans (Nov 30, 2011)

I just created a test app and am able to read and write to a table in an SQLITE DB.  My situation is:
Users upload things via a web page on the server my app runs on.  When uplaoded, the web page runs a php script which creates an entry into the DB for the uploaded item.
My app then grabs those things and manipulates them, keeping track of it's changes and modifying that items DB entry as it goes.  

Will SQLite crash if someone uploads a new item via the web page as my program is writng to the DB?  or does it only support one concurrent write connection?

The website writes and has a search page users can search for items with which reads the DB.

he app also both reads and writes to the DB


----------



## Disparia (Dec 1, 2011)

Wrote up a quick script to try and simulate users uploading a file every 5 - 10 seconds. Table is just two columns, a string and an int.


```
$start = microtime(true);

$dbc = new SQLite3('sqlite.db');
$dbc->busyTimeout(5000);

$cnt = 'SELECT COUNT(*) FROM test';

for ( $x = 0; $x < 100; ++$x ) {
	$sql = 'INSERT INTO test VALUES ("' .  chr( mt_rand(97, 122) ) . '", ' . mt_rand(100, 999) . ')';
	$dbc->exec($sql);
	sleep( mt_rand(5, 10) );
}

echo 'Time: ', ( microtime(true) - $start ), 's', PHP_EOL, 'Row Count:', $dbc->querySingle($cnt);
```

Ran between 1 and 4 instances at a time. Under these conditions, had no problem and row count was as expected.

I tightened the timeout to 1000ms and 1-3 second insert per instance and still no problem.

Only when I took out sleep() and the inserts were continuous did I have a problem. A couple times a file lock couldn't be obtained and the query failed (warning thrown). Unlikely to happen under normal conditions, but to mitigate a worse case scenario, a timeout should be set, and perhaps a recursive function to handle the query. After _X_ fails, the problem can be handled in some way. All of it will depend on estimate users, their estimate usage, how many calls to the db you make per script, the phase of the moon, etc.

Another option I didn't think of, in case you anticipate a large number of users, is that there is an embedded version of MSSQL. Didn't think of it as I haven't used it before  Couldn't tell you about the setup, resources, etc, of it but it might be an option as well.


----------

