1. SQL Server Versions:
- SQL Server 1.0
- SQL Server 4.0
- SQL Server 6.0
- SQL Server 7.0
- SQL Server 8.0 (SQL Server 2000)
- SQL Server 9.0 (SQL Server 2005)
- SQL Server 10.0 (SQL Server 2008)
- SQL Server 10.5 (SQL Server 2008 R2)
- SQL Server 11.0 (SQL Server 2012)
- SQL Server 12.0 (SQL Server 2014)
- SQL Server 13.0 (SQL Server 2016)
2. SQL Server Editions:
- Data Center
- Enterprise
- Enterprise Evaluation
- Standard Edition
- Developer Edition
- BI Edition
- Web Edition
- Workgroup Edition
- Express Edition
- Mobile Edition
3. SQL Server 2014/ 2016 Pre-requisites:
- WMI: Windows Management Instrumentation – SQL Server monitoring & Management
- msxml: Microsoft XML Parser – Database management & SQL Server connection
- MDAC: Microsoft Data Access Component – Remote access to SQL Server
- Firewall: Secure connection to SQL Server
4. SQL Server Components:
- Database Engine: Store real time data
- SQL Server Analysis Services: Data for analysis & forecast
- SQL Server Integration Services: Data warehouse
- SQL Server Reporting Services: Report data, visualization
- Data Quality Service: Ensure correct/ consistent data store
5. SQL Server Tools:
- SSMS
- DTA
- DMA
- PSDIAG
- SQLCMD
- SQL Server Profiler Tool
- SQL Server Configuration Manager
6. SQL Server Default DB:
- Master: Store server properties
- Model: Template to create new DB
- Tempdb: Used by developers
- MSDB: Used by DBA’s
- Resource DB: Hidden DB, read-only Db, control other DB
7. Collation: A property to specify language and character setting for data storage.
8. Filestream: A property to store big data files and cloud data. Also, used for memory tables.
9. Creating SQL Server Database: Default Name = MSSQLSERVER
– Two files are auto created
A. Datafile (mdf)
B. TLog or Audit Log (ldf)
Additional file (ndf) can be created.
– Datafile contains pages = 8kb size
8 Pages = 1 Extent
Group of Files is called FileGroup
Collection of few consecutive Pages = “Virtual Log Files”
Start location of every log file is called “Mini LSN”
LSN: Log Sequence Number
10. Views: Stores static select queries
- SCHEMABINDING : prevent orphan views
- WITH CHECK OPTION : prevent unwanted inserts
- ENCRYPTION : hide view definition
Eg: create view VW_COURSE with schemabinding, encryption as
select course_id, course_name, course_dur from dbo.course where course_name=’COMPUTER’ with check option
=> Can not drop base table when view created on it with schemabinding
11. Procedures: Stores dynamic, DDL, DML statements, accepts parameters, faster compared view & functions.
Stored procedures are database objects used for storing any type of TSQL statements.
– Procedures are faster compared functions.
– Procedures generates an automated complied plan for future executions.
exec sp_recomplie ‘<procedure name>’ — Procedure will be marked for compilation and actual recomp occurs during next time procedure is executed.
exec sp_help ‘<procedure name>’
exec sp_helptext ‘<objectname>’
12. Triggers: DB objects to store any type of T-SQL statements.
- DML Triggers: Two triggers are auto created 1. Inserted & 2. Deleted also called Memory Tables or Magic Tables.
- DDL Triggers: 1. Database scoped DDL & 2. Server scoped DDL.
Eg: create trigger TRIGGER1 on COURSE for insert as begin insert into STUDENTS select * from inserted END
13. Transactions: Mechanism to execute complete set of operation or nothing, means “atomic execution”
- Auto commit: auto start & auto end
- Explicit Transaction: Manual start & manual end
- Implicit Transaction: Auto start & manual end
– Commit: save stored transaction details from DB log file to DB data file.
– Checkpoint: Internal process to perform actual commit operations. Governed by “Master DB”.
14. ACID Properties:
- Atomicity: Every transaction should be executed
- Consistency: Transaction failure should not impact state of DB.
- Isolation: Some transaction may or may not depend on other.
- Durability: Committed transaction is permanent. No data loss.
15. Locks: When ever we perform any operation “locks” are auto allocated. These locks are used to control concurrency in the database.
– These locks are managed by “Lock Manager”. Instance Lock Manager & OS Lock Manager
– Type of Locks:
- Shared LockSSelect data access
- Update LockUActual data update in table
- Exclusive LockXDML operation on table or view
- Intent LockISRow level lock during data retrieval
- IX Row level lock during data modification
- Metadata LockMDRead metadata from primary file of DB for DDL operation
- Schema LockSCH-SReport metadata in DB and its objects. Eg: SP_HELPDB
- SCH-M Modify metadata in DB and its objects. Eg: ALTER
=> Lockable Resources: DB, Filegroup, File, Page & DB Objects
=> Lock Escalation: (by lock manager)
– If more than 50% of rows are locked, entire page is locked.
– If more than 50% of pages are locked, entire table is locked.
exec sp_lock
select * from sysprocesses where blocked <> 0
16. Backups: A mechanism to create standby copy of the database in a file format. These are called “Backup Files”.
– Level of Backups & Restores:
- Complete DB Level Backup
- Filegroup Level Backup
- File Level Backup
=> How to take T-SQL backups?
- Full: backup database [UNI] to disk=’E:\backup\fulldb.bak’ with format
- Differential: backup database [UNI] to disk=’E:\backup\db_diff.bak’ with format, differential
- Log: backup log [UNI] to disk=’E:\backup\DB_Log.trn’ with format
– Types of Backups:
- Full Backup: All pages (Data Pages, Index & Log Pages)
- Differential Backup: Pages that contain changes since last full backup
- Log Backup: All pages from log file are back up.
- Copy Only Backup: Special type of full backup called isolated backup used for replication & DB Migration.
- Partial Backup: Backup of Read/ Write file group. Do not include Read Only filegroup of the DB.
- Mirrored Backup: Backup taken to multiple location at same time. One location is duplicate of another.
- Split Backup: Backup taken at multiple location at same time. One location is continuous part of another.
- Hot Backup: Backup performed on online DB.
- Cold Backup: Backup performed on off-line DB. Used for migration from one server to another.
17. Restores: A mechanism to use existing backups and recover the database.
– Phases of restore:
- Copy Phase: Content of the backup files are copied to SQL Server.
- Redo Phase: Committed transactions from log file are roll forwarded to data file.
- Undo Phase: Open transactions from the log file are roll back [undo].
– Types of Restore:
- Full database restore
- Filegroup restore
- File Level restore
- Page Level restore
– Restore Options:
- NORECOVERY: DB in “RESTORING STATE”, used in case more backups to restore.
- RECOVERY: DB in “ONLINE STATE”, not ready for further restores.
- STANDBY: DB in Online – READ ONLY state.
– Restores can be performed using:
- User Interface
- T-SQL scripts
=> How to restore remaining part of DB after partial restore completed and DB is online?
- Using “Tail Log Backup”
Eg: backup log [UNI] to disk=’E:\backups\log.trn’ with format, NORECOVERY
=> Verify backup file is valid?
- restore verifyonly from disk=’E:\backup\fulldb.bak’
=> Verify backup file restore information?
- restore filelistonly from disk=’E:\backup\fulldb.bak’
=> Restore using T-SQL: (Full, Diff & Logs backup available)
- restore database [UNI] from disk=’E:\backup\fulldb.bak’ with NORECOVERY
- restore database [UNI] from disk=’E:\backup\db_diff.bak’ with NORECOVERY
- restore log [UNI] from disk=’E:\backup\DB_Log.trn’ with RECOVERY
=> Partial Recovery:
- restore database [UNI] filegroup=’PRIMARY’ from disk=’E:\backup\fulldb.bak’ with NORECOVERY, PARTIAL
- restore database [UNI] filegroup=’ACCOUNTS’ from disk=’E:\backup\fulldb.bak’ with NORECOVERY
- restore database [UNI] from disk=’E:\backup\db_diff.bak’ with NORECOVERY
- restore log [UNI] from disk=’E:\backup\DB_Log.trn’ with RECOVERY
=> Page Restore:
- alter database [UNI] set single_user
- restore database [UNI] page=’1:256′ from disk=’E:\backup\db_diff.bak’ with replace — File ID & Page ID
- alter database [UNI] set multi_user
- select * from MSDB..SUSPECT_PAGES