Need to update SQL install documentation and install scripts for EA application

EA install document used for SQL installation missing steps. Also need to automate the installation of these steps.

1. Enable SQL compression for backups

2. Set SQL maximum memory and amount of memory required for SQL server.

3. Set SQL maximum degree of parallelism (MAXDOP)

4. Set SQL autogrowth setting for all database mdf and ldf files

   • MDF autogrow by 1 GB (1024 MB) or 8 GB (8192 MB)

   • LDF autogrow by 1 GB (1024 MB) or 8 GB (8192 MB)

5. SQL drive layout for SQL database mdf file, SQL transaction log file, SQL Tempdb. Each of these must be on separate drives.

   • Example: Drives D (SQL database), L (SQL logs), T (SQL Tempdb).

6. 
   

7. Control Panel "Power" be set to optimize for High Performance.

   • See Microsoft document https://learn.microsoft.com/en-us/troubleshoot/windows-server/performance/slow-performance-when-using-power-plan

8. Disable IPv6 on SQL server in network adapter TCP/IP properties.

   • GE doesn’t use IPv6 with its applications.

9. Network adapter “power management” should be disabled on SQL database server.

   • Go to Device Manager, select network adapter, choose properties, select tab for “Power Management”, uncheck the box for “Allow the computer to turn off this device to save power”.

10. Create a check during deployment process to catch if all CPUs on VM are all on 1 socket. You get a 30% CPU performance improvement by spreading multiple CPUs across 2 or 4 sockets. Note: SQL Standard edition does not support using more than 4 sockets. I recommend you choose 1 standard setting like "spread all VM CPUs across 4 sockets".

11. Automate the above settings using an install script.

12. Create a check during deployment to test speed of the drives presented by the customer to ensure they are fast enough for SQL database.

    • Drives for large databases over 250 GB space used in the mdf file should be SSD drives for

      • Tempdb drive,

      • database mdf file,

      • database transaction log file.

    • Simple test is copy 5 GB file from 1 drive to another within 1 minute. Simple and effective test and it only takes a few minutes to test. Drives that fail the test need to be fixed by customer before we install EA databases.

13. We get many support cases that the SQL backup jobs have been failing because the SQL service account is not being started with a domain user account. The SQL server service login should be a domain user with permissions to write to the backup file share. The backup should be configured to save to remote file share rather than a local drive at the time of GE installation .

    1. We should not have to use sp_mount_unc_path stored procedure to get access to a remote file share. Just set SQL service to use a domain user account.