Microsoft Certification Information

The New Generation of Microsoft Certifications http://www.microsoft.com/learning/mcp/newgen/default.asp

Microsoft Next Generation of Certifications: Frequently Asked Questions http://www.microsoft.com/learning/mcp/newgen/faq/

Microsoft Certifications for IT Professionals

http://www.microsoft.com/learning/mcp/mcitp

MCITP Database Developer

1. SQL Server MCTS (70-431)
2. 70-441 (Designing DB Solution)
3. 70-442 (Designing and Optimizing Data Access

MCITP DB Administrator

1. 70-431 (MCTS)
2. 70-443 Designing a DB Server Infrastructure
3. 70-444 Optimizing and Maintaining a Database Administration Solution

Or If you are upgrading from MCDBA

1. 70-431(MCTS )
2. 70447 ( Database Administration by Using Microsoft SQL Server 2005 )

MCITP: Business Intelligence Developer

1. MCTS: SQL Server 2005 (70-431) +
2. 70-445: PRO: Designing Business Intelligence Solutions by Using Microsoft SQL Server 2005 Analysis Services +
3. 70-446: PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005
   
   
   

SQL Server Connectivity Troubleshooting

Connectivity issues are very common in SQL Server 2005 since there are few settings to be done at server level and OS level to enable the server for remote connection. There are many documents available from Microsoft Protocol team gives step by step troubleshooting process. I always refer those Bolgs whenever I see such issues in forums. Then I thought to have a consolidated list where all the links are available

(a) Check whether the server is configured to accept remote connections. Use SAC to enable server for remote connection
.
(b) Ensure that the SQL Browser service is started (SQL Browser equivalent in 2000 is SQL Server Resolution Protocol (SSRP) )

(c) Determine whether clients are specifying the correct port (for using fixed ports with named instances) in the server alias or connection string.

(d) Check whether the client’s network protocols are enabled and configured to correctly handshake with those of the server.

(e) Be sure you have permission to connect on the server’s endpoints.

(f) When using encryption, be sure the server and client certificates match (that is, check their Common Name (CN) and any other relevant attributes) and are installed and configured correctly on both sides.

(g) Make certain that your firewalls are configured to permit the required network traffic.

(h) Check to see whether your users have permission to log in to the server and access the specified database.

(i) Make sure the provider, driver, DSN, server alias, or other connection mechanism is still valid and hasn’t been altered or removed from the system.

Refer following KBs and Blogs for more info :-
http://support.microsoft.com/kb/914277
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

How to Install SQL Server Client Tools from Command Line

Command line installation of Client Tools
driveletter:\Servers\Setup>start /Wait setup.exe ADDLOCAL=Client_Components,Connectivity,SQL_Tools90,SQL_BooksOnline

How to Detect Deadlocks from SQL Profiler

Deadloack detection is a very common requirement in Development (even in Production) environment. There are couple of method to detect Dead Locks. In SQL Server 2000, trace flag 1204 can be set to capture the processes involved in the deadlock. The output is text based but provides valuable information about the types of locks and the statements that were executing at the time of the deadlock. In addition to this approach, SQL Server 2005 offers the ability to capture detailed deadlock information via the SQL Server Profiler. The process of Dead Lock detection in SQL Server 2005 is as follows

(a) Create a new trace, Select a Blank template; this leaves the selection of all the events, data columns, and filters to you.

(b) Add the Locks:Deadlock graph event to the trace from the Locks category. An additional tab appears on the Trace Properties window, named Event Extraction Settings.

(c) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file. You could also export the results after the trace has been run by using the File, Export option.

Deprecated/Removed Command Line Utilities of SQL Server 2005

Tool Purpose Status

isql Used to execute SQL/SP/Script from Command Prompt Removed

rebuildm This utility was used was used to rebuild the master database. Removed

regrebld This utility was used to back up and restore the Removed
SQL Server Registry entries.

sqlmaint This utility is used to execute maintenance plans that were Deprecated
created in previous versions of SQL Server.

readpipe This utility is used to verify a client’s connectivity to SQL Server Deprecated
through named pipes

Osql Used to execute SQL/SP/Script from Command Prompt Deprecated

makepipe This utility is used to verify a client’s connectivity Deprecated
to SQL Server through named pipes

SQL Server 2005 Pricing and Licensing

Licensing is one area where I always struggle to give my suggestion because it’s a legal issue and the suggestion should politically correct. Check this document right from Microsoft...

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc


Also Check this thread


Developer and Express edition Licensing Model always need little clarificaiton

Developer Edition Licensing

The Developer Edition of SQL Server 2005 is available for a fixed price of $49.95(subjected to change). The Developer Edition is licensed per developer and must be used for designing, developing, and testing purposes only.

Express Edition Licensing

The Express Edition of SQL Server 2005 is available via free download Microsoft site. Developers can redistribute it with their applications at no cost by simply registering for redistribution rights with Microsoft. The Express Edition does not require a CAL when it is used on a standalone basis. If it connects to a SQL Server instance running Enterprise Edition, Standard Edition, or Workgroup Edition, a separate user or device CAL is required unless the SQL Server instance it connects to is licensed under the per-processor model.

Default Trace in SQL Server 2005

When i saw *SQL Trace ID 1 was started by login "sa" * in my Error Log I was little surprised. Since i have never started any trace I was wondering what is this all about. Then i came to know that this is the default trace started which is a new feature introduced in SQL Server 2005. Then i was curious about how can i see the contents. here we go...


To See All the Trace and its location :
select * from ::fn_trace_getinfo(default)

To get the File Location
select * from ::fn_trace_getinfo(default) where Property=2

And copy paste the Value column text.

To Get the Trace Data

SELECT * FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default) where starttime>'2007-10-15'