Issues might be encountered during HA test bed building
Issue: Failover cluster validation failed:
Resolution:
1. cause: There are durty network adapter left from fomer deleted failover cluster.
Reinstall Failover cluster from system mgmt.
2. cause: The shared disk is unavailable.
Enalbe Iscsi, and bring the shared disk on line.
3. cause: Network validation failed for the heardbeat adapter and the domain adapter are in the same network subnet.
Put them in the different network subnet. Like:
Domain IP: 10.10.10.41, correct subnet mask and DNS IP needed.
Heardbeat IP: 10.10.20.41, others default.
Issue: Validation before SQL installation failed for below issue:
Rule "Network binding order" generated a warning.
The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configuration to change the binding order.
Resolution:
1. Use below command to get all the network adapter GUID:
wmic nicconfig get description, SettingID > C:/nicconfig.txt
2. Find the GUID for the domain network adapter according to the adapter name from the .txt file created above.
3. Open regedit, and find the key: HKLM/SYSTEM/CurrentControlSet/Services/Tcpip/Linkage :Bind
4. Put the GUID we found for the domain network adapter as the top adapter in the Bind key.
Issue: Installing SQL cluster failed for below reason:
The following error has occurred:
The cluster resource 'SQL Server' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736)
Resolution:
Automated created group for SQL cluster must have a correct IP, in a private domain, which should be a static IP. This must be noticed during configuring SETUP UI.
Issue: EDI/AS2 cannot be configured for below issue:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 R2 Books Online.
Login timeout expired
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.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)
------------------------------
Login timeout expired
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.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Server Native Client 10.0)
The following error has occurred:
The cluster resource 'SQL Server' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736)
Resolution:
To configure Integration Services as a cluster resource
- Open the Cluster Administrator.
- In the console tree, select the Groups folder.
- In the results pane, select the group to which you plan to add Integration Services:
- To add Integrations Services as a cluster resource to the same resource group as SQL Server, select the group to which SQL Server belongs.
- To add Integrations Services as a cluster resource to a different group than SQL Server, select a group other than the group to which SQL Server belongs.
- On the File menu, point to New, and then click Resource.
- On the New Resource page of the Resource Wizard, type a name and select “Generic Service” as the Service Type. Do not change the value of Group. Click Next.
- On the Possible Owners page, add or remove the nodes of the cluster as the possible owners of the resource. Click Next.
- To add dependencies, on the Dependencies page, select a resource under Available resources, and then click Add. In case of a failover, both SQL Server and the shared disk that stores Integration Services packages should come back online before Integration Services is brought online. After you have selected the dependencies, click Next.
- On the Generic Service Parameters page, enter MsDtsServer100 as the name of the service. Click Next.
- On the Registry Replication page, click Add to add the registry key that identifies the location of the configuration file for the Integration Services service. This file must be located on a shared disk that is in the same resource group as the Integration Services service.
- In the Registry Key dialog box, type SOFTWARE/Microsoft/Microsoft SQL Server/100/SSIS/ServiceConfigFile. Click OK, and then click Finish.
- The Integration Services service has now been added as a cluster resource.
Configuring the Integration Services Service and Package Store
Having configured Integration Services as a cluster resource, you must modify the location and the content of the configuration file for the Integration Services service on each node in the cluster. These modifications make both the configuration file and the package store available to all nodes if there is a failover. After you modify the location and content of the configuration file, you have to bring the service online.
To configure the Integration Services Service and Package Store
- Locate the configuration file at %ProgramFiles%/Microsoft SQL Server/100/DTS/Binn/MsDtsSrvr.ini.xml. Copy it to the shared disk for the group to which you added the Integration Services service.
- On the shared disk, create a new folder named Packages to serve as the package store. Grant List Folders and Write permissions on the new folder to appropriate users and groups.
- On the shared disk, open the configuration file in a text or XML editor. Change the value of the ServerName element to the name of the virtual SQL Server that is in the same resource group.
- Change the value of the StorePath element to the fully-qualified path of the Packages folder created on the shared disk in a previous step.
- Update the value of HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/100/SSIS/ServiceConfigFile in the Registry to the fully-qualified path and file name of the service configuration file on the shared disk.
To bring the Integration Services service online
- In the Cluster Administrator, select the Integration Services service, right-click, and select Bring Online from the popup menu. The Integration Services service is now online as a cluster resource.