Performance Data Collection Poster

A summary of the key features

Policy Based Management Poster

A summary of the key requirement.

Central Mangement Server Poster

A summary of functionality.

 

Microsoft SQL Server 2008, Implementation and Maintenance

More exam links for 70-432. This article provides an great set of links to books online and the technical areas covered under the exam.

Implementation and Maintenance

Troubleshooting Performance

A useful chart for iterative processes to deal with common query performance and scalability issues for SQL Server 2005/2008.

troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspx

Useful Links for the MCTS Exam

Useful links for further reading for the MCTS exam.

TS: Microsoft SQL Server 2008, Implementation and Maintenance

http://www.microsoft.com/learning/en/us/exam.aspx?id=70-432

Understanding and Managing the suspect_pages Table
http://msdn.microsoft.com/en-us/library/ms191301.aspx

Tablediff Utility
http://msdn.microsoft.com/en-us/library/ms162843.aspx

How to Rebuild System Databases in SQL Server 2008
http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Rebuilding the SQL Server master database - Part 1
http://www.mssqltips.com/tip.asp?tip=1531
Rebuilding the SQL Server master database - Part 2
http://www.mssqltips.com/tip.asp?tip=1528

SQL Server 2005: View all permissions (2) - Explode the roles! second script works
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

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

Sql Server 2008 Geospatial Data Generator
http://sqlblogcasts.com/blogs/thepremiers/archive/2008/07/09/virtual-earth-gt-sql-server-2008-geospatial-data-generator.aspx

SQL Server Audit Action Groups and Actions
http://technet.microsoft.com/en-us/library/cc280663.aspx

fn_get_audit_file (Transact-SQL)
http://technet.microsoft.com/en-us/library/cc280765(SQL.100).aspx

Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html

Database Audit Specifications in SQL 2008
http://www.sqldbatips.com/showarticle.asp?ID=136

sys.dm_db_index_physical_stats (Transact-SQL)
includes the Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
http://msdn.microsoft.com/en-us/library/ms188917.aspx

Allocation SQL Server Management Studio Add in
http://ssmsallocation.codeplex.com/

Internals Viewer for SQL Server
http://internalsviewer.codeplex.com/

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization
http://ola.hallengren.com/

XML Jumpstart Workbench
http://www.simple-talk.com/sql/t-sql-programming/xml-jumpstart-workbench/

Replication Agent Security Model
http://msdn.microsoft.com/en-us/library/ms151868.aspx

Script to indentifty big replication transactions
http://www.sqlservercentral.com/scripts/Replication/71730/

Using Secondary Servers for Query Processing
http://msdn.microsoft.com/en-us/library/ms189572.aspx

Database Mirroring in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc917680.aspx

Monitoring SQL Server Database Mirroring with Email Alerts
http://www.mssqltips.com/tip.asp?tip=1859

SQL Server Best Practices Article
http://technet.microsoft.com/en-us/library/cc917713.aspx

Alerting on Database Mirroring Events
http://technet.microsoft.com/en-us/library/cc966392.aspx

Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1 (Part 1 to Part 3)http://www.mssqltips.com/tip.asp?tip=1687

SQL Server Perfmon Counters Poster
http://www.quest.com/documents/landing.aspx?id=11635&technology=34&prod=&prodfamily=&loc

Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

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

Who did what?
http://www.sqlservercentral.com/scripts/Monitoring/62467/

Execution Plan Basics
http://www.simple-talk.com/sql/performance/execution-plan-basics/

Performance Tuning
http://www.sqlservercentral.com/search/?q=joseph+sack

fn_trace_gettable (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188425.aspx

ClearTrace
http://www.scalesql.com/cleartrace/

XML Tutorial
http://www.w3schools.com/xml/default.asp

XML Data Modification Language Workbench
http://www.simple-talk.com/sql/t-sql-programming/xml-data-modification-language-workbench/

DDL Event Groups
http://msdn.microsoft.com/en-us/library/bb510452.aspx

DDL Events
http://msdn.microsoft.com/en-us/library/bb522542.aspx

Joe Celko The SQL Apprentice
http://joecelkothesqlapprentice.blogspot.com/

Sorting Months By Number (SQL Spackle)
http://www.sqlservercentral.com/articles/T-SQL/71511/

Creating Your Own Custom Data Collections
http://www.databasejournal.com/features/mssql/article.php/3798221/Creating-Your-Own-Custom-Data-Collections.htm

Clone a SQL Database

Microsoft have written an article which explains how to clone a database. This allows for a copy of the database to be made without any data contained within it. This is advantageous as it protects sensitive data and prevents the need to move large data files. The article is ‘How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 and in SQL Server 2008 at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914288

This is available in SQL Server Management Studio for SQL Server 2005 SP2 and later and SQL Server 2008 and later. It is necessary to script all database objects in the database and change the advanced options setting to include items such as Script Statistics and histograms

Central Management Server

This is a server which contains a central repository of sql servers which can be grouped in various ways. These could be grouped into customers, versions (2008, 2005) or state (Development, Staging, Production).

This group can be used to connect to SQL Server configuration manager, to run queries, object explorer or to evaluate policies for policy based management.

To create a central management server this can done through Registered Servers on the view menu. For more details

The Central Management Server only works in one windows domain, so if there are multiple domains that require to be managed there will need to be multiple Central Management Servers.

SQL Server 2008 R2 System Views Map

This Systems Views Map shows the key system views included in SQL Server 2008 and 2008 R2, and the relationships between them.

It can be downloaded from here

Microsoft SQL Server 2008 Service Pack 2 is released

Microsoft SQL Server 2008 Service Pack 2 is released and can be downloaded

Service Pack 2 contains updates for

SQL Server Utility
Data-Tier Application (DAC)
Integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies
Support for a maximum of 15,000 partitions in a database
It includes SQL Server 2008 SP1 Cumulative Update 1 to 8

Microsoft SQL Server2008 R2 Best Practices Analyzer

SQL Server 2008 R2 Best Practices Analyzer has been released for SQL Server 2008 and SQL Server 2008 R2. It is a diagnostic tool and can be downloaded here.

SQL Server Codenames

I was interested to see what all the SQL codenames were

Sphinx - SQL Server 7.0
Plato - SQL Server 7.0 OLAP Services
Shiloh - SQL Server 2000
Liberty - SQL Server 2000 (64-bit)
Rosetta - SQL Server Reporting Services
Yukon - SQL Server 2005
Katmai - SQL Server 2008
Kilimanjaro - SQL Server 2008 R2
Madison - Microsoft SQL Server 2008 R2 Parallel Data Warehouse
Bulldog - SQL Server 2008 R2 Master Data Services (Master Data Management)

SQL Server Version Numbering

This defines the product version numbering format for SQL Server. It is MM.nn.bbbb.rr which is defined as:
MM - Major version
nn - Minor version
bbbb - Build number
rr - Build revision number

Filestream

There are various types of data

• Structured Data is data which is stored in a relational database

• Semi Structured Data is often of xml type and can be stored in the database

• Unstructured Data is such as images, video etc

Unstructured Data or binary large object (BLOB) data needs to be reviewed to decide where it is stored. BLOB’s smaller than 256kb are better stored inside a database and BLOB’s larger than 1 MB, for filestream, are best stored outside of the database. Storing blobs in the database using the data type varbinary(max) is limited to 2GB per blob.

Filestream allows unstructed data to be stored in the NTFS file system in directories called data containers which are listed as filegroups in the database. This allows transactional consistancy between structured and unstructured data and allows point in time recovery. Deleting or renaming or any filestream files directly in the file system will corrupt the database. Each row has a unique row ID. Fragmentation can occur on the NTFS file system and regular defragmentation is required. Streaming of data through the filestream is more efficent than through the conventional database set up however backing up the database with filestream files is slower than just backing up the database. Each Filestream data container may need to be on it’s own volume to stop data contention. Data stored on the NTFS file system can be compressed but it is expensive if the data is uncompressable. Filestream requires integrated security and filestream cannot block antivirus scanning. If a file is infected it is best to quarantine the file and use DBCC CHECKDB to identify the missing file. Filestream has a garbage collection process to remove files no longer required, which is automatic, when the checkpoint process runs.

Filestream has to be enabled at the windows level before the SQL Server level.

Filestream data can not be encrypted. Database Mirroring does not support filestream although failover clustering does.

Data Collection Security

There are 3 roles

Mdw_admin
Grants read and write to the Management Data Warehouse (MDW). It allows users to purge and clean up jobs to manage the amount of data
Mdw_reader
Grants users with read only access
Mdw_writer
Allows write and upload of data to the MDW

Data Collection

This product is a development of performance dashboard, database reports and built on top of standard DMV's. Historical data is persisted and is not affected by service restarts.

Plan for data growth of up to 250 - 500 MB a day on the Management Data Warehouse (MDW). The default Data Retention Period in MDW is 4 weeks . Microsoft detected approximately 3 - 4% increase in CPU performance when running this application.

The System Data Collection Sets contain 3 types of data collector Disk Usage, Query Statistics and server activity. The data is stored in a Management Data Warehouse (MDW). The data is collected by running SQL Server Agent jobs and have associated SSIS packages.
Disk Usage Data Collection
This captures information about disk usage for data and log files for each database. This can be used to show disk space trends
Query Statistic Data Collection
This collects information about different queries that run on the server and statistics to help in tuning efforts and identify the greatest impact on resource usage
Server Activity Data Collection
Colelcts CPU, Memory and performance counts. Helps identify bottlenecks

Surface Area Configuration tool removed

The Surface Area Configuration tool has been removed from the SQL Server 2008 version. Instead the settings can now be found under facets which is part of the new Policy-Based Management.