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.

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

Feb 19

SP_Send_Mail Common Settings

There are some common setting that I find myself using when sending mail via the sp_send_dbmail command. I like these two features the best:

  1. @blind_copy_recipients
  2. @from_address, which you can use to change the sender’s display name
    • This feature (variable) is only supported in 2008 and above.

I’ve included an example of what I usually include in my send_mail calls:

Feb 15

SQL 2012 F5 Keyboard Shortcut Refresh

For some unknown reason, Microsoft decided to change the global refresh key (F5) in SQL 2012 to debug. This has caused me countless headaches when trying to refresh a database in the Object Explorer. Here is how I went about changing F5 from debug to refresh:

  1. In SQL Server Management Studio, click Tools, then click Options
  2. Under Environment, expand Keyboard then select Keyboard nested underneath:

  3. Search from View.Refresh and highlight.
  4. Under Press shortcut keys: press F5:

  5. Click Assign, the screen should look like this:

  6. Click OK and you’re done!

Stupid debug