I’m attempting to install MS SQL Express to use with v4 beta and I’m having a few newbie problems. Does anyone have a step by step guide to setting up a database?
I have not used v4 yet, so I wasn’t aware that it supported MS SQL Server Express: v3 supported only SQLite (for local DBs) and PostgreSQL (for networked DBs). If that’s the case then I guess my manual for v4 (when I’ve finished the v3 one, I’ll maybe get started with v4) will include all the instructions: but don’t hold your breath for that. ;D
I should also say that it’s some time since I used a version of MS SQL Express! So my knowledge of that is somewhat rusty.
There is no guide yet.
One thing to keep in mind is that only SQL authentication is supported, so you have to create an SQL user which is the owner of the database, or has the required permissions to access it at least.
OK then, if this is similar to earlier MS-SQL versions, the MS install will create a SQL user named sa by default (with a blank password). This user has admin. rights to all databases and the server engine etc.
I also recommend that you download MS SQL 2008 Express R2 with Management Tools, not just the basic version of Express. The MS Release Notes etc. are pretty good usually, and should at least help you install the thing.
I have got MS SQL with management console, and I’ve enabled TCP/IP in Configuration Management and created a database, but there must be something stupid I’m doing wrong as mAirlist can’t see the database. I’ve tried accessing the database with another piece of software and that doesn’t work, so it must be something strange that I’m doing.
Again I’m somewhat guessing here, but if it’s similar to the old PostgreSQL method, you need to give mAirList the user ID and password to the DB you created in MS SQL; in mAirList Config, Databases. If you’ve stayed with the defaults, this will probably be sa and blank, respectively. You then need to click the Create Database button in Config to actually create the mAirList TABLES within your new DB.
Not sure whether you also need to ‘tell’ MS SQL to allow login from a specific IP or not; you used to have to do that in the PostgreSQL version.
As I say, it’s a bit of a guess as I have neither MS SQL nor mAirList v4 installed here (nor am I likely to do so in the next few weeks); but knowing the way Torben goes about things ;), the above will hopefully help. You might also find the v3 mAirList manual helpful: read the bit about setting up a Networked mAirListDB (I forget the exact page number) and you should be able to ‘translate’ the concepts used for PostgreSQL setup into MS SQL equivalents (if you see what I mean?).
I’m not an SQL Server expert, and I don’t have any access to my test installation right now, but I remember that it was pretty straightforward.
First of all, create a new user for SQL authentication, then create a new database, and specify that user as the owner of the database. Then use mAirListConfig to set up a new database, just like in the PostgreSQL variant.
The MSSQL seems to be a bit buggy in the current beta though. I heard of an error message when running the scheduler. Please keep that in mind when evaluating it.
I’ll try and take a peek in the next few days. I have MSSQL running on my beast at home, so should be able to see who/what/why/where/when etc.
Perhaps; but I wouldn’t recommend using a SQLS beta version; which is why evil_dave’s choice of 2008 R2 Express seemd a sound one to me.
(PS: We use SQLS 2005 at work!)
I was talking about the mAirList beta.
What’s wrong with SQL Server 2008 R2 Express (other than that it’s SQL Server)? “Express” does simply mean that it’s the free edition which supports not more than 10 GB storage space (and lacks a couple of pro features). I have been using it for testing the implementation. No problems whatsoever (apart from the bugs in mAirList).
if you setup the hostname for the mssql server you need to enter “hostname\instancename” as database server.
To anyone using MS SQL Server Express 2008 R2.
Service Pack 1 for this was released a couple of days ago. Download from the page below:
I got it working in the end, nothing to do with mAirlist and everything to do with what I typed into mAirlist’s configuration to point it at the db.
Thanks for all the tips guys.
Would you mind sharing what the exact mistake was? Someone might come across the same problem later and will find this thread even more useful then.
I think i know what his mistake was…
If you setup a MSSQL Database Server you have to give that server an instance name.
Most people forget that your dbhost is combined out of the hostname and the instance name as seen in attachment “Bild 1.png”
If you go into the SQL Server Configuration Manager you see the instance name in the brackets (“Bild 2.png”)
Therefore your database server is: servername\instancename <-- seperated with a backslash
and your database name is the database in that particular instance.
Good to know. Thanks.