DAC doen't work with explicit port number

Hello everybody,

After install SQL 2005 I noticed than DAC (Dedicated Admin Connection) is started with dynamic port.

2006-08-08 12:19:47.870 Server Server is listening on [ 'any' <ipv4> 14330].
2006-08-08 12:19:47.880 Server Server is listening on [ 'any' <ipv4> 1433].
2006-08-08 12:19:47.880 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\A ].
2006-08-08 12:19:47.880 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$A\sql\query ].
2006-08-08 12:19:47.900 Server Server is listening on [ 'any' <ipv4> 1212].
2006-08-08 12:19:47.900 Server Dedicated admin connection support was established for listening remotely on port 1212.

My question:
Is possible fix port for DAC?
How can I use DAC behind a firewall if I don't know witch port it will use?

Ok, the BOL say: If SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number:
sqlcmd –Stcp:<server>,<port> (By the way, not is need -A parameter ?)

but it doen't work for me!

Por default connection I have:
C:\sqlcmd -A -SSERVER01\A -Usa -P123456 -dmaster

sys.dm_exec_connections say:
connect_time net_transport client_tcp_port local_tcp_port
-- - --
2006-08-08 12:24:05.750 TCP 3143 1433
2006-08-08 12:49:56.513 TCP 3273 1212

Specifying port I have:
C:\sqlcmd -A -Stcp:SERVER01,1434 -Usa -P123456 -dmaster

but I get "Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired."

PS: I try with any port but always receive timeout !!!

May someone help please..

Thx
Nilton Pinheiro

Hello peoples,

Please....

Is possible fix port for DAC?
How can I use DAC behind a firewall if I don't know witch port it will use?

thx.
Nilton Pinheiro

D.B. Maintenance

I am new to SQL Server. I'm running the initial install of SQL Server 2005 (no SP are installed, ver: 9.00.1399). I have not done any maintenance on my d.b since it's inception (about 3 months ago) and I'm concerned. It seems to be excessively large and I'm not sure about it's integrity. I tried to do some maintenance using the maintenance plan wizard. First of all, what is the best and easiest way to do periodic maintenance on the d.b. (i.e. reindexing, compacting, etc).? Secondly, in trying to use the maintenance plan wizard it failed in the end with the following:

Saving maintenance plan failed:

one or mor arguments are invalid

I'm at a loss as to what to do first. My Windows OS is Windows Server 2003 Standard SP2 completely updated as of today.

I was going to try and upgrade to SQL Server SP2 but that's a project and I'm not sure it will solve anything.

Any help would be greatly appreciated.

First point is to test and upgrade to SP2 in order to get all bug fixes for Maintenance plans.|||

First, you really should backup all of your databases and backup logs for all databases that are not in simple recovery. And do that right away. Then run DBCCs as soon as you can on all databases. There is no need to regularly run any "compacting". You want to size your data and log files to be the appropriate size as needed for daily operations and maintenance and that's it. In terms of reindexing, you want to check for fragmentation and then determine which reindexing operation is appropriate and proceed from there.

You'll probably have to mess with this for a bit to clean things up if you haven't done anything in 3 months - I hope it isn't a production server. Being that you may not have looked at much on that box, you would also want to go through your SQL error logs and the windows event logs to see if you have any other issues on the server that may need your attention.

No one can tell you the best way to do your maintenance as that has some dependencies on your system, the business needs, etc. A good place to start is by going through the System Administration chapter of the Operations Guide (it was written for 2000 but much of it applies to 2005) to get an idea of what all you should be doing and then after you define what you need to do you can explore how you will do it:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx

-Sue