Showing posts with label achieve. Show all posts
Showing posts with label achieve. Show all posts

Friday, March 30, 2012

Load Balancing using Service Broker

I am trying to achieve load balancing through service broker.

I have a 3 server setup:

server1:

Source Database where the requests are sent. This has two routes created on this to server 2 and 3 with the service broker instance id specified. The service broker matches both these routes to the destination databases on Server 2 & 3 and send the requests in a round robin fashion by itself.

server2 & 3: These servers have the destination databases. The databases are exactly same with same messages, contracts, queues, services, and servicing programs. These don't send the responce back and the source is also not expecting for any response from the target databases.

When I tested this scenario it was behaving in a different manner. If there are 20 requests, in a normal case 10 requests should go to either of the destiantion servers, but I found that sometimes it was 11-9 or 12-8 only once did it do a 10-10.

Also I want to know is it best practice to rely on the inbuilt feature of the service broker in matching the routes and sending the messages rather than doing it ourselves. I which case one has to write the logic for sending the message to appropriate target server based on the status of the destination queue.

I also want to know which would be the best technique to use to bring the two destination databases in a concurrent state.

thanks

ashok

The routes chosen are not round-robin, are random (based on a hash of conversation_id and conversation_id is a random guid). You will not get exactly 10-10, but on the long run the average should be 50%-50%.

The best practice is to let the routes do the load balancing. These assumes the services on server2&3 offer identical functionality. When you prefer a certain service, you enter into the realms of data depending routing, see http://rushi.desai.name/Blog/tabid/54/EntryID/38/Default.aspx and http://rushi.desai.name/Blog/tabid/54/EntryID/39/Default.aspx for a discussion of this topic. SSB does not offer data depending routing out of the box. My recommendation is to keep things simple and rely on the routes to do a 'dumb' load balancing.

About bringing the two back-end services in a concurrent state, the topic is a bit more complicated. Do you want all the data to be identical between the two? What you should have in mind is how to 'taxonomize' your data, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/dataoutsideinside.asp and other papers by Pat Helland. Lookup data can be stored on one central server and replicated to the N instances of the service (you have to plan for eventual growth by scale out of the service). Business transactions data can be kept partitioned on the server 2&3..&N instances. Some ETL process runs on each of these partitions and populates a central analisys server for reporting and analysis. Each service database in the server2&3...&N group can be individually mirrored for high availability and disaster recoverability.

HTH,
Remus

Monday, March 26, 2012

Load a text file with email addresses and compare against a database table that has email addres

Hello ALL

what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.

I also want to update some users using a different text file.

Please help me with the best way to do this

Thanks in advance

You'd use a data flow with a Flat File Source to read the text file, a Lookup to check whether the email address exists, and an OLE DB Command to issue the DELETE statement. Or you write the matching rows to a "temp" table using an OLE DB Destination, and use an Execute SQL task after the data flow to issue a batch DELETE statement. UPDATES can be done the same way.

Working through the tutorials in Books Online will introduce you to some of these concepts.

|||

jwelch

Could you please help may be with an example or something I will really appreciate that.

becuase I don't know what OLE DB command is.

so are you suggesting like so

text file connection

|

|

\/

lookup

|

|

\/

OLE DB COmmand (what is this?) (issue the delete or update command)

I want to be able to do delete from table A where A.email_address = text_file.email_address

Thanks for your help

|||

The OLE DB Command is one of the transforms in the toolbox in Visual Studio (same place you found the lookup).

The basic flow you have above is correct.

|||

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = ? and ? is set in the to param 0 to the value coming from the lookup.

Is that right?

|||

NewbietoSSIS wrote:

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = ? and ? is set in the to param 0 to the value coming from the lookup.

Is that right?

Looks like it.