Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.



  • Objects are divided into programmability and storage
  • Many built-in data types
  • Can create user-defined data types
  • Create indexes on frequently searched columns
  • Constraints define rules for column
  • Unique constraints ensure uniqueness
  • Constraints can also define relationships between tables (foreign key contrainsts)
  • Views can be updated
  • Stored proc’s are compiled t-sql stmts
  • Functions, similar to sp’s – return a value – never update data
  • Triggers – actions that are executed whenever data changes
  • DML – Insert/update/delete/merge
  • DDL – create/alter/drop

Database Recovery Models

  • Simple Recovery model – log files re-used as soon as they become full
  • Full Recovery model – trans logs kept until backups
  • Bulk-logged model – like full, except does not log non-logged operations
  • Non-logged operations offer better performance
  • Truncate table/select into – are non-logged or minimally logged
  • Cannot use the above statements when using replication

Tables and Views

  • Table must have at least one column
  • Stores data in underlying mdf/ldf/ndf
  • Various data types, can create User defined (Create/Alter/Drop type)
  • Columns can have special attributes: Identity, Computed, timestamp or uniqueId
  • UniqueIdentifier column – GUID, used for replication, guaranteed unique
  • Constraints include
    • Check – compares column data with value. Cannot check against another table. Use trigger
    • Primary Key – enforce uniqueness of each row
    • Unique – same as primary. But can have multiple
    • Foreign key – enforce relationships between tables
    • Default – default value of column
  • DML Statements commit as a whole or not

Creating Indexes

  • See post it notes – chapter 7
  • I’ve read tons of articles on indexes and this explanation is by far the best:
  • Enables sort and query performance
  • If table is not sorted, no index, known as a heap
  • Only 1 clustered index per table. Usually the primary key
  • Can have many non-clustered indexes. Usually create on frequently searched fields
  • Trade-off is write performance – indexes must be updated
  • CREATE INDEX statement. Use for Clustered and Non-clustered
  • Can choose index to be unique, or use filter clause
    • when ON, free space is allocated in each page of index. Amount, see FILLFACTOR
    • when OFF, free space for one row in each page
    • Specifies percentage of free space to allocate. If you specify 80%, then 20% will be free. See chapter 7 for more details.
  • ONLINE=ON|OFF – SQL 2008 enterprise. Underlying table online and accessible while index rebuilt
  • If you specify a column to be PRIMARY KEY it will default to Clustered index. This can be overridden by specifying non-clustered
  • If you specify a column to be UNIQUE it will default to non-clustered index

Full Text Indexes

  • SQL 2005 and earlier – separate service [full text search]
  • SQL 2008 – part of DB service
  • Full text index can be stored on separate filegroup
  • Only 1 full text per table – based on single, unique column that does not include nulls
  • FREETEXT – used to search full text index
  • Char, varchar, nchar, nvarchar, text, ntext, xml, image, varbinary
  • image, varbinary – must specify type column (docx, pdf, xlsx)


  • View types
    • Standard View – One or more tables. Can include join, where, top, order
    • Updateable view – single table only. Can have following triggers, [instead of update|merge|delete)
    • Index view – CREATE INDEX
    • Partitioned View – joins all partition data into 1 view (horizontal/vertical partitioning)
  • CREATE VIEW {columnnames} – allows you to rename columns shown in view
  • CREATE VIEW WITH ENCRYPTION – hide view definition also prevents  view from being replicated
  • CREATE VIEW SCHEMABINDING – bind with underlying table columns cannot be dropped/altered until the view is dropped. Only applies to columns used within the view

Creating Stored Procedures

  • Stored compiled t-sql
  • Can accept parameters (@ datatype) and return values (OUTPUT)
  • Can assign default parameter to use if none supplied
  • Can change data
  • WITH ENCRYPTION – encrypts the stored procedure definition
  • WITH RECOMPILE – instruct DB engine to never cache the execution plan for this SP
  • EXECUTE AS – allows to run sp as different user

Creating Functions

  • Similar to functions – cannot execute DML statements
  • Always return single value – or single table-valued expression
  • Used for re-using code
  • Functions can accept input parameters
  • Functions do not support output parameters
    • Scalar functions: return a single value
    • Single statement table-valued functions: single select return table expr
    • Multiple statement table-valued functions: return multiple table expr
    • Built-in functions – getdate(), avg()

Creating Triggers

  • Triggers are sp’s bound to table/view
  • Run when DML is executed
  • FOR UPDATE|FOR INSERT|FOR DELETE – execute immediately after insert/update/delete statement
  • Trigger can include complex t-sql, query tables/views, execute DML
  • Use sparingly, affect performance, difficult to maintain
  • Unlike the CHECK constraint, triggers can access other tables!
  • Trigger can access special tables INSERTED and DELETED
  • Virtual tables only exist while the trigger is executing
  • Query these tables to view changed records
  • Can define a trigger on a view


Thanks for reading.


Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

sql server partitioning 


  • System Monitor (Perfmon)
    • Performance monitoring provided by windows
    • Measure hardware performance, bottlenecks, process causing problem
    • Provides server wide overview
  • Tracing
    • Get info on t-sql or mdx executed on DB
    • Trace for deadlocks, lock escalation, warnings, data/log shrinks, blocks
  • SQL Profiler
    • Used to define/configure traces
  • Dynamic Management Objects

Trace Templates

Can modify all
Can export to text or database
Avoid tracing to system drives, data or log files stored 

  • Sp_counts – trace calls to start sp
  • Standard – instance overview, audit, t-sql/sp start and completion
  • T-SQL – troubleshoot client apps, t-sql start times
  • T-SQL_Duration – all t-sql stmts and duration to complete
  • T-SQL_Grouped – activity certain user/app
  • T-SQL_locks – all t-sql with exceptional locks
  • T-SQL_replay – recreate workload another server
  • T-SQL_sps – each step in a stored proc
  • Tuning – t-sql completion times, use in DTA for analysis

Trace Events

  • Decide what events to capture
  • Show all events/show all columns
  • Error and Warnings events – useful
  • Caution with lock events (many locks on busy sql server)
  • Trace as few events as possible – performance

Trace Filters

  • Restrict the data
  • Like or not Like
  • Filter by user, db, application etc

Server side tracing

  • Reduces impact of tracing
  • From server directly – no network involvement
  • Create trace in profiler – then export script trace definition – run in SSMS
  • Script out trace definition
    • Sp_trace_create – defines trace, output (@traceid)
    • Sp_trace_setevent – define events
    • Sp_trace_setfilter – define filters
    • Sp_trace_setstatus – controls trace, input(@traceid)
      • 0 – stop
      • 1 – start
      • 2 – close/remove definition
  • Select * from fn_trace_getinfo(null) – information on what traces are running
  • TraceID = 1 – always there, used for DMV’s

Load trace file into a table

  • Select * into abc_trace from ::fn_trace_gettable(‘c:\file.trc’,default)

Combining System Monitor & Profile traces

  • Introduced in SQL 2005
  • Combine perfmon and SQL trace (side-by-side)
  • Correlate high CPU with t-sql stmt

Replaying traces

  • Capture trace on prod, replay on dev
  • There are some minimum requirements
  • Easiest to use replay template
  • Cannot trace replay on server participating in transactional replication

Load trace file into a table

  • Select * into abc_trace from ::fn_trace_gettable(‘c:\file.trc’,default)


  • Command line tool
  • Data collection utility – since SQL 2005
  • Collects the following
    • Windows event logs
    • SQL Configuration details
    • System monitor performance logs
    • SQL profiler trace data
    • SQL server blocking info
  • All of the above can be captured individually – just easier with SQLDIAG – all in one go.

 Locks, Blocking and Deadlocks

  • Concurrency vital part of RDMS, Implemented by locking
  • Excessive locks cause blocks, which affects performance
  • Deadlocks occur – two tasks after same resource (endless loop)


  • Provides concurrency
  • Importance of locking (ACID)
  • Atomic & Consistent – transaction management
  • Isolation – achieved by locking
  • Durable – achieved by tran logs


  • Deadlocks occur – two tasks after same resource (endless loop)
  • Least expensive tran to rollback is killed

Transaction Isolation Levels

  • 5 levels, provide balance concurrency and data integrity
  • Defines the type of lock, how long to hold lock, how to read the data
  • Read un-committed (dirty reads)
  • Read committed  (default)
  • Repeatable Reads
  • Snapshot
  • Serializable

Lock Escalation

  • SQL tries to lock fewer resources as possible
  • Increase single lock to higher level = lock escalation
  • Row locks escalated to table locks
  • Page locks escalated to table locks
  • Row never escalated to page
  • SQL 200 – row/page locks can escalate to partition

Lock Compatibility

  • About 22 locks in SQL Server
  • Intent (I) – used to establish lock hierarchy
    • IS Intent Shared
    • IX Intent Exclusive (IX)
    • Shared with Intent Exclusive (SIX)
  • Shared (S) – read only operations (select *)
  • Update (U) – on resources that can be updated
  • Exclusive (X) – Used for data modification

Detect and Resolve Locks

  • Activity Monitor
    • Session.state
    • Running, waiting, sleeping
  • Profiler
    • Blocked process report
    • Deadlock chain
    • Deadlock graph
  • DMV’s
    • Sys.dm_exec_requests (waiting for lock)
    • Sys.dm_tran_locks  (detailed locking info)
  • Trace Flag 1222
    • Output lock details to SQL error log (sp_readerrorlog)
    • –t1222 (add to sql service cmd line / requires restart)
    • Dbcc traceon (1222,-1), no restart required


  • Sys.dm_xxxx
  • Permissions: server scope (view server state permission) or database scope (view database state)
  • DMV – provides snapshot information or cumulative information since last restart
  • Remember, trace ID 1 – gathers infor for DMV’s
  • Instead of sp_who2
    • Sys.dm_exec_requests
    • Sys.dm_exec_sessions
  • Dbcc showcontig replaced by sys.dm_db_index_physical_stats
  • Sys_db_exec_sql_text = replaces fn_get_sql : text of the sql batch
  • Sys_dm_exec_query_stats = perf stats of queried cached plans
  • Sys_dm_os_wait_stats = waits encountered by threads
  • Sys_dm_db_index_usage_stats = number of index usages
  • Sys_dm_db_index_operational_stats = low level I/O locking and latching
  • Sys_dm_db_missing_index_stats = info on missing indexes


  • Around since SQL 7 in various formats
  • SQL 2008 enterprise introduces next level – round robin thread model
  • New level of lock escalation – row/page to partition
  • Horizontal partitioning
    • 1 Table split into multiple tables
    • E.g. Each table would contain year, e.g. 2011, 2012 records, Contains only subset of rows
    • All columns in all partitioned tables
  • Vertical partitioning
    • 1 Table split into multiple tables
    • E.g. Each table would contain a subset of columns
    • Each table contains all rows
    • Requires a join to get data back together
  • Partitions can exist in one filegroup. Best practise to have multiple groups though
  • 1 partition – 1 filegroup – different disk – better performance
  • Partition key – Logical division between partitions
  • Partition function
    • Creates partition key but no data placement
    • Specifies boundaries between each partition
    • CREATE PARTITION FUNCTION (name, column)
    • Use LEFT/RIGHT to determine partition boundry
    • LEFT is default (equal to or less than
  • Partition Schema
    • Map partition to filegroup
    • 1 partition – 1 filegroup – best for performance
  • Moving data between partitions (ALTER PARTITION FUNCTION|ALTER TABLE)
  • ALTER PARTITION FUNCTION (Split – add new partition to end of existing range OR Merge – combine two partitions)
  • ALTER TABLE (Switch – move a complete partition OR Implement sliding partition window)

Performance Data Collector 

  • SQL 2008 only (server and target servers)
  • Disabled by default – requires config
  • Stores centralized performance data across all SQL servers
  • Provides reports
  • Considered a client consisting of
    • Data source
    • Executable (dcexe.exe)
    • Management Data warehouse db (mdw) – stores the data
    • Sql agent job – data collectors (dc)
    • Integration services
  • Sources
    • Perfmon counters
    • Dmv’s
    • Profiler trace
  • First create the MDW then the DC’s
  • MDW created in simple recovery mode – default
  • 2 types of data collectors (DC’s) – System and Custom
  • System
    • Disk usage
    • Server activity (mem, cpu)
    • Query stats (queries, exec plans)
    • Default reports available for above
  • Custom
    • T-sql query
    • Sql trace
    • Performance counter
    • Query activity
  • Data collectors run on each configured server (dcexec.exe)
  • Data collector caching
    • Cached mode – collect – store data temp – upload to mdw (separate agent job)
    • Non-Cached mode – collect and  upload to mdw at same time
  • MDW Security
    • Mdw_admin
    • Mdw_writer
    • Mdw_reader
  • DC Security
    • DC_admin
    • DC_operator
    • DC_proxy

Resource Governor Concepts 

  • SQL 2008 feature
  • Disabled by default
  • Ability to reserve minimum resources for users or tasks
  • However if resource is idle, it will be used for tasks that require it
  • 3 key concepts
    • Resource pool
    • Workload groups
    • Classification functions
  • Resource Pools
    • Manage min and max restrictions to resources
    • Internal pools (2) – created by install, used for internal sql processes
    • Default pools (18) – used for workloads assigned
    • Internal pools has highest priority
    • CREATE RESOURCE POOL xxx WITH (max_cpu_percent=100, min_cpu_percent = 50)
    • ALTER RESOURCE GOVERNOR RECONFIGURE – restart not required
  • Workload Groups
    • Used to assign user session to specific resource pool
  • Classification Function (UDF)
    • Identify incoming requests to SQL Serve
    • Map these requests to a workload group
    • Function can use any valid property to classify (username, hostname, ip)
    • Only one UDF can be a classifier at any time
    • Dedicated admin connection(DAC) is not exposed to classification
  • Resource Governor summary
    • Session begin
    • Classification function executes (UDF)
    • Workload group membership determined
    • Query executes within correct resource
  • Sys.dm_resource_governor_resource_pools
  • Sys.dm_resource_governor_workload_groups

Execution plans

  • Showplan xml statistics profile – actual execution plan
  • Showplan xml – estimated execution plan

Thanks for reading.

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.


SQL Service Broker (SSB)

  • Collection of objects in a db that when configured can be used to send msg’s from one process to another
  • Used for Async Tasks
  • SQL 2005 introduced SSB
  • Msg delivery guaranteed in order sent
  • Msg can only be received once
  • Msg always processed in correct order
  • SSB can only be used by SQL Server. Have to connect to SQL to use SSB.
  • Any process can send with correct rights
  • Any database (incl system) can use SSB
  • SQL Mail uses SSB
  • Two ways to receive a message
    • RECEIVE on demand: SQL job can call this command or a win app could log into db and then initiate the receive command.
    • Activated Stored Procedure: msg’s received an/processed automatically. It’s a normal SP which is started by receiving queue when msg received

SSB – Send messages scenarios

  • Send a msg within a DB
  • Send msg to another DB in same instance
  • Send msg to another DB on another server
  • Send msg to another DB on a SQL server hosted at 3rd party

Enabling and Resetting SSB

  • SSB comes ready in all databases
  • However it must be enabled (ALTER DATABASE SET NEW_BROKER)
  • If a DB is restored, have to reset broker (SET ENABLE_BROKER)
  • If you set NEW_BROKER on existing SB – loose in flight msg’s
  • SELECT is_broker_enabled from sys.databases

SSB conversation

  • Conversation > Messages
  • Conversation can contain multiple messages
  • Conversation exists between two services (queues)
  • Re-use conversation for many messages = increase performance (x4)
  • Receive on many messages single time = increase performance (x10)
  • Message body (single value, binary, xml, blank message)

SSB Message types

  • Conversation > Messages > Message Type
  • Definition of data within message
  • Definition of type of validation by SSB
  • 4 types of validation
    • NON E (default) – any data (text, num, xml, binary)
    • EMPTY – used for NULL/empty – cannot use ‘NONE’
    • WELL FORMED XML – correctly formed empty, saves writing validation routines
    • VALID XML WITH SCHEMA COLLECTION – create schema beforehand to use – (create XML schema collection)

SSB Contracts

  • Conversation > Messages > Message Type>Contract
  • 1 contract per conversation
  • Contract defines the Message type
  • Which end of conversations uses which msg type
  • CREATE CONTRACT – there is no ALTER or DROP

SSB Message and Contract views

  • Sys.service_message_types – row per msg type
  • Sys.service_contracts – row per contract
  • Sys.service_contract_message_usages – row per contract/msg pair


  • Queue is where messages are sent too
  • Physical object in a DB
  • Specify filegroup
  • Queue STATUS on/off – enable or disable queue from receiving messages
  • Queue RETENTION on/off – keep records after being received
  • Queue ACTIVATION – activated stored proc – allow SQl to start SP when msg arrives in queue.
  • 3 step
    • SET SP as activate procedure (ALTER QUEUE)


  • Bind contract to queue
  • Msg’s are sent from service to service
  • Service binds single queue to many contracts
  • Service names are case sensitive

Endpoints (TCP/IP)

  • SQL Server can listen for endpoints on http or tcp
  • SSB requires that listener be a TCP endpoint
  • 1 end point per SQL Server SSB (server wide) – all db’s use one endpoint
  • Tell SSB about remote endpoints (routes)
  • End point allows access to a SQL SSB
  • Cannot share SSB endpoint between 2 instances
  • Same server, then you need two different TCP/IP ports
  • Endpoint traffic is encrypted
  • ENDPOINT can be in 3 states
    • Started – Accepts remote connections
    • Stopped – Listening for removed connections + rejects them
    • Disabled – tcp port closed, connection fails, could not be found


  • Used to inform SSB that msg is for another server
  • Sys.routes
  • DROP ROUTE/CREATE ROUTE – run on both server so know about each other
  • Routes can expire or be deleted
  • Require the IP, PORT and GUID of SSB

Sending a Message

    • BEGIN DIALOG xxx
    • FROM SERVICE xxx
    • TO SERVICE xxx
    • ON CONTRACT xxx
    • SET @msg = (select X …)
    • MESSAGE TYPE @msg
  • RECEIVE from queue
    • End conversation (sends ack back to sender)


  • Sys.conversation_endpoints
    • Info about inflight conversations
    • Info about recent conversations
    • Single row for each conversation
    • State column – identify if source or dest has issue
    • Info on number of msg/conversations
    • When conversations will expire
  • Sys.transmissions_queue
    • Info on stalled msg
    • Single record for each stalled msg
    • Single record for each in-flight msg
    • Transmission_status column – text what issue may be. No value in the column indicates OK (in-flight)
    • Resolve/error msg will move to new record
  • Sys.service_message_types – meta data msg types
  • Sys.service_contracts – metadata contracts
  • Sys.service_contract_msg_usages – link contract to msg types
  • Sys.service_queues – meta data queues
  • – meta data services
  • Sys.service_contract_usages – service bound to contracts
  • Sys.service_queues_usages – queues bound to services
  • Sys.service_broker_endpoints
  • Sys.tcp.endpoints
  • Sys.routes


 Thanks for reading.


Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

 XML XQuery

XML Indexing

  • Index XML data type – BLOB (up to 2GB)
  • Used to optimise ‘xquery’ queries
  • Indexes all tags, values and paths
  • Two types of indexes:
    • Primary: Create primary XML index
    • Secondary: Create XML index (which is secondary)
  • The first index on XML data type must be PRIMARY
  • To create XML index, table must have a clustered index on Primary Key
  • CREATE PRIMARY XML  INDEX (xx) on XML_TABLE (custaddr)
  • CREATE XML INDEX USING [primary index] FOR [path|Value|property]
    • PATH – helps queries use xml path expression
    • VALUE – helps queries search values in xml
    • PROPERTY – helps queries retrieve particular property
  • ALTER INDEX – modify existing XML indexes
  • Indexes are enabled by default. Use ALTER to disable
  • DROP INDEX, if primary it drops secondary’s
  • Cannot create XML index on
    • View
    • Table variabl (xml column)
    • Table variable (xml type)

HTTP Endpoints

  • Not supported in Express version
  • Expose stored procedures and functions as methods to be called by SOAP
  • SQL Server listens for HTTP requests natively
  • [HTTP|TCP] – known as protocols
  • Native XML Web services (SOAP/HTTP endpoints) have been depreciated in SQL 2008
  • CREATE ENDPOINT – the state can be  started/stopped/disabled
    • Started – start and listening
    • Stopped – Stopped but Listening and returns errors
    • Disabled – Does not listen

Securing a User EndPoint

  • Connect permissions to Endpoint
  • Global permissions to SQL Server
  • Permissions to default system endpoints granted to all logins
  • To restrict access, deny everyone (deny connect) and then grant connect
  • To restore to default, grant connect to public group

Testing the EndPoint

Web Services

  • Web Service is – Platform independent message framework
  • Accessed by SOAP/UDDI/WDSL – consumes – Web service (xml)
  • Based on established HTTP protocol

XQuery Explained

  • Language used to query XML data
  • SQL 2008 supports subset of xquery
  • Based on XPath
  • XQuery used to query XML Docs/Data Sources
  • 4 Methods involved:
    • Query() – returns xml data that matches query. Select emp.query(..) from ABC
    • Value () – Extract a value from XML doc. Allows comparison with non-xml columns. Select emp.value(…) from ABC
    • Exist() – Check existence of value (returns 1 or 0). Select xxx from yyy where emp.exists(…)
    • Nodes() – return rowset for each row. Useful for making XML into relational. Cross apply emp.nodes(…)
  • XQuery syntax
    • Case sensitive
    • Valid XML names
    • Variables use $
  • SQL 2008 provides extension which allow XQuery to modify data – [UPDATE xxx SET emp.modify(insert/delete/replace)]
    • Insert – insert xml before/after existing. Insert attributes
    • Delete – delete elements and attributes
    • Replace Value Of – replace node, must be single user mode


  • Standard navigation language
  • Used to identify set of nodes in xml
  • Implemented in SQL 2008, subset of W3C xpath specs (SQLXML 4.0)
  • Used to compute values from XML to DOC
  • Number of XPATH expressions types – google it.


Thanks for reading.



Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

 start SQL Server in min config


Monitoring & Troubleshooting Tools

  • SQL 2000
    • Profiler
    • Trace flags 1205 & 3605
    • DBCC commands
  • SQL 2005
    • DMV’s
    • Profiler
      • Deadlock graph
      • Show XML Events
      • View Perfmon in Profiler
  • SQL 2008
    • Extended Events

Error Log

  • SQL Server writes errors and info to the error log
  • If it can’t write to the error log it still writes to the application log (SQL won’t start)
  • [C:\programfiles\ms sql server\mssql10.instance\mssql\log\]
  • SQL – ErrorLog
  • Agent – SQLAgent.out
  • Access the log in SSMS or using text
  • Startup parameter (-e)
  • Every SQL restart recycles the logs. Keeps 6 by default. Configurable, right-click in SSMS
  • Also recycles agent log (sqlagent.out)
  • Sp_cycle_errorlog
  • Sp_cycle_agentlog


Start SQL in minimal configuration

  • Allows database instance to start without tempdb
  • [c:\prog file\ms sql server\mssql\bin] – CMD – errorlog writes to console
  • Sqlservr.exe –c –f –m
    • –c : Start from command line
    • –f : Start in minimal configuration
    • –m : Start in single user mode (only sysadmin can connect)
  • After it’s running, connect using SQLCMD
  • Only sysadmins can connect

SQL Server Browser Service

  • Provide instance + Port info
  • UDP: 1434
  • SQL: 1433
  • If Browser service is stopped, must provide instance + port to access
  • If SQL configured with dynamic ports – must use browser service
  • If stopped, instance names not published
  • 1 browser service, works for all instances on the server



Set Transaction isolation level

  • Read Uncommitted: Read rows from other trans that are not committed, least restrictive, which creates ‘dirty reads’. NOLOCK
  • Read Committed: Cannot read data that is not committed. No dirty reads. It’s the SQL Server default – Shared log.
  • Repeatable read: shared lock
  • Snapshot: Read any data, transnationally consistent to the start of the query
  • Serealiazable: Range locks. Least restrictive.


  • Sqlagent.out
  • SQL Agent service, account that it’s running under, should be a sysadmin
  • If Agent running under system or local account, use proxy accounts to access network locations
  • Domain account -> Credential -> Proxy -> Agent Job
  • If domain account password is changed- then must update credential
  • T-SQL Agent step cannot use a proxy
  • Msdb.dbo.sysjobhistory


  • Sp_counts – used to count number of calls to SP
  • Standard – Instance overview, t-sql + sp start  + completion
  • Tsql – t-sql batch start, troubleshoot client apps, performance
  • Tsqlduration – tsql statements and completion times
  • Tsqlgrouped – capture activity for user or app, tsql grouped by user/app
  • Tsql_locks – tsql submitted by apps + exceptional locks, deadlock troubleshooting
  • Tsql_replay – trace to be replayed. Recreated workload another server
  • Tsql_sps – each step within a SP. Performance within SP’s.
  • Tuning – pass to sql database tuning advisor


Useful Perfmon counters. These are listed OBJECT first, then COUNTER

  • MEMORY – Available Bytes – amount of memory available to processes on server
  • Paging File – % used – amount used
  • Physical Disk – Avg. Disk sec/Read – avg time taken to read data
  • Physical Disk – Avg. Disk sec/Write – avg time taken to write data
  • Physical Disk – Disk reads/sec – rate of read operations per sec
  • Physical Disk – Disk writes/sec – rate of write operations per sec
  • Processor – % Processor time – Time that CPU is busy
  • SQLServer: Buffer Manager – Buffer cache hit ratio – % of data found in memory without reading from disk
  • SQLServer: Buffer Manager – Page life expectancy – number  of seconds page stays in buffer pool without references
  • SQLServer:General Statistics – User Connections – Number of users connected
  • SQL Server: Memory Manager – Memory Grants Pending – # of processors waiting on memory grant
  • SQL Server:SQL Statistics – Batch Requests/Sec – # of sql batch requests received by server
  • SQL Server:SQL Statistics – Compilations/Sec – # of sql compilations/sec  (should be low)
  • SQL Server:SQL Statistics – Re-Compilations/Sec – # of sql re-compilations/sec
  • System – Processor Queue Length – # of threads in processor queue. Shows ready threads, not threads that are running.


Thanks for reading.

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.



  • Replication – copying and distributing data between databases and then synchronizing them to maintain consistency and integrity
  • 1 of 4 HA methods (mirroring, clustering and log shipping)
  • Advantage over others
    • can filter rows and columns
    • Can read from primary and secondary
    • Performance – have reports running off secondary as read-only
    • Availability – in DR – change connection manually to point to active node
    • Mobile devices – connect and synch then disconnect
    • Could use replication to load data from DW to staging area
    • Replication is both DR and HA solution
    • Replication components
      • Article = objects that are replicated – table, sp, view, some rows or some columns Publication – collection of articles. Articles can be in more than on pub
      • Subscription – request for a copy of the publication – where, what and push/pull
      • Publisher – instance that holds publications
      • Distributor – Store replication status, meta data, data queue, can be local/remote
      • Subscriber – Receives replicated data – pulled or pushed

Replication types

  • SNAPSHOT Replication
  • TRANSACTIONAL Replication
    • Transaction Replication with Updateable Subscriptions
    • Peer-to-Peer Replication
    • MERGE Replication

Replication Versions

  • All Replication – Distributor SQL Version must be no earlier than the publisher
  • Trans Replication – subscriber to pub, any version within 2 of publisher. E.g.
    • SQL 2000 Pub -> SQL 2008 Sub
    • SQL 2008 Pub -> SQL 2000 Sub
    • Merge Replication – subscriber can be any version no later than publisher


  • Distributes data point in time
  • Good for small amount of data
  • Infrequent data changes
  • Acceptable out of date data
  • Primary key not required
  • Can write to subscription database, however each time it runs, will overwrite data
  • Locks held during snapshot replication


  • SNAPSHOT AGENT (snapshot.exe)
    • Shared agent for all replication types
    • Initial schema and data scripts
    • Generates scripts for objects
    • Writes to snapshot folder (bcp)
    • Writes command to distributor
    • DISTRIBUTION AGENT (distrib.exe)
      • Read from the snapshot folder
      • Read from distribution database
      • Propagate commands to subscriber end
      • Delete commands from distribution table distribution..MSreplCommands


  • Most used type
  • Replicate incremental changes not all data set
  • Use t-logs to replicate (however full or bulk logged not required)
  • Must have primary key
  • More scalable – less data to transfer
  • Good solution for large data volumes
  • Real time applications – minimal latency
  • Replicate between non SQL, e.g. Oracle. Oracle can be pub/sub – enterprise Ed only.


  • SNAPSHOT AGENT (snapshot.exe)
    • Same as above
    • LOG READER AGENT (logread.exe)
      • Monitor changes in t-logs and propagate to subscribers
      • Data in T-logs moved into > distribution..MSrepl_transactions
      • Then generates commands to run on sub > distribution..MSrepl_commands
      • Commands in binary, use sp_browsereplcommands to read
      • DISTRIBUTION AGENT (distrib.exe)
        • Same as above

Two type of Trans Replication

  • Updateable subscriptions
    • Introduced SQL 2005
    • When configured (new column added unique identifier and a trigger)
    • Changes at subscriber are propagated back to publisher using MSDTC
    • Can have queued updating subscribers (queue reader agent) when few subscribers and infrequent changes at subs
    • Peer-to-Peer
      • All nodes subscribe and publish to each other (sub and ps on all nodes)
      • Enterprise/Developer Edition only
      • Scale out solution, multiple databases, multiple servers (up to 10 db’s)
      • Clients can connect to any node
      • Can remove databases or a node, others will be ok.
  • Can add new node without stopping others. SQL 2008 onwards! SQL 2005 have to suspend all activity
  • Peer-to-peer topology wizard or sp_addsubscription (specify LSN#)
  • Can detect conflicts, enabled by default, stops processing on one node
  • After config, you cannot disable peer-to-peer
  • No filtering allowed in peer-to-peer


  • Start with snapshot
  • Changes at pub and sub tracked with triggers
  • Does not propagate intermediate data – performance enhancement
  • Working offline (mobile users) POS
  • When sub requires different partition of data
  • Replicating between different SQL Version (2000-2005-2008)
  • Supports conflict resolution (same a p2p)
  • Supports filtering
  • Merge replicate tables without primary keys

When you configure MERGE Replication

  • Creates insert/update/delete triggers in published tables
    • Msmerge_ins_guid
    • Msmerge_upd_guid
    • Msmerge_del_guid
    • Creates sp, views and conflict tables
    • Creates unique id field if it does not exist


  • Sys_admins have to enable a database for replication. Right click replication – publisher – properties
  • Db_owners – can create publications

Configuration of Distributor

  • Configure the distributor first! If you configure the publisher it will ask for details
  • Right click replication – create distributor
  • Select server
  • Configure snapshot folder
  • Configure distribution database
  • Add users (who can use distributor) from publisher

Configuration of Publisher

  • Right click local pubs – new publications
  • Select pub type – notice no p2p! Done differently – see next section
  • Select databases
  • Cannot replicate system databases, filestream or new data types in 2005 or DDL changes in SQL 2000.
  • Replicating views require underlying tables – either replicate or create manually
  • Trans replication – not allowed to select tables without primary key
  • Filtered columns must include primary key
  • Static filters – where clause
  • Dynamic filters – different data for different nodes (merge replication only)

Configuration of Subscriber

  • Right click local pubs – new subscription
  • Select publisher
  • Choose push or pull.  If it’s push, agents run on the distributor. Easier for admin. Pull run  agents on subscribers (reduce load)
  • Allocate subscriber database. If select existing db – will recreate all objects – careful.
  • Select users
  • Run on demand / Run continuously / Run at schedule
  • When to synch – now or on first load. Select now if  fewer loads on publisher



  • To configure P2P – you need to configure transactional replication first
  • Right click create tran replication, properties, subscription options – select p2p.
  • After you set to True, you cannot set back to False.
  • Right click transactional publisher now, option configure p2p

Conflict Resolution in MERGE REPLICATION

  • Merge replication, Merge agent detects conflict
  • Resolution based on set of rules
  • Default, publisher always wins
  • Can customize conflict rules
  • Default resolver is pub or sub with highest priority

Conflict Resolution in P2P REPLICATION

  • Previous versions no conflict resolution – introduced in SQL 2008!
  • Replication fails when conflict – fix manually – restart replication
  • 2008 – each row hidden column peer originator
  • Distributor agent compares these id’s – highest value wins

DDL Replication

  • Not supported in SQL 2000. If 2000 is one node, then change will not reflect
  • Ok in SQL 2005 by default (replicate schema changes set to true) – pub properties
  • DDL changes must be done at publisher
  • DDL Changes must be done by T-sql or SMO not SSMS. Cannot drop published objects.


  • Snapshot agent – do not use same as sql agent service – rights too high. Needs read rights to snapshot folder
  • Connecting to publisher – recommend win authentication
  • Publication Access List (PAL) – additional security measure, any login that needs to read data must be in this group. Maintained within Publisher database
  • Account that connects to sub, must be db_owner
  • Account that connects to dist, must be db_owner and PAL

 Replication Retention

  • Trans Retention Period – minim time data kept in dist db (distributor)
  • Max Retention time – max time dat kept in dist db (distributor)
  • History retention – performance meta data (distributor)
  • Pub retention Period – after this time, non-active subs will be dropped (pub)

 Replication Performance

  • Initial snapshot can impact. Use backup / restore
  • Don’t replicate unnecessary objects and columns
  • Transactional replication uses t-logs to read, move to different drive
  • Distributor on a separate server
  • Snapshot agent moves data from pub to distrib – schedule at low peak times
  • Schedule regular intervals instead of continuously
  • Avoid text, filestream columns etc
  • Push vs pull (work is on distrib or on sub)


  • Replication Monitor (sqlmonitor.exe) and Perfmon
  • Perfmon can log data and run for long time
  • Sp_publication_validation, sp_article_validation, sp_table_validation
  • Tracer tokens used to measure latency (Pub to Dist) and (Dist to Sub)
  • Alerts
  • Performance monitor counters
    • Replication agents
      • Number of agents running
  • Replication distribution
    • Info about Dist and Sub
    • Performance between the two
    • Delivered commands, trans/sec and latency
  • Replication log read
    • Info about pub and dist
    • Performance, monitor trans replicaton
  • Replication Merge
    • Conflicts/sec, downloaded changes/sec, uploaded changes/sec
  • Replication Snapshot
    • Info about Pub and Dist
    • Delivered cmds/sec, delivered trans/sec
    • DMV’s – introduced in SQl 2005
    • Four DMV’s for replication
      • Sys.dm_repl_articles – what objects
      • Sys.dm_repl_schemas – what columns
      • Sys.dm_repl_tranhash – trans being replicated
      • Sys.dm_repl_traninfo – tran or cdc info


Thanks for reading.


Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.



  • CMD line (runs outside SQL process)
  • Limited to SQL and Flat files
  • Case sensitive options
  • Use double quotes when export using query
  • No field terminator specified – it will use tab
  • No row terminator specified – it will us line feed (\n)
  • (-F) format files (-X) xml format
  • (-b) batch size, run in parallel, distribute load
  • SQL Server > BCP > Text file and vice-versa

BCP Options (case sensitive)

  • Query  (instead of table)
  • -N (Unicode)
  • -n (native)
  • -c (Character data type)
  • -t (field terminator)
  • -r (row terminator)
  • -f (format file) –x (xml)
  • -F (first row skipped) –L (last)
  • -b (batch size)
  • Native format best for in between SQL Servers
    • Regular native (-n)
    • Unicode native (-N) – best likelihood of transferring all chars in nchar and nvarchar

Bulk Insert

  • T-SQL
  • Only import data
  • No export (like BCP)
  • BULK INSERT (db..table) FROM (datafile) WITH
  • BULK INSERT and BCP work with FLAT FILES only.

Bulk Insert Options

  • ‘char’ – character format
  • ‘native’ – native format (performance, better than char)
  • ‘ widechar’ – Unicode
  • ‘widenative’ – native Unicode format (performance, better than widechar)

Recovery Model + Bulk Operations

  • Full – everything is logged, slower, but recover in full
  • Bulk logged
    • Logs normal things in full, bulk operations minimally
    • Instead of logging details of each row, it records which extents and pages where updated
    • Less I/O = faster
    • Downside – log file does not contain all the information
    • When you back up the log, the changes pages/extents are backed up
    • Log file can remain small, but the backup file could be big
    • You should perform a backup after bulk insert
    • If you had full recovery model, change to bulk, do the import, then change back to full and then backup the log file
    • Minimul recovery requires the following:
      • Not replicated
      • Tablock placed
      • No indices (unless empty)

Optimize Bulk load

  • Multiple batches = each batch = 1 tran
    • Only fails that particular batch
    • Can have multiple clients doing the processing
    • Single batch is default, entire load is success or failure, cannot clear log file until complete
    • Use bulk-logged model
    • Pre-sort to match key

Ad-hoc distributed queries

  • Allows clients to write queries that access external data
  • Enable advanced options ‘ad-hoc distributed queries’, 1
  • Each provider (allow or dissalow)
  • Option is a negative property (tick to disable)
  • Sys_admins can still use even if disabled


  • Access data across a data source
  • Can be used for bulk load (must use format file)
  • Linked server not required
  • Returns a table e.g. select * from openrowset(…)
  • Can be used with INSERT, UPDATE, DELETE


  • Must use a format file
  • Reads entire data file as single value
    • SINGLE_BLOG – large binary
    • SINGLE_CLOG – character value
    • SINGLE_NCLOB – Unicode value
    • Reads data as a single column


  • OPENROWSET – use in place of a table – can pass query
  • OPENDATASOURCE – use in place of instance name/FQ Name
  • OPENQUERY – Pass through query like OPENROWSET but used linked servers!

Linked Servers

  • Sites on top of OLEDB
  • Can specify a connection user
  • Replace instance with (linkedserver.database.schema.table)
    • Sp_addlinkedserver (add the server)
    • Sp_serveroption (configuration)
    • Sp_addlinkedserverLogin (security
    • Data Access Option – true – allows select/insert/update/delete
    • RPC in/out/ – true – allows use of stored procedres
    • Master.sys.server – list of linked servers

Distributed Transactions

  • Changes to remote and local system done as a transaction
  • Select/insert/update/delete on remote servers
  • Uses windows MSDTC
    • Transaction manager manages the across resource manager
    • Resource managers are the servers involved
    • 2 phase commit
      • Prepare – locks and verify that it will work
      • Commit – DTC instructs to commit otherwise rollback


  • Extract, transform and load
  • SSIS is an xml file with .dtsx extension
  • Use BIDS to create
  • Package configuration  
    • XML
    • SQL Table
    • Variable
    • Registry
  • Create SSIS using
    • Import/export wizard
    • Copy database wizard
    • BIDS
    • *maintenance plan
  • SSIS Service
    • One per server even if multiple instances
    • Manages storage, security on package
    • SSMS connect to IS, only uses Win authentication
  • Storage
    • Msdb (sysssispackages)
    • File system (…\100\dts\packages\)
  • Deploying SSIS
    • Deployment wizard – creates manifest file
    • Object explorer – import/export wizard
    • Dtutil – cmd untility
  • MSDB roles
    • Db_ssisadmin
    • Db_ssisltduser
    • Db_ssisoperator
  • Execute SSIS
    • DTExec
    • DTExecUI
    • Object Explorer
    • SQL Agent
    • Programmatically
  • SSIS logging
    • Windows Event log
    • Text files
    • Xml files
    • SQL Tables
    • SQL profiler trace

Copy Database Wizard

  • Only works between SQL Servers
  • Copies entire database
  • Can use for transfer 2000 to 2008
  • Method 1 – Detach/Attach
    • Efficient
    • Source must go offline
    • SQL agent on destination must be able to see mdf/ldf etc.
    • Method2 – SQL Management Object
      • Slower
      • Creates objects then populates
      • Agent service does not require access like method 1
      • Copy wizard also prompts for following:
        • Logins
        • User defined error msgs
        • SP’s in master
        • SQL Server agent definition
        • End points

 Import Export Wizard

  • SQL Server to any format
  • Any format to SQL Server


Thanks for reading.