Case Study: 1,800 SQL Servers connected via Service Broker

One of the largest SQL Server Service Broker implementations in the World!

published: 2012-09-21 00:00

A client needed a system designed, built and deployed to collect data from their 1,800 remote individual database servers into a central database for extracting and reporting by other tools. The system needed to be secure, robust, have central monitoring and be easily expandable.

Solution

The architecture the client used for their 1,800 SQL sites had no direct Virtual Private Network (VPN) with the central server residing on a corporate network therefore all communication was via the internet.

This client’s architecture had the following challenges:

The central server exists on a corporate network behind a firewall. The firewall protects their network from unauthorised outside communications.

The sites cannot connect directly to the central database as it would mean opening up the firewall to 100’s of addresses.

Communications need to be secure.

Hundreds of sites rather than just one or two sending messages between each other.

After analysis the client decided to use the SQL Server messaging technology, Service Broker. Service Broker is an asynchronous messaging technology first included as part of SQL Server 2005.

The solution was developed using Service broker messaging as it allows the 1,800 remote databases and the central database to form a loosely coupled system. It also means messages can go by as many routes as necessary, to end up at the central database.

Using Service Broker also offered the following advantages over other messaging technologies:

It is part of the SQL Server suite, so no additional product licensing was required.

The messaging parts of Service Broker and the database parts of the application use the same T-SQL language.

All queues and structures are contained within the databases, meaning backups occur as part of the normal database maintenance.

Permissions for sending and receiving messages are managed by SQL Server security.

The queues are part of the database, meaning reports can be developed to view what is happening in queues and backlogs with sending.

Using SQL Server Service Broker provides loose coupling between the remote sites and the central server. An application can send a message on a queue and then continue with application processing, relying on Service Broker to ensure that the message reached its destination.

Another benefit of service broker is that if the destination site is not immediately available, the message remains in the transmission queue for the sending database. Service Broker retries the message until the message is sent successfully. Service Broker delivers the message even if the instance fails over or restarts.

Message forwarding allowed us to add intermediate SQL Server instances for message forwarding and network abstraction. This intermediate instances accepted messages from the 1,800 sites and sent them on to the central database. This meant the central server only needed to be aware of the forwarders network address.

In establishing communication between 1,800 sites there was no practical way of running scripts and configuring communications manually because Service Broker offers none of the deployment tools or scripting wizards that DBA’s normally use when working with SQL Server. To solve this problem, a .NET application was built to be run on all of the object creation scripts on sites. There is no limit on the number of remote sites this can now be deployed to.

Benefits to client

Below are some of the major benefits SQL Server Service Broker provided to the customer:

Service Broker, as a solution proved to be a secure and reliable solution.

Different communications are now directed to different servers depending on the message contents.

Requests for data to be sent can now be managed centrally and at certain times to avoid performance impact on the client systems.

The solution has been proven to be extremely robust, and the client is now seeing extra potential, and looking how to use this secure, configurable, communication infrastructure to maximise the value of the data.

The client has requested us to implement many additional features to the solution based on the success of the initial project.

For more information on SQL Server Service Broker, or any of our solutions, please contact us.