Data retrieval and Join operations with cluster db server

Question

If any database spreads across multiple servers (ex. Microsoft Sql Server), how can we do join or filter operations. In my scenario, if suppose:

1. A single table spreads across multiple servers how can we filter rows based on user input?
2. If master table is there on one db server and transaction table is at another db server, how can we do join operations?

Please let me know how can we achieve this and where can I get more details about this?

Answer 1

I think you're confused about SQL clustering - it doesn't allow you to split tables across multiple servers any differently than you would put different tables in different databases on the same server. Clustering is used for hot failover and redundancy.

However, I think I see what you're asking - if you want to split a database up between different physical servers, the easiest thing to do might be to have a VIEW that unifies those tables together in one place, and then you can query and filter that. SQL Server is smart enough (as long as there are indexes and statistics in place to make the decision from) to send the query where it needs to go if you select something from the unifying view.

For example, say you have two servers - SERVER1 and SERVER2 that both have a database - DATABASE - and each server has a table - TABLE - that has half the data in it (between the two servers, you have every row). Just create a view somewhere - either server, or somewhere else entirely - that looks like this, and then add Linked Servers for SERVER1 and SERVER2 that allow SQL Server to get the data from the remote location:

CREATE VIEW SomeView
    AS
SELECT *
  FROM SERVER1.DATABASE..TABLE
 UNION
   ALL
SELECT *
  FROM SERVER2.DATABASE..TABLE

That way, you have one place to query and you'll always grab the data from whatever server it's on, instead of querying each server by itself. You can do this even if you don't want to split up individual tables - just create a view for each table you want to move, and have the view check whatever server the table is actually located on.

If I've missed your actual question, please leave a comment and some clarification and I'll be happy to add some more detail.

Answer 2

Look up 'Federated database servers' and 'Distributed partitioned views'. The example in BOL demonstrates splitting a Customer table across multiple servers:

-- On Server1:
CREATE TABLE Customers_33
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 1 AND 32999),
  ... -- Additional column definitions)

-- On Server2:
CREATE TABLE Customers_66
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 33000 AND 65999),
  ... -- Additional column definitions)

-- On Server3:
CREATE TABLE Customers_99
  (CustomerID   INTEGER PRIMARY KEY
                CHECK (CustomerID BETWEEN 66000 AND 99999),
  ... -- Additional column definitions)

A view is created on each member server as rwmnau describes:

CREATE VIEW Customers AS
   SELECT * FROM CompanyDatabase.TableOwner.Customers_33
UNION ALL
   SELECT * FROM Server2.CompanyDatabase.TableOwner.Customers_66
UNION ALL
   SELECT * FROM Server3.CompanyDatabase.TableOwner.Customers_99