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

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

Restoring the SQL Server Master Database

Total: 2 Average: 5

INTRODUCTION

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.

Read More

SQL Server System Databases – Basic Concepts

Total: 2 Average: 5

Introduction

The SQL Server design implies a one-to-many mapping between the database engine (instance) and the databases hosted on the instance. It means that you can deploy several databases on one instance of the SQL server. According to the Microsoft documentation, you can have up to 32767 databases on a single instance of SQL Server. Of course, there will be limitations, like the resources on the server, managing concurrency on TempDB, network traffic, etc.

Databases deployed on a SQL Server instance can either be System Databases or User Databases. System Databases come installed with the instance. In this article, we will discuss the purpose of each System database. Also, we’ll clarify what you need to care for when managing system databases on SQL Server.

Read More

Using SQL Server 2016 Upgrade Advisor

Total: 4 Average: 3.5

INTRODUCTION

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 which 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.

Read More

SQL Server Lock Escalation

Total: 1 Average: 5

Introduction

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.

Read More