|
Yes, it is possible to split a database into two databases - but it
takes planning. In essence you have two separate databases, each with
its own schema and storage. The applications must know that they are
accessing two separate databases. There is nothing automatic in SQL
Server that allows you to take one database and make it into two. That
requires human intervention. It will require more time to manage,
because you are managing two databases instead of one. The applications
must ensure the validity of data between the two databases. Generally,
this implies that there is likely to be some replication of data
elements between the two databases. You will have to code updates
to ensure valid data updates between the two systems.
You can potentially have an application that runs faster by running
against two databases; but, you could also end up with an application
that runs slower. Databases generally are able to optimize some
queries made against them. By splitting the data between two databases,
you are now the one that determines how to optimize queries between
the databases. Knowledge of how the data is accessed is critical to
process it effectively.
Personally, the only time I would implement dual SQL servers in a
cluster would be if there were two separate applications, each accessing
its own database. I think you would end up with a better performing
system, and one that requires significant less management, if you do
not split the data of a single database into two databases. The only
type of environment that I can think of off the top of my head where it
might make sense to split the data is one where there exist a number of
validation tables that are only read. These could be split to another
database on the other node and not impact the management issues too
greatly.
I'll end my ramblings.
tgc
|