Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rquigley1
Starting Member
6 Posts |
 Posted - 2004-11-17 : 18:40:37
|
| I am designing a database solution in sql server 2000 that will have a sperate database for each client. However the data in each database must be compared to one another. I was going to design it in such a way that there is a "master" db that stores all of the client db names and uses distributed views to compare data in each of the client databases. There are several issues here that are of concern. Will there be a problem if there are a lot of client databases in one instance of sql server? Are there performance issues using distributed views in this way if they lie on different servers. Would it be better to replicate the data into another single large database that stores all of the data instead of using views to access data amongst all client db's. The general concern is scalability. How do I set up a system for a potentially large amount of clients that each have their own database and still be able to compare data. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-17 : 18:47:50
|
| We have a system designed in this manner except it doesn't use distributed views. We have no reason to compare the data between the client databases. Anyway, distributed views should not give you performance problems as they are smart enough to not hit a particular server if it doesn't need to. If you aren't going to put the client databases onto other instances or other physical servers, then what's the point of the multiple clients?Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-18 : 19:20:10
|
| >> Are there performance issues using distributed views in this way if they lie on different servers.Yes - comparing data across servers will be a lot slower as it has to transfer the data to one of the servers for the compare. Make sure that it doesn't transfer a whole table needlessly.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
database design issue