Jump to: navigation, search

Setting Up the SQL Server for SpeechMiner

Installing SQL Server 2012

To install SQL Server 2012 for use with SpeechMiner, run the normal setup wizard first and follow the instructions.

  1. Run the installation program. The SQL Server Installation Center window opens, with the Planning screen open.
  2. From the menu on the left, select Installation. The Installation screen opens. Select New installation or add features to an existing installation. The installation wizard opens.
  3. Follow the on-screen instructions. When the screens mentioned below open, follow the instructions below to select the required settings and options for SpeechMiner.
  4. From the Setup Role screen, select SQL Server Feature Installation.
  5. From the Feature Selection screen, select the following options:
    • Database Engine Services
    • Reporting Services
    • Client Tools Connectivity
    • SQL Server Books Online
    • Management Tools Basic
    • Management Tools Complete
    Sm sql2012featsel.png

  6. From the Server Configuration screen, in the Service Accounts tab, for the SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services, do the following:
    • Enter the user account and password of the service account.
    • Under Startup Type, select Automatic.
    Sm sql2012servconf.png

  7. From the Server Configuration screen, in the Collation tab, under Database Engine, select SQL_Latin1_General_CP1_CI_AS (the default value).
    Sm sql2012collation.png

  8. From the Database Engine Configuration screen, in the Data Directories tab, select the locations for the database folders. If possible, put the User database directory, the Temp DB directory, and the Backup directory on a separate drive from the other folders.
    Sm sql2012dbconf.png

  9. From the Reporting Services Configuration screen, select Install the native mode default configuration.
  10. When you finish installing SQL Server, restart the machine on which you installed it.

Installing SQL Server 2014

To install SQL Server 2014 for use with SpeechMiner, run the normal setup wizard first and follow the instructions.

  1. Run the installation program. The SQL Server Installation Center window opens, with the Planning screen open.
  2. From the menu on the left, select Installation. The Installation screen opens.

    3. SQL2014-1.png
  3. Select New SQL Server stand-alone installation or add features to an existing installation. The installation wizard is activated.
  4. Follow the on-screen instructions. Refer to the instructions below to select the required settings and options for SpeechMiner.

    5. SQL2014-2.png
  5. In the Setup Role screen, select SQL Server Feature Installation and click Next.


    6. SQL2014-3.png
  6. In the Feature Selection screen, select the following features, and click Next:
    • Database Engine Services
    • Reporting Services - Native
    • Client Tools Connectivity
    • Management Tools Basic
    • Management Tools Complete

    SQL2014-4.png

  7. In the Instance Configuration screen select the Instance Configuration option and set the Instance ID name.

    8. SQL2014-6.png
  8. In the Server Configuration > Service Accounts tab, perform the following for the SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services and click Next:
    • Enter the service Account Name and Password.
    • Under Startup Type, select Automatic.

    SQL2014-7.png

  9. In Server Configuration > Collation tab, under Database Engine, select SQL_Latin1_General_CP1_CI_AS (the default value) and click Next.

    10. SQL2014-8.png
  10. In the Database Engine Configuration > Server Configuration tab, select an Authentication Mode and click Next. If required specify the SQL Server administrators password.

    11. SQL2014-9.png
  11. In the Database Engine Configuration > Data Directories tab, select the database folder locations and click Next.
    Note: It is recommended that the User database directory, Temp DB directory, and Backup directory be located on a drive that is different from the Data directories.

    12. SQL2014-10.png
  12. In the Reporting Services Configuration screen, select Install and configure and click Next.

    13. SQL2014-11.png
  13. In the Ready to Install screen, click Install.
  14. When you finish installing SQL Server, restart the machine on which you installed it.

Installing SQL Server 2016

To install SQL Server 2016 for use with SpeechMiner, run the normal setup wizard first and follow the instructions.

  1. Run the installation program. The SQL Server Installation Center window opens, with the Planning screen open.
  2. From the menu on the left, select Installation. The Installation screen opens.

    3. SQLServer2016-1.png
  3. Select New SQL Server stand-alone installation or add features to an existing installation. The installation wizard is activated.
  4. Follow the on-screen instructions. Refer to the instructions below to select the required settings and options for SpeechMiner.

    5. SQLServer2016-2.png
  5. In the Product updates screen, click Next.
  6. In the Feature Selection screen, select the following features, and click Next:
    • Database Engine Services
    • Reporting Services – Native
    • Client Tools Connectivity

    SQLServer2016-3.png

  7. In the Instance Configuration screen select the Instance Configuration option and set the Instance ID name.

    8. SQLServer2016-4.png
  8. In the Server Configuration > Service Accounts tab, perform the following for the SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services and click Next:
    • Enter the service Account Name and Password.
    • Under Startup Type, select Automatic.

    SQLServer2016-5.png

  9. In Server Configuration > Collation tab, under Database Engine, select SQL_Latin1_General_CP1_CI_AS (the default value) and click Next.

    10. SQLServer2016-6.png
  10. In the Database Engine Configuration screen, in the server configuration add the Authentication mode. In the Data Directories tab, select the locations for the database folders. If possible, put the User database directory, the Temp DB directory, and the Backup directory on a separate drive from the other folders. Click Next.

    11. SQLServer2016-7.png
    SQLServer2016-8.png
    SQLServer2016-9.png


  11. In the Reporting Services Configuration screen, select Install the native mode default configuration and click Next.

    12. SQLServer2016-10.png
  12. When you finish installing SQL Server, restart the machine on which you installed it.

Installing SQL Server 2019

To install SQL Server 2019, refer to SQL Server installation guide.

Configuring the SQL Server Setting

After the SQL server is installed, do the following:

  • Ensure that the SQL server is running
  • Configure the SQL server to start automatically
  • Enable both the TCP/IP and the Named Pipes protocols
Important
After you install SpeechMiner, you also have to deploy the CLR assembly and set its permissions. See Installing the SpeechMiner Components > Installing the SpeechMiner Database > SQL CLR.

To configure the SQL server and enable the required protocols:

  1. From the Start menu, navigate to Microsoft SQL Server 2008 (or later) > Configuration Tools > SQL Server Configuration Manager. The SQL Server Configuration Manager opens.
  2. On the left side of the window, select SQL Server Services.
    Sm sqlconfmgr.png

  3. On the right side of the window, for SQL Server Agent, check that the Status is Running, and the Start Mode is Automatic.
  4. If one or both of these values are not as they should be, do the following:
    • Double-click the row. The Properties window opens.
    • In the Service tab, set the Start Mode to Automatic.
    • If the service is not running, in the Log On tab, select Start.
    • Click OK to implement the changes.
  5. On the left side of the SQL Server Configuration Manager window, select SQL Server Network Configuration > Protocols for MSSQLSERVER.
    Sm sqlconfmgrprotocol.png

  6. On the right side of the window, for TCP/IP and for Named Pipes, check that the Status is Enabled.
  7. For each of these protocols, if it is not enabled, do the following:
    • Double-click the row. The Properties window opens.
    • In the Protocol tab, under Enabled, select Yes.
    • Click OK to implement the changes.

Configuring the Reporting Services (Pre SQL Server 2019)

The SQL reporting services should be configured as explained below.

Tip
The default configuration for mhtml rendering is html3.2. This configuration does not support padding. In order to render web archive open the Report Server config file (for example C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config) and change RenderingExtension (under EmbeddedRenderFormats) to HTML4.0. Once you are done, restart the report server.


To configure the SQL reporting services:

  1. In the Start menu, under All Programs, select SQL Server 2008 R2 > Configuration Tools > Reporting Services Configuration Manager. The Reporting Services Configuration Connections window opens.
  2. Enter the report server name and the instance name (if they are not already there), and click Connect. The Reporting Services Configuration Manager opens, with the Report Server Status screen displayed.

    Sm sqlrepservconfmgr.png

  3. Check whether the report server is running. If it is not, click Start.
  4. On the left side of the window, select Service Account.

    Sm sqlrepservaccount.png

  5. Configure the account name and password of the service account that will be used to run the report-server service, as required. Use either a local administrator account or an account that can log in as a service and run services on the local machine.

    6. The user must be a Domain user.

  6. On the left side of the window, select Web Service URL; make sure the settings in the screen match the settings as follows:

    Sm sqlrepservweb.png

  7. On the left side of the window, select Database. If you created a report-server database when you installed SQL Server, it appears under Current Report Server Database. If you did not, create it now.

    Sm sqlrepservdb.png

  8. On the left side of the window, select Report Manager URL; make sure the settings in the screen match the settings as follows:

    Sm sqlrepservrepurl.png

  9. On the left side of the window, select E-mail Settings.
  10. Enter the settings for the e-mail account you want the report server to use to send reports to SpeechMiner users.

    Sm sqlrepservemail.png

  11. Click Exit to close the Reporting Services Configuration Manager.
  12. In the Report Server config file (rsreportserver.config) change the MaxActiveReqForOneUser parameter value from 20 to 250.
    13. For more details see: http://msdn.microsoft.com/en-us/library/ms157273.aspx


Configuring the Reporting Services (SQL Server 2019)

The SQL reporting services should be configured as explained below.

Tip
The default configuration for mhtml rendering is html3.2. This configuration does not support padding. In order to render web archive open the Report Server config file (for example C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config) and change RenderingExtension (under EmbeddedRenderFormats) to HTML4.0. Once you are done, restart the report server.


To configure the SQL reporting services:

  1. In the Start menu, under All Programs, select SQL Server 2019 > Configuration Tools > Reporting Services Configuration Manager. The Reporting Services Configuration Connections window opens.
  2. Enter the report server name and the instance name (if they are not already there), and click Connect. The Reporting Services Configuration Manager opens, with the Report Server Status screen displayed.
  3. Check whether the report server is running. If it is not, click Start.
  4. On the left side of the window, select Service Account.
  5. Configure the account name and password of the service account that will be used to run the report-server service, as required. Use either a local administrator account or an account that can log in as a service and run services on the local machine.

    6. The user must be a Domain user.

  6. On the left side of the window, select Web Service URL; make sure the settings in the screen match the settings as follows: Web-service-url.png
  7. On the left side of the window, select Database. If you created a report-server database when you installed SQL Server, it appears under Current Report Server Database. If you did not, create it now.
  8. On the left side of the window, select Web Portal URL; make sure the settings in the screen match the settings as follows: Web-portal-url.png

  9. On the left side of the window, select E-mail Settings.
  10. Enter the settings for the e-mail account you want the report server to use to send reports to SpeechMiner users.
  11. Click Exit to close the Reporting Services Configuration Manager.
  12. In the Report Server config file (rsreportserver.config) change the MaxActiveReqForOneUser parameter value from 20 to 250.
    13. For more details see: http://msdn.microsoft.com/en-us/library/ms157273.aspx


Creating the Report-Server Database

If the report-server database was not created automatically when you installed SQL Server, you can create it in the Report Server Database Configuration Wizard.

To create the report-server database:

  1. Open the Reporting Services Configuration Manager.
  2. From the Database screen, under Current Report Server Database, click Change Database. The Report Server Database Configuration Wizard opens.
  3. In the wizard, fill in the fields as they are filled in in the examples shown (except, of course, for the server name and the credentials, which you must specify as appropriate for your system). Click Next to progress from screen to screen until you have finished creating the database.

  • Action

  • Database Server

  • Database

  • Credentials

Click on the image to enlarge.

Configuring Report Server Load Value

To ensure that the Report Server does not crash due to overload, verify that your Report Server is configured to enable a large amount of concurrent reports.

  1. Open the report server configuration file. By default the configuration file can be found in rsreportserver.config under \\SERVER_NAME\c$\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer.
  2. Change the value of MaxActiveReqForOneUser to the recommended value. The value of MaxActiveReqForOneUser depends on your deployment. Contact Customer Care for the value recommended for your deployment.
    3. Important
    The report server has a limit of the number of simultaneous connections, when the limit is reached, the SSRS does not accept new requests and throws 503 errors.
  3. Change the SME data source connection string:
    1. Go to the Report Server web interface. By default the location is http://SERVER_NAME/reports.
    2. Click on the database folder.
    3. Click SME datasource.
    4. Add the following string: max pool size = 10000 to the end of the connection string field.
    5. Click Apply.

Setting the Maximum Memory Usage

If the SQL-server's memory usage is not limited, it will consume all of the available memory. Therefore, it is recommended to limit the memory usage of the SQL Server by setting the max server memory value.

Important
In addition to the "server memory" that is limited by this value, the SQL server uses 2-4 GB of other memory. For this reason, it is recommended to set the max server memory to a value that is 2-4 GB lower than the maximum memory you want to allow the server to use. For additional details, see http://msdn.microsoft.com/en-us/library/ms178067.aspx.


You can see the current max server memory value, and modify it as required, in the SQL Server Management Studio.

To view or modify the max server memory value:

  1. From the SQL server, open the SQL Server Management Studio. (For example, in the Start menu, under All Programs, select Microsoft SQL Server 2008 R2 > SQL Server Management Studio.)
  2. On the left side of the window, right-click the SQL server and then select Properties. The Server Properties window opens.
    Sm sqlmemory.png

  3. On the left side of the window, select Memory. The memory settings are displayed.
  4. Under Maximum server memory (in MB), enter the value you want to use.
  5. Click OK. The setting is implemented, and the window closes.

If you prefer, you can also set the max server memory property by executing a query:

To set the max server memory by executing a query:

  1. On the SQL server, open the SQL Server Management Studio. (For example, in the Start menu, under All Programs, select Microsoft SQL Server 2008 R2 > SQL Server Management Studio.)
  2. On the left side of the window, right-click the SQL server and then select New Query. A blank text area opens on the right side of the window.
  3. Copy the following commands and paste them into the text area: 
    sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO
  4. The code sets the max server memory to 4GB (4096MB). If you want to set it to a different value, in the text area, change 4096 to the required value.
  5. Above the text area, select Execute. The commands are executed. When the process is completed successfully, Query executed successfully appears at the bottom of the window.
    Sm sqlmemory2.png


Recommended SQL Server Configuration

  1. On the SQL server, open the SQL Server Management Studio. (For example, in the Start menu, under All Programs, select Microsoft SQL Server 2008 R2 > SQL Server Management Studio.)
  2. On the left side of the window, right-click the SQL server and then select New Query. A blank text area opens on the right side of the window.
  3. Copy the following commands and paste them into the text area: 
    sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'backup compression default',1
reconfigure
exec sp_configure 'cost threshold for parallelism',50
reconfigure
exec sp_configure 'remote admin connections',1
reconfigure






This page was last edited on March 31, 2022, at 18:21.
Comments or questions about this documentation? Contact us for support!