Clustering SQL Server 2005 Videos … and some hacking

Brian Knight has posted a lot of good videos on JumpStartTV, including the following clustering videos:

• Windows and SQL Server 2005 Clustering Architecture  (16:14)
  http://www.jumpstarttv.com/windows-and-sql-server-2005-clustering-architecture_31.aspx
• Clustering Windows 2003 R2  (13:26)
  http://www.jumpstarttv.com/clustering-windows-2003-r2_32.aspx
• Clustering SQL Server 2005  (16:21)
  http://www.jumpstarttv.com/clustering-sql-server-2005_33.aspx

Plus one video on how to hack (read: SQL Injection), and prevent hacking, SQL Server:

• Hacking SQL Server   (55:59)
  http://www.jumpstarttv.com/hacking-sql-server_76.aspx

These are good videos, some of them almost an hour long like the Hacking video, most in the 5-15 minute duration. What’s also good about the site is  on the sidebar, the “pre-requisite” videos are mentioned.

JumpStartTV also has videos on Administration, High Availability, Performance Tuning, SMO, Data Warehousing and SSIS (Brian Knight made a whole bunch on SSIS), to name a few.

The videos are free, but JumpStartTV requires registration.

Order of Attribute-Centric Columns in FOR XML PATH matters

Error:

Attribute-centric column ‘@Title’ must not come after a  non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Reason:

You are having this issue if you have attribute- and element-centric columns in your FOR XML PATH query that are interleaved, as such:

SELECT

    EmployeeID       AS '@EmployeeID',            -- attribute

    FirstName        AS 'Details/FirstName',      -- nested element

    LastName         AS 'Details/LastName',       -- nested element

    Title            AS '@Title'                  -- attribute

FROM

    dbo.Employee

FOR XML PATH('Employee')

Resolution:

Order of attribute-centric columns in your FOR XML PATH query matters … if you have nested elements.

To resolve the error above, you must specify all attributes first, then specify the nested elements

SELECT

    EmployeeID       AS '@EmployeeID',            -- attribute

    Title            AS '@Title',                 -- attribute

    FirstName        AS 'Details/FirstName',      -- nested element

    LastName         AS 'Details/LastName'        -- nested element

FROM

    dbo.Employee

FOR XML PATH('Employee')

/*

Sample Output:

<Employee EmployeeID="1" Title="Production Technician - WC60">

  <Details>

    <FirstName>Guy</FirstName>

    <LastName>Gilbert</LastName>

  </Details>

</Employee>

*/

White Papers: Backup/Recovery, Performance Tuning

Here are some older Quest White Papers (specific to SQL Server 2000, but general concepts and practices still applicable to SQL Server 2005/2008):

 

Backup/Recovery

SQL Server Backup Recovery & Troubleshooting. Part 1
SQL Server Backup Recovery & Troubleshooting. Part 2

 

Performance Tuning

Part 1: Analyzing and Optimizing T-SQL Query Performance on MS SQL Server using SET and DBCC
Part 2: Index Tuning Strategies
Part 3: Query Optimization Strategies
Part 4. Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server using SHOWPLAN Output and Analysis

Fixing Collation: The Experiments

We’re trying to solve a few collation issues we have been having, and I had collation overload this week.

The Scenario
•    Some of our SQL Server instanced were installed using the default collation SQL_Latin1_General_CP1_CI_AS
•    We need to have case insensitive, accent insensitive – Latin1_General_CI_AI
•    We changed some of our database collations to Latin1_General_CI_AI

The Problems

•    We want to change the model database’s collation, so that all new databases get the collation we want
•    We want to change the tempdb collation, so that all temporary objects don’t have to use explicit collation

We use temporary tables in some of our important stored procedures and UDFs. It will be an issue if we have different collations between our user databases and tempdb. Yup, trying to do a query that involved these two tables will result in an error similar to:

Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

Background Check