SQL Operations Studio Dashboards

SQL Operations Studio is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server databases.

I have a great experience running this tool on Linux CentOS and you can build server and database management dashboards  like below.dashboard02dashboard03

To create custom dashboards check out my tip https://www.mssqltips.com/sqlservertip/5434/customize-sql-operations-studio-dashboards/

 

What is Stretch Database?

Stretch Database is a feature of SQL Server where data can be split between on-premises storage and cloud storage. With Stretch Database, cold, historical data is kept in the cloud and active data is kept on-premises for maximum performance.

Stretch Database requires no changes to client applications or existing Transact-SQL queries, so you can implement it seamlessly for existing applications. Stretch Database can reduce on-premises storage requirements both for data and associated backups. Backups of on-premises data are smaller and therefore run quicker than standard backups. Data in the cloud is backed up automatically.

With Stretch Database, cold historic data remains available for users to query, although there might be a small amount of additional latency associated with queries.

img2

Implement a Stretch Database
You can implement Stretch Database entirely within SQL Server Management Studio; you do not need to pre-configure servers or storage within Microsoft Azure.
Implementing Stretch Database involves the following steps:

  1. Start Microsoft SQL Server Management Studio and connect to the instance of SQL Server.
  2. In Object Explorer, expand Databases.
  3. Right-click the database, point to Tasks, point to Stretch, and then click Enable.

scretch

scretch2

scretch3scretch4scretch5Complete the steps in the Enable Database for Stretch wizard to create a  Database Master Key; identify the appropriate tables and configure the Microsoft Azure deployment.

After implementing Stretch Database, you can monitor it from SQL Server  Management Studio.

In Object Explorer, expand Databases, right-click the stretch-enabled database, point to Tasks, point to Stretch, and then click Monitor to open the Stretch Database Monitor. This monitor shows information about both the local and Azure SQL instances, along with data migration status.

Query Store Usage Scenarios

What is Query Store?

Query Store is a tool to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis.

query-store-process-3

So, if query store stores my query information, what kind of scenarios I could use this information?

  1. To fix queries with plan choice regressions is one example where to use it. Query Optimizer may decide to choose a different plan and without Query Store would be difficult to identify and fix it.
  2.  Tune top resource consuming queries is much more easier with Query Store because this tool will show the plan is using in a query that has high reads or duration for example.
  3. To compare workload performance before and after the application change or adding new hardware, create indexes or the next scenario SQL Server updates.
  4. To keep performance stability during the upgrade to newer SQL Serverquery-store-usage-5
  5. To improve ad hoc workloads. Run the Transact-SQL script bellow to get total number of query texts, queries, and plans in the system and determine how different they are by comparing their query_hash and plan_hash

/*Do cardinality analysis when suspect on ad hoc workloads*/
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query;
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;
SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan; 

 

SQL Operations Studio Linux

Great news, a visual way to manage SQL Server from Linux.

SQL Operations Studio came to fill a gap we were waiting to develop and manage SQL Server databases on Linux. In the link https://github.com/Microsoft/sqlopsstudio we can see more information like the features below.

Feature Highlights

  • Cross-Platform DB management for Windows, macOS and Linux with simple XCopy deployment
  • SQL Server Connection Management with Connection Dialog, Server Groups, and Registered Servers
  • Object Explorer supporting schema browsing and contextual command execution
  • T-SQL Query Editor with advanced coding features such as autosuggestions, error diagnostics, tooltips, formatting and peek definition
  • Query Results Viewer with advanced data grid supporting large result sets, export to JSON\CSV\Excel, query plan and charting
  • Management Dashboard supporting customizable widgets with drill-through actionable insights
  • Visual Data Editor that enables direct row insertion, update and deletion into tables
  • Backup and Restore dialogs that enables advanced customization and remote filesystem browsing, configured tasks can be executed or scripted
  • Task History window to view current task execution status, completion results with error messages and task T-SQL scripting
  • Scripting support to generate CREATE, SELECT and DROP statements for database objects
  • Workspaces with full Git integration and Find In Files support to managing T-SQL script libraries
  • Modern light-weight shell with theming, user settings, full screen support, integrated terminal and numerous other features

Let’s see how it works:

The process is very simple. First step download de file in this link https://go.microsoft.com/fwlink/?linkid=862646

After download the next step is to extract the files in a folder wherever you want and run the sqlops file.

My first impression is quite good. A good visual tool to work with SQL Server on Linux. I like the execution plan view and how to manage the databases.

sqlops1

 

This slideshow requires JavaScript.

Comparing with SQL Server Management Studio I miss some options such as:

  • LinkedServer Objects
  • Maintenance Plans
  • Replication
  • Database Mail
  • Extended Events

The conclusion is I really can work on that and run all my scripts and create objects like linkedservers, extended events or replications. That helps a lot to try a new platform concepts.

 

SQL Examiner Tool

I like tools and I’ve used a couple of them. I had the opportunity to try SQL Examiner to compare database structures and even though compare data. The tool is quite simple to configure the options.

Talking about comparison, well we compare one database with another. Imagine a situation, there are two environments (development and test) and we must deploy the changes from one to another.

Let’s see step by step how it works

The steps are quite simple, need to select source and target type (Microsoft SQL Server, Oracle, MySQL, PostgreSQL, SQL Azure). After that, define the servers name, the authentication mode and the database. Below I’m showing how to connect in 2 local instances I have on my local computer.

img2

After that, the SQL Examiner will compare what differences exist between databases.Now, I’d say I was expecting my objects differences and I got a great feature. In the image below the SQL Examiner is comparing the database options as well.

This is fantastic, the tool is showing different compatibility levels and it can be very helpful if you are doing performance tests, for example, the behavior is very different in this case. Also, I can compare my production environment and keep the database configurations equal in all environments.

img4

Great tool, simple and efficient.