Show Menu

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

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

Ports used by SQL Server

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
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

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

Fixed Server­-Level Roles

An instance lies between the SQL Software and the databases.
Can perform all activities possible on the Database Engine instance.
Can perform instan­ce-wide config­uration tasks.
Can shut down the instance
Able to manage instan­ce-­level permis­sions
Can terminate processes running on a Database Engine instance
Can add linked servers to and remove linked servers from the Database Engine instance.
Allowed to use the BULK INSERT statement on databases hosted on a instance.
Can manage instan­ce-­related files
Able to create, alter, drop, and restore databases hosted on the Database Engine instance.
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

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

Backup Types

Full Database Backup
Includes all database objects, system tables, and data.
Transa­ction occuring during backup is also included.
File attributes are altered.
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.