Kendra Thorpe’s VU Blog

I thought this would be pretty straight forward. I needed to move the system databases for my SQL SharePoint named instance to another drive. Seemed simple enough, I found very detailed instructions on http://msdn.microsoft.com/en-us/library/ms345408.aspx. Everything worked until I tried to move the master database. On step three, I couldn’t connect to the DAC in SQL Management studio or sqlcmd. This is because I’m running in a highly available/secure environment. So here are the steps they don’t tell you when you try to connect to the DAC on a clustered named instance with a non-default port:

• Start your instance with the following parameters:
  • NET START MSSQL$instancename /f /T3608
    
• Determine which port the DAC is on.
  • Open the ERRORLOG, and find the entry containing “Dedicated admin connection support was established for listening remotely on port xxxx.” Where XXXX is the port number.
• Open SQL Server 2005 Surface Area Configuration
  • Click Surface Area Configuration for Features
  • Click on DAC
  • Enable remote DAC
  • Click OK
• Open SQL Server Configuration Manager, and create a new alias for the DAC.
  • Right-click and select New Alias
  • I used the alias name DAC, but this value can be anything you want. Just note it for later.
  • Port number is the port number you got from step two.
  • Protocol is TCP/IP
  • Server is IPAddress\INSTANCENAME
  • Click OK
• Open SQL Server Management Studio, click cancel on the logon screen. I found a blog that really helped me with this step. Shoutout to http://chopeen.blogspot.com/2006/08/dedicated-administrator-connections-are.html.
  • Click New Query
  • Type your alias name from the previous step in the Server Name field
  • You can logon either using SQL or Windows accounts, it depends on how you have your authentication setup.
  • Click OK.

*NOTE: Make sure you turn off “ENABLE REMOTE DAC” when you are done.

I hope this saves someone else 2 hours in the middle of the night when you GOTTA get your server up and functioning.