• Article

How to: Remove an Instance of SQL Server from the SQL Server Utility

Use the following steps to remove a managed instance of SQL Server from the SQL Server Utility. This procedure removes the instance of SQL Server from the UCP list view and SQL Server Utility data collection stops. The instance of SQL Server is not uninstalled.

Important

Before you use this procedure to remove an instance of SQL Server from the SQL Server Utility, make sure that the SQL Server and SQL Server Agent services are running on the instance to remove.

  1. From the Utility Explorer in SQL Server Management Studio, click on Managed Instances. Observe the list view of managed instances of SQL Server in the Utility Explorer content pane.

  2. In the SQL Server Instance Name column of the list view, select the SQL Server instance to remove from the SQL Server Utility. Right-click on the instance to remove, and select Remove Managed Instance….

  3. Specify credentials with administrator privileges for the instance of SQL Server: Click Connect…, verify the information in the Connect to Server dialog box, then click Connect. You will see the login information on the Remove Managed Instance dialog.

  4. To confirm the operation, click OK. To quit the operation, click Cancel.

Manually Remove a Managed Instance of SQL Server from a SQL Server Utility

This procedure removes the instance of SQL Server from the UCP list view and stops SQL Server Utility data collection. The instance of SQL Server is not uninstalled.

To use PowerShell to remove a managed instance of SQL Server from the SQL Server Utility. This script performs the following operations:

  • Gets the UCP by server instance name.

  • Removes the managed instance of SQL Server from the SQL Server Utility.

# Get Ucp connection
$UcpServerInstanceName = "ComputerName\InstanceName";
$UtilityInstance = new-object –Type Microsoft.SqlServer.Management.Smo.Server $UcpServerInstanceName;
$UcpConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $UtilityInstance.ConnectionContext.SqlConnectionObject;
$Utility = [Microsoft.SqlServer.Management.Utility.Utility]::Connect($UcpConnection);

# Now remove the ManagedInstance from the SQL Server Utility
$ServerInstanceName = "ComputerName\InstanceName";
$Instance = new-object -Type Microsoft.SqlServer.Management.Smo.Server $ServerInstanceName;
$InstanceConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $Instance.ConnectionContext.SqlConnectionObject;
$ManagedInstance = $Utility.ManagedInstances[$ServerInstanceName];
$ManagedInstance.Remove($InstanceConnection);

Use the following Transact-SQL commands in SQL Server Management Studio if all other methods fail to remove a managed instance of SQL Server from the SQL Server Utility. Both scripts assume the user is running as sysadmin.

  1. Connect to the managed instance of SQL Server and run this stored procedure:

    EXEC msdb.dbo.sp_sysutility_mi_remove;

    At this point, the managed instance of SQL Server still appears in Utility Explorer, but does not upload any more data to the UCP. Status in the list view is be gray. Technically, the instance is not enrolled in a SQL Server Utility, so it could be enrolled in a different UCP.

  1. Connect to the UCP and run this script:

    DECLARE @instance_id int;
SELECT @instance_id = mi.instance_id
FROM msdb.dbo.sysutility_ucp_managed_instances AS mi
WHERE mi.instance_name = 'ComputerName\InstanceName';

EXEC msdb.dbo.sp_sysutility_ucp_remove_mi @instance_id;

Note that it is important to refer to the SQL Server instance name exactly as it is stored in SQL Server. On a case-sensitive instance of SQL Server, you must specify the instance name using the exact casing as returned by @@SERVERNAME. To get the instance name for the managed instance of SQL Server, run this query on the managed instance:

select @@SERVERNAME AS instance_name

At this point, the managed instance of SQL Server is fully removed from the UCP. It disappears from the list view the next time you refresh data for the SQL Server Utility. This state is identical to a user successfully going through the remove managed instance operation in the SSMS user interface.