Following on from my initial PowerShell post I decided to expand on that script.

The idea behind this PowerShell script was to obtain and display Windows Server information instantly. I was thinking along the lines of someone walking up to your cubicle and throwing any of the following questions at you:

  • How much disk space do we have on server xxx?
  • When was server xxx last rebooted?
  • Who are the local administrators on server xxx?
  • What shares exist on server xxx?
  • Etc

How quickly could you obtain these answers? How many different tools and utilities would you start firing up?  Would this result in a whirlwind of whirlwind of CTRL + TAB between various applications? The answers to the above questions (and more) could be obtained by using any of the following tools and utilities e.g. 

  • Windows Explorer
  • The Computer Management console
  • VMWare console
  • Event Viewer
  • SCOM
  • SSMS
  • Remote connecting to the server

More than likely you probably have many other scripts, tools or utilities that gather this information daily.  However, what I’m really trying to achieve is to answer all those questions in the minimum amount of key strokes and time. How about 4 keyboard strokes!! Yes, 4!

  1. Keyboard Windows Key (1 key stroke)
  2. Type ‘qc’ (2 key strokes) in the run/search box
  3. Press {enter}  (1 key stroke)

Now that’s fancy! Someone high up and important stands behind you and watches an old school DOS window popup, and all the server information stream out in the same time that it takes them to pull up and chair.

Shorterned example of the output:

PowerShell Quick Server Check

How to install

  1. Download these two files: (quickcheckBATcode) and (quickcheckPScode)
  2. Save the contents of [quickcheckBATfile.docx] into a [qc.bat] file within [c:\windows\system32] folder.
  3. Save the contents of [quickcheckPScode.docx] into a [QuickCheck.ps1] file within [c:\windows\system32] folder.

Note: It’s not best practice to save into this system folder, but we doing this to achieve the minimum number of key strokes as mentioned above. I’m also supplying word files as wordpress has restrictions on what file types can be uploaded. The bat file simply calls the PowerShell (ps1) file.

How to execute

  1. Keyboard Windows Key or click on the Windows Start Icon
  2. Type ‘qc’ in the run/search box and press {enter}
  3. Type in your servername at the prompt

QuickCheck v1 currently returns the following information:

  • Operating System
  • Server Specification
  • Current Disk Space
  • Last Reboot time
  • Network Shares
  • Services (running under a service account)
  • Services (set to automatic, but stopped)

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.



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