Default and Custom Widgets in SQL Server Operations Studio

There are already a number of articles and blog posts that reveal the benefits of the SQL Operations Studio tool. The demand created across the software and the related tools are relatively unimaginable than ever before. The trend and growth will continue to increase in the upcoming days.

The graphical representation is in the vogue today. Visualizing data helps us to better understand this and to make decisions. It’s no wonder that data visualization continues to attract a growing number of users. The development of any toolset could provide opportunities to speed up the software development lifecycle process.

This article demonstrates the advantages of using custom SQL queries or complex T-SQL to provide a great insight into the database and explains how one can use this insight to build custom widgets. In this case, the SQL Operations Studio Widget is a customized piece of the code to personalize the SQL Server Dashboard for effective management of SQL instances.

The more time you spend working with the tool, the more you realize that this intelligent light-weight cross-platform application can create interesting things.

In this article, you will learn the following:

  • Introduction to SQL Operations Studio
  • How to run a custom SQL query and view it as a chart
  • How to use default and custom widgets
  • Various panes and options in SSOS
  • Explain the different chart options
  • Create a custom insight
  • Details to define a custom widget step by step
  • And more…

Custom Widgets in SQL Ops Studio

Wrapping Up

In this article, we discussed how to configure a various default database and server widget insights. Also, the article highlights the steps to add a custom SQL as a part of the dashboard. We saw how simple it is to define and customize the dashboard. While SSMS is still the de-facto tool compatible with SQL Server instances, it’s more of an integrated environment. When you just want to play with SQL or create light widgets, SQL Operations Studio has several advantages over the SQL Server Management Studio.

As it’s available on Linux and easy to be integrated with PowerShell, it’s becoming a promising SQL toolset for many developers and administrators alike.

What do you think? Give it a download if you haven’t, and feel free to comment below.

 

Advertisements
Posted in SQL Ops Studio, Uncategorized | Tagged , , | Leave a comment

SQL Server DevOps Tools

This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations.

In this article, I will explore various SQL tools. I will be discussing in detail about each of the following tools in the upcoming articles:

  1. Sqlcmd
  2. Bcp
  3. SqlPackage
  4. MSSQL-Scripter
  5. SQL Operations Studio

For the first installment of this new series, I will give you an overview of DevOps and highlight how SQL Server tools help.

The recent trends under the umbrella of DevOps (Development and Operations) led to the birth of various new techniques to fulfill the complexities of today’s needs through Continuous Integration (CI) and Continuous Deployment (CD) over a wide range of diverse enterprise applications. DevOps is a combination of two teams — development as well as operations — and it’s a whole new value stream for delivery. There is a direct correlation between automation of software delivery processes and success with customers.

We’ll introduce ourselves to these tools in this article, and dive deeper into each of them in upcoming articles.

Further reading

SQL Server DevOps Tools

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Happy Learning!!

 

 

Posted in SQL Server DevOps | Tagged , , , , | Leave a comment

SQL Server Data Management Life Cycle

This is the second article in the “Backup and Restore (or Recovery) in SQL Server” stairway series (see the full TOC below). This article deals with the different phases of data management life cycle and it encompasses the following topics:

  1. Introduction to data corruption
  2. Defining data corruption and its causes
  3. Discussion on the impact of data corruption
  4. Explaining data prevention mechanisms
  5. Data protection
  6. And more…

Introduction to Data Corruption

Data is said to be corrupted when it attains an undesirable state different from the original data. Data corruption also refers to data errors that can occur during any of the stages of data writing, data processing or even reading, at the storage, or during transmission.

We all know that at the very core, data is nothing but a series of 1s and 0s. When data is written, what actually happens is that the 1s and 0s are written to the destination. This sequence of 1s and 0s is interpreted during the data reading operation, giving it some meaning. If these bits don’t get written in the intended sequence, the data is said to have been corrupted.

Small data means smaller sequences and relatively lower chances of corruption. When the amount of data increases, the chances of corruption also increases. The chances are particularly high when data generation is high, and the subsequent storage operations hit unprecedented rates.

Some of the most common reasons for data corruption include hardware or software issues with regard to I/O subsystems.

Causes of Data Corruption

  • Hardware issues or failures
  • Memory issues
  • Power failure or outages
  • I/O subsystem
    • SAN controllers
    • RAID controllers
    • Disk drivers
    • Bad sectors on the disks
  • Operating System errors
  • Virus attacks
  • Antivirus, defraggers, even data encryption,
  • SQL Server bugs
  • Human errors
  • Improper shutdown
  • Hard reset

Further reading SQL Server Data Management Life Cycle

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

 

 

 

Posted in SQL | Tagged , , , , , , | Leave a comment

Experiment with SQL Server and DBFS on Linux

Given that Microsoft now considers Linux a ‘first-class citizen’, SQL Server 2017 offers native support for Linux. It is engineered to be cross-platform, and that’s good news for Linux administrators and open source developers. Microsoft’s support for Linux (and open source in general), has come into clearer focus, and the company’s mission now seems to be all about bringing its tools to wherever its users are.

Microsoft has been known for proprietary software, and this openness to the open source community came as a surprise; the development is quite recent, and one in the right direction. SQL Server has had a myriad of GUI and CLI tools. These tools have also, along with SQL Server itself, have become cross-platform. These tools are almost equally as good on Linux as Windows, and they help effectively manage, develop, and administer SQL Server. Some of the tools are:

  • SSMS
  • Sqlcmd
  • Bcp
  • Sqlpackage
  • Mssql-conf
  • Mssql-scripter
  • DMV tool
  • SQL Operations Studio
  • MSSQL-Cli
  • SQL Extension for VS code

At first, it seems like a myriad of them. Taking one piece at a time helps with understanding the tools. Let us try to classify these tools based on what they are useful for:

Further reading

SQL Server and DBFS

Highlights

  • Support for Custom Query execution
  • Data can be viewed as JSON
  • Bash supports utilities such as cut, grep, sed, and awk, etc.
  • Projection of the live/real-time DMV data
  • Cross-platform tools which work on both SQL Server on Windows as well as SQL Server on Linux
  • DBFS is available for Ubuntu, RedHat Enterprise Linux, and CentOS as of writing this article.

Happy Learning!

 

Posted in SQL | Tagged , , , | Leave a comment

Importance of Memory Setting in SQL Server

Memory is one among the resources forming the performance triangle—CPU and storage being the other two. If one is hit, the other two take the load to try to bring the performance to acceptable levels, but there’s always the trade-off. Whatever transactions cannot be committed to the memory they would be forwarded to the disk subsystem by SQL Server. This causes a performance bottleneck. Therefore, the wait statistics can help identify performance issues on a SQL Server.

In this article, the following topics are discussed:

  1. Understanding internals of SQL Server memory setting and configuration
  2. The SQL Server memory and its impact on the database and application performance
  3. Discuss various SQL Server components that contribute to the memory usage
  4. Best practices and recommendation for memory sizing
  5. Multi-server memory report
  6. And more…

Memory management internals

SQL Server has a Memory Management Unit that performs automated dynamic memory management based on the workload of the system. This memory is the volatile space that’s critical to today’s Business – Tech needs, the right-sizing of which is vital to the optimal performance of the applications.

However, we all know that when setting up the server, the sizing contains some default values. in some cases, we soon find out that SQL Server uses almost all of the memory on the server, even though there’s no visible activity on the databases, bringing in the questions: Are the default values incorrect? If so, what should be the right size?

Memory Management on SQL Server works on the Fill-and-Flush algorithm. The default values do not restrict the memory consumption from growing unless there’s a request from the Operating System.

The sizing depends on various components of the system—in many cases, setting it between 70% and 80% is a good starting point. Then, you should also monitor it to see what else you may be missing and if you should tweak the setting. If you have other services on the SQL Server (you really shouldn’t), you may need to leave behind more, especially if these services are memory hogs. Consider revisiting the memory setting of the SQL instance in any of the following scenarios:

  • Unresponsiveness of the Operating System
  • Application exhaustion
  • Backup operations that require large memory buffers
  • In-Memory Optimized objects
  • Column store indexes, since they require large volumes of memory to perform index maintenances.

The memory setting on SQL Server is pretty straightforward. You can change the value using sp_configure or SSMS GUI. This is an online option but remember that setting or resetting these values may cause some of the internal cache objects to reshuffle, which will leave the system running slightly slower.

further reading

Importance of memory setting in SQL Server

Happy Learning!

 

 

Posted in SQL | Tagged , , , , | Leave a comment

SQL Server RLS feature and GDPR

Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.

Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.

There are several built-in security capabilities in SQL Server to help in reducing risk and an overall improvement in managing data at the database level or otherwise.

Now that we have a basic idea on GDPR, let’s now dive a little deeper. Here are a few points to keep in mind:

  1. Discover: Identify which data is of personal nature, and technical details about it such as its location and the mode of storage.
  2. Manage: Classify the data access needs and decide the governance model accordingly.
  3. Protect: set up security controls to prevent vulnerabilities and also detect and respond to data breaches.
  4. Report: Document and manage data requests, and provide notifications in case of breaches.

Following are the features in SQL Server that support GDPR compliance:

  1. Row-Level Security (RLS)
  2. Dynamic Data Masking (DDM)
  3. Transparent Data Encryption (TDE)
  4. Transport Layer Encryption (TLS)
  5. SQL Server Audit
  6. Temporal Tables
  7. Always Encrypted (AE)
  8. Authentication
  9. Azure vault
  10. Azure Active Directory
  11. SQL Threat detection

GDPR can be further classified into several categories as follow:

  1. Encryption
  2. Pseudonymous data
  3. Data access, authorization and limitation
    • Row-Level Security (to be discussed in detail in this article)
    • TDE
    • Azure Active Directory
    • Always Encrypted
  4. Assessment, reporting and notification
    • SQL Server Audit
    • SQL Threat Detection

Further reading

SQL Server RLS  and GDPR

Wrapping up

In this article, we walked through the filter and block predicates. We went step by step to provide the required access to users and also, isolate the data operations from various users. This feature greatly simplifies the data security design and helps go closer to implementing GDPR, by enabling us to manage the application access model effectively.

Posted in GDPR, SQL Server 2017 | Tagged , , , , , , | Leave a comment

An overview of SQL Server backup-and-restore process

In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.

Backup is probably the simplest and the most familiar process in most situations. A backup is a copy of the data derived from the production copy, stored at a location that’s different from where the production data is stored. A backup copy is used to recover data needed to restart an application correctly, after certain types of failures. Using various tools and techniques, the data is replicated to different geographies and is made available all the time. Some may even argue that having multiple working copies of the data somewhere else removes the need for having backups, but in a not-so-perfect world, backups are still required to make sure you can always recover from data mishaps.

Further reading

SQL Server backup-and-restore

Summary

As a general rule, the amount of time in between backups should be no more than the amount of time you are willing to spend redoing any lost work. For example, if spending a week recreating the lost data is too long for you, you should back up the data at least once a week.

The additional techniques that we mentioned for a zero-loss model are called High Availability in most situations. These may be very expensive for some organizations. On the other hand, backups are more economical to run. Therefore, because of our budget constraints, sometimes we have to live with the fact that we will lose a few minutes of data.

The recovery point objective and recovery time objective are metrics that are usually decided by the business. Several aspects of the business are taken into consideration when coming up with these metrics. Once these objectives are decided, these must be clearly communicated to the stakeholders (IT and otherwise) within the organization.

 

Posted in SQL | Tagged , , | Leave a comment

SQL Server – Internals of sp_spaceused

This article is an effort to dissect the output of the sp_spaceused stored procedure.

Introduction

Understanding the database usage internals and the growth trends play a vital role in the defining the right sizing of the database. sp_spaceused is probably an administrator’s most widely-executed system stored procedure to find the disk space used by a database. This helps get a quick glimpse of the database usage. statistics. sp_spaceused is used to display the number of rows, the data size, index size, amount of used space, unused space by each object, and the unallocated size of the database. Although looking at the values given by sp_spaceused, one shouldn’t think of shrinking the database or data file or log file. Many a time, we are unaware of what we are doing. Many a time, we don’t know what would be the aftereffects of doing such resource intrinsic operations. The output of sp_spaceused tells us a lot about the current performance of the database. The unallocated column and the unused column tell us the free space left at the database and the table levels.

This article considers:

  1. A peek into sp_spaceused
  2. Impact of the auto-growth setting on the columns, unallocated and unused
  3. Finding the space usage details at the database and the instance levels
  4. Measuring the auto-growth events
  5. Finding the mdf and ldf file sizes
  6. Factors determining the performance of the database
  7. And more…

Further reading, click the following link sp_spaceused

Thanks for reading my space!!

 

Posted in SQL | Tagged , , , , , , | Leave a comment

Know How to Recover SQL Server Database Without Backup

Microsoft SQL Server has widely used database management system for storing and retrieving data. It stores its files in MDF, NDF and LDF format. If SQL database lost due to disasters then backup acts as a silver lining. That is why MS SQL administrators always suggested to take backup regularly. But, data loss can occur anytime due to multiple known and unknown causes. Under such circumstances, if a user has MDF and LDF file as the backup then it is easy to restore the deleted and lost database back to its original state. Otherwise, repairing corrupt SQL files, tables, and other components can be a tedious task if users do not follow the right technique. There are manual commands available that shows how to recover SQL server database without backup but an authentic third-party software is always the best approach to do it.

Method to Recover SQL Server database without backup

If you do not have backup and you need to recover SQL database without backup. Then, in order to recover corrupted tables in SQL database, there are some commands are mentioned below which helps you to recover database manually.

DBCC CHECKTABLE (‘table_name’)
To check entire database in MS SQL Server, this command will require to be used by command line or query manager.

DBCC CHECKDB (‘database_name’)
Both REPAIR_ALLOW_DATA_LOSS and REPAIR_FAST commands have the following repair options:

REPAIR_ALLOW_DATA_LOSS – This command performs allocation & deallocation of rows, pages for correcting allocation errors, structural row or page errors, deletion of damaged text objects etc.

But, these repair command can lose some data. So, this command should be performed only when all methods do not work. The recovery may be done under a user transaction to permit the user to roll back the changes made. If repairs are rolled back, and the database will still have errors, then it should be restored from the backup. Once, recovery process is completed, then you will get back up of the database.

REPAIR_FAST – This command performs minor and non-time-consuming repair actions like repairing extra keys in non-clustered indexes. These repairs can be done fast & without risk of any data loss.

Professional Solution to Recover SQL Database Without Backup

When the manual approach is not able to repair SQL Server Database, generally when the corruption level is severe and users do not have the backup file. In this situation, users are looking for a third-party solution which can smartly handle any level of corruption without backup file. Thus, SQL recovery software is the best tool to recover SQL Server database without backup. The software is extraordinarily programmed to recover all minor & major MDF file corruption issues with accuracy. Moreover, this utility has the ability to restore all SQL database objects such as Tables, Functions, Triggers, Stored Procedures, Keys, Views, Rules, Indexes etc. This software also ensures the quick results without any risk of data loss.

Recover database without backup

Conclusion

If a user has not the backup of SQL database and he or she wants to know how to recover SQL server database without backup. Then, there are both manual method and automatic solution discussed in this blog. But, in case of mass corruption, the manual command may fail to perform the recovery process. To overcome such situation, it is advised to take help from professional solution i.e SQL Recovery Tool. It is very easy and effortless utility.

Posted in Backup and Restore, SQL 2016 | Leave a comment

SQL Server 2017 DMVs and DMFs for DBAs

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

The new or enhanced DMV’s/DMF’s fall into the three categories:

  • Database related
    • sys.dm_db_log_stats
    • sys.dm_db_log_info
    • sys.dm_db_stats_histogram
    • sys.dm_db_file_space_usage
    • sys.sys.dm_db_tuning_recommendations
  • Transaction related
    • sys.dm_tran_version_store_space_usage
  • SQL Server Operating System related
    • sys.dm_os_host_info
    • sys.dm_os_sys_info

Further Reading

SQL Server 2017 new(or Enhanced) DMVs and DMFs

Wrapping up

The overview of new dynamic management views and dynamic management functions that ship with SQL Server 2017 have been explained in detail. They can be put to use to get more insight into the status of the system. It can be informative and useful for the administrators to understand the metadata pertaining to the SQL Server instance. To me, the sys.dm_db_file_space_usage and sys.dm_db_tuning_recommendations turns out to be something I was looking for, for a long time. How about you? Please feel free to comment!

Posted in SQL 2017, T-SQL | Tagged , , , , , , , | Leave a comment