Failed to Create Availability Group, SQL Server error 41042 encountered

Wow, it's been close to six months since my last post!!! Where has the time gone?

Anyway, I ran into an issue that no amount of Google-fu seemed to help me with. The issue was "Microsoft SQL Server, Error: 41042". I ran into this problem trying to create an Availability Group using the name of an AG that had been previously deleted, but appears to have not been completely removed. Here's a screengrab for the error:

As stated, this was an AG name for an AG that had existed and was gone from SQL Server.... but hadn't disappeared from the "Failover Cluster Manager." So I deleted the AG's service manually from the Failover Cluster manager.  

This appeared to do the trick, but then I got the error. So what to do????? As I mentioned, I checked google profusely to no avail. And after thinking for a bit, decided that this had to be a registry issue. I did some googling to determine where SQL Server Availability Group Registry keys live, and found the following excerpt from http://msdn.microsoft.com/en-us/library/ff929171.aspx:

In the registry I navigated to HKLM\Cluster\HADRAgNameToIDMap, and found the AG name I was trying to use. I deleted the key, and reran the AG wizard and.... still no luck. So I decided to do this on every node that had been a part of the AG and... Voila!!!! IT WORKED!!!


Now remember, use this at your own risk.

Availability Group Listener's IP address keeps changing between Online and Offline address

I've been testing Multi-Subnet Availability Groups and I ran into a peculiar situation which I hadn't experienced before; the IP address kept flipping between the offline and online IP address for the resource.

I was a little perplexed, I had tested Multi-Subnet AGs on our old Colocation, and was now testing it with our new (and improved) colo. I tried lots of things, but it came down to one cluster resource setting for my AG... RegisterAllProvidersIP.

When this setting is set to 1, any IP address that is associated with the clustered resource. As shown below:

This means that dns will return either IP address, and your client could fail connecting if the offline IP address is returned by DNS after the TTL timer expires.

By setting RegisterAllProvidersIP to 0, only the online IP address is registered. Success!!

version of SQL Server instance MSSQLSERVER does not match the version expected by the SQL Server update

While trying to patch SQL Server 2008 R2 SP1 CU4 with CU6, I got the following prompt

It specifically says the below:
"The version of SQL Server instance MSSQLSERVER does not match the version expected by the SQL Server update. The installed SQL Server product version is 10.50.1600.1, and the expected SQL Server version is 10.51.2500.0"


This error says that I don't have SQL Server SP1 installed. This is rather interesting as I know for a fact I have SP1 installed. What to do?!!

Well I decided to reapply SP1 and see if there was a repair option. There wasnt a repair option, but on the config screen, I saw it had "Incompletely Installed" as the Upgrade Status. I ran it, and it quickly completed.

After the install completed, I retried to apply the latest CU patch, and voila!! It worked!!.

Ports needed to connect to remote SQL Server Instance

I've been working with our System Center Configuration Manager Engineer in getting his Configuration Manager Distribution Points working after connecting to a Configuration Central Management Point. Everything has been working fine, except our Development Environment Distribution Point cannot connect to our Central Management Point (CMS) database.

We've been at it for a week. The CMS db is located on a SQL Server 2008 R2 named instance in our Production Environment, and the Distribution point is located in our development environment.

The following works when trying to connect using SSMS:
Server
Server\Instance,port

Does not work:
server\instance

This was driving me nuts as the distribution point uses server\instance to connect. And according to our Config Manager Engineer, there isnt a way to specify... which may or may not be true. So I did my due diligence and discovered that on a named instance, if using server\instance as your connection string, then the requesting server goes through UDP port 1434 to request connection variable data regarding your sql named instances.

We opened UPD port 1434, and voila. It works!!

Blog that helped me figure it out:
http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

Installing Microsoft Dynamics AX 2012 on SQL Server 2012

With SQL Server 2012's release this past April, we knew we would have to prepare for our future deployment of Microsoft Dynamics 2012 to run on it. We had successfully upgraded existing versions of SQL Server 2008 R2 supporting Dynamics 2012 to SQL Server 2012. But one thing we never tested was installing Dynamics 2012 directly onto SQL Server 2012.

I set up all the pre-requisites for the Dynamics AX. The DB successfully installed and then I got the following error on the AOS install:
An error occurred during setup of Application Object Server (AOS) MicrosoftDynamicsAX at port 2712.
The following error/warning occurred:Cannot find the object 'UTILIDELEMENTS', because it does not exist or you do not have permission. Cannot find the object 'UTILELEMENTS', because it does not exist or you do not have permission. Cannot find the object 'UTILMODELS', because it does not exist or you do not have permission.
Hmmm.... interesting as I know we've successfully gotten these running on SQL Server 2012 before. I did a bit of google-fu and saw someone listing as needing to run Dynamics AX 2012 patch listed as KB2680186. Luckily we had this in our share. I ran that update, and noticed that it didnt actually update a client, but rather installed an update of the "Installation Support Files".

Once that completed, I rebooted my server and voila. It installed.
-Victor


Edit: I had used version 6.0 of the Dynamics AX 2012 installer. Although I was able to proceed, I got various errors that I couldnt get past. I ended up undoing the AOS install and db install.

I then redid the AOS and DB install with version 6.1, which was patched to run with SQL Server 2012 out of the box, and lo and behold, IT WORKED!!!!!

Drop User Failed - User has granted one or more permission(s). - Part 2

Once again, I ran into the same issue as my previous post on a different server. Trying to drop a user, but not able to because "User has granted one or more permission(s)."

previous post:
http://becomingexceptional.blogspot.com/2012/04/drop-user-failed-user-has-granted-one.html

This time around though, there was one caveat, the endpoint was in use. I ran the same steps to determine what the endpoint was and what it was named.

So I had to change endpoint ownership to our SQL service account. But how to do it? I googled alter endpoint owner and found the following technet article:
http://technet.microsoft.com/en-us/library/ms187359.aspx

ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO 'domain\svcaccount';
GO

I ran it, and I was now able to successfully delete the user.

Drop User Failed - User has granted one or more permission(s).

Today I was tasked with cleaning up our security logins, and one of the users came up with the following error:

I wasn't quite sure what to do. After a little google-fu, I found the following script:

select * from sys.server_permissions

where grantor_principal_id =
(select principal_id from sys.server_principals where name = 'domain\user')

And got two results:

This told me the user owned an endpoint, but which one? The previous query only gave me the major_id which was 65536.

Luckily, I found the following: http://technet.microsoft.com/en-us/library/ms180076.aspx

So I ran SELECT * FROM sys.endpoints and got the following results:

I saw endpoint_ID 65536 and found it was a mirroring endpoint. A mirroring remnant of mirroring that was once set up, but was now gone. So all I had to do was drop that endpoint with:

DROP ENDPOINT Mirroring

And I was good to drop the user.

Fixing A Broken SQL Config Manager

I've had to deal with a broken SQL Configuration Manager four times in the past four months. The first time was a bit nerve-wracking as I didn't quite know what to do.
You'll know that it's broken when you get the following prompt after trying to open "SQL Configuration Manger":

Turns out all I had to do was run the following in a command prompt to get the Config Manager up and running:
mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

And voila it's fixed.

Check: http://support.microsoft.com/kb/956013 for full technical details.

P.S. If Avamar can't view databases on a server, this will fix it. Thanks to John Habu for figuring that out with me.

On the road to Execptional DBA

My very first blog, dated January 23rd, 2012. I've probably missed the blog bandwagon by many years, but that's fine by me, I finally have something I want to write about; My road to becoming an "Exceptional DBA." For those that don't understand what this is, please Google "How to Become an Exceptional DBA" by Brad M. McGehee.

March 2011 was the month I started working with SQL Server. It's been close to a year now, and I'm much more comfortable in my role today than I was then. It's definitely been an interesting road, some highs and some lows. But through it all, I've continued on my path. I know there's a vast array of SQL Server knowledge, but I'm young and willing to learn. I'm also lucky to have a great mentor, Ivan Bermudez (http://hollywoodsql.blogspot.com/).

My current goal is to get my first SQL certification, the MCTS "Microsoft SQL Server 2008 - Implementation and Maintenance" certification. SQL Server 2012 will be coming out later this year, so I'll upgrade my certs when that date arrives.

Wish me luck!!!