How to Capture Database(s) usage stats using sp_spaceused and PowerShell

The objective of this article is to look at the possible ways of capturing database usage trends using the most commonly available tools and techniques. The integration of SQL, the use of sp_spaceused, and the flexibility offered by PowerShell have been used to get the required database growth patterns; this data is combined into a single result. After the transformation is done, the data is stored in a dedicated repository.

Continue reading….

capture-databases-usage-stats-using-sp_spaceused-powershell/

Wrap Up

In this article, we looked at modifying the output of sp_spaceused system stored procedure, and fetching the raw data that can be put to use after some transformations. We primarily looked at overcoming the challenge of combining the two result sets, using PowerShell to combine the data into a friendly JSON report, which can then be transformed into a SQL table, stored in a central repository; we can now use this repository as a source of raw data which can be used to generate meaningful reports. Also, we saw the integration with OPENROWSET (BULK) function to import JSON files into SQL Server using JSON constructs.

Fetching the raw data and storing is important. When processed, this data would be helpful in capacity planning and forecasting. We shall discuss on how to put this data to use in capacity planning, in a separate article.

Happy Learning…

 

 

Advertisements
Posted in JSON, SQL, SQL 2012, SQL 2016, T-SQL | Tagged , , , , | Leave a comment

Multi-server Script to Find Orphaned Data Files using PowerShell

In this article, we take the sledgehammer approach in searching for MDFs and then comparing them against files which are available on the listed drive. The idea behind this is to generate a report of detached database files across multiple servers. That’s one way to keep our databases compliant in terms of meeting SLAs, and ensuring optimal performance. Running such reports periodically helps us to achieve compliance in terms of file auditing.

Further reading multi-server-script-find-orphaned-data-files-using-powershell

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, Uncategorized | Tagged , , | Leave a comment

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

This post demonstrates one of the ways to gather an inventory of database backup information. The output of the script includes various columns that show the internals of a database, backup size, and gives the latest completion statuses and the corresponding backup sizes. Though the output is derived from the msdb database, having the data consolidated at one place gives us better control and provides us with greater visibility into the database process. A clear understanding of these parameters is a good way to forecast storage requirements. We can schedule a job to pull the data to a central repository and then develop a report on capacity planning and forecasting on a broader spectrum of database backup. That way, we’d have an insight into the sizing of every backup type, such as Full, Differential, and Log. With such information, we can easily decide on the type of backup required at a very granular level, i.e., at the database level.

Getting started

There are many ways to gather data in a central server repository such as using T-SQL and PowerShell. In this section, I’m going to discuss the data gathering tasks using PowerShell cmdlets.

The pre-requisites are

  • Require SSMS version 16.4.1
  • SQL Server PowerShell module

New cmdlets have been introduced with the SQL Server module, which is going to replace SQLPS, by retaining the old functionality of SQLPS with added set of rich libraries. It is safe to remove the SQLPS and load the SQL Server module.

Further Reading

Planning-sql-server-backup-restore-strategy-multi-server-environment-using-powershell-t-sql/

Happy Learning!!

 

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

A Quick Start Guide to Managing SQL Server 2017 on CentOS/RHEL Using the SSH Protocol

This article details the following

  1. How to get PuTTY and SQL Server
  2. How to install/uninstall SQL 2017 CTP2.1
  3. How to Upgrade to SQL 2017 RC1
  4. How to Install/remove SQL Tools

In one of my previous articles Installation of SQL Server vNext CTP on Linux Distribution CentOS 7, I spoke about the installation of SQL Server 2017 on a Linux computer. Let us now look at another way to showcase that a SQL Server installation and configuration can also be managed using the Secure Shell (SSH) protocol using PuTTY. We shall look at the important details, such as, how to get PuTTY, SQL Server, how to install/uninstall SQL 2017 CTP2.1, Upgrade to SQL 2017 RC1, and install/remove SQL Tools.

Further reading, A Quick Start Guide to Managing SQL Server 2017 on CentOS/RHEL Using the SSH Protocol

Suggestions and comments are most welcome!

Happy Learning!!

 

 

 

 

 

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

How to Configure TDE database with AlwaysOn using the Azure Key Vault in SQL Server 2016

One of the recent tasks that I have undertaken seemed rather like an experiment to me. I set out to configure Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with AlwaysOn. That, to me, opened a whole different dimension on data security.

Configure-tde-database-alwayson-using-azure-key-vault-sql-server-2016

Happy Learning!!

Posted in AlwaysOn, SQL 2016, SQL AZURE | Leave a comment

How to analyze SQL Server database performance using T-SQL

The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.

Using the techniques and methods baked right into SQL, one can collect the aggregate information at the instance level. The instance level data sometimes might be helpful when you want to get an insight at a higher level. When we work with mission-critical systems, the breakdown at a granular level and detailed information on CPU, Memory, and I/O is always helpful. There are few metrics that are important, and I’m going to discuss those shortly.

This article describes on how to measure the various performance characteristics of databases. Learn about how:

  1. In-lines T-SQL’s module for each performance metrics
  2. Details the use of SQL 2017 STRING_AGG string function
  3. Includes the use of DMF sys.dm_db_log_info
  4. Display consolidated data using T-SQL
  5. and more …

What database metrics do I monitor?

SQL Server Performance Monitoring revolves around many key areas

  • CPU
  • Memory
  • Disk I/O
  • Also, the factors such as user connections, database transaction rate, and data and log file settings

These factors give an overview of its impact on the performance of the application. This article is an effort to understand the general characteristics of databases; it gives an overview of the key factors used to classify the databases as critical, medium and low usage databases.

There are many tools available to track the database usage details, which are listed below:

  • DMV’s
  • SQL Profiler
  • Counters
  • Activity Monitor
  • Windows Perfmon
  • Third party tools

SQL Server bundles various monitoring options but there will be cases when you would want to turn to third party tools. The article outlines the details the using native SQL techniques to identify the most resource-intensive databases of an SQL instance. The following SQL’s are tested on SQL 2012-2017

Further reading…

https://www.sqlshack.com/analyze-sql-server-database-performance-using-t-sql/

Happy Learning!!

 

Posted in SQL Server 2017, sysfiles, T-SQL | Leave a comment

SQL Server 2017

SQL 2017

 

Happy Learning!!

Posted in SQL Server 2017 | Leave a comment

Overview of Resumable Indexes in SQL Server 2017

Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.

This article outlines

  • Overview of the indicators of disk space issues during heavy index rebuilding maintenance
  • Index maintenance options available in SQL 2017
  • How to use index_resumable_operations
  • Application of these settings on SQL partitions
  • A demonstration
  • And more…

Background

Being a database administrator, understanding the implication of fragmentation takes precedence over fixing it. Often we don’t think about fragmentation details, and the proceeds to rebuild (or defrag) every index in the database. This operation eats up a significant amount of transaction log space and has a substantial impact on the system resources, backup size, restoration time, etc. Finding the Fill Factor parameter and setting it to a right value works great to deal with fragmentation, but in many cases, finding this value is a tedious job.

Rebuilding indexes require additional disk space, and an inadequate disk space can negatively impact the I/O, and degrade system performance; sometimes the entire operation may fail.

The index rebuild operation typically has an option to sort the results in a system database called “TempDB”. The option, SORT_IN_TEMPDB, is set to OFF by default for each index. When using this option, one must ensure that there is enough room to expand TempDB. If not, we must at least make sure that there is enough room on the disk on which the user database resides.

If the index rebuild maintenance operation fails due insufficient disk space, the operation has to be started from the beginning. In many instances, administrators struggle to manage the load on the system resources and wish to pause and resume the operation at a later point.

Further reading….

Overview-resumable-indexes-sql-server-2017

Happy Learning!!

 

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

How to use Python in SQL Server 2017 to obtain advanced data analytics

On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.

After the conference, there were a few questions some of us had, though. Is Microsoft SQL Server 2017 emerging as an enterprise solution for data science? Does it provide the required capabilities—is the engine capable of handling huge data? It seems the answer is “Yes”, as starting with the CTP 2.0 release of SQL Server 2017, Microsoft has brought Python-based intelligence to data in SQL Server.

Python has gathered a lot of interest recently as a language of choice for data analysis. This language has the right set of libraries for data analysis and predictive modeling, not to mention a simpler learning curve.

The growing trends of data science and modeling predict a massive growth in data in the upcoming years. The propulsion towards innovation and adaptation to leading trends in the data technology might intrigue us enough to make us take a look at the current release of SQL Server 2017.

Data science is a combination of Data Mining, Machine Learning, Analytics and Big Data. The integration of SQL 2016 with data science language, R, into the database the engine provides an interface that can efficiently run models and generate predictions using SQL R services. Python builds on the foundation laid for R Services in SQL Server 2016, and extends that mechanism to include Python support for in-database analytics and machine learning.

further reading…….

https://www.sqlshack.com/how-to-use-python-in-sql-server-2017-to-obtain-advanced-data-analytics/

Happy Learning!!

 

 

Posted in Python, SQL Server 2017 | Leave a comment

Top string functions in SQL Server 2017

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

This article outlines the basic concepts of how to use the new string function in SQL Server 2017 on a Linux machine.

The SQL Server 2017 CTP 1.1 contains various string function out of the box, a few of them being:

  • CONCAT_WS, TRANSLATE, and TRIM
  • Support for the WITHIN GROUP clause for the STRING_AGG function.

A collection of new string function in SQL Server 2017, such as TRANSLATE, TRIM, CONCAT_WS, STRING_AGG are almost similar to the string functions of other relational databases engines.

As developers, we try to achieve results in simple and smart ways. The string functions available in SQL Server 2017 make the life of a developer much simpler.

So, let us get started, and see the usage of the available string functions!

TRIM

Removes the space character char(32) or other specified characters from the start or end of a string.

As a SQL developer, you must have often come across a scenario where you had to remove the empty spaces at the beginning and the end of strings. To achieve that, you may have used the string functions, RTRIM and
LTRIM—you had to use both because SQL Server does not have a function which can trim leading or trailing spaces of a given string with a single function, even though TRIM() is a very popular function in many languages. The release of SQL Server 2017 comes with this new string function “TRIM”, which you can use to get rid of the leading and trailing spaces around any given string.

SQL Server 2017 SQL Server
Syntax TRIM ([characters FROM] string)
Characters could be a literal, variable, or a function call of any non-LOB character type (nvarcharvarcharnchar, or char) containing characters to be removed. nvarchar(max) and varchar(max) types are not allowed.
A string is an expression of any character type (nvarcharvarcharnchar, or char) from which characters should be removed.
CREATE FUNCTION dbo.TRIM(@str VAR-CHAR(MAX))
RETURNS VAR-CHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@str))
END
or CLR

Let us consider an example string, from which we have to remove the leading and trailing spaces:

further reading

https://www.sqlshack.com/top-string-functions-in-sql-server-2017/

Happy Learning!!

 

 

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