CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Multiple SQL Databases

    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 )

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Multiple SQL Databases

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Re: Multiple SQL Databases

    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 !

  4. #4
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Multiple SQL Databases

    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Re: Multiple SQL Databases

    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)

  6. #6
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Multiple SQL Databases

    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.

  7. #7
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Re: Multiple SQL Databases

    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

  8. #8
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    Re: Multiple SQL Databases

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

  9. #9
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Multiple SQL Databases

    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.

  10. #10
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    Re: Multiple SQL Databases

    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

    Code:
    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")
    Encourage the efforts of fellow members by rating

    Lets not Spoon Feed and create pool of lazy programmers

    - ComIT Solutions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured