Synthetic Data Generation. Part 3: Backup and Restore

Total: 1 Average: 5

Implementation of the general script for data sanitization and the secret data changes

We’ve examined simple examples for each type of altered data in the previous articles (Part 1: Data Copying, Part 2: Data Changing):

  1. Changing the date and time;
  2. Changing the numerical value;
  3. Changing the byte sequence;
  4. Changing the characters’ data.

However, the examples described above don’t meet the criteria 2 and 3 for the data altering scripts:

Read More

Synthetic Data Generation. Part 2: Data Changing

Total: 1 Average: 5

Character Data Change

Here, we take an example for the English and Russian alphabets, but you can do it for any other alphabet. The only condition is that its characters must be present in the NCHAR types.

We need to create a function that accepts the line, replaces every character with a pseudorandom character, and then puts the result together and returns it.

Read More

Synthetic Data Generation. Part 1: Data Copying

Total: 2 Average: 3.5

Introduction

Sooner or later, any information system gets a database, often – more than one. With time, that database gathers very much data, from several GBs to dozens of TBs. To understand how the functionals will perform with the data volumes increasing, we need to generate the data to fill that database.

All scripts presented and implemented will execute on the JobEmplDB database of a recruiting service. The database realization is available here

Read More

Best SQL Tools for Database Developers and Administrators

Total: 21 Average: 3.8

For a SQL database developer, it is essential to have the necessary SQL tools in hand, which enable to reduce the development time of given functionality. The same is true for the tester. For a database administrator, it is also important to have monitoring tools.

In this article, we are going to look at some MS SQL Server development and monitoring tools, such as:

Read More

Transferring Jobs and Schedules between Instances using T-SQL

Total: 5 Average: 4.4

Introduction

Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, and these cases are not uncommon. Also, this wouldn’t work if the recipient MS SQL Server instance is of a newer version than the one you’re transferring the jobs from. So, how can you transfer the Agent jobs without restoring the msdb database?

We will look at an example of a T-SQL script which copies the Agent jobs from one instance of MS SQL Server to another. This solution was tested by transferring jobs from MS SQL Server 2012-2016 to MS SQL Server 2017.

Read More

Implementing Automated Database Backup and Restore with Default Means

Total: 9 Average: 4

Introduction

You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means.

This example will cover a number of approaches – from checking the database’s integrity before backing it up to restoring the database from a previously created backup copy.

Read More

Automatic Deletion of Forgotten Transactions in MS SQL Server

Total: 7 Average: 4.4

Introduction

It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).

In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.

Read More

Implementing Failover in MS SQL Server 2017 Standard

Total: 4 Average: 4.8

Introduction

Often, we need to ensure fault tolerance of MS SQL Server DBMS, especially, when there is no Enterprise edition, but only the Standard one.

We would like to note that we are not going to examine the Express edition because there are significant restrictions to this instance. Sure, we can bypass some of them. For example, to resolve the issue with the database size of 10 GB, we can split a large database into smaller ones. To do this, we can create a new database based on a certain property, and combine the selections from the same tables of different databases in the views in the principal database. However, fault tolerance in the Express edition will be performed either by a system administrator or by using your own or third-party software.

In this article, we are going to explore all existing standard fault-tolerance technologies for MS SQL Server 2017 and an example of implementing the most suitable unified standard of fault-tolerance in the Standard edition.

Read More

Implementing a Common MS SQL Server Performance Indicator

Total: 3 Average: 4.7

Introduction

There is often a need to create a performance indicator that would show database activity related to the previous period or specific day. In the article titled “Implementing SQL Server Performance Indicator for Queries, Stored Procedures, and Triggers”, we provided an example of implementing this indicator.

In this article, we are going to describe another simple way to track how and how long the query execution takes, as well as how to retrieve execution plans for each time point. 

This method is especially useful in the cases when you need to generate daily reports, so you can not only automate the method but also add it to the report with minimum technical details.

In this article, we will explore an example of implementing this common performance indicator where Total Elapsed Time will serve as a metric.

Read More