Anyway, I received several questions during this session that I wanted to address via this blog post, as I think they could benefit everyone. So without further delay, here they are:

• What would you recommend for the maximum number of (practical) databases per Availability Group?
  • This will depend on the hardware you’re running on (specifically the number of CPU threads that the primary replica can support), and the network bandwidth available between your primary and secondary replicas. Also, the amount of transactions per second occurring in each database will be a factor in this. There are no hard-and-fast rules about how many databases can be in the Availability Group. Please see http://msdn.microsoft.com/en-us/library/ff878487.aspx#RestrictionsAG for Microsoft’s recommendations in this regard.
• How do Availability Groups work with CDC?
  • CDC is fully supported (and even complimented by) AlwaysOn Availability Groups. Please see this MSDN article: http://msdn.microsoft.com/en-us/library/hh403414.aspx#CDC
• If an Availability Group is setup at the SQL Instance level, can you have multiple SQL instances per cluster node and have an Active-Active configuration?
  • First of all, an Availability Groups is not the same as a Failover Cluster Instance. An Availability Group is a group of 1 or more databases that all failover together and share a set of replicas onto which that failover may occur. Each replica is another SQL Server instance that sits on another node of the same Windows Server Failover Cluster that the primary replica does. With that said, an Availability Group can only have replicas that are nodes of the same Windows Server Failover Cluster. Therefore, active/active in an Availability Group would be more a question about which replicas are readable or not and not so much about running multiple Availability Groups. Additionally, an Availability Group is not an instance-level failover (like in a Failover Cluster Instance), so things like the master and MSDB databases, logins, etc. do not failover in an Availability Group. You can have multiple Availability Groups running at the same time, but keep in mind that they would all need to be sitting on nodes of the same Windows Server Failover Cluster, and only one instance of SQL Server per cluster node can participate in Availability Groups due to the coordination between the Availability Groups and their underlying Windows Server Failover Cluster. To clarify that a bit, you cannot install 2 SQL Server instances to the same Windows Server Failover Cluster node and have one instance host a replica for on Availability Group and the other instance host a replica for a different Availability Group. Instead, you would have a single SQL Server Instance on the Windows Server Failover Cluster node that would participate in both of the Availability Groups.
• Is it possible to set this up with demo licenses? Is there a temp/demo/developer clustering license available from Microsoft? (for those of us on the bench who would like to test this)
  • Absolutely! Microsoft offers an evaluation version of SQL Server 2012, which can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=29066 and used to test AlwaysOn Availability Groups. In addition, if you already have SQL Server Developer Edition licenses, you can use those licenses to test AlwaysOn Availability Groups (you just can’t use them in any production capacity).
• Can you select which databases are in the Availability Group? Can you have two different databases from two different servers?
  • Yes, you can select which databases are part of the Availability Group. However, any database that is part of the Availability Group will need to be present on the primary replica and then synchronized to all secondary replicas. Therefore, if your primary replica has 10 databases, you could select 5 of those databases to be part of the Availability Group and those would then be synchronized to the other replicas. The 5 databases not included in the Availability Group would remain untouched and only on the server that they were on originally. The same is true of the secondary replicas. They will already contain all of the databases that are part of the Availability Group, but they can also contain a number of local databases that are not part of the Availability Group.
• Can we use SQL Server Standard edition for only two nodes? (reference: http://msdn.microsoft.com/en-us/library/cc645993.aspx)
  • No, you cannot. What the High Availability matrix is showing is running a two node Failover Cluster Instance on Standard edition. There are only two editions of SQL Server that will support Availability Groups, and those are Enterprise and Developer editions, and both edition support up to 5 replicas in an Availability Group. Remember that AlwaysOn is just a marketing term that Microsoft uses to describe several of their High Availability features, and is not a feature in itself. Don’t let their overuse of this term confuse you.
• Should the listener be a separate server? Does the listener need to have SQL Server installed on it?
  • The listener name is just a cluster resource name, and is not a separate physical server or cluster node, nor is it a separate SQL Server instance like a Database Mirroring Witness would be. Think of the listener name as just another service that the Windows Server Failover Cluster can host on any of its nodes. The caveat here is that the Availability Group and the Cluster are talking to one another and so the Availability Group makes sure that the listener name is always hosted by the cluster node that is the primary replica of the Availability Group. Therefore it is safe to say that the primary replica (a stand-alone SQL Server Instance installed on a Windows Server Failover Cluster node) is always the host of the listener name (if you created one).

Using AlwaysOn Availability Groups with Service Broker Replication

Well, it has certainly been a while since the last installment of this blog series, and now I’m working at a new company and doing a pretty different kind of work. However, I’ve been getting a lot of requests to complete this series, and I still have the source-code, so let’s get to it!

When we last left off on our little adventure, I described the general architecture of my Service Broker Replication system and why I made the design decisions that I did. In this blog post, I’m going to further that discussion a bit by explaining how Service Broker and AlwaysOn Availability Groups can be used together to increase the availability of the system.

Why is Availability Important to Service Broker Replication?

Well, other than the obvious answer that availability is important to EVERY system, there is one key part of the Service Broker Replication topology that is especially susceptible to a failure, and where a failure would be devastating: the distributor database. If the distributor database fails, then not only will messages fail to be sent through the environment (and therefore the replication partners would get out of sync, but we would also lose the ability to bring new replication partners online, as we wouldn’t have the message history needed to bring them up to speed. For these reasons, some kind of availability solution is critical to Service Broker Replication.

In addition to preventing data-loss, we also need to minimize the amount of time that the distributor is unavailable during an outage. This is because the longer the distributor is inaccessible, the more our replication partners will get out of sync, and the more likely we are to have conflicts occur because expected updates aren’t being replicated across the environment. With that said, we could just implement something like Database Mirroring, Log Shipping, or a Failover Cluster Instance, and all of those are certainly viable options. However, what would happen if there were a loss of connectivity at the data-center housing our distributor database? What if that outage lasted for a couple minutes? How about a couple hours? A couple days? A couple weeks? I think you get the idea. If we implement one of the availability solutions I mentioned above, we don’t really have an answer to those questions (yes, Log Shipping and geo-clustering can potentially solve the issue, but they each have caveats I’d like to avoid, like data-loss due to backup timing and prohibitively expensive and complex SAN hardware).

The Solution

Enter SQL Server 2012 Enterprise Edition and AlwaysOn Availability Groups. Chances are, if you’re at an organization that needs a solution like Service Broker Replication, then you’re probably already running Enterprise Edition. If not, you may want to consider increasing my licensing budget a bit, because Enterprise Edition has some pretty amazing features! One of those amazing features is AlwaysOn Availability Groups, which take the best features of Database Mirroring and Failover Cluster Instances and combine them together. For more information on AlwaysOn Availability Groups and why they solve a lot of availability problems, check out my AlwaysOn Availability Groups page.

The reason why AlwaysOn Availability Groups are a big win for Service Broker Replication is that unlike Failover Cluster Instances, Availability Groups don’t require any cluster shared storage objects. Therefore, geo-clustering with AlwaysOn Availability Groups gets MUCH easier (and cheaper) than it is in a Failover Cluster Instance. So, if you have an Availability Group that spans two data-centers, and the first data-center’s Internet connection fails, your Availability Group will automatically failover to the node in the working data-center, and your Service Broker Replication topology will remain up and running, with no data-loss (actually, there is a possibility for data-loss if you’re running in asynchronous commit mode, but it’s usually pretty minimal).

Another beautiful thing about this combination is that even if messages do fail to send to the distributor or from the distributor to a replication partner during the failover process, those messages will remain en-queued by Service Broker and will be resent once connectivity is restored, which is usually within a minute or two. Therefore, as long as we don’t have any data-loss at the distributor database, our replication partners will synchronize as though no failure even happened.

In addition to making the distributor database a member of an Availability Group, you can also reap the benefits of Availability Groups at each of your replication partners, and keep your local databases and applications up and running in the event of patching, hardware failures, and losses of connectivity. However, I would consider running Availability Groups at the replication partners a lower priority than running an Availability Group at the distributor, so if cash is short, at least make sure your distributor is protected.

Coming Up

Stay tuned! This series gets a lot more juicy in the next installment, as I dive into the different message types that Service Broker Replication sends and how they each work. This is where we make the leap from theoretical to practical, so you won’t want to miss it!

Service Broker Replication – Table of Contents

With that said, here are my plans (for better or for worse):

• Begin blogging again
  • I’d like to set my cadence at one blog post every other week or so, until I see how that works out, and then I’ll adjust it.
  • I’d like to complete my Service Broker Replication series (because I should, and also because I’ve been asked to by a couple of readers).
  • In my new position, I am doing quite a bit of documentation and written communication, and blogging can only help me improve in those areas.
• Start speaking again
  • I have to say, I’ve missed the limelight. But more importantly, I’ve missed being able to help other SQL Server Professionals, and I miss my SQL Family as well.
  • I’d like to start developing new sessions again. I have a couple of ideas for topics that I’d like to learn more about, and I think they would make great presentations as well (I’ll probably be blogging about them too).
  • I’ve gained so much from being a member of PASS, that I need to keep giving back to the community in return. After all, that’s how this sort of community works.

So in conclusion, I’m back, I’m recharged, I’m excited, and I think you’ll be reading, hearing, and seeing a whole lot more of me as we head into summer. It’s going to be a great year!

In my session on Service Broker and also in my session on AlwaysOn Availability Groups, I mention that if you’re using AlwaysOn Availability Groups or Database Mirroring that you should create your logins with the same SID on each instance/replica so that when a failover occurs, you don’t have to re-map your database users to their appropriate logins. However, I don’t really go into the detail of how to do that in my sessions, so I wanted to take some time to do that here.

use master;

-- Get a new GUID value to use as the SID
declare @SID uniqueidentifier = newid();

-- In order to use the SID in a create login statement, it must be in binary format
declare @SID_Binary varbinary(max) = (select cast(@SID as varbinary(max)));

-- View the SID in GUID and Binary format:
select @SID, @SID_Binary;
-- E72669E3-9FAA-4BCB-8F8F-570EBF114674, 0xE36926E7AA9FCB4B8F8F570EBF114674

-- Here is the statement we really want to run:
--create login SQLDiablo with password='Passw0rd!', sid=0xE36926E7AA9FCB4B8F8F570EBF114674;

-- But that requires us to paste in the SID. There has to be a better way:
declare @UserName nvarchar(128) = 'SQLDiablo', @Password nvarchar(max) = 'Passw0rd!';
declare @Query nvarchar(max) = 'create login ' + @UserName + ' with password=''' + @Password + ''', sid=0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@SID_Binary") )', 'varchar(max)') + ';';

select @Query;
execute sp_executesql @Query;

-- Since varbinary can be a little tricky to work with in dynamic SQL, XPath is our friend.
-- Above we converted the value of @SID_Binary to Hex using XPath's value method (don't forget to add 0x to the beginning of it).

-- Get the SID for the login we just created, as a GUID
select sp.name, cast(sp.sid as uniqueidentifier) SID_AS_GUID from sys.server_principals sp where sp.name = 'SQLDiablo';

-- SQLDiablo, E72669E3-9FAA-4BCB-8F8F-570EBF114674

set @Query = 'drop login ' + @UserName + ';';
execute sp_executesql @Query;

• Craig Purnell & Allen White – Thanks for being such awesome leaders and mentors throughout the planning process, I really appreciated your council and direction
• The Devry staff – THANK YOU, THANK YOU, THANK YOU for donating your facility for the day, for helping me remember to take breaks every now and then, for helping keep me sane, and for all of your help throughout the day (you guys were truly AWESOME!!!)
• The Mavis Winkle’s staff – Despite rushed deadlines, miscommunications, double-bookings, an overly stressed team lead (yours truly), and lots of calls back and forth, you guys still pulled off two awesome meals for us and helped make for a great event. I would especially like to thank the owners, Bob and Marie, for helping to make my life a little less stressful on Friday and Saturday. It was really cool getting to work with you and your staff.
• All of our sponsors – We couldn’t do an event like this without your support, and for that we are EXTREMELY grateful. THANK YOU!
• Steven Wright and SQL Sentry – Thanks for sponsoring our Speaker/Sponsor/Volunteer dinner, YOU ROCK!
• Ann Marie Kozlowski – Thanks for letting us use the Solutient offices for bag stuffing, and thanks for taking care of the breakfast arrangements
• Carlton Ramsey & Cory Stevenson (and his wife) – Thanks for buying cookies, pop, and all of the other little odds-and-ends that we needed for the event
• Sam Nasr – Thank you for taking care of the after party arrangements
• Colleen Morrow, Erin Stellato, and anyone else who helped out – THANK YOU SO MUCH!

From the speaker’s dinner on Friday night until I left Devry sometime around 6 PM on Saturday, I was pretty much a blur of activity as I tried to help make sure everything ran smoothly. Despite all of that running around, I was still able to attend two awesome sessions, present my own session, mingle, network, and have a ton of fun! I think the key to my success was that I had an absolutely awesome team that I could rely on to really get things done.

One of the things to keep in mind about an event like this is that it is inevitable that there will be lots of little problems and issues that come up throughout the day. The key to handling these issues is to keep calm, ask for help when you need it, and to trust your team to do their job. It’s not necessary to manage every little detail of an event like this. It’s simply too much to handle for one person. Instead, break the responsibilities up into smaller tasks, assign those tasks to people or teams to accomplish, and then give them the room to do their job.

As far as the food was concerned, there are a couple things to consider when planning an event like this:

• If you have the space to allow everyone to dine in the same area, then you have a lot more flexibility as to what kinds of food you can serve, and you can even have a fully catered buffet (much like the awesome buffet at SQL Saturday in Chicago earlier this year).
• If you don’t have a large common area, then you will need to distribute the food and have the attendees go into the session rooms to eat it. If this is the case, you’re going to want to go with highly portable food such as boxed lunches.
• Don’t forget to get lots of heavy-duty construction garbage bags and to distribute them throughout the venue to handle the trash that will be generated. It would also be a good idea to have a team that checks the garbage cans and bags throughout the day and empties them as needed.
• An afternoon snack is a good idea, but don’t over do it. We bought 2 cookies for every attendee as an afternoon snack, and we had about half of them left over after the event.
• You’re going to have food left over after an event like this. It might be a good idea to get in touch with your local foodbank, homeless shelter, or area churches before the event and see if they can use the leftover food. They’ll thank you for it, and it’s one more way you can give back to the community.

As far as the venue goes, here are my thoughts:

• Keep the logistics of an event like this in mind when choosing a venue. Make sure the hallways are wide enough, the doors won’t automatically lock you out throughout the day (yep, this actually happened to us), and that there is enough space for the number of attendees you’re aiming for. Sometimes cheaper (free) isn’t always better.
• Regardless of whether you get the venue for free or at full price, make sure to clean up after your event and to try to help the venue’s staff in any way you can. After all, you’re representing PASS as a whole, and you might even want to have another SQL Saturday there next year.
• If you’re having the event at a college or school, why not do a track of sessions that students from the school can attend? It’s a way of saying thank you for the use of the venue and it’s also a way to give back to the community. SQL Saturday in Cleveland was able to use the Devry campus here for free as a direct result of SQL Saturday in Chicago doing exactly what I mentioned. The Cleveland team also did a track of intro sessions for the Devry students, and the track was very well received.

Other than that, my only advice is to HAVE FUN! Events like this aren’t worth the effort if they’re not only educational, but fun and social as well. You’re going to have to work hard to get the job done, but that doesn’t mean you can’t play hard too.

After mentioning this caveat, I was asked a very good question: Can you use transaction isolation levels (such as snapshot isolation) with AlwaysOn Availability Groups, and would they help to avoid the issue of read-only queries on secondary replicas blocking the redo thread? In order to answer this question, I’m going to break it two parts, and then we’ll work through a couple demos to illustrate the answers.

Questions:

Q: Can you use transaction isolation levels with AlwaysOn Availability Groups?
A: Yes and no. Yes, you can set whatever isolation level you would like when running queries on the secondary replicas, and SQL Server will not return an error. However, SQL Server will automatically override the isolation level (and ignore all lock hints) when querying a read-only replica, and instead force the queries to use snapshot isolation.

Per the “Benefits” topic of the Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) BOL article:

Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.

Q: Does using snapshot isolation prevent read-only queries from blocking the redo thread on secondary replicas?
A: After reading the above quote from BOL, you would think that read-only queries won’t block the redo thread, and that is true for DML statements. However, it is not true for DDL statements. Sure, snapshot isolation prevents read-only queries on the secondary replicas from taking locks and preventing other DML statements against the database from executing, but it doesn’t prevent the read-only queries from taking schema stability locks and blocking DDL statements.

If you take a look later on in the same article I quoted above, you will come to the “Read-Only Workload Impact” section under the “Performance Considerations” topic:

Also, read-only workloads on the secondary replicas can block data definition language (DDL) changes that are applied through log records. Even though the read operations do not take shared locks because of row versioning, these operations take schema stability (Sch-S) locks, which can block redo operations that are applying DDL changes.

Demos:

In order to have a reference architecture to use for this demo, I’m going to use the Availability Group that I setup as part of the presentation that I mentioned at the beginning of this article. If you would like to play along at home, you can download the scripts and slide deck for that session here. This Availability Group is called DemoAG, and has three replicas, AlwaysOn1 (primary), AlwaysOn2 (secondary), and AlwaysOn3 (secondary). There are two databases participating in the Availability Group: AdventureWorks2012 and AdventureWorksDW2012. I’m going to be using AdventureWorks2012 for this demo.

Testing isolation levels and lock hints:

Unfortunately, I haven’t come up with a good way to demonstrate that SQL is overriding the transaction isolation level on the readable secondary because it is actually doing this internally (I’ll keep looking into this to see if there is a good way to demonstrate it). I can, however, demonstrate that SQL Server is ignoring lock hints on the read-only replicas. I can do this by beginning a transaction, executing a select query with the tablockx lock hint, and leaving the transaction open. I then open a new query window and query sys.dm_tran_locks to see if there is a table lock on the table. If I do this on the primary, you will see an open table lock on the table. If I do this on the secondary, there is no open table lock. If I then try to update data on the primary replica, I am able to see the statement completes and that the data has been written to the replica, even with the open transaction on the secondary that should be holding a table lock.

-- Execute this in a session of its own on the primary replica:
begin tran;

select * from ErrorLog with (tablockx);

-- With the above transaction open, launch a new query window on
-- the primary replica, and execute this query
-- (you should notice a table lock on the ErrorLog table):
use master;

select
t.name
, dtr.*
from sys.dm_tran_locks dtr
join sys.databases d
on d.name = 'AdventureWorks2012'
and d.database_id = dtr.resource_database_id
join AdventureWorks2012.sys.tables t
on t.object_id = dtr.resource_associated_entity_id;

-- Now try running this insert in the same query window as the sys.dm_tran_locks query
-- (you should get blocked until you commit or rollback the transaction in the other session):
insert AdventureWorks2012.dbo.ErrorLog(UserName, ErrorNumber, ErrorMessage)
values('DemoUser', 1, 'Demo Message');

-- Go ahead and close both query windows, and connect to one of the secondary replicas.
-- Now, try running through the scenario above on the secondary replica (minus the insert).
-- You should notice that there is no table lock being held, even though we explicitly requested one.

Testing read-only queries blocking DDL statements:

As far as demonstrating that read-only queries can block DDL statements, all we need to do is run a long-running query on the secondary replica and simultaneously try to alter the table that the query on the secondary is using. We should notice that the redo queue size on the secondary then increases and stays that way until we either kill or complete the read-only query. This indicates that the redo thread is not able to commit the DDL changes because the select statement is taking a schema stability lock and preventing the alteration to the table.

-- Run this query on the secondary replica
-- (it's horribly ugly and inefficient, but the idea is to hold a
-- schema stability lock on ErrorLog for as long as we can):

select
NumbersTable.Number
from AdventureWorks2012.dbo.ErrorLog el
cross apply (
select
Number
from
(
select
row_number() over (order by s1.name) as number
from AdventureWorks2012.sys.sysobjects s1
cross apply AdventureWorks2012.sys.sysobjects s2
cross apply AdventureWorks2012.sys.sysobjects s3
cross apply AdventureWorks2012.sys.sysobjects s4
cross apply AdventureWorks2012.sys.sysobjects s5
) as InnerNumbersTable
) NumbersTable
group by NumbersTable.Number
order by NumbersTable.Number desc;

-- While that beast is running on the secondary replica, run the
-- following on the primary replica:

alter table AdventureWorks2012.dbo.ErrorLog add DemoColumn varchar(10) null;

-- Now go back to the secondary replica, open a new query window, and run the
-- query below (you should notice that the redo queue is greater than 0,
-- which indicates that the redo thread is having trouble applying changes
-- to the database):
select
dhdrs.redo_queue_size
from master.sys.dm_hadr_database_replica_states dhdrs
join master.sys.databases d
on d.database_id = dhdrs.database_id
and d.name = 'AdventureWorks2012';

The other tough part in all of this has been juggling life, work, and prepping for my sessions. I’ve been working on two different sessions and will be presenting each of those sessions twice in a one week period (split between three different speaking engagements: SQL Saturday #117 in Columbus, the Ohio North SQL Server User Group meeting next Tuesday in Cleveland, and SQL Saturday #121 in Philadelphia, PA that is a week from tomorrow). Having so much to do in such a short amount of time has been pretty tough to juggle and make sure that nothing slips through the cracks, but somehow I’ve managed. Don’t get me wrong, I’m not complaining about any of this. In fact, I feel extremely honored to have been selected to speak at these events, and it’s a huge privilege to get to give back to the PASS community that has given me so much over the last year. However, I should have taken more care in submitting my abstracts, scheduling vacations, etc to give myself a little more time to prepare. I think my sessions will go fine, but it never hurts to have a little more time to practice and prepare.

Anyway, I think it’s time to grab another cup of coffee, sit back, listen to some smooth jazz music, and relax a little before my next batch of craziness kicks in. Take care, and hopefully I’ll see you at a SQL Saturday, user group meeting, or at the PASS Summit this year.

Click to view full size

As you can see, the percentage they are asking for is the amount of free space to leave on the index pages, and NOT the fillfactor for the index pages. Had I read this screen more carefully yesterday, I would have saved myself and my colleagues much pain and suffering today (a database with 90% free space in the indexes doesn’t perform well AT ALL…lol).

Update: I’ve also posted this question on DBA.StackExchange.com.

The Setup:

I have a Hyper-V host running 6 Windows Server 2008 R2 VMs (BTDevSQLVM1-BTDevSQLVM6). The VMs are grouped into 2-node WSFCs with node and file share quorum. I’ve installed standalone SQL 2012 Developer Edition instances on each of the VMs, and created an Availability Group with a listener on each cluster (SBReplDistrib, SBRepl1, & SBRepl2).

For the purpose of this blog post, I’ll be focusing on the communication between SBRepl1 and SBReplDistrib. The illustration below shows the Service Broker objects for each side of the conversation:

The Service Broker endpoints and routes are setup per this MSDN article.The SBRepl_Receive route in MSDB is for the local server’s service (//SBReplDistrib/SBRepl on SBReplDistrib, and //SBRepl1/SBRepl on SBRepl1), and points to the local instance. The SBRepl_Send route on SBRepl1 maps service //SBReplDistrib/SBRepl to TCP://SBReplDistrib:4022, and the SBRepl_Send_SBRepl1 route on SBReplDistrib is a similar mapping for the service on SBRepl1.

The Expected Behavior:

My understanding of how Service Broker handles message sending and receiving is thus (This is pretty simplified. There is a lot more detail about this process in Klaus Aschenbrenner’s (blog | twitter) book “Pro SQL Server 2008 Service Broker”):

1. The initiator app creates a message (in this case, well formed XML)
2. If there is an existing dialog conversation between the initiator service and the target service that is in the conversing status, the app can simply send the message on the existing conversation handle. Otherwise, the initiator app should begin a dialog conversation between the initiator service and the target service and send the message on that conversation handle.
3. The message is placed in the sys.transmission_queue system table and Service Broker begins making attempts to deliver the message to the target service.
4. Service Broker looks for an appropriate route and remote service binding and uses them to determine the address to connect to in order to deliver the message.
5. Service Broker opens a connection to the target, authenticates, and delivers the message to the target service broker.
6. The target Service Broker attempts to classify the message and determine what local service will handle the message (it uses route data in the msdb database for this).
7. The target Service Broker delivers the message to the target service’s queue
8. Once the message is successfully delivered to the target queue, the target Service Broker looks for route information back to the initiator and attempts to deliver an acknowledgement that the message was received.
9. The initiator’s Service Broker receives the acknowledgement and uses routing information in MSDB to determine what local service the acknowledgement is for.
10. Upon successful routing of the acknowledgement to the initiating service, the message is then removed from the sys.transmission_queue system table.
11. If the initiator does not receive an acknowledgement that the message was received, it will periodically retry delivering the message to the target. If the target has already received the message, it will simply drop any additional delivery retries and send acknowledgements for them.

The Odd Behavior:

Step 11 is where I am seeing some very odd behavior with Service Broker and AlwaysOn. I see the message getting delivered to the target and processed successfully, and I also see the acknowledgement getting sent back to the initiator and received. However, the message remains in sys.transmission_queue as though no acknowledgement was received. To make things even more strange, Service Broker isn’t attempting to resend the message like I would expect it to if the acknowledgement wasn’t received. Instead, the message simply remain in the sys.transmission_queue, and as new messages are sent, they get delivered, acknowledged, and they too remain in the sys.transmission_queue. It seems to me like service broker is getting the acknowledgements and therefore stops trying to deliver the message, but doesn’t remove it from the sys.transmission_queue for some reason. The transmission_status for these messages remains blank, which should indicate that Service Broker hasn’t attempted to deliver them yet.

I checked the retention setting on the service queue, and it is set to off, but that should only impact the service queue and not the sys.transmission_queue. I have also traced both sides of the conversation using SQL Profiler, and I am able to see the message getting sent and the acknowledgement being sent back to the initiator and getting received (see XML trace data at the end of this post).

One odd thing did jump out at me in the traces though. I noticed that both sides seemed to be a bit confused about the TCP connections, because messages are sent from the IP address of the node itself while the service routes and the messages themselves point to the name/IP of the AG listener. This confusion appears to be causing each side to close the existing connection between the two services and create a new one in order to deliver a message or acknowledgement. I’m not sure if this is normal or not or if it has anything to do with why the acknowledgements aren’t being handled correctly, but it was the only thing I could see that could possibly explain the odd behavior.

The Plea for Help:

At this time, I don’t have a solution to this message retention issue other than to manually end the conversation with cleanup on both sides, and that’s not really something I want to do. If you have any ideas as to why this might be happening or what I can do about it, please leave me a comment and let me know. If there is any additional information that you would like me to provide about my setup or about the issue, please let me know in the comments as well. I will post a followup to this post if/when I find a solution to this issue.

The Trace Data:

I’ve removed the trace data from this blog post for brevity and security reasons. Please leave me a comment if you would like to review the trace files.

System Overview

In my previous posts, I explained how Service Broker works and why I chose it as part of a solution to our HA & DR problem. In this post, I’ll give a high-level overview of the solution and the replication model I chose. I will go into further detail on the various aspects of the solution in future blog posts in this series (including how AlwaysOn Availability Groups can be used with this system).

Replication Model

For this project, I evaluated two models for replication: a mesh (peer to peer) & a distributor-subscriber model. In a mesh, every replication partner sends its changes to every other replication partner and consequently receives changes from every other replication partner as well. The benefits of this model are that it has a low latency for changes to be applied and there is no single point of failure. If any replication partner goes down, the remaining replication partners stay up and continue to synchronize with each other. However, there is a large amount of setup and maintenance needed for this model, and it increases exponentially as new replication partners are added (the more replication partners you add, the more services/queues/routes, etc there are to add and manage).

With a distributor-subscriber model, each replication partner sends its changes to the distributor, and the distributor then relays those changes to the other replication partners. Therefore, each replication partner only really receives changes from the distributor. The benefits are simpler setup and maintenance (each replication partner only needs to communicate with the distributor instead of communicating with every other replication partner) as well as the ability to easily log the order in which changes are made in the environment (I’ll explain why this is important later). The drawbacks to this model are that the distributor becomes a single point of failure (AlwaysOn Availability Groups can help with this problem), and slightly increased latency for changes to be replicated throughout the environment.

In the end, I decided to go with the distributor-subscriber model because of the maintenance nightmare that a mesh can become and because with a mesh, it can be very difficult to bring on a new replication partner and synchronize it (this is because there is no single list of the changes that are being made in the environment and no guarantee as to the order those changes will be applied at any given replication partner). By having a centralized distributor that coordinates all communications, the system knows exactly what changes are being made and in what order, so it becomes much easier to take backups at any replication partner or bring a new replication partner online (I will describe the processes of taking backups and bringing on new replication partners in later posts in this series).

Service Broker Topology & Security

In keeping with the theme of simplicity and ease of management, each replication partner has a single queue, service, and message type that handle both sending & receiving changes to and from the distributor. This also ensures that changes will be sent (and applied) in the order that they occur. However, I still wanted a logical way to separate the kinds of messages being sent between the replication partners. The easiest and most extensible way I came up with was to just declare what kind of message was being sent as the name of the root node of the XML message (see the example below). This allows me to add additional types as needed in the future without having to alter the service broker objects themselves. The three kinds of messages I came up with are DML messages, DDL messages, and COMMAND messages. I will go into more detail on these message kinds in future posts in this series.

<DML OriginatingServerName="ReplPartner1" SentUTCDate="2011-11-10 14:30:39.7779946" DistributorReceivedUTCDate="2011-11-10 14:30:39.9742583" DistributorSentUTCDate="2011-11-10 14:30:40.0211474">
  <INSERT DatabaseName="TestDB" SchemaName="dbo" TableName="TestTable">
    <row>
      <Description>Test Description 1</Description>
      <Name>Test Name 1</Name>
      <ServerId>2</ServerId>
      <TestTableId>1</TestTableId>
    </row>
  </INSERT>
</DML>

Since each replication partner should be able to have multiple databases participating in replication, I created a special database (SBRepl) just for the service broker objects and to store configuration options. However, this means that instead of using a database user without a login to execute the auto activation stored procedure for the queue, I had to use a database user with a login. This is because a database user without a login cannot be granted rights to other databases in the same instance. By using a login, I was able to have the auto activation stored procedure reside within the SBRepl database and have it apply changes to any other database within the instance (as long as I grant it the necessary rights to those databases). Conversely, any logins that make changes to a database that is replicated will also need rights to the SBRepl database to allow those changes to be replicated.

Data-Driven Subscriptions and Supporting Objects

In an effort to simplify management while still maintaining some control over what changes are replicated and to which replication partners, I decided to implement data-driven subscriptions. These subscription are broken into two parts: DML subscriptions and DDL subscriptions. DML subscriptions control which tables within a subscribed database to replicate data changes for. DDL changes control which DDL statements (create user, create table, alter procedure, etc.) to replicate for a subscribed database. Each subscription also indicates whether a given replication partner can send messages, receive messages, or both for that subscription. Therefore, a replication partner might be able to receive DML and DDL changes for a certain database, but not send them (or vice versa). This allows a subscribed database to be read-only, write-only, or read-write with respect to replication. In most environments, the subscriptions will be read-write, but I wanted the option to be there in case it was needed. As for DML and DDL subscriptions themselves, I will go into further detail about them in a later blog post in this series.

Up to this point, I’ve been describing the communication of changes. However, the system needs to be able to detect that changes were made in the first place. In order to do that, I decided to use a database trigger to detect DDL changes made within a replicated database and a DML trigger to detect changes made to data in a replicated table. Each of these triggers does 3 things: 1) Checks to see if the trigger was fired by a change being applied from another replication partner and stops execution of the trigger if it was (this prevents looping within the replication environment) 2) Checks the DML or DDL subscriptions to see if it is “allowed” to send these changes to the other replication partners and stops execution of the trigger if it is not 3) Gathers the changes into one or more messages and sends them to the distributor. I will cover the database and table triggers in more detail in a future post.

Service Broker Replication – Table of Contents