<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.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"></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 ><SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > 0.52 was the first to include a <ACRONYM CLASS="ACRONYM" >TDS</ACRONYM > Connection Pooling server. It lives in the <TT CLASS="FILENAME" >src/pool</TT > directory. </P ><P >The <SPAN CLASS="PRODUCTNAME" >FreeTDS</SPAN > connection pool is a server process, it acts just like a <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN >. You can use any program to attach to it that you could use to attach to a real <SPAN CLASS="PRODUCTNAME" >SQL Server</SPAN >. 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" >README</TT > in the pool directory for a more detailed description of its inner workings. </P ><P >To configure the pooling 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 >Next, edit the <TT CLASS="FILENAME" >pool.conf</TT > file 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 the <TT CLASS="FILENAME" >freetds.conf</TT > with a section name in brackets and options for each section in key/value pairs. </P ><P >Just like the <TT CLASS="FILENAME" >freetds.conf</TT > file 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 dataserver.</TD ></TR ><TR ><TD >password</TD ><TD >Any</TD ><TD >none</TD ><TD >The password of the user at the dataserver.</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 dataserver 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 dataserver 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 dataserver.</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 dataserver.</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-6. 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 >Now you can 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 >
