The Stored Procedure to Get the Instance Settings

SQL Server instances house databases containing the data for the backend stack of a business model, or configuration data for particular applications. Regardless of the use case, an instance has a set of values/settings that should be tuned to follow best practices.

The purpose of the Stored Procedure that I’m showcasing in this article is to present the DBA a set of important settings/values that shouldn’t be overlooked. Moreover, I will share a cool feature that help DBAs keep control of any particular setting/value that has been recently changed/modified.

CodingSight - The Stored Procedure to Get the Instance Settings/Values
Read More

Restoring the SQL Server Master Database


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


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