DBA Hub

📋Steps in this guide1/12

1. SQL Server: Instance vs Database

SQL Server Basics Concise comparisons and practical snippets so you can learn from Oracle to SQL Server with confidence. Contents: 1. Instance vs Database 2. Editions 3. System Databases 4. Files (MDF, LDF, NDF) 5. Authentication 6. Indexes 7. Architecture 8. SQL Server Agent 9. Backup Types 10. Daily Checklist 1. SQL Server: Instance vs … Continue reading Sql Server Basics For Oracle Dbas →

sqlserver configurationintermediate
by MSSQL
24 views
1

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. SQL Server - Many databases per instance - Service-based architecture (MSSQLSERVER or named instance) - Files: MDF (data), LDF (log), NDF (secondary) Oracle - Traditionally 1 DB per instance (CDB/PDB changes this model) - Memory structures (SGA/PGA) + background processes (DBWR, LGWR) - Files: Datafiles, Redo logs, Control files
2

SQL Server

- Many databases per instance - Service-based architecture (MSSQLSERVER or named instance) - Files: MDF (data), LDF (log), NDF (secondary)
3

Oracle

- Traditionally 1 DB per instance (CDB/PDB changes this model) - Memory structures (SGA/PGA) + background processes (DBWR, LGWR) - Files: Datafiles, Redo logs, Control files
4

2. SQL Server Editions (Quick Comparison)

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

3. System Databases Explained

SQL Server automatically creates a set of system databases that store instance-level metadata and runtime state. Tip: Monitor closely — it’s a common bottleneck for SQL Server workloads.
6

4. SQL Server Files: MDF, LDF, NDF

SQL Server separates data and log storage: - — primary data file - — secondary data file(s) — Optional - — transaction log file Oracle maps these to datafiles (for data) and redo logs (for transaction logging).

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
-- 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);
7

5. Authentication: Windows vs SQL Logins

SQL Server supports two primary authentication modes: - Windows Authentication — Uses Active Directory; preferred for security. - 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.
8

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

Code/Command (click line numbers to comment):

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

7. High-level Architecture Differences

Key comparisons:
10

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: and external cron/RMAN scripts.

Code/Command (click line numbers to comment):

1
2
-- 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.
11

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
-- 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';
12

10. SQL Server DBA Daily Checklist

- Check SQL Agent job failures ( ) - Verify backups completed and test restore regularly - Monitor disk space on data and log drives - Check errorlog for critical errors ( ) - Look for blocking sessions and long-running queries - Monitor CPU, memory, and I/O waits Quick query to find top CPU consuming queries:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!