• XA 7.12 was running on a SQL 2014 mirrored database. Databases were created using Generate Script option in Studio.
• SQL server was using custom port 10010 for the XA database instance on both principal and partner SQL servers.
• Initial connection after the Studio wizard completed resulted in a connect string being built using Database=SQLServerA\XAInstance,10010 but for Failover Parter the connection string was missing the custom port "Failover Partner=SQLServerB\XAInstance (note missing port)"
• Reconfigured using recommended Test-*DBConnection/Set-*DBConnection=Null,Set-*DBconnection= sequence and the site came back up as expected.
• Attempted manual SQL failover so that ServerB became the principal and now the DDC fails to connect to the database. Failing back to original SQL principal ServerA returns the site to working order.
• Have checked DDC permissions against both principal and partner databases and they appear OK (they were created with the Studio scripts so should be). As an example, Get-BrokerDBConnection results in: Server=ServerA\XAInstance,10010;Initial Catalog=<Site Database>;Integrated Security=True;Failover Partner=ServerB\XAInstance,10010
• We did note that (Test-BrokerDBConnection -DBConnection (Get-BrokerDBConnection)).ExtraInfo returns a Fail for the partner database and doesn't list the custom port.

 Key Value --- ----- Database.Status Normal Database.RecoveryModel Full Database.LastBackupDate 05/08/2017 00:10:01 Database.IsReadCommittedSnapshotOn True Database.Collation Latin1_General_100_CI_AS_KS Database.IsMirroringEnabled True Database.MirroringStatus Synchronized Database.Server.0.Name ServerA\XAInstance,10010 Database.Server.0.ConnectionResult Success Database.Server.1.Name ServerB\XAInstance Database.Server.1.ConnectionResult Fail Database.Server.1.Fqdn ServerB.FQDN.LOCAL Database.AvailabilityGroupName 

Instructions

When using the custom SQL port, the SQL still goes to the default port for communication. In order for SQL to use a specific SQL port, the setting is the TCP Dynamic Ports needs to be modified.

To assign a TCP/IP port number to the SQL Server Database Engine

1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.

   Note

   If you are having trouble opening SQL Server Configuration Manager, see SQL Server Configuration Manager.

2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. (You will probably see both IP version 4 and IP version 6 addresses.) Right-click each address, and then click Properties to identify the IP address that you want to configure.

3. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

      4. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

      5. In the console pane, click SQL Server Services.

       6. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.