{ "metadata": { "kernelspec": { "name": "powershell", "display_name": "PowerShell", "language": "powershell" }, "language_info": { "name": "powershell", "codemirror_mode": "shell", "mimetype": "text/x-sh", "file_extension": ".ps1" }, "azdata_notebook_guid": "6CEA0362-E666-4456-B88F-CAFCC74E749C", "extensions": { "azuredatastudio": { "version": 1, "views": [] } } }, "nbformat_minor": 2, "nbformat": 4, "cells": [ { "cell_type": "markdown", "source": [ "# MSSQLSERVER\\_35250\n", "\n", "Applies to: ![yes](https://docs.microsoft.com/en-us/sql/includes/media/yes-icon.png?view=sql-server-2016)SQL Server (all supported versions)\n", "\n", "## DETAILS\n", "\n", "| Attribute | Value |\n", "| --- | --- |\n", "| Product Name | SQL Server |\n", "| Event ID | 35250 |\n", "| Event Source | MSSQLSERVER |\n", "| Component | SQLEngine |\n", "| Symbolic Name | HADR\\_PRIMARYNOTACTIVE |\n", "| Message Text | The connection to the primary replica is not active. The command cannot be processed. |\n", "\n", "### [](https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-35250-database-engine-error?view=sql-server-2016#explanation)" ], "metadata": { "azdata_cell_guid": "c7e761ea-489e-4e51-b4a9-c6fa79e0fae8", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "markdown", "source": [ "**Please make sure to change the values of these variables to match your environment**" ], "metadata": { "azdata_cell_guid": "7d365faf-7c43-4cd0-ac74-ad6a0f8e75cf", "extensions": { "azuredatastudio": { "views": [] } } }, "attachments": {} }, { "cell_type": "code", "source": [ "$ServerName = \"MyServer\" #replace with server name or virtual name in dobule-quotes \"MyServer\"\r\n", "$server_IP_address = \"192.168.2.27\"\r\n", "$port_number = 1433\r\n", "$DBName = \"master\"\r\n", "[string] $SQLInstance= $ServerName + \"\\\" + \"sql2017\" # please replace with server\\instance format. For default instance, just use $ServerName\r\n", "\r\n", "\r\n", "#create the SQL function to use throughout\r\n", "function ExecuteSqlQuery ($SQLInstance, $DBName, $SqlQuery) \r\n", "{\r\n", " \r\n", " $SqlConnection = New-Object System.Data.SQLClient.SQLConnection\r\n", " $SqlConnection.ConnectionString = \"server='$SQLInstance';database='$DBName';trusted_connection=true;\"\r\n", " $SqlConnection.Open()\r\n", " $SqlCmd = New-Object System.Data.SQLClient.SQLCommand\r\n", " $SqlCmd.Connection = $SqlConnection\r\n", " $SqlCmd.CommandText = $SQLQuery\r\n", " $Result = $SqlCmd.ExecuteReader()\r\n", " $DT = New-Object System.Data.DataTable \r\n", " $DT.Load($Result)\r\n", " $SqlConnection.Close() \r\n", " return $DT\r\n", "}" ], "metadata": { "azdata_cell_guid": "19304c57-1660-4a71-9b47-aea65355e375", "tags": [], "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "### Explanation\n", "\n", "This message occurs when attempting to join secondary databases to an Always On availability group. Inability to connect to the endpoint can typically cause this error.\n", "\n", "### [](https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-35250-database-engine-error?view=sql-server-2016#user-action)User Action\n", "\n", "> NOTE \n", "> All the following steps must be run on both the Primary replica and the problematic Secondary replica(s).\n", "\n", "1. Ensure the endpoint is created and started. Run the following query to discover the endpoint." ], "metadata": { "azdata_cell_guid": "478c72f4-fcf7-4867-b583-28559ba8f2da", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "clear\r\n", "\r\n", "\r\n", "[string] $SqlQuery= $(\"SELECT\r\n", " tep.name as EndPointName,\r\n", " sp.name As CreatedBy,\r\n", " tep.type_desc,\r\n", " tep.state_desc,\r\n", " tep.port\r\n", " FROM\r\n", " sys.tcp_endpoints tep\r\n", " INNER JOIN sys.server_principals sp ON tep.principal_id = sp.principal_id\r\n", " WHERE tep.type = 4\")\r\n", "\r\n", "$DT = New-Object System.Data.DataTable\r\n", "$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "\r\n", "#print the results\r\n", "$DT | Format-Table\r\n", "\r\n", "Write-Host (\"The Resultset contains: \" + $DT.Rows.Count + \" rows\")\r\n", "" ], "metadata": { "azdata_cell_guid": "4dcc5adc-8374-482b-b356-f1f00ac87edb", "tags": [], "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "> WARNING\n", "> Use caution when executing the next command as it can cause a momentary downtime for the replica.\n", "\n", "You can use these commands to restart the endpoint you discovered\n", "\n", "**Please make sure to change the values of the $endpoint variable to match your environment**" ], "metadata": { "azdata_cell_guid": "a5b7ba59-f455-447f-a328-7e238549d43c", "extensions": { "azuredatastudio": { "views": [] } } }, "attachments": {} }, { "cell_type": "code", "source": [ "\r\n", "$endpoint = \"TSQL Default VIA\" #please replace with valid AG/DBM endpoint\r\n", "\r\n", "[string] $SqlQuery= \"ALTER ENDPOINT [\" + $endpoint + \"] STATE = STOPPED; ALTER ENDPOINT [\" + $endpoint + \"] STATE = STARTED\"\r\n", "$DT = New-Object System.Data.DataTable\r\n", "#$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "\r\n", "#check status of endpoint\r\n", "$SqlQuery= \"SELECT * FROM sys.endpoints WHERE name = '\" + $endpoint +\"'\"\r\n", "$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "\r\n", "$DT | Format-Table\r\n", "\r\n", "#validate the data\r\n", "Write-Host (\"The table contains: \" + $DT.Rows.Count + \" rows\")\r\n", "" ], "metadata": { "azdata_cell_guid": "637f86e1-5db5-4a98-bb85-272ceb6825a5", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ " 2. Check if you can connect to the endpoint.\n", " \n", "\n", "- Use Telnet to validate connectivity. Here are examples of commands you can use:" ], "metadata": { "azdata_cell_guid": "09872772-70da-45f8-ab21-4c5a9be5cbb8", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "Test-NetConnection -ComputerName $ServerName -Port $port_number\r\n", "Test-NetConnection -ComputerName $server_IP_address -Port $port_number" ], "metadata": { "azdata_cell_guid": "471cdee8-05e9-47da-9600-fa93054b6877", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "- If the Endpoint is listening and connection is successful, then you will see a blank screen.  If not, you will receive a connection error from Telnet\n", "- If Telnet connection to the IP address works but to the ServerName it does not, there is likely a DNS or name resolution issue\n", "- If connection works by ServerName and not by IP address, then there could be more than one endpoint defined on that server (another SQL instance perhaps) that is listening on that port. Though the status of the endpoint on the instance in question shows \"STARTED\" another instance may actually have the port binding and prevent the correct instance from listening and establishing TCP connections.\n", "- If Telnet fails to connect, look for Firewall and/or Anti-virus software that may be blocking the endpoint port in question. Check the firewall setting to see if it allows the endpoint port communication between the server instances that host primary replica and the secondary replica (port 5022 by default). Run the following PowerShell script to examine for disabled inbound traffic rules\n", "- If Telnet fails to connect, look for Firewall and/or antivirus software that may be blocking the endpoint port in question. If you are running SQL Server on Azure VM, additionally you would need to [ensure Network Security Group (NSG) allows the traffic to endpoint port](https://docs.microsoft.com/en-us/azure/virtual-machines/windows/nsg-quickstart-portal#create-an-inbound-security-rule). Check the firewall (and NSG, for Azure VM) setting to see if it allows the endpoint port communication between the server instances that host primary replica and the secondary replica (port 5022 by default)." ], "metadata": { "azdata_cell_guid": "f8024f18-343e-4ab5-bec2-57408c90e466", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "Get-NetFirewallRule -Action Block -Enabled True -Direction Inbound| Format-List" ], "metadata": { "azdata_cell_guid": "97d5a981-8e3b-4a16-a7a8-fdefada3ecb9", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "- Capture a NETSTAT -a output and verify the status is a LISTENING or ESTABLISHED on the IP:Port for the endpoint specified." ], "metadata": { "azdata_cell_guid": "be46d341-f98f-4c16-98c9-9e7a2dcb5409", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "Get-NetTCPConnection -LocalAddress $server_IP_address" ], "metadata": { "azdata_cell_guid": "7d445c69-733c-46e1-9cc9-9e789bbd6809", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "3.  Check for errors in the system. You can query the **sys.dm\\_hadr\\_availability\\_replica\\_states** for the last\\_connect\\_error\\_number that may help you diagnose the join issue. Depending on which replica was having difficulty communicating, you can query both the primary and secondary:" ], "metadata": { "azdata_cell_guid": "4db7a8de-33a9-4596-b80e-66b904d016c4", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "[string] $SqlQuery= $(\"select\r\n", " r.replica_server_name,\r\n", " r.endpoint_url,\r\n", " rs.connected_state_desc,\r\n", " rs.last_connect_error_description,\r\n", " rs.last_connect_error_number,\r\n", " rs.last_connect_error_timestamp\r\n", "from\r\n", " sys.dm_hadr_availability_replica_states rs\r\n", " join sys.availability_replicas r on rs.replica_id = r.replica_id\r\n", "where\r\n", " rs.is_local = 1\")\r\n", "$DT = New-Object System.Data.DataTable\r\n", "$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "$DT | Format-Table\r\n", "\r\n", "#validate the data\r\n", "Write-Host (\"The table contains: \" + $DT.Rows.Count + \" rows\")" ], "metadata": { "azdata_cell_guid": "593b02cf-0fd6-48db-be44-0948c24d8ad7", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "For example, if the secondary was unable to communicate with the DNS server or if a replica's endpoint\\_url was configured incorrectly when creating the availability group, you may get the following results in the last\\_connect\\_error\\_description:\n", "\n", "`DNS Lookup failed with error '11001(No such host is known)`\n", "\n", "4.   Ensure the endpoint is configured for the correct IP/port that AG is defined for\n", "\n", "- Run the following query on the Primary and then each Secondary replica that is failing to connect. This will help you find the endpoint URL and port." ], "metadata": { "azdata_cell_guid": "4123de52-a0ed-46bc-bb1f-d7f4d292d403", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "\r\n", "\r\n", "[string] $SqlQuery= $(\"select endpoint_url from sys.availability_replicas\")\r\n", "$DT = New-Object System.Data.DataTable\r\n", "$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "\r\n", "$DT | Format-Table\r\n", "#validate the data\r\n", "Write-Host (\"The table contains: \" + $DT.Rows.Count + \" rows\")" ], "metadata": { "azdata_cell_guid": "d0b835e9-ebba-407d-8757-8acc5cbad20e", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ " - Run the following query to find the endpoints and ports." ], "metadata": { "azdata_cell_guid": "f5e9b66f-ba60-4eb0-903a-72339be02cc7", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "[string] $SqlQuery= $(\"SELECT\r\n", " tep.name as EndPointName,\r\n", " sp.name As CreatedBy,\r\n", " tep.type_desc,\r\n", " tep.state_desc,\r\n", " tep.port\r\n", "FROM\r\n", " sys.tcp_endpoints tep\r\n", " INNER JOIN sys.server_principals sp ON tep.principal_id = sp.principal_id\r\n", "WHERE\r\n", " tep.type = 4\")\r\n", "$DT = New-Object System.Data.DataTable\r\n", "$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "\r\n", "$DT | Format-Table\r\n", "#validate the data\r\n", "Write-Host (\"The table contains: \" + $DT.Rows.Count + \" rows\")" ], "metadata": { "azdata_cell_guid": "443882f8-611e-4690-a114-3c553096cc03", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "- Compare endpoint\\_url and port from each query and ensure the port from the endpoint\\_url matches the port defined for the endpoint on each respective replica.\n", "\n", " ### Note\n", "\n", " If you are using specific IP addresses for the endpoint to listen on, versus the default of “listen all”, then you may have to define URLs  that use the specific IP address rather than the FQDN. \n", "\n", "5. Check whether the network service account has CONNECT permission to the endpoint. Run the following queries to list the accounts that have connect permission to the endpoint on the server(s) in question, and to show the permission assigned to each relevant endpoint." ], "metadata": { "azdata_cell_guid": "550b2eaf-3ad1-404a-8c96-18fa94308ac6", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "clear\r\n", "\r\n", "[string] $SqlQuery= $(\"SELECT \r\n", " perm.class_desc,\r\n", " prin.name,\r\n", " perm.permission_name,\r\n", " perm.state_desc,\r\n", " prin.type_desc as PrincipalType,\r\n", " prin.is_disabled\r\n", "FROM sys.server_permissions perm\r\n", " LEFT JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id\r\n", " LEFT JOIN sys.tcp_endpoints tep ON perm.major_id = tep.endpoint_id\r\n", "WHERE \r\n", " perm.class_desc = 'ENDPOINT'\r\n", " AND perm.permission_name = 'CONNECT'\r\n", " AND tep.type = 4;\r\n", "\r\n", "SELECT \r\n", " ep.name, \r\n", " sp.state,\r\n", " CONVERT(nvarchar(38), suser_name(sp.grantor_principal_id)) AS grantor,\r\n", " sp.TYPE AS permission,\r\n", " CONVERT(nvarchar(46),suser_name(sp.grantee_principal_id)) AS grantee\r\n", "FROM sys.server_permissions SP \r\n", " INNER JOIN sys.endpoints ep ON sp.major_id = ep.endpoint_id\r\n", "AND EP.type = 4\r\n", "ORDER BY Permission,grantor, grantee;\")\r\n", "\r\n", "$DT = New-Object System.Data.DataTable\r\n", "$DT = ExecuteSqlQuery $SQLInstance $DBName $SqlQuery \r\n", "\r\n", "$DT | Format-Table\r\n", "#validate the data\r\n", "Write-Host (\"The table contains: \" + $DT.Rows.Count + \" rows\")" ], "metadata": { "azdata_cell_guid": "32f72887-f1b9-4173-9f71-945389a65dd2", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "6. Check for possible name resolution issues\n", "\n", "- Validate DNS resolution by using NSLookup on the IP address and the name:" ], "metadata": { "azdata_cell_guid": "06ba61d4-22c0-457c-83e9-fe270b772773", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "Resolve-DnsName -Name $ServerName\r\n", "Resolve-DnsName -Name $server_IP_address" ], "metadata": { "azdata_cell_guid": "0f772880-24af-4056-ab00-ed71fcd7e49b", "tags": [], "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "- Does the name resolve to the correct IP address? Does the IP address resolve to the correct name?\n", "- Check for local HOSTS file entries on each node that may be pointing to an incorrect server. From Command Prompt print the HOSTS file using this:" ], "metadata": { "azdata_cell_guid": "67896d40-7fa6-46a1-8028-7d49ddda2b92", "extensions": { "azuredatastudio": { "views": [] } } } }, { "cell_type": "code", "source": [ "get-content 'C:\\WINDOWS\\system32\\drivers\\etc\\hosts'" ], "metadata": { "azdata_cell_guid": "96ac88f0-6ea1-4de4-b299-b1105cee0d71", "extensions": { "azuredatastudio": { "views": [] } } }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "- Check if there are [Server Aliases for Use by a Client](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-2016) defined on the replicas.\n", " \n", "\n", "7. Ensure your SQL Server is running a recent build (preferably the [latest build](https://docs.microsoft.com/en-us/troubleshoot/sql/general/determine-version-edition-update-level#latest-updates-available-for-currently-supported-versions-of-sql-server) to protect from running into issues like [KB3213703](https://support.microsoft.com/topic/kb3213703-fix-an-always-on-secondary-replica-goes-into-a-disconnecting-state-10131118-b63a-f49f-b140-907f77774dc2). \n", "\n", "For more information, refer to [Create Availability Group Fails With Error 35250 'Failed to join the database'](https://techcommunity.microsoft.com/t5/sql-server-support/create-availability-group-fails-with-error-35250-failed-to-join/ba-p/317987)" ], "metadata": { "azdata_cell_guid": "3f6d1672-d39c-4c40-980d-5f046b30456e", "extensions": { "azuredatastudio": { "views": [] } } } } ] }