Solution Internals

Welcome to the Solution Internals to learn more about the overal architecture of AI-DBA, internal features and data flow stages.

Data Movement Flow

The AI-DBA telemetry data movement flow involves several steps to collect, process, and utilize data for SQL Server instances. Here is a comprehensive explanation of the flow:

  1. Accessing SQL Server Instances:
    The targeted SQL Server instances can be accessed through either an on-premise data gateway or a serverless data gateway in the cloud. These gateways provide a secure and reliable connection to the SQL Server instances.

  2. Health Check Process:
    Once the connection is established, a health check process is initiated based on a preset schedule. This process collects around 160 SQL health factors, which include various performance metrics, usage patterns, and other relevant data points.

  3. Telemetry Data Collection:
    The collected telemetry data is then pushed to a repository database via a data endpoint. This database serves as a central storage location for all the gathered data.

  4. Machine Learning Service:
    Once the telemetry data is stored in the repository database, a machine learning service comes into play. This service analyzes the database workload and leverages machine learning algorithms to generate recommendations. These recommendations can include performance optimizations, query tuning, index suggestions, and other actionable insights.

  5. Backend Services:
    The backend services work in conjunction with the machine learning service to assist end users. These services provide documentation, email notifications, and other functionalities to help users understand and act upon the generated recommendations.

  6. Cognitive Services and Open AI:
    To enhance the intelligence and interaction capabilities, cognitive services and Open AI are utilized. These services enable intelligent email processing and allow users to have conversations with the telemetry data. Natural language processing and machine learning techniques are employed to understand and respond to user queries and requests.

  7. File Storage:
    Once the backend services generate files, such as reports or other relevant artifacts, they are stored in Azure Blob Storage. This storage solution ensures the files are easily accessible in the future through a portal or other interfaces.

In summary, the AI-DBA telemetry data movement flow starts with accessing SQL Server instances through gateways, followed by a health check process and telemetry data collection. The collected data is then stored in a repository database, where a machine learning service generates recommendations. Backend services provide additional assistance, while cognitive services and Open AI enhance the interaction with the data. Finally, files generated by the backend services are stored in Azure Blob Storage for future use.

Get all the databases in a good shape and health

The AI-DBA solution is designed to support a wide range of SQL Server versions and platforms, including:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2012
  • Microsoft SQL Server 2014
  • Microsoft SQL Server 2016
  • Microsoft SQL Server 2017
  • Microsoft SQL Server 2019
  • Microsoft SQL Server 2022

In addition to on-premise SQL Server versions, AI-DBA also supports various cloud-based SQL Server platforms, including:

  • Microsoft Azure SQL Managed Instance
  • Microsoft Azure SQL Database
  • Microsoft Azure SQL Pool

Furthermore, Ai-DBA is compatible with other cloud providers' offerings:

  • Google Cloud SQL for SQL Server
  • Amazon Web Services (AWS) RDS for SQL Server

AI-DBA is designed to support a wide range of SQL Server versions and platforms, including on-premise versions, Microsoft Azure offerings, Google Cloud SQL, and AWS RDS for SQL Server. This enables users to leverage its features and capabilities across various SQL Server environments to optimize their database performance.

Feature Overview

AI-DBA is a cutting-edge technology that leverages AI (Artificial Intelligence) and Machine Learning capabilities to revolutionize SQL Server database management. With the power of Azure SQL, Python, R, and Azure Cognitive Services, AI-DBA introduces a range of intelligent features to enhance administration, performance tuning, data security, communication, and knowledgebase functionality.

  1. AI Administration:
    AI-DBA utilizes AI and Machine Learning algorithms to provide intelligent administration capabilities across all databases. By leveraging Azure SQL, Python, R, and Azure Cognitive Services, AI-DBA automates and streamlines administrative tasks, enabling efficient management of SQL Server instances. This includes tasks such as performance monitoring, query optimization, resource allocation, and more.

  2. Intelligence Performance Tuning:
    AI-DBA excels in performance tuning by optimizing resource-intensive queries within minutes. By analyzing query execution patterns and leveraging AI algorithms, AI-DBA identifies performance bottlenecks and provides recommendations for improvement. With zero code changes required, AI-DBA can enhance performance by up to 60%, resulting in significant efficiency gains for SQL Server databases.

  3. Intelligence over Data Security:
    AI-DBA incorporates the best of machine learning and abnormal detection techniques to ensure robust data security. By continuously monitoring data access patterns and applying AI algorithms, AI-DBA can detect and alert on any suspicious or unauthorized activities. This proactive approach enhances data security and helps prevent potential breaches or unauthorized access.

  4. Intelligence Communication:
    AI-DBA introduces a groundbreaking feature that allows humans to communicate and command the system to perform tasks. This natural language processing capability enables users to interact with AI-DBA through voice or text commands, making it easier and more intuitive to manage SQL Server databases. This feature enhances user experience and streamlines administrative tasks.

  5. Extensive Knowledgebase:
    AI-DBA incorporates an extensive knowledgebase that harnesses the power of intelligent internet search, summarization, and itemization. By leveraging AI algorithms, AI-DBA can quickly retrieve relevant information, summarize it intelligently, and provide itemized details for specific topics. This knowledgebase empowers users with comprehensive insights and facilitates informed decision-making.

AI-DBA represents a new era of SQL Server database management, offering a range of intelligent features powered by AI and Machine Learning. With capabilities such as AI administration, performance tuning, data security, communication, and an extensive knowledgebase, AI-DBA transforms the way SQL Server databases are managed. By harnessing the power of Azure SQL, Python, R, and Azure Cognitive Services, AI-DBA delivers enhanced efficiency, performance, security, and user experience for SQL Server administrators and database professionals.

Preventive maintenance and proactive administration

AI-DBA's advanced features such as Intelligence Maintenance Window, Preventive Maintenance, Proactive Administration, Intelligence High Availability, and Intelligence Consolidation provide comprehensive capabilities for efficient management and optimization of SQL Server databases. These features leverage AI and historical data analysis to enhance maintenance scheduling, prevent potential issues, optimize resource utilization, recommend high availability solutions, and identify consolidation opportunities. With AI-DBA, administrators can proactively ensure the stability, performance, and availability of their SQL Server environments. 

  1. Intelligence Maintenance Window:
    The Intelligence Maintenance Window feature in AI-DBA identifies the exact peak and non-peak hours of a SQL Server database instance. By analyzing historical data and workload patterns, AI-DBA determines the optimal time for performing maintenance tasks such as backups, index rebuilds, or system updates. This ensures that maintenance activities are scheduled during non-peak hours, minimizing the impact on database performance and user experience.

  2. Preventive Maintenance:
    AI-DBA's Preventive Maintenance feature utilizes historical data analysis and prediction models to identify potential problems before they occur. By analyzing past performance metrics and trends, AI-DBA can predict upcoming issues such as disk space shortages, index fragmentation, or query performance degradation. It provides proactive recommendations, workaround instructions, and scripts to prevent these problems from escalating into catastrophic disasters. This feature helps maintain the stability and reliability of SQL Server databases.

  3. Proactive Administration:
    The Proactive Administration feature in AI-DBA analyzes statistical data and workload patterns of SQL Server databases. By monitoring resource utilization, query execution times, and other performance metrics, AI-DBA identifies areas where hardware resources can be optimized for maximum efficiency. It provides recommendations such as index optimizations, query tuning, or resource allocation adjustments to ensure optimal database performance and utilization of hardware resources. This proactive approach helps administrators stay ahead of potential performance bottlenecks and optimize the overall database workload.

  4. Intelligence High Availability:
    The Intelligence High Availability feature evaluates SQL Server instances within a farm or infrastructure to recommend the best high availability solution based on the existing workload and infrastructure. By analyzing factors such as database size, transactional volume, and failover requirements, AI-DBA evaluates options such as SQL Server Always On Availability Groups, database mirroring, or clustering. It provides recommendations on the most suitable high availability solution to ensure minimal downtime and maximum data availability for the SQL Server instances.

  5. Intelligence Consolidation:
    The Intelligence Consolidation feature evaluates the database workload and compatibility across multiple SQL Server instances to recommend consolidation strategies. By analyzing factors such as resource utilization, database dependencies, and licensing considerations, AI-DBA identifies opportunities for consolidating databases onto fewer instances. It provides recommendations on merging databases, optimizing resource allocation, and ensuring compatibility during the consolidation process. This feature helps organizations optimize their license usage and hardware resources while maintaining the required performance and availability levels.

Gain performance with no app changes with intelligence query optimization

The AI-DBA feature for intelligence performance optimization focuses on enhancing the performance of SQL Server databases without requiring any changes to the application code. This feature includes the following components:

  1. Database Structure and Index Analysis:
    AI-DBA performs a comprehensive evaluation of the table and index structures within the database. It takes into account factors such as the database workload, configuration settings, and executed queries. By analyzing these elements, AI-DBA identifies areas where improvements can be made to the database structure and indexes to optimize performance.

  2. Problematic Queries Revision:
    AI-DBA identifies long-running queries that are impacting the overall performance of the database. It then applies best practices, examines table structures, and evaluates indexes to rewrite these problematic queries. By revising the queries, AI-DBA aims to improve their efficiency and execution time, resulting in enhanced performance for the database.

  3. Expensive Query Plan Resolution:
    AI-DBA analyzes the query execution plans and identifies queries that have high execution costs. It determines whether existing indexes can be altered or new indexes can be created to reduce the cost of query execution. By optimizing the query plans, AI-DBA aims to enhance performance by reducing the time and resources required for query execution.

Overall, the AI-DBA feature for intelligence performance optimization focuses on analyzing and optimizing the database structure, indexes, and query execution plans. By identifying and addressing issues in these areas, AI-DBA can significantly enhance the performance of SQL Server databases without requiring any changes to the application code. This approach allows for improved performance and efficiency, ultimately resulting in a better user experience and optimized database operations.

Prevent data security breach – Always keep a step ahead

These security features collectively contribute to a robust security posture for database management. By leveraging artificial intelligence and machine learning capabilities, AI-DBA enhances the ability to detect and respond to potential security threats, ensures the synchronization and validation of account sessions, protects sensitive data through data masking, and strengthens the overall security of the database system. 

  1. Intelligence abnormal activity detection: 
    The abnormal activity detection feature in AI-DBA is designed to identify and detect any unusual or abnormal behavior within the database system. It utilizes machine learning algorithms to continuously learn and analyze user behavior patterns over a period of 3 months. During this learning phase, the system establishes a baseline of normal behavior for each user. Once the learning phase is complete, the system can quickly detect any deviations from the established baseline for existing users within a minute. This enables the system to identify potential security threats or unauthorized access attempts promptly. When abnormal activity is detected, appropriate alerts or notifications can be generated, allowing for immediate investigation and action to mitigate any potential risks. 

  2. Active Directory Synchronization: 
    The Active Directory synchronization feature in AI-DBA ensures that account sessions for linked SQL Server instances are synchronized and validated with the Active Directory. This synchronization process occurs every 30 seconds, ensuring that the account sessions are always up to date and aligned with the latest information in the Active Directory. By synchronizing and validating account sessions, AI-DBA helps maintain the integrity and security of user accounts and access control. Any changes or updates made in the Active Directory, such as user account modifications or access permissions, are reflected in real-time within the SQL Server instances. This helps prevent unauthorized access and ensures that user access rights are accurately reflected within the database system. 

  3. Intelligence data masking: 
    The intelligence data masking feature in AI-DBA involves the analysis and identification of table columns in a database that contain sensitive or personally identifiable information (PII). The system utilizes its knowledge base and model to determine which columns need to be masked or obfuscated to protect the data. Data masking is a technique used to replace sensitive data with realistic but fictional data, while maintaining the overall structure and integrity of the database. By masking sensitive information, AI-DBA helps safeguard the data from unauthorized access or exposure, reducing the risk of data breaches or misuse. This allows organizations to comply with data protection regulations while still being able to perform necessary analysis and processing on the data. 

  4. Intelligence security hardening: 
    The intelligence security hardening feature in AI-DBA is designed to analyze and identify security misconfigurations in both Windows and SQL Server instances. The system utilizes intelligence and its knowledge base to identify potential vulnerabilities or security gaps in the configuration settings. By analyzing various security parameters and settings, AI-DBA helps identify areas where security measures can be improved or strengthened. This includes aspects such as access controls, authentication mechanisms, encryption settings, firewall configurations, and more. By identifying and addressing security misconfigurations, AI-DBA helps minimize the risk of unauthorized access, data breaches, or other security incidents. This feature assists in ensuring that the database system is properly hardened and protected against potential security threats or attacks.

First ever system to receive command via natural language

The AI-DBA intelligence communication feature enables the system to receive and process natural language inputs from users through two channels: email and the portal interface. This feature allows users to interact with AI-DBA using conversational language, making it easier to communicate and seek information or assistance.

  1. Intelligence Email Processing:
    With the intelligence email processing capability, users can send emails to a designated email address associated with AI-DBA. The system is designed to receive and process these emails, extracting the natural language content and understanding the user's intent or query. It utilizes natural language processing algorithms to analyze the email content and extract relevant information. Once the email is processed, AI-DBA can provide a response to the user via email. The response may include answers to the user's questions, additional information, or requests for further clarification if needed. This email-based communication allows users to interact with AI-DBA in a convenient and familiar manner, just like sending an email to a human assistant.

  2. Intelligence Conversation via Portal:
    The intelligence conversation feature allows users to interact with AI-DBA through the portal interface. Users can input their queries or requests using natural language, and AI-DBA processes and understands the intent behind the input. This enables users to have dynamic and interactive conversations with the system. AI-DBA leverages live data from the internet and the collected telemetry data to provide accurate and up-to-date responses. It can access relevant information from external sources and combine it with its internal knowledge base to deliver comprehensive and contextually relevant answers to user queries.This intelligence conversation feature is currently available in Private Preview, meaning it is being tested and evaluated by a limited group of users. The aim is to gather feedback and refine the feature before making it widely available.

Overall, the intelligence communication feature in AI-DBA enhances user experience by allowing natural language interactions via email and the portal interface. It enables users to communicate their queries and requests in a conversational manner, while AI-DBA processes the inputs, retrieves relevant information, and responds accordingly.

The best of internet search, intelligence suggestion and self-healing

The AI-DBA feature of extensive knowledge base, intelligence internet search, and intelligence self-healing is designed to help with identifying and resolving errors in databases or Windows servers.

The feature utilizes an extensive knowledge base and intelligence internet search to find relevant information and workarounds for the errors raised in the database or Windows server. It evaluates the content of web pages to determine their relevancy to the error and assigns a relevancy level. Once the web page content is evaluated, the feature summarizes and itemizes the information found. It then generates action steps based on the workaround provided in the web page content. These action steps are fed to the agent service, which can automatically apply the workaround to resolve the error. The main challenge addressed by this feature is the time-consuming and tricky process of identifying the correct workaround for errors. DBAs (Database Administrators) may overlook some underlying errors and warnings, which can lead to further issues.

The solution provided by the AI-DBA feature leverages intelligence features for internet search and self-healing. This allows the system to automatically identify, search, and fix the underlying errors, providing a more efficient and effective way to resolve database and Windows server errors.

See Also

 Getting Started
 Data Gateway