Hi guys, What database is going to be best to use with ASP? MySQL or MS SQL? Which will be best to get my head around quickly considering I'm just used to connecting to Access databases and SQL Lite as I've only worked on small projects and this will be for a medium sized project. Thanks

For a generic answer, SQL Server will be the easiest to use with ASP of any flava, as there are native framework objects that will speed up your dev process. Connecting to MySQL will require an additional library that the group distributes for free.

Is this classic ASP or ASP.NET?

Classic ASP

Will it be hard connecting to MySQL? Can you still take backups of the database and can you view the actual database like you do with Access?

SQLExpress editions FTW http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx (2005 version) http://www.microsoft.com/express/sql/download/ (2008 edition - I prefer it)

They are free, limited versions of SQL Server, but great for small-to-medium sites and dev work.

A site built in SQL Express will work in an enterprise version of SQL server should you need it to do so, but in my opinion (not supported by Microsoft!) many small sites can be run from the free Express version. The major limit is that the DB cannot grow larger than 4GB.. which, if you are used to developing with Access, will be no problem at all.

Typically, you would install SQL server express on your dev machine, and then copy the database onto the live server (running an enterprise version)

The SQL Server Management Studio provides a nicer interface than Access, and can do everything that Access can (as far as I recall, last time I used Access was about 8 years ago.. evil stuff) - definitely backups, view/edit database rows and design schemas.. it is a really pleasant experience.

MySQL on the other hand is also pretty easy enough to use, although the MySQL Control Center is not as user-friendly as the SSMS.

There are slight syntax differences between the SQL code for MySQL and Access, but on the whole they are similar.

You'll need the MySQL ODBC Driver installed on your server (a .dll) and reference it in your connection string.

PhpMyAdmin is the alternative GUI but I hate it. It is web-based and and server that has MySQL installed will give you PhpMyAdmin access as part of the hosting package.

In my opinion, as your experience is with Access, then go with SQLExpress.

with Scud.

I use both MySQL and MS SQL Server daily but on projects of different scope. Scud is right about SQL Express it will keep you going for a long time on small sites. And the 4GB limit can fairly easily be overcome by sharding* your data.

*Don't use this guys modulus approach to distributing data if you do this.

Cheers for the help guys

In regards to querying the database does it all stay the same in ASP and the SQL syntax is it just setting up the database and the connection that differs?

The project will have 600 users to start with but will constantly grow so I'm not sure what will cope with that best?

first, if you're in SQL server I would highly recommend utilizing stored procedures. They are there for a reason, they run a lot more optimized than doing inline SQL.

Within Enterprise Manager you can generate stored procedures based on a template, ie, say you want to to create a stored procedure for an insert / update / delete there are tools to build a generic one for you to replace out code as needed.

As well, I'd suggest installing books online with the tool (not sure if it is part of the Express install or not, just remember it being there for Enterprise level). You can find all the information you need there as far as how to set up stored procedures, etc.

And when in doubt on syntax for any DB connections:http://www.connectionstrings.com/

querying the database does it all stay the same in ASP and the SQL syntax is it just setting up the database and the connection that differs

With MS SQL, queries would be pretty much exactly what you are used to, and setting up the database exactly the same. Connection strings will be different.

600 users is not really a medium site, I'd consider that pretty small (in web terms, not in pressure-to-build-a-really-good-site terms!) so unless each of them is hitting the DB multiple times a second all the time, there will be no serious issues there.

Stored procedures really rock, if only for the measure of security* you get from them

And this helpful tool will generate them (or, at least, the basic ones) in the SQL Server Management Studio IDE..http://www.ssmstoolspack.com/ (plus some other cool stuff)

• SQL injection is a MAJOR problem with ASP Vbscript sites, there are automated scripts that update every "text" field in the DB with iFrames linking to malicious sites. I've had at least 5 sites hit (thankfully, ones I did not develop) and fixing them is a PITA

NB, this "security" provided by using Stored Procedures is not at all adequate, although with correct permissions it will filter out the worst offenders. Santize all possible user input, especially anything that comes from a querystring.

Excise my ignorence, but am I right in thinking that in terms of my ASP and SQL code it doesn't really matter what type of database I use it's just the connection string that matters?

The only reason I'm slightly panicking is because the client will be telling me what database I will be using in the next day or so though it's looking like it's going to be MS SQL 2008 and I just want to make sure I'm not out of my depth as it's a really tight deadline.

Is there a way I can create my database like I do in Access by creating all the tables in Access and inserting test data manually?

Originally posted by: Suzy

Is there a way I can create my database like I do in Access by creating all the tables in Access and inserting test data manually?

do you want to use Access to test manually, or do you want to use the clients db to test manually the same way that you've previously used Access?

The clients db to test manually the same way that I've previously used Access I think or can I not do that, do I need to create the tables and test data via SQL?

It's looking like I have got the option of MySQL as well but not sure which will be easiest to work with when I use ASP?

Definitely get hold of a copy of SQLExpress 2008 and SQL Server Management Studio Express

Setting up tables etc is very similar to Access, basically, create a database, click down through the oprions until you get to "Tables", and right click--> create new table.

The only thing that is different would be that your identity field must be set to "Primary Key" (as in Access) - but also set to "Identity" which is in the "Properties" panel

You can edit table data directly exactly as you can in Access (good for initial testing)

Once the DB is set up, and the connectionstring set, the actual ASP/SQL code will be practically identical to what you are used to working with with Access.. might be minor differences but I doubt it.

You can then backup your database and restore it to the live server, and test there - or, using the SSMSE GUI, you can edit the live database directly

If you are "slightly panicking" at any stage, and you are not getting answers in the forum fast enough, P.M. me or send a mail to scudsucker @ gmail - I have lots of ASP VBscript experience (although I try to avoid it these days)

Thank you ever so much scudsucker, I really appreciate that So I presume that I can somehow create a database in SQLExpress 2008 and then move it onto the webspace and change it to a MS SQL 2008 database?

I'm just looking at the support files of the web hosting provider they will be using and I'm presuming it is this bit I will needhttp://hosting.intermedia.net/support/kb/default.asp?id=1063 ?

There are three possibilities:

a) Download and install SQLExpress 2008 and SQL Server Management Studio Express, then create the database locally, and test locally (on your own server / IIS), then make a backup of the database. You'd then upload it to the server and "restore" - essentially a copy operation from your local version to the live version.

SQLExpress 2008 and SQL 2008 databases are near enough identical for you to do this without any real concerns.

or

b) Download and install SQLExpress 2008 and SQL Server Management Studio Express, then create the database locally, test locally with random data.

Then choose the option to "Generate Scripts" in the right click-->tasks menu (on the database name) which will then spit out the SQL needed to create the empty database.

You can then execute that on the live server to get an empty copy.

or

c) Download and install SQL Server Management Studio Express, and do everything on the live server.

The way I'd do it is option A, using the information in the knowledge base link you provided, though I always end up editing tables on the live server in the pre-launch panic.

Thanks I'll let you know how I get on!

They have just given me all the data I need in an Excel file (all 600 records), am I able to import this into MS SQL 2008 somehow?

Yup- right click on the database in the "object explorer" window, choose "Tasks" --> "Import Data"

Click through choosing the appropriate settings (Datasource = Excel etc)

I usually import all the data as-is from Excel then use SQL to move it into various tables for a normalised database design, but if you have just one table just import it and then edit the data-types as you wish.

The Object Explorer windows sometimes has to be refreshed in order to show changes you have made.

Is that in SQL Server Management Studio Express? Does it matter if I do it on the database on the website or in SQLExpress? I presume not.

Thank you for all your help with this scudsucker, and other who have replied, and taking the time to answer my questions

Yes, (SMSS Express can be used to manage Express and enterprise versions) and

If you are going to develop using the live server, then connect to it using the database connection you should have been supplied, or, do it on a local copy.

Doesn't matter, turns out it was an error at the hosting companies end.

Sorry can I just ask do I need to download SQLManagementStudio_x64_ENU.exe or SQLManagementStudio_x86_ENU.exe from this pagehttp://www.microsoft.com/downloads/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en#filelist ?

Yes, that is the one - most likely the x86 version.

x64 is for the newer 64 bit PCs so unless yours is a very new one, download SQLManagementStudio_x86_ENU.exe

Thanks scudsucker

Installation isn't as simple as I expected! Do you mind if I keep asking questions? Do I want:

New SQL Server stand-alone installation Launch a wizard to install SQL Server 2008 in a non-clustered environment or to add features to an existing SQL Server 2008 instance

or

New SQL Server failover cluster installation Launch a wizard to install a single-node SQL Server 2008 failover cluster.

Standalone should do it.