Only One Database?

[plnelson]

I've been using HostMySite for several years, for three very simple websites, quite happily.

But recently I decided I would learn more about writing ASP.NET code to interface with a Microsoft SQL Server so I upgraded one of my sites from their "ASP.NET" hosting package to their "ASP.NET+" package. The difference is that the latter gives me "MS SQL Server 2005 or 2008" with 600MB of storage. My plan is to use this to learn about database concepts, control-binding, ADO, etc. (i.e., just for study, not for commercial use)

When I went to create a database to start playing with, HostMySite told me I could only have ONE database. It could be 600MB with a jillion tables and relationships but I only get to create ONE!

Is this common in the web-hosting world? Why do they care how many databases I have as long as I don't exceed the storage or bandwidth limits? How do other hosting companies do it with MS (not My-) SQL?

Thanks in advance.

[felgall]

Why do you need more than one database. More than one database is like more than one universe - if everything imaginable fits in one why should there be more than one.

Just use different prefixes for the tables for each application and set up different users that have access to the different combinations of tables and your one database will handle everything that a billion databases could handle without needing more than one.

The whole point of creating a database back when the concept was first though of is to make it a central repository for ALL data. Having more than one database defeats the purpose for which databases exist.

[OKICreative]

As mentioned technically you can get by with one database as it is all you will ever need.

Personally I prefer using one database per script I use however I did used to get by with just 1 back in the day and did just fine. One benefit would be that for backups you only have one database to backup instead of multiple.

You can probably find a host that will give you unlimited databases and tons of diskspace and bandwidth you will never use but if the service stinks well it kind of defeats the purpose

[plnelson]

Why do you need more than one database. More than one database is like more than one universe - if everything imaginable fits in one why should there be more than one.

Because I'm doing this as a learning/educational exercise, the advantages of multiple databases I can see are:

1. I can experiment with different normalization schemes or run the risk of totally clobbering some database with a runaway set of newbie SQL transactions without wrecking other databases.

2. I can expose certain database's passwords, etc, to the public so I can request help in various sw developers' forums on particular topics I'm struggling with without exposing my whole site.

But, yes, you're right that what I'll have to do to cope with this is to create one humongously-complicated database with "some of this and some of that" to use as a learning exercise. It's just that on my desktop PC, where I have MS SQL Server installed, I create a separate database for each new concept I want to learn or experiment with.

[felgall]

1. I can experiment with different normalization schemes or run the risk of totally clobbering some database with a runaway set of newbie SQL transactions without wrecking other databases.

You can do that within one database. Just set up a username that only has access to a group of tables and that user can't clobber anything else.

2. I can expose certain database's passwords, etc, to the public so I can request help in various sw developers' forums on particular topics I'm struggling with without exposing my whole site.

If the username only has access to a few tables anyone using that username and password can't see any of the other tables and has no way of telling that they are there. No difference between the users and matching tables being in separate databases or all in the same one.

Internally all the databases on the one server are treated as parts of the one database anyway so whether you set your part of that database up as one or many doesn't make it work any different.

You might want to read up on the GRANT command which is used to define user privileges such as what tables a given user can read and what ones they can read and write.

[MyDomAinZ.us]

To allow only one database will be their hosting policy. You should check the plans before subscribing the services.

Good luck

[plnelson]

You can do that within one database. Just set up a username that only has access to a group of tables and that user can't clobber anything else.

I didn't know I can do that! (See? Proof that I'm a MSSQL newbie!) This is the GRANT command you mentioned? I looked in my O'Reilly SQL Pocket Guide (2nd Edition) and I don't see GRANT DENY or REVOKE in the index, but they're all over the place on the web - any idea what's up with that? ( Thanks in advance!! )

Internally all the databases on the one server are treated as parts of the one database anyway so whether you set your part of that database up as one or many doesn't make it work any different.

Really? So if I'm experimenting with OTLP veresus OLAP, say, or different schemes to optimize for writing as opposed to reading, I won't see any performance difference?

[plnelson]

To allow only one database will be their hosting policy. You should check the plans before subscribing the services.

Good luck

Easier said than done. When I ran into this yesterday I called them and asked where, exactly, does it say it on their website and they couldn't find anyplace! I then talked to a supervisor at HMS and they couldn't find it either. Even if it's there someplace, if they can't find it I don't know how the PDU is supposed to find it.

Anyway, my question was whther this is common in the webhosting world and why web hosts would even CARE how many databases you have as long as you don't exceed the storage and bandwidth limits.

But, thanks to felgall I might have a work-around for at least part of the problem so my question migh be more academic at this point.

[MyDomAinZ.us]

Anyway, my question was whther this is common in the webhosting world

Our answer is 'not at all'

and why web hosts would even CARE how many databases you have as long as you don't exceed the storage and bandwidth limits.

It depends upon webhost-to-webhost.

Regards.

[felgall]

I didn't know I can do that! (See? Proof that I'm a MSSQL newbie!) This is the GRANT command you mentioned? I looked in my O'Reilly SQL Pocket Guide (2nd Edition) and I don't see GRANT DENY or REVOKE in the index, but they're all over the place on the web - any idea what's up with that? ( Thanks in advance!! )

Page 64.

[plnelson]

Page 64.

Nope.

Page 64 is CUBE and ROLLUP. This is 2nd edition brand new from Barnes and Noble 2 weeks ago. Maybe there's a Third Edition out?

But That's OK - as I said, it's all over the web now that I know to look for it - it just makes me wonder what else is missing from the Pocket Guide that I might want to know.

BTW, another surprise from HostMySite - when I hooked up to my new database from my home PC using Microsoft SQL Server Management Studio Express I could see a listing in the Object Explorer of all the other databases on that server. I tried expanding a few to see if I could because that might tell me how exposed MINE was and most of them were protected but ONE that I tried wasn't!

Isn't SQL server configurable to prevent this? I'm new to SQL and databases, but so far this is very interesting.

[r937]

it just makes me wonder what else is missing from the Pocket Guide that I might want to know.

most likely, just more DBA-oriented stuff

i've got the first edition, and the Intro clearly states that it is about SQL, the language used by all database systems, and that the book specifically covers SQL that works in Oracle, DB2, SQL Server, and MySQL

database administration commands would typically be covered only in a book oriented to a specific database system

BTW, another surprise from HostMySite

your host is negligent

[dklynn]

http://dev.mysql.com/doc/refman/5.0/en/grant.html

Always go to the front end of the horse for good information!

Regards,

DK

[r937]

david, you're talking to the wrong horse altogether

plnelson is using MSSQL (MicroSoft SQL Server)

[SoulScratch]

david, you're talking to the wrong horse altogether

plnelson is using MSSQL (MicroSoft SQL Server)

[wwb_99]

Why do you need a host for this at all? You can get SQL Express free and databind at home, with full control of the system, to your heart's content. Especially with SQL server where having root access makes alot of stuff easier.

[MikeDVB]

Why do you need a host for this at all? You can get SQL Express free and databind at home, with full control of the system, to your heart's content. Especially with SQL server where having root access makes alot of stuff easier.

From a learning standpoint, this is exactly what I was wondering myself. Set up a development environment on your home PC and learn there... Cheaper, faster, easier.

[dklynn]

Rudy,

david, you're talking to the wrong horse altogether

plnelson is using MSSQL (MicroSoft SQL Server)

BUMMER! How'd I miss that one?

Does M$ have a similar documentation system for MSSQL?

Thanks for catching that!

Regards,

DK

[r937]

Does M$ have a similar documentation system for MSSQL?

yes

it is affectionately known as "BOL" == Books OnLine

i have downloaded it as a handy-dandy .chm file, but you can also access it over teh interwebs

it is available here -- http://msdn.microsoft.com/en-us/library/bb510741.aspx

you should see two window panes

the right side is the content, the left side is the navigation

in the left pane, scroll down and click on "WITH common_table_expression (Transact-SQL)"

you should end up at this url -- http://msdn.microsoft.com/en-us/library/ms175972.aspx

if these instructions worked, then congrats, you are now a BOL user