Which Scripting Language for Sybase Administration on Windows?
Summary
This paper considers five scripting options available to automate the administration of Sybase database servers on Windows.
Introduction
A number of scripting options are available to automate the administration of Sybase database servers on Windows. This paper considers five different options:
1. Perl
2. VBScript
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
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.
Emulators include:
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.
For sites intending to write complex batch scripts in the scripting language, its important to take time to review the strengths and weaknesses of each language against functional requirements. At this point, its appropriate to point out that Microsoft has no further plans to develop VBScript although will continue to support the language for many years.
- Team experience
Apart from the DBA’s personal preference and experience, other team members may be supporting the database server and scripts. As many Sybase DBAs are familiar with Perl, large sites with dedicated DBA teams may choose it as a standard scripting language.
For smaller sites the secondary support may be the Windows sysadmins, who are likely to be more familiar with VBScript. If secondary support is provided by an application team with developers familiar with Visual Basic or VBA, then VBScript is an easy transition.
For those with no prior experience in Perl or VBScript, its generally acknowledged that VBScript is quicker to learn.
- Platform mix
Some sites are predominantly Unix/Linux based, with a Windows server set up solely for reporting or testing. If there is also a lack of Windows experience, then such sites may consider (i) running remote shell scripts or (ii) installing a non-Windows environment emulator. We don’t recommend this solution as we think it simply adds scripting and configuration complexity to the relatively straightforward requirements of database server maintenance.
The number of Windows servers may be a defining factor between Perl and VBScript. Some sites prohibit Perl installations due to the additional overhead to configuration maintenance.
For sites that have multiple database platforms (Windows plus Unix and/or Linux), Perl is the only genuine cross-platform scripting language described here. This offers the ability to write some common scripts for database server maintenance. In reality, common scripting is limited by tasks interacting with the operating system (file maintenance, process checking etc) so the DBA will have a set of Perl scripts per platform. We don’t believe that the cross-platform aspect of Perl should be overstated as an advantage except in relation to reducing training overhead: as the number of database scripts required is relatively small, we haven’t experienced problems in maintaining script sets written in both Korn shell and Vbscript.
Submitted: 10 Nov 2006
Author: Caroline Walsh, Five Salmon Consultancy
(c) Five Salmon 2006