REMEMBER TO SET DESIGNER OPTIONS

www.remmis.com\SQLEXPR_x64_ENU.exe  EXPRESS
www.remmis.com\vs_SSMS.exe

www.remmis.com\SQL2022-SSEI-Expr.exe  EXPRESS
www.remmis.com\SQL2022-SSEI-Eval.exe  STANDARD
www.remmis.com\sql2017_x64.exe
www.remmis.com\sql2017_MS_x64.exe
www.remmis.com\SQLData.zip

www.remmis.com\DRESQLData.zip
www.remmis.com\DRESQLDataOLD.zip

Version Query
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

1. Install sql2017_x64.exe
   a. Make sure Browser is set to Automatic
   b. Set Authentication to Mixed Mode
   c. Set SA password to Remmis123
   d. Deselect all but Database Services in choices window
   e. Continue finishing setup	

2. Install sql2017_MS_x64.exe (Management Studio)
   a. Cancel Restart (only needed for Azure) 	

3. Open SQL Configuration Manager 
   a. Click on SQL Server Network Config (usually second from bottom)
   b. Click on Protocols for SQLExpress
   c. Enable TCP/IP (right click)
   d. Right click on TCP/IP and select Properties
   e. Click IP Addresses Tab
   f. Set IPAll TCP port to 1433 and close properties window
   g. Set ALL TCP Dynamic Ports to 0(zero)
   h. Click on SQL Server Services (usually on top)
   i. Stop SQL Server Browser (right click) 				 
   j. Stop SQL Server (SQLEXPRESS) (right click)
   k. Start SQL Server (SQLEXPRESS) (right click)
   l. Start SQL Server Browser (right click)

4. Turn of Firewall or Open Filewall Ports(see below)
   
   www.remmis.com\sql_ports_setup.docx
   
   Setup TCP and UDP rules (one rule for each)
   a. Click Start > Run and type firewall.cpl. The Windows Firewall window opens.
   b. Click on the “Advanced Settings” link on the left pane. The Windows Firewall with Advanced security window opens.
   c. Click on the “Inbound Rules” option.
   d. On the left pane, click on “New rule”.
   e. Under “Rule Type” select the option “Port” and click next.
   f. Select “TCP” or "UDP" and “specific local ports” options.
   g. Key in the port number.
      TCP 135, 1433-4022
      UDP 1434
   h. Click Next.
   i. Select the option “Allow the connection”.
   j. Click Next, do not change any option here and click Next again.
   k. Specify a name for this rule. RemmisTCP or RemmisUDP
   l. Click Finish.
   
   Open SQL Configuration Manager
   a. Stop SQL Server Browser (right click) 				 
   b. Stop SQL Server (SQLEXPRESS) (right click)
   c. Start SQL Server (SQLEXPRESS) (right click)
   d. Start SQL Server Browser (right click)

Error:
ODBC Driver 17 for SQL Server
Delete the following:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations
 

Sample Connection String

Driver={SQL Server};server=SERVERNAME\SQLExpress;uid=sa;pwd=Remmis123;Trusted_Connection=No;database=Remmis

This connection string in found in r:\Remmis\Tables\SQL.loc  (r = server share letter)

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System]
"EnableLinkedConnections"=dword:00000001

Other:

DigitalRE.ini

[Installation Type]
Closing Attorney
 
[Path]
c:\DigitalRE\
c:\DigitalRE\Tables\
c:\DigitalRE\Files\
C:\Program Files (x86)\DigitalRE\Templates
c:\DigitalRE\Temp\
c:\DigitalRE\Setup\
 
[Message]
Auto
 
[Display]
16771022
13487565

Remmis.ini

[Installation Type]
Closing Attorney
 
[Path]
c:\Remmis\
c:\Remmis\tables\
c:\Remmis\files\
C:\Program Files\Close IT Plus\templates\
c:\Remmis\temp\
c:\Remmis\controls\
c:\Remmis\setup\
c:\Remmis\files\
 
[Message]
Auto
 
[RelCode]

False
[BC]

[LastCltNo]

TROUBLESHOOT SLOWNESS

https://www.google.com/search?q=how+much+ram+should+a+sql+server+standard+have&sca_esv=d3b625d2bcd07c3a&sxsrf=ANbL-n7vOwbWbyHBoXEX2LibGCAlEM1DOg%3A1777501277753&ei=XYTyac_ILYqu5NoPjJPFgAM&biw=1904&bih=910&sclient=gws-wiz-serp&fbs=ADc_l-aN0CWEZBOHjofHoaMMDiKpFZYo9qIX3pM4_jubAu4b8Go4RDwe7bFpL3uSo4ZTks1D9lv1s0uW0kp3GrJ0MoWAQoRe3zuKkQbcrzU3DzdrpJKz6AYqc1V00HstmeWL-0JuvT2HFPArGIjIYhidCnDxmiWtSIHPCYD3tn9lDlUgY9weMceQS8sZWZnV1QBKLTgPsV9NrA-3tlx-v0bFvlD1sGKLpQ&aep=10&ntc=1&mstk=AUtExfAgYXBRLtN3J53eEogMn4KTZSCgINAMXoZR8SI14eq0qEsiJERONfVD0A_2Qc8BACs5h8SapxAW-uAaJzERJVL1GudIZ3Fw2zVg9o2bDv3oCPJhNlLBvFHrx3zeG0wzaTcK9WuaBGxIlKMXzYANEyPY8t2xei4M3OWZEs7B1yGO090uqduLdO20hJdkTdqzu3fRVBkWQQdxXHeYa2_fWLbFvBOpScs2uC_9G4wlWI42Grgi1CRCaHkIrQHPdowgxbl0220gP5p-kQ&aioh=3&csuir=1&sourceid=chrome&cs=0&hl=en-US&mtid=FYXyaYnlNdCkiLMP4Jf_6AU&lns_mode=cvst&udm=50

For database slowness perform these three non-disruptive steps:
1. Cap the Max Memory (If not already done)

If SQL Server isn't capped, it will grow until it chokes the Operating System.
Right-click the Server Instance > Properties > Memory.
Set Maximum server memory (in MB) to 20480.

2. Enable "Optimize for Ad hoc Workloads"
This prevents SQL Server from wasting RAM on single-use query plans, which is a common cause of performance degradation over time.
Right-click the Server Instance > Properties > Advanced.
Set Optimize for Ad hoc Workloads to True.

3. Check your Disk Latency
If performance drops as RAM fills up, SQL might be struggling to write "checkpoints" to your disk.
If your database is on an old mechanical drive or a slow virtual disk, the "restart fix" usually lasts only until the first heavy write-operation occurs.

Script 1: Why is it slow? (Wait Stats)
This script shows you exactly what SQL Server has been "waiting" on since the restart. It identifies if the bottleneck is CPU, Disk, or Memory.

This script shows you exactly what SQL Server has been "waiting" on since the restart. It identifies if the bottleneck is CPU, Disk, or Memory.
CXPACKET: Usually means high CPU usage or missing indexes.
PAGEIOLATCH_XX: Your Disk is too slow.
SOS_SCHEDULER_YIELD: Your CPU is overwhelmed.
RESOURCE_SEMAPHORE: Extreme Memory pressure.


-- Check what SQL Server is waiting for
USE [digitalre];
GO
SELECT TOP 10
    wait_type AS [Wait Type],
    wait_time_ms / 1000.0 AS [Wait Time (Seconds)],
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS [Percentage]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
    'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
    'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
)
ORDER BY wait_time_ms DESC;


Script 2: The "Quick Wins" (Missing Indexes)
This finds the top 10 indexes that would provide the biggest performance boost for your specific 10GB workload.

-- Find the top 10 most impactful missing indexes
USE [digitalre];
GO
SELECT TOP 10
    d.statement AS [Database.Schema.Table],
    s.avg_user_impact AS [Estimated Benefit %],
    s.user_seeks AS [Times This Would Have Been Used],
    d.equality_columns, 
    d.inequality_columns, 
    d.included_columns,
    'CREATE INDEX [IX_' + OBJECT_NAME(d.object_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns,''),',','_'),'[',''),']','') + '] ON ' + d.statement + ' (' + ISNULL(d.equality_columns,'') + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(d.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + d.included_columns + ')', '') AS [Create Script]
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
ORDER BY s.avg_user_impact * s.user_seeks DESC;

Script 3: Memory Health (Page Life Expectancy)
Since you have 26GB of RAM for 10GB of data, this number should be very high. If it's low, your RAM is being misused.
Result < 300: Your server is "thrashing" memory.
Result > 1000: Your memory health is good.

-- Check Page Life Expectancy (PLE)
USE [digitalre];
GO
SELECT [object_name], [counter_name], [cntr_value] AS [PLE_Seconds]
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Page life expectancy'
AND [object_name] LIKE '%Buffer Manager%';

Script 4: This script identifies Disk Latency (speed) for each of your database files. It is the best way to confirm if your storage is the "bottleneck" causing the high wait times you saw in Script 1.

-- Check Disk Latency for all digitalre database files
SELECT 
    DB_NAME(vfs.database_id) AS [Database Name],
    mf.name AS [Logical Name],
    mf.physical_name AS [Physical Path],
    vfs.io_stall_read_ms / vfs.num_of_reads AS [Avg Read Latency (ms)],
    vfs.io_stall_write_ms / vfs.num_of_writes AS [Avg Write Latency (ms)],
    CAST((vfs.size_on_disk_bytes / 1024.0 / 1024.0) AS DECIMAL(10, 2)) AS [Size on Disk (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id 
    AND vfs.file_id = mf.file_id
WHERE DB_NAME(vfs.database_id) = 'digitalre'
ORDER BY [Avg Write Latency (ms)] DESC;

Script 4: This script will look inside the SQL Server "Buffer Pool" (the part of RAM where data lives) and count exactly how many megabytes each database is currently occupying.

SELECT 
    ISNULL(DB_NAME(database_id), 'System / Resource DB') AS [Database Name],
    COUNT(*) * 8 / 1024 AS [Memory Used (MB)],
    CAST((COUNT(*) * 8 / 1024.0) / 1024.0 AS DECIMAL(10, 2)) AS [Memory Used (GB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY [Memory Used (MB)] DESC;


Summary of your "Stability Plan"
To stop the slowness from coming back, here is your checklist:
Cap the RAM: Set max server memory to 20480 (20GB). This stops the OS from crashing.
Enable Ad-hoc Optimization: (Code above) This stops the "System" from wasting RAM.
Create the Top Index: Run Script 2 and implement the index with the highest Benefit %. This makes queries "cheaper" for the CPU and RAM.
Monitor Latency: If slowness returns, run the Disk Latency script. If it's >20ms, your hardware (the disks) simply can't keep up with the data writes.