Prashanth Jayaram

Prashanth Jayaram

SQL Server DB Migration – Cloning a database to another collation

September 19, 2017 by

Database migration is a vital task in any environment, complex or otherwise. Seamless migrations are the goal but the efforts required to ensure it are tremendous.

Backing up and restoring the database is surely a preferred and a robust approach, but does it work well in all situations? How do we plan this when the source and the destination databases need different configurations? How do we make such a migration seamless?

Read more »

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

September 11, 2017 by

Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator.

Read more »

Multi-server Script to Find Orphaned Data Files using PowerShell

August 23, 2017 by

Having worked in busy dev-test environments, it isn’t uncommon to come across situations where someone detached a database from an SQL server, but forgot to reattach it, or drop it from the server. The next task we are then required to undertake, upon coming across such a situation, is to clean up the traces. This task can be complicated if the detached databases get deregistered from the SQL Server metadata catalog.

Read more »

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

August 15, 2017 by

Introduction

Database availability is critical to every enterprise and conversely, unavailability directly can create a severe negative impact to the business in today’s world. As database administrators, it is important that we ensure we take all possible steps to minimize data loss. While it is naïve to think that our databases are invincible because of all such precautions, we can always aim to bring the databases back into operation as quickly as possible by meeting the RPO and RTO. This way, the business is unaffected, and we also meet our SLAs.

Read more »

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

August 8, 2017 by

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.

Read more »

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

July 28, 2017 by

One of the recent tasks I undertook on configuring Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with Always On opened a different dimension on securing data for me. Even though it seems slightly complex, if you have the key details, the steps are in fact, really straight forward.

Read more »

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

June 20, 2017 by

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.

Read more »

Top SQL string functions in SQL Server 2017

June 8, 2017 by

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.

Read more »

Installation of SQL Server vNext CTP on Linux Distribution CentOS 7

May 10, 2017 by

It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.

Read more »

SQL Server 2016 enhancements – SQL Truncate Table and Table Partitioning

April 18, 2017 by

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the SQL truncate command partitioning enhancements in SQL 2016

One of the biggest challenges for a DBA is to identify the right candidate for table partitioning, as it requires expertise in design and implementation.

Read more »

Custom keyboard shortcuts in SQL Server Management Studio (SSMS)

March 27, 2017 by

People love taking shortcuts because it’s the easiest way to do things faster; computers are no exception. Shortcuts, particularly ones performed by keyboard, can save you hours of time once applied properly. Learning keyboard shortcut saves you a lot of time; you’ll definitely notice a boost the productivity because you’re not unnecessarily reaching for a mouse all the time.

Read more »

SQL Server Database Recovery Process Internals – database STARTUP Command

February 14, 2017 by

A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.

Read more »