1. SQL Server: Instance vs Database

SQL Server: An instance is the running database server (a service). Each instance can host multiple databases.
A database inside SQL Server contains its own data and log files.

Oracle: Typical Oracle deployment ties a single database to an instance (SGA + background processes). With Multitenant you can have many PDBs inside a CDB.

2. SQL Server Editions (Quick Comparison)

Choose edition based on features and budget. As an Oracle DBA, map them roughly like this:

3. System Databases Explained

SQL Server automatically creates a set of system databases that store instance-level metadata and runtime state.

Tip: Monitor tempdb closely — it’s a common bottleneck for SQL Server workloads.

4. SQL Server Files: MDF, LDF, NDF

SQL Server separates data and log storage:

• MDF — primary data file
• NDF — secondary data file(s) — Optional
• LDF — transaction log file

Oracle maps these to datafiles (for data) and redo logs (for transaction logging).

-- Example: Add a data file in SQL Server
ALTER DATABASE MyDB
ADD FILE (NAME = MyDB_Data2, FILENAME = 'D:\\MSSQL\\DATA\\MyDB_Data2.ndf', SIZE = 512MB);

-- Example: Add a log file
ALTER DATABASE MyDB
ADD LOG FILE (NAME = MyDB_Log2, FILENAME = 'E:\\MSSQL\\LOG\\MyDB_Log2.ldf', SIZE = 256MB);

5. Authentication: Windows vs SQL Logins

SQL Server supports two primary authentication modes:

1. Windows Authentication — Uses Active Directory; preferred for security.
2. SQL Server Authentication — Username/password stored in SQL Server (use strong passwords).

Oracle typically uses database accounts or external authentication (LDAP/OS), but SQL Server’s tight AD integration is a major difference.

6. Indexes in SQL Server (vs Oracle)

Common SQL Server index types:

• Clustered Index — The table rows are stored in index order (only one per table). Similar to Oracle’s Index-Organized Table (IOT).
• Non-Clustered Index — Like Oracle B-tree indexes.
• Columnstore Index — Columnar storage for analytics (similar goal to Oracle In-Memory).

Example: Create a non-clustered index

CREATE NONCLUSTERED INDEX IX_MyTable_Col
ON dbo.MyTable (Col);

7. High-level Architecture Differences

Key comparisons:

8. What is SQL Server Agent?

SQL Server Agent is the built-in job scheduler used for:

• Backups
• Maintenance tasks
• Custom automation (scripts, SSIS jobs)

Oracle equivalent: DBMS_SCHEDULER and external cron/RMAN scripts.

-- Example: Create a simple SQL Agent job (T-SQL to create job requires msdb context and stored procedures)
-- Use SQL Server Management Studio UI for easiest job creation.

9. Backup Types: Full, Differential, Log

SQL Server supports:

• Full backups
• Differential backups (changes since last full)
• Transaction log backups (for point-in-time recovery)

These map to Oracle RMAN concepts—use log backups (archive) for point-in-time restores.

-- Example: Take a full backup to disk
BACKUP DATABASE MyDB TO DISK = 'E:\\backups\\MyDB_full.bak' WITH INIT;

-- Transaction log backup
BACKUP LOG MyDB TO DISK = 'E:\\backups\\MyDB_log.trn';

10. SQL Server DBA Daily Checklist

1. Check SQL Agent job failures (msdb.dbo.sysjobhistory)
2. Verify backups completed and test restore regularly
3. Monitor disk space on data and log drives
4. Check errorlog for critical errors (sp_readerrorlog)
5. Look for blocking sessions and long-running queries
6. Monitor CPU, memory, and I/O waits

Quick query to find top CPU consuming queries:

SELECT TOP 10 qs.total_elapsed_time/qs.execution_count AS avg_elapsed_ms,
       qs.execution_count, SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
       ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_ms DESC;