Questions re databases

impr0v

I'm a relative noob to db driven sites, having developed only two or three running off bog standard Access databases. I now want to take this to the next level and move up to a db which will support a larger site easily and, since I use asp, I suspect the logical move is MS SQL server.

However, the information on the microsoft product page is somewhat confusing. Do I only need a developer licence in order to create databases, rather than the uber expensive server licence? And is it fairly straight forward to create databases, i.e. is it anything like access to use?

Apologies if these questions are somewhat stupid.

Goodshape

sorry, no answers, just a suggestion -- have you had a look at using a MySQL database instead of the MS one? It's free, open and powerful.. plenty of resources out there to get you started too.

impr0v

I was under the, perhaps misinformed, impression that MySQL isn't generally used on a windows server, and is more usually used with php.

I get the feeling I'm increasily showing my ignorance.

Seth Brundle

Download trial here -
or here

Are Access and MS SQL the same? Very much nope! But if you understand the principles behind Access it will give you a good start with MS SQL. Search Google for tutorials on it. You will also probably need to set up an IUSR_<machine> account or whatever to access it via IIS when testing your site.

Goodshape

impr0v wrote:

I was under the, perhaps misinformed, impression that MySQL isn't generally used on a windows server, and is more usually used with php.

on a windows server? perhaps not as much so, no. but I don't see why it wouldn't work.

can't really comment myself anyway.. I'm no expert - only know what I use, and thats MySQL with PHP on a linux box.

seamus

MSSQL is a database server, just like MySQL or PostGreSQL.

Afaik, the licensing for MSSQL is per-installation, not per-database.

In either case, you can download a free version of the server for development purposes:
http://www.microsoft.com/downloads/details.aspx?FamilyID=413744d1-a0bc-479f-bafa-e4b278eb9147&DisplayLang=en

This allows you to develop in the MSSQL environment, but afaik there's a limit to the number of concurrent connections (like IIS on WinXP).

It's all based on SQL, so you'll need to know SQL to create databases, but it's a doddle. You will also need a client* to connect to the MSSQL server. I use EMS MSSQL Manager Lite (Freeware): http://sqlmanager.net/products/mssql/manager/ and it does the job.

*Afaik, the server/enterprise/full version of MSSQL comes with a MMC plug-in which acts as your MSSQL client.

Serbian

MySQL works perfectly on a Windows machine. You can download the installer here (it's miles down the page, so just scroll or do a search for 'windows'). I would recommend downloading the Windows Essential version as that is the easiest to install for someone who is new to MySQL. There is an installation guide for Windows here.

squibs

I'm running and asp/mysql site with no issues. There is definitely more documentation for php/mysql though.

impr0v

But doesn't the MySQL idea presume that I have the freedom to install the server element? This isn't the case, and the hosting company I use have only windows boxes, so I think it would be safe to assume they don't support MySQL.

seamus wrote:

It's all based on SQL, so you'll need to know SQL to create databases, but it's a doddle. You will also need a client* to connect to the MSSQL server. I use EMS MSSQL Manager Lite (Freeware): http://sqlmanager.net/products/mssql/manager/ and it does the job.

*Afaik, the server/enterprise/full version of MSSQL comes with a MMC plug-in which acts as your MSSQL client.

In other words there's no gui like there is an access environment, where you can create tables, etc., you have to create them using an SQL statement?

I don't understand the client concept you refer to. I won't be running a server locally for testing, I'll most likely be testing directly on the remote server, does this make any difference?

Seth Brundle

1. I wouldn't assume that because it is on windows that MySQL is absent. Many of the windows hosts I use support MySQL. I also have one (in America) that supports MySQL, MSSQL (and Access)!

See the linked Apps in either my or Seamus posts. The software gives a GUI interface for making tables, etc but just not as basic as Access. also the likes of forms, etc are not standard but then again why would they be?

If you are not planning on runnig a server locally then thats fine. It is just quicker than accessing a remote server.

canker

Microsoft SQL Server is the way to go!

Secure, robust, great performance etc etc.

And if you're coming from access you'll find Enterprise manager (comes with any sql server lisence) great for creating databases, tables, views, constraints or whatever without having to jump into the SQL itself.

You should just need a single developer lisence (about 130 Euro i thinks) and your web host will pay for the server lisences and all that.

I use both webhost.ie and discountasp.net for hosting sql server databases (and asp websites) and find them both excellent (webhost is more expensive but generally better).

seamus

Apparently there is a GUI - as kbannon and canker point out. I'm not overly familiar with MSSQL.

The way you're used to doing it improv, I'm guessing is to design and create the database in Access, then upload the .mdb file to the web server. It works quite differently with DBMSes like MySQL and MSSQL, in that you don't have control over the physical location of the database (more-or-less). You connect to the MSSQL server, and give it commands, and it returns answers to your queries. What a GUI does is transparently create the commands for you (you tell it what to do, it translates these into SQL commands and talks to the server) and then gives you an easier-to-read visual display of the returned data.

Because of this, I think of a lot of developers (myself included) prefer to work with a local server while designing the DB and application. This makes DB access quicker (since you're not going over the web) and also allows you to work when your internet connection is down. Then you export the created database - it actually creates a file full of commands which will replicate the structure and/or data of the database when run on a different server - and create it on the live server.

I didn't realise how tough it was to explain. It's one of those things you kind of need to get your hands dirty with to begin understanding.

Seth Brundle

TBH your best bet is to download the trial version of SQL Server and play around with it to see what it is like.

impr0v

kbannon wrote:

TBH your best bet is to download the trial version of SQL Server and play around with it to see what it is like.

I've come to that conclusion too.

Thanks for the help and suggestions people.

steve-hosting36

As above, download and play with the trial version. Many hosts also offer shared MS SQL databases with Windows Hosting plans, which would allow you to test and build an online application.

rsynnott

seamus wrote:

*Afaik, the server/enterprise/full version of MSSQL comes with a MMC plug-in which acts as your MSSQL client.

Yes, MySQL Enterprise Manager. An application that never felt the gentle caress of a usability expert, sadly.

"You've accidentally put the same column name in two fields while creating a relationship (but before clicking ok)? You want to change it? Oh, no, I'm very much afraid I'll have to crash now. Byyeeee!"

canker

The ms sql gui is almost identical to access. You could also make all your stuff in access and copy the SQL and run it on SQL server!

DJB

Hi,

As probably already suggested as I only quickly read through the posts above but here you go anyway. When I started with MS SQL, this is what I did...

Download trial version of MS SQL from Microsoft site. This is a 60 day trial of the database. You can still use the tools such as Enterprise Manager, Query Analyzer, Data Import/Export, etc. after the 60 days but the database itself will not work after that time.

Don't bother with MS SQL on your local test machine if you don't want to spend money on licences. Your host will be charging you for the use of their MS SQL server, such as Hosting365 and you get good space from them. Download MSDE to your local machine and this will work just like MS SQL for your development purposes. It's a cut down version of the database engine and can handle up to 25 concurrent connections.

Now, in addition to using Enterprise Manager and the likes, you can still use Access as the frontend to your MS SQL Database. Quite handy during a migration period when you getting used to MS SQL Tools. I still use it and it runs much faster than Enterprise Manager, etc. when connecting and working with a remote database. To do this, you need to create and ADP file. Open Access, Select File, New. Now select Project using Existing Data. Save the file as a name, e.g. mywebsite.adp. Enter your server name (localhost or mssql.myhost.ie or something like that), put in username and password, select the database on that database server, click test the connection and away you go.

Hope that helps.

Also, when dealing with MS Access, MS SQL, MySQL the actual SQL syntax changes a bit. I tried migrating Access databases to MySQL before but came across too many code changes. The main ones for access to MS SQL are for dates use ' instead of # and when deleting, use DELETE FROM instead of DELETE * FROM. I think you'll have an easier job going to MS SQL than MySQL if you are already working with ASP/Access. Trust me! Been there. But I'm sure I'll get slated for something I said!

Best of luck

Dave

rsynnott

I'd be cautious of actually designing MS SQL databases in Access. And if you're using the migration tools, check what it does. The two databases do indexing and foreign keys a little differently; in particular, it's quite easy to end up with something that claims to be indexed but isn't. It's best to stick to ugly ol' Enterprise Manager for setup.