CA Service Management

 View Only
  • 1.  Configuring SDM to connect to new SQL Server DB

    Posted Aug 29, 2019 03:38 PM

    Hi everyone,

    I think it might be a great idea to share that here as my question might help others who would (or will have) that problem in the future.

    We are in the process of migrating (finally ! our database from SQL Server 2008R2 to SQL Server 2017) and for security purposes our DBAs don't use "instance​ named" database anymore as a way to connect to the database. The port has to be specified.

    I tried moving my actual SDM (14.1.05.1) to the newly copied mdb on the new server using pdm_configure wizard. When entering all credentials for the new SQL Server (server node, database name, sa (and password), mdbadmin (and password), and port). I get an error message impossible to connect to SQL Database.
     
    Configure.log shows this (Installation of CA SDM is in French):

    08/28 15:27:41.612 INFO  SQLDatabasePage.java   520 ## Background Servername () ; Primary Servername () ; Local Servername (MMTRLPALDAPP137)
    08/28 15:27:45.543 INFO  SQLDatabasePage.java   763 Running: sqlcmd -S testServer.local -U sa -P <password> -l 5 -i tmp_sql.sql
    08/28 15:27:51.211 INFO  SQLDatabasePage.java   783 Sqlcmdÿ: erreurÿ: Microsoft ODBC Driver 13 for SQL Serverÿ: Fournisseur de canaux nomm‚s : Impossible d'ouvrir une connexion … SQL Server [2]. .

    08/28 15:27:51.211 INFO  SQLDatabasePage.java   783 Sqlcmdÿ: erreurÿ: Microsoft ODBC Driver 13 for SQL Serverÿ: D‚lai d'attente de connexion expir‚.

    08/28 15:27:51.211 INFO  SQLDatabasePage.java   783 Sqlcmdÿ: erreurÿ: Microsoft ODBC Driver 13 for SQL Serverÿ: Une erreur li‚e au r‚seau ou sp‚cifique … l'instance s'est produite lors de l'‚tablissement d'une connexion … SQL Server. Le serveur est introuvable ou n'est pas accessible. V‚rifiez si le nom de l'instance est correct et si SQL Server est configur‚ pour autoriser les connexions distantes. Pour plus d'informations, consultez la documentation en ligne de SQL Server..

    08/28 15:27:51.211 ERROR SQLDatabasePage.java   791 Failed to check sql user

    It meant "timeout expired" and connection could not be done. If I try to "upgrade" to CA SDM 17.2 using the setup.exe on the DVD I can go through first screen asking for databases credentials (except retrieving "community administrator" for Unified self service) but when it is time to install all the components it fails when it's time to update content in the mdb database. And again configure.log shows the same errors.

    What I found out is that even when defining port number in the pdm_configure wizard or in the installation wizard, all further "sqlcmd" done by SQLDatabasePage.java   never specify the port number in the "sqlcmd" string.

    For example (working outside of SDM in a command prompt)




    Not working

    P:\>sqlcmd -U mdbadmin -P password -S testServer.local -d mdb -Q "Select * from call_req" > C:\z_sqlcmd\test_2_2017.dat

     

    Working (Expecting 55555 a port number for example)

    P:\>sqlcmd -U mdbadmin -P password -S testServer.local,55555 -d mdb -Q "Select * from call_req"  > C:\z_sqlcmd\test_2_2017.dat


    But for now I can't figure out how to have SQLDatabasePage.java write the specific port. I tried adding ,55555 in the database node in pdm_configure but validation fails, I tried changing database node and port number in Nx.env but that didn't change anything.

    Anyone could help for that issue?

    Thanks



  • 2.  RE: Configuring SDM to connect to new SQL Server DB

    Broadcom Employee
    Posted Aug 29, 2019 03:45 PM
    Benoit...........

    None of the Service Desk Manager versions (14.x or 17.x) are certified/supported with SQL Server 2017.

    Are you able to reproduce the behavior with a supported SQL Server version (SQL 2014 for SDM 14.x and SQL 2016 for 17.x)?

    ------------------------------
    Paul Coccimiglio
    Principal Support Engineer
    Broadcom Inc.
    ------------------------------



  • 3.  RE: Configuring SDM to connect to new SQL Server DB

    Posted Aug 29, 2019 04:32 PM
    ​Hi Paul,

    Actually it seems to be problem when the database has no instance and does not use the default port. I tried removing the instance name in SQL2008R2 (using 14.1.05.1) and just using the port and I got exactly the same error. Does SDM support DB without instance specified?

    Thanks

    Benoit


  • 4.  RE: Configuring SDM to connect to new SQL Server DB
    Best Answer

    Broadcom Employee
    Posted Aug 30, 2019 10:57 AM
    Benoit.......

    At a minimum, SDM needs a DEFAULT instance within SQL Server.

    The following KB article might also be helpful:

    https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=45044

    ------------------------------
    Paul Coccimiglio
    Principal Support Engineer
    Broadcom Inc.
    ------------------------------



  • 5.  RE: Configuring SDM to connect to new SQL Server DB

    Posted Aug 30, 2019 11:09 AM
    ​Thanks Paul, since it's on the server, I'll have that KB article looked by our DBAs here.
    Do you have any plan of having SQL Server 2017 as a supported version soon on SDM 17.2 and further on?

    Have a nice day


  • 6.  RE: Configuring SDM to connect to new SQL Server DB

    Broadcom Employee
    Posted Aug 30, 2019 11:14 AM
    Beniot..........

    Certification of SQL Server 2017 is in the certification backlog for our Engineering team.

    Unfortunately, I do not have an ETA as to when this certification will be completed.

    ------------------------------
    Paul Coccimiglio
    Principal Support Engineer
    Broadcom Inc.
    ------------------------------