Keep your Servers Close and Your Database Closer

Having the client close to the server in geographical proximity is important. However, this is not as important of having the database closer to the server.

#database

What is GEEK

Buddha Community

Keep your Servers Close and Your Database Closer
Ruth  Nabimanya

Ruth Nabimanya

1620633584

System Databases in SQL Server

Introduction

In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.

System Database

Fig. 1 System Databases

There are five system databases, these databases are created while installing SQL Server.

  • Master
  • Model
  • MSDB
  • Tempdb
  • Resource
Master
  • This database contains all the System level Information in SQL Server. The Information in form of Meta data.
  • Because of this master database, we are able to access the SQL Server (On premise SQL Server)
Model
  • This database is used as a template for new databases.
  • Whenever a new database is created, initially a copy of model database is what created as new database.
MSDB
  • This database is where a service called SQL Server Agent stores its data.
  • SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.
TempDB
  • The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
  • User can create their own version of temporary tables and those are stored in Tempdb.
  • But this database is destroyed and recreated every time when we restart the instance of SQL Server.
Resource
  • The resource database is a hidden, read only database that holds the definitions of all system objects.
  • When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.

#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database

Loma  Baumbach

Loma Baumbach

1598296680

SQL Server System Databases - Basic Concepts

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.

Overview of System Databases

System databases are a part of many processes taking place when you install an instance of SQL Server. By default, these databases are created in the following paths:

%programfiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

and

%programfiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log

The path can be different. During the installation of SQL Server, you can specify the location of the system database files.

To list all the system databases in an instance, you can invoke the code in Listing 1. Listing 2 can be used to determine the location of the datafiles associated with the system databases. Note that in both scripts, we use a filter returning databases with database_id of 5 or higher than 5.

The essential “visible” system databases have database_ids 1,2,3,4 – they refer to master, tempdb, model, and msdb, respectively. There is also an “invisible” database called the resource databases and other system databases created when you enable features like replication.

-- Listing 1: System Databases
select 
name
,database_id
,create_date
,state_desc
,recovery_model_desc
,log_reuse_wait_desc
,physical_database_name
,is_db_chaining_on
,is_broker_enabled
,is_mixed_page_allocation_on
from sys.databases 
where database_id<5;

-- Listing 2: System Database Files
select 
name
,database_id
,DB_NAME(database_id)
,name
,physical_name
,type_desc
from sys.master_files 
where database_id<5;

Figure 1: System Databases

The master Database

The master database is the first database open on the start of SQL Server, containing the following data:

  • The records of the structure/configuration of the instance and all other databases.
  • The most dynamic management views that are necessary for monitoring the instance.

Thus, it has the information necessary for opening all other databases. That’s why it has to be first to open. The question is how to do it.

The SQL Server startup parameters contain two entries, which define the locations of the master database data and log files. The default startup parameters include only three lines – the third one is the error log file location. When SQL Server starts up, it must be able to write to that error log file.

The master database opens first. The information stored in the master database, including the configurations defined using sp_configure, applies to open other databases and complete the instance startup process.

Figure 2: SQL Server Configuration Manager

Figure 3: SQL Server Startup Parameters

There are several ways to learn about useful SQL Server system objects, like Dynamic Management Views and Functions.

For instance, expand the views or programmability nodes for the master database on object explorer. There, review these objects’ names and get more details from Books Online.

You can also migrate logins from one instance to another. For that, restore a backup of the master database to the destination instance. We’ll describe the specific technique in a separate article.

#sql server #sql server #sql server instance #sql server system databases #tutorial #database

Ruth  Nabimanya

Ruth Nabimanya

1621850444

List of Available Database for Current User In SQL Server

Introduction

When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server

Get the list of database
Conclusion

#sql server #available databases for current user #check database has access #list of available database #sql #sql query #sql server database #sql tips #sql tips and tricks #tips

Brain  Crist

Brain Crist

1600347600

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

A glimpse of previous articles
Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2
  • We learned to create a database and maintain it using SQL statements.
  • Best practice methods were also mentioned.

#sql server #benefits of schemas #create schema in sql #database schemas #how to create schema in sql server #schemas #schemas in sql server #sql server schemas #what is schema in sql server

Edison  Stark

Edison Stark

1598994060

Restoring the SQL Server Master Database

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.

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.

CHECK THE STATUS OF APPLICATION SERVICES

Restoring the master database to another instance will involve starting the instance in the single user mode. Thus, it is essential to make sure that the active session is the only one controlling the instance.

For that, stop all application services from accessing the instance, especially if such applications have privileged access. Problems can occur if you start the instance in the single-user mode while the application has already established a session. In this case, you will not be able to proceed with an interactive session from SQL Server Management Studio.

STOP SQL SERVER SERVICES

Stop all SQL Server Services with the SQL Server Configuration Manager: right-click each service and select Stop from the context menu (see fig. 2).

Figure 1. Stopping SQL Services

START SQL SERVER IN THE SINGLE USER MODE

To restore the master database, you need the SQL Server instance in the single user mode. Take the following steps:

  1. Open CMD Prompt
  2. Navigate to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
  3. Issue sqlservr –m OR NET start MSSQLServer /c /m /T3604

Figure 2. Starting SQL Server in Single user Mode

OR

Figure 3. Starting SQL Server in Single user Mode

Note that step 2 refers to the Binn directory location. It can be different in your installation.

You can find the necessary directory by checking the Service tab of SQL Server service properties in SQL Server Configuration Manager (See Figure 4):

#programming #sql server #how to #sql server #sql server master database #database