Requirements

Microsoft SQL Server 2016 (or newer) Standard edition (or higher) is required for SureView to store its database in as per the Minimum Requirements.

The sections on this page provide additional guidance for what is required.

Location Options

SQL may be running either:

1. on your own SQL Cluster that your IT team is responsible for
2. on the SureView servers themselves which SureView are responsible for

Note that regardless of location, properly licensing SQL is the responsibility of the customer (see guidance below)

Guidance: High Availability

SQL must be set up for High Availability in production to ensure a server outage does not impact the SureView software. There are a number of facilities provided by SQL for this and there are three options that are supported by SureView:

• Database Mirroring: this is the simplest to configure and uses two identical database servers and a witness server, with one of the servers being active and then automatically failing over to the other server if the witness detects an outage. Note that this feature was "deprecated" in SQL 2012 in favor of the more complicated AlwaysOn Failover Clustering, however it still exists in SQL 2014, 2016, 2017, and 2019 and remains the simplest option for High Availability in Microsoft SQL Server.
  • Two identical database servers running SQL Standard Edition or higher (database fails over between them)
  • One smaller server running SQL Express Edition as the Witness
• AlwaysOn Failover Clustering: this works almost identically to Database Mirroring using a database that fails over between two servers, with the notable difference that your database servers must be set up in a Windows Server Failover Cluster (WSFC). This advanced solution may be used by your IT Team on your own SQL Cluster but SureView do not provide assistance with setup or support. Items required:
  • Two identical database servers set up in a Failover Cluster and running SQL Standard Edition or higher (database fails over between them)
  • A network file share on a third server (such as the domain controller) as the File Share Witness
• AlwaysOn Availability Groups (Enterprise Edition only): this is an advanced feature only included with SQL Enterprise Edition and builds on Failover Clustering by allowing more than one secondary server and the ability for the secondaries to be readable, allowing increased performance. This advanced solution may be used by your IT team on your own SQL Cluster but SureView do not provide assistance with setup or support.

Guidance: Licensing

Microsoft SQL has two licensing models to choose from depending on your needs:

• Server+CAL (available for Standard Edition only): a license is required for:
  1. Each server that is hosting live databases (so any failover server that is only hosting inactive mirror databases does not need a server license)
  2. Each 'client' connecting to SQL with options of:
     1. User: each user has a license and allows that person to use unlimited devices (for example you would have a User CAL for each user that may connect to SureView). This is best suited to customers with users that may be connecting from any number of devices so knowing how many devices are in use may not be possible.
     2. Device: each device has a license and any number of users can use it (for example each computer and mobile device that will be used by staff to access SureView has a license). This is best suited for customers who have specific devices that will be used to access SureView (such as computers in a SOC), with users not being able to log in from anywhere other than those devices
• Core-based (available for Standard and Enterprise Editions): a license is required for every CPU Core that has been assigned for SQL to use (processor affinity) and there are no restrictions on number of users or devices. This is best suited for larger organizations whose IT team have volume license agreements with Microsoft, already have a SQL Cluster, or have a large number of users and devices where it would not be cost effective to buy individual CALs for each of them.

Note that the licenses are Version and Edition specific but allow older versions to be used i.e:

1. A User CAL for SQL 2016 allows one user to access any number of databases on servers running SQL 2016, 2014, 2012 etc, but not on SQL 2019 (being a newer version than the CAL covers).
2. A server license for SQL 2016 Standard allows one server to be hosting live databases using SQL Standard 2016, 2014, 2012 etc, but not SQL 2019 Standard (being newer), and not SQL 2016 Enterprise (being a different edition)

Guidance: Editions

Microsoft have a number of editions of SQL available as follows:

• Standard: has very high production-level performance limits and supports all major features including "Database Mirroring" and "AlwaysOn Failover Clustering" and for High Availability. This is ideal for most customers
• Enterprise: has no performance limits and adds the advanced "AlwaysOn Availability Groups" high availability feature. This is best suited for larger organizations whose IT team have volume license agreements with Microsoft
• Web & Express: Express has performance limits that prohibit production use and neither can  perform High Availability (both can only be a witness in a failover setup). As a result these can only be used on test servers and are not permitted for production use by SureView
• Developer: is identical to Enterprise edition but is not permitted for use in production environments by Microsoft. As a result this can only be used on test servers

Guidance: Encryption At Rest

Customers may choose to encrypt their SQL database at rest via the Transparent Data Encryption (TDE) feature of SQL. The setup and maintenance of this is the responsibility of the customer and it has no impact on SureView as it is all inside SQL.

Guidance: Encryption In Transit

Customers may choose to encrypt communications with their SQL database via the encrypted connections feature of SQL. The setup and maintenance of this is the responsibility of the customer and it is supported by SureView via the connection string options for encryption.

Guidance: Multi-Server Access

For multi-server deployments you will need to expose the SQL service over TCP (usually on port 1433) so that the other servers can connect to it.

This needs two steps on the database servers:

1. Set SQL to listen on a TCP port: using the "SQL Server Configuration Manager" MMC Snap-In (see screenshot below). Note:
   1. To open the snap-in run "mmc.exe" and go to "File"->"Add/Remove snap-in..."
   2. The "TCP/IP" protocol will need to be Enabled and set to a fixed port, ideally on all IP addresses the server has (the "IPAll" section - see screenshot below)
   3. Remember to restart the SQL Server service to apply any changes
   4. After you have performed these steps you should be able to connect to the port locally on the database server
      
2. Open the port in the firewall: using the Windows Defender Firewall" MMC Snap-In so that the other servers can connect to it. Note:
   1. To open the snap-in run "wf.msc"
   2. After you have performed this step you should be able to connect to the port remotely from other servers on the network

Guidance: Mirroring

WARNING: recent Management Studio versions have bugs that stop the Mirroring Setup from working properly when done via the user interface, showing misleading errors and leading to mirroring failures. As a result you must perform the Mirroring setup using the provided queries rather than the GUI.

Prerequisites

For mirroring to work you must have the following set up and confirmed:

1. Servers of:
   1. Two servers with SQL Standard Edition or higher installed (main database servers i.e. "server1" and "server2")
   2. One server with SQL Express Edition or higher installed (witness)
2. All 3 servers must have:
   1. the SQL Service running as the same domain username (you can use the SureView Service account for this)
   2. fully qualified domain names that resolve to the correct IPs of the servers (i.e. server1.mydomain.com, server2.mydomain.com, and witness.mydomain.com) - you can use the Hosts file for this if needed.
   3. access across the network to the other two servers on TCP ports 1433 and 5022
3. On all 3 servers you must be able to use SQL Management Studio to connect to SQL via all 3 fully qualified domain names (i.e. on "server1" use Management Studio to connect to "server1.mydomain.com", "server2.mydomain.com", and "witness.mydomain.com"
4. The SureView database must be installed on server1 (no database installed on server2 or witness)

Preparation

Perform the following steps to prepare the servers and databases for mirroring:

Endpoint creation and testing

Run the following query on each of the servers to start them listening on port 5022:

On server 1 and server 2:

-- On server 1 and server 2:
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT=5022, LISTENER_IP=ALL) FOR DATA_MIRRORING (ROLE=PARTNER, AUTHENTICATION=WINDOWS NEGOTIATE, ENCRYPTION=REQUIRED ALGORITHM AES)

On the witness:

-- On the witness:
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT=5022, LISTENER_IP=ALL) FOR DATA_MIRRORING (ROLE=WITNESS, AUTHENTICATION=WINDOWS NEGOTIATE, ENCRYPTION=REQUIRED ALGORITHM AES)

Then test that each server can connect to port 5022 on each of the three servers i.e. on each of the three servers, run the following commands, replacing the names of the servers with your own:

telnet server1.yourdomain.com 5022
telnet server2.yourdomain.com 5022
telnet witness.yourdomain.com 5022
(repeat on all 3 servers)

Database recovery mode configuration

On the server containing the databases that you will be mirroring, set the databases to use "Full" recovery mode by running the following query, replacing the database name:

-- On server 1 where the database you want to mirror is:
ALTER DATABASE [YourDatabase] SET RECOVERY FULL

Instructions

Perform the following actions to enable mirroring for a database:

Backing up the database on server 1

On server 1 that contains the databases that you will be mirroring, perform a backup of the database and log to the same file by running the following queries, replacing the database name and backup file name:

-- On server 1:
BACKUP DATABASE [YourDatabase] TO DISK='yourbackupfile.bak' WITH FORMAT
BACKUP LOG [YourDatabase] TO DISK='yourbackupfile.bak'

Restoring the database to server 2 in recovery

Copy the backup file created at the step above from server 1 to server 2, then restore the database and transaction log without recovery by running the following queries, replacing the database name and backup file name:

-- On server 2 having copied the backup file over:
RESTORE DATABASE [YourDatabase] FROM DISK='copiedbackupfile.bak' WITH File=1,NORECOVERY,REPLACE
RESTORE LOG [YourDatabase] FROM DISK='copiedbackupfile.bak' WITH File=2,NORECOVERY

Enabling Mirroring

On server 2 tell it the location of server 1 by running the following query, replacing your database name and the address of server 1:

-- On server 2 (the one currently 'In Recovery'):
ALTER DATABASE [YourDatabase] SET Partner=N'TCP://server1.yourdomain.com:5022'

On server 1 tell it the location of server 2 and the witness then enable mirroring by running the following queries, replacing your database name and the addresses of server 2 and the witness:

-- On server 1 (the one currently with a working database):
ALTER DATABASE [YourDatabase] SET Partner=N'TCP://server2.yourdomain.com:5022'
ALTER DATABASE [YourDatabase] SET Witness=N'TCP://witness.yourdomain.com:5022'
ALTER DATABASE [YourDatabase] SET SAFETY FULL

Helpful Queries

To see the mirroring status connect to any server and run the following queries (shows the connection state of all servers, same as the Database Mirroring Monitor program shows):

SELECT * FROM sys.database_mirroring
SELECT * FROM sys.database_mirroring_endpoints

To perform a manual failover with both servers online, connect to the current PRINCIPAL server and run the following query:

ALTER DATABASE [YourDatabase] SET PARTNER FAILOVER

To force service of the mirror (if both the principal and witness are offline) connect to the MIRROR and run the following query (WARNING: doing this may result in data loss):

ALTER DATABASE [YourDatabase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

To remove mirroring from a server run the following query:

ALTER DATABASE [YourDatabase] SET PARTNER OFF

Troubleshooting

"The target principal name is incorrect" errors when connecting after changing SQL Service user account

Sometimes after changing the user account that the SQL Server service is running as you may be unable to connect getting errors saying "The target principal name is incorrect".

To resolve this you must open the "Active Directory Users and Computers" MMC snap-in on your domain controller, enable Advanced Features via the View->Advanced Features menu, then find the computer account for the server with the issue, open the Properties for it, view the "Attribute Editor" tab, find the "servicePrincipalName" entry, and delete any starting with "MSSQLSvc/...". Then restart the SQL Server service and you will be able to connect.