SQL Server 2005 Performance Dashboard Reports

This is a wonderful resource to have with SSMS. With SP2 giving you an window to build custom reports use these for your help to diagonize problems. Download from here
http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

SQL Server 2005 Integration Services Guided Tours

Good resource in SSIS

http://www.microsoft.com/sql/technologies/integration/tours.mspx

How to unlock the user with out password reset

there is no direct way... this is a workaround

Alter login [test1] with CHECK_POLICY = OFF
go
Alter login [test1] with CHECK_POLICY = ON
go

SQL Server 2005 Deployment Guidance for Web Hosting Environments - Best Practices

a wonderful article which explain almost all issues in SQL Server 2005

SQL Server 2005 Deployment Guidance for Web Hosting Environments - by Alex DeNeui http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspx

FAQ : How to change Authentication Mode in SQL Server 2005

Trying to connect to SQL Server instance using SQL Server Login (mixed mode Authentication ) when the server is actually configured for Windows authentication is a very common issue for a newbie. How to change authentication mode using Management Studio is as follows :-

• Open management Studio and connect to the server
• Right Click on the Server Name and select the properties
• Select Security Page in the left pane
• On Security page there are two options
o Windows Authentication
o SQL Server and Windows Authentication Mode
• Select whichever the mode you want and apply and close (OK)
• The authentication mode is read from the registry when SQL Server first
Starts up. Therefore, if you change the authentication mode, you must stop
and restart the SQL Server service for it to take effect.

NOTE : RESTART THE SERVICE AFTER THE CHANGE

Third party Tools for SQL Server

Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object


Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare

DTS Comparison
Red Gate – DTS Package Compare

Server Comparison
Quest - ServerCompare

Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition

courtesy to Arnie -- thanks Arnie

Optimizable SARGs in SQL Server

SQL Server’s Query Optimizer examines the conditions within the query’s where clause to determine which indexes are actually useful. If SQL Server can optimize the where condition using an index, the condition is referred to as a search argument or SARG for short. However, not every condition is a “sargable” search argument:

✦ Multiple conditions that are ANDed together are SARGs, but ORed conditions are
not SARGs.
✦ Negative search conditions (<>, !>, !<, Not Exists, Not In, Not Like) are not
optimizable.
It’s easy to prove that a row exists, but to prove it doesn’t exist
requires examining every row.
✦ Conditions that begin with wildcards don’t use indexes. An index can quickly
locate Smith, but must scan every row to find any rows with ith anywhere in the
string.
✦ Conditions with expressions are not SQL Server compliant, so these expressions
will be broken down with the use of algebra to aide with the procurement of
valid input data.
✦ If the where clause includes a function, such as a string function, a table
scan is required so every row can be tested with the function applied to the
data.