Hi, We're planning to upgrade our plan later to make use of 2 or more MS SQL Server databases. Each database will store different business entities but should be able to JOIN. Example: db1 contains the "setup" data (products, employees, customers) and db2 contains the "transaction" data (sales, payroll). We archive "transaction" data on a regular basis to prevent it from growing too much. This is our practice now on our intranet app. May I request that all (up 10 w/ Ultimate plan) of our databases reside on the same SQL Server instance? Consequently, a single db login should be able to access all databases. I've read another thread (http://forum.Winhost.com/showthread.php?t=6292) that , I'm afraid, tells me that you store the databases on different servers. If so, then my plans will not materialize. Thanx.
I'm afraid that might not be possible because our automated provisioning system will create the database on the least utilized server. If you create the databases one right after another, there is a high probability that they will reside on the same server instance, but we cannot guarantee that. I'm afraid we cannot set a single DB login for all databases either.
Our provisioning system will pick a server to place your database depending on how many databases are in the server, as well as load. Unfortunately we really cannot control where the databases are created, as they're automatically assigned and we have no setting to configure it how you asked. I did however, go into the CP and create 10 databases, and they're all in the same server. While I know it's not a reliable method, it's at least an alternative which may work on your case if you can create all databases at once. If the databases are on the same server, you should then be able to assign permissions to the database for other users as long as they're on the same server.
@Elshadriel, @Jose : Thanx for the prompt reply. Even without the ideal solution, I'll still go ahead with my plans. I'll tell you the result when i've crossed the bridge. Worst case scenario: I'll have to modify my code to use just 1 database.
