Create your own awesome maps

Even on the go

with our free apps for iPhone, iPad and Android

Get Started

Already have an account?
Log In

SQL Server Administration by Mind Map: SQL Server Administration
0.0 stars - reviews range from 0 to 5

SQL Server Administration




Parallel Data Warehouse


Enterprise/Developer/Evaluation Editions


Limits, 4 CPU, 2 Node cluster




Overview, Entry level database solution, Small business/department

Limits, 2 CPU


Tools, Express Tools, Express Advanced


SQL Server Overview

System Databases

Master, Server Wide Metadata, Server Logins, User Databases Metadata

Model, Template for New Databases

TempDb, Holds Temporary Tables, Used for Sorting/Query processing, Recreated at Startup

MSDB, Backup History, Replication Metadata, Scheduled Jobs


Storage, File Groups, Tables, Files, Extents, Pages, Indexes, Transaction Log, Files

Database Options, Auto Update Statistics, Auto Create Statistics

DB Users

Server Wide Settings

SQL Server Logins

Database Mail

Planning & Installation

How to install

Upgrade, SQL Server 2008 upgrade technical reference

Installation Center

Checklist, Software, Hardware, Disk Requirements, Estimating size of database, SQL Server Edition, Service account to run the services, Domain account, Local system account, Local service account, Network service account, Which SQL components and features need to be installed?, Database engine sevice, Replication, Full-Text Search, Filestream, Reporting Services, Analysis Services, Integration Services, Instance Name, Default (Server Name), Only one per server, Name Instance (Server Name\Instance Name)

AdventureWorks Database Download



Window Service

Executes scheduled tasks

Maintenance Plan Wizard

Task, Integrity checks, Reorganize indexes, Backups, Shrink database, Execute SQL job, Updating statistics



Core Components, Operators, Person, Group, Device, Notifications, Actions, E-mail, Page, Net send, Write to Windows Application log, Automatically delete job, Job Steps, Types, T-SQL, OS executables, SSIS Packages, SSAS Tasks, One or more job steps per job, One task per job step, Control Flow, Success/Failure/Completion, Quit job reporting sucess, Quit job reporting failure, Go to next step, Go to specific step, Logging, Output, Text file, Table, Alerts, Response, Send notification, Execute job, Types, SQL Server Event, Windows Management Instrumentation (WMI) event, Performance Condition, Schedules

Execution, Scheduled, On demand, sp_start_job, Management Studio, Response to specific event

Job Activity Monitor, Active Jobs, Job History


MSDB, Configuration information, Job History


Roles, SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole



Managing Servers

Central management server, MSDB, Stores registered servers and group properties, Views, sysmanagement_shared_registered_servers, sysmanagement_shared_server_groups, Setup, How-to

Registering Servers, How-to

Multiserver Administration, Policy-Based Management, Central Management Servers, Multiserver queries

Policy Based Management

Execution, Steps, On Demand, On Change: prevent, On Change: log only, On Schedule

How to

Overview, Create and apply policies to one to many servers, databases,objects

Termnology, Facet, Logical grouping of predefined configuration settings, Properties, Examples, Surface area configuration, Server audit, Database files, Condition, Permitted states for one more properites, Boolean expression, Policy, Single condition to enforce, Predefined, Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033, Category, One or more policies enforced together, Target, Servers, databases, objects policies are applied to, facet + condition = policy

Creation Steps, Select facet, Define condition, Define policy, Determine whether instance is in compliance

White paper


Server Components

Database Engine Services


Full-Text search

Analysis Services

Integration Services

Reporting Services

Management Tools

Graphical Tools

SQL Server Management Studio

SQL Server Configuration Manager

SQL Server Profiler

Database Engine Tuning Advisor

Business Intelligence Management Studio (BIDS)

Connectivity Components

Command Line Tools


White papers

SQL Server Security

SQL Server Auditing


Network, TDS, Communication format between application and SQL Server, Endpoints, Shared memory, Named pipes, TCP/IP, VIA, DAC, SNI

Connections, Steps, Opening ports in the firewall to permit database communication, Client, Install network protocols on server and client, Enable and configure client computer to connect using the desired protocol, Install SQL Server Native client on client, Server, Install network protocols on server and client, Enable and configure data engine to listen on network protocols

Principals & Securables

Principals are the individuals, groups, and processes granted access to SQL Server.

Principals, Types, Individuals, Processes, Groups, Windows group, Database roles, Application roles, Levels, SQL Server, Server role, SQL Server login mapped to Windows login, SQL Server Logins, Database, Database role, Application role, Database user, Public database role, Windows, Domain login, Local login, Windows group, Default, Public role

Securables, Levels, Server, Endpoint, Login, Database, Server roles, bulkadmin, dbcreator, diskadmin, processadmin, securityadmin, serveradmin, setupadmin, sysadmin, Database, User, Role, Application role, Assembly, Message type, Route, Service, Remote service binding, Fulltext catalog, Certificate, Asymmetric key, Symmetric key, Contract, Schema, Database roles, Types, Predefined roles, User-defined application roles, User-defined roles, Schema, Objects, Aggregate, Type, Constraint, XML schema collection, Function, Procedure, Queue, Statistics, Synonym, Table, View

Permissions, Types, Statement, Create statements, Implicit, Object


Two Steps Approach, Authentication, Who are you?, Authorization, What can you do?

Microsoft Security Goals, Secure by design, Secure by default, Secure in deployment, Secure through communications

Types of Threats, Data theft, Data Integrity, Data vandalism, Legal, How you store your data, How do you protect it

Best Practices

Intall most recent critical fixes and SPs -OS and SQL

Authentication Modes, Most secure: Windows, Mixed mode: leverage complex passwords and lockout policies, Don't use SA acount, Disable and rename SA

Use active directory and role-based security

Disable unnecessary features

Create policies with policy based management

Install only required components

Run MBSA and SQL Server BPA, MBSA, BPA

Hide SQL instance or disable Browser service

Change default port

Enable firewall to filter unnecessary traffic

Enable security auditing

Remove BUILTIN\Administrators from SQL Server logins

Authorization, Follow the principle of least priviledge, Remove users from public database role, Use schema to group and secure objects


Master Service Key (SMK), Database Master Key

Objects, Symmetric Keys, Generate values used to encrypt and decrypt data, Encryption Algorithms, Protected by, Password, Another symmetric key, Certificate, Asymmetric key, Asymmetric Keys, Description, Pair of keys, Public key, Used to encrypt data, Private key, Used to decrypt data, Certificates

White paper

Transparent Data Encryption (TDE), What is?, Real-time encryption/decryption, Encrypts data and log files, No programming, How, Requires certificate to access files, Encrypts/decrypts at the page level, When enabled - tempdb is encrypted, Performance could be an issue, Can't turn off TDE in tempdb, DB size doesn't change, Limitations, Can't encrypt subset of data, Does not restrict data access, FILESTREAM data is not encrypted, Steps, Create master key, Create certificate, Back up certificate, Create database key, Enable DB encryption, System Views, sys.dm_database_encryption_keys


Ability to track and log events

DDL triggers


SQL Server auditing, Levels, Server, Audit, Database, Audit setup steps, Create audit/target log, Server or database specification, Enable specification, Enable audit, Read audit events, Log Target, Windows Application Log, File, Windows Security Log, Objects, Audit, Audit Specification

White paper

Server Properties/Security, Login auditing, Writes to SQL Server error log, Logs successful and/or failed login, C2 audit tracing, Logged to the default data directory, Common Criteria compliance




Endpoints, sys.endpoints



Configure, SQL Server Configuration Manager, SQL Server Network Configuration

Types, Endpoints, TSQL, Local Machine (Shared Memory), Named Pipes, TCP, VIA, DAC, Dedicated Administrator Connection, Remote access, turned on by sp_configure

Get endpoint permissions

Authentication modes

Windows, Advantages, Single user login, Leverage windows auditing features, Simple login management, Leverage existing password policies

Mixed security, Windows and SQL Server logins

Performance Tuning




SQL Server




Performance Studio/Data Collector, Data Collector, Must be setup on every server, Management data warehouse, Configure (wizard), Performance Data Warehouse, Data Collector, SSIS packages, SQL Server Agent jobs, Collector types, T-SQL, SQL trace, Default trace, User defined trace, Performance counter, Query activity, How-to

Resource Governor, Setting workload priority, Set limits, What?, CPU, Memory, When?, Connection is created, Who?, Connections, Groups of users, Assign and classify connections, Resource pools, Prioritize, White paper, How-to

SQL Server Database Engine Tuning Advisor (DTA)

Dynamic Management views, Prefix, dm_db_*, sys.dm_db_index_usage_stats, dm_exec_*, dm_io_*, dm_os_*


White paper

Monitoring SQL Server

System Monitor (PerfMon.exe)

Captures statistical information, Hardware, OS, Application, must expose properties and counters

Architecure, Polling interval, Capture, Logs numeric data, Exposed by applications

Counters, Hierarchy, Levels, Object, Component, application, or subsystem, E.g. Processor, SqlServer:Databases, Counter, At least one counter per object, E.g. data file size, Counter instances, Zero or more per counter, E.g. each database, Counters to capture can be defined at any level

Logging, Counter log, Logs to file

Core coutners, System, System:Processor Queue length, Physical Disk: Avg. disk queue length, Network Interface: Output queue length


Windows logs, Application, System, Security

Sql Server Agent logs

SQL Server logs

Database mail logs

White paper

Monitoring events

SQL Trace, Tools, SQL Serve Profiler, Defining trace, Events, Data column, Filters, Event Groups, Broker, CLR, Cursors, Database, Depreciation, Errors and warnings, Full text, Locks, OLDDB, Objects, Performance, Progress report, Query Notifications, Scans, Security audit, Server, Defining a trace, Events, Data columns, Filters, Trace Components, Trace controller, Central componet, Manages all traces, Trace I/O providers, Send trace data, File, Rowset, Permission, Alter Trace, Server-side tracing, Stored procedures, sp_trace_create, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus, Tables, sys.traces, sys.trace_events, sys.trace_columns, fn_trace_geteventinfor(), New node, fn_trace_getfilterinfo(), fn_trace_gettable, Default trace

Event notification

Task manager

Data Collector

Collects different sets of data and stores the collected data in the management data warehouse.

Data sets

Schedule, User-defined, Constantly

Management data warehouse

Management Studio

Activity Monitor, Processes, Resource waits, data file I/O, Expensive queries, Graphical interface, Real-time performance data

Predefined Standard Reports


OS, sys.dm_os_sys_info, sys.dm_os_sys_memory


Backup Compression

Default setting, Initial setting is off, sp_configure to change value

Backup Types






Partial, Full, Differential

Recovery Plan


Mission Critical?

What needs to be backed up?

Backup frequency?

Offsite, Storage, Retrieval

Backup file security?

How long to retain the data?, Onsite, Offsite

Downtime cost?

Regulatory requirements?


Recovery Model



Simple, No transaction log backup



User errors

Natural disasters

Copy database to another server

Hardware failure


Maintenance Plan Wizard


Backup Devices, Tape (local only), Disk File, Local, Network Share




Types, Nonclustered, Clustered

Constraints, Primary Key, Unique

Index Design, Know your data, Know how SQL Server works, Index internals, Statistics, Maintenance, Query optimization, Know your workload

Commands, dm_db_index_physical_stats, DBCC IND


Data Types, Numeric, DECIMAL (p,s), Scale (s), Max number right of decimal, Precision(p), Max Decimal Digits (1-38)(5-17 Bytes), Integers, TINYINT, 0 to 255 (1 byte), SMALLINT, -32768 to 32767 (2 bytes), INT, -2,147,483,648 to 2,147,483,647 (4 bytes), BIGINT, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (8 Bytes), Character, VARCHAR(n), CHAR(n), Datetime, DATE, DATETIME, DATETIME2

Default Values

Constraints, Check, NULL or NOT NULL, Foreign Keys, Source Table, Column, Target Table, Columns, Unique, Primary Keys

Identity, Properties, Starting seed, Incremental value, Commands, SET IDENTITY_INSERT tablesname ON, IDENT_SEED(tablename), @@IDENTITY, INDENT_INCR(tablename), IDENTITYCOL, SCOPE_IDENTITY(), IDENT_CURRENT, DBCC CHECKIDENT(tablename)




Container for Objects


High Availability


Why, Seamless failover - little impact on client application, Reduce downtime during routine maintenance, Sql and Windows patches, Instance level availability

How to

Database Mirroring

How to, Manually fail over, Manually fail over (T-SQL)

Overview, per-database, Two copies of the data, Principle server, Accessed by client apps, Mirror server

Roles, Principal, Mirror, Witness (optional)

Operating modes, High availability, High performance, High safety

Why, Data protection, Increases database availability

Setup overview, Server wide data must exists on the mirror server, Logins, SQL Jobs, Create mirror database, Restore DB from principle server, with NORECOVERY option, Configure database mirroring, Security, Database mirroring endpoints

Clients/Applications, Connecting clients

Log Shipping


Peer-to-peer, How to

White paper


What impacts availability?, Power, Software, Hardware, Natural disaster

Solutions, Levels, Hardware, CPU, Memory, Hard disks, RAID, RAID1, Disk mirroring, RAID5, Disk striping with parity, RAID10, Disk mirroring with striping, Power supplies, Network, Multiple network cards, Database, Peer-to-peer replication, Log shipping, Database mirroring, Server, Clustering

Managing Metadata

New node



Create distributor

Create publications

Create subscriptions


Publisher, Master copy of the data

Subscriber, Receives data from publisher

Distributor, Main engine, Distribution database




Snapshot, Copies all objects and data


Article, Types, Table, Stored procedure, Function, Set of data that SQL Server replicates

Publications, Grouping of articles

Filter, by, Rows, Columns

Data Partitioning


Create a partition function, Select partition key, LEFT (default) < and RIGHT>, n + 1 partitions

Create partition scheme

Create table or index paritition


Querying Data, $PARTITION, Limit query to specific function

SPLIT, Add, Add new boundary point/partition

MERGE, Delete, Remove boundary point/partition

SWITCH, Move, Move partition of data between tables


Index alignment

Partition function

Partitioning key

Partition scheme


Divide a table or index into multiple filegroups

Tables and indexes are partitioned horizontally based on partitioning column

Benefit, Faster access, More manageable sub-sets

Parallel operations, Multi-processor

Range Partitions, Divides a table into subsets based on values, Example, Dates, Boundaries define the partitions, Define the boundaries, Map to one or more filegroups, What should the range be, partition key



Full-Text Search


Search words, phases, or multiple forms of words of phases in a column, Indexes all words and parts of words, Except stoplist words

Linguistic search against text and documents

Operating on words and phrases based on rules of a particular language

Allows contextual searches for relevant data

Goal, Optimize linguistic searches of unstructured data


Inflectional forms, Enabled by default, Run and Ran, Flexible google search



Commands, CONTAINS, used to search specific terms, word or phrases, FREETEXT, Wildcard searches by default, True/False results, FREETEXTTABLE, CONTAINSTABLE

Performance, Pass unicode strings for search terms




Instance Level, Install full-text, part of the SQL installation, Full-text enabled by default, for all databases

Database Level, Create storage structure, Full text catalog, One or more full text indexes, Logical container, Create full text index, Data types, CHAR/VARCHAR, XMl, Special processor, content only, VARBINARY, Stored documents, Specify type of document, Loads assembly, Ships with 50, Word, PowerPoint, HTML, FILESTREAM, One per table or indexed view, Change tracking, When underlying data changes, Options, Manual, Auto


Word breakers


Stop lists



Full-text catalog


Full-text index

Token, New node






Full-Text Engine

Filter daemon host process (Fdhost.exe)

Filter daemon Launcher(FdLauncher.exe)


Thesaurus, Search synonyms of search argument, Used automatically for FREETEXT, Must use FORMSOF THESAURUS for CONTAINS, Language specific XML file, Stored in FTDATA directory, Tsenu.xml (English -US), Expansion or replacement sets

Stop lists, Excludes words, Common words, the, a, an

Populating, FULL, Reprocesses every row, INCREMENTAL, Only rows changed since last population, Requires, TIMESTAMP column, UPDATE, Since last update, Requires CHANGE_TRACKING, Set to manual

White papers



fdhost.exe, Filter Deamon Launcher Service, Manages, Breakers, Stemmers, Filters

Thesausers, XML files, sys.sp_fulltext_load_thesaurus_file

Indexer, Stored index words, location and primary key