Disable Auto Close, Enable CLR & Verify Full-Text Search
Friday, October 21, 2016 5:53 AM


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.

Disable Auto Close


Launch the SQL Server Management Studio, located in the Windows start menu.



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.


  1. sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE;
    GO

    [itemClick on the [! Execute] button to execute the script and enable .NET CLR.
  2. 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.


  1. SELECT SERVERPROPERTY('IsFullTextInstalled')

  2. Click on the [! Execute] button to execute the script.

  3. 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.