About SQL Server

About SQL Server

About SQL Server

Microsoft SQL Server is a relational database management system (RDBMS). PypeServer Enterprise and tools connect to a SQL Server instance or database, where all part, pipe, and other shared data is stored. The PypeServer Kiosk arrives with SQL Server Express installed.  SQL Server Express edition is the entry-level, free database best suited for small businesses. If you need more advanced database features, SQL Server Express can be seamlessly upgraded to other higher end versions of SQL Server. 

SQL Management 

PypeServer Kiosk configuration

  1. Each Enterprise site uses two SQL databases named “PypeServerAdmin” and “PypeServerDB”.
  2. Out of the box, PypeServer uses SQLBackupAndFTP for scheduling DB backups.
    1. PypeServer configures this to backup the two SQL databases to internal SSD, and to a separate small drive (a 16GB SD card or USB thumb drive) plugged into kiosk.
    2. Moving this backup data off the machine to both on and offsite data storage is highly recommended.
  3. It is highly recommended that you back your data up daily—off the kiosk.
  4. For zero data loss, consider building your own SQL server with raid 1 or raid 10 type capabilities.  Given the nature of pipe cutting, this is arguably an overkill.
  5. Data Growth (see below) is, in server terms. inconsequential.  As such, consider the relative low cost of full backups to preserve data in case of disk failure.

Database growth

  1. Databases typically grow between 5 and 20 Megabytes per year. 

Relocating the SQL server

Performance Considerations
  1. Note that any time that server is down, or the network (LAN) is down, then your machine will be down.
  2. In order to help you with many system issues, PypeServer personnel will need both RDP or TeamViewer connectivity to the server, and admin rights to the databases.
  3. Network Latency between PypeServer and SQL must be very low.  Any ping latency above 4ms will start to degrade performance.  See the section on WAN/VPN latency.
  4. If you're running firewalls on your server, you'll need inbound rules on your SQL server so that PypeServer can access it.  This is detailed in "About Networking".
  5. If you move your DBs and start having problems, then support for that will bill out hourly, as it is neither under warranty nor part of the support package.

SQL Configuration 

  1. 2022, 2019, 2017 and Azure are supported. PypeServer endeavors to not leverage features that may be deprecated.
  2. SQL Server Browser should be enabled on the Kiosk or other host system.
  3. The MS Distributed Transaction Coordinator service must be configured for manual or automatic startup.
    1. See "About Microsoft Data Transaction Coordinator" for more detail.

Relocation Steps

Before relocating a server consider what type of accounts and security users will require to connect. Network and SQL accounts and network security considerations are outside the scope of this article. Please see the "Other Articles" section for more information.

  1.  Enable TCP/IP on the server
  2.  Allow remote connections
  3. Turn off Firewalls on the server, or Add inbound rules through your firewall
    1. The rules described are for SQL Server Express.  You may need to adjust your rules from allowing the application (for dynamic ports) to allowing specific ports.  Your call, though I believe that the config for dynamic ports (as described in this doc) will work for non-dynamic ports.
  4. Restore the database backups to the new server. If using Azure then use SQL Server Management Studio to push the local databases to Azure.
    1. The database names are PypeServerDB and PypeServerAdmin
  5. Grant users read and write access to these two databases.
  6. Turn off the SQL Server on the PypeServer Kiosk
    1. DO NOT UNINSTALL SQL FROM THE KIOSK.  If you have problems with your server or debugging is required, you may elect to copy the databases from your server back to the kiosk for easier/less invasive access by PypeServer personnel.
    2. See the “Configure Database Backups” step for some further simple failover processes.
  7. From the PypeServer kiosk, under the user you intend to run as, use the SQL Server Management Studio to navigate to your new Server and make sure you have access to the databases.
    1. Try editing PypeserverDB.dbo.SystemConfigInfo—just add your company name or something.  If that works, then you probably have all the rights you need.
    2. If you cannot access the Server, try just turning off the firewalls on the Server to see if it is just a firewall IO rule.
  8. Redirect the PypeServer application to the new server location. Do this for the kiosk and for all remote seats by simply starting the PypeServer app and following the path to the dialog below.  
    1. When you restart PypeServer it may take up to a minute before it gives up trying to find the databases, and then it will prompt you that it cannot find the SQL servers.  Request Admin Privileges when the SQL Connections Manager dialog appears.
    2. Edit the server name and credentials and then test the connection. 
      1. If it fails (turns yellow) see the Troubleshooting section later in this article.
  9. If users or other services are accessing the PypeServer system for read-purposes (like for Excel Reporting, ERP integration, etc), add Read Access as needed.
  10. Configure database backups (Recommended/Reminder)
    1. To mitigate network issues, consider backing up your databases directly to the PYPESERVER kiosk. If your server or network goes down, one can start SQL on the kiosk, load the last saved DB to the local SQL server, and remap the kiosk to that server.

Troubleshooting

Firewall

The following ports/services are required to have incoming access through the firewall on the system hosting the SQL Server (usually the PypeServer Kiosk).
Ports
135     TCP Data Transaction Services
445     TCP Share/Print Services
1433   TCP SQL Server: Database access
1434   UDP SQL Browser: Remote seat connections
22350 UDP CodeMeter Browser: Remote seat connections
22350 TCP CodeMeter Server: Licensing Service
Services
codemeter.exe, msdtc.exe, spoolsv.exe, sqlservr.exe
More Information:  Please see "About Networking".
Enterprise connects to SQL using the Microsoft Object Linking and Embedding for Databases (MS OLE DB) interface. This system-level connection can be tested by creating a Universal Data Link (UDL) file, adding the server and account information, and then testing the connection.  The Enterprise HealthCheck tool can test a SQL connection by creating the UDL file for the user to validate the connection and determine if the issue is within Enterprise or the Microsoft network stack. 

Testing Microsoft Data Transaction Coordinator (DTC)

Enterprise uses the Transactions framework and MS DTC to ensure that any SQL operations that may be interrupted by hardware or network issues can be safely committed or rolled back in the event of a transaction failure. Some Enterprise functions will fail If the DTC service is not running or communication issues exist between the user's system and the SQL server. A DTC connection can fail because of firewall and/or network permission issues. 

The Enterprise HealthCheck tool will review the DTC settings and run a transactional test to confirm that the DTC connection is fully functioning. Microsoft also has a DTCPing diagnostics tool and a Test-DTC PowerShell command available for diagnosing DTC issues.
More information: Please see "About Microsoft Data Transaction Coordinator"

Other Articles

    • Related Articles

    • Import pipe inventory into SQL server

      This article is for IT professionals familiar with Microsoft SQL Server and related technologies The user assumes all responsibilities for any data integrity issues or corruption that may occur when updating a database using anything other than a ...

    • About License Server

      About CodeMeter The licensing system is the CodeMeter security system offered by WIBU (www.wibu.com). It consists of a set of licensees stored in a USB dongle or encrypted file at the PypeServer Kiosk. A WIBU CodeMeter License Service runs on the ...

    • System Requirements

      The article lists the recommended configurations for PypeServer Enterprise in a production environment. Hardware Kiosk The PypeServer computer is stored in a industrial kiosk stationed next to the cutting machine. I has the following minimum ...

    • About Networking

      About Networks Enterprise supports multiple systems working together in a distributed environment connected to one or more Enterprise site servers. The best performance is achieved within a high bandwidth/low latency local area network. High latency ...

    • About Network Latency

      Network Performance Enterprise supports multiple systems working together in a distributed environment connected to one or more Enterprise site servers where the best performance is achieved within a high bandwidth/low latency local area network. ...