Testing a SQL database connection

“Error: Cannot connect to database server X”

How many times have you seen this error? You can ping the database server, and are *certain* the connection works. Here’s a way to verify:

Login to the web server as the user that needs to connect to the database.

1) Open notepad and save a blank text file as “connection.udl” to your desktop.
2) Right-click on connection.udl, and choose “Open” from the menu. This will open the a “Data Link Properties” window.
3) Choose the “Provider” tab, and ensure that “Microsoft OLE DB provider for Sql Server” is selected (if you are connecting to SQL Server.)
4) Click on the “Connection” tab.
5) In the “Select or enter a server name:” field , enter the name of your database server.
6) Ensure “Use Windows NT Integrated security” is selected.
7) Click the drop-down box below the “Select a database on this server” section.

If your credentials are working, this drop-down list will now be populated with a list of databases your credentials can access. This is the first indication you have proper access. To be certain, click the “Test Connection” button and fully validate the connection.


Result of enabling AWE on 8GB SQL Server

AWE enables SQL Server to use ram beyond 4GB in a 32-bit architecture. Here is the “before” and “after” states on a SQL Server with 8GB of RAM (where SQL Server was allocated 7GB). Note the 272MB available before AWE and the 6.79GB available AWE was enabled.

Turn off default tracing in SQL Server

SQL Server 2005 runs a default trace. Run this query to determine if tracing is enabled:

FROM fn_trace_getinfo(default);

This yields the result set below, where property 5 (“Current Trace status”) is set to a value of 1 (where “1” means on and “0” means off).

To turn off default tracing, run this query:

sp_configure ‘default trace enabled’, 0

To reenable it, run this query:
sp_configure ‘default trace enabled’, 1

About database indexes

“Indexes are most beneficial when they are declared on columns that have many distinct values.” The more unique values that are in the column the faster the query will run. Even though you indexed the column it does not guarantee that SQL server will use the index. If the column does not contain enough unique values, then the sql server query optimizer will resort to a table scan and not use the index.