1. Microsoft SQL Server Requirements
The WIN-911 Standard, Interactive or Advanced license uses MS SQL Server to manage its configuration. This choice of data layer provides a great deal of flexibility, and this article aims to provide some guidance and information on utilizing MS SQL Server products.
A. Supported SQL Server Versions/Editions
WIN-911 includes Microsoft SQL 2017 Express which should be sufficient for most if not all installations of WIN-911. Other editions and versions of MS SQL Server are supported as described below.
Higher editions of SQL Server provide added features such as enhanced performance for larger data sets, greater reliability through failover clustering, and improved auditing. If your organization already owns a higher edition of MS SQL Server, such editions are fully supported by WIN-911. Moreover, other versions of MS SQL Server are also supported; Microsoft SQL Server 2008 R2 through 2017 have been tested for compatibility.
You can compare product features across all editions of SQL Server 2017.
System Requirements for MS SQL Server 2017 can be found in the Hardware and Software Microsoft Documents.
B. SQL Deployment Considerations
The Installer for WIN-911 will detect a network-accessible instance of SQL Server named WIN911 and will offer to install a new instance locally if not detected. While this option is easy to accept, you also have the option to decline this installation and select an existing SQL Server instance later in the installation process. WIN-911 need not reside on the same physical machine as its SQL Server instance; distributed WIN-911 systems need not keep all of their configurations in one instance. The following information is provided to help guide your decision on how to deploy SQL to meet the needs of your organization.
WIN-911 utilizes a web server, Internet Information Services (IIS), to host its configuration GUI; so it must be installed on a Windows system running IIS. For enhanced security, it is advisable to separate web and database servers. When installing WIN-911 and Microsoft SQL Server on the same machine, we recommend the use of Firewalls to restrict access to IIS.
For more information, please reference Microsoft’s Security Considerations for a SQL Server Installation.
For larger WIN-911 configurations, it may be advisable to install SQL Server on a separate server. This is especially true for servers at or near the minimum system requirements. Without initial performance tuning, SQL Server is designed to run at peak performance and consume a significant portion of RAM. Installing SQL on another machine shifts this resource utilization elsewhere to allow improved performance on the local machine. If you choose to install SQL and WIN-911 on the same machine, you should consider utilizing CPU affinity masks for SQL and IIS to isolate the two on separate cores and configuring SQL to reserve less RAM.
For information, please reference Microsoft’s TechNet articles regarding SQL Server monitoring and Performance Tuning.
With SQL Server Express, configuration backup and restore procedures are not automatic and may be easier to perform if all of the configurations for WIN-911 reside in the same instance. Distributed WIN-911 system modules may share an instance of SQL Server to facilitate such maintenance, or you may wish to trade this convenience for added security or performance as outlined above. When distributing a WIN-911 notifier module in the DMZ, we recommend that its SQL database be hosted on a separate instance or with separate credentials for added security.
C. SQL Server Permissions
While installing WIN-911, the user performing the installation must have the ‘dbcreator’ role assigned on the targeted SQL Server instance. If the WIN-911 installer process previously installed Microsoft SQL Server 2017 Express, then sufficient permissions will be applied to the instance automatically as the installing user will be the owner and a 'sysadmin' role by default. These permissions can be relaxed after installation but will need to be restored to 'dbcreator' for future upgrades.
Additionally, The WIN-911 Service account specified during installation will need permission to read, write, alter, drop and add tables within the created SQL database. The install process will validate that the Service account has the 'sysadmin' role assigned to it. Again, these permissions can be relaxed to 'dbcreator' but must be restored prior to attempting upgrades.
When utilizing an existing SQL Server instance, you should make both the WIN-911 Service account and the installing user account (if different) 'sysadin' users for the duration of the installation. The installation process will validate that both users have sufficient permissions prior to installation.
If your SQL Server is located on another machine, you will need to provide authentication across the network. For domain environments, the domain server will function as the authentication provider and is accessible from both machines. For Workgroup environments, identical user credentials must exist on both machines. For example, the WIN-911 machine (WIN911SRV) and the SQL machine (SQLSRV) will both have a user (SamanthaSmith) with the exact same username and password. During the WIN-911 installation process, you would enter in the local username, e.g. WIN911SRV\SamanthaSmith. In SQL Management Studio you would need to add permissions for SQLSRV\SamanthaSmith, ensuring that you add the SQL server name and not the WIN-911 machine name.
D. Network Details
By default, MS SQL Server utilizes TCP port 1433 for network communications. If accessing SQL remotely, please ensure that this port is opened in your firewall.
The Windows Service SQL Server Browser must be running on a remote SQL host in order to discover that instance across the network. In addition, UDP port 1434 must be opened in your firewall to allow the discovery of the SQL server.
When connecting to an unnamed instance of SQL Server, the syntax expected by the WIN-911 installer is "<host>\" where <host> is the computer name of the Windows machine where the instance is installed.