<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <HTML ><HEAD ><TITLE >TDS Connection Pooling</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REL="HOME" TITLE="FreeTDS User Guide" HREF="index.htm"><LINK REL="UP" TITLE="Advanced Configurations" HREF="configs.htm"><LINK REL="PREVIOUS" TITLE="Appending Dump Files" HREF="appendmode.htm"><LINK REL="NEXT" TITLE="stunnel HOWTO" HREF="stunnel.htm"><LINK REL="STYLESHEET" TYPE="text/css" HREF="userguide.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8"></HEAD ><BODY CLASS="SECT1" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" ><SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > User Guide: A Guide to Installing, Configuring, and Running <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="appendmode.htm" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" >Chapter 5. Advanced Configurations</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="stunnel.htm" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="TDSPOOL" >TDS Connection Pooling</A ></H1 ><P >The Connection Pooling server swims in the <TT CLASS="FILENAME" >src/pool</TT > directory.</P ><P >The <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > connection pool is a server process; it emulates a <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN >. Any program that can attach to a real <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > may instead elect to attach to the pool server. The pool in turn connects to the <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > and database you specify, and attempts to share these connections. See the <TT CLASS="FILENAME" >src/pool/README</TT > for a more detailed description of its inner workings.</P ><P >To configure the pool server, first make sure <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > has a working entry for the real <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > by connecting to it with <SPAN CLASS="APPLICATION" >SQSH</SPAN > or another program.</P ><DIV CLASS="NOTE" ><P ></P ><TABLE CLASS="NOTE" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/note.gif" HSPACE="5" ALT="Note"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >The <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > connection pool currently only supports <ACRONYM CLASS="ACRONYM" >TDS</ACRONYM > version 4.2. <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >This restriction applies to both the client-to-pool and pool-to-server connections!</I ></SPAN ></P ></TD ></TR ></TABLE ></DIV ><P >After FreeTDS has been installed, you will find an executable named <B CLASS="COMMAND" >tdspool</B > in the <TT CLASS="FILENAME" >/usr/local/bin</TT > directory (or whatever directory was specified with the <B CLASS="COMMAND" >configure</B > <CODE CLASS="OPTION" >--with-prefix flag</CODE > option).</P ><P >Edit <TT CLASS="FILENAME" >pool.conf</TT > in the <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN >'s <TT CLASS="FILENAME" >etc</TT > directory. The <TT CLASS="FILENAME" >pool.conf</TT > file is formatted like <TT CLASS="FILENAME" >freetds.conf</TT >, with a section name in brackets and options for each section in key/value pairs.</P ><P >Just as in <TT CLASS="FILENAME" >freetds.conf</TT > there are two types of sections, a <TT CLASS="LITERAL" >[global]</TT > section whose options affect all pools, and a section with the name of the pool for pool-specific options. The following options are supported and may appear in either section.</P ><P ><DIV CLASS="TABLE" ><A NAME="TAB.POOL.CONF" ></A ><P ><B >Table 5-1. pool.conf settings</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><THEAD ><TR ><TH >Name</TH ><TH >Possible Values</TH ><TH >Default</TH ><TH >Meaning</TH ></TR ></THEAD ><TBODY ><TR ><TD >user</TD ><TD >Any valid user</TD ><TD >none</TD ><TD >The username used to connect to the servername.</TD ></TR ><TR ><TD >password</TD ><TD >Any</TD ><TD >none</TD ><TD >The password of the user at the servername.</TD ></TR ><TR ><TD >server</TD ><TD >Any <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >TDS 4.2</I ></SPAN > entry in the freetds.conf file</TD ><TD >none</TD ><TD >The alias from the freetds.conf file representing the servername that will be connected to.</TD ></TR ><TR ><TD >database</TD ><TD >Any valid database</TD ><TD >User's default database</TD ><TD >The database on the servername to use.</TD ></TR ><TR ><TD >port</TD ><TD >Any TCP port</TD ><TD >none</TD ><TD >Port on which tdspool will listen.</TD ></TR ><TR ><TD >min pool conn</TD ><TD >1 or more</TD ><TD >none</TD ><TD >Minimum number of open connections to maintain to the servername.</TD ></TR ><TR ><TD >max pool conn</TD ><TD >1 or more</TD ><TD >none</TD ><TD >Maximum number of open connections to open against the servername.</TD ></TR ><TR ><TD >max member age</TD ><TD >0 (no limit) or a number of seconds</TD ><TD >0</TD ><TD >Maximum age of idle members before connection is closed.</TD ></TR ></TBODY ></TABLE ></DIV ></P ><P >Now, let's put this into practice. <DIV CLASS="EXAMPLE" ><A NAME="E.G.POOL.CONF" ></A ><P ><B >Example 5-8. pool.conf</B ></P ><PRE CLASS="PROGRAMLISTING" > [global] min pool conn = 5 max pool conn = 10 max member age = 120 [mypool] user = webuser password = secret database = ebiz server = fooserv max pool conn = 7 port = 5000 </PRE ></DIV > The <TT CLASS="LITERAL" >[global]</TT > section defines that we will open 5 connections against the server initially, and will increase up to 10 as demand requires. These connections will be closed after being idle for 2 minutes (120 seconds), but only until there are 5 remaining open.</P ><P >The <TT CLASS="LITERAL" >[mypool]</TT > section defines a pool named <TT CLASS="LITERAL" >mypool</TT > that will listen on port 5000. It will login to a <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > named <TT CLASS="LITERAL" >fooserv</TT > using the user <TT CLASS="LITERAL" >webuser</TT > and the ever so clever password of <TT CLASS="LITERAL" >secret</TT >. Once logged in, the connections will use the database <TT CLASS="LITERAL" >ebiz</TT > instead of webuser's default database. Also, since this <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN > has a limited number of <ACRONYM CLASS="ACRONYM" >CAL</ACRONYM >s (Client Access Licenses), we are restricting the maximum number of connections to 7, which overrides the <TT CLASS="LITERAL" >global</TT > setting of 10.</P ><P >Run <B CLASS="COMMAND" >tdspool</B > with the name of the pool you are serving. <PRE CLASS="SCREEN" > <SAMP CLASS="PROMPT" >$ </SAMP ><KBD CLASS="USERINPUT" > tdspool mypool</KBD ></PRE ></P ><P >Before your clients connect to the pool, you must edit your <TT CLASS="FILENAME" >freetds.conf</TT > to include the host and port of the pooling server, and point your clients at it.</P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="appendmode.htm" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.htm" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="stunnel.htm" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Appending Dump Files</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="configs.htm" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >stunnel HOWTO</TD ></TR ></TABLE ></DIV ></BODY ></HTML >