Server Roles
sysadmin |
Role members can perform all activities possible on the Database Engine instance. You specify initial membership of this role when performing installation of the Database Engine feature |
serveradmin |
Role members can perform instance-wide configuration tasks. Members of this role can shut down the instance |
securityadmin |
Assign this role to logins that must be able to manage instance-level permissions |
processadmin |
Role members can terminate processes running on a Database Engine instance. |
setupadmin |
Role members can add linked servers to and remove linked servers from the Database Engine instance. |
bulkadmin |
Assign this role to logins that you want to allow to use the BULK INSERT statement on databases hosted on an instance. |
diskadmin |
Role members can manage instance-related files |
dbcreator |
Role members are able to create, alter, drop, and restore databases hosted on the Database Engine instance |
public |
All SQL Server logins are members of this role. |
Installation of SQL Server
Feature Selection |
Database Engine Services |
Includes the Database Engine, the core service for storing. |
Installation |
New SQL Server stand-alone |
Installing the final SQL Server in a non-clustered enviroment |
Setup Support Rukes |
Status of rules |
should pass all the rules |
Instance Configuration |
Default Instance |
Choose the default instance configuration |
Engine Configuration |
Specify Server Admins |
add a new or current user |
Engine Configuration |
Authentication Mode |
Choosing Mixed Mode - and setting up password |
Monitoring Tools
SQL Server Profiler |
tracks engine process events, such as the start of a batch or a transaction |
Distributed Replay |
use multiple computers to replay trace data, simulating a mission-critical workload. |
System Monitor |
primarily tracks resource usage |
Activity Monitor |
displays information about: Processes running, Blocked processes, Locks, User activity. |
Transact-SQL |
Transact-SQL stored procedures that create, filter, and define tracing: |
Error logs |
The Windows application event log provides an overall picture of events occurring on the Windows Server |
Date data types
datetime |
From January 1, 1753 to December 31, 9999 |
smalldatetime |
From January 1, 1900 to June 6, 2079 |
date |
Store a date only. |
time |
Store a time only to an accuracy of 100 nanoseconds |
|
|
System Databases
Master |
Records all the system-level information for an instance of SQL Server. |
Model |
Is used as the template for all databases |
MSDB |
Is used by SQL Server Agent for scheduling alerts and jobs. |
TEMPDB |
Is a workspace for holding temporary objects or intermediate result sets. |
String data types
char(n) |
Fixed width character string |
8,000 characters |
varchar(n) |
Variable width character string |
8,000 characters |
varchar(max) |
Variable width character string |
1,073,741,824 characters |
text |
Variable width character string |
2GB of text data |
nchar |
Fixed width Unicode string |
4,000 characters |
nvarchar |
Variable width Unicode string |
4,000 characters |
nvarchar(max) |
Variable width Unicode string |
536,870,912 characters |
ntext |
Variable width Unicode string |
2GB of text data |
binary(n) |
Fixed width binary string |
8,000 bytes |
varbinary |
Variable width binary string |
8,000 bytes |
varbinary(max) |
Variable width binary string |
2GB |
image |
Variable width binary string |
2GB |
List of relational databases
Oracle |
MySQL |
Microsoft SQL Server |
PostgreSQL |
IBM DB2 |
A relational database management system, is a database management system that is based on the relational model.
Relational databases have often replaced legacy hierarchical databases and network databases because they are easier to understand and use.
Designing the Installation
Database Engine Services |
Provides the core service for storing, processing, and securing data |
SQL Server Replication |
Allows for copying and distributing data and database objects from one database to another |
Full-Text and Semantic Extractions For Search |
Supports Full-Text Extraction for fast text search |
Data Quality Services |
Facilitates both computer-assisted and interactive methods of managing data source quality and integrity |
Analysis Services |
Supports online analytical processing (OLAP) and data mining |
Reporting Services – Native |
Facilitates the creation, management, and delivery of reports through email and interactive web-based formats |
Reporting Services – SharePoint |
Integrates report viewing and management through SharePoint. |
Reporting Services Add-in |
Provides management and user interfaces that allow integration between SharePoint and SQL Server Reporting Services (SSRS). |
Data Quality Client |
Interacts with data source quality and integrity services |
|
Created By
Metadata
Favourited By
Comments
SYGT NOK CHEAT SHEET
Add a Comment
More Cheat Sheets by Hoejlund