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 |
|
DNF98
Starting Member
12 Posts |
 Posted - 2007-01-30 : 09:32:40
|
| All the reading I've done so far indicates that federated servers with distributed partioned views is useful for large databases. While I am not working with a large database, I do need a solution for segregating data across multiple databases and instances of SQL Server 2000. We have a client that requires their data to be in their own database. Our other clients, so far, are okay with being in a shared database. We will be running two instances of SQL Server 2000 on the same server - one will be for the new client, one for our other clients. The tables in the two databases will mirror each other in structure. There are several places in our intranet where we will need to pull data from both databases in one query. So, providing we have adequate hardware and we utilize federated servers with distributed partitioned views, my question is about performance issues. How big of a performance hit can we expect going this route? If you know of any articles online regarding this topic or have had experience with federated servers, your help would be greatly appreciated. Thanks! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-01-31 : 05:13:18
|
by the lack of responses i'm guessing that not many people use thosei'm also one of them but here's a MSDN article about it:http://msdn2.microsoft.com/en-us/library/aa177979(SQL.80).aspxGo with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 05:20:37
|
| Do you need to "merge" data from multiple clients (seems unlikely) or do you mean that you have some tables that are fundamentally shared between all the client's - such as a Country Code lookup table?If its the later:I would move all the "Code tables" to a different database, and then change the application to reference those tables from the "central codes database" instead.Alternatively, I would put VIEWs in each database referring to the Central Codes Database tables. The application would not need to change. There may be issues with INSERT/UPDATE if you do this (probably fixable with INSTEAD OF TRIGGERS on the VIEWS), but perhaps the central "code tables" are not allowed to be updated by the clients anyway?I see a "federated server" approach as the solution to the problem "We have loads of transactions / divisions and we need to split the volume of data across multiple databases / servers"; I don't really see it as "We have some customers / divisions who have security worries about the visibility of their data". But either solution works for either problem, just my gut feeling is that there is a better-way-round, and a worse-way-round, matching Problem & Solution (assuming you still understand what the heck I'm typing here!!)Kristen |
|
|
|
|
|
|
|
|
Federated Servers - SQL Server 2000