If you’re plugged into our community through Twitter, Facebook, the blogosphere, etc., then you’re probably quite familiar with the repeating cycle we seem to go through as we jump from one dramatic social crisis or hot-button issue to the next, blazing a trail of fire, accusations, slurs and offensive language, and disrespect along the way. I guess that’s to be expected, but that doesn’t make it right. It concerns me…a lot, and it has me asking some tough questions of myself and of our community, and taking a hard look at my involvement and affiliation with it all.

Have we lost our focus as a community? Have we forgotten that this community was founded on principles or helping, supporting, and uplifting each other? Have we become too accustomed to and perhaps jaded or spoiled by our successes? Have we forgotten to treat each other like human beings, with dignity, kindness, and respect… like real people, with real problems (both personal and professional), and perhaps most importantly, with real feelings that can be hurt.

Where do we draw the line between having constructive discussions about hard-hitting issues and being just plain mean, rude, hateful, attacking, inflammatory, slanderous, or offensive? Can we find a balance that lets us be considerate and respectful about what we say and how we say it without being afraid to talk about important things and to stand up for what we feel passionately about? How do we stay true to ourselves, our brands, our voices, our beliefs, and our feelings without trampling on those of others?

Like I said, these are tough questions, and I don’t have answers to most of them. I don’t expect things to get better overnight or even to improve because of this blog post. Part of my reason for writing this is to vent some of my worries and frustrations with what our community is becoming and how we (all of us…myself included at times) are behaving. The other reason I write this is in hopes that at least one person out there will read it and will ask themselves these same questions. I don’t know that I even care what their answers are, really. I just care that they spend some time thinking about these things and evaluating whether they’re moving in the direction they want to be, behaving the way they want others to behave, and coming into this with the right motivations, whatever those may be.

After all, we’re all adults here. We’re better than this, and we know it. We’ve achieved some great things together, and I know we can achieve even greater things together in the future, but we need to stay focused to do it. We need to focus on our community and its goals and mission and on what each of us can bring to the table to help us all grow and learn and improve. We need to reevaluate what is important to us. We need to reevaluate our behaviours and our interactions with others. We need to redefine our core purpose and our core beliefs as a community (even if that just means restating them as they are already), and then we need to focus on living by that purpose and those beliefs. Let’s try not to get distracted by the drama, by the pettiness, by the personalities, by the politics, by all of the things that take us away from helping each other. You see, it’s by helping each other grow that we grow the most ourselves.

I love you, #SQLFamily, really, I do. I think of many of you as close friends and even as part of my extended family. For me, SQL Saturdays and the PASS Summit are like a vacation, a family reunion, and a chance to geek out all rolled into one. Many of you have been mentors to me throughout the years, and I’ve had the privilege of being a mentor to several others as well. That is what makes this community great. That is what drew me in originally, and it’s what keeps me coming back. That is what we need to stay focused on and passionate about. Let’s draw the line here. Let’s make a stand, and let’s make a difference. Let’s start treating others with respect again. Let’s focus back on helping others. Let’s focus on building up our community instead of tearing it down.

With all of that said, I’m extremely proud, honored, and humbled to announce that I have been elected to be the next Chapter Leader and President of the Ohio North SQL Server User Group. Allen left some pretty big shoes to fill, and while I can’t replace him, I’ll certainly do my best to carry the group forward, to continue Allen’s work in mentoring and fostering new speakers and members of our community, and to look for new and creative ways to get others involved in our group and our events and to foster a real sense of ownership in our group. It’s these things specifically that make our User Group one of the best out there. I get asked all the time how we manage to have such an engaged team that follows-through on tasks and routinely puts on such outstanding events, and my reply is always the same: it’s the sense of ownership in the group and in the events and the mentorship and friendship that exists within the group that allow us to be so successful. We’ve got an amazing group of people with an awesome breadth  and depth of skills and insights, and as Chapter Leader, I promise to keep fostering that, and to find ways to engage it to the best of my ability.

Finally, I’d just like to close with a brief listing of the things I’d like to work on this year and into the years that follow in an attempt to keep our group moving forward, growing, and evolving:

• Finalize\restart the process to get 501(c)(3) non-profit status for the group
  • Help move the group bank account over to the non-profit organization, and not have it tied to any one person
• Continue Allen’s work to foster new local speakers
  • Look at how we can adjust the meeting\agenda to better support this (lightning talks, multiple sessions, etc.)
  • Have an open invitation to personally help new speakers with session\abstract reviews and ideas
• Hold board\officer meetings to review the current officer positions and their responsibilities and adjust them as needed
• Use my professional network to attract speakers from other areas to speak at our user group (please let me know if you’re interested; we’d love to have you)
• Work with the other officers to improve the user group website, communications, etc.
• Lead the SQL Saturday planning efforts, or work closely with someone else if they would like to lead the planning efforts
• Create additional roles within the group (like the Sponsorship Coordinator position that I held previously) to allow others to contribute to the user group, if desired
• Represent the user group at the PASS Summit, various SQL Saturday events, other user groups, etc. to exchange knowledge and ideas for running a successful User Group or SQL Saturday event

Thank you to everyone who voted for me tonight, and for letting me lead this awesome group of professionals. I’m looking forward to the year ahead and to some new and interesting challenges.

Sincerely,
Adam Belebczuk
Chapter Leader and President
Ohio North SQL Server User Group

• I finally got to meet Paul Randal, Kimberly Tripp, and Jonathan Kehayias of SQL Skills and I was able to attend Paul’s pre-conference session on Performance Tuning Using Waits and Latches. As expected, the pre-conference session was awesome, as were Paul, Kimberly, and Jonathan.
• I was able to network with other people from the Cleveland area, as well as from around the world, and I was even able to recruit at least one person to apply for a job at my company.
• I was able to attend volunteer meetings and the SQL Saturday Roundtable meeting for the first time and to get some insight into PASS as an organization and how other community members approach their events. In addition, I was able to hear from several sponsors about how the community can better engage them.
• I volunteered as a Summit Ambassador for the first time and was able to assist attendees with finding their way around the conference center.
• I was interviewed by PASS TV to speak about local events and was able to give a shout-out to both the upcoming Cleveland SQL Saturday and Columbus SQL Saturday.
• I presented my Introduction to AlwaysOn Availability Groups session to one of the largest audiences that I’ve ever presented to in-person (it was also video recorded), and even had a Microsoft employee attend my session and offer some insight into where AlwaysOn Availability Groups are heading in future versions of SQL Server. I was also paid a visit by the Demo Gremlins when my demo laptop decided to reboot while I was presenting the first half of my session. Fortunately, I was able to recover from the issue on the fly and still presented a successful, working demo of an Availability Groups (it just had 2 replicas instead of 3 ). My audience was energetic, enthusiastic, and engaged, and I even had a swarm of attendees approach me after my session to say hi and to ask follow-up questions. As a speaker, I couldn’t be happier with how things worked out.
• I was able to visit the Experience Music Project (EMP) museum for the second time for the PASS Community Appreciation Party, and it ROCKED!
• I was also approached by Pluralsight to produce a training video about AlwaysOn Availability Groups, and by Apress to write a book about them as well. We’ll see how these opportunities pan out, but I’m cautiously optimistic about them at this point. *fingers crossed*

As you can see, it’s been a busy, exciting, fun, and exhausting week for me. I’m still blown away by the fact that I was selected to speak and that I was approached by Pluralsight and Apress, and it’s all thanks to PASS and to amazing friends and mentors like Allen White, Erin Stellato, and Brian Davis who got me started as a speaker and helped me grow over these past several year. I’ve said it before, and I’ll say it again: this community has an amazing ability to change peoples’ lives and to help them grow by bring them together for a common cause. It’s been a wild ride, and the best part of it is that it’s only just the beginning.

I still can’t believe it, even as I typed those words and added the graphics from PASS to my site to promote the Summit. I have been selected to present my session “Introduction to AlwaysOn Availability Groups” as a 75 minute regular session at the Summit! It’s hard to believe that I’ve only been a member of PASS for 4 years now, starting with my very first SQL Saturday event in Columbus, OH on June 26th, 2010. Although, even sitting at that first SQL Saturday as a quiet, timid, and more than slightly confused database geek, I knew that PASS would change my life and my career as I knew them, and that has most definitely held true.

You see, it was that first SQL Saturday that introduced me to the concept of local SQL Server user groups, and it was at my local user group that I met and eventually became very good friends with Allen White (twitter | blog) who is now not only a friend, but a mentor to me. At the beginning of every Ohio North SQL Server Users Group meeting, Allen says something to the effect of “Every single one of you has something to share that everyone can learn from.” It’s part of his usual introduction to the meeting, but more than that, it’s a mantra that has inspired me (and many others) and that I have tried to live by over these past 3 years as I’ve spoken at various PASS events.

It really is amazing, as I look back on the last couple years, how much I’ve grown as a database professional, as a speaker, and as a person. I’m more confident, more outspoken and self-assured, and more knowledgeable, largely in part to PASS and the experiences and friendships that I’ve gained from becoming a member of this organization. In fact, if it weren’t for PASS, I would never have met Erin Stellato (twitter | blog), another good friend and mentor who was the connection that ultimately led me to my current job with Hyland Software. It’s funny how such a simple thing as one organization or one event can have such a profound impact on someone’s life, and I know I’m not alone in my experiences. It’s this and this alone that keeps me coming back and keeps me giving back to this awesome organization.

Thank you PASS, and more importantly the volunteers that give up their time and energy to make PASS events happen, for changing my life and giving me an awesome group of people that I’m proud to call my #SQLFamily!

Oh, and did I mention that we have two awesome pre-con sessions available? Why yes, yes we do!

A Day of SQL Server Internals and Data Recovery

Take your recovery game to an all new level. Take a deep dive into SQL Server internals and data recovery and learn how to handle a wide variety of data loss and corruption scenarios. The session will cover how to be prepared for, prevent, and recover data lost due to deletion or corruption.

Learn the following skills in this session:

• Built-in functionality in SQL Server for preventing and detecting corruption that you may not even know about.
• How to identify a specific transaction in the transaction log and recover data lost from that transaction.
• Categories of corruption and how to manage recovery differently for each one.

Don’t come empty handed. Bring your laptop and we’ll practice recovering corrupt databases together.

Speaker Bio:

Argenis Fernandez is a Senior Database Engineer for SurveyMonkey based in Redmond, WA. He has worked with SQL Server for over 15 years and enjoys large SQL Server farms, high-end OLTP databases, managing Windows environments, performance troubleshooting, high availability, disaster recovery, best practices, and PowerShell scripting. Prior to SurveyMonkey, Argenis worked as a Senior Database Administrator for Coinstar/redbox and as a Senior Consultant on SQL Server Core for Microsoft Consulting Services. In 2013 he founded the Security Virtual Chapter for the Professional Association for SQL Server (PASS) (http://security.sqlpass.org).

Argenis is a SQL community enthusiast and speaks frequently at major SQL Server conferences, including the PASS Summit, PASS SQL Rally, IT/Dev Connections, SQLBits, and Microsoft TechEd. He is also a Microsoft Certified Master on SQL Server 2008, an avid Twitter user (you can follow him at @DBArgenis), and occasional blogger on SQL Server topics at SQLBlog.com.

Register for this precon

Automate and Manage SQL Server with PowerShell

This soup-to-nuts all day workshop will first introduce you to PowerShell, after which you’ll learn the basic SMO object model, how to manipulate data with PowerShell and how to use SMO to manage objects. We’ll also cover how to manage data using the Invoke-SQLCmd cmdlet as well as ADO.NET.  We’ll then move on to creating Policy-Based Management policies, work with the Central Management Server, manage your system inventory and gather performance data with PowerShell. We’ll wrap up with a look at PowerShell Remoting and how you can use PowerShell to manage SQL Server 2012 in server environments including Windows Server Core. After this one day, you’ll be ready to go to work and able to use PowerShell to make you truly effective.

Speaker Bio:

Allen White is a Microsoft SQL Server MVP and Practice Leader at UpSearch. Allen has been working with relational database systems for over 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. He currently serves the SQL Server community as President of the Ohio North SQL Server User Group, the Cleveland, OH based chapter of the Professional Association for SQL Server (PASS).  Contact Allen at upsearch.com. Follow Allen on Twitter: @SQLRunr

Register for this precon

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).

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';