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: 1 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

How to Use SQL Server HierarchyID Through Easy Examples

Total: 5 Average: 5

Do you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.

You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.

Read More