Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Connection options in SSMS

When connecting to a SQL Server instance in SSMS there are a fair number of options. Most DBAs I know have only ever looked beyond the basics so I thought I would mention a few of the others.

First open a connection window in SSMS: BOL

SSMSConnectionOptions0

This is what most people see and we do have a few options right here.

  1. Server type: This is, as it says, the list of server types that SSMS can connect to. I have to admit, with rare exceptions the only one I use is Database Engine. But then again I’m a fairly plain DBA with very little BI (SSIS only) and no Reporting.
    1. Database Engine
    2. Analysis Services
    3. Reporting Services
    4. SQL Server Compact
    5. Integration Services
  2. Server name: Again, very descriptive. This is the name of the server to be connected to.
  3. Authentication: This area is dependent on the Server type. If for instance you are connecting to a Database Engine then the options are Windows and SQL Server Authentication. If on the other hand you are connecting to a Reporting server then your options are Windows, Basic and Forms Authentication. Etc.

 
Having gone through the basic options hit the “Options>>” button.

SSMSConnectionOptions1

The page is now not only larger but we have two additional tabs. Connection Properties and Additional Connection Properties.

The Connection properties tab is dependent on what “Server Type” you are connecting to. I’m only going to go over the Database Engine properties here. If you use other connection types I highly recommend reviewing what options you have.

Database Engine: BOL

SSMSConnectionOptions2

  1. Connect to database: This allows you to change the database you initially connect to. This overrides the default database.
    This is helpful if your default database has been dropped preventing you from easily connecting to the server or when using a contained database and a loginless user.
  2. Network protocol: TCP/IP, Named Pipes etc. Allows you to be specific about what network protocol you use to connect to the server. This not only overrides the order set by SSCM (SQL Server Connection Manager) but picks one specific network protocol and if it doesn’t work the connection fails.
    I’ve used this a number of times when testing a specific protocol so that I can actually see the failure rather than have the connection fail over to another protocol.
  3. Network packet size: Change the size of the network packets sent.
    I’ve never used this but I imagine it has it’s uses also.
  4. Connection time-out: Change the number of seconds before a connection timeout. The default is 15 seconds.
    This is rather helpful if some network latency is making it difficult to connect.
  5. Execution time-out: Change the number of seconds before an execution timeout. The default is 0 or never.
    Again I’ve never used this but I could see how it would be helpful if you are going to walk away from a long running query and want it to time out if it goes to long.
  6. Encrypt connection: Forces the connection to be encrypted.
    Useful if you need the connection to be encrypted (duh).
  7. Use custom color: Pick a background color for the status bar.
    I typically set this in my registered servers so I don’t mess with it here.

 
Additional Connection Parameters: BOL

SSMSConnectionOptions3

This allows you to add additional connection parameters to the connection string. Personally I’ve never seen a use for this myself although Balmukund Lakhani did.

These connection options are not a constant need but something that DBAs should be aware of for when they are needed!


Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, SSMS Tagged: connection problems, microsoft sql server, SSMS, TCP/IP

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...