×

Top Reporting Tools for MySQL in 2018

MMS Founder
MMS RSS

News Via RSS Feed

Featured Database Articles

Posted June 11, 2018

By Rob Gravelle

  • Previous
    Top Reporting Tools for MySQL in 2018
    Next

    Top Reporting Tools for MySQL in 2018

  • Previous
    SeekTable
    Next

    SeekTable

    SeekTable is an online ad-hoc reporting web tool that supports pivot tables and pivot charts. The company behind SeekTable.com is located in Kyiv, Ukraine. The SeekTable service is maintained by the same team that supports NReco Components. They’ve been in business long enough that you can be sure that SeekTable will not suddenly vanish. Although your data is only ever used to build visualizations and reports requested by you, a web-based service may not be the best choice for very sensitive data.

    Data can come from a variety of sources, including CSV,TSV, and Excel files, as well as a number of databases, including MySQL (Amazon Aurora), SQL Server (Azure SQL), PostgreSql (Amazon Redshift, PipelineDB), Yandex ClickHouse (binary protocol), MongoDb (Azure Cosmos DB), ElasticSearch, and just about any other DBMS that has a compatible ODBC driver.

  • Previous
    WideStage Open Source Reports
    Next

    WideStage Open Source Reports

    Here we have a lightweight self service reporting tool that’s powered by NodeJS. It uses Semantic so that your users can query data using business terms instead of SQL. It also supports SQL Server, Oracle, and Google BigQuery.

    In addition to NodeJS, WideStage Open Source Reports also requires that npm, Bower, and MongoDB be installed on your system. MongoDB is used to store the widestage metadata, so you have to install it even if your data is stored in MySQL. Once everything is installed, you can launch the application by typing “node server.js” at the command prompt. Then point your browser to your ip/server name and enter your credentials to access your reports.

  • Previous
    Quadbase
    Next

    Quadbase

    Quadbase Systems, Inc. has focused on leading edge Java technologies for Web-enabled data reporting, visualization, and dashboard presentations since 1997. Their reporting products are EspressReport and EspressReport ES.

    EspressReport is a Java report development toolkit designed for business intelligence and operational reporting. It offers a powerful, easy-to-use report writer/report engine.

    The ES in EspressReport ES stands for Enterprise Server. It provides a powerful, centralized enterprise reporting architecture that leverages the functionality of EspressReport’s pure Java reporting engine and extends it to enterprise-level functionality.

  • Previous
    PHP-Reports
    Next

    PHP-Reports

    If you’re a web developer like me, you’ll appreciate a language that lets you integrate reporting into your websites. PHP is one of my preferred programming languages, so PHP-Reports is an obvious choice for me. Developed by Murat Çileli, PHP-Reports is a cloud based, interactive report engine for generating neatly formatted PDF reports from Word (.docx) templates. The name is a bit of a misnomer, because PHP-Reports also supports ASP.NET, ASP.NET MVC, WPF, Silverlight, WinRT, HTML5, Windows Forms, Java, Python, Objective-C, Swift, Delphi and other languages as well.

    Here’s a code sample:

    $pr = new PHPReports('f9nb3k8bzfumne6g6yu6fu4d');
    $pr->setTemplateId(2);
    $pr->setOutputFileType(PHPReports::OUTPUT_PDF);
    $pr->setOutputAction(PHPReports::ACTION_GET_DOWNLOAD_URL);
    $pr->setOutputFileName('My_Generated_Report.pdf');
    $pr->setTemplateVariables(
        array(
            'client_name'   => 'Armut Inc.',
            'email_address' => 'murat.cileli@gmail.com',
            'products'      => array('Computer', 'Smart Phone', 'Book')
        ));
    $pr->generateReport();
    
  • Previous
    Datapine MySQL Reporting Tools
    Next

    Datapine MySQL Reporting Tools

    Datapine was founded in 2012 with the idea to facilitate the complex process of data analytics through technical innovation. After four years in business, they had acquired thousands of users in more than 25 countries around the globe.

    All you need to do to set up datapine is connect your SQL database(s), such as MySQL, PostgreSQL, MS SQL Server or Oracle, which takes under 10 minutes. After setup is complete, you’re ready to start carrying out your MySQL queries and generating custom reports.

    The tools include a MySQL query builder, MySQL report builder, business intelligence software, dashboard builder, and more.

    Although the service costs money, they do offer a free trial so that you can get a feel for the product.

  • Previous
    Ubiq
    Next

    Ubiq

    Ubiq is a web-based reporting tool that supports a wide variety of databases, including MySQL, PostgreSQL, Amazon RDS, Amazon Redshift, and others. Ubiq lets you quickly build interactive dashboards and reports using a drag & drop interface. No uploading of data or programming is required. Results are presented in visually stunning online dashboards and reports that may be exported in multiple formats as well as securely shared with others.

    Subscriptions are purchased on a monthly basis. Prices range from $18 per month for a single user (3 dashboards), $45 per month for three users (10 dashboards), to $75 per month for five users (15 dashboards). Custom plans may also be purchased for other group sizes. All plans are available via a free trial in order to determine which plan is best suited to your requirements.

  • Previous
    Smart Report Maker
    Next

    Smart Report Maker

    Smart Report Maker is a popular PHP report builder for MySQL by StarSoft Software, an online software development company with a focus on the open source Internet development market. It’s one of several products in their roster, which also includes Smart Form Maker, Smart Chart Maker, and Smart Pivot Table.

    Smart Report Maker allows the creation of PHP auto-update reports for any MySQL database without writing any SQL or programming code. Users can design reports visually with an easy-to-use wizard specifically designed for speed.

    A single license for Smart Report Maker is available from mysqlreports.com for $48 USD. That allows you to install Smart Report Maker in 5 domains. There is an online demo that you can try for evaluation purposes. Bundle licenses are available as well. For instance, you can purchase Smart Report Maker, Smart Chart Maker, Smart Form Maker, and Smart Pivot Table for $135 USD during a limited time sale. (Normally it’s $192)

  • Previous
    dbForge Studio for MySQL
    Next

    dbForge Studio for MySQL

    Sometimes, reporting is one component within a larger software package. Case in point, dbForge Studio for MySQL is a GUI tool for managing, developing and administrating MySQL and MariaDB databases that features a MySQL Data Reporting and Analysis Tool. It allows users to design a variety of report types with parameters, customizable formatting, calculated summaries, and more. In addition, the Master-Detail Browser allows users to view and analyze related data all together in a comprehensive way.

    It also offers a Pivot Table tool for converting large amounts of data into compact and informative summaries. You can rearrange (or pivot) your data using drag & drop until you get the best layout for understanding the data relationships and dependencies.

    dbForge Studio for MySQL comes in three editions: Standard Edition, Professional Edition, and Enterprise Edition. Prices for single licenses are $149.95, $299.95, $399.95 respectively, with discounts available for multiple licenses.

Back in 2015, I presented a slideshow of the Top 10 MySQL Reporting Tools. Since then, many readers shared their own favorite reporting tools for MySQL. Moreover, the reporting landscape has changed in the past three years. These two developments have informed me that this might just be the right time to revisit this topic. So now, for your viewing pleasure, here are another 8 of the Top Reporting Tools for MySQL.

See all articles by Rob Gravelle

Please enable Javascript in your browser, before you post the comment! Now Javascript is disabled.

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Azure SQL Database and GDPR Compliance

MMS Founder
MMS RSS

In recent years, technology landscape has undergone dramatic changes, driven primarily by cloud computing and a continuously increasing level of attention dedicated to security, privacy, and compliance. As recent revelations related to improper use of consumer data by social network providers and research firms have shown, this attention is well warranted. Similarly, surging numbers of increasingly sophisticated cyber attacks further underscore the importance of proper protection and detection mechanisms guarding individuals and organizations alike. One of the more significant initiatives that attempts to address these challenges is General Data Protection Regulation (GDPR), which is the regulation imposed by the European Union (EU) on May 25, 2018. While its primary purpose is to legislate data protection and privacy within the boundaries of EU, it applies to any business or organization (regardless of their location) that conducts business with the European Economic Area. In this article, we will explore how Azure SQL Database could help with addressing the GDPR requirements. Note however, that you should always consult with a legal professional in order to ensure full compliance.

One of the crucial aspects of GDPR regulations deals with measures that must be implemented in order to facilitate control and protection of personal data. These measures are described in the following sections of the GDPR legislation:

  • GDPR article 25 stipulates that data must be protected by design and by default. This includes implementing controls in regard to data access, data collection scope, and data retention.
  • GDPR article 30 dictates that all data processing operations must be logged and monitored.
  • GDPR article 32 references security (including pseudo-randomization and encryption), resiliency (including recoverability, high availability, and disaster recovery), and fidelity (including regular testing) mechanisms that data storage and processing involve.
  • GDPR article 35 describes the requirements regarding performing and documenting risk assessment.

Complying with these measures typically requires significant effort, which translates into additional cost and management overhead. In order to minimize this effort, it is worthwhile considering the use of technologies that incorporate mechanisms that GDPR demands. Azure SQL Database is an example of such technology. Let’s review some of its features that illustrate this point.

To start, in order to properly control and protect customer data, it is critical to be able to properly identify it. The table-based structure of Azure SQL Database yields itself to cursory examination of the data content based on column names. An in-depth analysis can be performed by relying on the Full-Text Search capability of the database engine, which has been part of the Azure SQL Database offering since April 2015.

The first line of defense when controlling access to Azure SQL Databases involves IP address and port filtering on the Azure SQL Server instance level, implemented as firewall rules defined in its master database. The firewall allows inbound traffic from designated IP addresses or address ranges on TCP port 1433 only. By default, all external connections are blocked, so you need to explicitly enable them by specifying the public IP address (or IP address range) assigned to your Internet entry point. You also have the option of controlling network-level access by using firewall rules applied to individual databases (for more information, refer to Microsoft Azure SQL Database Security – Firewall Configuration). In addition, Virtual Network Service Endpoints allow you to restrict inbound traffic to an Azure SQL Database server to specific Azure virtual network subnets that you designate. Even though the server remains associated with a public IP address, traffic from private IP addresses of Azure virtual machines on the designated subnets is routed via the Microsoft backbone network, rather than via public Internet (for more information, refer to Azure SQL Database Virtual Network Service Endpoints and Rules).

The subsequent levels of defenses are facilitated by features in other, security-related areas, such as authentication and authorization, encryption in transit and at rest, as well as data integrity and confidentiality. Azure SQL Database supports two modes of authentication. The traditional one relies on server-level logins with the corresponding user accounts in individual user databases. The recommended approach takes advantage of the newer authentication method, which integrates with Azure Active Directory (Azure AD). With Azure AD authentication, each server instance includes an Azure AD-based administrative account, which corresponds to either an Azure AD user or an Azure AD group. You can use this administrative account to create database-level users. This new approach to authentication provides a number of benefits. The ability to use the same user name and password to obtain access to multiple Azure SQL Database server and database instances eliminates the credential sprawl, automatically improving security and lowering the management overhead associated with user account management. By virtue of integration with Azure AD, you also can implement centralized management of these credentials, either in the cloud (for Azure AD managed accounts) or on-premises (for synchronized and federated accounts originating from on-premises Active Directory). For more information, regarding this topic, refer to Azure SQL Database – Azure AD Authentication

From the authorization standpoint, Azure SQL Database implements granular object model and role-based security, which facilitate implementing the principle of least privilege. This not only helps limit the risk of unintended data exposure but also minimizes the impact of a potential exploit. Data protection is further enhanced by leveraging such features as:

  • Transport Layer Security encrypts all network connections to Azure SQL Database. This is a default, enforced functionality, which provides automatic protection of data in transit.
  • Dynamic Data Masking limits the visibility of sensitive data stored in relational database tables accessible to non-privileged users. This functionality, supported starting with SQL Server 2016 and present in the current version of Azure SQL Database relies on a custom policy defined on the database level. The policy specifies one or more filters that should be applied to result sets returned in response to user or application initiated queries. This facilitates scenarios where database-resident content should not be fully exposed to database users. This is commonly required when referencing data containing Personally Identifiable Information (PII), such as credit card numbers, or email addresses (for more information, refer to Azure SQL Database – Dynamic Data Masking).
  • Row-Level Security implements a predicate-based access control. This means that its underlying mechanism relies on an arbitrary condition to evaluate whether requests to perform specific actions on individual rows of a database table should be granted or denied. These actions are determined based on the type of predicate (for more information, refer to Azure SQL Database – Row-Level Security).
  • Transparent Data Encryption encrypts content of the database, its transaction logs, as well as backups while at rest. Encryption and decryption are performed in real-time, at the individual page level, as the database content is being written to and read from storage, without necessitating changes to applications accessing their data. In the case of Azure SQL Database, its performance impact is minimized by relying on the Intel AES-NI hardware acceleration, included as an inherent part of the service (for more information, refer to Azure SQL Database – Transparent Data Encryption).
  • Always Encrypted offers end-to-end encryption of customer data by handing control over the encryption process to that customer. The encryption is implemented by a client-side driver, with encryption keys never revealed to the management layer on the Azure SQL Database side. This delivers the highest assurance of data confidentiality, without the need for changes to application code.
  • Azure SQL Database auditing tracks database-level and server-level security-related events, allowing you to identify instances of unauthorized changes and access attempts, addressing at the same time regulatory compliance requirements. Logging can be selectively turned on or off based on the event types, which include Data Access, Schema Changes, Data Changes, Security Failures, and Grant/Revoke Permissions (for more information, refer to Azure SQL Database Auditing). In order to track all data changes, you can take advantage of Temporal Tables. Their primary purpose is to provide the full visibility of historical changes of content stored in Azure SQL Database tables. Effectively, you have the ability to identify the state of your data at an arbitrary point in time. This comes in handy in a variety of scenarios, including performing analysis of usage trends of database-bound applications, identifying data lineage, or carrying compliance and regulatory audits. In addition, you have a rather unique ability of granular recovery of individual table entries, without resorting to traditional restore methods. All of these benefits can be realized without the need for programming or database design changes. Existing applications interact with their data sources in the same manner as they would without having temporal tables in place (for more information, refer to Introduction to Azure SQL Database Temporal Tables).
  • Azure SQL Database Threat Detection automatically detects and alerts on suspicious database activities, which might be a sign of a cyber attack or a security breach. This functionality relies on machine learning and behavioral analytics technologies. It integrates with Azure Security Center, which additionally provides guidance regarding threat remediation.
  • Point-In Time Restore relies on automatic backups to facilitate database restore to an arbitrary point in time (for more information, refer to Azure SQL Database Resiliency – Point-in-Time Restore).
  • Active Geo-Replication provides disaster recovery capabilities. It supports up to four read-only secondaries located in arbitrarily chosen Azure regions, which allows you to fail over to any of the four locations. In addition, since each of the replicas operates in read-only mode, you not only can mitigate the impact of datacenter-wide disasters, but you also gain the ability to facilitate online application upgrades or migrations as well as offloading data analysis or reporting tasks from your production instance. (for more information, refer to Azure SQL Database Resiliency – Business Continuity).

    This concludes our overview of Azure SQL Database features that could help you with addressing GDRP requirements.

    See all articles by Marcin Policht

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Databases with MAXSIZE Set

MMS Founder
MMS RSS

Every DBA needs to appropriately manage database growth.  No DBA wants an application to fail because the database is out of space.  A database can run out of space a number of different ways.  One of those ways is to set the MAXSIZE of a database.  When the database grows to the MAXSIZE the database will not grow anymore and if someone tries to add more rows they will get an error saying the database is full.

There may be legitimate reasons to set the MAXSIZE of a database.  But most of the time you will want your databases to always autogrow when they run out of space. If you are new to your SQL Server environment, or inherited a new database server, you should consider reviewing the databases that have their MAXSIZE limit set.  This way you can review the appropriateness of setting the MAXSIZE for any databases you identify. 

Here is some code that will find all database files that have their max_size set:

-- Database files that have maximum size limit set
SELECT db_name(database_id) DatabaseName, 
       type_desc, 
          name, 
          physical_name, 
          size,
          max_size
FROM sys.master_files 
WHERE 
-- File will grow until the disk is full
max_size <> -1 
-- Log file not set to 2TB
and (type_desc = 'LOG' and max_size <> 268435456);

See all articles by Greg Larsen

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Indexes That Have Not Been Used but Are Being Updated

MMS Founder
MMS RSS

We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources.  If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer.  Those updates run longer because they have to maintain those unused indexes.  If your application is not using an index, then there is no value in having that index.

Periodically you should review your index usage statistics to determine how your indexes are being used.  You can do that by using the sys.dm_db_index_usage_stats dynamic management view (DMV).  This view will show you if your indexes are being used in a seek, scan, or lookup operation, and whether or not the indexes have been updated.  Remember DMVs only track information since SQL Server started.  Therefore, you need to consider running this DMV after SQL Server has been up for a reasonable time in order to get an accurate picture of how your indexes have been used.

By using the sys.dm_db_index_usage_stats you can identify those indexes that have never been used.  In the following code there are two SELECT statements with a UNION clause between them.  The first SELECT statement identifies those indexes that have never been used but have been update in your databases.  The second SELECT statement identifies those indexes that have never been used and have not been updated.  By “updated” I mean the index has either been maintained due to an INSERT, UPDATE or a DELETE operation.  You should consider running this query periodically to identify those indexes that are providing your application no value but are costing you compute and disk space resources. Once you know what indexes are not being used, then you can determine whether or not you even need those indexes.

-- indexes that have been updated and not used
select SCHEMA_NAME(o.schema_id) as [schema_name], 
       OBJECT_NAME(s.object_id) table_name,
       i.name index_name, 
       s.user_seeks, 
       s.user_scans, 
       s.user_lookups,
       s.user_updates,
       'yes' Index_updated_but_not_used
from sys.dm_db_index_usage_stats s
join sys.objects o
on s.object_id = o.object_id
join sys.indexes i
on s.index_id = i.index_id
and s.object_id = i.object_id
where 
(s.user_seeks = 0
  and s.user_scans = 0
  and s.user_lookups = 0) 
and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
UNION
-- indexes that have not been updated or used 
SELECT
       SCHEMA_NAME(o.schema_id) as [schema_name], 
       OBJECT_NAME(o.object_id) table_name,
       i.name index_name, 
       0  as user_seeks, 
       0 as user_scans, 
       0 as user_lookups,
       0 as user_updates,
       'no' as Index_updated_but_not_used
FROM
sys.indexes i
JOIN
sys.objects o
on i.object_id = o.object_id
  
WHERE
  i.index_id NOT IN (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id
AND s.database_id =  DB_ID(DB_NAME()))
and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
order by Index_updated_but_not_used desc;

See all articles by Greg Larsen

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Moving from Oracle Solaris to x86

MMS Founder
MMS RSS

Oracle has decided that Solaris needs to retire, giving those who are running Oracle on Solaris roughly two years to move to another operating system. A possible choice involves commodity hardware (x86) and Linux. Since OEL is a ‘ready to use’ version of Red Hat Linux it would be a reasonable presumption that many companies will be choosing it as a Solaris replacement. Available as a full release for a bare-metal system and as a pre-configured VM it’s positioned to be a ‘go-to’ option for migration.

Solaris, on SPARC, is a big-endian operating system, where the most-significant byte is stored last. Linux, on the other hand, is a little-endian system where the bytes are stored in the order of significance (most-significant is stored first). This can make moving to Linux from Solaris a challenge, not for the software installation but for the database migration as the existing datafiles, logfiles and controlfiles can’t be used directly; they must be converted to the proper endian format. Time and space could be important issues with such conversions/migrations as there may be insufficient disk space to contain two copies of the database files, on big-endian and on little-endian. The actual conversion shouldn’t be difficult as RMAN can convert the files from a recent backup. Other considerations include access to the new servers (since they may not be on the production LAN while the build and conversions are taking place) and replicating the current Oracle environment, including scripts, NFS mounts, other shared file systems and utilities, from Solaris to Linux. RMAN isn’t the only method of migration as expdp/impdp can be used to transfer users, tables and data from the source database to its new home; using datapump means the destination database can’t be rolled forward after the import has completed, which can cause a longer outage than the enterprise may want to endure since all user access to the application data must be stopped prior to the export.

Looking at the ‘worst-case scenario’ let’s proceed with the migration using Oracle Enterprise Linux and the datapump transfer of data and tables. (Installing the software should be a fairly simple task so that portion of the migration will not be covered here.) Once the server is running and Oracle is installed the first step in this migration is to create an “empty” Oracle database as the destination. This takes care of the endian issue since the new datafiles are created with the proper endian format. If the Linux server is created with the same file system structure as the source then it’s simply a matter of performing the import and checking the results to verify nothing went awry. Any tnsnames.ora files that applications use for connectivity need to be modified to point to the replacement database server and remote connections need to be tested to verify they do, indeed, work as expected. After successful application testing has completed the database and its new ‘home’ should be ready to replace the original Solaris machines.

It’s possible that the new Linux server will use a different storage configuration; it’s also possible that the DBA team, in an effort to simplify tasks, decides to use Oracle Managed Files. In either case the import may not succeed since datapump can’t create the datafiles. This is when the tablespaces need to be created ahead of the datapump import; using the SQLFILE parameter to datapump import will place all of the DDL into the specified file. Once this file is created it can be edited to change the file locations or to use OMF when creating tablespaces. It’s critical that file sizes are sufficient to contain the data and allow for growth. Datapump will create users if the necessary tablespaces exist so the only DDL that should be necessary to run prior to the import will be the CREATE TABLESPACE statements; all other DDL should be removed prior to running the script. It’s also possible to create a DBCA template to create the destination database by modifying an existing template. Creating the database manually or with a template is a DBA team decision; in the absence of database creation scripts that can be modified it might be a good decision to modify an existing template to minimize errors.

Presuming the storage configuration for the x86 system is different from that on Solaris, and that the file system structure doesn’t match the source server, the tablespace DDL has been extracted, modified and is ready to run. After the tablespaces are in place the import can be run; it is always good practice to make a test run into the new database before the final import is executed, to verify that the process runs smoothly. The final import should put the new database in ‘proper working order’ for the applications that use it so all insert/update/delete activity must be stopped prior to the export. This will ensure data consistency across the tables.

One area that may be an issue is application account passwords in the new database. It’s a good idea to verify that the passwords from the source database work in the new database. If not, they can be reset to the current values; end-users and batch jobs will be happier if logins are successful.

Connectivity to remote systems is also critical, so any tnsnames.ora files that are in use on the source system need to be copied to the destination system and database links need to be tested. This may involve the security and system administration teams to open ports, set firewall rules and ensure that any software packages not included with the operating system are installed. There should be no surprises once the new server and database are brought online.

Other choices, such as Unified Auditing or Database Vault, that require an Oracle kernel relink need to be discussed and decided upon before the destination database is created. Re-linking the Oracle kernel before any database exists reduces overall downtime for the migration.

No document can cover every conceivable issue or problem that may arise so it’s possible the first pass at moving from Solaris to x86 may reveal issues that weren’t obvious at the beginning of this process. It may also take more than one pass to “iron out” all of the “kinks” to get a smoothly running process. The time and effort expended to ensure a smoothly running migration will pay off handsomely when the day finally arrives.

Moving from Solaris to x86 (or any other operating system) may not be at the top of your wish list, but it will become a necessity when Solaris is no longer supported. Getting a start on this now will provide the needed time to fix any issues the migration may suffer so that when the fateful weekend arrives the DBA team is ready to provide a (mostly) painless move.

See all articles by David Fitzjarrell

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Adaptive Query Processing in SQL Server

MMS Founder
MMS RSS

Introduction

Adaptive query processing is the latest, improved query processing feature introduced in the SQL Server database engine. This method is available in SQL Server (starting with SQL Server 2017 (14.x)) and Azure SQL Database. 

Query performance is always a subject of research and innovation. Every so often, Microsoft includes new feature and functionality in SQL Server to improve query performance to help their users. In SQL Server 2016, Microsoft introduced Query Store. Query Store monitors query performance and provides methods to choose better query plans for execution. Query store is a feature to persist query execution plans and analyze the history of query plans to identify the queries that can cause performance bottlenecks. Any point in time these plans can be reviewed, and the user can use plan forcing to enforce the query processor to select and use a specific query plan for execution. You can read more about Query Store in Monitor Query Performance Using Query Store in SQL Server.

In SQL Server 2017, Microsoft went one step further and worked on improving the query plan quality. This improved query plan quality is Adaptive Query Processing. 

Query Execution Plan

Prior to SQL Server 2017, query processing was a uniform process with a certain set of steps. The SQL Server query optimizer first generates a set of feasible query plans for a query or batch of T-SQL code submitted by the database engine to query optimizer. Based on the cost of the query plan, optimizer selects the lowest-cost query plan.

Once the query execution plan is ready and available to execute, SQL Server storage engines get the query plan and execute it based on the actual query plan and return the data set.

This end to end process (query plan generation, and submitting the plan for processing) once started, has to run to completion. Please see Understanding a SQL Server Query Execution Plan for more information.

In this process, query performance depends on the overall quality of the query plan, join order, operation order and selection of physical algorithm like nested loop join or hash join, etc. Also, cardinality estimates play a vital role to decide the quality of the query plan. Cardinality represents the estimated number of rows returned on execution of a query.

The accuracy of cardinality drives a better quality of query execution plan and more accurate allocation of memory, CPU to the query. This improvement leads to a better and improved execution of query, known as adaptive query processing. We will discuss exactly how SQL Server achieves this in rest of the section,.

Adaptive Query Processing

Prior to SQL Server 2017, the behavior of Query Optimizer was not optimal; it bound to select the lowest cost query plan for execution, despite poor initial assumptions in estimating inaccurate cardinality, which led to bad query performance.

In Adaptive Query Processing, SQL Server optimized the query optimizer to generate more accurate and quality query plans with more accurate cardinality. Adapting Query Processing gets a better-informed query plan. There are three new techniques for adapting to application workload characteristics.

Adapting Query Processing Techniques

  1. Batch Mode Memory Grant Feedback. This technique helps in allocating the required memory to fit in all returning rows. The right allocation of memory helps in, 1) reducing excessive memory grants that avoid concurrency issues and, 2) fixing underestimated memory grants that avoid expensive spills to disk.

    Query performance suffers when memory allocation sizes are not sized correctly. In Batch Mode Memory Grant Feedback, optimizer repeats the workload and recalculates the actual memory required for a query and then updates the grant value for the cached plan.  When an identical query statement is executed, the query uses the revised memory grant size and improves the query performance.

  2. Batch Mode Adaptive Join. This technique provides the choice of algorithm selection and the query plan dynamically switches to a better join strategy during plan execution.  This defers either hash join or nested loop join selection until after first input has been scanned.

    The new operator in the family, Adaptive Join operator helps in defining a threshold. This threshold is used to compare with the row count of the build join input; if the row count of the build join input is less than the threshold, a nested loop join would be better than a hash join and query plan switches to nested loop join. Otherwise, without any switches query plan continues with a hash join.

    This technique mainly helps the workload with frequent fluctuations between small and large join input scans.

  3. Interleaved Execution. Interleaved execution is applicable to multi-statement table valued functions (MSTVFs) in SQL Server 2017 because MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and an estimate of “1” for earlier versions, which leads to passing through inaccurate cardinality estimates to the query plan. In Interleaved execution technique, whenever SQL Server identifies MSTVFs during the optimization process, it pauses the optimization, executes the applicable subtree first, gets accurate cardinality estimates and then resumes optimization for rest of the operations. This technique helps in getting actual row counts from MSTVFs instead of fixed cardinality estimates to make plan optimizations downstream from MSTFs references; this results in improvement in workload performance. Also, Interleaved execution enables plans to adapt based on the revised cardinality estimates as MSTVFs changes the unidirectional boundary of single-query execution between the optimization and execution phase.

Additional facts for Adaptive Query Processing Techniques

There are certain facts associated with each Adaptive Query Processing technique while optimizer improves the query performance using these techniques.

Batch Mode Memory Grant Feedback. Feedback is not persisted once a plan is evicted from cache. You will not be able to get the history of the query plan in Query Store, as Memory Grant Feedback will change only the cached plan and changes are currently not captured in the Query Store. Also, in case of failover, feedback will be lost.

Batch Mode Adaptive Join. SQL Server can dynamically switch between the two types of joins and always choose between nested loop and hash join; merge join is not currently part of the adaptive join. Also, adaptive joins introduce a higher memory requirement than an indexed nested loop Join equivalent plan.

Interleaved Execution. There are some restrictions; 1) the MSTVFs must be read-only, 2) the MSTVFs cannot be used inside of a CROSS APPLY operation, 3) the MSTVFs are not eligible for interleaved execution if they do not use runtime constants.

Configure Adapting Query Processing

There are multiple options to configure Adaptive Query Processing. These options are as follows:

Option-1: SQL Server by default enables Adaptive Query Processing and executes the queries under compatibility level 140.

Option-2: T-SQL is available to enable and disable all three Adaptive Query Processing techniques:

T-SQL to enable Adaptive Query Processing techniques

  1. ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
    
  2. ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;
    
  3. ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;
    

       T-SQL to disable Adaptive Query Processing techniques

  1. ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF; 
    
  2. ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF; 
    
  3. ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF; 
    

Option-3:Enforce Adaptive Query Processing technique using query hint. Query hint takes precedence over a database scoped configuration and other settings.

  1. OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));
    
  2. OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));
    
  3. OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));
    

Summary

The adaptive query processing improves the quality of a query plan. This helps in selecting the right join, right order of operation and more accurate memory allocation to fit all rows. The three techniques of adaptive query processing make SQL Server 2017 significantly faster at processing the workload. Also, adaptive query processing provides significant improvements without refactoring T-SQL code.

Overall, adaptive query processing is a great addition in SQL Server and enables SQL Server to generate well informed quality query plans to manage application workload processing.

See all articles by Anoop Kumar

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Oracle's Conventional Export (exp) May Cause Problems

MMS Founder
MMS RSS

Sometimes it’s desired to move data from production to test or development environments, and if done with the original exp/imp utilities, issues can arise since these were written for database versions older than 9.x. Those utilities don’t support features found in newer database versions, which can create performance problems.

Tables with Top-n or hybrid histograms, when exported with exp, won’t get those histograms replicated to the destination database; both Top-n and hybrid histograms will be converted to Frequency histograms. Looking at a table in 12.1.0.2 (from an example by Jonathan Lewis) let’s see what histograms are present:


COLUMN_NAME              Distinct HISTOGRAM          Buckets
-------------------- ------------ --------------- ----------
FREQUENCY                     100 FREQUENCY              100
TOP_N                         100 TOP-FREQUENCY           95
HYBRID                        100 HYBRID                  50


Using legacy exp the table is exported. Importing this into another 12.1.0.2 database using legacy imp the histogram types have changed:


COLUMN_NAME              Distinct HISTOGRAM          Buckets
-------------------- ------------ --------------- ----------
FREQUENCY                     100 FREQUENCY              100
TOP_N                         100 FREQUENCY               95
HYBRID                        100 FREQUENCY               50


Note that the Oracle release is the same in both databases; it’s the exp/imp utilities creating the problem. Using datapump to transfer the data would have preserved the histograms. If there are scripts in use that use these old utilities it’s probably time to rewrite them to take advantage of datapump export and import.

It’s sometimes easier to use what’s already written but in the case of conventional export and import it’s time to retire these scripts when using Oracle releases that support datapump.

See all articles by David Fitzjarrell

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Monitor Your MySQL, MariaDB and Percona Servers with Navicat Monitor

MMS Founder
MMS RSS

Configuring Navicat Monitor on Windows 10

One of the hottest trends in database administration is agentless monitoring. Tools that employ an agentless architecture collect performance metrics from your database server(s) without requiring the installation of any agent software on the server(s). From the perspective of database administrators (DBAs), the agentless approach is a lot easier to manage than agent-based monitoring because you don’t need to deploy agent software on every server and can monitor your server(s) remotely. The latest player to enter the database monitoring arena is Navicat, whose line of database administration tools for MySQL, MariaDB, SQL Server, SQLite, Oracle and PostgreSQL are widely regarded as industry leaders. Today’s article will provide one of the first looks at this promising new tool by exploring some of its many features as well as how to configure it, with an emphasis on Windows 10.

Requirements

Navicat Monitor monitors your MySQL, MariaDB and Percona servers, and collects metrics at regular intervals using an agentless architecture. It’s also compatible with a variety of cloud databases like Amazon RDS, Amazon Aurora, Oracle Cloud, Google Cloud, Microsoft Azure and Alibaba Cloud. The Monitoring software is installed on a local computer, server, or virtual machine and then collects a number of process metrics such as CPU load, RAM usage, and a variety of other resources over SSH/SNMP.

Navicat Monitor employs a dedicated database to store alerts and metrics for historical analyses. The repository database can be any existing MySQL, MariaDB, PostgreSQL, or Amazon RDS instance.

Supported Operating Systems (OSes)

Navicat Monitor will run on much the same OSes as their other products. As such, there are installation packages available for Windows, macOS, and Linux. Here are all of the specific supported OS editions:

  • Microsoft Windows:
    1. Windows 8
    2. Windows 8.1
    3. Windows 10
    4. Server 2008
    5. Server 2012
    6. Server 2016
  • macOS:
    1. Mac OS X 10.10 Yosemite
    2. Mac OS X 10.11 El Capitan
    3. macOS 10.12 Sierra
    4. macOS 10.13 High Sierra
  • Linux:
    1. Red Hat Enterprise Linux 6.6 or later
    2. CentOS 6.6 or later
    3. Oracle Linux 6.6 or later
    4. Fedora 20 or later
    5. Debian 8.0 or later
    6. SuSE Linux Enterprise 12 or later
    7. openSUSE 42.3
    8. Ubuntu 14.04 LTS
    9. 16.04 LTS or 17.10
    10. Amazon Linux 2

Supported Browsers

Navicat Monitor is a server-based software, which can be accessed from anywhere via a web browser. With Web access, you can easily and seamlessly keep track of your servers from anywhere in the world, around the clock.

You should consider using one of the following browsers before any other. Of course, other browsers will probably work as well, but these have been tested and are recommended by Navicat:

  1. Firefox (Latest Version)
  2. Chrome (Latest Version)
  3. Internet Explorer 11 or later
  4. Microsoft Edge 39 or later
  5. Safari 9.1.3 or later

Features at a Glance

We’ve already established that Navicat Monitor employs an agentless architecture and can be installed on any workstation/device OR server running one of the main three (Windows, macOS, Linux) OSes. There are two ways to configure the Navicat software:

  1. You can install the Navicat Monitor software directly on your workstation/device and then access it locally via your preferred browser.
  2. You can also install the Navicat software on a server, which would allow you to access the monitor tools from any device that can communicate with your server.

Now let’s take a bird’s eye view of Navicat Monitor’s other pertinent features. Some are integral to any professional monitoring tool, while others go above and beyond, offering additional value in one complete package.

  1. Real-time Performance Monitoring

    Real-time data monitoring (RTDM) is a process through which an administrator can review and evaluate the overall database processes and functions performed on the data in real time, i.e., as it happens, through graphical charts on a central interface/dashboard. Navicat Monitor includes both a set of real-time and historical graphs that allow DBAs to drill down into the details of server statistics. It provides a fine-grained view of server load and performance metrics relating to availability, disk usage, network I/O, table locks and more. Moreover, by showing side-by-side statistics for each server, DBAs can easily spot and track deviations in performance and traffic among servers. Using the metrics provided by Navicat’s interactive dashboard, DBAs can determine possible remedies and adjust server settings accordingly.

    Real-time Performance Monitoring
    Real-time Performance Monitoring

  2. Alerts

    Even the most dedicated DBA can’t be watching server performance 24/7. Even if it was feasible, that wouldn’t be a an enviable existence! That’s where database alerts come in. An alert is raised when a threshold value for a System Monitor counter has been reached. For example, you could create an alert that is raised when the number of deadlocks exceeds a specific value. In response to the alert, Navicat Monitor’s built-in Alert feature notifies you before bigger problems arise to ensure your databases are constantly available and performing in optimal capacity. Notifications can be sent via email, SMS or SNMP whenever a warning or critical condition occurs in your infrastructure.

    After an alert has been raised, Navicat Monitor provides advanced root cause analysis that enables you to drill down and find more in-depth information when an issue is found – server metrics, performance statistics, hardware usage, and historical data. You can also monitor your alerts in the Alert Details screen, which provides an overview of the selected alerts that comprises its summary, timeline, metric charts, and more.

    Navicat Monitor Alerts
    Navicat Monitor Alerts

  3. Query Analyzer

    The query analyzer monitors queries in real time to quickly improve the performance and efficiency of your server. It shows the summary information of all executing queries and lets you easily uncover problematic queries, for instance:

    • identifying top queries with cumulative execution time count
    • slow queries with unacceptable response time
    • detecting deadlocks when two or more queries permanently block each other

    Query Analyzer
    Query Analyzer

  4. Reporting

    In addition to collect metrics of multiple instances on the same chart to compare and analyze data, Navicat Monitor can also generate professional reports at lightning speed. You can also save your report as a high-quality PDF document via the PDF button.

    Reporting
    Reporting

  5. Replication

    Monitoring a replication topology is an important aspect of deploying replication. Because replication activity is distributed, it is essential to track activity and status across all computers involved in replication.

    Using Navicat Monitor, you can apply schematic diagrams to visually represent the relationship between master servers and slaves. You can monitor the overall health of a replication topology as a whole, each individual node, and each replication subsystem to make sure the data on the replicated servers is always up to date.

    Monitored Replications
    Monitored Replications

  6. Manage Users and Roles

    Navicat Monitor allows the creation of local users, or external users using LDAP or AD authentication, along with their access rights and restrictions by assigning roles to users. You can customize the pre-defined roles to best fit your needs or create new roles with customized privileges settings to restrict access to selected pages in Navicat Monitor.

    Manage Users and Roles

    Manage Users and Roles

  7. Instances Management

    Organize your servers into groups to make it easier for users to find them by category. Assign users to specific groups as members and apply the same alert configuration settings to them with minimal effort. All members will get notified when an alert is raised.

    Instances Management
    Instances Management

  8. Export Monitor Settings

    Exporting Navicat Monitor settings can be useful if you want to migrate an application server or for backup purposes. The exported zip file includes Token Keys, Instance Settings, Repository Database Settings and all the files necessary for the restoration.

    export_monitor_settings (69K)
    Export Monitor Settings

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

5 More Features of IBM Db2 12 for z/OS

MMS Founder
MMS RSS

Zero-latency HTAP

As big data applications matured one thorny performance issue remained: the data in the appliance was huge, and required time-consuming data load processes. For some customers it became like a large data warehouse, providing analytics against day-old data. Other customers attempted to load the appliance as soon as operational data was available. Still, there was a time delay while loading applications executed. Also, at times several loads were required to several tables to maintain data consistency. For example, one couldn’t load new orders into the Order table until the corresponding customers were loaded into the Customer table.

The latest versions of IBM’s Db2 version 12 and IDAA can now be configured to allow concurrent transactional and analytical processing. IBM’s new patented concurrent replication protocol is a “just-in-time” method of propagating operational Db2 table changes to the IDAA. The result is that transaction data in the operational application systems is up-to-date for business analytics requests executed against data in the appliance.

The result is a hybrid of both transactional and analytical processing.

With these new features (and more) of their flagship relational database management system, IBM has redefined the infrastructure of enterprise information technology to better align with digital business, cloud computing and mobile applications. New access path management methods such as dynamic SQL plan stability allow for more efficient capacity planning while reducing the risks of rogue SQL statements running for extended periods while locking data and consuming large amounts of CPU time. Finally, synergy with advances in IBM z14 hardware such as taking advantage of the hardware acceleration of cryptographic processing and data compression means that Db2 on IBM Z will continue to provide industry-leading scalability, availability and performance.

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.

Introduction to Azure Cosmos DB Security

MMS Founder
MMS RSS

Deployment of cloud-based technologies introduces a wide range of challenges; however, few of them are scrutinized to the same extent as security. When analyzing security-related challenges, it is important to note that they encompass several distinct but interrelated technologies, including authentication and authorization, network isolation, as well as data integrity and confidentiality. In addition, when dealing with data services, it is also necessary to distinguish between the data plane, facilitating access to the underlying content and the management plane, which allows for delegation of administrative tasks. In this article, we will explore how these concepts apply to the Azure Cosmos DB offering.

The first line of defense when controlling data plane access to Cosmos DB is implemented by leveraging the IP address-based network filtering at the account level. By default, this filtering is disabled, effectively allowing network connectivity from any location. Once you enable it, all inbound traffic is blocked unless you explicitly permit it. When using the Azure portal, there are four configuration options that represent different connection types:

  • Add my current IP – this option provides a convenient method to allow connectivity from the public IP address representing your computer. This can be a computer residing in any location with Internet connectivity, including on-premises datacenters and Azure virtual networks.
  • IP (SINGLE IPV4 OR CIDR RANGE) – this option allows you to specify any Internet-based IP address or an IP address range in the Classless Inter-Domain Routing notation.
  • Exceptions (Allow access to Azure Services) – this is a checkbox (enabled by default) that controls connectivity from Azure PaaS services, such as Azure Stream Analytics, Azure Functions, and Azure App Service that are capable of interacting directly with Cosmos DB.
  • Exceptions (Allow access to Azure portal) – this is a checkbox (enabled by default) that controls connectivity to Cosmos DB directly from the Azure portal via such portal-based data management interfaces as Data Explorer.

If you decide to manage a firewall programmatically (for example, by using Azure PowerShell, Azure CLI, or REST API), then you can accomplish this by modifying the ipRangeFilter property of the object representing a target Cosmos DB account. In such case, you will need to provide specific IP addresses or IP address ranges:

  • Exceptions (Allow access to Azure Services) is represented by the IP address 0.0.0.0.
  • Exceptions (Allow access to Azure portal) – is represented by the IP addresses 104.42.195.92,40.76.54.131,52.176.6.30,52.169.50.45,52.187.184.26 for Azure public (the portals for US Gov, as well as Germany and China national clouds are represented by their respective, unique IP addresses).

Following establishing a successful network connection, incoming requests are subject to authentication and authorization. To implement them, Cosmos DB relies on several complementing security mechanisms. The first of them involves the use of account-specific, autogenerated master keys. Each account includes two master keys, which provide full control of its entire content. In addition, each account also includes a pair of read-only keys, which grant the ability to carry out read-only operations (with the exception of reading permissions of account resources). Having two master (and two read-only) keys allows you to regenerate each key independently of the other one in the same pair, facilitating key rotation while at the same time, providing uninterrupted access to users and applications.

The use of master keys should be limited to scenarios that require full privileges to the content of an account. For more granular access, you should use resource tokens. Resource tokens provide user-based permissions to individual account resources, including collections, documents, attachments, stored procedures, triggers, and user-defined functions. They are auto-generated when a database user is granted permissions to a resource and re-generated in response to a request referencing that permission. By default, they are valid for one hour, with the maximum timespan of five hours.

Resource tokens are typically employed in scenarios that implement the valet key pattern (for details, refer to its architectural overview on Microsoft Docs). This pattern involves a middle-tier service that serves as the authentication and authorization broker between a client and a back-end service (which in our case, corresponds to the target Cosmos DB account). The mid-tier service has full access to the account based on the knowledge of one of the two master keys. The mid-tier service is also responsible for authenticating the client (for example, by using Azure Active Directory). Once the client successfully authenticates, the mid-tier service requests a resource token associated with the authenticated user from the Cosmos DB account and relays the token back to the client. At that point, the client can use the token to access Cosmos DB resources directly. This continues until the token expires, resulting in the 401 exception. In response, the client contacts the middle-tier service again, which in turn initiates the request for a new resource token.

The control plane security protection of Cosmos DB leverages Role Based Access Control (RBAC), which is part of the Azure core platform functionality. RBAC involves the use of pre-defined and custom roles that determine the list of actions that a role holder is allowed to carry out. Implementing it requires designating an Azure Active Directory user, group, or service principal that will become the role holder and specifying the scope (a subscription, resource group, or an individual resource, such as a Cosmos DB account) in which the role assignment will take effect. Cosmos DB-specific built-in RBAC roles include only DocumentDB Account Contributor and Cosmos DB Account Reader, but you have the option of creating custom roles if needed (for details regarding this process, refer to Microsoft Docs).

This concludes our introduction to Cosmos DB security. In upcoming articles published on this forum, we will explore user and permission management in more detail.

See all articles by Marcin Policht

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.