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

How SQL Server 2016 Took Azure SQL Data Warehouse to Next Level

Azure SQL DW (SQL Data Warehouse) is a massively parallel, petabyte-scale, cloud solution for data warehousing based on SQL. It is highly elastic and fully managed, allowing you to scale capacity in seconds and set up in minutes. You can scale computing and storage independently by yourself. It will enable you to burst computing of analytical workloads that are complex, or scale down your warehouse for archival scenarios and pay depending on what you are utilizing rather than lock yourself into cluster configurations that are predefined – and obtain better cost efficiency when compared with traditional data warehouse solutions. Read More

Exploring SQL Server 2016 Query Store GUI

Introduction

Query store is a new feature, introduced in SQL Server 2016, that allows database administrators to historically review queries and their associated plans using the GUI available in SQL Server Management Studio, as well as to analyze query performance using certain Dynamic Management Views. Query Store is a database scoped configuration option and is available for use if the compatibility level of the database in question is 130.

Read More

Performing Data Changes Audit Using Temporal Table

SQL Server 2016 has introduced a feature called ‘System versioned temporal table’. Using normal table, you can retrieve current data; while using a system-versioned temporal table, you can retrieve data which was deleted or updated in the past. To do that, a temporal table will create a history table. The history table will store old data with “start_time” and “end_time”. Which indicates a time period for which the record was active. Read More