Best practices for shopMaster

CREATE LOGIN shopmaster_sync WITH PASSWORD = 'YourSecurePassword123!';

USE YourDatabaseName;
GO
CREATE USER shopmaster_sync FOR LOGIN shopmaster_sync;
GO

-- Permissions on product table
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.YourProductTable TO shopmaster_sync;

-- Permissions for ChangeLog table
GRANT SELECT, INSERT, DELETE ON dbo.ChangeLog TO shopmaster_sync;

-- One-time permissions for initial configuration
GRANT CREATE TABLE TO shopmaster_sync;
GRANT ALTER ON SCHEMA::dbo TO shopmaster_sync;

REVOKE CREATE TABLE FROM shopmaster_sync;
REVOKE ALTER ON SCHEMA::dbo FROM shopmaster_sync;

SELECT 
    COUNT(*) AS TotalRecords,
    MIN(ChangeDateTime) AS OldestChange,
    MAX(ChangeDateTime) AS NewestChange
FROM ChangeLog;

-- Create a view for monitoring
CREATE VIEW vw_ChangeLogHealth AS
SELECT 
    COUNT(*) AS RecordCount,
    DATEDIFF(HOUR, MIN(ChangeDateTime), GETDATE()) AS OldestChangeHours,
    CASE 
        WHEN COUNT(*) > 10000 THEN 'WARNING: High record count'
        WHEN DATEDIFF(HOUR, MIN(ChangeDateTime), GETDATE()) > 48 THEN 'WARNING: Old changes not synced'
        ELSE 'OK'
    END AS HealthStatus
FROM ChangeLog;

-- Backup first
SELECT * INTO ChangeLog_Backup_20260303 FROM ChangeLog;

-- Clear specific date range
DELETE FROM ChangeLog 
WHERE ChangeDateTime < '2026-03-01';

local_db_url=mssql+pyodbc://user:pass@server/db?driver=ODBC+Driver+17+for+SQL+Server&Encrypt=yes&TrustServerCertificate=no

New-NetFirewallRule -DisplayName "SQL Server - shopMaster only" `
  -Direction Inbound `
  -Protocol TCP `
  -LocalPort 1433 `
  -RemoteAddress 192.168.1.100 `
  -Action Allow

-- Update statistics on product table
UPDATE STATISTICS YourProductTable WITH FULLSCAN;

-- Update statistics on ChangeLog
UPDATE STATISTICS ChangeLog WITH FULLSCAN;

-- Rebuild all indexes on product table
ALTER INDEX ALL ON YourProductTable REBUILD;

-- Or reorganize if rebuild takes too long
ALTER INDEX ALL ON YourProductTable REORGANIZE;

-- Enable statistics
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Test an update
UPDATE YourProductTable
SET SellPrice = SellPrice
WHERE ProductID = 'TEST001';

-- Check ChangeLog was updated
SELECT * FROM ChangeLog WHERE ProductID = 'TEST001';

-- Create archive table (one-time)
SELECT TOP 0 * INTO ChangeLog_Archive FROM ChangeLog;

-- Monthly: Archive records older than 7 days
INSERT INTO ChangeLog_Archive
SELECT * FROM ChangeLog
WHERE ChangeDateTime < DATEADD(DAY, -7, GETDATE());

DELETE FROM ChangeLog
WHERE ChangeDateTime < DATEADD(DAY, -7, GETDATE());

CREATE VIEW vw_SyncHealth AS
SELECT 
    (SELECT COUNT(*) FROM ChangeLog) AS PendingChanges,
    (SELECT MAX(ChangeDateTime) FROM ChangeLog) AS OldestPendingChange,
    CASE 
        WHEN (SELECT COUNT(*) FROM ChangeLog) > 1000 THEN 'CRITICAL'
        WHEN (SELECT COUNT(*) FROM ChangeLog) > 500 THEN 'WARNING'
        WHEN (SELECT MAX(ChangeDateTime) FROM ChangeLog) < DATEADD(HOUR, -2, GETDATE()) THEN 'WARNING'
        ELSE 'HEALTHY'
    END AS HealthStatus,
    CASE
        WHEN (SELECT COUNT(*) FROM ChangeLog) > 1000 THEN 'More than 1000 pending changes - sync may be failing'
        WHEN (SELECT COUNT(*) FROM ChangeLog) > 500 THEN 'High number of pending changes'
        WHEN (SELECT MAX(ChangeDateTime) FROM ChangeLog) < DATEADD(HOUR, -2, GETDATE()) THEN 'Changes older than 2 hours not synced'
        ELSE 'All systems operational'
    END AS StatusMessage;

DECLARE @HealthStatus NVARCHAR(20);
DECLARE @StatusMessage NVARCHAR(500);

SELECT @HealthStatus = HealthStatus, @StatusMessage = StatusMessage
FROM vw_SyncHealth;

IF @HealthStatus IN ('WARNING', 'CRITICAL')
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Default Mail Profile',
        @recipients = '[email protected]',
        @subject = 'shopMaster Sync Alert',
        @body = @StatusMessage;
END

CREATE TABLE SyncLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    SyncStartTime DATETIME,
    SyncEndTime DATETIME,
    RecordsSynced INT,
    SyncStatus NVARCHAR(20),
    ErrorMessage NVARCHAR(MAX),
    CreatedDate DATETIME DEFAULT GETDATE()
);

SELECT name, is_disabled FROM sys.triggers
WHERE parent_id = OBJECT_ID('YourProductTable');

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backups\YourDB_Full.bak'
WITH REPLACE;

-- Check ChangeLog exists
SELECT COUNT(*) FROM ChangeLog;

-- Check triggers exist
SELECT name FROM sys.triggers
WHERE parent_id = OBJECT_ID('YourProductTable');