using multiple schemes in a clustered sql server 2008 Database

Question

I have a SQL server 2008 R2 database with 2 schemas, i need to enhance the performance of the server so we plan to cluster the DB, we will have 2 nodes . is it possible to separate each scheme so each node will have one schema

Answer 1

No. Simple like that. The unit of separation is a database, not a schema. And clusters are active/passive.

Answer 2

You can build an active/active cluster, which with SQL Server basically means you have two separate SQL instances with it's own name an virtual IP address. Each instance can hold one of your schemas.

Some caveats to keep in mind:

• Note you need to fully license both cluster nodes in this scenario.<
• You need to have enough shared storage for the two dedicated SQL instances as LUNs can't be active on more than one node.
• You can only use half the memory on each node, in case of box failure you'd need to run both instances on one node.

There are good guides on the net how to configure this, it's a bit more complex than a normal active/passive cluster.