The images on the left section describes an example of a known, working setup of SQL Server 2017. The images on the right section describe an example of a known working setup of SQL Server 2022.
How Large Should my SQL Server be??
Microsoft can't recommend more than 1 core and 2gb of RAM on the SQL machine, because they do not know what applications you are running on SQL. LAW has alot of network traffic with SQL and for hat reason we have come up with this equation to help right size your SQL Server.
Calculating LAW SQL Cores
•At minimum – the Total Number of SQL Cores should = The # of total LAW instances* that can be communicating or interacting with SQL ÷ 3
o*Some LAW components are single threaded and others are muti-threaded. If you using ED Loader, then count how many times you can open LAW. If you are using Turbo add how many times you can open LAW and how many cores are available in your Turbo pool.)
•Add one core for the OS
Calculating LAW SQL RAM
•At minimum – the Total amount of SQL RAM should = The # of total LAW instances* that can be communicating or interacting with SQL × 4
oNote when RAM starts to go over 100gb it is more of a bell curve and must be adjusted by each environment.
Installation Steps
Step 1: Install SQL Server 2017 and 2022
SQL Server 2017 and 2022 with an installation of Microsoft SQL Server Management Studio 17 & SQL Server Management Studio (SSMS) 20.2.1
1.Download the installer file for SQL Server 2017 or SQL Server 2022 from the Microsoft website.
2.Run the installation file. The SQL Server 2017 or SQL Server 2022 installation window opens.
3.Select Custom then follow prompts to continue.
4.Specify SQL Server media download target location will open in a new window. Browse to the location that you would like to save your SQL files.
5.After setting the installation path, then select Install.
6.In SQL Server Installation Setup dialog box select the Installation tab.
7. Select Install SQL Server Management Tools to download and install SQL Server Management Studio 17.6+ or SQL Server Management Studio (SSMS) 20.2.1
Microsoft SQL Server Management Studio or SSMS is a software application that is used for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server. (https://en.wikipedia.org/wiki/SQL_Server_Management_Studio)
8. Select New SQL Server Standalone installation or add features to an existing installation.
9.The SQL Server 2017 Setup window with License Terms will open next.
10. If you accept the license terms then select Next.
11. SQL Server 2022 you can select if you want to have the program check for updates. Make your selection and then select Next.
12. SQL Server 2022 when installing the rules you may get a warning on the Windows Firewall ports that need to be enabled. We suggest opening ports 1433 and 1434 after installation. Steps to do so can be found here
13. SQL Server 2022, for our purposes we do not need to connect to Azure. Please see your DB Admin for your companies needs.
13. Feature Selection window will open in a new window.
•SQL Server 2017 Setup both Database Engine Services and Client Tools Connectivity are required.
•SQL Server 2022 Setup only needs Database Engine Services.
•Please refer to your SQL database administrator for other features that you may need.
14. Select or deselect options accordingly to your SQL database administrator's guidelines, then select Next.
15. The Instance Configuration window will open. Select Named instance, if you would like to change the instance name. The default is SQLSERVER for SQL 2017 and MSSQLSERVER for SQL 2022
Make sure to document your installs so that server instances can easily be reproduced.
• Computer/server name where SQL is installed
• SQL system administrator password
• SQL Instance name
If you write your password down it recommended to keep it in a secure location, possibly off-site.
16. Select Next after making any necessary changes.
17.The SQL Server 2017 Setup: Server Configuration window will open. The services will vary depending on options chosen during Feature Selection.
18. SQL Server Browser service, under Startup Type, needs to be set to Automatic.
Some Services are not needed by LAW and/or Explore
You can install the SQL Server Agent if you intend to run jobs on the server or do maintenance tasks such as automated backups. However, this function is best handled by a DBA.
The other services (SQL Server Analysis and SQL Server Reporting Services) are unnecessary for LAW/CloudNine™ Explore and consume valuable resources on your server.
•It is best to locate your databases (.mdf and .ldf) and your tempdb on separate physical disks; preferably NOT a USB-connected disk. This makes it easier to troubleshoot disk access problems, should they occur.
•We do not recommend the use of NTFS data file encryption or compression on SQL server databases and log files.
19. Leave the password blank.
20. Click Next.
21. In Database Engine Configuration dialog box, select Mixed Mode (SQL Server authentication and Windows authentication).
22. In the Enter password and Confirm password fields, type the SQL System administrator password you want to use. Recommended: write the password down in a secure location.
23. Under Specify SQL Server administrators, click Add Current User. Add other users as necessary, and then click Next and then Install
24. When the installation is complete the Complete window will open.
25. Select Close.
Step 2: Add SQL use Account for LAW
1. Start SQL Server Management Studio.
2. Login to the new SQL Server instance.
3. Expand Security, right click Logins, and then click New User or New Login....
4. In Login name type LAW_User.
5. Select SQL Server authentication.
6. In the Password and Confirm password fields type (Contact Technical support for password assistance.).
7. Deselect Enforce password expiration, Enforce password policy, and User must change password at next login.
8. In the left panel of the dialog box, click Select Server Roles and then select dbcreator.
9. Click OK.
10. Close Microsoft SQL Management Studio.
You can use a custom login and password for SQL Server Authentication.
