Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008

Useful resources for the 70-450 exam

Compare Edition Features
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

SQL Server PowerShell Overview
http://msdn.microsoft.com/en-us/library/cc281954.aspx

Central Management Server
http://www.sqlwebpedia.com/content/sql-2008-central-management-server
There are two tables used to store the database server details:-

msdb.dbo.sysmanagement_shared_server_groups_internal
msdb.dbo.sysmanagement_shared_registered_servers_internal

http://thomaslarock.com/2010/05/sql-2008-central-management-server/
http://sqlcms.codeplex.com/

Understanding Non-uniform Memory Access
http://msdn.microsoft.com/en-us/library/ms178144.aspx

Hardware NUMA,  to find the number of memory nodes available to SQL Server run

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

How to: Configure SQL Server to Use Soft-NUMA
http://msdn.microsoft.com/en-us/library/ms345357.aspx

ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU=1 TO 4
 
Max degree of parallelism Option
http://technet.microsoft.com/en-us/library/ms181007.aspx

General guidelines to use to configure the MAXDOP option
http://support.microsoft.com/kb/329204

Table Partitioning Sliding Window Case
https://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

Partitioned Table and Index Strategies Using SQL Server 2008
http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx

Create Partition Scheme
http://msdn.microsoft.com/en-us/library/ms179854.aspx

sp_estimate_data_compression_savings
Returns the current size of the requested object and estimates the object size for the requested compression state.
http://msdn.microsoft.com/en-us/library/cc280574.aspx

FILESTREAM Storage in SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx

The FILESTREAM directory structure contains
Filestream.hdr - a metadata file describing the data container
$FSLOG directory - the FILESTREAM equivalent of the database transaction log
http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-directory-structure.aspx

Create Statistics
http://msdn.microsoft.com/en-us/library/ms188038.aspx

Clone a database - creating a statistics only copy
http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/21/cloning-in-sql-server-2005.aspx

SQL Server Statistics ebook
http://www.red-gate.com/our-company/about/book-store/sql-server-statistics

Clustered Index Design Guidelines
http://msdn.microsoft.com/en-us/library/ms190639.aspx

Index with Included Columns
http://msdn.microsoft.com/en-us/library/ms190806.aspx

Plan guides (plan freezing) in SQL Server 2005/2008
http://blogs.msdn.com/b/sqlblog/archive/2009/02/19/plan-guides-plan-freezing-in-sql-server-2005-2008.aspx

Guide to SQL Server Isolation Levels
http://www.littlekendra.com/2011/02/08/isoposter/ 


Tips on Optimizing SQL Server Composite Indexes
http://www.sql-server-performance.com/2007/composite-indexes/

Reorganizing and Rebuilding Indexes
http://technet.microsoft.com/en-us/library/ms189858.aspx

SQL Server 2008 Full-Text Search: Internals and Enhancements
http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx

Create Fulltext Stoplist 
To prevent a full-text index from becoming bloated SQL Server discards commonly occurring strings that do not help the search.  For example, words such as "a," "and," "is," and "the" are left out of the full-text index. http://technet.microsoft.com/en-us/library/cc280405.aspx

Contains (Full Text)
http://msdn.microsoft.com/en-us/library/ms187787.aspx

Monitoring Best Practices by Using Policy-Based Management
http://technet.microsoft.com/en-us/library/cc645723.aspx

Enterprise Policy Management Framework
A reporting solution against a desired state defined in a policy
http://epmframework.codeplex.com/

Microsoft® SQL Server 2008 R2 Best Practices Analyzer
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
Import existing policirs, these policies are located in C:\Program Files\Microsoft SQL Server\100\Tools\Policies.

Designing and Understanding DDL Triggers
DDL triggers fire stored procedures in response to an eventin response to a variety of Data Definition Language (DDL) events.
http://msdn.microsoft.com/en-us/library/ms175941.aspx
http://msdn.microsoft.com/en-us/library/ms186406.aspx

An Introduction to SQL Server 2008 Audit
http://www.bradmcgehee.com/2010/03/an-introduction-to-sql-server-2008-audit/

An Introduction to SQL Server 2008 Change Data Capture
Designed to capture insert, update, and delete activity applied to SQL Server tables.
http://www.bradmcgehee.com/2010/04/an-introduction-to-sql-server-2008-change-data-capture/

Change Tracking Overview
A lightweight solution that provides an efficient change tracking mechanism for applications.
http://msdn.microsoft.com/en-us/library/bb933875.aspx

Comparing Change Data Capture and Change Tracking
http://msdn.microsoft.com/en-us/library/cc280519.aspx

Auditing in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

SQL Server Alerts: Soup to Nuts
http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/
sys.messages: contains one row for each system error or warning that can be returned

SQL Server Management Objects (SMO)
A collection of objects for programming all aspects of managing Microsoft SQL Server.
http://msdn.microsoft.com/en-us/library/ms162557.aspx

Create Event Notification
Log and review changes or activiity occuring on the database
http://technet.microsoft.com/en-us/library/ms189453.aspx

Configure a Fail-Safe Operator for Notifications
http://technet.microsoft.com/en-us/magazine/dd408824.aspx

Multi-Server Administration with Master Target jobs
http://www.sqlservercentral.com/articles/Administration/multiserveradministration/675/

Instructions for using SQL Server 2008 in FIPS 140-2-compliant mode
http://support.microsoft.com/kb/955720

Audit a SQL Server Configuration
http://sqlserverpedia.com/wiki/Audit_a_SQL_Server_Configuration

Transparent Data Encryption
http://www.mssqltips.com/tip.asp?tip=1514
http://www.bradmcgehee.com/2008/09/sql-server-2008-transparent-data-encryption/
http://technet.microsoft.com/en-us/library/bb934049.aspx

Cell Level Encrpytion
http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx

SQL Server 2008 Jumpstart Training Materials
http://blogs.msdn.com/b/jbarnes/archive/2008/09/08/free-sql-server-2008-jumpstart-training.aspx

Dynamic Management Views and Functions
http://msdn.microsoft.com/en-us/library/ms188754.aspx

Free Poster – SQL Server Dynamic Management Views
http://kevinekline.com/2010/08/29/free-poster-sql-server-dynamic-management-views/

sp_trace_setevent - SQL Trace historical analysis
http://msdn.microsoft.com/en-us/library/ms186265.aspx

sys.dm_tran_locks
http://msdn.microsoft.com/en-us/library/ms190345.aspx

sp_who and sp_who2
http://www.sqlhacks.com/Administration/Who_vs_who2

Isolation Levels in the Database Engine
http://msdn.microsoft.com/en-us/library/ms189122.aspx

Capturing the Execution Plan
http://sqlinthewild.co.za/index.php/2011/01/04/capturing-the-execution-plan/

Understanding SET STATISTICS IO and SET STATISTICS TIME
http://www.sqlprof.com/blogs/sqlserver/archive/2008/03/23/understanding-set-statistics-io-and-set-statistics-time.aspx

The Data Collector
http://msdn.microsoft.com/en-us/library/bb677248.aspx
http://msdn.microsoft.com/en-us/library/bb677356.aspx

The Reports for Data Collections
http://www.databasejournal.com/features/mssql/article.php/3779846/Reports-for-SQL-Server-2008-System-Data-Collections.htm

Linked Servers
http://msdn.microsoft.com/en-us/library/ms188279.aspx

MS DTC Distributed Transactions
http://msdn.microsoft.com/en-us/library/ms190799.aspx

Scalable Shared Databases Overview
http://technet.microsoft.com/en-us/library/ms345392.aspx

SQL Server Replication

http://msdn.microsoft.com/en-us/library/ms151198.aspx

SQL Server Replication: Providing High Availability using Database Mirroring
http://sqlcat.com/whitepapers/comments/2348.aspx

Disaster recovery solution for distribution database in replication
http://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx

Replication Security Best Practices
http://msdn.microsoft.com/en-us/library/ms151227.aspx

Peer-to-Peer Transactional Replication

http://msdn.microsoft.com/en-us/library/ms151196.aspx

High Availability Solutions Overview

http://msdn.microsoft.com/en-us/library/ms190202.aspx

Log Shipping Deployment
http://msdn.microsoft.com/en-us/library/ms188698.aspx

Database Mirroring Overview
http://msdn.microsoft.com/en-us/library/ms189852.aspx

SQL Server 2008 Failover Clustering
http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

Introduction to Backup and Restore Strategies in SQL Server

http://msdn.microsoft.com/en-us/library/ms191239.aspx

Copy Only Backup

http://www.mssqltips.com/tip.asp?tip=1772

Backup Compression
http://www.keepitsimpleandfast.com/2010/04/backup-compression-in-sql-server-2008.html
http://technet.microsoft.com/en-us/library/bb964719.aspx
To calculate the compression ratio of a backup use
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
Using Mirrored Backup Media Sets

http://msdn.microsoft.com/en-us/library/ms175053.aspx

Backup Devices

http://dbarecovery.com/backup-and-recovery-exercises/sql-server-2005/sql-server-2005-create-backup-device.html
Have a backup strategy document

Data Metaphors

The more articles you read the more you become aware of the use of metaphors in the phenomena of data descriptions. The comparisons which allow visualization of various images of unprecedented data growth seem endless

• Big Data  - http://www.capacent.is/library/Skrar/Analytics/economist-data-20100227.pdf
• Data Avalanche  - discussed http://kevinekline.com/2010/11/09/tn-summit-2010-surviving-the-data-avalanche
• Data Flood  - discussed in The Fourth Paradigm Data-Intensive Scientific Discovery http://research.microsoft.com/en-us/collaboration/fourthparadigm/
• Data Explosion   - The digital universe decade http://www.emc.com/collateral/demos/microsites/idc-digital-universe/iview.htm
• Data Revolution – from social data to open data sets http://giscience.org.uk/2011/03/21/the-open-data-revolution/
• Data Deluge  - http://www.sciencedaily.com/releases/2011/04/110422131123.htm
• Data Tsunami   - http://pubs.amstat.org/doi/pdfplus/10.1198/jasa.2011.ap10508