Database Normalization: A Primer

The Relational Model of data management was first developed by Dr. Edgar F. Codd in 1969. Modern relational database management systems (RDBMSes) are aligned with the paradigm. The key structure identified with RDBMS is the logical structure called a “table”. Tables are primarily composed of rows and columns (also called records and attributes or tuples and fields). In a strict mathematical sense, the term table is actually referred to as a relation and accounts for the term “Relational Model”. In mathematics, a relation is a representation of a set.

The expression attribute gives a good description of the purpose of a column – it characterizes the set of rows associated with it. Each column must be of a particular data type and each row must have some unique identifying characteristics called “keys”. Data change is typically more efficient when done using the relational model while data retrieval may be faster with the older Hierarchical Model which has been redefined in model NoSQL systems. Read More

Update SQL Server statistics using a database maintenance plan

Database backups, integrity checks, and performance optimizations are core regular tasks of DBAs. The client data is very important for a DBA to manage the database backup and make sure the integrity of the backups. So if something goes wrong with a production database, it can be recovered with minimum downtime. The database integrity checks are also important because, in the case of database corruption, it can be corrected with minimum downtime and data loss. Managing database performance is also important. Managing database performance is a combination of multiple tasks. Read More

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

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

DBCC SHRINKFILE Overview

Running DBCC Shrink commands is quite a controversial issue across the SQL Server community. In this article, we will review details about this command and provide a brief overview of its use and also warn you about the risks of running this command. As DBAs, a number of databases were handed over to from other teams or vendors, and it is not always we get to manage the databases which we created. As DBAs, whenever we are involved in migrations or new projects, we need to ensure that we carefully plan a smooth transition of the database to production and regular use. It is at this stage that we need to factor in the size of the database. Can you imagine, you set up a database application without considering the growth forecast for the first year or so. How about you create a SQL Server database with size so small that it needs to grow every other day raising capacity disk alerts in the middle of the night? It may sound silly, but in reality, the truth is this happens, and this sometimes may not be in your control.

Read More

DBCC CheckDB Overview

Introduction

Regular database maintenance is an important part of a Database Administrator’s job which helps to ensure that critically important systems are running as per normal. One of the easiest ways to accomplish this will be to automate tasks related to DBCC CheckDB. No matter what version of SQL Server you are running, there will never be a database that requires no maintenance. You will have to plan the maintenance to occur regularly so that you can cover your back especially at the time of a real disaster scenario.

Read More

Running SQL Database Maintenance Tasks Using SQLCMD

This article is about developing an advanced understanding of the Sqlcmd utility which lets you run T-SQL commands directly from the command prompt without needing SSMS (SQL Server Management Studio).

The article also highlights the importance of using Sqlcmd to perform some advanced-level database tasks that would otherwise require additional steps, e.g. getting connected to the database via 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 great time saver for Database developers and DBAs since they can run the required SQL scripts right from the command line. Read More

Transferring Jobs and Schedules between Instances using T-SQL

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