Prashanth Jayaram

Author
Recent Posts

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

Substring function overview

September 14, 2018 by Prashanth Jayaram

The requirement of data refactoring is very common and vital in data mining operations. In the previous article SQL string functions for Data Munging (Wrangling), you’ll learn the tips for getting started with SQL string functions, including the substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.

SQL string functions for Data Munging (Wrangling)

September 13, 2018 by Prashanth Jayaram

In this article, you’ll learn the tips for getting started using SQL string functions for data munging with SQL Server. In many cases, Machine learning outcomes are only as good as the data they’re built on – but the work of preparing data for analytics (that is, data wrangling) can eat up as much as 80% of your project efforts.

SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup

September 13, 2018 by Prashanth Jayaram

A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.

SQL Replication: Basic setup and configuration

September 12, 2018 by Prashanth Jayaram

This is article is a continuation of the previous: SQL Server replication: Overview of components and topography.

By now, you’re familiar with the components of replication. So far, we’ve seen a lot of theory about replication. It’s a time for practical walkthrough of setting up a basic transactional SQL Replication system. The best way to get a feel for how SQL Replication is implemented and how it works is to see it in action.

How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases

September 12, 2018 by Prashanth Jayaram

In this data-driven era, replication is often a critical requirement for achieving a modern, agile database management environment. It is believed designing an enterprise-grade dataset is the to achieving this requirement but building datamarts from datasets always presents certain challenges

In this article, we’ll discuss what it takes to setup “central subscriber with multiple publishers” replication model, to create an aggregate dataset from multiple sources, and you’ll also see how to scale with the data.

SQL Server replication: Overview of components and topography

September 11, 2018 by Prashanth Jayaram

The volume of data retained, managed, and accessed today is unprecedented. Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. To meet today’s needs; we need to replace outdated and inefficient legacy processes with new, more agile techniques. SQL Server Replication is one of the techniques to accommodate such demands.

How to setup cross-platform transactional SQL Server replication for database reporting on Linux

August 31, 2018 by Prashanth Jayaram

In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.

How to do a quick estimated compare of data in two large SQL Server databases to see if they are equal

August 29, 2018 by Prashanth Jayaram

Bringing impactful analysis into a data always comes with challenges. In many cases, we rely on automated tools and techniques to overcome many of these challenges.

How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases

August 29, 2018 by Prashanth Jayaram

In this article, you’ll learn how to setup a simple, custom distributed database replication system.

Introduction

In general, a typical setup of transactional replication model of a central publisher with multiple subscribers includes the creation of a replica database(s) which may serve multiple purposes including:

How to Add/Drop articles from existing publications in SQL Server

August 24, 2018 by Prashanth Jayaram

This article discusses the challenges of meeting the availability, and performance requirements of high ended transactional replication environments. In addition, you’ll learn a new innovative approach that can be used to add/drop articles from an existing replication environment while maintaining replication system up and running.

How to continuously audit transaction log file data directly into a SQL Server database

August 23, 2018 by Prashanth Jayaram

Business transformation requires solid tools to automate complex integration to seamless deployments. In today’s modern data-rich world, nothing is more important than data management, making it critical to know how to safeguard and meet compliance requirement is very critical and the key to the business success.

How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog

August 23, 2018 by Prashanth Jayaram

Outline

In this article, we’ll discuss how to read SQL Server transaction logs. This article should provide and insight into how the workload is being captured in the transaction log files. We are going to see how to get the transaction meta-data details and the history of the data will give us a clear idea of how the system is working and helps to decide peak usage hours, also helps to log information about who is doing what?

Understanding automatic tuning in SQL Server 2017

August 22, 2018 by Prashanth Jayaram

Monitoring databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused or expensive indexes is a common database administration task. As administrators, we’ve all wished, at some point, that these tasks were simpler to handle.

How to set up a DDL and DML SQL Server database transactional replication solution

August 22, 2018 by Prashanth Jayaram

In the article How to setup SQL Server database replication for reporting Server we discussed all about setting up a simple SQL Server transactional replication environment. In this article, we’ll simulate a more robust system that replicates faster and includes both DDL and DML statements. We’ll walk-through the steps to setup a dynamic transactional replication.

SQL Server database migrations with zero data loss and zero downtime

August 15, 2018 by Prashanth Jayaram

The growing importance and complexity of data migration, in an era of exploding data volumes and ever-changing business requirements, means that old approaches will no longer get the job done. We are in a world where everything needs to run instantly. Every Database Administrator or Developer would have definitely heard about database migrations with zero downtime and with zero data loss.

What’s new in SQL Server 2017

August 14, 2018 by Prashanth Jayaram

SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing lot about it and testing various features of SQL Server 2017, I’d like to walk you through some of its interesting features.

Getting started with Azure Data Studio (ADS); initial installation and configuration

August 14, 2018 by Prashanth Jayaram

Microsoft Azure Data Studio is a new GUI-based (vs CLI) lightweight tool for developing and managing your modern SQL databases. The tool resembles Microsoft’s lightweight code editor, the open source IDE Visual Studio Code in terms of functionalities and the UI in general. If you’ve used Visual Studio Code (or VS Code), you’d feel at home with Azure Data Studio. Azure Data Studio also integrates really well other products like Azure SQL Database and Data Warehouse has been made very simple. Azure Data Studio is a cross-platform tool available for Linux, Windows, and Mac to manage the SQL Server databases. You can go ahead and download Microsoft Azure Data Studio to try it out!

All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool

August 14, 2018 by Prashanth Jayaram

One of the major challenges we face today, in the software development lifecycle, is with respect to development and deployment. As applications are deployed by moving various pieces of the SQL code between several versions, configuration, different editions, and sometimes even different platforms, deployment becomes daunting. In my opinion, scripting helps a lot and can really “grease the wheels” with addressing many scenarios that involve such complexity.

The importance of database level scripting is high, but it is getting easier by the day. Scripting is a process of generating a text file that contains the data structures and data from the database objects.

So, do we have a SQL native tool which serves the various purposes and usage? In some cases, the answer’s a yes, and in others, it’s a no.

Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe

August 14, 2018 by Prashanth Jayaram

A little curiosity on my part led me to research a little further on the available pieces of SQL Server Tools. A cross-platform tool for continuous integration and deployment, called SqlPackage, is part of SQL Server Data Tools. I picked it up because continuous integration, delivery and deployment are now must-have in the modern-day application development paradigm.

We’d talk about the following in this article:

The Database Management Life Cycle
Complexities of the database release management process
Introduction to SQL Server Data Tools and SqlPackage
Using SqlPackage.exe, with examples

The BCP (Bulk Copy Program) command in action

August 13, 2018 by Prashanth Jayaram

There are various methods available for bulk data operations.

BCP utility
BULK INSERT
Using OPENROWSET
Import/Export wizard

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information. Hence, it is recommended to create a format file to record the data format so in case of any failures, you can refer to the format file and better understand the data format to determine what may have gone wrong..

We’ve been using the BCP tool for a long time, the reason being that it has a very low overhead, and works great for bulk exporting and importing of data. It is one of the most efficient ways to handle bulk import and export of data.

Overview of the SQLCMD utility in SQL Server

August 13, 2018 by Prashanth Jayaram

This article is aimed at helping you understand the sqlcmd utility. Of course, this is a tool most of us have used it at several occasions given that the tool has been around for a decade now. However, to be a solid starting point, this article contains some examples that help you understand the tool from different viewpoints.

This article discusses the following:

How to enable SQLCMD
A few important points to remember
SQLCMD with scripting variables in SSMS
Passing variables (or argument) as T-SQL and or a SQL Script file
SQLCMD with scripting variables and Windows scripting in SSMS

Using transactional data replication to replay and test production loads on a staging server

August 9, 2018 by Prashanth Jayaram

Outline

In this article, you’ll see how to simulate production loads on a test server with a “record and replay” type situation using the transaction log, batch scripting, PowerShell and a SQL Server agent job.

We’ll be walking through the scenario in the following steps

Record the production load and write the transactions to disk by generating a timestamped replay script
Create a batch file to automate the task at an interval of every 1 minute
Create a SQL Server agent job to schedule the batch file
Replay the production workload to the target/test database by running a PowerShell script to open and execute the scripts at the same interval as they were created, every 1 minute
Validate the data between the source and the target databases to make sure our job works
Monitor the load with a monitoring tool, solution of your choice

How to setup SQL Server database replication for a reporting server

August 7, 2018 by Prashanth Jayaram

In this article, we’ll discuss the purpose of database replication and show how you can implement Replication using ApexSQL Log, a SQL Server transaction log reader.

SQL interview questions on database backups, restores and recovery – Part I

July 30, 2018 by Prashanth Jayaram

So far, we’ve discussed a lot about database backup-and-restore process. The backup database command is an online database copy of the SQL Server database and restore database command gives an option to test the consistency and integrity of the backup file.

As we all know, the backup database command bound with many database options. Indeed, it facilitates the execution of specific backup database command that meets the business requirement.

Database Backup and Restore process in SQL Server – series intro

July 26, 2018 by Prashanth Jayaram

It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures.

Ready to dive-deep into Backup-and-Restore procedures? Let’s go!