PASS Summit 2014 Recap

Image

Just moments ago, the PASS Summit 2014 wrapped up, and will be forever in the history books. When I got back to my hotel room, I started thinking about the event, the awesome people that I met for the first time, the old friends that I was able to reconnect with, the completely mind-blowing experience of being a first time speaker at the Summit, and everything else that has gone on over just one short week. I quickly realized that I wanted to take a moment to blog about it and share some of my experiences, but to also act as a journal of sorts. Below are just some of the awesome things that I’ve done, or that have happened to me this week:

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

PASS Summit 2014 – I’m Speaking!

Summit14 - I'm SpeakingWell, with a title like that, I think the cat’s out of the bag…I’M SPEAKING AT THE PASS SUMMIT!

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!

SQL Saturday Cleveland 2014

Cleveland SQL SaturdayHappy New Year, and welcome to another exciting year of SQL Server learning! With that said, why not join us at Cleveland’s 3rd SQL Saturday on February 8th at Hyland Software? The weather may be cold and gray outside, but I promise you it’ll be warm, inviting, and fun at our event! This year I’ve decided to step into the captain’s seat and lead the planning efforts for SQL Saturday, and it’s been a blast! We have a ton of awesome speakers and experts lined up like Tom LaRock, Steve Jones, Tim Ford, Grant Fritchey, Kendal Van Dyke, Argenis Fernandez, Stacia Misner, Andy Leonard, and Erin Stellato, just to name a few. Check out the full line-up here! In addition to our amazing speaker line-up, we’ll also have some fun activities and experts available to answer your questions. Believe me, you don’t want to miss this event!

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

Introduction to SQL Server 2012 AlwaysOn Availability Groups – Q & A

Last week, I presented my session “Introduction to SQL Server 2012 AlwaysOn Availability Groups” to my largest audience ever at the PASS DBA Fundamentals Virtual Chapter. There were 191 total attendees, and I would like to take a moment to thank all of you for attending, it was truly AWESOME! Also, I would like to take a moment to apologize for the audio issues that occurred throughout the session. This was primarily my fault, as I had joined the webinar twice, once as a presenter by phone with a high-quality headset and good quality audio connection, and another time as an attendee just to keep an eye on what all of you were seeing. Unfortunately, the attendee laptop was somehow selected as the microphone to be used while I presented from my actual presenter laptop, and that is why the audio kept fading in and out and was poor quality. Mark, Mike, and I met to discuss this and how to prevent it in the future, and so this should not happen again.

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

Service Broker Replication – Using AlwaysOn Availability Groups with Service Broker Replication

Service Broker Replication – Table of Contents

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

Slacking Off…or…Adjusting To Change

If you’re reading this, then I owe you some credit as a committed follower of my blog. :-) In addition to some credit, I also owe you an apology. As you may or may not know, I started a new job back in November, and since that time I’ve been slacking off on my blog posts and speaking engagements. For that, I am truly sorry. However, I do have some good news: Now that I’ve had over 4 months to adjust to these changes, I’ve finally decided to get off of my lazy butt and start blogging and presenting again!

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!

Creating a Login with a SID

For those of you who don’t already know, there is a new PASS Virtual Chapter called DBA Fundamentals that has meetings on the first Tuesday of every month. The chapter specializes in providing training and support for new DBAs and also for seasoned DBAs who would just like to brush-up on their skills. I was fortunate enough to be asked to present at their second meeting on 9/4, and I gave my session about SQL Server Service Broker. One of the questions that I was asked during the session was: How do you create a login with a SID, and more specifically, how do you create and find the SID for that login?

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;