Apr 29

AlwaysOn Multi-Subnet Configuration

There are several ways to configure AlwaysOn, and even more ways to configure networking behind the cluster. A very common configuration I’ve setup and seen in production environment is a Active site and Passive disaster recovery site. In this scenario the cluster configuration needs the ability to bring the AlwaysOn resource online, even if it will never have the need to actually do that. Because of this you will need to assign an IP address from all subnets to the AlwaysOn group. This can potentially cause problems with DNS if all addresses are registered to the CNAME. There are some steps to overcome this here. In summary this is what action I take on my clusters:

  1. Open PowerShell with “Run as Administrator”
  2. Run command to list all clustered resources. We are focused on the AlwaysOn listener name:

  3. Run the following command to unregister the IP address that are not in use:
  4. Restart the Availability Group clustered resource.
Apr 25

Failover Clustering Tools Install Failure | Object reference not set to an instance of an object

There have been several times I’ve attempted to install Failover Clustering Tools on a server and received the following error: “Object reference not set to an instance of an object”

Here are the steps I’ve taken in the past to correct this:

  1. Open PowerShell with “Run as Administrator”
  2. Run this command:
  3. Reboot

Additional instructions can be found here.

Dec 01

Backup Restore Progress

I cannot take credit for this script, but I use it all the time:

 

Source

Nov 18

Group Managed Service Accounts (gMSA) for SQL services

How to run SQL services using a Group Managed Service Account (gMSA). The process is very similar to using a Managed Service Account, here are the steps to get this up and running:

  1. Open a PowerShell prompt with “Run as Administrator”.
  2. Run the following commands:
  3. Lastly in PowerShell, run this command (replacing ACCOUNTNAME with your Group Managed Service Account):
  4. Open SQL Server Configuration Manager and select SQL Server Services.
  5. Open the Properties of the service you wish to edit.
  6. Under the Log On tab, Browse or type in the gMSA name. The gMSA should end with a $. The password field should remain blank:
  7. A prompt will appear asking for confirmation. Select Yes.
Oct 15

SQL Job Runtime

Recently I received a request to gather run-time information about a particular SQL scheduled job. They wanted to know how long a specific job was running. I started digging through the msdb.sysjobhistory table and found some strange values for the columns. After searching the Internet I wrote this query that fit my needs:

 

Apr 22

Always-On: Encrypted using a compatible algorithm Error

When configuring always on in an a few environments I have come across a this error a few time:

Checking whether the endpoint is encrypted using a compatible algorithm:

The configuration of endpoint data encryption is incompatible between replicas and the endpoint connection will fail. The current configuration is listed below with following order: the name of replica, role of replica, encryption and algorithm

 

This isn’t as big of an issue as it looks like. The portion in red above is the part you need to pay attention to. The error is stating that the server you are trying to add to the availability group has default Mirroring endpoint that is using a different encryption then the type that the servers in the availability group are using. A simple fix is to modify the new server’s endpoint encryption to match the existing replicas setup. In this case the first two servers were using AES and the new server is using RC4. Here are the steps:

  1. In SSMS expand Server Objects, then Endpoints, then Database Mirroring.
  2. There should be an endpoint named Mirroring here, right click on this endpoint and Script Endpoint as->Create to->New Query Editor Window:
  3. Modify the script from Create to Alter and modify the Required Algorithm to match the existing replicas:

     

 

Feb 10

Cursor Template

Most of the time when I use a cursor I find myself using the same template. I am always writing the same code so I figured I’d save myself 3 minutes each time I use a cursor and store the code here:

 

Feb 05

Permission to Read SQL Logs

Recently we had a group of DBA who have limited access on an instance. They wanted to view the SQL logs but were unable. I found it difficult to find a clear solution for this, but after some trial and error here is what I came up with:

Even after this you might get a message: The user does not have permissions to perform this action Error: 297.

In order to correct this you must grant VIEW SERVER STATE to the login:

Now the user or group should have access to view the SQL error logs.

Jan 23

Check for Sysadmin

I needed a way to check for the sysadmin role on an instance. I came up with this small query that does that: