May 15, 2012 at 2:26 pm
What we are doing?
We are developing a distributed service broker application that processes data on different databases residing on different servers. These service broker applications are deployed are interdependent. These service broker applications process business routines (TSQL statements) on receiving a message from another service broker application. Basically we are going to configure a load balancing configuration as mentioned your book. All the data generated as a result of processing service broker applications are saved locally on each server.
What we want to achieve?
Synchronize/transfer data to a single Master database. There may be 5-10 huge tables and may not be all the tables in the database.
Questions
1. Which is a more viable technology SSIS or replication and why?
2. Are there any other data transfer alternatives to SSIS or replication for the above mentioned scenario?
3. Also since Load balancing configuration is prettymuch a deterministic routing and not a actual load balancing is there a way to actually load balance the work load based on availability of server resources i.e. we are having servers with different configurations some are very high end server and other are mediocre servers.
Thanks a lot.
~ Jagadish
May 15, 2012 at 3:09 pm
jagadish1978 (5/15/2012)
Basically we are going to configure a load balancing configuration as mentioned your book.
Who's book?
All the data generated as a result of processing service broker applications are saved locally on each server.
What we want to achieve?
Synchronize/transfer data to a single Master database. There may be 5-10 huge tables and may not be all the tables in the database.
So, basically, Asynchronous Merge Replication?
Questions
1. Which is a more viable technology SSIS or replication and why?
2. Are there any other data transfer alternatives to SSIS or replication for the above mentioned scenario?
3. Also since Load balancing configuration is prettymuch a deterministic routing and not a actual load balancing is there a way to actually load balance the work load based on availability of server resources i.e. we are having servers with different configurations some are very high end server and other are mediocre servers.
You really want to look into MongoDB and the other shard-DBs that are out there if you don't require transactionally consistent data and are intending to balance like this. Another option is Oracle RAC if you've got gobs of money to throw at the problem. Yes, I know I'm just an anarchist, but SQL Server isn't always the best tool.
Service Broker is used for asynchronous load balancing, but not usually across multi-server when it's used that way. It's to keep a large volume of calculations/functions that need to get into the system eventually but can be delayed from taking over the entire system. Otherwise it's usually used to avoid synchronous update delays to a foreign server, an audit server for example. It's rarely used to fully load balance multiple relational databases to each other.
Merge replication is mostly used when you have salespeople on-site taking orders and the like. They take the orders, they come back to the office and they 'sync' with the main server. Again, not really a load balancing component.
SSIS would be useful if all DML came into a single source and you used a number of warehouse or mirror servers to do all of your reads from unless you're changing data in that transaction. In this case, Mirror/Snapshot would be as viable a solution. You really need SSIS when you're only using portions of the data or you want to do in-flight transforms or your sources/destinations are heterogeneous, the rest is just convenience. I'm not really sure how SSIS got into this discussion.
In general, you don't load balance SQL Server. You can use Service Broker to balance the internal load on the engine for specific tasks against data 'bursts', but you don't multi-server balance under most circumstances. It's not built for it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply