• rsbrux Correct "Known Issues" once resolved.
  

Overview

This describes the installation of the 64 bit version of SQL Server 2017 Express, for use as a DB for MediaPortal's TV Server.  The file names mentioned below apply to Windows installations using English as the preferred language.  If you have a different language selected as the preferred language, you may receive files with slightly different names.

Methodology

2017 is the last member of the MS SQL Server family supported by Windows 8.1.   The methodology described here enables its semi-automatic installation.

Principles

The basic principles of this methodology are:

1. Download the desired version of SQL Express.
2. Download SQL Server Management Studio (SSMS).
3. Prepare the files for installation.
4. Prepare a configuration file,
5. Complete the installation.

Techniques

This methodology can be employed from any account, as long as you have access to an account having administrative privileges to authorize the actions requiring User Account Control (UAC).  However, there will be many such occasions, so it will be much easier if you perform the installation while logged into an account with administrative privileges. 

Download the desired version of SQL Express.

1. Download "SQLServer2017-SSEI-Expr.exe" from this Microsoft site. This is only a stub, which will download the full installation package for the host OS. 
2. Open a command (or PowerShell) window in the directory where you saved the file downloaded in the step above. In Windows 8.1, you can do this by holding down the SHIFT key, right-clicking the directory name in Explorer and choosing "Open command window here" from the resulting pop-up menu.  In Windows 10, PowerShell has replaced CMD as the default command line interpreter (CLI), but it will serve just as well and you can invoke it in a similar way:  Just hold down the SHIFT key while right-clicking the directory name and choose "Open PowerShell window here" from the resulting pop-up menu.
3. Enter the following command into the new window:
   .\SQLServer2017-SSEI-Expr.exe /Action=Download
4. A dialog will open prompting you to select a download package and a folder to store it in.
5. Select the "Express Advanced" package (this is the one which has been tested) and the same directory where you downloaded the stub file.
   
6. When the download is finished, the downloader will report success:
   
7. Click on "Open folder".

Download SQL Server Management Studio (SSMS)

This step is optional but highly recommended, in case you should need to examine the contents of the DB.  In SQL 2008, the Management Studio was included in the SQL Server installation package, but this is no longer the case.  The current version of SSMS (18.8 as of this writing) is compatible with SQL versions from 2008 - 2019.

1. Download the SSMS installation package from this page, which also includes detailed explanations about  the package's capabilities, compatibility and known issues.  Choose the same direcotory where you downloaded the files in the previous section.

Prepare the files for installation.

1. In the window which opened after downloading SQL Server 2017 Express, right-click on the file that was downloaded, "SQLEXPRADV_x64_ENU.exe", and select "Run as administrator" from the resulting pop-up menu.
2. This will result in a dialog asking for a directory in which to unpack the installation files. The default is a sub-directory, named "SQLEXPRADV_x64_ENU", of that from which the file was executed, but please delete the subdirectory and extract it to the same directory you used for the other files.

Prepare a configuration file.

1. Open Notepad (or other text editor of your choice, but not Word), copy the text under "Configuration file (MPSQL.ini)" below and paste the text into the open text editor window.  This configuration file has been tested under Windows 8.1 and Windows 10 with MP 1.26 and 1.27.  It should be valid for SQL Server 2016, 2017 and 2019.  Nevertheless, changes in the configuration file may be required. You will find further information about configuration file syntax and interactive creation of configuration files, including a sample configuration file here.  Another example of a configuration file (for SQL 2016, which should also be valid for SQL 2017 and 2019) can be found here.
2. Save the file as "MPSQL.ini" in the directory you chose in step 2 above.

Complete the installation.

1. Go back to the command (or PowerShell) window you opened in the first section of this procedure and enter the command:
   .\setup.exe /CONFIGURATIONFILE=.\MPSQL.ini
2. This will install SQL Server Express and create a database with an administrative (sa) password of "MediaPortal" (or whatever you substitute for it in MPSQL.ini). This is the login and password you will need when installing TV Server.
3. If you downloaded SSMS, go back to the command (or PowerShell) window you opened in the first section of this procedure and enter the command:
   .\SSMS-Setup-ENU.exe start "" /w .\SSMS-Setup-ENU.exe /Passive

You can verify a successful installation by starting the Management Studio and logging in as "sa" with the password "MediaPortal" (or whatever you chose in the configuration file) To do so, you will have to select "SQL Server Authentication" instead of "Windows Authentication".

Best practices

This method evades the restrictions on passwords generally enforced by SQL Server in order to setup the default login parameters used by MediaPortal. These restrictions would normally prevent the use of a weak password, such as "MediaPortal". This is unlikely to be problematic as long as the database is only accessible within your local (home) network. If you plan to allow access from outside, please follow the recommendations for installation using a strong password in the command line as shown in the earlier wiki article, "SQL Server 2008".

Known issues

During TV Serve installation, you will be prompted to configure and test the DB connection.  As of MP 1.27 you must choose Microsoft SQL Server 2005 (even though MP no longer supports SQL Server 2005).   You can also provide various other parameters used to create and access the TV Server database.  Unless you have changed the supplied configuration file, MPSQL.ini, it shouldn't be necessary to change any of these in order to create a database and connect to it.  The dependency of the TV Server service is set by default to SQLBrowser (SQL Server Browser).  Ideally this should be MSSQL$SQLEXPRESS (SQL Server (SQLEXPRESS)).  However, if you change this here (as of MP 1.27), it is only effective until the next invocation of the TV Server Configuration program (SetupTV.exe).  In order to ensure that the database is accessible when the TV Service starts, it may help to change the startup of the TV Service from Automatic to Automatic (Delayed Start).  To do so,  enter services.msc into the Windows run utility (Win+R) and press enter.  In the list of services, double-click on TVservice (or right-click on it and pick Properties from the pop-up context menu).  You can then select Automatic (Delayed Start) from the dropdown list  labelled Startup type.

If the MP client still has difficulty accessing the TV Server, it may help to delay the MP client's startup (see Delay startup under Startup/Resume Settings).

A more sophisticated workaround is to create a scheduled task to reset the TV Service dependencies on every startup.

To do so, create a command file TVS-SQL-dependencies.cmd with the content shown under Resources below.  Then execute the following code from an administrative command window to create the corresponding scheduled task:

schtasks.exe /create /TN SetTvServiceDependencies /tr "<path to command file>\TVS-SQL-dependencies.cmd" /sc ONSTART /ru "System"

Resources

Configuration file (MPSQL.ini)

;SQL Server 2017 Configuration File
[OPTIONS]

; Required: specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. 
ACTION="Install"

; Startup type for Browser Service.
; Manual - Service startup is manual mode (default).
; Automatic - Service startup is automatic mode.
; Disabled - Service is disabled 
BROWSERSVCSTARTUPTYPE="Manual"

; Install in English, regardless of system localization
ENU="True"

; Installs all features available in the package being installed.
ROLE="AllFeatures_WithDefaults"

; Can be used to override the above.
; Specifies features to install, uninstall, or upgrade.
; The list of top-level features include SQL, AS, RS, IS, and Tools.
; The SQL feature will install the database engine, replication, and full-text.
; The Tools feature will install Management Tools, Books online,
; SQL Server Data Tools, and other shared components.
; FEATURES=SQL,Tools

; Displays the command line parameters usage
HELP="False"

; Specifies that SQL Server Setup should not display the privacy statement when run from the command line.
; Required only when the /Q or /QS parameter is specified for unattended installations.
SUPPRESSPRIVACYSTATEMENTNOTICE="True"

; Required only when the /Q or /QS parameter is specified for unattended installations that include the Anaconda Python package.
IACCEPTPYTHONLICENSETERMS="False"

; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use.
; Required only when the /Q or /QS parameter is specified for unattended installations that include the Microsoft R Open package.
IACCEPTROPENLICENSETERMS="False"

; Required: Accept SQL Server license terms
IACCEPTSQLSERVERLICENSETERMS="TRUE"

; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="True"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="False"

; If this parameter is provided, then this computer will use Microsoft Update to check for updates.
USEMICROSOFTUPDATE="False"

; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"

; Specify the root installation directory for shared components. This directory
; remains unchanged after shared components are already installed.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the root installation directory for the WOW64 shared components. This 
; directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

; Specify the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Required: Specify a default or named instance. MSSQLSERVER is the default 
; instance for non-Express editions and SQLExpress for Express editions. 
; This parameter is required when installing the SQL Server Database Engine 
; (SQL), Analysis Services (AS), or Reporting Services (RS). 
INSTANCENAME="SQLEXPRESS"

; Optional: Specify a non-default Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
;INSTANCEID="SQLEXPRESS"

; Specify 0 to disable or 1 to enable the Named Pipes protocol.
NPENABLED="1"

; Setup will not display any user interface.
QUIET="False"

; Setup will display progress only without any user interaction.
QUIETSIMPLE="True"

; Set SA password (required for SECURITYMODE=SQL).  
; Complexity requirements not tested for configuration file.  Interactive 
; installation rejects passwords without special characters, even if they meet 
; the other 3 criteria (digits, lower- and upper-case letters).
SAPWD="MediaPortal"

; Enable mixed-mode authentication (default is Windows only)
SECURITYMODE=SQL

; Specifies a Windows collation or an SQL collation to use for the Database Engine.
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account. 
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Windows account(s) to provision as SQL Server system administrators. 
SQLSYSADMINACCOUNTS="BUILTIN\Administrators"

; Specify 0 to disable or 1 to enable the TCP/IP protocol.
TCPENABLED="1"

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
; It can only be used with the /ACTION=INSTALL and UPGRADE parameters.
; Normal is the default for non-Express editions and presents all setup dialog boxes for the selected features.
; AutoAdvance is the default for Express editions and skips nonessential dialog boxes.
; Incompatible with QUIET & QUIETSIMPLE
;UIMODE="AutoAdvance"

; If true, specifies that Setup should install into WOW64. 
; This command line argument is not supported on an IA64 or a 32-bit system. 
X86="False"

Command file (TVS-SQL-dependencies.cmd)

sc config TVService depend= MSSQL$SQLEXPRESS/SQLBrowser/Netman

Related

• SQL Server