In SmartConnect 21, each SmartConnect 21 Customer ID registration allows for 4 instances of SmartConnect 21 databases that the customer can manage using the SmartConnect Configuration tool to Activate if needed and then Retire instances when no longer necessary.
The act of “retiring” the instances retires them in the eOne UAM “cloud” server and also marks them as “retired” in the local SmartConnect database.
If you’d rather watch a video explaining this, see below or click here.
To retire an existing instance, in the SmartConnect Configuration tool, right-click on the instance you would like to retire and select “Retire”
After confirming that you wish to retire the selected instance and entering the credentials to the Server and pressing OK, this normally does retire the instance and will show as “Inactive” as many of them in the following screenshot are.
The issue comes when the server that the SmartConnect database is registered with no longer exists on your network.
If that is true, then the configuration tool cannot connect with that SQL Server and we get the standard connection error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error locating Server/Instance Specified)
This happens because the Configuration tool is trying to connect to that specific server & database in order to mark it as “retired”.
However, the connection fails due to the server no longer existing and the Configuration tool will not mark the server as “retired” leaving it available and included in the license count.
You could, of course, create a new SQL Server with the same name/sql instance and restore a SmartConnect 21 database to then retire. However, easier is to use a SQL “trick” from SQL Server Tools to accomplish the same thing. If the Server existed but not the specific SmartConnect database, we can skip the next step. Otherwise, we can use SQL Server Configuration Manager to redirect the calls to the old server to this server.
On the SQL Server itself – this one is EONE\EONESQL – launch the SQL Server Configuration Manager application.
We can use a SQL Native Client 11.0 Configuration – Alias to tell SQL “if you see this Alias Name then redirect to this Server instead”.
The server & instance that no-longer-exists is eone-2014\eone and since SmartConnect 21 is 64bit we will create a new Alias with the Alias Name of eone-2014\eone. Assuming standard port and protocol, we can leave those as default and for the Server – we put in the actual SQL Server & Instance of this SQL Server which is EONE-EONESQL.
Press OK and close the SQL Server Configuration Manager application as we are finished with the application.
Since the target database we need to retire also no longer exists, we need to create it and then restore a working copy (ideally of that same database but good chance no backups exist either since removed.)
In SQL Studio Management Studio, select the Server Instance and right click and choose “new database”.
The database we’re going to retire was named TESTING1234 so create the database with that name and should be able to accept the defaults and press OK to create the database.
At this point, the database is created but is just an empty database. SmartConnect Configuration is going to expect it to have at least the Setup table. Potentially we could create the table manually – but we already have an existing SmartConnect 21 database we can make a copy of and retire.
In SSMS, right click on your current (or other valid) SmartConnect 21 database – mine is SmartConnect21 – and choose Tasks | Backup. Add a path to backup to Disk and give it a name – I chose simply SC21.bak.
Press OK to make the backup.
Next, we want to overwrite our empty TESTING1234 database with this backup.
In SSMS, right click on the TESTING1234 database and choose Restore | Database.
In the Restore Database window, select to restore from Device and use the lookup to pick the SC21.bak file we just created.
Select the Options page, and then on the Options page, mark the “overwrite existing database” checkbox and press OK to restore the SmartConnect21 database over the TESTING1234 database.
At this point, we have created the SQL alias to redirect the old server to this server and recreate the missing TESTING1234 database – the only thing left is to retire it as we had tried previously using the SmartConnect Configuration Tool.
In the SmartConnect Configuration Tool, once again right click the eone-2014\eone – TESTING1234 and choose to Retire.
Confirm the choice and enter the credentials to that database – probably sa/password – and press OK.
This time SmartConnect be able to connect to that faked SQL Instance due to the SQL Alias and will successfully retire the database on the UAM server and also that local TESTING1234 database.
At this point, you can drop the TESTING1234 database and delete the SQL Alias we had created, if desired.
