SQL Vulnerability Assessment

SQL Vulnerability Assessment is a feature available in the latest versions of SQL Server Management Studio (SSMS). This feature is very easy to use and it will show you all the security vulnerabilities and deviations in your SQL database. This is something you can run on your most critical databases to ensure you’re properly following strict security practices and that your client’s databases are in safe hands. In this article, we will describe the process of running these scans against your databases. With the amount of data growing with each year, database security is an important aspect every DBA needs to take care of. The consequences of data breaches are severe, so they may affect your future as a DBA and severely damage your firm’s reputation. Read More

Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect

Introduction

This tutorial includes information about SQL (DDL, DML) that I have gathered during my professional life. This is the minimum you need to know while working with databases. If there is a need to use complex SQL constructions, then usually I surf the MSDN library, which can be easily found on the internet. To my mind, it is very difficult to keep everything in your head and, by the way, there is no need for this. I recommend that you should know all the main constructions used in most relational databases such as Oracle, MySQL, and Firebird. Still, they may differ in data types. For example, to create objects (tables, constraints, indexes, etc.), you may simply use integrated development environment (IDE) to work with databases and there is no need in studying visual tools for a particular database type (MS SQL, Oracle, MySQL, Firebird, etc.). This is convenient because you can see the whole text, and you do not need to look through numerous tabs to create, for example, an index or a constraint. If you are constantly working with databases, creating, modifying, and especially rebuilding an object using scripts is much faster than in a visual mode. Besides, in my opinion, in the script mode (with due precision), it is easier to specify and control rules for naming objects. In addition, it is convenient to use scripts when you need to transfer database changes from a test database to a production database. Read More

SQL Data Discovery and Classification

With the recent data protection laws and the implementation of GDPR last year, it is imperative for us to know the type of data our databases are storing. This will help us classify data based on the regulations and help us meet the data privacy standards. It is quite difficult for DBAs to know the type of data within a database. However, there is a new built-in tool within SQL Server Management Studio (SSMS) that can help us to easily discover the data within the database. With this tool, we will be able to classify the data and work on reporting any sensitive data.

Read More

SQL Server Database Tables Export and Import Methods

When working as a SQL Server database administrator or developer, you cannot live in your isolated SQL Server world without communicating with other data sources. For example, there is rarely a day when you won’t be requested to import data from an Excel, Access or CSV file into your SQL Server table. Or, conversely, export one of your SQL Server database tables to an external file in order for this table to be used in another database engine or to be analyzed externally by the corresponding team.

SQL Server provides us with a number of methods that can be used to export an existing SQL Server database table to an Excel or text file, and import data from an external data source to a new or existing SQL Server database table. In this article, we will gradually cover all these methods.

Read More

Basics of Running T-SQL Statements from Command Line using SQLCMD

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

Read More

Creating and Deploying Multiple Versions of Database through Schema Snapshots

Overview

This article talks about using database schema snapshots to maintain different versions of a database to be deployed to different environments.

Database schema snapshots are point-in-time copies of the current state of the database which are normally used to reconcile the differences when deploying changes from one environment to another environment.

This article will be focused on a particular scenario where database schema snapshots are more than just point-in-time copies of the database rather they are used to create fresh versions of specific environments.

Read More

How to create Snapshot Replication

The concept of snapshot replication is simple. It generates and distributes the snapshot of schema and data of articles, appeared at a specific time and updates the changes on subscriptions. When we create snapshot replication, SQL Server creates a Snapshot Agent Job which generates a snapshot of publication database objects. Snapshot is stored on either network location or on the hard drive. You can define the interval to generate the snapshot using the SQL Job schedule. Read More

How to create Transactional Replication

In this article, I am going to demonstrate how to create transactional replication.

Transactional replication generated a snapshot of publication DB objects and data within it. After the snapshot is generated, all the data changes and schema changes occurred on the publisher database are delivered to subscriber databases. In transactional replication, data and schema changes are almost real-time, hence transactional replication can be used to offloading reports and sometimes can be used as DR Site.

Read More

Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure

This article provides a walkthrough of database unit testing a stored procedure which contains a utility procedure within it.

In this article, I am going to discuss a database unit testing scenario when a main stored procedure depends on a utility procedure and the main procedure needs to be unit tested in order to make sure that the requirements are met. The key is to ensure that a unit test can only be written for a single unit of code which means we need one unit test for the main procedure and another unit test for the utility procedure. Read More