Load Balancing Using Service Broker
Jun 29, 2006
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
View 1 Replies
ADVERTISEMENT
Sep 10, 2007
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
View 3 Replies
View Related
Feb 27, 2006
Hello,
How is load balancing working in SQL Server 2000. We want to have two different servers as a back end for our web based eCommerce application. Is this possible? While there be any modification requirement in the application?
View 4 Replies
View Related
Nov 6, 2000
What is the meaning of Loadbalancing and how can I use this term to my advantage.
Q2. How would I know that I need to do load balancing and how can I set it up.
I have wrote load balancing in BOL and did not get any thing on it. can anyone refer me to where I can read more about it.
thanks
Ahmed
View 1 Replies
View Related
Dec 14, 1999
Hi All,
We have a Intranet Application which uses IIS / SQL Server 7.0, we are trying to support the application for 30,000 + users.
Since SQL Server can only support upto 32676 user connections (Theoretically), is it possible achieve this with clustering / dynamic load balancing. Does MSCS and SQL 7.0 support this. Any other ideas, 3rd party software etc?????
Any input will be of great help.
Thanks
NK
View 1 Replies
View Related
Jul 20, 2005
How much traffic/load can a database server running MS SQL server takebefore it can't handle it anymore? And when that time comes, what are therecourses? Am I able to load balance it between separate servers?
View 3 Replies
View Related
Sep 9, 2007
need advice for below scenario
currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.
incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.
with regards
alby peter
View 4 Replies
View Related
Apr 18, 2008
I want to know about Data Base Load balancing work. How to do that? I want to know that.
Please help me........
Reagrds
Binod Kumar Tiwari
+91 9958680044
View 1 Replies
View Related
Aug 14, 2007
Why SQL Server 2005 Dosn't Supoort Load Balancing?
I think Load Balancing is a feature of great necessity.
Would it be probably added to other features in Next versions?
thank every one
View 5 Replies
View Related
Jan 25, 2006
HiOn our SQl Server (2000) we can have queries that take at least 10-20mins(require full table scan fo billion row table). When one of these queriesis running it substantailly slows down very quick queries (sub secondqueries take several seconds).I believe there is no way to set priorities but was wondering if thereare other configuration settings that could help. The server is dualprocessor so maybe setting maxdop to 1 will help. Memory size isdynmaically assigned up to 4Gb but as the DB size is > 1Tb I'm not sureif allowing this much memory is actually decreasing performance when thequick query trys to get through.Any suggestions.ThanksMike
View 8 Replies
View Related
Nov 30, 2007
need the concpet of load balancing cluster ?can any one help me out with any link....?
View 1 Replies
View Related
Jul 20, 2005
When a web application becomes overloaded with traffic, one can offload itby load balancing and clustering the front end web servers. What happenswhen the back-end MSSQL database becomes overloaded? Does MYSQL offer loadbalancing and clustering?
View 3 Replies
View Related
Apr 27, 2007
Hi guys, can I know whether 2 servers (load balancing) able to set up the mirroring on another server? From what I know, mirroring only can set 1 IP address for principal. I just want to double confirm on it. Thanks for any assistance here.
Best Regards,
Hans
View 1 Replies
View Related
Apr 10, 2006
Hi,
We have a massive database that users run complex queries on. The queries are never the same so caching cannot be used too much effect. When many queries are run at one time by the various users the system slows down.
What we are trying to achieve is maybe have a primary SQL Server 2005 machine and multiple (4-5) SQL Server 2005 secondary machines. The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query.
We are not worried about failover and clustering and all that fun stuff. Our primary concern is to achieve the quickest execution of the query. We were wondering if SQL Server 2005 has any feature that enables query load balancing of some kind.
Thanks
View 1 Replies
View Related
Nov 3, 2006
Is it compatible to set up SQL DB mirroring on 2
different physical servers (High availbility + FULL transaction safety +
Automatic failover with a third server as witness) and simultaneously set up
network load balancing between these 2 servers (option with Windows Server 2003
Enterprise) ?
If yes, which type of licenses do I need and how
many (for SQL server 2005 and Windows Server 2003) with such a configuration
?
- 4 physical servers in total : A, B, C and
D
- load balancing between A and B (same application)
- load balancing between C and D (same application)
- A hosts principal DB X, B hosts mirror of DB
X
- C hosts principal DB Y, D hosts mirror of DB
Y
- D is witness for A and B
- B is witness for C and D
- A and B : only internal clients <
25
- C and D : both internal and external clients
(internal clients <25, number of external clients is unknown (>25)
)
Hoping this description will be helpful enough for
you...
(I am looking for a very high availability
system)
Thank you in advance for your support.
This licensing question is pretty much urgent (for
a bid) : a quick answer would be very appreciated...
View 7 Replies
View Related
Jun 7, 2005
Hi!Currently we only have one SQL Server database in our production system. I would like to add one more SQL server 2000 database. I would like to configure them so that both server share the load and Failover. I did some research and I found that I can do this by installing the OS (Windows 2000 Server Enterprise Edition) and SQL 2000 Enterprise Edition on both machines using the windows clustering. I want both servers to be active. They both have a copy of the production data. What I don't know is that, if it is possible to synchronize the data on both databases using SQL Server Replication utitlites. From what I know one SQL Server must be Publisher and one Subscriber. Can one sql server be both? Because I want both sql server to be identical. Can I set up replication between more than 2 servers? We just need to add one server for now but I would like it to be expandable. In the future we may need more.So Please provide me some ideas and answers about the following.1- Can two SQL Server cross replicate (both update each other in order to be identical)?2 -Does replication work beyond 2 servers.?3- If you were to set up a production database; what do you recommend considering the load balancing and Failover using Windows 2000 Server Enterprise Edition and SQL Sever 20000 Enterprise Edition?Thanks,Michael
View 3 Replies
View Related
Oct 15, 2007
We are looking at expanding our web application (C# and Sql Server). The module we are going to be adding will be far more processor intense (plotting and modeling) than any of the current code.
I need to know what the best design for this add on would be.
Would it be better to
a) Keep the new module as part of the core db (entire application on 1 database) and use loading balncing between 2 (or more) servers to handle the huge increase in demand?
b) Create the new module as a seperate database that runs on a seperate server?
If B is the best option (I am thinking it is not) is there a way to write easy and efficient queries and views of the 2 databases as 1.
Thanks
Justin
Justin Bezanson
www.justinbezanson.com
www.geekdaily.net - web development and technology blog and news
www.offsidegames.com - free online flash games
www.thrufare.com - free proxy website
View 4 Replies
View Related
Nov 6, 2006
i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
View 3 Replies
View Related
Feb 16, 2008
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View 1 Replies
View Related
Nov 8, 2001
Hey guys,
Lately we have come across a problem where our application is undergoing some extreme load against the SQL 2000 server database we have setup, where the server is hitting 100% CPU utilization each time. Currently the box is a 2 processor box.
Here is the question I have. I have seen under most SQL Server clusters that an active/passive setup is implemented. Where the passive server just exists as a failover mechanism. What I am looking for is some information on how to setup active/active setup where each server receives processes to handle.
Has anyone created a setup like this? Are there any standard benchmarking tools that can be used to see how this configuration increases performance? Is this setup more favorable than going to a 4 processor server as oppossed to our current 2 processor server?
BTW: We have noticed that after a web application where the user sits idle for a while the SQL Server application loses the connection with the application user...Is this the SQL Timeout causing the connection to disconnect?
Thanx for any response...
Bri
View 1 Replies
View Related
Jun 13, 2014
Is there such a thing called 'Load balancing' on fail over cluster?
View 2 Replies
View Related
Jun 14, 2007
Hello.
I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:
I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:
1) How can I revert back to SQL1 once it is ready?
2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?
3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.
4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?
Another setup:
We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:
1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?
2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?
3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.
I read so much topics about this, but the more I research, the more I get confuse.
Any suggestions, comments, advice is greatly appreciated!
View 6 Replies
View Related
Mar 22, 2006
You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.
We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.
We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.
But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.
The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.
So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.
I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.
I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.
We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?
Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.
Any advice?
many thanks
Steve
View 4 Replies
View Related
Aug 28, 2014
We are having a conversation at work and the subject of load balancing with SQL came up. Right now we are running SQL Server 2014 on four (4) machines. I am using a AlwaysOn with Availability Groups (AG). Now I know that we can scale out the reading in AG by allowing the secondary serves to receive reads.
Is there a way to be able to do this with writes? Can I have in essences 2 masters that some how reconcile with each other? We are expecting a huge amount of writes in the near future and we need a way for SQL to handle the amount of traffic we are expecting with out any issues.
I explored the possibility of Peer - to - Peer replication; however, it seems that it would be more work if we are constantly making updates to the database scheme.
View 2 Replies
View Related
Jul 7, 2007
I have posted this also in one forum. Somebody might have idea here.
We have four Windows 2003 advance servers with SP 3 configured on NLB.
Each of them has one NIC.
Recently we started to get frequent time-out error messages from web applications on those servers when they try to establish the connection with the SQL 2005. If you run web pages application to connect to sql server 10 times, 9 times you will get good connection results and 1 time you will get time-out.
Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
/common/mypage.asp, line 20
Connection to sql server is established with the connection string. Any idea how to solve this problem? Is there a connectionproblem? from NLB to Web to SQL?
We have tried doing some suggestions (e.g. LMHOST, IP, domain account for WEb to SQL connection, the Query Wait in SQL advance property.), but seems this timeout expired still exist
Thanks in advance...
View 3 Replies
View Related
Apr 5, 2007
Hi,
I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.
I look forward to some responses.
Kind regards,
Neeva
View 2 Replies
View Related
Mar 30, 2007
I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....
CREATE PROCEDURE [usp_ProcessMessage]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body AS VARBINARY(MAX)
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP (1)
@conversation_handle = conversation_handle,
@message_body = message_body
FROM [tcp://mydomain.com/INITIATE/MyQueue]
), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
END CONVERSATION @conversation_handle
IF @message_body IS NOT NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]
WITH ENCRYPTION = ON, LIFETIME = 600;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]
(@message_body);
END
COMMIT;
END
END
GO
My endpoints are created like so:
CREATE ENDPOINT MyEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)
GO
GRANT CONNECT TO CertOwner
GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner
GO
And my routes like so:
GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner
GO
CREATE REMOTE SERVICE BINDING [MyCertificateBinding]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
WITH USER = CertOwner,
ANONYMOUS=OFF
CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]
WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',
BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
ADDRESS = N'TCP://xxx.xx.xx.xx:4022'
GO
View 10 Replies
View Related
Sep 26, 2007
I am doing some research to see if the Service Broker technology would help my company with our Enterprise application. Here is our scenario: We have a 3 tier system. The first tier needs to contact the second tier asynchronously. Hence, using queues is a good option. However, the process that needs to happen on the second tier is mostly process intensive with little database updates. Is it still worth our time to use Service Broker?
I like the concept of Activation that Service Broker provides. But, from what I am reading most of the documentation describes activation as a way to call another stored proc. I definitely dont' want to do any process intensive work on the SQL server. So here comes my question...
How would I use a windows service to listen to the activation event from the Service Broker. I could have multiple windows services watching the same queue (scalable). Would I have to handle collisions myself? If so, I think I would rather keep it simple, and just use a simple table as my queue.
Thanks for your comments in advance...
Vijay.
View 3 Replies
View Related
Apr 5, 2008
Hi to all, I want to study Sql server Service broker, have some questions1. What is the use of service broker ?2. Where this will use ? (With example)3. How to enable Service broker? Because i have sql server 2005 version but no folder like service broker.
View 2 Replies
View Related
Aug 29, 2006
Im having a hard time understanding everything required to create a simple Service Broker example. Can someone please assist? Source code would be ideal, but if not "do this, do that" would even be helpful.
Thanks.
View 1 Replies
View Related
Sep 8, 2006
I am trying to implement service broker. I send a message from my application code to the database to execute a specific stored procedure. How do i return the result set obtained by the execution of the stored procedure to the application.
View 4 Replies
View Related
May 16, 2006
My service broker seems to be broken... The database was restored from another crashed server but i have tried the
ALTER AUTHORIZATION ON DATABASE::[SPYDERONTHEWEB] TO [SA];
The error i'm getting is
Service Broker needs to access the master key in the database 'SpyderOnTheWeb'. Error code 25. The master key has to exist and th service master key encryption is required.
Error: 28054, Severity 11, State: 1.
View 4 Replies
View Related
Sep 26, 2006
Hi
It will be great to have an update on MS plans to solve the problem of using
Service Broker for remote users who sit behind the NAT.
Any news will be appreciated.
Leonid.
View 1 Replies
View Related
