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

FAQ: 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 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.

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.

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 Express 4GB Size Limitation and the Error

I have seen lot of queries regarding the size limitation in SQL Server Express. We all know there is limitaiton and its 4GB. But is it exactly 4GB or when the engine stops you pumping more data into the database? And whats the error you gets? These are the few FAQ on this subject. Then I thought I should try myself and document it.

SQL Server Version

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


(a) When We try to restore a database of size more than 4 GB

RESTORE FILELISTONLY FROM DISK='D:\GP.bak'
RESTORE DATABASE GPtogpx
FROM DISK='D:\GP.bak'
WITH MOVE'Gp' TO 'd:\GP.mdf',
MOVE 'Gp_log' TO 'd:\GP_log.ldf'

Error
Msg 1827, Level 16, State 4, Line 1
CREATE DATABASE or ALTER DATABASE failed because the
resulting cumulative database size would exceed your licensed
limit of 4096 MB per database. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

(b) When a database crosses 4 GB Limit

sp_spaceused Result

database_name database_size unallocated space
-------------------- ------------------------------------
ABC 4216.75 MB 77.16 MB

reserved data index_size unused
------------------ ------------------ ------------------
4115296 KB 4098792 KB 11824 KB 4680 KB

Error

Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database 'GPtogpx' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


So, the bottom line of this study is, the limitation is just not in the paper and it really going to hit you sooner or later if the database going to grow beyond 4GB. So go for Licensed Edition