SQL Server and other resource providers troubleshooting

  • Article

 

Applies To: Windows Azure Pack

This topic describes troubleshooting issues that pertain to SQL Server, MySQL, and other resource providers in Windows Azure Pack for Windows Server. Recommendations are provided for the following issues:

  • Add a database to a SQL Server cluster

  • Change the FQDN for a resource provider

  • Connect to SQL Server or MySQL

  • Create a script to register SQL Server

  • Determine which SQL Server databases are used

  • Set up a SQL Server group for a tenant

  • Unregister a custom resource provider

For issues pertaining to deploying SQL Server items in the gallery, see Virtual Machine Clouds troubleshooting. For APIs of interest to resource providers, including programming SQL Server databases, see Windows Azure Pack Resource Providers.

Add a database to a SQL Server cluster

Pertains to: Using SQL Server or MySQL with Windows Azure Pack

Issue

Not able to add a database to a SQL Server cluster in Windows Azure Pack.

Recommendation

Be sure that any SQL Server extensions that you are using are installed on the management API server. If you continue to get errors, you may have to uninstall and reinstall the management APIs, see Install the Windows Azure Pack Service Management APIs.

Back to top

Change the FQDN for a resource provider

Pertains to: Deploy Windows Azure Pack for Windows Server

Issue

Need to change the Fully Qualified Domain Name (FQDN) for web service endpoints for a resource provider.

Recommendation

See the topics Update FQDNs for resource providers and Reconfigure FQDNs and Ports in Windows Azure Pack.

Back to top

Connect to SQL Server or MySQL

Pertains to: Using SQL Server or MySQL with Windows Azure Pack

Issue

Logon fails when attempting to add a SQL Server or a MySQL database in the management portal for tenants.

Recommendation

Check the following:

  • Verify that a firewall is not blocking the SQL Server default port (1433), or the MySQL default port (3306).

  • Verify that the correct instance name is being accessed.

  • Verify that the TCIP/IP protocol is enabled for network configurations in the configuration manager.

You can use the following procedure to determine if MySQL is accessible from a tenant's computer.

  1. Start the MySQL Command Line Client by entering the following at a command prompt (where pwd is the password): mysql -u root -p pwd

  2. Enter the following commands:

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pwd' WITH GRANT OPTION;  
FLUSH PRIVILEGES;  
use mysql;  
update user set grant_priv='Y' where user='root'; 

  3. When ready, exit the session: Exit;

  4. Restart MySQL service with Server manager or by entering the following commands in an Administrator command prompt: 

        net stop mysql  
    net start mysql 

Back to top

Create a script to register SQL Server

Pertains to: Using SQL Server or MySQL with Windows Azure Pack

Issue

Need a script to facilitate adding a database to the management portal for administrators.

Recommendation

After you create the database, you can use the following script as the basis for your code.

$Adminuri = 'https://myEnvironment:30004/'
$token = Get-MgmtSvcToken -Type 'Windows' -AuthenticationSite https://myEnvironment:30072 -ClientRealm http://azureservices/AdminSite -DisableCertificateValidation

#Get the Hosting Group to add the Hosting Server to 
$HostingGroup = Get-MgmtSvcSqlServerGroup -AdminUri $Adminuri -Token $token -DisableCertificateValidation | where {$_.GroupName -eq "Default"}

#Add the hosting Server
$dbusername = 'sa'
$dbpassword = ConvertTo-SecureString "passw0rd1" -AsPlainText -Force
$dbcredential = New-Object System.Management.Automation.PSCredential ($dbusername,$dbpassword)
$dbServerName = 'myDbServer'

Add-MgmtSvcSqlHostingServer -AdminUri $Adminuri
    -Token $token
    -ServerGroupId $HostingGroup.GroupId
    -Name $dbServerName
    -User $dbcredential
    -TotalSpaceMB 5000
    -DisableCertificateValidation

Back to top

Determine which SQL Server databases are used

Pertains to: Using SQL Server or MySQL with Windows Azure Pack

Issue

Need to know which SQL Server databases are used by Windows Azure Pack when configured with a SQL Server group.

Recommendation

Windows Azure Pack uses SQL Server databases based on number of databases and space allocated to each database. The only way to further refine placement is through SQL Server groups as defined in plans.

Back to top

Set up a SQL Server group for a tenant

Pertains to: Using SQL Server or MySQL with Windows Azure Pack

Issue

Want to set up a dedicated SQL Server group for a tenant.

Recommendation

See the blog post, Dedicating a part of the SQL Server fabric to a specific tenant

Back to top

Unregister a custom resource provider

Pertains to:

Issue

After installing a custom resource provider to Windows Azure Pack, need to know how to unregister it after removing its DLLs, assemblies, and other files.

Recommendation

You can use the following script where $rpName is a variable that contains the name of the custom resource provider.

$rp = Get-MgmtSvcResourceProviderConfiguration -Name $rpName
if ($rp -ne $null)
{
    $rp = Remove-MgmtSvcResourceProviderConfiguration -Name $rpName -InstanceId $rp.InstanceId
}

Back to top

See Also

Windows Azure Pack troubleshooting