Database updates

I needed a method to update a database application across a WAN, and found that I could do this with my Linux firewalls, and Windows batch scripting. The topology is this:

Win2k Svr -> Linux FW -> Internet <- Linux FW <- WinNT Svr Win2k Svr batch file:

rem @echo off
set filename=mydb.log
Net Stop "SQL Anywhere - mydb" >> "%filename%"
attrib -R "C:\Program Files\mydb Software\mydb\mydb.db"
attrib -R "C:\Program Files\mydb Software\mydb\mydb.log"
net use Z: \\win2ksvr\home\backup\backup\mydb admpass /USER:domain\admin
for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set d=%%k%%i%%j
for /F "tokens=1,2,3 delims=:. " %%i in ('time/t') do set t=%%i%%j%%k
xcopy "C:\Program Files\mydb Software\mydb\mydb.log" Z:
call :report %errorlevel% "C:\Program Files\mydb Software\mydb\mydb.log"
xcopy "C:\Program Files\mydb Software\mydb\mydb.db" Z:
call :report %errorlevel% "C:\Program Files\mydb Software\mydb\mydb.db"
net use Z: /delete
Net Start "SQL Anywhere - mydb" >> "%filename%"
goto :eof


:report
if %1==0 set status=OK
if not %1==0 set status=Failed
echo %2 %status% on %d% at %t% >> "%filename%"
set status=
goto :eof

:eof
end

Linux FW Script:

#!/bin/sh
# Source function library.
. /etc/init.d/functions

# Get config.
. /etc/sysconfig/network

# Check that networking is up.
if [ ${NETWORKING} = "no" ]
then
echo "Networking is not setup on this computer."
exit 0
fi


# Check that DSL is up.
host="ameritech.net"
ping -c1 $host 2>&1 1>/dev/null

if [ $? -gt 0 ]; then
echo "Can't ping ameritech.net. We must be down,\nor we are not resolving properly.\nPlease check it out" mail -s "We appear to be down." hostmaster@domain.org
exit 0
fi


## Check that Domain is up.
if ! wget -T 30 -O /dev/null -q http://www.domain.org/index.html; then
# Domain is down
echo "Domain is down, the first page of the \nwebsite is not available" mail -s "Domain is down." hostmaster@domain.org
exit 0
fi

## Zip up the db
cd /home/backup/rambackup/Mydb
if [ -e mydb.db -o -e mydb.log ]; then
zip -9 -m mydb.zip mydb.db mydb.log
cp mydb.zip /mnt/domain1
else
echo "There was no mydb.db or mydb.log file on /home/backup/rambackup/Mydb to zip up. Check and make sure they exist." mail -s "Mydb zipping error." hostmaster@domain.org
exit 0
fi

sftp -C -b /etc/cron.daily/mydb.batch -oUser=smbuser -oIdentityFile=/home/samba/.ssh/id_dsa domain 2> /tmp/mydb >> /tmp/mydb
mail -s "Mydb transfer results." hostmaster@domain.org < /tmp/mydb rm -f /tmp/mydb exit 0

mydb.batch

lcd /home/backup/rambackup/mydb
cd /mydb
put mydb.zip
exit

WinNT Script

rem @echo off
cd "c:\MyDB Update Scripts\"
set filename=update.log

setlocal
set OK=N
for /f "Tokens=*" %%a in ('net stop "SQL Anywhere - MyDB"^|find /i "stopped successfully"')
do (
set OK=Y
)
If "%OK%" EQU "N" (
@echo The MyDB server did not stop successfully. >> "%filename%"
) ELSE (
@echo The MyDB server stopped successfully. >> "%filename%"
)
endlocal


attrib -R "c:\Program Files\MyDB Software\MyDB Fundraising\MyDB.db"
attrib -R "c:\Program Files\MyDB Software\MyDB Fundraising\MyDB.log"
net use P: \\10.0.0.3\MyDB
for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set d=%%k%%i%%j
for /F "tokens=1,2,3 delims=:. " %%i in ('time/t') do set t=%%i%%j%%k
xcopy P:\MyDB.log "c:\Program Files\MyDB Software\MyDB Fundraising\"
call :report %errorlevel% P:\MyDB.log
xcopy P:\MyDB.db "c:\Program Files\MyDB Software\MyDB Fundraising\"
call :report %errorlevel% P:\MyDB.db
net use P: /delete
Net Start "SQL Anywhere - MyDB" >> "%filename%"
goto :eof


:report
if %1==0 set status=OK
if not %1==0 set status=Failed
echo %2 %status% on %d% at %t% >> "%filename%"
set status=
goto :eof