Image for post
Image for post

SQL Server Agent is a component used for the database tasks automation. For instance, we need to perform Index maintenance on Production servers during the non-business hours only. So, we create a SQL Server job of running index maintenance and schedule it for “off” hours.

When we install SQL Server, the SQL Server Agent service is disabled. First, we enable it and start it manually. Then, we configure the SQL Server job, using SQL Server Management Studio and the system stored procedures of the MSDB database.

This article explains how to create a SQL Server Job using the system stored procedures of the MSDB database. …


Image for post
Image for post

Azure Database Migration Service (ADMS) supports Schema and Data migration from different database technologies to the Azure platform. This article will shed some light on validations, limits, and use cases of migration tasks for SQL Server (RDS & On-Premises) to Azure cloud database with ADMS.

Before creating a first migration project in ADMS, let’s get familiar with the basic requirements of the Azure Database Migration Service.

We can create a migration project in the ADMS with migration parameters chosen only. While adding the project, it asks to select the migration activity type.

There are three primary activity types available on the Azure…


Image for post
Image for post

SQL Server is designed to allow multiple databases on a single instance. With this model, it is possible to have databases sitting on an instance that are not the same version as the instance itself. You can think of this as running the database in the “backward compatibility mode.” To break it down further, we are saying that you can have a 2008 database deployed on an SQL Server 2016 instance. In such a scenario, the database is, for instance, allowed to use certain constructs that belong to a previous version of SQL Server.

There are cases where you would like to upgrade all databases in your instance to a more recent version for security reasons or to align with your organization’s policies. When doing this it may not be safe to assume that such a database will automatically work when upgraded. Using Upgrade Advisor, you can determine whether your existing databases are suitable for an upgrade to SQL Server 2016 or any other higher version. …


Image for post
Image for post

Microsoft Azure platform provides an Azure Database Migration Service (ADMS) — a convenient solution for the Azure Cloud-based Databases migration tasks in a straight forward way. In this article, we elaborate on the AMDS essential work and integrated processes.

We put in the focus issues related to the migration of object resources on Cloud to Cloud and on-premises database server to cloud. Also, we’ll deal with the cross-database migration compatibility between different database technologies.

ADMS supports several Automation migration tasks for data migration on Azure platform from SQL Server, MySQL, AWS RDS for MySQL, PostgreSQL, AWS RDS for PostgreSQL, Oracle, AWS RDS for SQL Server, and NoSQL Server (MongoDB). …


Image for post
Image for post

The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database. It also contains most of the dynamic management views that are necessary to monitor the instance.

The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.

It is crucial to back up the master database daily. Equally important is to know how to restore the master database to the instance. The most frequent cases are the database crash or the necessity to restore the master database to another instance when no longer use the source instance. In this article, we will examine the specific case of moving the master database to another instance. …


Image for post
Image for post

Azure SQL Database is a Platform as a Service (PaaS) offered by Microsoft Azure. Hence, you need the Azure subscription to use this SQL Database. Azure offers new subscribers a free subscription and $200 worth of resources for 30 days. Recently I revived my relationship with MS Azure and explored the platform a little bit, using the Warner Chaves’ PluralSight Azure SQL Database for SQL Server DBAs training for the start.

In this article, I will explore the steps required to create an SQL Server in Azure and deploy a small database. Here I am using a free tier subscription, and the resources are on the basic level. …


Image for post
Image for post

In a previous article, we highlighted that the msdb database stores practically all objects related to automation. In this article, we’ll review the case of moving jobs and objects between the SQL Server instances.

Let’s start with the list of objects stored in msdb on this instance of SQL Server.

We have several jobs created with a maintenance plan (see the article Creating Maintenance Plans in SQL Server). We also have two alerts and one operator. Msdb stores alerts and operators as well (see Figure 1). …


Image for post
Image for post

The current article is about Stopwords and Stoplist usage. The aim is to make Full-Text Search more efficient in terms of storage and performance. Additionally, the readers of this article will get a walkthrough of implementing Full-Text Search with Stopwords and Stoplist.

The article also highlights the importance of Stopwords and Stoplist(s) to keep the Full-Text Search precise and avoid noise words.

Understanding Stopwords and Stoplist

First, let’s clarify the essence of Stopwords and Stoplist. Then we’ll proceed to use them to improve Full-Text Search.

A Stoplist

A stoplist, as the name implies, is a list of stopwords. When associated with Full-Text Search, the Stoplist can filter out meaningless words or terms, thus improving search results. …


Image for post
Image for post

Relational databases follow the ACID properties in how they implement transactions — Atomicity, Consistency, Isolation, and Durability. Isolation is necessary to ensure that multiple transactions can’t cause changes to data and leave the eventual results inconsistent. To guarantee that the operations remain isolated, SQL Server applies Locking mechanisms.

Lock Modes and Hierarchy

SQL Server’s mechanism for concurrency control is involved. To optimize performance in terms of lock waits, deadlocks, and the like, you have to make a decision based on the specific scenario.

In SQL Server, locks can be held in various ways, and at several levels of granularity. …


Image for post
Image for post

Last year we got a requirement to ensure encrypted connections to our SQL Server instances. Before, we did not think it was necessary — all our instances were accessed by application services internally. Still, secure connections protect the instance and clients from attacks as man-in-the middles, so we did it.

Connection encryption differs from Transparent Data Encryption, but you need a certificate in both cases. In this article, we describe the procedure of setting up an encrypted connection for SQL Server instances.

Setting Up the Certificate Snap-in in MMC

A certificate is a digitally signed document containing public and private keys that encrypt connections. The public and private keys are a “Key Pair” — the public key encrypts the data, and it is only the private key that can decrypt them. …

About

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store