How to Test a Persistent off core SQL Server Connection

Version 16

    Verified Product Versions

    Endpoint Manager 2016.x

    Purpose

     

    The purpose of this article is to connect to an off-core SQL Server without using LANDesk technology to ensure database connectivity. A connection will be created from the LDMS core server to the SQL server using Microsoft’s SQLCMD.exe tool. SQL activity monitor will be used to monitor the connection watching for the connection to drop. If the connection drops it can be concluded that there is an issue with network connectivity between SQL and the Core that will need to be resolved before further troubleshooting can be done.

     

     

    Tools Needed

     

    • Microsoft SQL Server Command Line Query Utility SQL 2008

     

    Microsoft SQL Server 2008 Command Line Utilities

     

    The SQLCMD utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008 instances. The bcp utility bulk copies data between an instance of Microsoft SQL Server 2008 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

     

    Note: This component requires both Windows Installer 4.5 and Microsoft SQL Server Native Client

     

    Microsoft SQL Server 2008 Feature Pack, October 2008

     

    Microsoft SQL Server 2008 Command Line Utilities


    X86 Package
    (SqlCmdLnUtils.msi) - 7234 KB
    X64 Package (SqlCmdLnUtils.msi) - 12212 KB
    IA64 Package(SqlCmdLnUtils.msi) - 16515 KB

     

    Microsoft SQL Server 2008 Native Client


    X86 Package
    (sqlncli.msi) - 4549 KB
    X64 Package (sqlncli.msi) - 7963 KB
    IA64 Package (sqlncli.msi) - 11112 KB

     

    Steps for testing

     

    Core Side

     

    1. Install SqlCmdln Utility on the core server.
    2. Open a command prompt.
    3. Change the directory to \Program Files\Microsoft SQL Server\100\Tools\Binn
    4. Enter the following command string: sqlcmd.exe -S SQLServerName -d DatabaseName -U Username -P Password (example: sqlcmd.exe -S SQL005 -d LDDB -U sa -P Password1 )

    If the connection is successful you will see a 1> on your screen.

     

    sqlcmd.jpg

    For a full list of switches for SqlCmd refer to this MSDN article.

     

    SQL Server Side

     

    1. Open SQL Management Studio and connect to the SQL server in the Object Explorer window.
    2. Expand the Management tree then open the activity monitor. Locate the connection opened by the SQLCMD application.

     

    At this point, you will want to view the activity monitor on the SQL server to see if the connection has stayed up. Viewing the activity monitor at the end of the day or the next day will determine if there have been any disconnects. SQL command line utility will not reconnect if there is a drop either by SQL or network. The application name(SQLCMD) is listed in the application column of Activity Monitor so it is easy to identify the sqlcmd connection.

     

     

    ObjExpl.JPG

    Activity2.JPG