We are developing our business/data tier and one of the developers from another team who is going to be using our component asked if there was a way if the sql server was down when at attempt to fetch/save/delete, we could switch programatically to use one of the replicated sql servers?

To be honest, I am not familiar with failover and things like that. I've heard things about sql server clustering and replication, failover. It just doesn't seem 'right' to do this programmatically in the business tier. So, what do you guys think? Any thoughts or opinions would be great!

You can do this in code, but it's not that easy (i.e.: it requires some work). You can do this by opening the connection manually on an adapter, wait for the timeout and check the exception returned and then decide to connect to a failover server. You then can track the decision made in some static/threadsafe object which is consulted everytime the adapter's connection test is performed.

Though this isn't ideal. SQLServer has failover support which should be transparent, i.e.: the sqlserver code makes sure you connect to the right instance, as they operate as a black box for you.

Otis wrote:

You can do this in code, but it's not that easy (i.e.: it requires some work). You can do this by opening the connection manually on an adapter, wait for the timeout and check the exception returned and then decide to connect to a failover server. You then can track the decision made in some static/threadsafe object which is consulted everytime the adapter's connection test is performed.

Though this isn't ideal. SQLServer has failover support which should be transparent, i.e.: the sqlserver code makes sure you connect to the right instance, as they operate as a black box for you.

Thanks, I was pretty sure there was a better way, now I hope they buy into it.

MarcoP wrote:

It just doesn't seem 'right' to do this programmatically in the business tier.

I don't think the decision should be made in the business tier, but in an abstracted data services tier. While the Adapter itself could be considered a data services tier, I guess, I prefer to wrap the Adapter into a distinct physical tier. This pattern would probably be best for deciding which catalog/server will handle persistence.

Jeff...