A Fine Slice Of SQL Server
Ronald Dameron shows how to change the endpoint URLs for Availability Group replicas:
I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas.
The reason for this blog post is that I could not answer the following questions:
Do I need to suspend data movement prior to making this change?
Would this change require a restart of the database instance?
Now Ronald can, and after reading his post, you’ll be able to as well.
Comments closedAllen McGuire shows how to maintain synonyms between instances in an Availability Group:
I’m a big fan of using synonyms to point to objects in other databases, but if you’ve tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because the instance name is part of the synonym definition. In order for the synonyms to work again, they have to point to the instance they now reside in – assuming the databases were in the same instance, respectively.
The next challenge was to automate the of detection of when the primary has changed, therefore triggering an update of the synonyms. I put the following tSQL into a SQL Agent job and set it to run every five seconds.
Click through for the SQL Agent job details. I have only created a couple synonyms and don’t really like them that much, but if they’re your bag, then at least you know you can use them safely with Availability Groups.
Comments closedA notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents merge complications and problems. For example, the cleanest way to handle the process would be to drop and create the object each time the synchronization runs. If that happened, however, there would be gaps when logins didn’t exist and applications would fail to connect, SQL Agent jobs would lose history, and/or the processing of a job would fail because it was dropped part way through executing.
With that limitation aside, this is a very interesting process and I recommend giving it a careful read. Derik also includes the Powershell script at the end.
Comments closedTracy Boggiano shows off a script which checks Availability Group status of selected servers:
My favorite thing to automate using PowerShell is checking on the status of things on multiple servers. For example, after patching your environment running a quick query to make sure the version number is the same. In this example, we will use a cmdlet my coworker wrote in combination in my cmdlet to check the health of all the Availability Groups across our landscape or you could use it just check one. After all I do consider myself to be an HA/DR nut.
I’ve blogged about my coworker’s Get-CmsHost cmdlet before but now he has and shared it on github so you can read more about here.
In my cmdlet I use the same code that used in the SSMS AG dashboard to check the status of my Availability Groups.
Tracy includes her cmdlet as well as several example calls.
Comments closedAdrian Buckman shows off a few Availability Group DMVs:
Whilst I was combing through various combinations of sys.dm_hadr and sys.Availability I stumbled across a couple of gems that I thought would be nice to share.
sys.dm_hadr_cluster
sys.dm_hadr_cluster_members
sys.dm_hadr_cluster_networks
Read on for usage examples.
Comments closedPaul Randal describes a problem when you create a readable secondary on an Availability Group:
Yesterday I blogged about log shipping performance issues and mentioned a performance problem that can be caused by using availability group readable secondaries, and then realized I hadn’t blogged about the problem, only described it in our Insider newsletter. So here’s a post about it!
Availability groups (AGs) are pretty cool, and one of the most useful features of them is the ability to read directly from one of the secondary replicas. Before, with database mirroring, the only way to access the mirror database was through the creation of a database snapshot, which only gave a single, static view of the data. Readable secondaries are constantly updated from the primary so are far more versatile as a reporting or non-production querying platform.
But I bet you didn’t know that using this feature can cause performance problems on your primary replica?
Definitely read the whole thing.
Comments closedMicrosoft says that turning on TDE (Transparent Data Encryption) for a database will result in a 2-4% performance penalty, which is actually not too bad given the benefits of having your data more secure. There is even more of a performance hit when enabling cell level or column level encryption. When encrypting any of your databases, keep in mind that the tempdb database will also be encrypted. This could have a performance impact on your other non-encrypted databases on the same instance.
In a previous post I demonstrated how to add an encrypted database to an AlwaysOn group in SQL2016. In this article I will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group compared to the same database not-encrypted.
The results aren’t surprising, though the magnitude of the results might be.
Comments closedTracy Boggiano shows how to enhance the built-in Extended Events session for Availability Groups:
Now that the extended events session is setup we can use some queries to query information about our AGs and error messages happening on our servers. First to query when the server failover and becomes your primary server query the event availability_replica_state_change. The first 10 lines just read in the files for the extended event session so you don’t have to identify the exact filenames. Then we parse the xml event for get the timestamp, previous state, current state, repliace name, and group name for the event FROM the filenames we collected before. In the WHERE clause were are looking for when the state has changed to PRIMARY_NORMAL indicating a failover to the server.
Read on for scripts showing what she extends and also how to query this data.
Comments closedDon’t panic, this is still going as planned as this is totally expected and this is the reason why:
We are now in a situation where we have the Primary server running 2017 but one (for us) or possibly more than one for you running on 2016 , its not possible for the 2016 server to synchronize as its databases have not been upgraded yet, they will therefore be stuck in recovery but we are about to fix that very soon.
This is a viable upgrade option: we did it when upgrading from 2014 to 2016. There are a lot of steps, but in the end, it worked fine.
Comments closedThere is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire. This method is my personal choice. It works great and is easy to automate. You can run it with SQLAgent or you can just use Scheduled Tasks in the OS. The scheduled tasks method is a little cleaner, but you don’t get to see it in SQL Server. Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.
Chrissy wrote this with the intent of making migrations easier, and she succeeded. In fact, I made it a point to thank her at MVP Summit last year because it made my life insanely easier. The advantage here is that you can automate a lot more than than just logins. In fact you can migrate and automate pretty much anything at the server level. Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.
DBATools would be my preference in this situation as well, but click through to see four other methods, as well as code.
Comments closed