| Author |
Topic |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2004-08-06 : 14:43:14
|
| I have 200+ single user monolithic MS Visual Foxpro relational databases out in the field. The application/database is called ChemWatch. They all have the same database schema with 42 tables. Each database represents a ship-to client from our ERP system. The size of these dynamic db's range from 50MB to 600MB.I want to import all of these databases into SQL Server in order to supply a web site with data. Queries and permissions will be granted based on the web user. Some users may have access to multiple databases, such as the CEO of a parent company. Queries will be made on individual databases, and also a combination of databases. My question is should I...1. Combine all of these ChemWatch databases into a single SQL Server databaseor 2. Should I create separate databases for each client?I started with option 1. For each table in the SQL Server db I added 2 columns, one for the ERP customer # and another Identity column for use with updates. Problems started when I attempted to make db diagrams for cascading updates/deletes. Since the relationships of the tables are based on 2 separate fields (Customer_No & original the primary key of the parent table involved) I don't even know if this is possible at this point.So now I'm thinking of having a separate database for each client. This will allow me to control access based on user logins, and I can also preserve the same relationship as in the original database. However that increases the complexity of the queries when I want to combine the data of multiple clients. Anyone have any suggestions?TIA |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-07 : 12:21:55
|
| Seperate database for each client, with a data warehouse that combines the data and aggregates. You could also have one database with partitioned tables and federated views. It sounds like the final database will be quite large. :) On option 1, you would need to redefine your primary keys. You should probably do this anyway, since it sounds like your clients would always be hitting the enterprise system by combination of key and customer_no anyway. It would be much faster to query and aggregate from that type of structure.I can look at it more after the weekend. I'm getting ready to go on a trip. :) Hopefully, some other people will have good feedback for you. Sounds like a fun project.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-09 : 09:45:24
|
| Well, I definately need it all in one database because someone my want to query for something from all databases. Such as "Show me how much Torrex (a chemical) that was used by each company."FYI this database won't me modified by anything other than the import process (at this point), so at this time I'm not too worried about setting up relations. However, I would like to invoke the protection.I like your idea on the warehouse, and am thinking that I may use replication to move each individual db to the warehouse. What do you think about that?Right now each table has a Unique Identity ID column (named SQL_ID) that is populated within the import process utilizing DTS. I am thinking that I could setup foreign keys in the child tables using this column from the parent, instead of a multiple column relation, which I don't think is possible. I foresee the update of the child table as something like this...UPDATE ChildTable SET ChildTable.Foreign_SQL_ID = Parent.SQL_ID WHERE (SELECT TOP 1 FROM Parent WHERE Parent.Customer_No = Child.Customer_No AND Parent.Primary_Key = Child.Foreign_Key)With this type of relation why should I setup and maintain multiple databases, except for the idea of not modifying the original data source? Any comments on that?Thanks for your response! |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-09 : 11:26:53
|
| I just read up on Partitioned Views and Federated databases. This sounds real interesting. A couple of questions:1. If I setup a separate database for each client, can it also maintain original relations for that database? Or, do I still need to setup the additional keys as described above?2. What's the advantages/disadvantages to using Partioned Views over Replication besides data storage? It would seem to me the processing time on a partioned view would be a lot longer than that on a single database.TIA again. |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-12 : 07:48:51
|
| Am I doing this wrong? How can I setup database relations with the above SQL_ID to Foreign_SQL_ID key, and also have cascade updates in effect for the Customer_No column? Do I need to create a composite key field which would be a combination of the Customer_No and original primary_key field? Wouldn't that be slower than using an Integer? Maybe I'm worrying about something that I shouldn't be worrying about since the Customer_No will not change?A bit confused this morning. Will someone straighten me out please?TIA |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-12 : 07:54:02
|
| My experience of partitioned views is that they are .. less than excellent when it comes to performing aggregate functions over them. If this is important to you then you may want to reconsider the design.-------Moo. :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-13 : 08:46:37
|
| When you say "partitioned views is that they are .. less than excellent" are you meaning even when you have all the "special dummy CONSTRAINT column which is part of the Primary Key" stuff set up?Ken, have you considered putting all the data in one database but with owners representing each "ship-to client" original database? Not sure if it gains you anything, just thought I'd mention it.I think the way the partitioned horizontal views work is one of those forehead-slapping "of course" moments, but.Kristen |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-13 : 09:19:37
|
| For now I have decided to go with all clients in one database. Each table has a Customer_No field which represents the Ship-To-Client. I have setup composite indexes consisting of the Customer_No and the original primary key for each table. I use these composite indexes in my database diagrams to enforce relational integrity.I am changing all of the original databases to contain an "Identity" column, so my import process will know whether to update a record or add it. The remote program will also keep track of deletions so I can execute these on the SQL Server DB.How can I setup "owners representing each "ship-to client"". Can User/Role security be based on the value of a column?Thanks Kristen et al. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-13 : 09:30:46
|
| A Changed Date would be handy (in addition to Identity) so you can do an UPDATE of only those rows that you know have changed - need to be sure that it gets changed (UPDATE TRIGGER would be best :-) and you need to be sure that individual people's PCs will have a valid forward-moving time ... :-(An UPDATE statement with a massive "pessimistic" (or is that Optimistic, I never know whether the glass is half full, or completely empty!) WHERE clause will reduce the number of actual UPDATEs that occur, but it will still take ages to run on a 600MB database, compared to having some sort of marker on the records that have changed. I suppose a sinple bit column that is set to 1 on INSERT/UPDATE and set to 0 when the data is extracted to "head office" would do the trick.KristenKristen |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-13 : 09:36:40
|
| Yeah, I failed to mention that there is a datetime update column in each row of the VFP source db. Nice thing is that I am going to have the same RI on SQL DB as VFP DB so I do not have import child tables updates when the updates are generated by RI.Can you answer: How can I setup "owners representing each "ship-to client"". Can User/Role security be based on the value of a column?TIA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-13 : 17:08:28
|
| Each table has a Customer_No field which represents the Ship-To-Client. I have setup composite indexes consisting of the Customer_No and the original primary key for each table. I use these composite indexes in my database diagrams to enforce relational integrity.Why??? Do you really need it on every table? Remember, SQL Server is a relational database engine. Don't design it like a mainframe database.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 07:55:37
|
| "Can you answer: How can I setup "owners representing each "ship-to client"". Can User/Role security be based on the value of a column?"CREATE TABLE XXX.MyTable(...)[But I still think the idea is probably Pants, but it might be worth considering].Yes, user XXX can be granted rights to the table XXX.MyTable, and other users denied rights.I *think* if you saySELECT * FROM MyTableand you are logged in as XXX (more correctly Aliased as XXX, although it normally resolves to the same thing) then SQL will find XXX.MyTable for you BEFORE it discovers that there is a dbo.MyTable ... so you could control the whole thing just by LOGIN and Table Owners and STILL have all the data in one database for when you want to produce composite reports etc.But I think a single table, single database, approach will be better long term, albeit more development work short term.Kristen |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-16 : 03:17:50
|
quote:
Originally posted by Kristen
When you say "partitioned views is that they are .. less than excellent" are you meaning even when you have all the "special dummy CONSTRAINT column which is part of the Primary Key" stuff set up?
Yep. Even when set up with the constraint that fixes the max/min values for the partitioning columns, the analyzer seemed to choose some horrendous plans for partitioned views v. the data in a table. Specifically GROUPed aggregates were low performing.-------Moo. :) |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-16 : 09:37:01
|
| Kristen: I understand your idea but don't like separate tables for each client. I think that would be a maintenance nightmare. I'm still wondering about setting up user/role permissions based on the value of a column. Has anybody done this if it is possible? If so, do you see much query degradation?Derrik: Actually there are 3 reasons behind my madness of adding customer_no to each table:1. I was hoping to setup user/role permissions based on the value of this column.2. Overall simplicity and speed of SQL queries.3. I tried to setup a relational db using SQL Database Diagrams according to a single "client" table that would relate to all of the other tables. Problem is that I need to have cascading updates on multiple colums in the child tables below the client table, and I couldn't figure out how to do that. Here's an example of 1 of the 10 or so relations in the current VFP database with primary keys...Tables & Primary Keys:Client Table - Customer_No - No other table contains this columnWashers Table - Washer_No - Dynamic keyWashRun Table - WR_ID - Static keyWashInj Table - WI_ID - Static keyWashChem Table - WI_ID & Chemical_SKU - Dynamic KeyChild to Parent Relations:The WashChem Table contains WI_ID of WashInjWashInj Table contains WR_ID of WashRunWashRun Table contains Washer_No of WasherSQL DB:Client Table - Customer_NoWashers Table - Washer_No & Customer_NoWashRun Table - WR_ID & Customer_No WashInj Table - WI_ID & Customer_NoWashChem Table - WI_ID & Chemical_SKU & Customer_NoNow in order have cascading updates in the database diagram for the WashRun table, wouldn't it have to contain a column to store Client.Customer_No in both the Washers and WashRun table?Thanks everyone for your help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-17 : 06:54:07
|
| "I understand your idea but don't like separate tables for each client"I agree, I reckon it stinks, but I'd keep it in your mind because it may turn out to solve plenty of other problems - assuming you need a quick fix, rather than being able to take you time developing a Neat Solution.User/Role based on Column could be handled by a VIEW (which could take into account one of the SUSER_NAME type System Thingies - assuming that each user is using a SQL Server Login [rather than a single application login])Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-17 : 07:18:36
|
| Server: Msg 137, Level 15, State 2, Procedure RptDocsOut, Line 34Now in order have cascading updates in the database diagram for the WashRun table, wouldn't it have to contain a column to store Client.Customer_No in both the Washers and WashRun table?No. You have the relationships you need to do a cascading delete without doing that. The Customer_No should only be in one of those tables (probably Washers) the way you currently have it set up. If you set up the cascading delete properly, it will work. What you are doing is just a bad idea from a normalization perspective.Think about it though. Let us know what you decide on. I'm just telling you what I would do from experience.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-17 : 09:46:52
|
| Thanks Kristen. I know that I could inject permissions into the the queries, but I was hoping that SQL Permissions would actually handle that. For instance if I were to login as CUST1004, and did a SELECT * FROM MyTable, I would only want the rows returned where MyTable.Customer_No contained 1004 based on SQL permission settings. Maybe this can't be done, but I thought I read somewhere that column based permissions could be used. BTW the problem with views is that they cannot be parameterized so I would probably go with a SPROC for the queries. However I would rather let SQL handle this permission internally. I will be looking into this & let you know of my findings. If anyone else knows how to limit access to rows based on a columns value for a SQL Login please say so! |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-08-17 : 10:11:52
|
| Thanks for the advice Derrick! I understand what you mean by normalization and being a MeanOldDBA I know how you don't like to see repetitive data. I don't either, but I am struggling with using DB diagrams to handle the cascading updates/deletes. I realize that I could write SPROC's to handle the RI but why reinvent the wheel? Call me lazy. It seems to me that the SQL DB Diagrams lack the ability to join tables like you would do in an Update statement. Right now I have the DB Diagram setup to do cascading updates/deletes using unique composite indexes of the following:Client to Washers - Customer_No Washers to Washrun - Customer_No & WasherWashrun to Washinj - Customer_No & WR_IDWashinj to Washchem - Customer_No & WI_IDNow let's say that this delete statement is executed:DELETE FROM WASHRUN WHERE Washrun.WR_ID = 15023 and WashRun.Customer_No = 1004With the above diagram only the washinj & washchem rows that relate to Washrun.WR_ID 15023 and Customer_No 1004 would be deleted. If I dropped the Customer_No from the diagram for Washinj snd Washchem then wouldn't it delete all rows where the WashRun.WR_ID is 15023 regardless of the Customer_No value?Am I missing something here? The only alternitive I see here is to create my own unique keys within the SQL Server DB at import time which I was inituially edoing, but that seemed like a waste of time and effort because I would still have to use composite keys to support cascading updates in addition to writing the code to create the keys!I really appreciate your time.Derrik - Thought I'd throw this at the top of the list in case you are missing it.Derrik - "" one more time. Then I'll leave you alone. |
|
|
|
MFlanagan
Starting Member
10 Posts |
Posted - 2007-03-27 : 14:37:36
|
I have a similar issue as the one discussed here. I currently have upwards of 1000 databases that are all identical with regards to schema. These 1000 databases are spread across 7 servers all running SQL 2005. I need the ability to create statistical reports based on data contained in all or subsets of all of the databases. We currently create joins across servers and databases but as you can well imagine, the performance is not the best  I am open to suggestions... We have considered consolidating all of the databases into one massive data reporting database but then we have the problem of upkeep. The reports need to be realtime. I am also concerned about the performace hit the production databases would experience if they had to update themselves and a reporting database everytime the data changed.Any suggestions??Thanks |
|
|
|
|
Setting up same db's on one server