Multiple SQL Databases

[George1111]

With ACCESS I can have any number of Databases with the same name, by simply putting them into different directories.

With SQL Server 2005/2008, is it possible to have multiple databases of the same name, on the same Server Machine ?

(I think not, but its worth a try :))

[dglienna]

Nope, but you can have more than one SQL Server instance running :)

[George1111]

Well that could do it - great ! I guess that would mean a significant overhead if I had 20 companies able to be selected

The problem stems from companies having multiple companies on their server which has worked happily on Access up until now

I think it may be better if I change my software to allow for different Database Names within the same SQL Server

Thanks for being a sounding board !

[Shuja Ali]

George. I guess it is time to re-look at how your access database was designed. Maybe you don't need different databases for different companies. You could be just fine with one database for all the companies.

Imagine, how would it work out when your client base increases and goes up to say 200. How would you handle so many SQL instances.

[George1111]

Point taken ! I would need a server farm!

I'll just re-engineer my app. to allow any Database name to be used so that one SQL Server can handle any number of databases.

(I am also assuming that each database can have a maximum size of 4GB if I'm using SQL Server 2005 Express)

[Shuja Ali]

I guess I was not clear enough in my previous post. What I actually meant by re-engineering is that you should have one database that will handle any number of clients. There is no need to have a separate database for each client. I assume the structure of all these databases would be similar.

[George1111]

Its actually a full ERP solution with around 120 Tables in a database.
Each company is totally independant of each other and needs to be backed up separately etc etc

So I'll end up with one database per company (as each company can be quite large in terms of storage after a few years) - but should still be within the 4GB limit

Thanks

[Alsvha]

Change the application to allow for different database names. It is the best approach.

[Shuja Ali]

This makes sense now. Are you going to host the application and DB in your own office or will it present in multiple locations. I believe you are trying to sell Software as a service, which is a good idea. :thumb:

[ComITSolutions]

You Can also try this with single instance

Attach the Different Databases for each company and name the Database according to Company.
You can always change the Initial Catalog string dynamically for the connection string in the front-end with out doing much alteration in your front end and back end.


Eg.
Company Name SQL Data File SQL Log File DataBase Name
ComPany-1 Company1.MDF Company1.Ldf Company1DataBase
ComPany-2 Company2.MDF Company2.Ldf Company2DataBase


In Front- End


Dim DataBaseName as string
DataBaseName ="Company1DataBase" <--- This You can set programatically

SqlCon = New SqlConnection("Password=xxx;Persist Security Info=True;User ID=sa;" & _
"Initial Catalog=" & DataBaseName & ";Data Source=MyServer")