Hi Everyone,

This is a high level question.

We are going to be reviewing our current system architecture and a little
bird, a good friend of mine who is one of the most knowledgable SQL Server
MVPs I know, told me I should take a look at Service Broker. Well I have
some other questions about it that he felt would be best if I asked in this
newsgroup, so here goes.

Our current system consists of a central database and a lot of remote
locations. Due to the nature of the business, the remote locations are
actually the masters, and data cannot be saved in the central DB without the
remote being committed. However, it is possible to commit data at the remote
that isn't necessarily committed centrally - this is one of the problem
areas.

The central database should still contain data from all locations, and is
also the data source for the website, regardless of which location is
involved.

Sorry if this is kinda vague but for obvious reasons I can't divulge too
much about the actual business.

Currently there are a number of issues with the system as it exists right
now, and please note I am new to this and my job is to get the issues
resolved. First of all, almost all of the tables have PKs that are GUIDs,
which I would like to eliminate. The reasoning behind this is due to the
master status of the remote locations coupled with the fact that the central
database stores all data, so the PK must be unique across the entire system.
When data is committed at the remotes, that PK value must still be unique,
regardless of whether or not the data is successfully committed in the
central database, hence the use of GUIDs, in an attempt to achieve
system-wide uniqueness. Of course the problem is now that GUIDs are
non-sequential and with them also being PKs there are an awful lot of page
splits going on and re-organization of indexes. I want to get rid of GUIDs
and replace them with a sequential ID, quite possibly IDENTITY values.

The other problem with committing at the remote first, is that currently
MSMQ is being used to transmit the transaction to the central database, and
it is, at best, unreliable. Data gets lost.

Now in the other direction, a transaction that begins at the central
database cannot be committed until the transaction at the remote has been
successfully committed. Distributed transactions are NOT being used for
this. Instead the application open two parallel connections, and begins the
transaction on the central database but does not commit it until it receives
confirmation from the other connection, to the remote, that the data was
committed successfully. Then, and only then, in the transaction in the
central database committed. This causes various issues, including deadlocks,
as the locks on the tables and indexes are being held until the remote
transaction is committed. Due to the location and connection type for some
remotes, the remote transactions can take a very long time to commit.

The other part of this is whether or not the technology is able to use
Service Broker. Right now the central database is 2005 Enterprise, but the
remotes are all 2000 Personal Edition. Obviously the remotes would have to
be updated to 2005 and I presume the equivalent to PE would be 2005 Express.
Would there be any issues with using Service Broker with Express?

Any other suggestions, hints, advice, etc. would be greatly appreciated.

Thanks.

Michael MacGregor
Database Architect