How To Configure Filegroups On Multiple Machines - SQL 2005

Hi folks,

I am exploring various solutions to setting up a scalable database configuration. To do a proof of concept, I need to setup the database in such a way that the data is partitioned on 2 physical machines.

I looked at distributed partitioned views, which allowed me to create a table on databases residing on two different servers, and then I created distributed view (with appropriate range constraints) that supported distributed selects, joins, updates, and inserts. This seemed to be the solution we were looking for.

However, I ran into a major problem because of the fact that distributed partitioned views don't allow a table to maintain an Identity column with an identity seed.

I did more research and found the Partitioned Table concept that was introduced in SQL 2005. This partitioned table allows us to have a single table which is horizontally partitioned across multiple file groups. This table now is just like a regular table in that it supports the identity column and identity seeds and guarantees index integrity. We were successful in implementing the partitioned table on a single machine, with three filegroups.

Now I need to move these file groups to different physical machines. I have found some articles on adding file group on additional hard disks on the same machine - but nothing to setup a database that is comprised of multiple servers that each handle a part of the data partition.

Is it possible to create a database comprise of filegroups residing on different physical machines?

Thanks.