Database Integration in UC Server

Version 2

    Database Integration in UC Server


    Integrating a database with UC server can provide additional functionality and flexibility in designing services. This document will explain how to integrate with the two most common external database types, SQL Server and CSV files.

     

    Support Advisory

    ADTRAN Support provides this guide as a courtesy for customers who may not have completed the Advanced UC Training Course or may not have experience with Microsoft ODBC connections.  ADTRAN Support is limited to troubleshooting the UC Application. If problems are encountered with the ODBC connector or with the database itself, please contact Microsoft Support or your local Microsoft Certified Representative.




    Confirming Licensing Requirements


    Database integration is a license attribute that may or may not be included with your server, depending on the package that you purchased. To confirm that this attribute exists in your license, open the UC Client and go to “Help”, then “License Information”

     

    Scroll down to the “Database integration support” attribute. If the value is not set to “Yes”, please contact your reseller to purchase an upgrade key. If the value is Yes, please continue with this guide.

     

     

    Integrating With SQL Server


    Creating ODBC Connection In Windows


    The procedure for integration will be slightly different depending on whether the operating system in use on UC is 32 bit or 64 bit.

     

    For a 32 bit server, use the shortcut defined in Administrative Tools

     

    For a 64 bit server, the data source must still be 32 bit. The file to access this is located at c:\windows\syswow64.  When you access this file, right-click and “run as Administrator”

     

     

    Once the Data Source Administrator window is open, select the “System DSN” tab and click “Add”

     

    Select “SQL Server” from the list of options and click “Finish”. Fill in a name and description that will be used to identify the connection and select the SQL server you wish to connect to, then click “Next”

     

    Leave the authentication options intact and click “Next”.  If using windows Authentication, ensure you are currently logged-in with the UC Service Account.  Check the box to change the default database and select the database that contains the information you wish to use in your UC service. Click “Next”.

     

    Leave the remaining options unchanged and click “Finish”

     

    Upon completion, a window will appear with information on the new ODBC connection that has been created. Click “Test Data Source” to confirm that the connection is set up correctly. If the window comes back with “Tests Completed Successfully”, the ODBC connection is set up correctly. Click “OK” to be returned to the ODBC Data Source Administrator and “OK” again to close the program.

     

     

     

    Linking SQL Database to UC


    Once the ODBC data source has been created in Windows, open the UC Client and go to the “Data Sources” link in the “Administration” tab. Right click in the main window and select “New”, then “ODBC Data Source”

     

    Enter a name to identify the database in UC. Under the “ODBC DSN” drop down menu, the Windows DSN you created should show as an available option. Select it and click “Connect”

     

    If the connection is successful, the display to the right of the “Connect” button should go green and change to “Connected”. The “Table” drop down menu will also change from being grayed out. Use this drop down menu to select the specific table with the UC data.

     

    Once a table is selected, the list of available fields should populate. Select the individual fields you wish to use or click the “All >>” button to populate all fields

     

    Once the appropriate fields have been selected, click “OK”. The data source you created should now appear in the “Data Sources” window in UC. This indicates that the database is now available for use by UC services.

     

     

    Integrating With CSV File


    Some customers may prefer to use a CSV file over a database for a variety of reasons. If using a CSV file, it should be formatted with the first line being the column headings and each following line being comma separated data as in this image.

     

    Creating ODBC Connection In Windows


    The procedure for integration will be slightly different depending on whether the operating system in use on UC is 32 bit or 64 bit.

     

    For a 32 bit server, use the shortcut defined in Administrative Tools

     

    For a 64 bit server, the data source must still be 32 bit. The file to access this is located at c:\windows\syswow64.  When you access this file, right-click and “run as Administrator”

     

    Once the Data Source Administrator window is open, select the “System DSN” tab and click “Add”

     

    Select “Microsoft Text Driver” and click “Finish”

     

    Fill in a name and description for the data source and then uncheck the “Use Current Directory” box. This should permit the clicking of the “Select Directory” button. Navigate to the folder that contains the CSV file and click “OK”. Click “OK” again to close the ODBC Text Setup Window. The new data source should now appear in the System DSN list. Click “OK” to close the window.

     

     

    Linking CSV File to UC


    Once the ODBC data source has been created in Windows, open the UC Client and go to the “Data Sources” link in the “Administration” tab. Right click in the main window and select “New”, then “ODBC Data Source”

     

    Enter a name to identify the CSV file in UC. Under the “ODBC DSN” drop down menu, the Windows DSN you created should show as an available option. Select it and click “Connect”

     

    If the connection is successful, the display to the right of the “Connect” button should go green and change to “Connected”. The “Table” drop down menu will also change from being grayed out. Use this drop down menu to select the CSV file you wish to use.

     

    Selecting the CSV file will populate a list of available fields if the CSV file was formatted correctly. Select individual fields for use in the database connection or click the “All >>” button to select all available fields.

     

    Since CSV files do not contain a native data type like SQL, ensure that the “Data Type” listed for each of the fields correctly corresponds to the data that the field will contain.

     

    Click “OK” to complete the setup. The data source should now appear in the list of sources in the client.