-
Feb 14th, 2014, 04:53 AM
#1
Thread Starter
Addicted Member
[RESOLVED] I want an alternative to SQL/MySQL Database
Hello everybody,
Here is my problem. I developed a application for my job using a SQL Database (SQL Server 2008R2). It works ok on my local computer but this is only because I have spent many hours and many days just to figure out how to do this. No I want to run this app from another computer on a network and it doesn't work. I tried everything, permissions, firewalls, settings, etc. etc.. nothing.
Then I changed SQL with MySQL and it's the same. It works fine locally but I can't make it work over a network. So I am thinking to an alternative to all the server-based solutions because it's really too difficult for my needs and the last thing I want is to spend day after day for maintenance and server management.
The idea is that my app is running by only 4 users so I don't think I need the complexity and performance of Sql or MySQL server so I want to keep it as simple as possible.
My only request is that I need the 4 users to be able to write at the same time a certain info to the db.
So, please tell me if there is an alternative, like a simple database file that is stored on a simple folder on a simple computer that can be accessed by 4 users.
SQL and MySQL have very great performance but I am not a database specialist and I only want to create a simple app. The database itself is also quite simple.
The other problem is: let's say I want to sell this software to another company. They would have to hire a SQL server specialist just to make it work an to be sincere I think this is the wrong to do this... Not for what I need anyway.
Pls help me with this.
-
Feb 14th, 2014, 06:11 AM
#2
Thread Starter
Addicted Member
Re: I want an alternative to SQL/MySQL Database
What about Local Database (SQL Server Compact 3.5 database for local data)? As I understand there is no need for SQL Server to be installed. Is it easier to work with? And can I put it on a server computer so multiple users can read/write to it?
When I mean users I am not referring to database users. They are people that have the same app installed and that access the database in the same way, using the same connection string. There will be no need for user/password kind of thing...
Edit:
It doesn't work because it Server Compact doesn't allow to create a database on a network share.
Can anybody please give me some suggestions?
Last edited by ovi_gm; Feb 14th, 2014 at 07:12 AM.
-
Feb 14th, 2014, 03:38 PM
#3
Addicted Member
Re: I want an alternative to SQL/MySQL Database
When you say network do you mean over LAN or WAN? If it's LAN, I'm assuming you allowed remote connections and the client pc had the correct driver installed too?
-
Feb 14th, 2014, 04:34 PM
#4
Thread Starter
Addicted Member
Re: I want an alternative to SQL/MySQL Database
Originally Posted by smendoza
When you say network do you mean over LAN or WAN? If it's LAN, I'm assuming you allowed remote connections and the client pc had the correct driver installed too?
I mean LAN. I did what you were saying but I couldn't make it work. Anyway, I didn't like the idea of SQL Server in the first place because it is too complicated for what I need. Besides, I am not a specialist in this field, this is just a hobby.
I barely could make it work on my local computer. And there is a possibility to give this software to another company and they will get scared when I will tel them they need a SQL Server and somebody to be able to maintain it, cause I won't be there. So I need something to install and work without any complications.
I did some tests with Access database today and I am surprised how easy it worked over the network. It is just a file that can be copied on a PC on a network and it can be accessed by anyone. I was thinking to go this way.
My app will be accessed by a maximum 4 - 5 people and rarely they will be on the same time, on the same project. So maybe, when someone edits a database table, somebody else will access the same table (if needed) on a read-only level. Something like this...
What do you think?
-
Feb 14th, 2014, 04:35 PM
#5
Re: I want an alternative to SQL/MySQL Database
And the correct connection string too... that's important... "it doesn't work. " statements like that without any further information, like what the error was or anything... is meaningless... maybe all you needed to do was set the right server on your database... maybe it's simply a matter of getting the right drivers or something.
-tg
-
Feb 14th, 2014, 05:09 PM
#6
Thread Starter
Addicted Member
Re: I want an alternative to SQL/MySQL Database
Probably. Anyway, I tried a lot of things, all the possible connection strings, I got a lot of different errors, read a lot of forums, a lot of problems and solutions... and to be honest I'm tired of it. So I really want to leave SQL Server forever and focus on something much simpler, that is ok for my application. It really doesn't need all this performance and I loose valuable time trying to make it work. If there isn't any simpler solution then this project will be abandoned.
I repeat, this is just a hobby of mine and I don't want it to be so much time consuming. I think I have spent several days just to make it work on my local computer. I had to make a "possible errors book" of my own with problems that might come up when installing SQL Server and solutions to it like modifying registries and so on... Really guys, this is too much for me. I really don't have the physical time to make this work. So this was the idea. A software that installs very quickly and that can work very easy.
If I have to reinstall my windows i don't want to spend a week just to make this work again.
Maybe that's just me, but everytime I installed SQL Server I got different errors. Not to mention the impossibility to uninstall. While I created this app I think I reinstalled windows for 4 times just because my SQL Server Setup screwed up.
I'm sure this is great for complicated multi-user applications but I really need something simple. So please just tell me if there is an alternative that I should focus on, and please consider SQL Server forgotten in my case
Thx.
-
Feb 14th, 2014, 05:37 PM
#7
Addicted Member
Re: I want an alternative to SQL/MySQL Database
I wouldn't recommend access because the sql syntax is MS ACCESS syntax and I waste a lot of time looking the correct syntax. There are a few other reason but I don't feel like explaining them.
-
Feb 15th, 2014, 09:53 PM
#8
Lively Member
Re: I want an alternative to SQL/MySQL Database
Hi,
Reading your thread I agree SQL Server is too difficult to install for local apps. I use VistaDB you can check it out at : http://www.gibraltarsoftware.com/
-
Feb 15th, 2014, 10:40 PM
#9
Re: I want an alternative to SQL/MySQL Database
If there is no data sharing... then SQL CE might be an option... 7 dlls, one sdf file and that's it... but it re----
ugh... I jsut read this:
What about Local Database (SQL Server Compact 3.5 database for local data)? As I understand there is no need for SQL Server to be installed. Is it easier to work with? And can I put it on a server computer so multiple users can read/write to it?
No... you can't... if you need multiple users to be able to read/write from/to the database then you're looking at Access (ick), SQL Server, My SQL, or Oracle...
Yes, you could use a local database, but that's what it is... a local database. Yes, it does not require SQL Server, as I started to mention, it's 7 DLLs and the sdf. Is it easier to work with? That's debatable... it's a different flavor of SQL Server, so there's differences, some of them minor, some of them significant. And as I mentioned, no you cannot put it on the server for multiple people to access. It is a local database, it is designed to be accessed by a single user at a time.
-tg
-
Feb 16th, 2014, 04:29 AM
#10
Thread Starter
Addicted Member
Re: I want an alternative to SQL/MySQL Database
Thank you crmfghtr for VistaDB. I will take a look and see how it fits. Yesterday I changed some of my code using Access database. I am doing everything by code (connections, datasets, datatables, dataadapter, etc...). This access solution seems very convenient to me right now. I find it easy to work with, also over a network. I have to check now how it can be accessed and modified by multiple users. I was thinking to somehow control each table access so that, at a given point, only a single user can modify a table. The other users will be able to see the table but not modifying it.
I don't know yet how to achieve this. Maybe I will create a text file near the database (or a field in the database) where I will store the opened for edit tables. So, if a user opens a table that is already opened then he/she can't change any data.
And something else: my app is something like a project manager inside a design company. So for each of the company's projects I was thinking to create a separate database so each project has it's own database, together with other useful data, stored in a folder of it's own. So, in case something happens with the database I will not loose every project that was created in time. I'm not worried about disk usage because each project has got gigas of data stored in it. So 1MB of data plus or minus for the database is insignificant.
Do you think I'm on the right track?
Last edited by ovi_gm; Feb 16th, 2014 at 04:37 AM.
-
Feb 16th, 2014, 10:05 AM
#11
Re: I want an alternative to SQL/MySQL Database
Honestly? No. I don't think you are on the right track at all. Especially with multiple databases, text files for... what? "checking out" tables? Personally I think you're setting yourself up for disaster in a major way.
Whether you want it or not, here's my opinion: You really should take the time to learn how to use a server-based database...have ONE database, and a proper maintenance and back up process in place. Use a table to segregate your data... that's one of the points of a database... create Project table... then use the ID of that to segregate your other records.
-tg
-
Feb 16th, 2014, 01:04 PM
#12
Thread Starter
Addicted Member
Re: I want an alternative to SQL/MySQL Database
Thx techgnome for you advice. Could you please give me some arguments as to why I shouldn't use the above method? The idea behind the multiple db (each project with its own db) is because I have heard that access database might at some point get destroyed by unknown reasons. That's why I was thinking that no matter what happens I can't loose ALL of my data.
I agree that SQL is safer but wit Access maybe this is better. The projects themselves are not even related to one another in any way...
And another advantage (besides the absence of a db server) would be the possibility to export a project database to a xls file that could be transmitted to somebody that doesn't have this app installed. I did this in my first version to export to a xml file and it was quite a lot of code...
-
Feb 17th, 2014, 01:20 AM
#13
Re: I want an alternative to SQL/MySQL Database
You will have to learn server based solutions. If you can make it work over the network then it will work via a localhost connection (still over "network"). Your solution has to be scalable. What if the business grows to more than 4 users? As to XLS, there are a lot of tools and reporting technologies that can do this. No need to reinvent the wheel. But you will need to know how to connect these to your server based database.
You are already worrying overly on the details of implementation when you can still come up with a better implementation plan which may render obsolete/inconsequential the things you are worrying about right now. See the forest for the trees.
-
Feb 17th, 2014, 06:42 AM
#14
Re: I want an alternative to SQL/MySQL Database
The projects themselves are not even related to one another in any way
Right up until the moment someone asks "how many tasks have we got outstaning in total, across all our projects?" or something similar. You suddenly find yourself needing to query each database separately and manually aggregate the results. And then someone adds a task to the third database while you're still querying the second and throws yor calculations off. And so on and so on. That's a pretty basic example of why ou try to keep everything under one roof but it doesn't take much imangination to see that there are a load of scenarios where you will regret separating them. As TG said, you would be better keeping them as one DB and introduciong a "project" field to the apropriate tables.
This:-
So, in case something happens with the database I will not loose every project that was created in time.
...is not a good argument for separating them either. It's an argument for taking regular backups and ensuring that you can restore them in a timely and resilient fashion.
That's not really pertinent to your original enquiry though. As to what DB you should use, in your case I think I probably would recommend Access (which is not something I do often). It sounds like yor instances of paralel access will be very rare so you should be fine. I believe (though perhaps an Access expert could confirm because it's a loooong time since I did anything with Access) that Access will not allow two users to access the database simultaneoulsy, the second user is locked out of the whole file, not just the individual tables. If I'm right in that then you have no concerns about parallel access.
Whether I'm right or wrong, though, do not do this:-
Maybe I will create a text file near the database (or a field in the database) where I will store the opened for edit tables
What you're doing tere is trying to implement the logging and locking capabilities of a "proper" server based DBMS. It's a huge subject and one which the developers of those DBMS's have invested hugely in... because it's hard! If you you are struggling to get your head around SQLServer then you aer certainly not equipped to rewrite a huge swaythe of it's functionality. (I'm pretty good as a SQL Server developer and I'm damn certain I wouldn't be able to do it).
Overall, I think you would be best off taking the time to learn SQLServer. It does have a bit of a learning bump but folks here will be happy to help you past it. And once you've got it down pat you'll find it's actually pretty easy and you'll be glad you did it. That said, if you're talking about 4 users with rare parallel access then Access deployed to a shared drive is probably a legitimate alternative for you.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Feb 17th, 2014, 07:00 AM
#15
Junior Member
Re: I want an alternative to SQL/MySQL Database
Most sql servers (including mysql) installed on a local machine only listen on 127.0.0.1
If you want something small and portable, give sqlite a try.
-
Feb 17th, 2014, 07:11 AM
#16
Thread Starter
Addicted Member
Re: I want an alternative to SQL/MySQL Database
Originally Posted by FunkyDexter
If you you are struggling to get your head around SQLServer then you aer certainly not equipped to rewrite a huge swaythe of it's functionality. (I'm pretty good as a SQL Server developer and I'm damn certain I wouldn't be able to do it).
Thx FunkyDexter for your comment. You are right in every aspect of what you said, I just realized it. When you first think of how to solve a problem you never now how deep it actually goes and you think the solution is quite simple when it actually isn't.
Anyway, I decided to go with Access. I will rarely have simultaneous access to the same records in the database so it wouldn't be a problem.
Besides, I did some tests with a app where me and other user tried to change the same record in the same time. I get no error and the change is the one that was clicked last. Something like this... But again, there will be rare times, almost never, when this will happen.
I also read some articles that said Access is recommended for less than 25 users (I have about 5) and less then 50.000 records / database which is almost impossible to reach for now.
So I will go this way and in the future if the app proves itself to be useful and productive I will take into consideration SQL Server. And of course if Access proves to be unproductive. I guess this is a battle between advantages and disadvantages, comfort, costs and so on. Thank you all - thread resolved.
-
Feb 19th, 2014, 02:23 AM
#17
Re: [RESOLVED] I want an alternative to SQL/MySQL Database
If you are going to use Access then pls. consider to always have a back-up of it since Access is prone to corruption. =)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|