One of my least favorite things about Availability Groups
Well, really, this goes for Mirroring and Log Shipping, too. Don’t think you’re special just because you don’t have a half dozen patches and bug fixes per CU. Hah. Showed you!
Where was I? Oh yeah. I really didn’t like the backup and restore part.
You find yourself in an awkward position
When you’re dealing with large databases, you can either take an out of band COPY_ONLY backup, or wait for a weekly/daily full. But, if you’re dealing with a lot of large databases, chances are that daily fulls are out of the question. By the time a full finishes, you’re looking at a Whole Mess O’ Log Restores, or trying to work a differential into the mix. You may also find yourself having to pause backups during this time, so your restores aren’t worthless when you go to initialize things.
You sorta-kinda got some relief from this with Availability Groups, but not much. You could either take your backups as part of the Wizarding process (like Log Shipping), figure it out yourself (like Mirroring), or defer it. That is, until SQL Server 2016.
Enter Direct Seeding
This isn’t in the GUI (yet?), so don’t open it up and expect magic mushrooms and smiley-face pills to pour out at you on a rainbow. If you want to use Direct Seeding, you’ll have to script things. But it’s pretty easy! If I can do it, anyone can.
I’m not going to go through setting up a Domain Controller or Clustering or installing SQL here. I assume you’re already lonely enough to know how to do all that.
The script itself is simple, though. I’m going to create my Availability Group for my three lovingly named test databases, and add a listener. The important part to notice is SEEDING_MODE = AUTOMATIC. This will create an Availability Group called SQLAG01, with one synchronous, and one asynchronous Replica.
CREATE AVAILABILITY GROUP [SQLAG01] FOR DATABASE [Crap1], [Crap2], [Crap3] REPLICA ON N'SQLVM01\AGNODE1' WITH (ENDPOINT_URL = N'TCP://SQLVM01.darling.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY), SEEDING_MODE = AUTOMATIC), N'SQLVM02\AGNODE2' WITH (ENDPOINT_URL = N'TCP://SQLVM02.darling.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY), SEEDING_MODE = AUTOMATIC), N'SQLVM03\AGNODE3' WITH (ENDPOINT_URL = N'TCP://SQLVM03.darling.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY), SEEDING_MODE = AUTOMATIC); GO ALTER AVAILABILITY GROUP [SQLAG01] ADD LISTENER N'SQLAGLISTEN01' ( WITH IP ((N'18.104.22.168', N'255.255.255.0')), PORT=6000); GO
The next thing we’ll have to do is join our Replicas to the AG with the GRANT CREATE ANY DATABASE permission. I prefer to do this in SQLCMD mode so I don’t have to change connections manually.
:CONNECT SQLVM02\AGNODE2 ALTER AVAILABILITY GROUP [SQLAG01] JOIN GO ALTER AVAILABILITY GROUP [SQLAG01] GRANT CREATE ANY DATABASE GO :CONNECT SQLVM03\AGNODE3 ALTER AVAILABILITY GROUP [SQLAG01] JOIN GO ALTER AVAILABILITY GROUP [SQLAG01] GRANT CREATE ANY DATABASE GO
And uh, that was it. I had my AG, and all the databases showed up on my two Replicas. Apart from how cool it is, it’s sort of anti-climactic that it’s so simple. People who set their first AG up using this will take for granted how simple this is.
What’s really nice here is that when you add new databases, all you have to do is add them to the Availability Group, and they’ll start seeding over to the other Replica(s). I need to do some more playing with this feature. I have questions that I’ll get into in another post in the future.
CREATE DATABASE [Crap4] GO ALTER AVAILABILITY GROUP SQLAG01 ADD DATABASE [Crap4]; GO
These are empty test databases, so everything is immediate. If you want to find out how long it will take to Direct Seed really big databases, tune in to DBA Days Part 2. If anyone makes a SQL/Sequel joke in the comments, I will publicly shame you.
Thanks for reading!
Update! The Man With The PowerShell Plan himself, Mike Fal, also wrote about this feature for T-SQL Tuesday. Check it out.
Brent says: wanna see this capability get added to SSMS for easier replica setup? Upvote this Connect item.