Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

MS SQL SERVER Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Hardware Requir­ements

Hardware
Minimum
Recommend
Processor
x86: 1.0 GHz
x64: 1.4 GHz
2.0 GHz or faster.
Memory
Express Editions: 512 MB
All other edition: 1 GB
Express Editions: 1 GB
All other edition: At least 4GB
Disk Space
6 GB
Graphic
Super-VGA (800x600)

Ports used by SQL Server

Descri­ption
Port
Analysis Services
TCP 2383
Database Mirroring
Chosen by admini­strator
Dedicated Admin Connection
TCP 1434
Default Instance
TCP 1433
Integr­ation Services: Microsoft remote procedure calls
TCP 135
Integr­ation services run time
TCP 135
Microsoft Distri­buted Transa­ction Coordi­nator (MS DTC)
TCP 135
Named instances in default config­uration
Configure named instances to use fixed TCP ports
Replic­ation
TCP 1433
Reporting Services Web Services
TCP 80
Reporting Services configured for use through HTTPS
TCP 443
Service Broker
TCP 4022
SQL Server Browser service
TCP 1434
SQL Server Browser Service
TCP 2382 (Only used for an analysis service named instance)
SQL Server instance running over an HTTP endpoint
TCP port 80 for CLEAR_PORT traffic
TCP port 443 for SSL_PORT traffic
SQL Server Management Studio browse connection to browser service
UDP 1434
Transa­ct-SQL Debugger
TCP 135

Software Requir­ements

Software
Version
.Net Framework
3.5.1 + 4.0
4.0 is installed during feature instal­lation
Powershell
2.0
Internet Explorer
7.0 or later

Fixed Server­-Level Roles

Role
Descri­ption
Note
An instance lies between the SQL Software and the databases.
Sysadmin
Can perform all activities possible on the Database Engine instance.
Server­admin
Can perform instan­ce-wide config­uration tasks.
Can shut down the instance
Securi­tyadmin
Able to manage instan­ce-­level permis­sions
Proces­sadmin
Can terminate processes running on a Database Engine instance
Setupadmin
Can add linked servers to and remove linked servers from the Database Engine instance.
Bulkadmin
Allowed to use the BULK INSERT statement on databases hosted on a instance.
Diskadmin
Can manage instan­ce-­related files
Dbcreator
Able to create, alter, drop, and restore databases hosted on the Database Engine instance.
Public
All logins are forced members of this role. By default has no rights and shouldn't be given rights
Custom Roles
Custom roles with Custom rights, can be created on server­/Da­tabase level. Database level, allows per table custom rights. Can be defined by: Security -> Database role -> new -> Securables (search) -> all objects -> tables, and ticking what you want of rights, defined per-table.
 

SQL Server Agent Setup

SQL Server Agent is a protocol to automate the execution of jobs.
It is by default disabled.
When SQL Server Agent is started, it automa­tically creates an account for it self.
This account must be a member of the sysadmin group, and is so by default during creation.
The SQL Server Agent should never be a member of the Admini­str­ators Group on the local host.

Dynamic Management Objects

Category
Descri­ption
sys.dm­_exec_*
Provide inform­ation about connec­tions, sessions, requests and query execution.
sys.dm­_os_*
Provide access to SQL Server operating system­–re­lated inform­ation.
sys.dm­_tran_*
Provide access to transa­ction manage­ment.
sys.dm­_io_*
Provide inform­ation on I/O processes.
sys.dm­_db_*
Provide databa­se-­scoped inform­ation.

Backup Types

Name
Descri­ption
Full Database Backup
Includes all database objects, system tables, and data.
Transa­ction occuring during backup is also included.
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 Backup
Includes all database objects, system tables, and data.
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 Backup
Back up data that has been changed since the last full backup.
Use differ­ential backups in conjun­ction with the last full backup.
File attributes are not altered.
Transa­ction Log Backup
Makes a backup of all transa­ctions which has occured in a database.
When the backup is finished the transa­ction log can be trunca­ted­/cl­eaned up.
File & Filegroup Backup
Back up individual database files and filegroups rather than performing a full database backup.
Transa­ction Log backup must also be performed, without truncate.