Whats the deal with SQL Server?

[Denaes]

Most of the books and tutorials seem to highlight it.

 

I know its a VERY expensive program. Is SQL Server required to use it in a VB.Net program?

 

So far, from what I can tell, oleDB/Access seems to be better, but maybe its just what I'm used to.

[bungpeng]

Of course you can use whatever database you want as long as there are OLEDB or ODBC support. Since SQL Server & MS.NET both are Microsoft products, then definitely there are some advantages if you use both of them together.

[Denaes]

Can you use SQL server without buying it?

 

Can I make a program using SQLServer rather than oleDB and distribute it as is?

 

I guess that would mean that I'd need to create the database with code then...

[bungpeng]

Of course cannot....

 

If you want to use database without buying it, then in Windows environment, you may only choose MS Access. But you need to know, with MS Access, you cannot use for client-server application, it is security issue...

[Denaes]

so you need to ship SQL Server 2000 with your program?

 

Its $2,000-$6,000 per liscence from what I see.

 

True, that may be worth it for true server situations like a corporation serving 100's of computers.

 

But then it seems that oleDB is going to be the standard for normal programs which you distribute to clients (ie, just datastorage).

 

Are there any books that deal with ADO.Net and oleDB then? It seems that every book just on ADO.Net focuses mostly on SQL Server and only oleDB as a sidenote.

[bungpeng]

Depend on your application need, you may use text file or xml to act as database.

 

Database is just a data storage of your application. The method to connect to different databases are mostly the same, I don't think there is any problem when they use SQL Server as a sample.

[Denaes]

Can I just replace the SQL Server namespace (SQLDB is it?) with oleDB and most everything would work fine?

 

I got a hunking huge book by APress, which covers both SQLServer and oleDB, but its first priority is always SQLServer.

 

Often times I'm not sure if data adapters work the same with their .update and 4 built in commands with their parameters or whatnot.

 

If its all compatable, just in a different wrapper to optimize for SQLServer, I really don't mind.

 

If its fairly different, then thats a pretty big problem for trying to sort through what sample code will work with oleDB and what isn't.

 

The people I work for insist on my database being an actual database and just ignore me when I mention XML.

 

In truth, it wouldn't be too hard to switch, would it? Load the oleDB database into a dataset, then save it as XML?

 

Personally I think an Access database is wasted on what I'm doing. I have like 15 tables, 6 relationships and only like 20 records (mostly testing my application entering data) and the database is over a megabyte already.

 

When I get a little app to generate a few thousand records, I'll see exactly if most of it is start-up bloat or if its proportional.

 

Do you know if XML is any faster/slower than .mdb?

[bungpeng]

Different databases, the main thing is the connection string different, you can refer to here:

 

http://www.able-consulting.com/ADO_Conn.htm

 

I will suggest you use MS Access (.mdb) then, definitely it will faster than XML.

[quwiltw]

Denaes,

Despite what bungpeng posits above, Access isn't your only choice. Take a look into MSDE. It might very well be overkill for this particular problem but it's something you should probably learn about any way.

 

Since I just googled it and didn't read it, I won't attest to the accuracy of this article but it should get you started.

http://www.informit.com/isapi/product_id~%7BD31B236D-00C1-4A5A-8832-BC9A68E9D5B8%7D/content/index.asp

[samsmithnz]

MSDE is indeed a 'free' version of SQL Server, but it has much less features and only allows a certain number of connections at a time. Make sure these contraints are ok for your project.

[bri189a]

It's also a pain to set up on your dev computer... at least has been form me... I'm sure you'll find plenty of my posts, and thankfully helpful suggestions from these guys, when you go to do it yourself... I wouldn't even want to think of the pain of deploying that thing. I use Access rather extensively and because of ADO.NET connection pooling I haven't had a problem with it. Of coarse these are desktop apps or very small web apps...

 

with MS Access, you cannot use for client-server application, it is security issue...

 

What are you talking about? (Seriously... I want to learn new things!), I do this all the time... as long as the data you have isn't consider sensitive what does it matter? Access passwords are easy to crack yes, but not that large of percentage know how and what would be the point if there is no sensitive info in it anyway? And if you have sensitive info SQL server isn't going to magically fix any security issues... any you had with Access will be there with SQL... maybe not as vulnerable or as apparent, but I'm sure it will be floating out there just the same. I've seen serveral large compainies (such as Intuit's Track-It!) that use Access as the back-end db of there main program - and it doesn't have a db password... so bam, anyone with Access can see the table structure, relationship structure, queries... everything!

 

How many CONCURRENT connections you have to the db is my biggest issue between Access and MSDE/SQL (Access has 10 max)... true SQL statements can't be as long, but to be honest, if you max out your SQL string... your doing something wrong... especially when you can do parameters.

[akiaz]

MSDE has the advantage over Access in that you can create stored procedures and user defined functions that will work directly when upgraded to SQL Server. This allows multi-tier application development to progress with you knowing ahead of time that you application will scale.

[bungpeng]

bri189a

Sorry, I may wrong. But I just curious how do you use MS Access in your client-server application? You share your .mdb file in a server share folder? what is the security and permission setting? Because I did try to use it before, but finally give up.

[bungpeng]

MSDE is indeed a 'free' version of SQL Server, but it has much less features and only allows a certain number of connections at a time. Make sure these contraints are ok for your project.

 

Sorry, I just want to confirm, is it MSDE is really free to use? Can I build a application with it and sell to customer? or it is only free for development purpose? I not sure about this.

[iebidan]

Well, if you go for prices, SQL Server is cheaper compared to Oracle, if you intend to do an application better add some support to other databases, so you'll be able to have more than 1 database supported in your application.

Using MSDE can be a good option, but as samsmithz says better know what can be the problems with it.

Using Access un a client-server application is done the way you say, share the database in a server and make the apps point at it, maybe this was your problem, the app was looking for a local path

[quwiltw]

Using MSDE can be a good option, but as samsmithz says better know what can be the problems with it.

Just a nit-picky clarification... the problems aren't going to be with it as much as with the developer and a steep learning curve because it is a pretty solid product.

[samsmithnz]

Just a nit-picky clarification... the problems aren't going to be with it as much as with the developer and a steep learning curve because it is a pretty solid product.

 

Yeh but I'm not the only one (as mentioned above) who has found it difficult to set up (maybe its the documentation). I've never had a problem setting up SQL servers, and I've set up all sorts of those now.

 

But yes bungpeng, it is free.

 

(link worked at time of posting)

http://www.microsoft.com/downloads/details.aspx?FamilyID=413744d1-a0bc-479f-bafa-e4b278eb9147&DisplayLang=en

[PhilBayley]

MSDE is free - be careful though as it does have limits i.e. it can only store 2gb worth of data from what i remember (or why would they sell you sql ?). I have found it a little hard to set up but it works well once it is.

 

If your looking for a portable solution, such as a cd based database then I have only ever been able to use access and you can secure this with a password which you specify in your connection string.

 

Best of luck with your project though.

[Procaine]

The constraints on MSDE are rarely a problem. the main ones are maximum size of 2gb, and maximum active connections to 25. Only the biggest web projects will ovceed these.

 

For my database applications, I use Firebird, a free, opensource database based on Interbase. It's extremely fast (compared with SQL Server) and it supports Embedded Servers (similar to Access - you can access the database without running a database service in the background), which makes it great for software applications. It's also got a .NET wrapper. Prepare for a big learning curve though.

[bri189a]

You got to remember that a server is just another computer... typically my 'servers' aren't in the traditional since a server. A server is basically any machine that acts a service to several computers... so for instance this particular computer I'm running on now holds a db for an application on 32 computers... this computer is the server for those computers when it comes to that program. The db is in a shared folder and the permissions to that folder are a group of people that I want to have access to it (I never use the Everyone Permission). They have the Share permissions of Read/Write and NTFS permission of Read/Execute/Modify. It's going to be the same thing on a traditional 'server' such as W2K-Server of W2K3-Server or NT-4-Server... set up a shared folder where they have the shared and the correct NTFS permissions. I have a couple out there that work in this way too. But again as all of these people have been saying, if the amount of connection or the size of the db will become an issue just going straight to SQL server... Access can only have 10 simulateneous connections and 2Gb in size, and I believe there is a restriction on table sizes too, and MSDE as Procaine pointed out has similiar restricitions. I'm in a blessed environment where neither of those are an issue...at least it hasn't been up until recently. Good luck!

[Derek Stone]

You do realize that any user with the given permissions can load up the database in Access and corrupt it to their pleasing? What you've described is a horribly insecure system.

[bri189a]

Derek, yes, IF they have the database password... I agree, it's not the secutist scheme...I'd be using MSDE of SQL for those apps if I thought it would be a problem... the database password keeps everyone except the person whose going to go to the trouble to figure out how to crack it, and somebody who knows how to do that would be willing to do the same for SQL or MSDE... But you know I look at a program we have here written by Intuit which has a Access database, no database password, and has so many relationships that a user altering the name of one field would make the program unusuable... yet they market it like that... I think you have to trust the people your selling it to, to some extent, and build security where you need to, when you need to. Anthing that involved secure data or critical data I wouldn't use Access and I wouldn't tell anybody to use Access, I think people have to take those things on a case by case basis, and I was just saying to the guy that it is possible to use Access if you need to.

[Derek Stone]

A user wouldn't need the database password to corrupt the file. They simply need to to open the file in Notepad and delete away. Edited February 6, 2004 by Derek Stone

[Denaes]

A user wouldn't need the database password to corrupt the file. They simple need to to open the file in Notepad and delete away.

 

:D

 

Something about that just sent me into a fit of laughter and really put into perspective how important security is and how we can often stand behind a paper shield thinking we're safe.

 

Can you give someone (or a program) permission to modify+read the data in the database without giving them permission to do said nasty things to the file itself?

[Denaes]

For my database applications, I use Firebird, a free, opensource database based on Interbase. It's extremely fast (compared with SQL Server) and it supports Embedded Servers (similar to Access - you can access the database without running a database service in the background), which makes it great for software applications. It's also got a .NET wrapper. Prepare for a big learning curve though.

 

I know a bit about Access and next to nothing about SQL Server. The huge price tag has prevented me from even wanting to learn it if the customer would have to have SQL Server as well. IE, its not what I'm looking for.

 

But this firebird. It looks interesting. You can use it like an Access file directly, but can also run it off a server?

 

If you run it directly from the database file (like access) is it faster or slower than a .mdb file would be?

 

I found a few links to a "Firebird Database", could you help me figure out which is the correct (best?) one?

 

I found:

 

firebird.sourceforge.net/

 

http://www.ems-hitech.com/pages/firebirddatabase/index.php

 

You said there were .net wrappers and a high learning curve (compared to what, sql server?), is there much documentation/boards to help out with this learning?