Calling all GURUS ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

[Ziggy]

SQL Server Load Balancing -- Any ideas?

There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.

Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?

Will this work? If not, why not?
What will work? Will federated servers work for an entire database??

[Karl]

Hi,

in response to your first question. You can use merge replication and manage the transactions via MSMQ. I've worked in an environment where this is exactly what was been done.

The client connects to the n-tier and the n-tier then redirects that client to one of the SQL Servers.

Strictly speaking, this is probably the only way that you are going to get "true" load balancing. "True" in the sense that you are physically balancing the load between two servers.

You do have to offset this against the resources that are taken up due to replication. Are there a lot of transactions? If so then this is something you should consider. I would put the distribution database on another server and let that handle some of the replication load. Then again, I'm a SQL Server guy, and I don't control the purse strings so I always recommend stuff without thinking about the financial constraints. In your case this might be a factor.

Also, you might want to consider transactional replication with immediate-updating subscribers instead of merge. It depends on what your scenario is as to whether you need this but it delivers slightly closer to real time replication than merge. Again, this might or might not be a considering factor in your case. When I did it we were using SQL Server 7 and merge replication. With SQL 2000 though you can queue transactions on a subcriber that has immediate-updating subcribers turned on. So it makes tran replication a viable alternative.

Federated servers can work but you won't get true load balancing. With federated servers you can use distributed partitioned views. However this only means that you are balancing the data across different servers. You are not balancing system resources. It also depends very much on the nature of the data you have. You might not be able to horizontally partition your views.

If you can here are a couple of things to bear in mind. Typically, this is something that you should consider during the design of the system so if the database already exists you might find yourself doing a lot of work changing things around and making it possible for the views to be created. Just because the data is partitioned doesn't mean that you will get major performance benefits. For example, say you partition a table over months of the year (i.e. Jan, Feb,Mar,....,Dec). And lets say that each partition is on a different server. If most user queries are based on the current month then all of the user queries will be hitting one server whilst the other servers are standing still doing nothing. In this scenario you have gained nothing. Ok, you will have reduced the volume of data that has to be searched queried by one twelth but that one server will be hammered.

The other thing you have to bear in mind with this method is that you might have to partition several tables depending on what kind of queries are performance critical. Again, this may or may not be feasible in your situation.

This method could work but it really does need a lot of thought and design and planning and it could take a while setting up. Not that the first method is easy. They both require planning but on balance using replication is perhaps more manageable plus it gives you load balancing across system resources whereas federated servers will only load balance the data.

Both options are worth considering and it all depends on your specific situation on which one is viable.

Hope that helps,

Karl


------------
Ziggy at 5/19/2002 3:13:07 AM


SQL Server Load Balancing -- Any ideas?

There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.

Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?

Will this work? If not, why not?
What will work? Will federated servers work for an entire database??