Modern SQL Server Administration: Evolving from Legacy to Latest Tools and Practices

For many database administrators, the landscape of SQL Server management has undergone a significant transformation over the past two decades. What began with SQL Server 2000's Enterprise Manager and Query Analyzer evolved through SQL Server 2005's Management Studio, and continues to advance with each new release of SQL Server (2019, 2022) and the advent of Azure SQL Database. While core DBA responsibilities endure, the tools and methodologies have modernized considerably.

This article explores how common DBA tasks are performed in today's SQL Server environments, leveraging modern tools like SQL Server Management Studio (SSMS), Azure Data Studio, and PowerShell, while acknowledging how far we've come from earlier versions.

Evolution of SQL Server Management Tools

The journey from SQL Server 2000's separate Enterprise Manager and Query Analyzer to the unified SQL Server Management Studio (SSMS) was a major leap forward with SQL Server 2005. Today, SSMS remains the primary GUI tool for managing on-premises SQL Server instances and Azure SQL Database. However, the ecosystem has expanded:

SQL Server Management Studio (SSMS): The venerable, feature-rich IDE for database administration, development, and tuning. It continues to be updated regularly.
Azure Data Studio (ADS): A cross-platform tool built on VS Code, offering a lightweight, modern experience for data professionals working with on-premises SQL Server, Azure SQL, PostgreSQL, and more. It excels at notebooks, source control integration, and custom extensions.
PowerShell: Indispensable for automation, scripting, and repeatable tasks, especially with the dbatools module for SQL Server.
Azure Portal: The primary interface for managing Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines.

While direct feature comparisons to SQL Server 2000's "Taskpad" are no longer relevant, understanding how to perform common tasks in modern tools is crucial.

Performing Common DBA Tasks in Modern SQL Server

Database Properties and Overview

In modern SSMS, general database properties, options, user count, creation date, and owner information are all easily accessible by right-clicking a database and selecting Properties. For more granular details or scripting, T-SQL queries are your best friend.

Checking Database Space Allocation

While older versions might have had simple "disk space overview" tabs, today's SSMS provides excellent reports (right-click database > Reports > Standard Reports > Disk Usage). For T-SQL, you can use:

-- Get file sizes and free space per file
SELECT
    name AS FileName,
    physical_name AS FilePath,
    size_on_disk_bytes / (1024 * 1024.0) AS SizeMB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) / (1024 * 1024.0) AS UsedMB,
    (size_on_disk_bytes - CAST(FILEPROPERTY(name, 'SpaceUsed') AS BIGINT)) / (1024 * 1024.0) AS FreeMB
FROM sys.database_files;

-- Get log space usage (per database)
DBCC SQLPERF (LOGSPACE);

For more detailed drive-level free space on the host server, especially for on-premises instances:

EXEC xp_fixeddrives;

Note: xp_fixeddrives is an undocumented extended stored procedure and should be used with caution, but it's widely used for this purpose.

Table Information and Space Usage

SSMS offers "Disk Usage by Top Tables" and "Disk Usage" reports. For quick T-SQL, sp_spaceused remains highly relevant:

EXEC sp_spaceused 'YourTableName'; -- Replace YourTableName

For a comprehensive view of all tables in a database:

SELECT
    t.name AS TableName,
    SUM(s.used_page_count) * 8 / 1024 AS Used_MB,
    SUM(s.reserved_page_count) * 8 / 1024 AS Reserved_MB
FROM sys.tables AS t
JOIN sys.indexes AS i ON t.object_id = i.object_id
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units AS au ON p.partition_id = au.container_id
JOIN sys.dm_db_partition_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id AND p.partition_id = s.partition_id
GROUP BY t.name
ORDER BY SUM(s.used_page_count) DESC;

Scripting Objects (Stored Procedures, Functions, etc.) with Permissions

The "Generate Scripts" wizard in SSMS remains the gold standard for scripting database objects, including their permissions. This feature has evolved and is robust:

Right-click your database → Tasks → Generate Scripts.
Select the specific objects you wish to script.
In the "Set Scripting Options" step, click "Advanced."
Scroll down and set "Script Object-Level Permissions" to True.
Consider other useful options like "Script DROP and CREATE," "Script IF NOT EXISTS," and "Script USE database."
Complete the wizard to generate your script.

For quick ad-hoc scripting of a single object (e.g., a stored procedure) with its definition:

In SSMS, navigate to the object (e.g., Stored Procedures), right-click → Script Stored Procedure as → CREATE To / DROP and CREATE To → New Query Editor Window.

Retrieving Object Creation and Modification Timestamps

While the old sysobjects.crdate is still available for backward compatibility, modern SQL Server uses sys.objects which provides both creation and modification dates. This is the preferred method today:

SELECT
    name AS ObjectName,
    type_desc AS ObjectType,
    create_date AS DateCreated,
    modify_date AS LastModified
FROM sys.objects
WHERE name = 'YourObjectName'; -- Replace YourObjectName

Login and Security Management in Modern SQL Server

Migrating logins has become more streamlined. While the legacy `sp_help_revlogin` (and its variants for specific versions) served its purpose for server-level principals, today's best practices involve:

Database Migrations (Backup/Restore, Always On Availability Groups, Log Shipping): Server logins (SQL Logins and Windows Logins) are automatically transferred with the `master` database backup/restore for a full instance migration. However, when migrating a *single user database* to a *new instance*, you often encounter "orphaned users" (database users without a corresponding server login).
dbatools PowerShell Module: For cross-instance login transfers, the open-source dbatools PowerShell module is an industry standard. It handles SQL logins, Windows logins, server roles, and passwords with robust functions like Copy-DbaLogin.
```
# Example: Copy all logins from SourceServer to DestinationServer
Install-Module dbatools -Force
Copy-DbaLogin -Source SourceServer -Destination DestinationServer
```

T-SQL for Orphaned Users: To fix orphaned users in a migrated database:

-- List orphaned users
EXEC sp_change_users_login 'Report';

-- Fix a specific orphaned user (if SQL login)
EXEC sp_change_users_login 'Auto_Fix', 'YourDatabaseUserName';
-- Or, if the login already exists on the new server but isn't linked:
ALTER USER YourDatabaseUserName WITH LOGIN = YourServerLoginName;

Azure AD Integration: For Azure SQL Database and Managed Instance, the modern approach is to use Azure Active Directory (AAD) authentication, eliminating the need to transfer SQL logins for many scenarios. Users are managed as AAD logins at the server level and AAD users within the database.

Summary

The SQL Server ecosystem has evolved significantly since the early 2000s, bringing powerful new tools and streamlined workflows. While some legacy features have naturally faded, modern SSMS, Azure Data Studio, PowerShell (especially with dbatools), and the Azure Portal provide superior capabilities for managing databases on-premises and in the cloud.

Embracing these contemporary tools and practices ensures that DBAs remain efficient, effective, and ready for the challenges of today's data platforms. The focus has shifted from finding "missing features" to leveraging a richer, more integrated toolkit for comprehensive database management.

What are your go-to modern tools or techniques for SQL Server administration? Share your insights in the comments below!

Search This Blog