3

We have multiple MSSQL 2005 servers that contain silos of information. We want our database developers to be able to join across these silos in the most transparent and performant way possible. The databases vary in size, but average in the tens of millions of rows, and are updated frequently. The servers are physically co-located.

I want to be able to create a sort of "single logical database" from many different server instances.

Linked servers are the obvious choice, but I'm worried about performance and tight-coupling of a query to a particular server. Am I too worried? Do you have experiences to support this solution?

Other options I'm weighing are using replication, SSIS packages, service broker, or physically attaching more databases to one of our larger machines.

Also, I'm wondering if there are other vendors that provide a solution to this problem?

Jeff Meatball Yang
  • 233
  • 2
  • 7
  • 1
    Sounds like you need a data-warehouse, so you aren't talking to your transactional databases. – Nate Dec 30 '09 at 17:40
  • Each silo is already a data warehouse in a sense. Now the question is, how can we analyze the data across silos? (imagine a silo is a department in a company: T&E, marketing, manufacturing... and I want to get the total cost per unit across all silos) – Jeff Meatball Yang Dec 30 '09 at 17:45
  • As far as I know, this explicit feature only exists for DB2 (and somewhat for Oracle databases). With SQL Server you're stuck with some sort of emulation. Maybe you can even try exporting relevant views, and importing them to a central server. – blueberryfields Dec 30 '09 at 19:05

4 Answers4

1

There is no solution from SQL Server that allows for 'transparent' data access. Querying across many servers requires Distributed Queries (ie. linked servers) and having a solution deployed with hard coded queries against linked servers across many instances will quickly hit the ground, for reasons of availability and maintainability. Is surprising how mobile databases really are: server change names, failover happens, databases are moved for reasons of load balancing. Given the tight coupled nature of DQ this doesn't really work.

Other big deployments approached this by going SOA and relying on messaging, see MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Data. This implies a serious paradigm shift in programming and how one approaches the solution, existing apps that use T-SQL queries will not magically transparently work on a 'farm' of servers.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
0

I don't know of anything like you describe for SQL Server, either from Microsoft or a third-party. Linked servers will allow you to create cross-server queries, but they're anything but transparent. I think the closest you might be able to come is to use replication or SSIS to aggregate data from the various instances into a single data warehouse.

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12
0

You solution will lie in integrating the service broker into your app. it sounds like you might be interested in the Real-time Data Integration sample. You can also create views that span across sql server instances. depending on the type of joins you are trying to do and where you want the queries to run (distributed or centralized) one of these approaches (or both) should do what you need to do

Jim B
  • 23,938
  • 4
  • 35
  • 58
0

It sounds like you're looking for "Central Management Servers," a feature new to SQL 2008.

Here is the official documentanion: http://technet.microsoft.com/en-us/library/bb895144.aspx

Here is a blog with an example: http://www.mssqltips.com/sqlservertip/1767/execute-sql-server-query-on-multiple-servers-at-the-same-time/

Mike
  • 1,261
  • 5
  • 18
  • 31