SQL,tables in a DB: instances

Showing posts with label instances. Show all posts

Sunday, March 25, 2012

(local) name explanation please

In some SQL2K instances, I have (LOCAL) in others its (Local) and in
development code the connect to server parameter is always local.
Where is (local) associated to 127.0.0.1 or local server name?
If I have 2 or more instances of SQL Server on my maching which one would
(local) refer too?
TIA
Neil.
(local) or . refers to the local machine. The dot (.) or local tells the
client that the host is local and not remote, such that a local pipe
connection can be made. This avoids going on the network. This applied
more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
for local connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks for your response.
So this is pipe and not tcp/ip then. Can you point me to any books
on-line/help files that actually explain the local usage please as they must
be somewhere?
Also do you know why LOCAL is uppercase on some machines and lowercase on
others in EM I mean?
thanks
"Kevin McDonnell [MSFT]" wrote:

> (local) or . refers to the local machine. The dot (.) or local tells the
> client that the host is local and not remote, such that a local pipe
> connection can be made. This avoids going on the network. This applied
> more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
> for local connections.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Yes. local pipe is a pipe connection not a socket based connection.
We mention local pipes briefly in Books Online under the topic "Client and
Server Net-Libraries".
I've also seen it discussed in Network Programming books.
"Windows NT Network Programming" Davis.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

(local) name explanation please

In some SQL2K instances, I have (LOCAL) in others its (Local) and in
development code the connect to server parameter is always local.
Where is (local) associated to 127.0.0.1 or local server name?
If I have 2 or more instances of SQL Server on my maching which one would
(local) refer too?
TIA
Neil.(local) or . refers to the local machine. The dot (.) or local tells the
client that the host is local and not remote, such that a local pipe
connection can be made. This avoids going on the network. This applied
more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
for local connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for your response.
So this is pipe and not tcp/ip then. Can you point me to any books
on-line/help files that actually explain the local usage please as they must
be somewhere?
Also do you know why LOCAL is uppercase on some machines and lowercase on
others in EM I mean?
thanks
"Kevin McDonnell [MSFT]" wrote:

> (local) or . refers to the local machine. The dot (.) or local tells th
e
> client that the host is local and not remote, such that a local pipe
> connection can be made. This avoids going on the network. This applied
> more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
> for local connections.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Yes. local pipe is a pipe connection not a socket based connection.
We mention local pipes briefly in Books Online under the topic "Client and
Server Net-Libraries".
I've also seen it discussed in Network programming books.
"Windows NT Network Programming" Davis.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

Thursday, March 22, 2012

(2000) Unable to connect to debugger

On my development maching, I have three different SQL Server instances:

..\SQL2000
..\SQL2005EXPRESS
..\SQL2005

When i try to debug a stored procedure in Query Analyzer, I get the
following error:

"Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side components,
such as SQLLE.DLL, are installed and registered on MYPC. Ddebugging disable
for connection 53."

Client side components _are_ installed. What gives? Any ideas?

J. Jespersen
DenmarkJeppe Jespersen (jdj krlledims jdj punktum dk) writes:

Quote:

Originally Posted by

On my development maching, I have three different SQL Server instances:
>
.\SQL2000
.\SQL2005EXPRESS
.\SQL2005
>
When i try to debug a stored procedure in Query Analyzer, I get the
following error:
>
"Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to
debugger on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side
components, such as SQLLE.DLL, are installed and registered on MYPC.
Ddebugging disable for connection 53."
>
Client side components _are_ installed. What gives? Any ideas?

Which service pack of SQL 2000 are you running, and which OS do you
have?

If you are running Windows XP SP2, you should upgrade to SP4 of SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

(2000) Unable to connect to debugger

On my development maching, I have three different SQL Server instances:
.\SQL2000
.\SQL2005EXPRESS
.\SQL2005
When i try to debug a stored procedure in Query Analyzer, I get the
following error:
"Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect
to debugger
on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side components,
such as SQLLE.DLL, are installed and registered on MYPC. Ddebugging disable
for connection 53."
Client side components _are_ installed. What gives? Any ideas?
J. Jespersen
DenmarkThe files might be corrupted or you don't have permission for the program. T
o
check the permission check the sp_sdidebug.
"Jeppe Jespersen" wrote:

> On my development maching, I have three different SQL Server instances:
> ..\SQL2000
> ..\SQL2005EXPRESS
> ..\SQL2005
> When i try to debug a stored procedure in Query Analyzer, I get the
> following error:
> "Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to conne
ct to debugger
> on MYPC\SQL2000 (Error = 0x800401f3). Ensure that client-side components,
> such as SQLLE.DLL, are installed and registered on MYPC. Ddebugging disabl
e
> for connection 53."
> Client side components _are_ installed. What gives? Any ideas?
> J. Jespersen
> Denmark
>
>
>

Monday, March 19, 2012

"Setup has detected one or more instances of SQL Server 2000..."

Tried to install the CTP and got a message stating that 2008 won't coexist with 2000. I had an instance of 2000 Developer Edition on here that I was testing some stuff with. I removed that, rebooted, and I still get the same message. Am I to assume that you can't even install 2008 if you've got the 2000 client tools installed, or am I missing something else?
Have a look at section 2.1.1 of the readme file, it states "To work around this issue, manually remove the registry key at HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80, and then re-run SQL Server "Katmai" June CTP Setup." Note that this will likely break the 2000 tools so uninstall them first or better yet use VPC/VMWare to create an isolated environment. CTP's often don't play well with other versions and can be difficult to uninstall cleanly.|||Ah ha, the actual CTP ISO has an outdated readme that doesn't mention that part. I bet that'll do the trick. Thanks.
|||

Hi,

I also got the same warning message about side by side running of SQL2k and Katmai June CTP. But I could not find any note about SQL 2000 in the readme documents of the installation.

|||Yeah, the readme file that's included with the installation is slightly out of date. I think this is the one you need: http://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=6834

|||

Hi David,

Thanks for the link. Great help!

"Setup has detected one or more instances of SQL Server 2000..."

Hi,

I also got the same warning message about side by side running of SQL2k and Katmai June CTP. But I could not find any note about SQL 2000 in the readme documents of the installation.

|||

Hi David,

Thanks for the link. Great help!

Friday, March 16, 2012

"Schemas" in same database vs multple Sql Instances

We have a DataWarehouse project. We are considering using table names (in the same database) like this:

Acct.Payroll

...

Claims.Payroll

.....

Shipping.Payroll

...

where the "Schema" differentiates the "Payroll" tables. The tables have similar functions but are quite different internally. We expect these table to grow significantly over time.

Question: From a performance\ maximum database capacities, admin perspective what the pros/cons of each approach:

1. Schema approach

2. Multiple instances of sql (up to 16) on a single server

3.Seperate databases on the same server

TIA,

barkingdog

First, having different schemas is the simplest and "fastest" approach.

Second, would be seperate databases. There is very little overhead in accessing another database on the same server.

WAY, WAY down the list, about 412 on the performance scale, would be using seperate instances. Connections are very cpu and memory intensive as well as running the entire SQL server instance, etc. I would not recommend it for this application.

I would highly suggest using file groups and many hard drives to spread the usage in one database with multiple schemas.|||

If the assumed parts of you logical design are supposed to have some logical consistency between them, you would most probably need to use transactions that span over multiple functional partitions in your application. If so, then:

1. With multiple instances/databases you won't be able to use declarative referential integrity between the tables residing on different instances/databases.

2. With multiple instances you will be paying a much higher price of the full-blown 2-phase distribution transaction commit protocol for the transactions that span multiple instances. Although for multiple database the engine also uses a variation of a 2-phace commit protocol, but in fact it's very lightweight and does not actually incur a noticeable performance overhead.

3. With multiple instances/databases if the 2-phase coordinator becomes unavailable all the participating transaction on the other instances/databases that have prepared but have not receive the commit message will not able to make any progress and will need to wait until the coordinator comes back online. Quite often resolving situations like this requires manual intervention.

4. With multiple instances you won't have automatic deadlock detection - it might become a major administrative headache because you would need to 'detect' and 'resolve' the deadlocks manually.

5. With multiple instances/databases a sound backup strategy is more complicated to design and to implement.

Probably the only real benefit (besides somewhat higher availability if the setup is right) that one could get from going multi-instance is when a single machine, however powerful it is, is unable to handle the workload. If so then you really need a scale-out solution and in this case the disadvantages don't matter and you will have to pay the price.

Assuming that you properly partition your tables and/or design the placement of the tables in filegroups, the best possible option would be to use multiple schemas in the same database. Unless, of course, a single machine is unable to handle your workload.

Another possible exception is the case when certain database level options are not applicable to all partitions of your application, For instance – the Payroll would benefit from the row-level versioning, but the Claims for some reason wouldn’t. If you don’t want to pay the versioning overhead for the Claims then you would probably want to move it to a separate database on the same instance.

|||

From a raw performance perspective on a single database server, using schemas would be best. It also would be the easiest to develop and administer.

Using multiple databases in the same instance would perform fine, but would be a little more complicated, since your applications would need different connection strings. Backups/restores would be quicker.

With multiple instances on a single database server, the available RAM will be divided between all of the instances. You will also have to deal with different connection strings.

If you find that your single database server cannot handle the workload in the future, and you don't want to scale up, you would want to have these three tables on separate databases on separate servers to scale out instead.

Saturday, February 25, 2012

"certificate not found" for second receiver

I am trying to test load balancing between multiple broker service instances. I have set up one sender and two receivers. When I tried sending a lot of messages from the sender, I noticed that all messages were being received by receiver 1 alone. While I am able to communicate between sender and receiver 1, I am not able to send message to the second receiver (I stopped the first receiver instance to find this out). I receive the "certificate not found" error in the Profiler for the second receiver. The code for my second receiver is very similar to the first one.

I am dumping in the full code down here. I appreciate if someone can figure out what is wrong. Thanks

Sender:

use [master];
go

create master key encryption by password = 'masterhello1';
go

create certificate TrpCertServ1
with subject = 'TrpCertServer1',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 2
BACKUP CERTIFICATE TrpCertServ1
TO FILE = 'c:\amit\Official\Service Broker\certs\TrpCertServ1Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance
CREATE ENDPOINT SSB1
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4021
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ1,
ENCRYPTION = REQUIRED
);
go

USE master ;
GO

ALTER ENDPOINT SSB1
FOR SERVICE_BROKER ( MESSAGE_FORWARDING = ENABLED,
MESSAGE_FORWARD_SIZE = 10 ) ;
GO

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin1
with password = 'remserver@.1';
go

create user remconnuser1
from login remconnlogin1;
go

--grant connect to the endpoint to the login
grant connect on endpoint::ssb1 to remconnlogin1
go

--now is time to go over to server 2 and do similar tasks
--but first make sure that the cert you dumped out above can be
--available for server 2

--Step 2
-- you have now done the similar setup in server 2,
--and you should now do the final setup in master
--where you create a certificate from server 2's public
--cert and assigns it to the user created above
--uncomment from here to go and execute
create certificate TrpCertServ2Pub
authorization remconnuser1
from file = 'C:\amit\Official\Service Broker\certs\TrpCertServ2Pub.cer';
go

--adding for new receiver
create certificate TrpCertServ3Pub
authorization remconnuser1
from file = 'C:\amit\Official\Service Broker\certs\TrpCertServ3Pub.cer';
go
-- end of addition

use master;
go

--create the database
create database [rem_ssb1];
go

use [rem_ssb1];
go

--set master key
create master key
encryption by password = 'hellodb1';

create certificate DlgCertServ1Db1
with subject = 'DlgCertServ1Db1',
start_date = '06/01/2005'
active for begin_dialog = on;
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 2
BACKUP CERTIFICATE DlgCertServ1Db1
TO FILE = 'c:\amit\Official\Service Broker\certs\DlgCertServ1Db1Pub.cer';
go

--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser1
without login;
go

--Step 2
create certificate DlgCertServ2Db2Pub
authorization remdlguser1
from file = 'c:\amit\Official\Service Broker\certs\DlgCertServ2Db2Pub.cer';
go

-- adding content for new receiver
create certificate DlgCertServ3Db3Pub
authorization remdlguser1
from file = 'c:\amit\Official\Service Broker\certs\DlgCertServ3Db3Pub.cer';
go
-- end of addition

use [rem_ssb1];
go

-- we need two message types
CREATE MESSAGE TYPE [sendmsg]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [recmsg]
VALIDATION = WELL_FORMED_XML;
go

--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go

--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q1
with status = ON;
go

--we need a service
CREATE SERVICE [rem_s1]
on queue q1
(
[Ctract]
);
go

--create a route to the remote service, we know it'll be called rem_s2
create route [rem_s2_route]
with
service_name = 'rem_s2',
address = 'TCP://127.0.0.1:4022';
go
--drop route [rem_s2_route]

-- adding for new route
create route [rem_s3_route]
with
service_name = 'rem_s2',
address = 'TCP://127.0.0.1:4023';
go
-- end of addition

select * from sys.routes

--as we'll be doing encrypted dialogs we need a remote service bindin
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s2'
WITH USER = remdlguser1,
ANONYMOUS=Off

--give the user send rights on the service
grant send on service::rem_s1 to remdlguser1;
go
use [rem_ssb1];
go

-start the dialog and send a message
-uncomment from here until the following go statement and run
DECLARE @.h uniqueidentifier --conversation handle
DECLARE @.msg xml; --will hold the message

BEGIN DIALOG CONVERSATION @.h
FROM SERVICE rem_s1
TO SERVICE 'rem_s2'
ON CONTRACT [Ctract];

SET @.msg = '<hello00/>';
SEND ON CONVERSATION @.h
MESSAGE TYPE [sendmsg]
(@.msg);

RECEIVER 1:

--Step 1

use [master];
go

create master key encryption by password = 'masterhello2';
go

create certificate TrpCertServ2
with subject = 'Transport Certificate for Server2',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 1
BACKUP CERTIFICATE TrpCertServ2
TO FILE = 'c:\amit\Official\Service Broker\certs\2\TrpCertServ2Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance

CREATE ENDPOINT SSB2
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ2,
ENCRYPTION = REQUIRED
);
go

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin2
with password = 'pass1234$';
go

create user remconnuser2
from login remconnlogin2;
go

--grant connect on the endpoint to the login
grant connect on endpoint::SSB2 to remconnlogin2
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser2
from file = 'c:\amit\Official\Service Broker\certs\2\TrpCertServ1Pub.cer';

--go back to server 1 and step 2 in the script 1_setup_sec_master_server1.sql
--make sure the public cert 'TrpCertServ2Pub.cer' is available
--from server 1

use master;
go

--create the database
create database [rem_ssb2];
go

use [rem_ssb2];
go

--set master key
create master key
encryption by password = 'hellodb2';
go

create certificate DlgCertServ2Db2
with subject = 'DlgCertServ2Db2',
start_date = '06/01/2005'
active for begin_dialog = on;
go

--make sure the cert exist
select * from sys.certificates;
go

BACKUP CERTIFICATE DlgCertServ2Db2
TO FILE = 'c:\amit\Official\Service Broker\certs\2\DlgCertServ2Db2Pub.cer';
go

create user remdlguser2
without login;
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate DlgCertServ1Db1Pub
authorization remdlguser2
from file = 'c:\amit\Official\Service Broker\certs\2\DlgCertServ1Db1Pub.cer';
use [rem_ssb2];
go

-- we need two message types
CREATE MESSAGE TYPE [sendmsg]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [recmsg]
VALIDATION = WELL_FORMED_XML;
go

--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go

--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q2
with status = ON;
go

--we need a service
CREATE SERVICE [rem_s2]
on queue q2
(
[Ctract]
);
go

--create a route to the remote service, we know it'll be called rem_s1
create route [rem_s1_route]
with
service_name = 'rem_s1',
--broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083',
address = 'TCP://127.0.0.1:4021';
go

--as we'll be doing encrypted dialogs we need a remote service binding
--and the user is the user we created in the 2_setup_objects_server2_db.sql scripts
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s1'
WITH USER = remdlguser2,
ANONYMOUS=Off
go

--give the user send rights on the service
grant send on service::rem_s2 to remdlguser2;
go
SELECT * from q2;

RECEIVER 2:

use [master];
go

--make sure master had master key
create master key encryption by password = 'masterhello2';
go

create certificate TrpCertServ3
with subject = 'Transport Certificate for Server3',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

BACKUP CERTIFICATE TrpCertServ3
TO FILE = 'c:\amit\Official\Service Broker\certs\3\TrpCertServ3Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance

CREATE ENDPOINT SSB3
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4023
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ3,
ENCRYPTION = REQUIRED
);
go

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin3
with password = 'pass1234$';
go

create user remconnuser3
from login remconnlogin3;
go

--grant connect on the endpoint to the login
grant connect on endpoint::SSB3 to remconnlogin3
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser3
from file = 'c:\amit\Official\Service Broker\certs\3\TrpCertServ1Pub.cer';
use master;
go

--create the database
create database [rem_ssb3];
go

use [rem_ssb3];
go

--set master key
create master key
encryption by password = 'hellodb3';
go

create certificate DlgCertServ3Db3
with subject = 'DlgCertServ3Db3',
start_date = '06/01/2005'
active for begin_dialog = on;
go

--make sure the cert exist
select * from sys.certificates;
go

BACKUP CERTIFICATE DlgCertServ3Db3
TO FILE = 'c:\amit\Official\Service Broker\certs\3\DlgCertServ3Db3Pub.cer';
go

--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser3
without login;
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate DlgCertServ1Db1Pub
authorization remdlguser3
from file = 'c:\amit\Official\Service Broker\certs\3\DlgCertServ1Db1Pub.cer';

--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go

--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q2
with status = ON;
go

--we need a service
CREATE SERVICE [rem_s2]
on queue q2
(
[Ctract]
);
go

--give the user send rights on the service
grant send on service::rem_s2 to remdlguser3;
go

select * from q2

For load balancing scenario, the two target services have to be identical from the security point of view. That is, they must have the SAME private key. In your example, you are using separate private keys for each instance of the [rem_s2] service. You should export the certificate and private key from receiver1 and import it into receiver2.

Note that this applies only to the dialog certificates, for transport is OK what you're doing.

Another thing you have to account for is the 'broker instance'. The initiator must be able to distinguish between the two instances of the service [rem_s2]. For this, it needs explicit routes containing the broker instance in addition to the service name. The broker instance will be the value of 'service_broker_guid' in the sys.databases catalog. So you have to add two more routes in the initiator database (yes, you'll have 4 routes at the end):

create route [rem_s2_route]
with
service_name = 'rem_s2',
broker_instance = '....', -- service_broker_guid of [rem_ssb2]
address = 'TCP://127.0.0.1:4022';
go

create route [rem_s3_route]
with
service_name = 'rem_s2',
broker_instance = '....', -- service_broker_guid of [rem_ssb3]
address = 'TCP://127.0.0.1:4023';
go

When you begin a dialog w/o specifying an explicit broker isntance, the first message sent will choose randomly between the two route available (the ones w/o a broker instance). The first reply or message acknowledgement received from the target will carry with it the broker instance of the target service. Subsequent messages sent from the initiator now will be stamped with the target broker instance, and the routing mechanism now needs to deterministically choose the correct route (based on the broker instance), hence the need for these two more routes.

To test these two routes that are correct, begin a dialog and specify explicitly the desired broker instance (once for [rem_ssb2] and once for [rem_ssb3]). The message should deterministically go to the desired instance of the [rem_s2] service.

Note that each time you run the ALTER DATABASE ... SET NEW_BROKER the broker instance changes, you must the all route to it as well.

HTH,
~ Remus

|||

Fantastic response !!!

The configuration that I had initially (the ones in which the broker identifiers were not specified for the routes) was working as a failover configuration not really as a load-balancing configuration. Once the first route was chosen, all subsequent messages were sent to the same target service (as you had indicated in your response). The good part was that when I stopped the target service, currently receiving messages, all the other messages got routed to the second receiver service.

I then altered the routes created above, specifying broker identifiers (Note that I had only two routes at this stage, not four). This configuration worked as a load-balancing one. However, if we stopped any one of the receiver services midway, say receiver 1, messages destined for receiver 1 still piled up on the transmission_queue of the sender. This is where we realized to have 4 routes as you suggested.

However, when we created four routes, initially some messages were distributed across the receiver service instances (It may have been because the routes specified with broker identifiers were chosen initially). But, after that all messages still kept going to one particular receiver (It may have been because one of the routes specified with NO broker identifiers were chosen). Basically, we are looking for a load-balancing + failover configuration. We have been able to figure out how to get each of these individually, however we haven't found a way to do both together. Can you suggest how could we achieve this?

Regards,

Amit

|||

An individual dialog cannot failover between different databases. Dialogs are sticky, the database contains persited information about them (sys.conversation_endpoints). Once the first message was acknowledged by one database (instance of a service), subsequent messages can be received only by that database.

But there are good news. When you are looking for a solution for high availability of service [rem_s2] on one of the databases, you are basically looking for a solution for high availability of the database itself. If you can make the database available, you are making the service available too. And SQL Server has solutions for database availability. You can use clustered servers or database mirroring. The later is especially atractive for Service Broker scenarios, since is explicitly supported in Service Broker routing, see http://msdn2.microsoft.com/en-us/library/ms166052.aspx . You would have only one route, with two addresses (ADDRESS and MIRROR_ADDRESS). Service Broker in this case is capable of identifying the current principal and reacts to a mirror failover, redirecting the messages imedetly as the failover happens, to the new principal. The bad news is Database Mirrorirng is not supported in RTM (see http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx)

With clusters you don't need special support from Service Broker, you just put the address of the virtual node the route and it will work.

You might be tempted to look into a solution that uses only one message per dialog. Since every message is the first message, it will look like you achieved some sort of failover capabilities as well as load balancing: messages will be spread between the two databases and on case when one is down messages will all go to the one still running. Perfect solution, isnt' it? Don't fall for it, there are hidden dangers. First, any dialog has at least two messages exchanged, since it must be closed (END DIALOG is also a message). Second, you run the risk of establishing the same dialog twice, once in each database. This later case is a case Service Broker is aware of and eventualy one of the dialogs will be closed with an error, but your application logic must be prepared to deal with this and have compensation logic to undo the effect of that message. And also you loose the order guarantees dialogs provide, so you're back to the old days when you had to implement the order in the application.

For anybody who happens to stumble on this post later on, when Database Mirroring is supported I'd say go for it, mirroring and broker are a perfect match ;-) Until then, the high availability solution remains clustering.

HTH,
~ Remus

|||

Hi Remus,

I was wondering how dialogs failed over from receiver 1 to receiver 2, when I had not specified broker_identifiers during the route creation. When I stopped receiver 1 midway while it was receiving its response from the sender, all other messages that could not reach Receiver 1 were automatically routed to Receiver 2. Right now, since we are only checking for Service Broker's capabilities around load-balancing, I am actually working on a crude solution to this problem rather than going for a cluster or mirror as you have suggested.

I observed that if I initially create routes with broker_ids specified, and stop one of the receivers' endpoint (Receiver1) midway, then messages destined for that that receiver stay in the sender's transmission_queue. Now, if I drop this defunct route from the sender, the messages destined for receiver1 that could not reach it, now go to receiver2. The only catch is that I now have to work out a way, wherein I can automate this process of dropping the original defunct route, so that the failover is automatic and not manual. Any suggestions around this, and also is this solution feasible.

Regards,

Amit

|||

Lets make a distinction between services that are identical from the perspective of security and functionality and services that are mirrored. When you deploy a service to two different brokers (i.e. databases), each with its own broker instance ID, such that they provide the same functionality and use the same private key for decryption and signing data; we will say the services are identical. On the other hand, when you deploy a service to mirrored brokers (i.e. using database mirroring), such that they have the same broker instance IDs but different endpoints, we will say the services are mirrored.

Identical services can be used for load-balancing dialogs (not messages, but dialogs). Load-balancing is useful for throughput not high-availability. Mirrored services on the other hand are used for high-availability and automatic failover. Service Broker routing allows you to configure the system for either scenarios.

When services are mirrored, we can failover dialogs automatically because the switching the target service to the mirror guarantees that the state associated with the dialog's business transaction is also mirrored. We have no such guarantees with identical services. Since identical services are in different brokers (i.e. databases), if we were to switch to a different service mid-stream, the state associated with that dialog will not failover automatically and so it just wouldn't work.

High-availability and load-balancing can also be mixed. Say you have 4 machines A, B, C, D across which you want to balance your load. You can create four databases on each of the machines to be your brokers and deploy identical services in each of these brokers. Next you can mirror each of the databases by pairing two machines together (B is mirror for A, C is mirror for B and so forth in a cycle). Now if you setup your routes as follows:

Route Service_Name Broker_Instance Address Mirror_Address
r1 s - A B
r2 s - B C
r3 s - C D
r4 s - D A

With this setup, dialogs will get automatically partitioned between A, B, C and D. At the same time, each broker is also mirrored, so if one of the machines was to die (say C), dialogs targetting that broker would automatically failover (to D).

Rushi