SQL Server Audit Services

TotalSoft offers to the companies of IT services adapted to their needs, regardless of their field of activity. It refers to checking the SQL Server instance in order to identify any hardware issues of the physics or virtual machine that it is installed on, incorrect configurations and settings, database architecture issues, blocked processes or with poor performance.

TotalSoft offers three audit packages, depending on the complexity of the client's requirement:

1. DMV

 
The package involves the query "Dynamic Management System Views" made available by MS SQL Server and analyzing the returned results. Instead, "Dynamic Management System Views" includes information about the latest restart of SQL Server, the analysis period should be longer. It will analyze the machine where the instance is installed, the SQL instance settings and a random database.

Following the analysis, will result in a report that includes scripts in order to solve the problems mentioned below:
In addition, it will be transmitted also a list of costly processes, based on server-specific functions.

2. Baseline

 
Includes the DMV package to which it is added new services, detailed below.
 
The analysis is also done through “System Dynamic Management Views”, the difference from DMV being that the results will be saved regardless of the server restart time. The system views are queried at an interval of 15 minutes, and the results will be saved starting from the last running. Thus, you can observe the evolution over time of a configuration.
 
For the most accurate analysis, we recommend that the data collection period should be at least one week, and the machine being analyzed should be the one on which the instance is installed, the SQL instance settings and all the databases on that instance.
 
Following the analysis, will result in a report that includes, in addition to the data in the DMV report, the optimization of a database. Top 10 optimizations can be determined which can be done quickly in case of inefficient procedures such as running time / logical reads / CPU consumption (without affecting the business flows or rewriting a significant part of their code).

3. SQL DR  - SQL Server Distributed Replay

 
Includes DMV and Baseline packages to which it is added testing corrections or recommendations made on an image of the production.
 
Thus, depending on the time when the blocking / slow processes were detected, a backup of the database is started and in parallel, a workload of the production base is captured (duration - minimum 4h, recommended 8h, to have an image representative of what happens in the production activity). The workload will be in the form of a side trace server.
 
Once the list of fixtures is complete, the following activities will be carried out:

• the production backup is restored (on a medium similar to the production one);
• the captured trace is reapplied to obtain times/values ​​on the new environment;
• the corrections are run;
• the trace captured on the base is reapplied to obtain times/values ​​on the corrected environment;
• the two results are compared;
• the backup is restored on a recommended environment following the analyzes from the previous options;*
• the corrections are executed and the trace applied to the recommended environment;*
• run the corrections and reapply the trace on the recommended environment;*
• compares with the previous two results.*

 
(*can be provided only if the server does not comply with the current recommendations of hardware specifications for installing SQL Server)
 
Following the analysis, will result in a report that includes, in addition to the data in the DMV and Baseline also the specifications mentioned below:

• Hardware upgrade simulation (where applicable);
• Optimizing the top three costly procedures, without affecting business flows;
• Graphical analysis report “Before vs. After".
• DEA analysis base