Managing Multiple Database Instances in SQL Server: Registered Servers vs. Central Management Servers

When working with multiple database instances in SQL Server, managing registered servers or a central management server can greatly assist with day-to-day operations. Here are the three key differences between registered servers and central management servers:

Registered Servers:

1. Connection data storage: Connection data is stored locally in an XML file, with passwords being encrypted.
2. Authentication options: Registered servers can be used with both SQL Server Authentication and Windows Authentication.
3. Applicability: Registered servers can be utilized for the database engine, SSAS (SQL Server Analysis Services), SSIS (SQL Server Integration Services), and SSRS (SQL Server Reporting Services).

Central Management Servers:

1. Connection data storage: Connection data is stored in a central SQL Server, specifically in the msdb database.
2. Authentication options: Central management servers can only be used with Windows Authentication.
3. Applicability: Central management servers are primarily used for the database engine.

It’s worth noting that the central management server (CMS) serves as a subgroup within registered servers, as indicated in the provided example. The query shared allows you to retrieve server details from the msdb database when querying a CMS instance.

dvgfv tb.

1. Script to Retrieve Registered Server Details:
This script retrieves information about registered servers, including the parent group, group name, display name, server name, and description.

“`sql
SELECT
CASE
WHEN parent_id = 0 THEN ‘NONE’
ELSE (SELECT name FROM msdb.dbo.sysmanagement_shared_server_groups WHERE server_group_id = Grps.parent_id)
END AS [Parent Group],
Grps.name AS [Group Name],
Srv.name AS [Display Name],
Srv.server_name AS [Server Name],
Srv.[description]
FROM msdb.dbo.sysmanagement_shared_registered_servers AS Srv
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_server_groups AS Grps ON Srv.server_group_id = Grps.server_group_id;
“`

2. Script to Add a Registered Server:
This script demonstrates how to add a new registered server, specifying the server name, connection details, and authentication method.

“`sql
EXEC msdb.dbo.sp_add_server_internal
@server_name = ‘MyRegisteredServer’,
@server_type = ‘SQL Server’,
@server_local = ‘False’,
@is_registered = ‘True’,
@use_local = ‘False’,
@parent_id = 1, — ID of the server group
@srvproduct = ‘SQL Server’,
@providername = ‘SQLNCLI11’,
@datasource = ‘MyServerInstance’,
@location = ‘MyServerLocation’,
@providerstring = ‘SQLNCLI11:Server=MyServerInstance;Integrated Security=SSPI’,
@catalog = ‘MyDatabase’;
“`

3. Script to Create a Central Management Server:
This script demonstrates how to create a central management server (CMS), specifying the server name, connection details, and authentication method.

“`sql
EXEC msdb.dbo.sp_sysmanagement_create_shared_server_internal
@server_name = ‘MyCentralManagementServer’,
@server_type = ‘SQL Server’,
@is_registered = ‘True’,
@use_local = ‘False’,
@providername = ‘SQLNCLI11’,
@datasource = ‘MyServerInstance’,
@location = ‘MyServerLocation’,
@providerstring = ‘SQLNCLI11:Server=MyServerInstance;Integrated Security=SSPI’,
@catalog = ‘msdb’;
“`

These scripts provide a starting point for managing registered servers and central management servers in SQL Server. Remember to adjust the server names, connection details, and other parameters according to your specific environment.

Feel free to explore these features and functionalities further. Happy exploring

Understanding SQL Server Database Files and Virtual Log Files (VLFs): A Comprehensive Guide

SQL Server databases consist of two types of files: database files and log files. Database files can have multiple instances, denoted by extensions such as .mdf and .ndf, while log files use the .ldf extension. At any given time, a database uses only one log file, although it is possible to have multiple log files, which is generally not recommended. The log file maintains a record of all transactions that occur in a SQL Server database.

What are Virtual Log Files (VLFs)?

Virtual Log Files (VLFs) are the building blocks of a SQL Server transaction log file. The number of VLFs is not fixed or limited per transaction log file. When a transaction log file is created or extended, SQL Server dynamically determines the size of each VLF. The aim is to keep the number of VLFs in the transaction log file small because SQL Server handles a smaller number of files more efficiently. The size or number of VLFs cannot be controlled or set by a database administrator. Having too many or too few VLFs can negatively impact performance.

The number of VLFs can increase during an auto-grow event and decrease when shrinking the SQL Server transaction log file. The concept of VLFs allows SQL Server to manage various operations on the log file, including checkpoints and the different phases of recovery like analysis, undo, and redo.

How to Check VLF File Details?

You can check the details of VLF files using the undocumented command “dbcc loginfo.” Here is an example:

“`sql
USE abhi_test
GO
DBCC LOGINFO
“`

Results:
– The Fieldid is the same for all entries, indicating they are from the same transaction log file.
– The Filesize and Startoffset are measured in bytes.
– A Status of 0 means the VLF is unused and reusable, while a status of 2 means the VLF file is active.
– The CreateLSN indicates when the VLF file was created. A value of 0 means the VLF was created when the original log file was created.

It is important to note that in the simple recovery model, the CHECKPOINT operation only truncates the transaction log, marking the VLFs for reuse. In the full recovery model, you need to take a log backup.

Having too many VLFs can lead to slower restores, updates, inserts, increased locking, and sluggishness in mirroring or high availability (HA) setups.

On the other hand, having too few VLFs can cause slowness when taking log backups.

There is no definitive right or wrong number of VLFs per database, but it is generally recommended to keep the count below 100.

Happy exploring!

Exploring the Components of SQL Server: Network Interface Layer, Database Engine, Storage Engine, and SQLOS

1. The Network Interface Layer: This component handles the unwrapping of the TDS packet received from the client’s end.
2. The Database Engine: Responsible for executing database queries, the query processor processes and executes the queries.
3. Storage Engine: This component stores the database using a set of OS system files, including objects, indexes, and tables. Each file has a logical name used in T-SQL statements and a physical name seen in the OS file structure. The storage engine can be further divided into Pages and Extents, which will be discussed later.
4. SQLOS: The SQLOS layer manages operating system resources, including memory/buffer management, exception handling, extended events, resource governance, and I/O operations.