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!

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

How to fix the database consistency and integrity issues using DBCC CHECKDB and Third Party Tool

Overview

The database protection and recovery is a vital role of database administrators. While working on SQL Server, sometimes users get stuck in annoying situations when the database becomes inaccessible. After executing DBCC CHECKDB command to check the problem, the user gets database consistency errors

Many companies may have the budget to have a database recovery solutions in place to protect the system from various disasters.

I would also like to discuss the third party tool Stellar Phoenix SQL Database Repair software from Stellar Data Recovery as a recovery solution.

The DBCC CHECKDB Consistency Errors behind the doors

The reason behind the database consistency error can be varied from

  1. file system corruption
  2. corrupted pages in memory
  3. underlying the hardware system issues
  4. drive issues
  5. or some problem with SQL Server Engine

How to Fix Database Consistency Errors Reported By DBCC CHECKDB?

DBCC CHECKDB checks the physical as well as the logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationship, and other structure. If any of these checks fail then, errors will be described as part of the command. To resolve the inconsistency errors, users first need to restore the data from the backup of data. However, if users are not able to restore from backup then CHECKDB gives a feature to repair errors. Many times, the problem arises due to file system or hardware issue so the user should correct these first before restoring and running repair. There are some resolutions that discussed below to help users to fix SQL database consistency errors.

Windows System Event Log

Windows System Event Log errors indicate possible I/O problems that can be associated with the inconsistency of database. It contains various Event IDs depend upon the inaccessibility of data. Every event ID has different resolution to fix the occurrence of an error.

Integrity of File System

Many times, the users face the inconsistency error due to the file system integration. To check the integrity of file system, the user can use the chdsk command. This command helps to create and displays a status report for the disk. It also lists all the errors on the disks. It is available with different parameters from the command prompt.

I/O Requirements

There are various counters, which are related to I/O and all are located in Logical and Physical Disk. The physical Disk performance object consists of counters that monitor hard or fixed disk on the system. The logical disk performance of an object consists of counters, which monitors the logical portion of fixed or hard disk drives.

SQLIOSim Utility

There is a utility known as SQLIOSim that also reports the consistency errors. SQLIOSim is a tool, which is independent of SQL Server Engine for testing the integrity of I/O for the disk system. It can be downloaded from the web for utilization.

Note: SQLIOSim ships on SQL Server 2008 does not require the separate download.

Verify Checksum Option

Make sure that the database, which is using PAGE_VERIFY CHECKSUM option. If the checksum error is reported then, it indicates that the consistency error has occurred. After writing the SQL Server, pages to disk the error occurred so that the disk system should be checked thoroughly. The user gets 824 error in SQL Server, which is caused due to some external conditions. Some external conditions have caused the modification on database page outside the SQL Server engine code. It can be resolved if the user running any hardware or system checks to determine if CPU, memory or other hardware related issues exist. The user can update all system drivers, Operating system, or hardware is required on the system.

Minimum Repair Option

When the user runs DBCC CHECKDB, a recommendation is used to indicate the minimum repair option, which is essential to repair all errors. Its message appears as mentioned below:
CHECKDB found 0 allocation errors and 15 consistency errors in database 'adventureworks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (adventureworks

The repair recommendation is the minimum level of repair to attempt to resolve all the errors from CHECKDB. It does not mean that all the errors are fixed by repair option. It means that all the errors that are not reported by CHECKDB. When repair_allow_data_loss is recommended will result in loss of data. The repair must be run to determine if there is the resolution of an error that will result in data loss. It is a technique to help narrow down if the repair level for each table is to access DBCC CHECKTABLE for any table that is reporting an error. It will display the minimum level of repair for given table.

Third Party tools

To download the SQL recovery tool and test with the free Demo version of the software and then proceed.

I searched online for this product, conducted a thorough research on software and decided to download its free version.

This is how to proceed:

Check Software Specifications

Checked with software specification of Stellar Phoenix SQL Database Repair software:

Operating System
Windows Server 2012 / 2008 / 2003 and Windows 10 / 8 / 8.1 / 7 / Vista / XP
Memory 1 GB
Hard Disk 50 MB
Version Supports MS SQL Server 2016, 2014, 2012, 2008 R2, 2008, 2008*64, 2008 Express, 2005, 2005*64, Express, 2000, 2000*64, 7.0 and mixed format

Install Software on the system

  • Download the software here 
  • After the download, run the Setup Wizard
  • Accept the License agreement
  • Specify the target location for the installation
  • Click Next
  • Stellar Phoenix SQL Database Repair software was successfully installed and ready to use.

Test software on the system

Open the software Stellar Phoenix SQL Database Repair Software.The software GUI interface is simple and self-explanatory to perform the proceeding steps.

  • Click on Select Database
  • Locate the file using find database submenu. The MDF-extension related files opened and prompted for selection of the correct file.10
  • Next, the Stellar software scanned through MDF file and provided the scanned preview of repaired SQL Database for verification.

11

  • Proceeded with SQL database verification and saving file at preferred location.

12

  • Software provided various saving options and my preference rested on MDF format.

SQL Database was saved as MDF file in original format.

In a nutshell, the whole process of Database recovery and resolution of Suspect mode problem was completed with the click of three buttons –

Select file —- Repair File —– Save File

 

Conclusion

In the above discussion, the problem and cause of the consistency error are described. Along with this, solution on to how to troubleshoot database consistency errors reported by DBCC CHECKB is discussed. It makes easy for users the error occurrence.

The primary responsibility of the database administration team is to review all types of RDBMSs in the enterprise and to develop a comprehensive backup plan to conduct effective backup management by proactively monitoring backups, getting alerted for failed backups and rerunning these seamlessly, without loss of time. It is good practice to back up data to physical disk and to then archive the data to tape for disaster recovery purposes.

Stellar Phoenix SQL Database Repair software is a third party tool and dissolves any kind of Database related distress for SQL Administrator. I would recommend trying this tool.

 

Posted in Uncategorized | Leave a comment

Python and SQL Server Administration

Some of my previous articles on Python provided insight of the basics and the usage of Python in SQL Server 2017.

This article is an effort to collect all the missing pieces and try to showcase the importance of using Python programming in SQL Server.

Many say that PowerShell has the upper hand over Python in some aspects of the database administration. I too am under the same impression as many technical enthusiasts, but with very limited knowledge, we can also see the power of Python. Perhaps, instead of pitting PowerShell and Python against each other, we can look at them as complementing technologies.

In 2016, R, the statistical computing programming language was integrated with the SQL Server version, named for the same year. The other side of the coin was missing since Python is also a leading machine learning language and even having a large user base. In SQL Server 2017, Python is integrated. Now, R and Python are under the same umbrella of the feature called machine learning services.

Further reading

Python in SQL Administration

Happy Learning!!

 

 

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

Different methods to write PowerShell output to a SQL table

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.

I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.

When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.

Let’s get out and see the possible options to transform WMI data to SQL table. In this article, we will:

  1. discuss Invoke-Sqlcmd
  2. talk about the .NET class libraries
  3. talk about exporting data using various Export* cmdlets
  4. learn how to use Windows Management Instrumentation (WMI)
  5. discuss SQL Constructs to load data from file
  6. and more

This guide details the working example of checking disk space by querying WMI.

We discuss the transformation of the above data into a SQL Table using some direct as well as indirect methods in this post:

  1. using Invoke-Sqlcmd
  2. using ADO
  3. WMI Query
  4. using Export commands such as JSON,XML and CSV

 

Further reading

PoSH->Data Transformation -> SQL Table

Happy Learning!!

 

Posted in PowerShell, SQL, T-SQL | Tagged , , , , , | 2 Comments

Configure SQL Server 2017 on Linux with mssql-conf, the Linux way

The configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.

SQL Server on Linux provides a wide range of options for configuration, management, and administration.

  1. mssql-conf
  2. Transact-SQL
  3. SQL Server Management Studio
  4. PowerShell

This article outlines the use of mssql-conf in conjunction with other available tools, to manage SQL Server.

On Windows, we are familiar with SQL Server Configuration Manager, the configuration tool used to manage and administer SQL Server services, configure the network protocols, and to manage network connectivity between the SQL Server and the client computers.

On Linux, we have a configuration tool known as mssql-conf. The primary function of this tool remains the same as that of the SQL Server Configuration Manager, but makes it easy for Oracle and Linux admins to work with SQL Server 2017 on Linux. I’d like to visit some of the key concepts of the Oracle parameter file (PFILE and SPFILE) here, with which the server configuration can be set/unset by adding/modifying entries in the configuration file.

Further reading…

mssql-conf

Happy learning!

 

 

Posted in SQL 2017 on Linux | Tagged , | Leave a comment

An introduction to a SQL Server 2017 Graph database

What is a graph database?

A graph is composed of two elements: a NODE (vertices) and an EDGE (relationship). Each node represents entities, and the nodes are connected to one another with edges; these provide details on the relationship between two nodes with their own set of attributes and properties.

The graph database can be defined as the data structure representation of an entity modeled as graphs. It is derived from the graph theory. The data structures are the Node and the Edge. The attributes are the properties of the node or the edge. The relationship defines the interconnection between the nodes.

Relationships are prioritized in graph databases, unlike other databases. Therefore, no data inference using foreign keys or out-of-band processing is needed. We can build sophisticated data models simply by assembling abstractions of nodes and edges into a structure. Given the priority for relationships over data, the development stack receives the biggest value here.

In today’s world, relationship modeling requires more sophisticated techniques. SQL Server 2017 offers graph database capabilities to model relationships. Graph DB has nodes and edges—two new table types NODE and EDGE. And a new TSQL function called MATCH(). The Node and Edge (relationships) represent entities of the graph database. And since this capability is built into SQL Server 2017, already-existing databases don’t have to be ported to another system, so to speak.

Further reading…

Graph database

Stay tuned for more updates on this topic….

Posted in Graph database, SQL 2017, Uncategorized | Tagged , , , | Leave a comment

SQL Server 2017 : Data Transformation and Interpolation using Python

As a continuation to my previous article, How to use Python in SQL Server 2017 to obtain advanced data analytics, a little bit of curiosity about Deep Learning with Python integration in SQL Server led me to write this latest article.

With Python running within SQL Server, you can bring the existing data and the code together. Data is accessible directly, so there’s no need to extract query data sets, moving data from storage to the application. It’s a useful approach, especially considering issues of data sovereignty and compliance, since the code runs within the SQL Server security boundaries, triggered by a single call from T-SQL stored procedures.

Continue reading…

Data Interpolation and Transformation using Python in SQL Server 2017

See Also,

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 2017, SQL and Python | Tagged , , , , | Leave a comment

SQL Server Log Shipping on Linux

Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.

On a high level, Log Shipping consists of preparing the secondary database (NORECOVERY or STANDBY) using a backup of the primary instance database and then applying the transaction logs from the primary database onto the secondary database using SQL Server Agent Jobs and a shared file location. In other words, it’s an automated process of restoring transaction log backups from the primary database server on to the secondary database/database server. This way, the database is available for failover.

We’ve seen multiple models of implementation of log shipping: log shipping on the same server, on to a different server, across domains or workgroups, or two different versions of SQL Server. SQL Server, now being available for Linux, makes us think, “Can we set up log shipping across platforms?”

As it turns out, yes, we can. This article discusses the setup. We’ll use a network file share for the demonstration. Let’s jump right in!

Continue reading….

Log shipping on Linux

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 Logshipping on Linux, SQL 2017 | Tagged , , , , | Leave a comment

An overview of Python vs PowerShell for SQL Server Database Administration

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

All that is to say, you can perform the same actions or achieve the same level of automation, using either language: PowerShell or Python.

Python is better as a general-purpose programming/scripting language. If you also have Linux or other non-Windows boxes to administer, you may also probably prefer Python, even though Microsoft is pushing and assisting third parties in developing PowerShell modules to cover wide areas of technologies. If you are a Windows admin, PowerShell is something you’d probably like more, simply because of its deep integration with Microsoft products.

In this article, we’ll describe and discuss both technologies and you can see for yourself, which you may choose.

In my opinion, PowerShell or Python should now become PowerShell and Python.

Continue reading…

An overview of Python vs PowerShell for SQL Server Database Administration

What’s your opinion?

What programming language do you recommend to sysadmins and new IT pros? What resources do you think would be most helpful when learning a new coding language? Let’s have a discussion in the comments below!

Thanks for reading my space!

 

Posted in PowerShell, Python, SQL 2017 on Linux | Tagged , , | Leave a comment

Dynamic Data Masking – Tracking all masked columns in your database

dotnetvibes

I have presented on Making Developers lives easier with SQL Server 2016 multiple times over the last couple of months, and I must admit that even though the content is the same – every presentation is different depending upon the attendees and their expertise levels. During theGulf Coast Code Camp, MobileandDevSpace Conference, Huntsvillethere were some interesting questions I was asked on Dynamic Data Masking during/after my presentation. I thought of writing a quick blog post to answer those questions. Honestly this is something which I plan to start doing more often – since every time I give a presentation there are few questions which stand out and and some times I have to come back and do more research to find the right answer. Well it is never too late to make a start!

Dynamic Data Masking in SQL Server 2016 is a new built-in…

View original post 185 more words

Posted in Uncategorized | Leave a comment