Conditional Split Transformation: Populate data from SQL Server, divide and save in MySQL and Oracle databases

In this article, I have explained how we can divide and export data of SQL Server database table into Oracle and MySQL databases. That process is based on the condition defined in SSIS as “conditional split transformation”. Conditional Split transformation is like case statement of any programming language. Using conditional, we can redirect the output of a component in multiple destinations that is based on a condition defined in the conditional split component. Read More

Jump to Start Test-Driven Database Development (TDDD) – Part 3

This article is a walk-through of creating a report base on a database object developed and tested by using test-driven development (TDDD). Furthermore, some tips for improving database unit testing via TDDD will be discussed in this article too.

Test-driven database development (TDDD) Recap

In simple words, TDDD is all about writing unit-test before the database object is even created. So, if we want to create a database object to satisfy some business requirement, it should be processed by creating a unit-test that ensures the object exists. Moreover, it has to be followed by another unit-test that will ensure the proper functioning of the database object (at the minimum) though that functioning check should only be limited to meet only the desired requirement. Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Introduction

 

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More

Export data from SQL Server to Excel and Text file via using SSIS package

While using the SSIS data flow, we can perform ETL task that can be used for data migration, data offloading, design and implementation of the data warehouse.

In this article, I explain how to export data from an SQL table to excel and text file by using the SSIS data flow task.

In this demo, I will perform through the following steps:

1. Create an SSIS package and create ADO.Net connection manager.

2. Create data flow task that has ADO.NET source to populate data from SQL Server. Data will be written to excel file and text file hence configure flat file destination and excel destination.

3. Execute SSIS package and verify that data was written to excel and text files.

Read More

Jump to Start Test-Driven Database Development (TDDD) – Part 2

We discussed the basics of test-driven database development (TDDD) with examples and compared it with traditional database development in the first part of this article.

In the second part, we are going to move beyond basics to focus on a more realistic scenario of meeting report requirements by using TDDD.

A quick recap of TDDD at this point is handy in understanding how it can help us to achieve such goals.

Read More

Consolidating SQL Server Instance by Clustering and Stacking

Stacked SQL Cluster Instances

NOTES:

  • Windows Failover Clustering comprising two nodes.
  • Two SQL Server Failover Cluster Instances. This configuration optimizes the hardware. IN01 is preferred on Node1 and IN02 is preferred on Node2.
  • Port Numbers: IN01 listens on port 1435 and IN02 listens on port 1436.
  • High Availability. Both nodes back up each other. Failover is automatic in case of failure.
  • Quorum Mode is Node and Disk majority.
  • Backup LAN in place and routine backup configured using Veritas

Read More

Understanding Data Sharing in SQL Server Docker Containers

In the previous articles, “Introduction to Docker and Docker Toolbox” and “Docker and SQL Server on Linux“, we discussed a lot about SQL Server Docker containers. As you should know by now, instantiating SQL images is very simple and pretty straightforward thing with the Docker containers.

This article talks about shared volumes and the preferred mechanism for data persistence using the Docker containers. Read More

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2

In my previous article, I have covered the following topics:

  1. What are Registered Servers and their usage.
  2. How to create and configure a Local Server Group and Central Management Studio.

In this article, I am going to demonstrate how to execute a query on multiple servers using Registered Servers. Also, I am going to cover how we can evaluate the database policy on multiple database servers using Registered Servers. Read More