SQL Server Assessment Results and Recommendations for Improvement
Gain insights into the results of a SQL Server assessment, highlighting areas of success and improvement opportunities. Discover key recommendations for enhancing security, compliance, operations, monitoring, performance, and scalability. Take necessary steps to optimize your SQL Server environment for peak efficiency and data integrity.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SQL Server Assessment Results Click here to view in Azure Log Analytics
Executive Summary 1. What went well: 87 87% % Passed Passed Upgrade, Migration and Deployment 2. What needs Improvement: Security and Compliance 3. Highest Priority Recommendations: 13 High Priority Review the build number of the SQL Server instance. 92 Low Priority 5 Resolved 664 Passed Checks
Security and Compliance Highest Priority Recommendations Configure and Enforce the Setting "Windows Firewall: Domain: Firewall state" via GPO Enable and Enforce the Setting "Turn off Autoplay" via GPO Configure and Enforce the Setting "Windows Firewall: Public: Firewall state" via GPO Mitigations missing for speculative execution side-channel vulnerabilities Avoid using domain administrator accounts to run the SQL Server service. Configure the Setting "Network security: LAN Manager authentication level" and Enforce via GPO Enable and Enforce "Microsoft network server: Digitally sign communications (if client agrees)" via GPO Avoid using the Local System account to run the SQL Server service. Review the SQL Server Instance configuration settings for CLR enabled and configure the value to match the company security policy. Ensure only essential users are added to the SQL Server instance sysadmin server role. 68 68% % Passed Passed 6 High Priority 50 Low Priority 0 Resolved 114 Passed Checks
Operations and Monitoring Highest Priority Recommendations Create a schedule to run DBCC CHECKDB regularly. Review the auto grow increment setting for databases on the SQL Server instance. Configure Auditing for Account Logon: Credential Validation Configure Auditing for Detailed Tracking: Audit PNP Activity Configure Auditing for Account Management: User Account Management Configure Auditing for Detailed Tracking: Audit Process Creation Configure Auditing for Logon-Logoff: Audit Account Lockout Configure Auditing for Object Access: Audit Detailed File Share Configure Auditing for Logon-Logoff: Audit Group Membership Configure Auditing for Logon-Logoff: Audit Other Logon/Logoff Events 77 77% % Passed Passed 1 High Priority 17 Low Priority 0 Resolved 59 Passed Checks
Performance and Scalability Highest Priority Recommendations Increase the number of tempdb database files for optimal performance. Modify auto-growth settings to use a fixed size growth increment of less than 1 GB on data and log files of user databases and the TempDB system database. Consider updating statistics by using AUTO_UPDATE_STATISTICS and/or UPDATE STATISTICS (T-SQL) Investigate the SQL Error Log: 3409: Performance counter shared memory setup failed. Increase the initial size of the tempdb database. Update the user databases to match the collation of the master and model databases. Review the SQL Server instance configuration setting for Backup Compression Default and configure the value to match your company policy. Review the system managed page file setting on the Microsoft Windows Server. Increase the amount of available physical memory investigate for excessive paging file usage or reduce the number of processes on the computer. Separate tempdb and user database files by placing them on different volumes. 90 90% % Passed Passed 3 High Priority 14 Low Priority 0 Resolved 144 Passed Checks
Upgrade, Migration and Deployment Highest Priority Recommendations Review the build number of the SQL Server instance. Install the latest service pack or hotfix for the SQL Server. Verify the compatibility of third party modules loaded into SQL Server address space in case of upgrade Review the Hyper-V Operating System Virtualization for SQL Server. One or more databases are using Enterprise edition features 88 88% % Passed Passed 1 High Priority 4 Low Priority 0 Resolved 35 Passed Checks
Availability and Business Continuity Highest Priority Recommendations Schedule a full database backup and ensure that it has a frequency of seven days or less. Change the recovery model to FULL or BULK_LOGGED. Review the SQL Server Instance configuration setting for recovery interval and change it to the default value. The current SQL Server error log is too large. Investigate SQL Error Log message 17806 SSPI handshake failed while establishing a connection with integrated security. Move databases from the system disk to a Data Disk. Review the non-default Agent XPs option setting on Microsoft SQL Server. Place data files and transaction log files on separate physical drives for user databases. Review the SQL Server instance configuration settings for transform noise words and configure the value to match your company policy. 98 98% % Passed Passed 2 High Priority 7 Low Priority 5 Resolved 312 Passed Checks
