Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distributed Service Broker questions Expand / Collapse
Author
Message
Posted Tuesday, May 15, 2012 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 5:46 AM
Points: 2, Visits: 30
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
Post #1300592
Posted Tuesday, May 15, 2012 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1300626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse