SQL Server Replication and Availability Groups – the Publisher

I have worked quite a bit (too much?) with SQL Server replication. It has mostly been Transactional Replication and less Merge Replication.

A very common scenario I come across in both real life and in forum discussions is SQL Server Replication and Availability Groups.

This is the first in a short series of posts about replication and Availability Groups. As time permits, I will also finish posts on the Distributor and Subscriber roles in Availability Groups and finalize with a post on war stories from trying to move a distributor and subscribers from standalone instances to Availability Groups.

Picture of Replication Protein A3 from wikipedia, licensed under CC-BY-SA-3.0

I’m not going to give very detailed instructions. That’s already well described in official documentation. Use this page as a starting point: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver15

Instead I’m gonna try to summarize which scenarios are supported and which are not supported and a brief overview of the steps included. I will also share some of my experience too, gotchas and success factors.

Setup Publisher in Availability Group

A publisher can be part of an availability group. Both transactional replication and merge replication are supported scenarios.

Assuming you have already setup the distributor, these are the steps. Order does matter. I’m also assuming you have already setup the Availability Group before configuring replication publisher. If that’s not the case, aka you have already setup a publisher and then add them to availability group, this is also supported and you will just have to skip the steps that have to do with setting up the primary replica as publisher in this list.

  • Install replication features on all replicas.
  • Configure the primary replica and all secondary replicas as publishers in the distributor (with sp_adddistpublisher).
  • Configure the distributor at all replicas (with sp_adddistributor).
  • Enable the database(s) for replication (with sp_replicationdboption) in the primary replica.
  • (Documentation say linkedserver-connections are to be setup from publisher to push subscribers. I don’t do this, and more specifically, I don’t always know which subscribers I will have when I setup publications).
  • On distributor, redirect publisher to Availability Group Listener name (using sp_redirect_publisher)
  • Validate redirection works (with sp_validate_replica_hosts_as_publishers). This step will fail for secondary replicas not setup for read access.

It might come out as strange that the validation step fails. Usually you don’t want red errors in your result. But to get rid of red errors in the validation, you will need to pay additional enterprise licenses for the secondary replicas. If you won’t use the secondary replicas for read-only workloads, it’s not gonna be worth spending the extra money on additional enterprise licenses.

Test, test, test

I strongly encourage you to test this scenario and also test failing over to all secondary replica hosts.

On a busy system, you will have very, very, very nasty side effects when your publisher isn’t able to send stuff to the distributor. You will see transaction logs fill up and you will see replication as log_reuse_wait_desc in sys.databases. What that means is your transaction logs will grow, grow and then grow some more, until there’s no more disk to grow on and your databases will stop. If you miss this, and get a call in the middle of the night (these things always happen around 2AM) you’re gonna find yourself in a fun situation. A fast way to get rid of traces of replication is to set DB to single recovery mode and then run sp_removedbreplication. But it’s an Availability Group your dealing with here, and simple recovery mode doesn’t play well with Availability Groups. So your other option will be to reconfigure replication on the now primary replica, publish something and then remove replication. Either way, you’re into a mess you don’t want to deal with in production.

Script, script, script

As part of your setting up replication publishers on Availability Groups in your sandbox environment (that is where you should start, if you don’t have such environment, create one, it’s worth the effort and money), create scripts and make sure those scripts work.

  • Script to setup replication.
  • Script to add secondary replicas including setting up replication on them.
  • Script to fail back to your original primary replica (where you have a greater chance of logreader agents actually working and not filling up transaction logs).
  • Script to remove all traces of replication.

The script to remove all traces of replication is an important one. It might have to start with enabling replication, with the distribution database on the publisher and then removing it, as some commands to remove replication won’t work if you can’t reach the distributor.

It doesn’t matter if you create SQL-scripts, Powershell scripts or anything else. But please do what you can to automate creating and destruction of replication. Test these scripts. Not just the normal flow, but the odd quirky ones too. Test from the DR-site, to make sure you have firewalls open to everything you need. Test on all replicas. Test what happens when distributor is offline.

Monitor, monitor, monitor

There are a million ways to monitor your SQL Server environment. Choose your favourite flavour. But make sure to monitor your replication setup. Monitor transaction log usage on the publisher side. If your published databases’ transaction log usage remains high after transaction log backups, you want to set off alarms. You want to monitor messages from the Log Reader Agents. Your Log Reader Agents might seem to work just fine, in the way that they are in a running state. That doesn’t mean they are sending anything at all to the distributor. Try to monitor end-to-end. Does an inserted or changed row from a published table in the publisher end up in the distributor? If it doesn’t, you have a problem. And you want to solve that problem before your index maintenance jobs start filling up the transaction log the next night.

Do I hate replication?

Reading my warnings above, it might look like I hate SQL Server replication. That’s not the case. Transactional replication is a much cheaper solution than readonly AG secondaries for reporting scenarios. And you can choose to replicate only the stuff you need for reporting instead of the whole database. And you can add your reporting-specialised indexes on your reporting database. And, and, and…

But be careful. You don’t want to called in at 2AM because your production database died. Especially if the reason it failed is because you weren’t careful.

We’re back: Data Weekender #TheSQL

DataWeekender organizer badge

Data Community Weekender, Data Weekender or #DataWeekender. We did the first edition on May 2nd and we’re back October 17th with the second edition, the sequel or #TheSQL if you like. Register here: https://www.meetup.com/DataWeekender/events/272258429/

For those of you who didn’t attend Data Weekender in May, Data Weekender is a free, online conference all about Microsoft Data Platform.

On our first event, we had 42 sessions on six different tracks and over 600 attendees in total. We organised the event in more or less just four weeks, from announcing the Call for Speakers to selecting sessions and running the conference. It was a lot of work, not much sleep and tons of fun. We also received really good feedback, much thanks to our lovely volunteers that helped moderating sessions and various other tasks.

This time around, we thought we’d give ourselves a little more time, and therefore we announce it more than two months in advance. It’s still going to be tons of work and not much sleep. This time, like the first time, we run Data Weekender on a zero budget, meaning we don’t accept any sponsors. All the costs involved are covered by us on the organising team. Most of all that means we don’t have a marketing budget and therefore have to rely on ourselves to try to reach out on LinkedIn, Twitter and Facebook about it.


Although we think our first episode of Data Weekender was really successful (it really was, way, way better than we ourselves expected), we are making a few changes this time. I can’t reveal everything yet, mainly because not everything is settled, but some of the changes are:

  • We will push the schedule to a slightly later time, to allow for more speakers and attendees from North and South America. Not much though, we still want to make it possible for attendees and speakers from Australia, New Zealand, Asia, Africa and Europe to be able to attend.
  • We will arrange the tracks a little differently, more on that later.
  • The organising team will be put to the test, not only as organisers. You will be able to influence that. And I’m honestly a little scared about it.

Useful links

Data Weekender on Twitter: https://twitter.com/DataWeekender

Data Weekender webpage: https://www.dataweekender.com

Meetup group: https://meetup.com/DataWeekender

Register to attend: https://www.meetup.com/DataWeekender/events/272258429/

Organising team

The organising team behind Data Weekender is:

Magnus Ahlkvist (T)
Kevin Chant (T)
Gethyn Ellis (T)
Asgeir Gunnarsson (T)
Mark Hayes (T)
Damir Matesic (T)

It’s #SQLFriday!

It's SQL Friday

Finally, it’s live. I hoped to announce the birth of SQL Friday on #tsql2sday but I simply didn’t have time for it. But now the site is live and speakers are queueing up for it.

What is SQL Friday?

SQL Friday is a weekly Friday lunch session, where a community speaker joins me in a Teams-meeting to present a Microsoft Data Platform or #SQLFamily related topic. The sessions are recorded and put on Youtube (and on the site).

The schedule is announced well ahead of time, but the full session details is announced and the registration opens up for next week’s SQL Friday just as this week’s SQL Friday finishes.

How do I attend SQL Friday?

You join the Meetup group created for SQL Friday, and register to attend to the session you’re interested in. https://meetup.com/Sql-Friday

SQL Friday

Enköping, SE
189 Members

SQL Friday is a weekly online lunch seminar about Microsoft Data Platform. It’s hosted by Magnus Ahlkvist (B | T) and financed by Transmokopter SQL AB.Homepage of SQL Friday …

Next Meetup

Kevin Chant and Sander Stad on “Azure devops duet”

Friday, Jun 5, 2020, 12:00 PM
58 Attending

Check out this Meetup Group →

My response to Covid-19: #DataWeekender

T-SQL Tuesday
T-SQL Tuesday

This post is a contribution to this month’s T-SQL Tuesday (hashtag #tsql2sday on Twitter). Glenn Berry is this month’s host, and the topic is: “What you have been doing as a response to COVID-19”, as described in Glenn’s blog post T-SQL Tuesday #126 – Folding@Home.

I’m thinking of two contributions I’ve been working on for the community we know as SQL Family. One being planned, TGI SQL Friday, a friday lunch talk about Microsoft Data Platform, with one other member of the SQL Family as a guest. But this post is going to be about Data Community Weekender Europe, better known #DataWeekender.

I had been looking forward to 2020 as my most active year as a speaker. I have been in contact with local user groups in Europe, to whom I was going to pay a visit. I have submitted way more session abstracts to SQL Saturdays and other conferences than any previous year. Then came Covid-19. SQL Saturday Croatia was postponed. SQL Saturday Stockholm was cancelled. Trips to user groups were obviously cancelled. You get the pattern.

When looking at Twitter, I saw Kevin Chant (B | T) gathering interested parties to a virtual conference. Two tweets later, I was one of the six organisers of Data Community Weekender Europe, better known as #DataWeekender or Data Weekender. This was April 9th. The other organisers had already come up with the name of the conference and setup Call for Speakers in Sessionize. After that, it’s been hard work.

We had a number of challenges to overcome, many of them technical.

But before getting any of that up, we needed speakers. Without speakers, there wouldn’t be much of a conference. A discussion we had on an early stage was: “Should we as organisers submit sessions to the conference?” We decided we could, but wanted to wait to see how many abstracts were submitted before deciding. We had a record short Call for Speakers period, from April 8th to April 17th. That’s ten days. We felt it was a bit short, but to realistically have the conference on May 2nd, we couldn’t extend CfS-period further. Thus the decision we would all submit a session each if needed, to make it at least two tracks on the conference.

Wow. Were we wrong. There was absolutely no need to submit sessions ourselves. It turns out we had excellent channels to reach potential speakers, and our tweets and LinkedIn-posts about the conference reached out amazingly well (thank you everyone who tweeted and retweeted on an early stage!). By the end of CfS-period, we had almost 200 excellent sessions to choose from.

Next dilemma: With so many fantastic session abstracts submitted, from 76 speakers, how on earth were we to select sessions? We had discussed running two, maybe three tracks. We ended up with six parallel tracks, a total of 42 really good sessions, many of them from the most well-known speakers in the industry.

April 22nd, we made the schedule public. By then, we had roughly 150 registered attendees. We are all experienced community organisers and we know the no show-rate on any free event is 40-60%. That would leave is with roughly 75 attendees, for a six-track conference. Not impressive. The day we published the schedule, we got another 80 or so attendees registered. But we still had a long way to go and the days following April 22nd were pretty slow on registrations (50 or less per day).

Then something happened on April 27th, less than a week before the conference. We got 155 registrations on the same day. And an average of 100 registered attendees per day the following days.

This didn’t just happen. Between the six of us on the organising team, we probably tweeted 500 times with the hashtag #DataWeekender. We Posted around 100 LinkedIn-posts. We got in touch with all user groups we could find and told them about the event. We told people on the largest Facebook-groups about the event. Our Twitter account @DataWeekender started sending out tweets with dance-gifs every time we hit an even 50 or 100 registered attendees. And those tweets reached out.

I have to mention the help we got from volunteers as well. Both with promotion before the conference, but perhaps most importantly, all the help we received on the day of the event. If it’d just been the six of us, we would have been moderating one track each all day, without any way to answer support speakers during the day. We had setup WhatsApp-groups for the speakers of all the six tracks, to quickly reach speakers and give them a direct contact with one of the organisers. But without help with session moderation, it would have been useless, nobody can multitask that much in the middle of an ongoing session. So a yuuuuuuuuuuuuge Thank You to all the volunteers! ❤❤❤❤❤❤❤

Now the event day seems long gone, though it’s just ten days ago. We have been distributing speaker feedback to all the speakers and we evaluated the event feedback we got. And we are already talking about next iteration. Should we run it in another timezone? What else do we need to change (not much tbh, we feel really comfortable with the format). When will it happen? Does that make you curious? Sign up on the MeetUp-group Data Community Weekender and you’ll be the first to know!

Until next time: Thank You everyone who participated in any way to make the inaugural a complete success!

Organising team for Data Weekender:

Magnus Ahlkvist (B | T)
Kevin Chant (B | T)
Gethyn Ellis (B | T)
Mark Hayes (B | T)
Asgeir Gunnarsson (B | T)
Damir Matesic (B | T)

I am a #DataWeekender – schedule is live!

Data Weekender Schedule

The past ten days, I have spent quite some time promoting the virtual conference Data Community Weekender Europe, or #DataWeekender on May 2 2020. I’m one of six organizers, all people from the Microsoft Data community, better known as #sqlfamily. We saw conferences get cancelled or postponed and wanted to do something for the members of the community, speakers as well as conference attendees. So we came up with the idea to organize a virtual conference – #DataWeekender.

We are not alone, EightKB is another new Microsoft Data Platform conference, in June. And GroupBy is happening as usual in May. (I have submitted sessions to both, and for GroupBy, you are very welcome to vote for my session, as their sessions are picked by the community, not the organizers).

But #DataWeekender is probably the conference with the shortest time from idea to conference day. We opened up Call for Speakers April 8 and the conference is May 2. Given that short timeframe, I’m completely blown away by the number and quality of submissions. Doing session selections has been extremely difficult. If we created a new conference, with only the speakers and sessions we unfortunately had to reject, it would still be a very respectable conference schedule.

We still have a lot of work to do the coming less than two weeks leading up to the conference. And during the actual conference, the whole organising team will be busy moderating sessions. And until then, we need to test out conference technology, distribute and get confirmation on tons of information to speakers. And last but absolutely not least, we need to continue marketing the conference to attendees.

A very important milestone in the conference planning is now done. We have a schedule! Six tracks. 42 sessions. 43 speakers. Check it out on www.dataweekender.com/schedule ! Also checkout the speaker wall below, click on speaker name to see their bio and session.

icon-company icon-blog icon-linkedin icon-other icon-twitter

#DataWeekender is running on a zero budget. We haven’t accepted any sponsors and we are not charging anyone to attend. Speakers get a Thank You, and bragging rights for getting selected. But nobody is paid anything. That makes marketing a little different than commercial conferences. We also do not have the Sql Saturday platform to channel information to the community. Instead, we are relying on our contacts with User Groups all over Europe and our social media channels.

Therefore, I ask from you to help promoting the conference. The schedule will be up on www.dataweekender.com late tomorrow. Whenever you see information about #DataWeekender in your social media feeds, please help us share the information. We would be very happy if you also make a post on your own about it. The speaker line-up is truly an impressive one, with MVPs, Certified Masters, Microsoft Employees and other amazing Data Platform presenters. Please help us get that known to the Data Platform community. I’m going to be more active than usual on the blog, sharing news not suited for the Twitter limited number of characters.

I hope to see you on May 2!.

No SQL Saturdays, but #dataweekender is online

The forced upon social distancing is new and – to say the least – different! For me, this means I’m exploring my local surroundings (we have really beautiful walking and running paths close to my house) a LOT more than usual. I’m spending my lunches running instead of looking for the best indian restaurants around the office.

Data Community Weekender Europe conference May 2nd, Call for speakers end on April 17th

A big part of my – and thousands of other SQL Server professionals – life is the Microsoft Data Platform Community – the SQL Family. That has obviously also seen some recent changes. I was gonna spek on SQL Saturday in Zagreb and Stockholm, I planned to go to London for SQL Bits and I had ideas for the SQL Server Usergroup which I’m co-leading. None of that happened. Speakers around the world tell the same story.

But instead of letting Nothing replace all of that, new things are happening. I’m speaking in Ohio next week. Or at least I’m speaking to user groups whose members live in Ohio. Online obviously, I’m not travelling to Ohio for a tuesday and a wednesday session. For the swedish SQL Server Usergroup (SQLUG Sweden), we were delighted to have Mark Hayes (T) speak about Power Platform the other day (Thanks Mark!).

Another thing which is happening is that online conferences are popping up. Me and a group of SQL Community organisers decided it’s too booring to just wait for social distancing rules to pass. That’s how Data Community Weekender Europe started. It’s really a crazy idea. From when Call For Speakers page came up, to when the conference happens (May 2nd), it’s roughly three weeks. In that time, we in the organising committee need to market the event to potential speakers, get session submissions in in a timeframe which is one tenth of a normal conference, do session selection, market the event to attendees and get the technical platform in place. Plus potentially recruiting volounteers for event day. But guess what? We WILL do it. We already have some great session submissions in and we’re working out the technical platform. We in the organising committee are all seasoned SQL Community organisers. We have pulled off crazy schedules before, though perhaps not at this scale. But we WILL pull it off. Stay tuned for the attendee registration. It will be announced here and on social platforms.

If you’re a Microsoft Data Platform speaker, send in your submission! It won’t be like attending a physical conference. The speakers’ dinner will be much less crowded, you’ll have to have your post session beer on your own. But you’ll be able to provide your amazing content. So thank you in advance, for submitting to this crazy idea of a conference!!

I spoke on SQL Server User Group Sweden

SQL Server User Group (SQLUG) Sweden had a meeting on October 24th. This one felt a bit special to me, as it was kind of a replay of my first time public speaking, in two ways. My first time speaking in public about SQL Server was on SQLUG Sweden, a few years ago. SQLUG Sweden arranged a Local Community Edition, with local speakers. That first time, it was me and Daniel Hutmacher (Twitter , Blog) speaking. And on October 24th, it was, again, me and Daniel Hutmacher speaking.

Continue reading “I spoke on SQL Server User Group Sweden”

Duplicate key in sysclsobjs using DBCC CLONEDATABASE

This blog post is about an error message I got the other day when using DBCC CLONEDATABASE in a T-sql-script. But first some background to DBCC CLONEDATABASE.

I was pretty excited about the DBCC CLONEDATABASE command, which was introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1. It creates a schema-only (that means all the database objects, but no data) copy of a database, keeping all statistics data, so that you can troubleshoot Query plans for certain queries without having to copy all the data. Before DBCC CLONEDATABASE (and to be honest probably also afterwords, DBCC CLONEDATABASE doesn’t replace all the needs) one had to make a full copy of a database to get the statistics data along. That’s usually copied to a test box. If the test box is identical to your production box, you’re almost fine. But on your test box, you don’t have the cached execution plans from the production box. Therefore, you might end up with very different Query plans in your test box. With DBCC CLONEDATABASE, you get a readonly copy of a database, on your production box and you can use that to tweak your queries and see what new estimated execution plans they get.

Continue reading “Duplicate key in sysclsobjs using DBCC CLONEDATABASE”