Microsoft SQL Server setup

Last update 2023-07-25

The PDXpert System Setup installer automatically downloads and installs SQL Server, the PDXpert Application Server and a PDXpert client application. For example, PDXpert 10.0 (and later) installs SQL Server Express LocalDB. This is a secure, low-profile, minimal footprint version of SQL Server. It's intended to support workgroups of up to about 10 users. The installation is fast, and won't affect other database applications.

If you choose to install your own SQL Server database server instance, then install it before¹ installing the PDXpert Application Server.

If you want...	And your operating system is...	Then you will...
The default SQL Server Express or LocalDB2	Any compatible version of Windows or Windows Server	Skip this topic. Go to Custom installation: PDXpert application server
SQL Server Workgroup, Standard or Enterprise Edition	A 64-bit version of Windows or Windows Server	Install the 64-bit version of the desired SQL Server edition (unless you have an existing 32-bit version installed)
SQL Server Workgroup, Standard or Enterprise Edition	A 32-bit version (not recommended) of Windows or Windows Server	Install the 32-bit version of the desired SQL Server edition. SQL Server 2014 is the final 32-bit version that Microsoft released, and is now considered obsolete.

Notes for installing SQL Server Express or higher

Do not use SQL Server's default installation settings. PDXpert requires SQL Server settings that can be configured only during a custom install. A default installation doesn't support changes to the required settings, and you will have to uninstall, and then reinstall, SQL Server.

SQL Server and the PDXpert Server must be installed on the same machine.

Do not install SQL Server on a read-only, compressed, network-mapped, or external (e.g., USB) volume. Do not install SQL Server on a Domain Controller.

Use a named instance. Do not use the default instance.

Do not create new objects (e.g., views) within the PDXpertDB database, since unrecognized objects may interfere with proper operation or be deleted during upgrade.

When using SQL Server Express, create a separate (dedicated) instance exclusively for the PDXpert database. Microsoft constrains Express performance per instance, and PDXpert requires all of the Express instance resources. Do not attach other databases to the same SQL Server Express instance.

When using SQL Server Standard and Enterprise editions, consider creating a separate (dedicated) instance exclusively for the PDXpert database. The PDXpert Server service may restart the instance after an upgrade.

After all tasks are finished, check for SQL Server updates from Microsoft's website.

This topic describes how to install SQL Server Express or a higher version. Install SQL Server LocalDB using the standard settings.

• This page summarizes the most important SQL Server installation instructions. Complete information is available on the Microsoft web site.
• Before installing SQL Server for use with PDXpert, read Microsoft's Hardware and Software Requirements for Installing SQL Server. Note that Windows Installer creates temporary files on the system (typically C:) drive. Before you install or upgrade SQL Server, verify that you have enough disk space on the system drive for these files. Even if you install to a non-system drive, SQL Server requires space on the system drive.
• Windows Installer 4.5 is a prerequisite for recent SQL Server versions. This may require a separate download for your operating system before running the SQL Server installer.

• SQL Server requires PowerShell 3.0 or higher. Windows Server 2008 R2 and Windows 7, and all later releases of these, already have Windows PowerShell 3.0 or higher.

• Recent SQL Server versions require Microsoft Visual C++ 2017 Redistributable (latest releases; 2017 x64; 2017 x86).

• The SQL Server database instance must be installed on the same computer that you'll use for the PDXpert Application Server.

• The SQL Server database instance uses the local System account (NT AUTHORITY\SYSTEM) with SQL Server authentication and Windows authentication (also called Mixed Mode). If your SQL Server administration policies do not allow the use of the Local System account, then you must follow the alternative procedure (below).

• For PDXpert releases 9.3 and earlier, the SQL Server database instance must be named PDXPERT and have its TCP/IP protocol enabled.

Installing on a computer that already has SQL Server Express or higher

You can install a new SQL Server instance on a computer that already has SQL Server installed.

Different SQL versions (say, SQL Server 2017 and 2022) and different SQL editions (for example, SQL Server Express and SQL Server Enterprise) will work on the same machine.

There is one exception: If you have a 64-bit version of Windows with 32-bit SQL Server already installed, do not use the PDXpert system installer to install SQL Server. Manually install a new instance of 32-bit SQL Server using the configuration values — local system account, mixed mode authentication, etc. — described on this page. and then install the PDXpert System without database (db=0). The PDXpert System installer relies on Windows' 32b/64b configuration to determine which SQL Server Express to download from Microsoft, and we've seen that 32b and 64b SQL Server instances don't work well together.

If possible, install the new SQL Server instance using the same Windows administrator account that was used to install the existing SQL Server instance.

If you're accessing PDXpert database views in the SQL Server PDXPERT instance, and another SQL Server instance exists on the server, then enable the SQL Server browser service. Otherwise, PDXpert doesn't require the SQL browser.

SQL Server installation example §

SQL Server Express download §

Download the desired SQL Server Express option.

• 2016 SP2: https://www.microsoft.com/en-us/download/details.aspx?id=56840
  and latest Cumulative Update: https://support.microsoft.com/en-us/help/3177312/sql-server-2016-build-versions
• 2017: https://www.microsoft.com/en-us/download/confirmation.aspx?id=55994
  and latest Cumulative Update: https://support.microsoft.com/en-us/topic/kb4047329-sql-server-2017-build-versions-346e8fcd-c07c-5eeb-e10b-e3411ba8d8dd
• 2019: https://www.microsoft.com/en-us/download/details.aspx?id=101064
  and latest Cumulative Update: https://support.microsoft.com/en-us/help/4518398/sql-server-2019-build-versions
• 2022: https://go.microsoft.com/fwlink/p/?linkid=2216019

SQL Server 2016 and later can only be installed on 64-bit Windows.

Installation procedure§

In this example, SQL Server 2019 Express edition is installed on a new, fully-updated Windows Server 2019 operating system.

1. After starting the SQL Server installer, select the Custom option and click the Install button. (This panel isn't displayed when installing SQL Server 2014 or earlier.)

2. After the SQL Server Installation Center is displayed, click the New SQL Server stand-alone installation… option. This opens a separate SQL Server Setup wizard.

3. Read each panel, respond if needed, then click the Next > button. When the Feature Selection panel is shown, confirm the Database Engine Services checkbox is marked. Click Next >.

   When installing SQL Server 2022, you can skip installing the Azure Extension for SQL Server by clearing its checkbox.

4. In the Instance Configuration panel, select Named instance: and type PDXPERT. Confirm the Instance ID: is also PDXPERT. Click Next >.

   Although you can assign any instance name, using PDXPERT is helpful later. When the PDXpert Server service starts, if it doesn't have a connection string, then it automatically seeks and connects to the PDXPERT named instance.

5. In the Server Configuration panel, assign accounts to the SQL Server services.

   If your IT policies do not permit using the NT AUTHORITY\SYSTEM account, you can assign a Windows administrator account instead.

   • Agent (if displayed):  Account Name as NT AUTHORITY\SYSTEM with the startup type as Automatic.
   • Database Engine: Account Name as NT AUTHORITY\SYSTEM with the startup type as Automatic.

   • Browser: Account Name as installer's default value, and select the startup type as Automatic.

     You can select Disabled if there are no other SQL Server instances on this machine.

     

     If the account isn't in the list, select <<Browse…>> to add the SYSTEM account name.

   Click Next >.

6. In the Database Engine Configuration panel's Account Provisioning tab:

   • Select the Mixed Mode radio button, then enter and confirm a strong password, like 1qwe!ASD The PDXpert Server will overwrite this later with a random strong password, so you don't need to keep a record of it.

   • If there's no user account displayed, click the Add Current User button.

     Add other user accounts as needed for database administration with SQL Server Management Studio or other tools.

   • Add the SYSTEM as a SQL Server administrator:

     1. Click the Add... button.

     2. In the Select Users or Groups window, enter SYSTEM (or the assigned Windows administrator account) into the textbox, and click the OK button.

        

     Click Next >.

7. After the installation is complete, you can verify that the configuration settings are correct by opening the Summary.txt log file, typically located at C:\Program Files\Microsoft SQL Server\version\Setup Bootstrap\Log

   For SQL Server 2022, version is 160; 2019 is 150; 2017 is 140; 2016 is 130; 2014 is 120; and 2012 is 110.

   In particular, note

   • the INSTANCENAME is PDXPERT (or the assigned instance name)
   • the SECURITYMODE is SQL
   • the SQLSVCACCOUNT is NT AUTHORITY\SYSTEM (or the assigned Windows administrator account)

8. Close the SQL Server 20… Setup and SQL Server Installation Center windows.

9. After SQL Server has been installed, install PDXpertSystemSetup.exe using the db=0 command line option. See PDXpert Application Server setup, Procedure B for important instructions.

Using an assigned Windows administrator account§

Normally, there's no relationship between this account name and any PDXpert user account name.

Modify the SQL Server installation procedure (above) if your SQL Server administration policies don't allow applications to use the Local System account:

1. Create a new Windows user account (for example, WinAdmin) and assign a password. Change the account type to Administrator.

2. Use the Installation procedure above. During installation:

   1. In the Server Configuration panel, assign the SQL Server Database Engine's Account Name as the Windows administrator (e.g., WinAdmin) with the assigned password.

   2. In the Database Engine Configuration panel's Account Provisioning or Server Configuration tab, click the Add... button, then enter the Windows administrator account (e.g., WinAdmin).

   3. When installing the PDXpert Server, use this Windows administrator account in the PDXpert Application Server setup, Procedure B instructions.

Configuring remote database connections§

After installing SQL Server, database connections are only allowed on the server machine. Remote connections from a network computer are disabled by default.

If other network-attached computers connect to SQL Server (for example, to connect CAD or ERP/MES/MRP via ODBC), then you must enable database connections.

Starting with PDXpert 8.0, the PDXpert client does not connect directly to SQL Server database, and does not use TCP port 1433. Only PDXpert 7.5 and earlier need port 1433 enabled.

• SQL Server instance must allow remote connections. Although you cannot use SQL Server Express LocalDB, you can use SQL Server Express.
• SQL Server's TCP/IP protocol must be enabled.
• TCP Dynamic Ports should be removed, and the TCP port specified as 1433.
• Your server's firewall must allow communication with SQL Server through TCP port 1433.
• Specify the instance in your connection.

Remote connection configuration example

This brief example offers one possibility for configuring SQL Server. It assumes that PDXPERT is the only named instance on your server machine, and uses the default TCP port. To learn about the many other possible options, search the web for SQL Server instance remote connection.

Allow remote connections

To allow remote connections, SQL Server Management Studio must be installed on the server machine.

Open SQL Server Management Studio and connect to the PDXpert instance (typically named PDXPERT), then:

1. In the Sql Server Management Studio window, select the {server name}\{instance name} node (like MyServer\PDXPERT) and select Properties from the context menu.

2. In the Server Properties - ... window, select the Connections page from the left side panel.

3. In the Remote server connections section, mark the Allow remote connections to this server checkbox and set the Remote query timeout value (default is 600).

Enable the TCP/IP protocol

To enable the TCP/IP protocol using the SQL Server Configuration Manager:

1. From the Windows Start menu, choose All Programs, point to Microsoft SQL Server 20…, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the Sql Server Configuration Management window, expand the SQL Server Network Configuration node, and then select Protocols for PDXPERT.

3. In the list of protocols displayed in the main panel, the TCP/IP protocol must be enabled. If you see that the TCP/IP protocol is disabled: click the TCP/IP protocol to select it, and then select Enable from the Action menu or context menu.

   

Specify the TCP port for the connection

Continuing in the SQL Server Configuration Manager:

• Right click on enabled TCP/IP row, and select the Properties menu command. In the TCP/IP Properties window, select the IP Addresses tab and find the IPAII section.
  • If the TCP Dynamic Ports value contains 0 (the "dynamic ports" setting), delete the 0 so the value is empty.
  • Set the TCP Port value to 1433.

Close the Sql Server Configuration Manager window. Reboot your computer — or restart the SQL Server (PDXPERT) service — to refresh the configuration.

Open the firewall

The Microsoft Windows Firewall closes port 1433 by default. You must reopen the port to listen for incoming client connections using TCP/IP. See: Configure a Windows Firewall for Database Engine Access.

If your server uses a different firewall, refer to that software's instructions.

Specify the instance in the connection

You must specify the SQL Server instance in your connection string by adding it to the server machine name (for example, 10.12.14.16\PDXPERT) or IP address (MYSERVER\PDXPERT). See: Creating a Valid Connection String Using TCP IP

Creating a read-only account for ODBC clients §

PDXpert has a built-in PDXpertViewer read-only account for querying public views in the [viewer] schema. To allow ODBC clients access to data in the default [dbo] schema, you must create a new user account in SQL Server.

If your ODBC client is not on the server machine, then SQL Server must be configured for remote connections.

To create a new read-only user account using SQL Server Management Studio:

1. In the Object Explorer, go to the {servername}\PDXPERT instance > Security > Logins node.
2. Right-click and select New Login... command.
3. In the General section:
   1. Enter a new Login name: like PDXpertReader
   2. Select ◉ SQL Server authentication with a strong password and other account settings.
   3. Set the Default database: as PDXpertDB
4. In the Server Roles section, mark the public server role.
5. In the User Mapping section:
   1. In Users mapped to this login: list, mark the PDXpertDB database and set the Default Schema as dbo
   2. In the Database role membership for: PDXpertDB list, mark the db_datareader and public checkboxes.
6. In the Status section, confirm that:
   1. Permission to connect to database engine: ◉ Grant
   2. Login: ◉ Enabled

 

Notes

1. The PDXpert 10.0 (and later) system setup offers a command-line option that allows the SQL Server instance to be selected after the PDXpert application server is installed.

2. PDXpert system releases install different SQL Server Express versions:

   PDXpert 10.3 (and later) installs Express 2014 LocalDB.

   PDXpert 10.0 through 10.2 installs Express 2012 LocalDB.

   PDXpert 8.0 through 9.3 installs Express 2008 R2.

   PDXpert prior to 8.0 installs Express 2005.