MS SQL SERVER Cheat Sheet

by Hbak via cheatography.com/67474/cs/16964/

Hardware Requir​ements Software Requir​ements

Hard​ware Mini​mum Reco​mmend Soft​ware Vers​ion

Processor x86: 1.0 GHz 2.0 GHz or faster. .Net Framework 3.5.1 + 4.0
x64: 1.4 GHz 4.0 is installed during feature instal​lation

Memory Express Editions: 512 MB Express Editions: 1 GB Powershell 2.0

All other edition: 1 GB All other edition: At least 4GB
Internet Explorer 7.0 or later
Disk Space 6 GB

Graphic Super-VGA (800x600) Fixed Server​-Level Roles

Role Desc​rip​tion
Ports used by SQL Server
Note An instance lies between the SQL Software and the
Desc​rip​tion Port databases.

Analysis Services TCP 2383 Sysadmin Can perform all activities possible on the Database Engine
instance.
Database Mirroring Chosen by admini​strator

Dedicated Admin Connection TCP 1434 Server​admi Can perform instan​ce-wide config​uration tasks.
n Can shut down the instance
Default Instance TCP 1433
Securi​tyadm Able to manage instan​ce-​level permis​sions
Integr​ation Services: Microsoft remote TCP 135
in
procedure calls
Proces​sadm Can terminate processes running on a Database Engine
Integr​ation services run time TCP 135
in instance
Microsoft Distri​buted Transa​ction TCP 135
Setupadmin Can add linked servers to and remove linked servers from
Coordi​nator (MS DTC)
the Database Engine instance.
Named instances in default Configure named instances to
Bulkadmin Allowed to use the BULK INSERT statement on databases
config​uration use fixed TCP ports
hosted on a instance.
Replic​ation TCP 1433
Diskadmin Can manage instan​ce-​related files
Reporting Services Web Services TCP 80
Dbcreator Able to create, alter, drop, and restore databases hosted
Reporting Services configured for use TCP 443 on the Database Engine instance.
through HTTPS
Public All logins are forced members of this role. By default has
Service Broker TCP 4022 no rights and shouldn't be given rights
SQL Server Browser service TCP 1434 Custom Custom roles with Custom rights, can be created on

SQL Server Browser Service TCP 2382 (Only used for an Roles server​/Da​tabase level. Database level, allows per table

analysis service named custom rights. Can be defined by: Security -> Database

instance) role -> new -> Securables (search) -> all objects -> tables,
and ticking what you want of rights, defined per-table.
SQL Server instance running over an TCP port 80 for CLEAR_PORT
HTTP endpoint traffic
TCP port 443 for SSL_PORT
traffic

SQL Server Management Studio UDP 1434

browse connection to browser service

Transa​ct-SQL Debugger TCP 135

By Hbak Not published yet. Sponsored by CrosswordCheats.com

cheatography.com/hbak/ Last updated 7th September, 2018. Learn to solve cryptic crosswords!
Page 1 of 2. http://crosswordcheats.com
 MS SQL SERVER Cheat Sheet
by Hbak via cheatography.com/67474/cs/16964/

SQL Server Agent Setup Backup Types (cont)

SQL Server Agent is a protocol to automate the execution of jobs. Transa​ction Makes a backup of all transa​ctions which has occured in

It is by default disabled. Log Backup a database.

When the backup is finished the transa​ction log can be
When SQL Server Agent is started, it automa​tically creates an account for
trunca​ted​/cl​eaned up.
it self.
File & Back up individual database files and filegroups rather
This account must be a member of the sysadmin group, and is so by
Filegroup than performing a full database backup.
default during creation.
Backup Transa​ction Log backup must also be performed, without
The SQL Server Agent should never be a member of the Admini​str​ators truncate.
Group on the local host.

Dynamic Management Objects

Cate​gory Desc​rip​tion

sys.dm​_ex Provide inform​ation about connec​tions, sessions, requests

ec_* and query execution.

sys.dm​_os Provide access to SQL Server operating system​–re​lated

_* inform​ation.

sys.dm​_tra Provide access to transa​ction manage​ment.

n_*

sys.dm​_io_ Provide inform​ation on I/O processes.

*

sys.dm​_db Provide databa​se-​scoped inform​ation.

_*

Backup Types

Name Desc​rip​tion

Full Includes all database objects, system tables, and data.

Database Transa​ction occuring during backup is also included.
Backup File attributes are altered.

Increm​ental Back up data that has been changed since last full backup
and increm​ental backup.
File attributes are altered.

Copy-only Includes all database objects, system tables, and data.

Backup Transa​ction occuring during backup is also included.
File attributes are not altered.
Copy backups cannot be used as the basis for a
differ​ential backup or transa​ction log backup.

Differ​ential Back up data that has been changed since the last full
Backup backup.
Use differ​ential backups in conjun​ction with the last full
backup.
File attributes are not altered.

By Hbak Not published yet. Sponsored by CrosswordCheats.com

cheatography.com/hbak/ Last updated 7th September, 2018. Learn to solve cryptic crosswords!
Page 2 of 2. http://crosswordcheats.com