Which Scripting Language for Sybase Administration on Windows?
This paper considers five scripting options available to automate the administration of Sybase database servers on Windows.
A number of scripting options are available to automate the administration of Sybase database servers on Windows. This paper considers five different options:
3. Dos batch scripting
4. Unix/Linux scripts running in emulator environments on Windows
5. Unix/Linux scripts running from remote servers
Before getting bogged down in debates on the merits and disadvantages of various scripting languages, remember that database administration tasks primarily require three capabilities: connecting to the database server, executing Transact-SQL, and manipulating files (database dumps, the error log, job log files etc). All five options (and many others) are capable of delivering these basic functions.
The choice comes down to other factors including additional scripting requirements, the DBA and support team experience, and the platform mix at the site. Following a rundown of the different solutions, we consider how these factors influence the ultimate decision.
Perl and Sybperl
The Windows scripting environment (wsh) allows execution of scripts in several scripting languages including Perl. A windows version of Perl is provided by ActiveState (http://www.activestate.com).
The Sybperl module, which provides Sybase open client extensions, is also available for Windows. Originally developed by Michael Peppler, resources and documentation are available from www.peppler.org.
Perl is a popular scripting choice for Sybase DBAs in non-Windows environments, but has gained less ground on Windows platforms. A major factor is that whereas Microsoft’s scripting technologies are installed with Windows 2000, Perl requires installation (and possibly a C++ compiler for compilation). This isn’t particularly difficult, and we provide a short guide to installing Perl and Sybperl as a prerequisite for use of the dbschema database extraction utility.
VBScript is a scripting language very similar to VBA and Visual Basic. It is automatically installed on all Windows 2000 machines (along with Jscript as an alternative scripting choice).
VBScript is a popular choice for general Windows administration, and there are many web resources available for sysadmins. The official Microsoft scripting guide is at http://www.microsoft.com/technet/scriptcenter/guide/default.mspx. Elsewhere on the web, there are plenty of sample scripts for managing Windows servers.
However, database administration tasks require a small portion of the COM automation features used for general server administration. Once the basics are familiar (variables, logic constructs, functions etc), the main area to concentrate on is ADO (ActiveX Data Objects), which is used to connect to the server, execute T-SQL and manipulate result sets (search MSDN for the ADO programmers guide). FSO (the File System Object) will also be employed to manipulate files and folders.
dos batch scripts
Despite the emergence of newer windows scripting technologies, many sites still use dos batch scripts to perform routine database administration tasks. If the main task requirement is to open an isql session and pipe in a SQL script, dos scripts may be appropriate. For testing environments, dos scripts can quickly be set up and scheduled to provide backups, dbcc checks and statistics updates.
For more robust or complex requirements, we recommend that an alternative method be taken. There seems little point in learning a scripting language that has been replaced by the provider (Microsoft) with its newer scripting technologies.
Keeping it Unix: running shell scripts on Windows
There are several Unix and Linux-like environments available for Windows, which allow running of shell scripts. While it is unlikely this option will be chosen purely for database administration tasks, a requirement for complex and robust batch scripting for other purposes may lead to these environments being installed on Windows.
1. Cygwin, an open-source linux emulator available from http://cygwin.com/.
2. MKS Toolkit, a commercially available solution providing a korn-shell type environment.
Keeping it Unix: remote shell scripts
There are many sites who are predominantly Unix or Linux-based but have a small number of Windows servers e.g. for MIS and reporting databases. There may already be a set of robust shell scripts for database server maintenance. In this case, the DBA may simply add the windows ASE server to the interfaces file on one of the Unix/Linux servers and run the scripts against the Windows database server.
All maintenance tasks performed using an isql session (database dumps, dbcc commands, update statistics etc) are suitable for this method. But other tasks must also be considered, tasks that require interacting with the operating and file systems e.g. compressing and moving dump files, checking the error log, checking whether processes are running etc. Splitting the database and non-database functions may simply involve more overhead and effort than its worth when a Windows solution must be found in any case.
Factors to consider when choosing a scripting language
This section discusses a number of factors to consider when choosing the scripting solution.
* Other scripting requirements
As stated in the introduction, routine database administration tasks require a small portion of the features and capabilities of each scripting language. All the options mentioned in this article are capable of performing database backups, updating statistics, checking error logs etc.
However, if there are additional database scripting requirements then Perl may be the better choice. For example, Perl is superior to VBScript and Korn shell scripting when it comes to regular expressions. Routine administration tasks do not require complex string manipulation, but if you want a script that parses multiple sp_sysmon output files to monitor performance trends then Perl is the way to go.