February 5, 2009 at 2:54 pm
Hi ,
I our current enviroment we have multipal database with same scham on different servers
what I need is to create one master look up database to populate common data.
what is best way to do this?
currently we have arroung 20 GB of common data in in all database
Thanks
February 5, 2009 at 2:57 pm
Not entirely clear on what you mean.
If you have one database that needs to exist on multiple servers, you can use replication or log shipping to keep them in synch. Is that what you mean?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 3:03 pm
Let me explain scnerio
we have a 10 prod servers with having single prod database having common schma
in all databases we have a common data of roughly 20 GB
what I wants to do is create one database on new server with common data and have all 10 server look this database for common info
that will save me populating common data on daily bases in all 10 database.
this way I can save some time and space in my prod enviroment
tahnks
February 5, 2009 at 3:11 pm
One solution, probably a good one, in that case would be to set up a single master database with the common data (as you proposed), then have the other copies have synonyms to a link to that server instead of having the actual tables.
I think you could do that and get what you need.
Do realize, though, that having the data on a linked server will be slower than having it on each server. Less maintenance and less overall disk space, but more network traffic and slower queries. It's a tradeoff.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2009 at 3:36 pm
Thanks for reply
Looks like batter solution
also one more question
how can we maintain refrential intrigtity between common data to use in my production data
Thanks
February 5, 2009 at 3:40 pm
If you take GSquared's approach, all the common data will be in one single database. There should be no issue in referential integrity.
February 6, 2009 at 6:58 am
You can maintain referential integrity through your own code in whatever manner suits your requirements the best. You can't have foreign keys that reference a different database, whether on the same server or not.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2009 at 3:04 pm
Thanks For prompt reply
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply