Configure SQL Jobs in SQL Server using T-SQL

Total: 1 Average: 5

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.

Read More

Configure Database Mirroring in SQL Server

Total: 1 Average: 5

Database mirroring is a SQL Server high availability solution provided by Microsoft, with the following components.

  1. Principal database server: a source database that you configure for the mirroring.   
  2. Mirror database server: a destination database that you must restore with NORECOVERY. Ideally, a mirrored database should be on a separate server.
  3. Witness Server: an optional component that you can set up to configure the mirror with automatic failover.
  4. Endpoint: the communication medium used by the principal and mirror server. The mirror listens on port number 5022; however, you can change it during the configuration.
Read More

Deploying the first SQL to Azure Migration project with Azure Database Migration Service

Total: 2 Average: 5

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.

Read More

Deploying a Database on Azure SQL

Total: 3 Average: 5

Introduction

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.  Further, I’ll explain these terms.

Read More

How to use Stopwords and Stoplist to improve SQL Server Full-Text Search (FTS)

Total: 6 Average: 5

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.

Read More

10 Tips & Tricks To Work With SQL Server Management Studio

Total: 6 Average: 4.8

SSMS provides access for managing and solving queries in SQL. However, besides its primary and most demanded options, it includes lots of tips and tricks that you might not be aware of. They help enhance the productivity of your work, though.

In this article, we’ll describe those tips you can use in SSMS to increase your efficiency and reduce the time spent on routine.

Read More

Deploying a Certificate for Encrypted Connection SQL Server

Total: 3 Average: 5

Introduction

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.

Read More

How to Configure Database Mail in SQL Server

Total: 3 Average: 5

INTRODUCTION

Notifications are usual in most modern systems. SQL Server implemented means of sending notifications by email early with the feature called SQL Mail. In SQL Server 2005, Database Mail was released, and we still have Database Mail working in current versions of SQL Server. Given this background, we can say that Database Mail is a very mature feature in SQL Server.  

I faced some use cases related to the Database Mail deployment in my experience.

Read More

How to Make Sense of SQL Server Geography Data Type

Total: 3 Average: 5

SQL Server Geography data type is unlike other types simply because you don’t use it often. It’s not like varcharintfloat, or DateTime present in every table, at least in one type.

Do we need the SQL Server Geography data type? To make sense of something new, we need to answer three fundamental questions:

What problems does it solve? The answer lies in use cases.
What are the things involved in studying it? You will learn a few basic definitions and acronyms. I collected only the important stuff, so it won’t bore you.
Are there examples to further explain how it works and is it easy to implement? Our kind lives with code. We eat and sleep with it. So, we’re going to have sample codes in this post.

Read More