Hello All, I have the ultimate plan, so I have 10 databases, much to my dismay I noticed that there is a username/password per database as if I have 10 different servers with a single database limit per server (hence the 10 databases). I read through the forum and I see that other people have lightly touched the subject, but this is a big problem for me as I need to organize my tables into different databases. I could very well just move everything into the same database (doesn't seem like I have a choice right now), but this will turn into a major problem for me at one point. I am going to have to come up with a backup plan for the issue. Aside from my need for organization, I would like to be able to join tables from different databases, but since these databases all live on different servers it is pretty much impossible to do unless link servers are setup (I haven't tried this yet). So as of right now I know that the following is impossible: SELECT tb1.*, tb2.*, calcColumn = Utilities.dbo.udf_CalcThis(123) FROM Database1.dbo.Table1 tb1 INNER JOIN Database2.dbo.Table2 tb2 ON tb1.JoinColumnID = tb2.JoinColumnID This is impossible to do because these databases all have different connection strings and linked servers are not allowed. So after my saying all of the above I have a request: Could the administrators please give us 10 databases in the same server so I can use one connection string, with one username and password combination instead of ten? Right now the way it is setup is very impractical and unrealistic. I think other users would agree with me about this. I understand that most websites really only need one or two databases, but I require 6 for the same site. Thanks, Eli
I'm afraid that isn't possible with the way databases are currently provisioned. Honestly it's kind of the opposite of the request we see from time to time, which is multiple logins per database. Just out of curiosity, why is it a problem to have the tables in the same database? Not trying to say they should be, just curious.
I was afraid you were going to ask this database thing is no big deal really, I can work around it I just thought it was oddly setup. I put a site together for someone, it is currently living on my test server. He told me about the magnitude of records that he was going to have imported so the first thing I wanted to not do is mix data in the same table even though I could using an additional column as a category column of some kind. So we are looking at about 6 different categories of information all pretty much using the same table schema, some small changes here or there. Two tables will only have 11,432 rows and the others will have +/-435, this is per day import though, so it isn't static and almost always growing. So here is the calculation: [(11432)*(2) + (435*4)]*365 days --> [22864 + 1740]*365 --> 24604*365 = 8,980,460 records total per year So because of this fact, I structured the system to split these imports into their own dated tables in their own separate databases at least that was the plan. I have seen what happens when you have too many records in one table, even if you index it it can get ugly and I am not a very good DBA, just a developer so of course I am probably missing best practices here and there. I also like putting certain functions/utilities into a specific database so I don't have to replicate it or control versions. You are probably wondering why the hell am I doing all this? This is all for a very crazy mathematician who loves stats. When the site is up I will link you. It is for all to see anyhow. You can check out the current stuff here: http://stochasticdemocracy.blogspot.com/, there are some tables there that are just images, I made them dynamic and I process some calculations too, just need to host it now. I just need to modify some code here and there to make it all work. I am going to just purge the tables every day before doing the import so there will never be a gagillion records. I refuse to make tables that large if I can avoid it. Well I thought it was worth a shot asking anyhow, not the end of the world though. Thanks for the reply, Eli
Dated tables is an interesting idea, but you may be underestimating how well SQL can perform is everything is properly indexed. Millions of records spread across dozens of tables isn't necessarily that big a deal. You know, assuming proper indexing and optimization. But without that, even a very small database can bog down. Even using dated tables though, it would seem more convenient to have all the categories in one database and just use table prefixes to keep them straight. But that's just me. You're the one who has to wrangle all of that data, so you should be comfortable with it.
My main problem is I am not very good at indexing because I still don't understand it much. I have to run some tests on my own for learning first before I can jump into it, so I was going with what I thought would be safest. I'll try it at one point or another because I know you are right, I am just afraid that I am going to screw it up.
Technically speaking, you want to keep everything in one database especially if they are going to serve one application. Having multiple databases and cross joining them can actually yield adverse negative results. However, I understand what you are trying to do, and I understand your concerns with it, but what you are trying to achieve on our shared hosting system is simply not feasible on our end because of how we have the SQL servers setup. The best advise I can tell you at this point is to test it out on your development box first, and perform some bench tests against it to weight out the performance.
I am going to consolidate everything into one database. Just curious though what did you mean by: How so? I would like to know about any pitfalls, I never had a problem doing this before in much larger databases, I am just curious about what you meant.
I'm referring to referential integrity. When you have one database and multiple tables you can define the relationships with them more appropriately. But if you have multiple tables from multiple databases and you set join statements, what's to stop someone making changes on one table thus causing an unforeseen consequences. Not to mention possible timeout issues between databases. The error you get on the application may not be descriptive or helpful enough because you are managing multiple databases but they are all suppose to act as one. Lets say you got SQL1 with tableA, that has a join statement or relationship to one of the columns on SQL2 tableB, but someone updates tableB, now your join statement may fail and you really don't know where to start looking. There are some situations where having multiple databases is of value, such as a database on its own for forms authentication, a separate database for storing sessions, and another database to store inputs from forms. All 3 act as one to serve the web application but all 3 are separate where as if one database fail, the other features may still function. And from my understanding what you are looking to do is break down the tables to different databases so that it can supposedly handle the process or amount of activity you are planning. Not really a good idea. Since all the tables have a integral relationship with each other.
I see what you are saying. There would only be a cross relationship between two databases in my plans at any given point, here is why. I have a utilities database that will: A. Contain two or three small lookup tables that all of the other tables always need to access, so I centralized it into one database so I don't make copies of the same thing. It is all updated in one place, like once or twice - static info. B. Give central access to UDFs. I hate replicating UDFs and USPs, so I like to centralize it into one Database when possible (harder to do with USP obviously - I try to avoid dynamic SQL when possible). But if i was joining all 6 databases, that would just be crazy, that isn't the case. The only reason I spread it out like that is because I came up with a developer's solution to a DBA's problem, plus I am afraid of indexes - mostly doing them wrong. I am not opposed to learning but I have a very small amount of time to get this done so I was doing the best thing I could do quickly. My strategy was to keep the tables small, small generally means quick look up/join times (given I am using primary key only). Again, it's okay though, I am going to junk that idea for now and put everything into one set of tables into one database and I will practice with indexes on my spare time. This is something I need to learn anyhow, just didn't know the best approach is all. Very cool though, thanks for the info I do appreciate your insight. Thanks, Eli
