Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Smile Unanswered: Sybase 12.0 Auto shutdown on W2K

    Dear DBA,

    I am new to Sybase on W2k, so please forgive me. Is there a script to shutdown databases automatically when the server goes down?

    Thanks
    Santhakumar

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    The server is mounted as a NT service. A Windows shutdown will first stop the services. don't care about that... only avoid to switch off abruptely the mchine... but it's more for Windows than for ASE : ASE is really able to recover this type of bad stop.

    Note that ASe execute a checkpoint (fetching each used pages on memory to the disk) during a normal shutdown... not during a Windows stop... some of your transaction should be rollbacked (but the data will keep consistant)
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by fadace
    The server is mounted as a NT service. A Windows shutdown will first stop the services. don't care about that... only avoid to switch off abruptely the mchine... but it's more for Windows than for ASE : ASE is really able to recover this type of bad stop.

    Note that ASe execute a checkpoint (fetching each used pages on memory to the disk) during a normal shutdown... not during a Windows stop... some of your transaction should be rollbacked (but the data will keep consistant)
    Doesn't a Windows shutdown give problems with identitycolumns? I've found out that a shutdown-command to the ASE-server is a more proper way to shutdown than to just shutdown the machine.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Yes, it's true. Windows send an abrupt interruption (= shutdown with nowait) and not a simple shutdown (that apply the checkpoints) ==> to save the unicity of the identities, the ASE will skip a range of values (by default, 5000... it's a parameter of sp_configure). After the next restart, the identities are increasing by 5000
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by fadace
    Yes, it's true. Windows send an abrupt interruption (= shutdown with nowait) and not a simple shutdown (that apply the checkpoints) ==> to save the unicity of the identities, the ASE will skip a range of values (by default, 5000... it's a parameter of sp_configure). After the next restart, the identities are increasing by 5000
    Because I'm the only DBA over here, and the other Server Administrators have absolutely no clue of how to shutdown ASE proper, I made a batchfile for every DB-server.
    It makes a tiny sql-script with the shutdown-command, takes this as input for a isql-command, and presto, the server shuts down proper.

    We've had several rebootsequences the last months, due to os-patches and stuff, and it works surprisingly well!

    I even made different batches that can disable the automatic start of the SQLserver-services. If there should be multiple reboots they don't have to stop the ASEservice every time.

    It's a pity ASE doesn't have the ability to give shutdown-permission to some login. now you have to make a login with the sa-role, and god knows what a unknowing serveradmnistrator can demolish with that!
    I'm not crazy, I'm an aeroplane!

  6. #6
    Join Date
    Aug 2004
    Posts
    7
    Martijnvs,

    Could post your sample batch file script here, if you have no objection I want to use it for my server.

    Thanks

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by santhakumar
    Martijnvs,

    Could post your sample batch file script here, if you have no objection I want to use it for my server.

    Thanks
    I will be happy to mail it to you, but you'll have to wait another week. I'm currently on my summerholiday, so I'm not able to access my resources at work.
    I'm not crazy, I'm an aeroplane!

  8. #8
    Join Date
    Aug 2004
    Posts
    7
    Quote Originally Posted by Martijnvs
    I will be happy to mail it to you, but you'll have to wait another week. I'm currently on my summerholiday, so I'm not able to access my resources at work.
    Enjoy your summer holiday, I will wait for the script.

  9. #9
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by santhakumar
    Enjoy your summer holiday, I will wait for the script.
    I have the scripts ready to mail them to you, but maybe it's smart to have some contact through msn/irc/icq or something, for some explanations?
    I'm not crazy, I'm an aeroplane!

  10. #10
    Join Date
    Aug 2004
    Posts
    7
    Quote Originally Posted by Martijnvs
    I have the scripts ready to mail them to you, but maybe it's smart to have some contact through msn/irc/icq or something, for some explanations?
    I don't use chat programs. If your are willing to help me please send via email. I will do my best to customise to my environment.

    Thanks

  11. #11
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You could attach it to this thread so that we all can benefit from your contribution!
    Thanks,

    Matt

  12. #12
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Thumbs up

    Quote Originally Posted by MattR
    You could attach it to this thread so that we all can benefit from your contribution!
    I'll attach the scripts to this thread, along with a link to a separate topic with some explanations.
    Give me some time to cough up some nice words and I'll make something nice of it .
    I'm not crazy, I'm an aeroplane!

  13. #13
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Lightbulb

    Allright, it took longer than I intended, but here are the 2 scripts for the correct shutdown, finally .
    Don't mind the occasional Dutch words, the explanation hopefully says enough so you can change them into something you understand yourself.
    In order to make things work you'll have to rename the 2 scripts to .bat instead of .bat.txt (.bat was a invalid filetype for attachments).

    setenv.bat:
    Code:
    rem @echo off
    :: setting Windows NT environment variables
    set SYB_SERVER=<servername>
    set SYB_MAINT_USER=<username>
    set SYB_MAINT_PW=<password>
    set SYB_TMPDIR=<tempdir>
    This script sets some variables used by the second script.
    SYB_SERVER is the name of the Sybase ASE-server (not neccesarily the name of the physical machine...).
    SYB_MAINT_USER is the sybase-loginname for the maintenanceuser. It has to have the sa-role to perform a shutdown.
    SYB_MAINT_PW is the password for the SYB_MAINT_USER .
    SYB_TMPDIR is a directory that can be used to place the temporary files in.
    The words between < and > have to be changed to values appropriate for your own server.

    simple_shutdown.bat:
    Code:
    rem @echo off
    goto INIT
    REM ****************************************************
    REM  Program    : sybmaint.bat
    REM  By         : MFD van S. (PRU) 03/2004
    REM  Version    : 0.1
    REM  
    REM  
    REM ****************************************************
    REM -------------------------------------------------------
    REM          INIT
    REM -------------------------------------------------------
    :INIT
    call %sybase_beheer%\ini\setenv.bat
    REM
    REM  Checks
    REM 
    if "%SYB_SERVER%"=="" goto NOSERVER
    if "%SYB_MAINT_USER%"=="" goto NOUSER
    if "%SYB_MAINT_PW%"=="" goto NOPASSWORD
    REM 
    REM -------------------------------------------------------
    REM          GEN_SCRIPT
    REM -------------------------------------------------------
    :GEN_SCRIPT
    echo use master					>%SYB_TMPDIR%\shutdown.tmp
    echo go						>>%SYB_TMPDIR%\shutdown.tmp
    echo shutdown					>>%SYB_TMPDIR%\shutdown.tmp
    echo go						>>%SYB_TMPDIR%\shutdown.tmp
    goto EXEC_SCRIPT
    REM -------------------------------------------------------
    REM          EXEC_SCRIPT
    REM -------------------------------------------------------
    :EXEC_SCRIPT
    isql -U%SYB_MAINT_USER% -P%SYB_MAINT_PW% -S%SYB_SERVER% -i%SYB_TMPDIR%\shutdown.tmp
    goto END
    REM -------------------------------------------------------
    REM          NOSERVER
    REM -------------------------------------------------------
    :NOSERVER
    echo Error : Server Name is Empty!
    pause
    goto END
    REM -------------------------------------------------------
    REM          NOUSER
    REM -------------------------------------------------------
    :NOUSER
    echo Error : User Name is Empty!
    pause
    goto END
    REM -------------------------------------------------------
    REM          NOPASSWORD
    REM -------------------------------------------------------
    :NOPASSWORD
    echo Error : Password is Empty!
    pause
    goto END
    REM -------------------------------------------------------
    REM          END
    REM -------------------------------------------------------
    :END
    if exist %SYB_TMPDIR%\shutdown.tmp del %SYB_TMPDIR%\shutdown.tmp
    From top to bottom...
    It calls the setenv.bat, setting all neccesary environment variables.
    Then checks if servername, username and password are emtpy. If so, it displays an error (NOSERVER, NOUSER and/or NOPASSWORD).
    Next, it generates the shutdownscript. It echoes a " use master go shutdown go" to a temporary file.
    After the script is generated, it is executes with isql, using the servername, username and password as logininformation, and the temprorary scriptfile as the -i parameter.
    When the execution finishes, the ASE-server has been shut down.
    Finally, it cleans up the temporary scriptfile in the tempdir.

    And presto! A automated shutdownscript!
    I also have an extended shutdownscript that disabled the automated startup of the service after a reboot.
    Optionally, it can insert a date and time in a small table before it shutdowns, so you can monitor the shutdowns.
    Particularly usefull if your systemadministrators should be able to shutdown you DBserver properly on their own without having to know all those difficult utilities like isql .
    And to monitor them if they say they did it properly, but all symptoms say they just pushed the reset switch .

    That, my friends, will be on the next episode of BDOH (Bastard DBA of Hell ).
    Attached Files Attached Files
    I'm not crazy, I'm an aeroplane!

  14. #14
    Join Date
    Aug 2004
    Posts
    7

    Thumbs up

    Thanks Martijnvs,

    I will test it in my environments and post the results. I will do this after the weekend.

    Thanks again for your valuable time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •