Microsoft SQL Server

How configure correctly AlwaysOn readable secondary replica for applicationIntent

2013-04-12T16:48:00Z

Hi all,

I would like to share with you an issue that I got recently because I didn't set up correctly AlwaysOn to enable readable database on the secondary replica.

Problem description: When I am trying to connect to my application with .Net or PowerShell, I got the following issue only when I add ApplicationIntent=Readonly on my connection string:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: No connection could be made because the target machine actively refused it..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..

Reproduction: I am able to reproduce with SQL CMD: sqlcmd -S myListener -E -K ReadOnly -Q "SELECT @@SERVERNAME" However, when I remove -K ReadOnly, it works fine.

Other interesting stuff, I can see a login/logout on my primary server inside the profiler trace, but nothing on the secondary

Solution: In my case, the issue was that the read_only_routing port mismatch with the port of my instance. I enclose below what you have to look.

How configure correctly AlwaysOn readable secondary replica:

1. Add Application Intent in the connection string by adding: ;ApplicationIntent=ReadOnly

2. ALLOW_CONNECTIONS property of the secondary replica is READ_ONLY.

3. The READ_ONLY_ROUTING_URL for each replica is set. The TCP port is a port number that is used by the Database Engine of the SQL Server instance

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine1\instanceName1'with
(secondary_role(read_only_routing_url='tcp://myMachine1:1433'))

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine2\instanceName2'with
(secondary_role(read_only_routing_url='TCP://myMachine2:1433'))

4. The READ_ONLY_ROUTING_LIST option must be set for each replica in the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP Transact-SQL command, as part of the PRIMARY_ROLE replica options. The READ_ONLY_ROUTING_LIST can contain one or more routing targets. Multiple routing targets can be configured. Routing occurs in the order that targets are specified in routing list.

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine1\instanceName1'with
(primary_role(read_only_routing_list=('myMachine2\instanceName2')))

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine2\instanceName2'with
(primary_role(read_only_routing_list=('myMachine1\instanceName1')))

Select Replica_Server_name,Endpoint_url,Secondary_Role_allow_Connections_Desc,Backup_Priority, Read_only_Routing_URLfrom Sys.AVailability_replicas

selectdns_name,port,is_conformant,ip_configuration_string_from_cluster FROM sys.availability_group_listeners

5. Then I installed the both KB to fix this issue System.ArgumentException: Keyword not supported: 'applicationintent'.

6. To enable Kerberos, I configured in Active Directory by a domain administrator for each availability group listener name

Configure SPN in AD for each AG Listener name - A Server Principal Name (SPN) must be configured in Active Directory by a domain administrator for each availability group listener name in order to enable Kerberos for the client connection to the availability group listener. When registering the SPN, you must use the service account of the server instance that hosts the availability replica . For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group. (http://msdn.microsoft.com/en-us/library/hh213417.aspx#SPNs)

setspn -A MSSQLSvc/myListenerName.FQDN:1433 myDomain\myserviceAccount

7. Configuring the Windows Firewall to Allow SQL Server Access

If you got any connectivity issues, I invite you to read: How to resolve SQL Server connectivity issues

Regards,

Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |

How to create a dependency between two or more Availability Groups

2013-04-08T16:48:00Z

Hi all,

Problem description: I got a specific scenario where I have to failover two or more AlwaysOn Availability Groups in the same time. To explain by another way, I have to create a dependency between 2 Availability Groups. If one of them failover, the second one has to move also on the same node.

In my case, I have to do that because, I’m not allow to put in the same group all databases. And a third application has to perform a query (write/read) between N databases from two different 2 Availability Groups.

Action plan: As it’s not a normal or expecting behavior, I will share with you, how I did to perform this task.

Step 0: Copy past the script PowerShell below on all nodes involved (SQL_Node_1 and SQL_Node_2 and SQL_Node_3) inside C:\HADRON\failoverAutomaticHadronGroup.ps1

import-modulefailoverclusters
$Cluster_Name = "myWindowsClusterName";

$my_AG1_Name ="AG1";
$my_AG2_Name ="AG2";

# We are looking for the current node of my_AG1_Name
$AG_1 =get-clustergroup -name$my_AG1_Name-Cluster$ClusterName|select-object OwnerNode,State

# then We are looking for the current node of my_AG2_Name
$AG_2=get-clustergroup -name"IngestPub"-Cluster$my_AG2_Name|select-object OwnerNode,State

if($AG_1.OwnerNode.Name -ne$currentTriggerNode -and$AG_2.OwnerNode.Name -eq$currentTriggerNode )
{
          # so we move the group AG_1 to the same node of AG2
          Move-ClusterGroup"$my_AG1_Name"–Node$AG_2.OwnerNode.Name -Cluster$ClusterName ;
}
elseif ($AG_2.OwnerNode.Name -ne$currentTriggerNode -and$AG_1.OwnerNode.Name -eq$currentTriggerNode )
{
          # so we move the group AG_2 to the same node of IngestPub
          Move-ClusterGroup"$my_AG2_Name"–Node$AG_1.OwnerNode.Name -Cluster$ClusterName
}

Step 1: Open eventvwr.msc

Step 2: Go on the chanel: Application and Services Logs\Microsoft\Windows\FailoverClustering\Operationnal

Step 3: Select eventID 1201 (The Cluster service successfully brought the clustered service or application 'XXXXXX' online.)

Step 4: Right-click -> Attach Task to this Event

Step5: Check start a program

Step 6: Select the powershell script of the step 0

Program/script : powershell.exe
Add arguments : -Noninteractive –Noprofile –Command "&{C:\HADRON\failoverAutomaticHadronGroup.ps1}"


NB: To modify the task, open Task Scheduler, please go to the Windows Scheduled Task Microsoft-Windows-FailoverClustering_Operational_Microsoft-Windows-FailoverClustering_1201.

Regards,

Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |

AlwaysOn warning: The current WSFC cluster quorum vote configuration is not recommended for this availablity group

2013-03-29T09:15:00Z

Problem description: You have got odd number of nodes. To prevent possible ties in the quorum vote you added a witness disk to the cluster and adjusted the quorum mode in the cluster manager.

However, when you perform a failover from SSMS on our SQL Server 2012 Availability group, we got a validation WSFC quorum vote configuration warning with action required

Error message: The current WSFC cluster quorum vote configuration is not recommended for this availability group.

Cause: This issue occurs because of a known issue in Windows Server Failover Clustering.

Resolution: To resolve this issue, you have to apply the hotfix below on all computers node of your cluster:

A hotfix is available to let you configure a cluster node that does not have quorum votes in Windows Server 2008 and in Windows Server 2008 R2
http://support.microsoft.com/kb/2494036

Current status: After applied the fix, you should see that the warning is vanished.

Regards,

Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |

Keyword not supported: 'applicationintent'

2013-03-20T11:26:00Z

Problem description: When you try to connect to your SQL Server by using powershell, .Net, sqlcmd... you got this error message:

System.ArgumentException: Keyword not supported: 'applicationintent'.
at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)

Cause: The message occurs when you include ApplicationIntent=ReadOnly (AlwaysOn Availability Groups properties) in your connection string. And the server does not recognize the property.

KB Extract number 2544514: "When the ApplicationIntent property is set to ReadOnly, the client requests a read access operation when it connects to an AlwaysOn-enabled database. The server will enforce the read-only access operation both at connection time, and during a USE database statement, but only to an AlwaysOn-enabled database. When the ApplicationIntent property is set to ReadWrite, the client requests a regular (read-write) access operation when it connects to an AlwaysOn-enabled database. The default setting for the ApplicationIntent property is ReadWrite."

Resolution: You have to install the update that introduces support for the AlwaysOn features from SQL Server 2012

- Update 4.0.2 for Microsoft .NET Framework 4 – Runtime Update

- An update introduces support for the AlwaysOn features from SQL Server 2012 to the .NET Framework 3.5 SP1

Regards,

Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |

8624 Internal Query Processor Error: The query processor could not produce a query plan

2012-12-11T13:50:00Z

Problem description: We got some mini memory dump. In side our SQL error log, we can see the error message below

          Stack Signature for the dump is 0x00000000950A8E26
          External dump process return code 0x20000001.
          External dump process returned no errors.Error: 8624, Severity: 16, State: 116.
          Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
          ex_raise2: Exception raised, major=86, minor=24, state=116,

severity=16, attempting to create symptom dump
ex_raise2: Exception raised, major=86, minor=24, state=116, severity=16

Interesting link: We found out a similar issue. However, I am in SQL Server 2005 SP4 (9.00.5000.00 (X64)), and the issue below was fixed in SQL 2005 SP2. E.G. FIX: Error message when you run a query in Microsoft SQL Server 2005: "Msg 8624, Level 16, State 116 Internal Query Processor Error: The query processor could not produce a query plan"

Cause: We got the query from the dump, but you could also get the query with a profiler trace with the events TSQL SQL:BatchCompleted or Stored procedure RPC:Completed with Error and Waring Exception. We the query we are able to reproduce the issue each time. We saw that the dump was generated by statistics stream corrupted (spotted that in the generation script of the objects)

Resolution: We run an update statitics with fullscan to solve the issue on all objects called by the query.We run again the query and the issue didn't occur.

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Moving SSAS Database to a new drive on same server

2012-10-03T16:30:00Z

Problem description: After you moved SSAS databases to a new drive on the same server, you are not able to administrate Analysis Service. SSAS databases is up and running. When you add a database or user, it appears that users are added successfully, but when going back out of the dialog nothing happened.

Work done: To move you database to a new drive, you followed the step below.

   Step 1: With Microsoft SQL Server Management Studio, connect to Analysis Server, then right Click on the server and click on Properties.
   Step 2: Edit the path of you data directory. And replace the old path by the new one.
   Step 3: Stop the SQL Server Analysis Services Service.
   Step 4: Move the Data to the new directory location.
   Step 5: Start the SQL Server Analysis Services Service.
   Step 6: verify that you can browser all database.

Cause: Inside the SSAS log, you can see an issue relating to something not allowing SSAS to update or access this file

     Message: Service started. (Source: ?\D:\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
     Message: The file '\\?\E:\OLAP\Data\master.vmp' could not be deleted.
     Please check the file for permissions. (Source: ?\D:\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC121001F)

I run Process Monitor and after SSAS restart I saw an access denied on various file and also on master.vmp.

For insformation: The master.vmp file is the master version map that contains the GUIDS for all of the objects and the version of each object that currently exists on the server. When the server starts, it looks at the master.vmp file and need to update it ( More information with my colleague Karan)

Resolution: You can solve this issue you can give the right "Modify" to SSAS service account to this new data folder.

Others points:

1. A database can be moved in many ways (SSMS Attach/Detach, AMO, XMLA). You could find some various way in the bol (Move an Analysis Services Database)
2. You can only move the file regarding Data, Backup, Log or Temp. However it's not supported to move manually the BIN and Config directory. You must reinstall your instance.

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

How a clustered instance of Analysis Services works

2012-09-28T18:33:00Z

Analysis Services is clustered as a generic service resource on a Windows failover cluster. This applies to 2005, 2008, 2008R2, and 2012.

Since SSAS 2005 version, Analysis Services is fully integrated into clustering Setup (the install of SQL):

- SSAS 2005 cluster setup is adding the service on all cluster nodes on the same setup
- SSAS 2008, the setup have to be done node by node Analysis Services is presented to the cluster as generic resource with the type Application Service and doesn’t implement his own explicit IsAlive method. Unlike SQL Server, Analysis Services doesn't have a cluster resource .dll that knows how to do that work like connect to AS and run a simple query to check that it responds.

Possible cause of failure that is detected for an Application Service:

- The service stopped(ie. crash, or was killed) then the cluster resource would notice
- A dependency in the group where the generic service is configured failed (a disk fails or goes offline, an IP, virtual name, mount point, SQL server…other dependencies affecting the group)

Note : Application Service will NOT detect a hang situation in SSAS, where all connections are rejected.

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Invoke-Sqlcmd : The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server

2012-08-18T18:22:00Z

Problem description : The PowerShell command below uses to run every 5 minutes. But since 2 days, I got the error message below : Invoke-Sqlcmd : The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server.

(Invoke-Sqlcmd -ServerInstance $R -Database SSAS_Monitor -Query "INSERT INTO dbo.SSAS_DISCOVER_SESSIONS SELECT '$I', getdate(), * FROM OpenRowset('MSOLAP', 'Data Source=$I;' ,'SELECT * FROM `$system`.DISCOVER_sessions WHERE SESSION_USER_NAME <> ''$proxy''')")

Work done :
-  I had a look to windows event system and application but I didn’t see any error about that.
-  I didn’t see any reboot or new installation of any Hotfix or whatever.
- I can see that provider for 32bit and 64bit are installed for MOLAP.5 (2012 RTM for the both)
-  I can see that provider for 32Bit only was installed for MOLAP.4 (2008 R2 RTM)
-  Process Monitor didn’t give me any clue.
-  I restarted the server just in case, but theissue was still there.

Workaround : It looks like my query is using now MOLAP.4 instead of MOLAP.5 (I guess but not sure). As the version for 2008R2 is installed only in
32bit,the script failed with the message The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server

Step 1 : Unregister the both DLL:

regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

Step 2 : Register the both DLL, with 32Bit in first position:

regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"
regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

Step 3 : Restart SQL Service

After that, everything was working well :-)

Relative links :

- Explanation of Regsvr32 usage and error messages
- How to perform a SQL Server distributed query with OLAP Server

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Migration SQL Server 2000 to SQL Server 2012

2012-06-15T10:00:00Z

You will be not able to migrate from SQL 2000 to 2012 directly. Migration from 2000 to 2012 requires an intermediate step.

SQL Server 2012 supports upgrade from only the following versions: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1.

If you try to restore a backup database from SQL Server 2000, you will get the error number 3169 with the following message: The database was backed up on a server running version %ls. That version is incompatible with this server, which is running version %ls. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

You will have to make the migration in two steps:

- Step 1: Make a first migration from SQL 2000 to SQL 2008 for instance. You need to be SQL 2000 SP4, then follow this step : Migration SQL Server 2000 to SQL Server 2008

- Step 2: Make a second migration from SQL Server 2008 to 2012.

I invite you to read the following bol :

- Supported Version and Edition Upgrades
- Use Upgrade Advisor to Prepare for Upgrades
- Breaking Changes to Database Engine Features in SQL Server 2012

Note : SQL Server Upgrade Advisor is available for 2012 : Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
X86 Package (SQLUA.msi)
X64 Package (SQLUA.msi)

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

Is-it supported to add SSAS 2012 instance to an existing SQL 2012 virtual server group ?

2012-04-17T20:10:00Z

How add Analysis Services to a clustered SQL Server instance ?

It's not supported to Add or Remove features for Clustered instances

SQL Server setup 2012 does not support adding features to an existing failover cluster instance. By using the wizard, you will get the following error message:
- Instance name '<Instance Name>' is already in use. To continue, specify a unique instance name.
- The SQL Server failover cluster instance name '<virtual name>' already exists as a clustered resource. Specify a different failover cluster instance name.

Analysis Services cannot be added to an existing instance of SQL Server. To share a resource group with an instance of SQL Server, you must choose to install Analysis Services during the initial installation of SQL Server. On the same note removing features from a clustered instance is also not supported.

Reference regarding old version: You cannot add or remove features to a SQL Server 2008 or SQL Server 2008 R2 failover cluster

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |