Q: I can't connect to my SQL Server unless I first map a drive to the server. Is this problem unusual?
A: Chances are you're just having a problem with Windows NT security and
authentication. Sometimes you need to log on and connect to an NT server before you can connect to the SQL Server. This procedure makes sense if you think of SQL Server as one of several resources available on the NT box.
Whether you need to log on to NT depends on which SQL Server Network Library
(Net-Lib) you're using. The four most common Net-Libs are Named Pipes,
Multiprotocol, TCP/IP Sockets, and NWLink IPX/SPX. The first two Net-Libs use
interprocess communications (IPC) mechanisms that require you to log on to NT
before you can access SQL Server; the second two socket-based Net-Libs do not
require you to log on to NT. Now, we don't want you to construe this explanation
to be a primer on NT user account security, but in general, you can connect to a
SQL Server via Named Pipes or Multiprotocol Net-Libs if you can connect
to a network share on the server.
Let's assume you're logged on to your local NT workstation, MyBox, using the
SQLGuy local account. You don't have domain security in place, and you have SQL
Server running on an NT server, DBBox. You can't connect to the SQL Server from
MyBox unless you can first log on to the NT server DBBox. Logging on to the
server requires having a SQLGuy account with matching passwords on both machines
or enabling the Guest account on DBBox.
You can connect to the SQL Server if you first establish a connection to
DBBox by logging on with a different set of credentials from those on the
account you're using on the local machine. You can easily do so by mapping the
network drive to any share on DBBox and specifying a valid username such as
DBBox\Administrator in the Connect As text box. Now you can connect to SQL
Server because you have an open network connection to the remote server running
your database. Just remember that Named Pipes and Multiprotocol both require
that the current user first log on to NT.
TCP and IPX, the two sockets Net-Libs, don't have the same restriction; both
protocols let users connect to SQL Server without first logging on to NT.
Connecting to SQL Server over a socket-based connection is like connecting to
Internet Information Server (IIS) running on a public NT Internet server. By
default, you don't need an NT account to connect to SQL Server because of the
way you implement TCP/IP sockets.
To summarize, Named-Pipes and Multiprotocol Net-Libs both require you to log
on to NT before connecting to SQL Server, but SQL Server doesn't care how you
accomplish that logon. The TCP/IP and IPX/SPX socket-based Net-Libs do
not require you to first logon to NT.
Be aware that, by default, SQL Server uses the Named Pipes protocol. To
change the default for your client machine, use the Client Configuration utility
that loads when you install the SQL Server tools and utilities on your machine.
Q: I'd like to monitor my transaction logs and take corrective action before they fill up, but I don't want to use Windows NT Performance Monitor (Perfmon). Is there any way to track log space used directly from SQL Server?
A:
DBCC PERFLOG is an undocumented command that will give you the information
you're looking for. The problem is you must capture the output so that you can
monitor it in a Transact-SQL (T-SQL) batch. SQL Server 6.5 extends the INSERT
statement in an interesting way that lets you capture the output easily. First,
look at the simple output from DBCC PERFLOG shown in Table 1.
Ahh, so near and yet so far. All the necessary information is right there,
but SQL Server doesn't offer a direct way to extract the Log Size and Log Space
Used information. The task would be simple if Database Consistency Checker
(DBCC) provided a direct way to populate a table with information from PERFLOG
(do you hear us, Microsoft?). Fortunately, however, we can populate the table by
taking advantage of SQL Server 6.5's ability to insert the result set of a
stored procedure into a table as illustrated in the following:
/* Create a wrapper sp to run the DBCC PERFLOG command*/
create proc RunPerfLog
as
dbcc perflog
_go
/* Create a table we can use for tracking log utilization information */
create table PerfLogOutput
(
PerfLogTime datetime default CURRENT_TIMESTAMP,
DBName varchar(30),
LogSize float,
SpaceUsed float,
Status bit
)
go
/* Populate our tracking table */
insert into PerfLogOutput (DBName, LogSize, SpaceUsed, Status)
exec RunPerfLog
go
/* look at the output */
select * from PerfLogOutput
go