Disable Auto Close, Enable CLR & Verify Full-Text Search
When connecting FocalScope to a remote SQL server with an existing database or if you had FocalScope created a SQL server database for itself, during installation, then we recommend you make a few configuration changes to the SQL server instance/database. This lesson shows you, step-by-step, how to make these changes. Please note: the "auto close" feature need not be set for databases which the FocalScope installer created.
WARNING! It is recommended that you create a standalone SQL server instance & database for FocalScope to connect to (see the lesson Configure a Dedicated SQL Server Instance for more details). Applying the changes listed in this document, to your existing remote SQL server instance/databases, could possibly lead to unexpected results and/or data loss. We recommend only experience SQL database administrators connect FocalScope to a production SQL server instance/database and apply the changes listed in this document.
Once the SQL Management Studio starts, click on the dropdown list for the [Server name:] field and select [Browse for more...].
The "Browse for Servers" page opens, click on the [Network Servers] tab, expand the [Database Engine] node and select the SQL server instance you setup for FocalScope or where an existing database was created for FocalScope.
The selected instance name will be in the [Server name:] field. Click [Connect] to continue. Select your login account which you set up for accessing this SQL server instance, in the [Authentication:] field and fill in the credentials (if promted) to log in.
Expand the [*ServerName*] node, expand the [Databases] node and right click and select [Properties] on the database associated with FocalScope.
In the opening "Database Properties" page, click on the [Options] page, expand the [Auto Close] dropdown and select [False]. Click [OK] once done.
Enable .NET CLR
Next we have to enable .NET CLR. To enable .NET CLR for the SQL server instance, we have to run a script in the SQL interpreter. Right click your SQL server instance and select [New Query] to open the interpreter.
Once the interpreter has loaded, copy and paste the following script, into the interpreter.
sp_configure 'show advanced options', 1;
sp_configure 'clr enabled', 1;
[itemClick on the [! Execute] button to execute the script and enable .NET CLR.
- In the message box we get the report that the "option 'clr enable' changed form 0 to 1", which means CLR is now active on this SQL server instance.
Verify that Full-Text Search is Enabled
Lastly we need to verify that Full-Text Search is installed and active. To do so, we need to run another query in SQL studio. Copy and paste the following query into the interpreter.
- Click on the [! Execute] button to execute the script.
- In the message box we get the results with "1" in the 1st column. This indicates Full-Text Search is installed and active.
You have now successfully configured your SQL server instance and database for use with FocalScope.