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.

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:

     

 

Jun 14

Cannot generate SSPI context

I received this error message: “Cannot generate SSPI context” while attempting to connect to a SQL server:

The server had DHCP enabled so the address had the potential to be different every time the machine restarted.

Solution: This was an easy one…using the Fully Qualified Domain Name for the connection string allowed me to connect to the server. Another solution would be flushing DNS on client attempting to connect.

Apr 26

GRANT Schema Permissions

Background: I had a user that needed to be able to read, but not execute, stored procedures in the DBO schema. I used these methods below to achieve this task.

There are two methods to granting schema permissions in SQL server.

  • One is through T-SQL commands:

Mar 14

Cannot alter user dbo

I was recently faced with the task to alter a SQL user’s database account; more specifically speaking  a Login name’s database user name. I ran into this error:

Cannot alter the user dbo. It seems as if SQL server is upset if you attempt to alter the account of the database owner (dbo). To get around this you will need to change the dbo to another account to modify the original user. Use the following code to change the database owner:

 

Feb 22

Backup Detected Log Corruption

I’ve been dealing with several data corruption issues for the last few days. It all started with a loss of a clustered disk and when it was brought back online there were a few Suspect and Recovery Pending databases. Here is how I was able to solve that issue: Database in Suspect or Recovery Pending State

For a few other database CHECKDB returns no error for and FULL backups complete successfully; but when performing transaction log backups we receive this error:

Along with these error in the SQL Server Logs:

No need to stress…Take a deep breath. There is a simple solution for this issue, and it turns out there is no data loss occurring on the database. Here are the steps:

Continue reading

Feb 18

Cannot Connect to WMI Provider

Today a client came to me with this error:

When running SQL Server Configuration Manager he received this error: “Cannot Connect to WMI Provider. You do not have permission or the server is unreachable.

I’ve handled this type of case before and a simple restart for the SQL server instance solved the issue, but that wasn’t the case today.

It’s important to note that this machine was 64-bit and the solution was to use the x86 (32-bit) shared components of configuration manager.

Solution:

  1. Open a CMD prompt, and Run as Administrator.
  2. Navigate to the shared components directory of the instance.

    • May vary based on you configuration and version.
    • UPDATE: If the mofcomp.exe file does not exist in the shared directory see instructions below.

  3. Run the following command (This will vary depending on your version on SQL server):

    • May vary based on you configuration and version.
You should see a message similar to the following:

UPDATE:

If the mofcomp.exe file does not exist in the SQL shared directory server perform a search for the file in the following directory: C:\Windows\winsxs

The result will be something similar to this: C:\Windows\winsxs\wow64_microsoft-windows-wmi-core.resources_31bf3856ad364e35_6.1.7600.16385_en-us_3b112a3415ba87ce

Replace the location for Step # 2 with the results of the search. Example:

Feb 13

Database in Suspect or Recovery Pending State

Today one of the SQL 2008 R2 clusters that I manage lost a shared disk. The disk happened to be the DATA directory for SQL server. When the disk came back online it performed a chkdsk, and determined that there was corruption on the disk. After logging into the SQL instance I found a database in a Suspect state and another in a Recovery Pending State.

Losing a disk is a scarring thing, especially when the databases on that disk don’t come back online in a healthy state. The resolution is fairly strait forward for both situations:

  1. Set the database into EMERGENCY mode and turn on SINGLE USER mode:
  2. Check the database for potential errors:
  3. At this point, based on the errors you received, you need to decided what course of action need to be taken. Errors regarding loss of data might constitute restoring the database from backup; on the other had errors regarding bad indexes and partitions you might consider allowing DBCC CHECKDB to correct the issues. Ultimately the end of the DBCC CHECKDB results will tell the you minimum repair level to correct the issues that it discovered. Use the command below that corresponds to the results of your CHECKDB:
  4. After CHECKDB is complete the database should be in a healthy state. Now you can bring it back to multi-user mode:

  5.