Attachments are all compressed using the standard zip compression algorithm before being imported into the database, so anything that is not already in a compressed format will be significantly reduced in size when stored in the database. (FYI: Documents in pdf format, and graphics in jpg format, for example, are already compressed, so they will take up approximately the same amount of space in the SOS database as they do outside of it.)
Bottom line is that if you use the option to delete the originals after importing, the net storage used will be less than if you just saved the documents separately on your hard disk or network. You also enjoy the benefits of greater security, the convenience of having the attachment information in a handy location, and the peace of mind that your attached documents are backed up along with the rest of your SOS data, with no need for any change in backup strategy or configuration.
Impact on performance of the database should be negligible, assuming appropriate amounts of RAM for the size of your database and modern disk drives.
The following instructions are specifically for SOS Release 2010 and later.
- First, it is necessary to configure your user ID in the Admin Module (Users and Passwords) to allow access from 3rd party products (Access tab option “Grant read-only access from non-SOS products”). If you are not cleared for this type of access, you will have to ask a user with full access to run the query for you. You cannot run queries with the “SUPER” ID; it must be another ID that has the “Grant read-only access” option enabled.
- Launch the ISQL utility by doing Start > Programs > SOS Applications > DBISQL. (An alternate, somewhat less user-friendly version can be launched by Start > Programs > SOS Applications > DBISQLC.)
- When prompted, type your SOS ID and password (you may find that your password will work only if typed all UPPERCASE) in the appropriate fields on the LOGIN tab, then next to ODBC Data Source select or type “SOSDATA” and click OK.
- A screen with multiple windows will open. Type your query in the Command (DBISQLC) or SQL Statements (DBISQL) window and click the Execute button (or press <F9> in either version).
Using the DBISQL version, use Data > Export to save your results in a file. In either version you can add a line to query to do the same. Add a semicolon, then an OUTPUT statement to the query. The following simple query creates a list of patients along with their email addresses and saves it in a “web page” file in the SOS folder called “pt-emails.html”. This HTML file can be viewed in your web browser, or opened, manipulated, and printed using Microsoft Excel:
SELECT lastname, firstname, email
FROM rv_patients
ORDER BY lastname, firstname
; OUTPUT TO c:sospt-emails.html FORMAT HTML
You are not limited to HTML output, by the way. In the example above, the results are output in spreadsheet format because of the keyword LOTUS after FORMAT. By changing the keyword, you can choose from an assortment of output file formats. Among the formats available for your query results are the following:
| Format |
Suggested filename extension (type) |
Keyword
|
Comments
|
|
Plain text
|
TXT
|
TEXT
|
The output is a TEXT format file with one row per line in the file. All values are separated by commas, and strings are enclosed in apostrophes (single quotes).
The delimiter and quote strings can be changed using the DELIMITED BY and QUOTE clauses. If ALL is specified in the QUOTE clause, all values (not just strings) are quoted.
TEXT is the default output type. This output format used to be called ASCII in previous versions of DBISQL. |
|
Fixed width columns
|
TXT
|
FIXED
|
The output is fixed format with each column having a fixed width. The width for each
column can be specified using the COLUMN WIDTHS clause. No column headings are output in this format. |
|
Web page
|
HTM or HTML
|
HTML
|
Open with your web browser. Also can be loaded in Excel and some other spreadsheet applications.
|
|
XML web browser format
|
XML
|
XML
|
Some programs and systems can import and/or process data in XML format.
|
You can save this query with a name by clicking File > Save and run it whenever you want by opening DBISQLC or DBISQL and clicking File > Open to recall it. By convention, queries like this are given names ending in SQL, like EMAILS.SQL, for example. I would suggest that you save it in your SOS folder so you don’t lose it. If you really want to get fancy, you can create a shortcut on your desktop to run your query with one double click (plus typing in your ID, etc. when prompted):
“C:SOSSAbin32DBISQLC
C:SOSEMAILS.SQL“, or “C:SOSSAbin32DBISQL
C:SOSEMAILS.SQL”
Documents you may find helpful include the rather dated, but still relevant, annotated data dictionary: http://www.sosoft.com/files/downloads/sosddct.pdf
and a set of entity diagrams showing links among tables for the most commonly used data: http://www.sosoft.com/files/downloads/sosdpics.pdf
It is most important that you review the final chapter (Accessing SOS Data from Other Programs) in sostech.pdf, located in your SOS folder. This chapter explains the basics of the database organization. You will be lost in doing anything beyond the simplest queries without that foundation.
The upgrade to SOS 2010 is quite similar to previous SOS upgrades that you may have done. It does, however, replace your older Sybase database components with newer versions. In some cases that change will require a couple of post-installation steps, which are detailed below.
All Installations, Including Network Workstations
You may find that one or two obsolete shortcuts remain in your Windows Start > Programs > SOS Applications menu.
The DBISQLG query utility has been replaced by the new DBISQL (no G at the end). The old program shortcut will no longer work and should be removed from the SOS Applications menu. Similarly, the shortcut to bring up the Sybase documentation now has a slightly different name, Sybase SA Manuals. The old Sybase ASA Manuals shortcuts will no longer work, so that shortcut should also be deleted, to avoid confusion.
To delete a menu shortcut, just right-click the shortcut and then click Delete on the pop-up menu. The two menu entries to be deleted are circled in the figure to the left.
Standalone Computer Installations
In most cases, the installation will make all necessary changes for you, except, possibly, the removal of the now obsolete menu items in the Start > SOS Applications menu.
64 Bit Windows Option
If you are running your software on a 64-bit version of Windows, you can choose to run the 64-bit version of the database engine rather than the default 32-bit version. Although either one will work fine in your environment, if you have more than three GB of RAM in your system, you may experience better performance with the 64-bit engine. Changing from one to the other is quite easy:
- Select Start > Programs (or All Programs) > SOS Applications > ODBC Administrator.
- Click the System DSN tab.
- Double-click SOSDATA.
- Click the Database tab.
- In the Start line, scroll to the left, so you can see the beginning of the command.
- Replace the number 32 with 64. The command will now start with c:\sos\sa\bin64\dbeng11.exe
- Click OK to save, then OK again to close the ODBC Administrator applet.
The next time you start SOS, the database will start using the 64-bit engine!
Database Servers
Edit the SERVER.PRM File
In the old version of the Sybase database engine, the recommended network packet size was 1480 bytes. With the new engine, the default and recommended size is 7300 bytes. For best performance, therefore, SOS recommends that you remove the “-p 1480” parameter from your SERVER.PRM file, or change the “1480” to “7300”. In addition, to close a potential security threat, SOS strongly recommends that you add the parameter “TDS=NO”, in parentheses, after “tcpip”. Here is an example:
Modify all Database Startup Shortcuts
Prior to the 2010 release, the database engine was called DBSRV9.EXE and was located in \SOS\ASA\Win32. The new database engine is named DBSRV11.EXE and is located in \SOS\SA\BIN32, with the 64-bit version located in \SOS\SA\BIN64. As a result, old shortcuts will no longer work and should be deleted. There is a shortcut in Start > All
Programs > SOS Applications to start the new database server, using the 32-bit engine (Start SOSDATA Server). You should delete any left-over shortcuts and create new ones by copying the one in the Start menu:
If you are running the database on a 64-bit Windows machine with more than 2 GB of RAM, you will probably see better performance by using the 64 bit engine, so after creating the 32-bit shortcut, just edit its properties as shown below, modifying the Target location from BIN32 to BIN64:
Delete and Re-create the Database Service
You may have been running your database as a windows background service, or perhaps you want to start doing so. The main advantage to running as a service is that the database will start and run whether or not anyone is logged into the server computer’s console. Services created in versions prior to SOS 2010 appear in the Windows Service Manager in the form:
Adaptive Server Anywhere – mysos
where “mysos” is the name you gave to the service when you created it. You must first remove the existing service, if there is one, then create a new one. The new one will appear in the list of services with a name in the form:
SQL Anywhere – mysos
To delete the existing service:
- Open a command window, being careful to use the Run as administrator option if you are working on Vista, Windows 7, or Server 2008. (That is, type CMD in the Start menu Search field, then right-click CMD.EXE in the search results and select “Run as administrator”.)
- Even if you plan to run the 64 bit database engine, change to the \SOS\SA\BIN32 directory:
CD \SOS\SA\BIN32 <enter>
- Assuming that the name of your existing service is “mysos”, delete it with this command:
dbsvc –y –d mysos
To create the new service:
- If you are not already in a command window running with Administrator rights, follow steps one and two above.
- Now create the new service with the following command. The options in this example will set the service to run under the system account (-sa), as a network service (-t network), to start automatically (-s auto), to display the database icon in the system tray (-i), and to be named “mysos” (-w mysos). It will appear in the Windows Services Manager as “SQL Anywhere – mysos”. If you are running the database in 64-bit Windows, using the 64-bit option is recommended, but either version of the engine will work. The 64-bit engine often provides better performance, especially for larger databases. If you are running in 32-bit Windows, you MUST use the 32-bit command. These commands would be typed on a single line, of course. Note that the -i option to display the icon in the system tray won’t work on Vista, 7, or Server 2008 and later, so omit that option if using one of those. Service configuration commands are case sensitive. Type your options exactly as shown (eg: “automatic” will fail but “Automatic” will work). If you still have trouble getting the service created, leave out the “-s Automatic”. You can change the properties to “automatic” from the Windows Services list after it is created.
32-bit Windows:
dbsvc -as -t Network -s Automatic -i -w mysos c:\sos\sa\bin32\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db
64-bit Windows:
dbsvc -as -t Network -s Automatic -i -w mysos c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db
- After executing the command, you will find a new Windows service listed in Windows’ Administrative Tools > Services applet: SQL Anywhere – mysos. You can adjust the properties for the service just as you would for any other service.
For more detailed discussion and instruction for running SQL Anywhere as a Windows service, see:
http://www.sosoft.com/userdocs/?p=30
Adjust Scheduled Tasks
You may be using one or more Scheduled Tasks in Windows to control starting or stopping your database. Normally stopping the database is handled with a –tq parameter in the SERVER.PRM file, such as:
-tq 22:00
to automatically shut down the database at 10:00 pm, but if running as a service, it is possible that you are using a scheduled NET STOP command to do so. More likely, however, if you check your Windows Scheduled Tasks, you will find a task that starts your database server engine each morning, whether you run it as a foreground task, or as a Windows service.
If you have a task to execute a CMD or BAT file that launches the database, find and edit the batch file, replacing the old c:\sos\asa\win32\dbsrv9.exe with the new c:\sos\sa\bin32\dbsrv11.exe (or c:\sos\sa\bin64\dbsrv11.exe) command.
If you are restarting a service, that service no longer exists, so change the command to start the correct service. For example, you might have scheduled the command:
NET START “Adaptive Server Anywhere – mysos”
which now should be:
NET START “SQLANYs_mysos”
This name, of course, should match the name of the service you created above. Check the properties of the new service in your Control Panel > Administrative Tools > Services to be sure of the name. Once you open the Properties dialog for the service, you will see the service name at the top of the first tab:
Windows Server 2008 / Windows 7 –
Windows Servier 2003 –
Network Workstations
After installing the new version of the software on a network workstation, including a terminal server, check the ODBC settings to be sure that the Buffer Size setting on the Network tab is set to 7300 bytes to match the packet size setting on the server. While on this tab, note that specifying the IP address of your database server in the form HOST=123.123.123.123 (using your own server’s address) is sometimes necessary if the workstation cannot otherwise locate and connect to the database server. Unless you are having a problem, do not specify this parameter. Also on the Network tab is an option to Compress network packets. Using this option can make a significant performance improvement on some networks, but can slow things down on others. If your servers and workstations are relatively speedy, but your network is slow, this option should help. On the other hand, if your server and/or workstations are already working pretty hard, and you have abundant network capacity, checking this option may actually slow things down. You will have to experiment to know for sure. Test by timing the generation of large reports.
Sorting
To sort records by a column’s values and replace existing sort conditions that are applied to the current or other column(s), click the target column’s header, until an UP or Down Arrow icon is displayed within the header. The Up and Down Arrows indicate ascending and descending sort orders, respectively.
To sort rows by a column’s values preserving existing sort conditions, do one of the following:
-
Click a column’s header holding the SHIFT key down, until an UP or Down Arrow icon is displayed within the header.
-
Right-click a column’s header and select Sort Ascending or Sort Descending from the context menu.
To remove sorting by a column, click a column header while holding the CTRL key down.
Grouping
Group Data
To group by a column, do one of the following:
Ungroup Data
To ungroup data by a grouping column, do one of the following:
To remove grouping by all columns, right click the group panel and select Clear Grouping from the context menu:
Change Group Order
To change the group order, move a grouping column header to another position within the group panel:
Customizing Summaries
End-users can display and/or hide total and group summaries using the Runtime Summary Editor.
Customizing Total Summaries
If the Summary Panel is displayed within a View, right-click it to invoke its context menu, and select ‘Customize…’.
This invokes the Total Summary Editor, allowing an end-user to manage total summaries.
Customizing Group Summaries
If data grouping is applied, right-click the grouping column’s header to invoke its context menu, and select ‘Group Summary Editor…’.
This invokes the Group Summary Editor, allowing an end-user to manage group summaries.
Sorting Group Rows By Summary
Sort Group Rows By Summary
To sort group rows by summary values (if any), an end-user should do the following:
- Invoke the grouping column’s context menu by right-clicking the column’s header.
-
Select the required summary item (if any), by whose values group rows should be sorted, and the sort order within the ‘Sort By Summary’ submenu, as shown in the image below:
Unsort Group Rows By Summary
Do one of the following:
- Click a column’s header.
- Invoke a column’s context menu and select Clear Summary Sorting.
Filtering
Invoke the Filter Dropdown List
In Table Views, hover over the column header. Click the filter button that appears within the column header.
Create a Filter Condition
To select records that contain a specific value in a specific column/card field, do the following:
-
Invoke the filter dropdown list containing available filter values.
-
Select the required filter value in the filter dropdown list:
The filter dropdown list will be closed immediately, and the control will display the records which contain the specified value in the specified column/card field.
If the filter dropdown list provides check boxes to the left of the filter values, multiple values can be selected (checked) simultaneously. In this mode, click Ok to close the filter dropdown list and apply the filter.
You can sequentially filter data against multiple columns.
Clear the Filter
To clear the filter applied to a column, do one of the following:
Disable/Enable the Filter
Toggle the checked state of the Enable Filter box displayed within the Filter Panel:
Using the Automatic Filter Row
The automatic filter row (if shown) allows end-users to filter data on the fly. To filter data, type text directly into the row. When you input text within the row, a filter condition is automatically created based upon the values entered, and applied to the column.
Showing and Hiding Columns
Hide Columns
To hide a column, drag its header and drop it onto the Column Chooser if it’s open:
Show Columns
Open the Column Chooser, drag the required column’s header from the Column Chooser and drop it onto the column header panel.
Navigating Through Rows and Cells
To move focus between cells and rows, use the ARROW, TAB, HOME, END, PAGE UP and PAGE DOWN keys. Note that the ARROW, HOME and END keys are used for navigation between rows/cards only when the focused cell is not being edited. Otherwise, these keys affect focus movement within the currently edited cell.
To focus the next cell, do one of the following:
- Press TAB.
- In Table Views, press RIGHT ARROW. In Card Views, press DOWN ARROW. If a cell editor is active, pressing RIGHT ARROW moves focus to the next cell if the caret is positioned at the end of the current cell’s text or if the cell’s text is selected in its entirety.
To focus the previous cell, do one of the following:
- Press SHIFT+TAB.
- In Table Views, press LEFT ARROW. In Card Views, press UP ARROW. If a cell editor is active, pressing LEFT ARROW moves focus to the previous cell if the caret is positioned at the beginning of the current cell’s text or if the cell’s text is selected in its entirety.
To focus the first cell within the focused row, press HOME (in Table Views).
To focus the last cell within the focused row, press END (in Table Views).
To focus the first row/card:
- Press CTRL+HOME in Table Views.
- Press HOME or CTRL+Home in Card Views.
To focus the last row/card:
- Press CTRL+END in Table Views.
- Press END or CTRL+END in Card Views.
To move focus to the Automatic Filter Row displayed at the top of a Table View, do one of the following:
- Click this row.
- If the top data row is focused, press CTRL+UP ARROW.
Selecting Rows
Select Individual Rows
To select a row and clear the existing selection, click its data cell or select the row using the ARROW keys.
To select a row and preserve the current selection, click its data cell holding the CTRL key down.
To toggle the focused row’s selected state, do one of the following:
- Press CTRL+SPACE.
- Click the row while holding the CTRL key down.
Select a Range Rows
To select all rows, press CTRL+A. Before pressing this shortcut, ensure that the focused cell is not being edited.
To select a continuous range of rows, you can do the following:
- Use ARROW, PAGE UP, PAGE DOWN keys while holding the SHIFT key down.
- To selects all rows between the currently focused row and another one, click the target row while holding the SHIFT key down.
Resizing Columns
To change a column’s width, drag the right edge of its header:
[This discussion assumes that you are running SOS 2010 or later]
The best and simplest option for getting optimal performance from a large database is to run it on a 64 bit Windows system with an abundance of RAM. Ideally, you would have enough RAM installed in your system so that the entire database could be cached. In that case, allowing a larger cache is simply a matter of increasing the value in the –ch parameter in the STDALONE.PRM or SERVER.PRM file located in the SOS folder on your standalone SOS computer, or your SOS database server computer, respectively. If you have, for example, 12 GB of RAM in your 64 bit system, you could allocate 10 GB of that RAM for potential use as database cache by including:
-ch 10g
in your PRM file.
Windows 32 bit platforms, however, normally would limit you to 1.8 GB, regardless of the amount of physical RAM in the computer. If your are running your database on a 32 bit version of Vista, Windows 7, or Server 2008, you can make a simple modification to quickly boost the maximum RAM you can allocate for cache from 1.8 GB to 2.7 GB:
- Log into the system using an administrator-level account.
- Open a command window.
- Type bcdedit /set increaseuserva 3072
- Restart Windows.
The maximum cache size is configured by inserting or adjusting the –ch setting in the STDALONE.PRM or SERVER.PRM file located in the SOS folder on your standalone SOS computer, or SOS database server computer, respectively. Although you can put most any valid setting in this file, it will be reset to the maximum available based on the usable RAM in your system. For example, if your SERVER.PRM contained the line:
-ch 2700m
indicating a high limit of 2,700 MB (2.7 GB) for database cache, the database engine will adjust it back to 1,800 MB when it starts. After executing the bcdedit command above, however, the defined high limit of 2.7 GB will be accepted by the database engine (2,700 * 1024 = 2,764,800K):
On a system with 4GB of physical RAM, you would not want to allocate more than 2.7 for database cache anyway, so this configuration is the best you can do. It will work reasonably well for databases up to about 5GB in size.
If you happen to have more than 4GB of physical RAM in your 32 bit Windows system running Windows Server Enterprise or Datacenter Edition, there is an option called AWE (Address Windowing Extensions) that can provide access to much more memory for you to use as database cache. If you happen to be in that situation, we would suggest that you call SOS to discuss your options.
The following recommendations should deliver adequate performance in each indicated category. If your implementation is particularly demanding, you want better than just “adequate” performance, or your database is large, you should increase both RAM and processor speed to compensate. Simultaneous usage of resource-hungry applications such as Microsoft Office also demand more RAM.
Windows 32 bit Software
(Compatible with Windows 64 bit)
At present, SOS applications are distributed as 32 bit software. They will, however, install and run in a Windows 64 environment and both 32 and 64-bit database engines are provided. The minimum version of Windows supported by SOS is Windows Vista because XP is no longer supported by Microsoft. That specification includes newer operating systems such as Windows 7, Windows 8, Windows 8.1, Windows 10, Windows Server 2008, Windows Server 2008R2, Windows Server 2012 and Windows Server 2012R2.
Windows Server 2000 and Server 2003 no longer receive security updates and therefore is not supported. SOS users are strongly encouraged to upgrade server operating systems to Windows Server 2008R2 or newer to assure compatibility with the next generation of SOS products.
Standalone Computer or Network Workstation
- A 1 GHz or faster computer with at least 1 GB of RAM, running Windows Vista, Windows 7, Windows 8/8.1, and Windows 10. For satisfactory performance SOS recommends a 1.5 GHz or faster computer with at least 2 GB of RAM.
- A faster, multi-core processor and more RAM is even better, especially if you will be running two or more applications at the same time, or if your SOS database will be large. The ideal is to have enough RAM to match the size of the database running on it.
- A minimum of one GB of free hard disk space for a small practice; much more for large groups and agencies.
- A CD-ROM or DVD drive, or high speed internet connection. The CD or DVD is used only for installation. On networks, a shared drive may be used. Software is generally available for download as well.
- A 17″ or larger CRT or 15″ or larger flat panel display, running 1024 x 768 resolution or higher. Multiple monitor configurations are supported and encouraged.
- TWAIN compatible scanner to scan documents from within SOS applications. WIA compatible scanners can be used, but do not provide either multipage or duplex capabilities. Scanners used by SOS customers include: Docketport 687 card scanner, Canon N670, Xerox Documate 252, and Fujitsu FI-6130. TWAIN is the industry standard scanner interface, so most scanner manufacturers, including Microtek, Umax, VistaScan, HP, Fujitsu have TWAIN models. Note, however, that certain scanner models, even from these manufacturers, do not support the TWAIN standard. For example, HP’s Officejet multi-function printers support WIA, but not TWAIN. Likewise, the popular Fujitsu ScanSnap 500 series uses a proprietary interface and software package that supports neither WIA or TWAIN. If purchasing a new scanner, confirm that TWAIN drivers are included or are available for the desired model and the version of Windows that you are using. Scanners that are not compatible with SOS’s scanner interface can, of course, be used to capture documents as computer files, and those files can be incorporated as attachments to patient records in SOS. Although not quite as convenient, it is still a workable solution.
- Tape backup or a similar robust backup system that permits easy media rotation and off-site storage of archival backups. Depending on the amount of data to be backed up, some installations will be able to use writable CD or DVD. Supplemental internet-based backups are strongly encouraged for redundancy and disaster-recovery purposes, but be sure that the backup vendor conforms to HIPAA requirements, including execution of a Business Associate Agreement. Your backups, whether local or online, must be encrypted.
Network Server
A note concerning the next generation of SOS software: The SOS software currently in development, G5, will be run on web and database servers located on a local network, in a data center, or on a cloud service like Amazon Web Services and Microsoft Azure. If you host the software on your own server or servers, you may need more server resources than with SOS’s current client/server deployment model. If purchasing new hardware these considerations should be factored into your equipment selection. Please see: System Requirements for Next Generation SOS Products
- A server running 32 or 64 bit, Windows Server 2008 R2 or Server 2012 or Server 2012R2 are recommended. It is also possible to use Windows Vista, Windows 7, Windows 8, or Windows 10 as your database server. For best performance and data security, SOS recommends that the designated server computer not be used simultaneously as a workstation and as a server. Performance, database security and integrity may be compromised when the server doubles as a workstation.
- Many SOS customers use virtual server environments such as VMWare and Microsoft Hyper-V, but SOS cannot provide anything beyond the most basic assistance for these environments. If you install on a virtual server and unusual issues should arise, SOS may require that the installation be moved to a traditional non-virtual platform before providing additional assistance. That said, we intend to have a deployment option for our next generation software in the form of a pre-configured, ready-to-run, virtual server package for Microsoft’s Hyper-V and, possibly, for VMWare.
- Servers should have no less than 2 GB of RAM. In general, additional memory is more important than a faster processor, but processor speed and multiple cores/CPU’s are also important, particularly on larger networks. The amount of RAM recommended is directly proportional to the expected size of your database. Large transaction volume organizations should think in terms of a 64-bit platform with at least 4 GB of RAM and the ability to easily add more. The included database engine will use multiple processors or processor cores to improve performance, if present.
- Hard drive space proportional to the size of the organization. Assuming that an average patient account includes about 20 journal entries (charges and credits), you should estimate at least 35K – 70K per patient, or 35 MB – 70 MB per 1,000 patient accounts, plus up to 1 GB for the program files. SOS would recommend that you double this figure to allow ample space for temporary tables, transaction logs, and other needs. Use of both SOS OM (receivables and billing) and CM (clinical records) will increase the amount of drive space required. Large groups and agencies can expect databases of several GB and up.
- Performance and data safety can be enhanced on an active system through the use of multiple hard drives or SSD’s, with the transaction log stored on a second drive in the same server and perhaps the index files on a third drive. Moving the transaction log to a different drive is straightforward but relocating the index files requires unloading and reloading the database with a custom reload script. If you would like to implement a more complex installation of this sort, call SOS to discuss whether such a modification would be worthwhile. By default, all data and transaction log files will be installed a DATA directory located within the SOS program directory on the designated server (SOS Release 2010 and 2013).
- One or more high performance hard disks. Ideally, the database files should be located on a separate, dedicated partition on a drive other than the primary system drive. See document #435 in the SOS Document Library for specifics (http://www.sosoft.com/html/document_library.php).
- If setting up a dedicated database server that will not also be used for file or printer sharing, you may use Windows Vista Business, Windows 7 Pro, Windows 8 Pro, or the comparable version of Windows 10 to host the database. That is also a possibility if setting up a small “peer” network with 10 or fewer users. It is neither required nor recommended (for security reasons) to configure a network share for the database. All communication between the workstations and the database is done through TCP/IP messaging, without any need for users to log into the database server computer. As a result, the Windows Vista, 7, and 8 limit of ten connected users does not apply for SOS database access. Be sure to disable power saving options that will cause the system to go into sleep mode!
- CD-ROM, CD-RW, DVD or DVD-RW drive or high speed internet connection.
- Tape backup or a similar robust backup system that permits easy media rotation and off-site storage of archival backups. Depending on the amount of data to be backed up, some installations will be able to use writable CD or DVD. Supplemental internet-based backups are strongly encouraged for redundancy and disaster-recovery purposes.
- SOS products include SAP/Sybase SQL Anywhere as the back-end database engine. The next generation SOS “Silver” product will permit the database to be run on your existing SQL Server back-end, if preferred.
To as great a degree as possible, the SOS system was designed to be self-correcting. In addition, the database in which your data are stored was specifically chosen for its proven track record for both reliability and performance. Nevertheless, electrical anomalies, hardware failures, and accidents of one kind or another can potentially create difficulties. Here are some situations that might arise and steps you can take to recover.
Database Corruption
If you are unable to start the database and determine that the problem is corruption of the database files, the usual course of action is to:
Before doing anything else, make a copy of all files in the DATA folder under SOS (or other folder, if your data is not located in the default folder). Ideally, burn a copy of the files onto a CD or DVD.
Identify and correct hardware problems (such as insufficient disk space, faulty hard drive, defective hard drive controller, or failing computer power supply) that might have caused the problem.
Restore from your most recent backup. If you backup only the DB files and not the matching LOG file, then be sure to rename or move the current log file before trying to restart the database after restoring your backup.
If the backup starts normally, it may be possible to apply the current log in order to bring it up to date. A technician at SOS can talk you through this process.
If you do not have a good, current, backup, you now know how important it is to make backups and verify them. It may still be possible to recover some or all of your data. Contact SOS to discuss possibilities and costs.
Authentication Error
The program seems to start normally, but as soon as it tries to add or update any data the system stops with an “Authentication error (8001)”.
Your copy of SA is a special “authenticated” edition that limits full usage to programs provided by SOS. These programs contain a special key that must be provided to the database before it will permit the program to modify any information. The details of the key must therefore be present in both the database and the program you are running. If you have just upgraded from a pre-2001 release of the software, there is some chance that the database has not been properly authenticated. You can try to run the SETAUTHN.EXE program (located in your SOS folder) to reinstall the authentication key in the database. Be sure to stop the database and restart it after running this program.
Unable to Connect to Database
On startup, the first thing that an SOS program does is to establish a connection to the database. The normal log-in window appears after the connection has been established. If the ODBC configuration window appears instead, this is a sure sign that the database is not accessible. Listed below are a variety of reasons why this might happen and how the problem can be corrected.
· The ODBC Configuration window will appear if the database cannot be located on a server (network) or started (standalone).
· Improperly Configured Firewall
If you are running firewall software such as Norton Internet Security or ZoneAlarm on your database server system and/or your workstation, temporarily disable it to see if the workstation will connect. If it connects when the firewall is down, but not when it is active, then you must configure your firewall software to permit UDP and TCP/IP traffic among the addresses used by your local network and open port 2638, which is the port used by the database for communication with the workstations.
· Missing or Invalid PRM File
When you run SOS on a standalone (non-networked) computer, it checks a file called STDALONE.PRM (through SOS 2009) in the main SOS directory for additional startup parameters. On a network server (and standalone for SOS 2010 and later) the file name is SERVER.PRM. As shipped from SOS, this file contains some standard parameters that set default packet size and cache size. If the STDALONE.PRM file (or on a server or SOS 2010 + standalone, SERVER.PRM) is not present, you will see an ODBC window similar to the one pictured above because the database engine could not start and the program could therefore not connect to it.
Check to be sure the appropriate PRM file, is present in the \SOS folder. If it is not, then create it with Notepad or another text editor and include the parameter:
-c 50m
or even just a space. Note that the parameter “-c” must be lower case. If you were to type “-C 50M” instead of “-c 50m”, on starting the program you would see a help screen listing all the valid startup parameters and the database would not start. There is no such parameter as uppercase “-C”, hence the failure.
If the file is present, but contains information that the database does not understand, (such as an uppercase parameter) the database engine will not start and the program, not finding the database, will behave as described.
Correct the information in the PRM file and try again.
· Network Workstation (Client) Displays ODBC Window
First you must determine whether the problem is in network communication or in the ODBC setup.
Assuming that the computers on the network communicate with one another using the standard TCP/IP protocol, you should be able to “ping” the server computer successfully. For example, if the IP address of the database server computer is 192.168.0.10, open a command window (Start > Run, then type CMD <enter>) and type the following command:
ping 192.168.0.10 <enter>
In the above text, <enter> means press the Enter key on the keyboard.
If communication with the server is intact, you will see something like this:
If there is a problem with network communication, the ping will not be successful and after a substantial delay, you will see an error message. Contact whoever is responsible for the network and report the situation to him or her.
If the ping is successful, double-check the status of the database server program. It must be running and have successfully loaded the sosdata database. If it has not started successfully, you must troubleshoot that problem first. If you have other workstations, can any of them connect successfully? If not, the problem is probably on the server side.
Very carefully examine the startup messages on the server to be sure that the database actually loaded and started successfully. It is possible for the server software to start but the database to fail. This can happen, for example, if the database has become corrupt or if the database and transaction log do not match. The DB files and the LOG files are a matching set. Having newer DB files or a mismatched LOG file in the directory along with the database files will cause the database to fail. If you think this might be the problem, rename the LOG file and let the database create a new one when it starts.
The server must be using the same network protocol as the workstations. If the server is set to start on the TCP/IP protocol and the the workstations are looking for it on a different protocol, you will never get a connection. Protocols are specified in the command line parameters (usually set in the SERVER.PRM file) on the server, and in the ODBC data source settings on the workstation. The next section of this document contains detailed information about startup settings for both the server and client components.
By default, when you first install SOS on a network workstation, the installation program will configure the “sosdata” ODBC data source to attempt connections on the TCP/IP protocol. If that protocol is not installed on the client, or is installed, but restricted by firewall software installed on the workstation or server, connection will fail. If you have a local firewall such as Norton Internet Security or ZoneAlarm installed, try temporarily disabling it to see if that makes a difference. If it does, then configure it to allow packets to go to and from the SOS programs, the IP addresses involved, and through the ASA port, 2638.
It is essential that the syntax be correct for any entries in the SERVER.PRM file. For example, almost all of these parameters should be specified in lower case (“-X” is not the same as “-x”). An invalid parameter will prevent the database server software from loading.
If you suspect that the parameters may be faulty, open a command prompt window on the server, change to the SOS directory, and type (all on one line):
c:\sos\sa\bin32\dbsrv11 @server.prm c:\sos\data\sosdata.db <enter>
(Substitute the correct directories if those in the example do not match your installation.) If a help window comes up rather than the server starting, there is almost surely a syntax error in your SERVER.PRM file.
To check for an incorrect protocol on the workstation:
1. Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).
2. Click the System DSN tab.
3. Highlight “sosdata” and click Configure.
4. Click the Network tab.
5. Inspect the protocol options. If the wrong protocol is checked, uncheck the inappropriate options and check the desired protocol. (Note that this protocol must be configured and running on your workstation. You can check that by looking at the Network settings in Control Panel.) Make sure that the TCP/IP protocol is installed and that it is bound to your network adapter.
On some systems, it may be necessary to specify the IP address of the system running the database server as the “host” address in the ODBC configuration next to the protocol, as shown in the figure below. In this case, the database is running on the computer configured with the TCP/IP address 192.168.0.32.
Of course, this is just an example. You must determine and substitute the actual address of the system running the database server software.
Testing the Workstation to Server Connection
Before going any farther, find and delete the file named ASASRV.INI in the workstation’s \SOS\SA\BIN32 folder, if it is present. This file contains information about the most recent successful connection and attempts to use it on subsequent connections. If you have changed the protocol you are using on the network, or the TCP/IP address or port on which the database server is running, the information in this file will be incorrect and can prevent a successful connection. During troubleshooting, it is strongly recommended that this file be deleted after every successful connection so that the information contained therein does not “muddy the waters” while you fine-tune your connection parameters.
To test the ODBC connection, use the Test Connection button on the first tab of the sosdata ODBC configuration. You must input an ID and password on the Login tab first. You can use any ID and password that you have set with the “Grant access from third party programs” option. You cannot use the “SUPER” account for this purpose! If you have not yet configured additional accounts, launch the SOS Log-in directly on the server’s console and use the SUPER account (ID = SUPER and initial password = SUPER) to launch the Admin module and create another user account, configured with the “Grant access from third party programs” option.
Network Server
When you start the database server (Start > Programs > SOS Applications > Start SOSData Server) on a Windows-based server, it checks a file called SERVER.PRM in the main SOS directory for additional startup parameters. As shipped from SOS, this file contains a parameter, -p 1460, which sets the network packet size to 1,460 bytes, the appropriate size for most networks, and another (-c 10m) that sets the starting size for the database’s cache. Check to be sure a file with this name is present. If it is not, then create it with Notepad or another text editor. Include these parameters as well as any others you want (see http://www.sosoft.com/fod/doc204-networkguide.pdf).
If running the database over the TCP/IP, it may be necessary to specify the system’s address in the server.prm file, as in this example:
-x tcpip{MYIP=192.168.0.1}
Of course, this is just an example. You must determine and substitute the actual address of the system running the database server software. Specifying the server’s address in this fashion will also eliminate the possibility of the server software trying to start on more than one IP address, which is remotely possible if you have assigned a static IP address to the server but also have a DHCP server running on your network.
Invalid Transaction Log
A corrupt transaction log file (sosdata.log), or a transaction log file that does not match the database, will prevent the database engine (standalone personal server) or server (network) from starting. When an SOS application starts, the first thing it does it attempt to connect to the database. If it cannot access the database, the ODBC configuration window will appear.
Assuming that you have a good backup of the main database files, rename the SOSDATA.LOG file. (SOS recommends that you include the date in the file name, for example: SOS042305.LOG.) You will the log file in the \SOS\DATA directory. It will be set to read-only, so you may have to change the file’s properties using Windows Explorer in order to rename it.
When you restart the database, it should create a new transaction log and load normally.
Do not delete the old transaction log (SOSDATA.LOG) file! Instead, copy it to a CD, label it “Transaction Log” with the date, and store it with your archival backups in a secure, preferably off-site, location. The LOG file serves as a detailed audit trail of database activity and may be required for data recovery or legal purposes.
Standalone Computer
Use of SOS on a standalone computer requires that the ASA personal server (standalone engine) be installed on the same computer as the SOS program and data files. The typical “Standalone” installation from the SOS CD should automatically copy all the required files and properly configure the Windows ODBC Administrator. If the ODBC data source is not configured correctly, SOS will not be able to connect to the database and the program will not run.
Configuring ODBC for Standalone Use
To check the ODBC configuration:
Select Start > Settings > Control Panel > Administrative Tools, then double click the Data Sources (ODBC) icon. When the ODBC Administrator window appears, select the System DSN tab. A window showing currently configured System Data Sources should appear, including a data source with the description “SOSDATA SQL Anywhere 11”.
Make sure the SOSDATA entry is highlighted and select Configure. The fields on each tab should be setup as described below, except that the drive and path references should match the drive and path in which it has been installed on your system. Prompts that do not appear below should be left blank.
| Tab |
Prompt |
Value |
| ODBC |
Data source name |
SOSDATA |
|
Description |
SOS Standalone |
|
Isolation level |
(blank) |
|
Microsoft applications |
(unchecked) |
|
Delphi applications |
(unchecked) |
|
Suppress fetch… |
(unchecked) |
|
Prevent driver… |
(unchecked) |
|
Delay AutoCommit… |
(unchecked) |
|
Describe cursor behavior |
If required |
|
Translator |
<No Translator> |
| Login |
Supply user ID and password |
(selected) |
|
User ID |
Optional. After adding users to SOS, may specify an ID that is has option set to “Grant access from third party programs”. Will eliminate need to type ID when using Crystal Reports and other products, but defeats security and therefore could be considered a HIPAA violation. |
|
Password |
Optional, as above. |
| Database |
Server name |
SOSDATA |
|
Start line |
(all on one line) c:\SOS\SA\BIN32\dbeng11.exe @c:\sos\stdalone.prm c:\sos\data\sosdata.db |
|
Database name |
SOSDATA |
|
Database file |
(blank) |
|
Encryption key |
(blank) |
|
Start database automatically |
option checked |
|
Stop database after last disconnect |
option checked |
| Network |
TCP/IP |
(unchecked) |
|
Shared memory |
checked |
|
Liveness timeout |
(dimmed/disabled) |
|
Idle timeout |
(dimmed/disabled) |
|
Buffer size |
7300 |
|
Compress network packets |
Default is unchecked |
|
…method for encryption… |
None |
| Advanced |
Connection name |
(blank) |
|
Character set |
(blank) |
|
Allow multiple record fetching |
checked |
|
Display debugging… |
(unchecked) |
|
Additional connection parameters |
(blank) |
In this example, additional startup options, such as specifying the initial size of the database cache, are read from the stdalone.prm file located in the c:\sos directory. This is a plain text file that you can edit with Notepad or another text editor. It is strongly recommended that you specify any command line options in this file rather than entering them directly in the ODBC field. If your command line or stdalone.prm file contains any startup options, you might want to remove them to see if the simpler configuration is successful. If it is, then carefully check your options and syntax before putting them back in.
To review options and syntax for the dbeng11 startup command line, go to a system prompt in your \sos\sa\bin32 directory and type dbeng11 /? <enter>
Network Systems
If you are working on a network, program connection problems are often the result of the fact that the database server program has not been started on the server computer or that the workstation has lost its connection to the network.
Are any other stations able to run the program? If everyone has the same problem, then it is surely a problem with the server, its network cable, or the switch or hub to which it is connected.
If the problem is restricted to one workstation, then it is likely to be a workstation issue such as improper network software configuration, a bad cable or interface card, or something of the sort.
Check the server console to be sure that the server is up and that the SOSData database engine is running. You will see a lightning bolt icon in the system tray at the right end of the task bar, unless you are running the database as a background service.
If you are running the database as a background service, bring up Task Manager and check the Processes tab. Make sure the option to display all user processes is checked and look for the dbsrv11 process in the list.
If the database is not running, then start it on Windows servers by selecting the appropriate item on the Start > Programs > SOS Applications menu.
If the SA server is running, then open its status window by double-clicking the icon in the system tray. Scroll up in the window to check for error messages and to be sure that the server is running and the database loaded successfully.
If the SOSData server seems to be loaded, check to be sure that the server computer is running properly. Enter some commands at the server console or return to a workstation and run some other programs that access the server computer’s hard disk to be sure that it responds. If it is hung, follow appropriate shutdown and restart procedures if possible. You should never just shut off or reset a server computer unless there is absolutely no other alternative!
If the SOSData server software appears to be running but none of the workstations can access it, try to verify that all network connections are intact. Can you run other programs from a workstation that access the server’s hard disk?
If the server computer, software, and network are intact, the problem could be an ODBC data source configuration error. Each workstation must have a correctly configured ODBC data source. Correct data source setup is detailed below.
If some stations are able to access the database server, but others are not, or you are just setting up the system for the first time, it could be an ODBC configuration error. In order for the application to establish a connection with the database server, the information in the ODBC configuration must be correct. During installation the setup program attempts to add a correctly configured ODBC entry, but if it failed or was modified after installation, that could be the problem.
You should always start the database from one of the following:
– Start > Programs > Start SOSData Server
– Desktop shortcut that executes the same command as the menu item above
– From a previously configured Windows Service that runs the above command.
Do NOT start the database server by starting an SOS application on the server computer. If you do so, you may find that the database is unloaded when you exit the application on that computer or log off that computer, preventing other workstations from accessing it.
Configuring ODBC for Network Clients
To check the ODBC configuration:
Select Start > Settings > Control Panel > Administrative Tools, then double click “Data Sources (ODBC)”. A window showing currently configured Data Sources should appear. The first tab is User DSN. If you see “sosdata” on this tab, it should be removed. (There are some rare exceptions to this rule, but in no cases should “sosdata” appear on both the User DSN tab and the System DSN tab of the ODBC Administrator.) Select the System DSN tab. You should see a data source with the description “SOSDATA SQL Anywhere 11”.
Highlight the SOSData entry select Configure.
Check each tab and set as described below. Prompts not detailed should be left blank.
| Tab |
Prompt |
Value |
| ODBC |
Data source name |
SOSDATA |
|
Description |
SOS Client |
|
Isolation level |
(blank) |
|
Microsoft applications |
(unchecked) |
|
Delphi applications |
(unchecked) |
|
Suppress fetch… |
(unchecked) |
|
Prevent driver… |
(unchecked) |
|
Delay AutoCommit… |
(unchecked) |
|
Describe cursor behavior |
If required |
|
Translator |
<No Translator> |
| Login |
Supply user ID and password |
(selected) |
|
User ID |
Optional. After adding users to SOS, may specify an ID that is has option set to “Grant access from third party programs”. Will eliminate need to type ID when using Crystal Reports and other products, but defeats security and therefore could be considered a HIPAA violation. |
|
Password |
Optional, as above. |
| Database |
Server name |
SOSDATA |
|
Start line |
(blank) |
|
Database name |
SOSDATA |
|
Database file |
(blank) |
|
Encryption key |
(blank) |
|
Start database automatically |
(disabled) |
|
Stop database after last disconnect |
(unchecked) |
| Network |
TCP/IP |
Checked |
|
Shared memory |
(unchecked) |
|
Liveness timeout |
120 |
|
Idle timeout |
240 |
|
Buffer size |
7300 |
|
Compress network packets |
Default is unchecked |
|
…method for encryption… |
Default is None |
| Advanced |
Connection name |
(blank) |
|
Character set |
(blank) |
|
Allow multiple record fetching |
Checked |
|
Display debugging… |
(unchecked) |
|
Additional connection parameters |
(blank) |
Workstation Loses Connection to Database Server
By default, the server is configured to disconnect any workstation client that has had no activity for four hours. This default client idle timeout period may be modified with the -ti server startup parameter, but client-side idle timeout settings will override the server default (see previous section).
Another process, called liveness timeout can result in disconnects on some systems. Normally, the connection between the client and server is tested every 30 seconds to check to see if the connection is still there. If the test fails four times in a row, the connection will be terminated. The interval can be changed from the default of 120 seconds on the Network tab of the ODBC system data source for SOSDATA on the workstation client. To disable this check altogether, set the liveness timeout to zero.
If the pattern of disconnects is clearly not related to timeouts, you probably have a hardware issue of some sort. In one case a user found a defective CPU fan that apparently was the source of the problem. In another case, there were some environmental, cable, or subtle hardware factors that prevented the network from running reliably with a 100 megabit switch. Replacing the fast switch with a 10 megabit hub solved the problem in that case.
Database Corruption
Using a Security Administrator ID, start the Administration Module, then select Database Tools > DBA Utilities.
Validate Database
When that program comes up, select Tools > Check Database to run the SA DBVALID utilty. DBVALID does a test read of all the data and indices that comprise the database. If any errors are reported, we would suggest that you contact SOS before attempting any correction. It may be necessary to do a database rebuild or we may recommend that you restore your most recent backup and apply the current transaction log. In the event of serious corruption, it may be necessary to send your data to SOS for diagnosis and repair, if possible.
Some of the following utilities are destructive (in the sense that rows in the database may be changed or deleted) so SOS strongly recommends that you do a backup of the database prior to proceeding with any of these steps!
Check Database Integrity
Also on the tools menu in DBAUtils is the selection Check DB Integrity. This utility works through the database, checking for a variety of possible issues. As each step is completed, a check mark will appear next to it. When the last step is done, click the Finish button in the lower right corner of the window.
If any issues were detected, a window will appear listing the issues detected.
Your next step should be to print the summary and detailed reports by clicking the appropriate icons in the toolbar (the printers). If you are sure that you have both a good backup and the reports, click the Repair All icon to attempt automatic fixes of the listed problems. Depending on the problem, this step can take a while.
When the repair is complete, click the scan (magnifying glass) button to repeat the first step to see if any problems remain.
Other Data Repair Utilities
There are several other, more specific, utilities on the Tools menu. It is best to use these with the guidance of an SOS support technician.
Hangs and Lockups
When a program seems to be “locked up” you may see a message that the application is “not responding”, or something to that effect. It is a very common mistake to assume that this message means that the program has stopped functioning. In fact, what this message really means is that the program is not interacting with the user interface (screen, keyboard, and mouse). Sometimes, in fact, this message will appear when a healthy application is just busy working and not locked up at all. The best way to check is to examine the Performance tab of Task Manager. That will show CPU activity. If the CPU is clearly working, you should think long and hard before doing something drastic, such as rebooting the system! The issue is further complicated by the fact that software like SOS sometimes issues a command to the database engine on the server computer and has to wait until that computer has issued a response before it can proceed. That situation will result in the “not responding” condition, but there won’t be any CPU activity in task manager to reassure you that something is happening. You would have to examine CPU activity on the database server to know for sure. There is yet another condition, that should be rare, in which your operation must wait for another user to release a lock on a row or table that you are waiting to update. If everything is working as it should be, this type of user lock scenario should be extremely rare.
That said, there are times when an application can hang. Hangs and lockups can come from a variety of sources. It is essential that you determine whether the problems are seemingly random or reproducible every time you attempt a particular operation in the system.
Reproducible problems
If the system hangs, locks, or crashes every time you try a particular operation, try reinstalling the software, especially if you are on a network system and the problem seems to be unique to your workstation. Make sure that the version you install is the same as that running on the other stations. If that does not seem to make a difference, there may be a programming, configuration, or installation issue that needs to be addressed. Document the problem carefully, including any error messages that appear, and contact SOS tech support.
Random lockups
Seemingly random lockups are often the result of conflicts among the programs that you are running. Check the programs that you are automatically loading when your system starts and eliminate any that are not really essential. Contact your consultant or another computer-savvy individual for assistance with this task.
It is essential that you have ample free space on your hard disk, especially the system drive, if you have more than one disk in your computer. Many programs, including SOS applications, create temporary files that can be quite large. If there is not enough space to store these files, frequent system crashes are inevitable. Check your disk space on your system drive.
Random hangs are sometimes the result of faulty RAM modules. Swap or replace the RAM modules in your system to see if that makes any difference.
A common cause of computer malfunction is heat. If your system acts up after it has been on for a while, the fan or fans in the system may not be turning, or you may have a good deal of dust in the system. If your system has a metal case, do a quick check by just touching the case. It should be cool to the touch. If it is warm or hot, you will have to open it up, blow out the dust with a can of compressed air (best done outside!), and switch it back on with the case off to see if all the fans are turning, including the one on the CPU chip/heat sink.
Even if the case is cool, the CPU fan may not be functioning, so you should check it to be sure it is ok if your system is exhibiting strange behavior.
Other Issues
Check the SOS web site Document Library at http://www.sosoft.com/html/document_library.php to see if there is a document that addresses your issue.
Another good resource is the SOS User Group at Google Groups: http://groups.google.com/group/sosoft. You can search all the messages to see if there has been any discussion of the problem you have encountered.
Those who have a current support contract with SOS are welcome to contact us directly by phone (352-242-9100), fax (352-242-9104, or email (support@sosoft.com) with any questions.
The information in the Backing Up Your Data article is quite sufficient for most installations. The current document discusses advanced topics that will be of particular interest to those running the system in larger organizations with high data entry volume. The following topics include how to recover data entered since the most recent backup and how to backup up a running database. Much of this material is reprinted from the Backup and Data Recovery chapter of the SA Usage manual, with permission from Sybase, Inc. You can read this information directly in the online SA help, accessed from your Start > Programs > SOS Applications menu.
The material below explains how to use SA transaction log files to protect your data, how to make backup copies of your database and log files while the database is running, and how to recover after system and media failures.
Backing Up a Database While It Is Running
In some installations it is desirable to keep the database running 24 hours a day. You cannot backup the running database using commercial backup software, so a special procedure must be used.
You can either:
1. Depend on Microsoft Volume Shadow Copy Services (VSS) to snapshot the database when using third party backup software after configuring your system to run the dbvss11 utility as a Windows service, or
2. (preferred) Use the included live backup utility to make a copy of the database in a separate directory, and backup that copy rather than the running production files in your backup set.
There is a downside to running the database non-stop. There are several maintenance/clean-up procedures that run automatically when the database is started. If you never restart the database, it is possible that you might run into minor issues, such as the inability to log into the system with a particular user ID after that user disconnected from the database in an unusual fashion. See Database Tools in the Admin Module to do the cleanup manually.
The recommended the following live backup procedure:
Create a CMD batch file containing the following commands:
(SOS 2015 and earlier)
C:\SOS\DBTOOLS /V
C:\SOS\DBTOOLS B=<target directory> -y
(SOS 2016 - Requires that you create a backup-only user account in SOS! See the SOSADMIN.PDF document in the \SOS folder of your system.)
c:
cd \sos\sa\bin32
DBBACKUP -c “uid=<account ID>;pwd=<account password>;dsn=SOSDATA” <target directory>
where <target directory> is an existing directory in which you want the copy of the database to be created. This directory must be located on the computer running the database. It should NOT be a shared folder on another computer! Make sure that this target directory is included in your backup configuration so that the database copy is backed up, and set the backup to skip the \SOS\DATA directory in which the running database files are located. Most backup software will fail to make usable backups of a running database!
Here is an example command line, including an optional -y parameter to create
the directory if it doesn't exist, and to overwrite existing files:
DBBACKUP -y -c "uid=BU_BILL;pwd=PASSword#1;dsn=SOSDATA" C:\SOSBU
You can run a database validation or database copy interactively from the menus in the “DBA Utils” progam in the Admin Module. Once the program has started, go to the Tools menu. Run the validation by selecting “Check Database”. You can run an ad-hoc live database copy by choosing “Copy Database” in the Tools menu. Generally, live backups are done as a Windows scheduled task that executes one or more times each day.
Note that for security reasons copying the database is only possible on the computer running the database software.
It is absolutely essential that you monitor your backups closely to be sure that the current database files are being copied to your backup media. If you are using commercial backup software, be sure to turn on the “verify” or “compare” operation and inspect your backup logs every day!
If you plan to backup the running database with Backup Exec, NovaBackup, or some other backup software that uses Microsoft Volume Shadow Copy Services (VSS) to backup open files, then create and run a Windows service for the SA VSS Writer component, DBVSS11.EXE, as follows:
- Open a command window.
- On 32-bit Windows type:
CD \SOS\SA\BIN32 <enter>On 64-bit Windows type:
CD \SOS\SA\BIN64 <enter>
- On 32-bit Windows type
dbsvc -as -s Automatic -t vss -w VSSWriter “c:\SOS\SA\BIN32\dbvss11.exe” <enter>On 64-bit Windows type:
dbsvc -as -s Automatic -t vss -w VSSWriter “c:\SOS\SA\BIN64\dbvss11.exe” <enter>
Once you have created the service, you should see it listed in your Windows Services as
“SQL Anywhere – VSSWriter”
It is absolutely essential that you test your backup during a period when the database is actively in use by restoring your backup, restarting your system, and validating the restored database as described in “Validating the Database”, above .
Live Backup and Database/Log Mirroring
SQL Anywhere also has the capability of running a continuous live backup of the transaction log on a separate computer, and even doing a failover database server running on a second computer. For discussion of these topics please see the appropriate topics in SQL Anywhere Database Administration manual located on your system. Go to Start > Programs > SOS Applications > Sybase SA Manuals. Open the Database Administration manual, then find “Backup and Data Recovery” in the “Maintaining Your Database” section.
System and Media Failures
SA has features to protect your data from two categories of computer failure: system failure and media failure.
System Failure: A system failure occurs when a power failure or some other failure causes the computer or operating system to go down while there are partially completed transactions. This could occur when the computer is inappropriately turned off or rebooted, or when another application causes the operating system to crash.
Media Failure: A media failure occurs when the database file, the file system, or the device storing the database file becomes unusable.
Recovery from Failure
When failures occur, the SA recovery mechanism treats transactions properly, as atomic units of work: any incomplete transaction is rolled back and any committed transaction is preserved. This ensures that even in the event of failure, the data in your database remains in a consistent state.
Make Regular Backups
You should make regular backups of your database files so that you can recover your database in the case of a media failure. SA uses the transaction log (which you should store on a separate device from the database for maximum data safety, or continuously backup using the transaction log mirroring feature of SA) to recover information put into the database since the last full backup. For information on management of the transaction log file, please see:
http://www.sosoft.com/fod/doc126-tranlog.pdf
The SA Logs
SA uses three logs to protect your data from system and media failure. These log files exist for each database running on a database engine or server.
All these logs play a role in data recovery. Each log exists for each database running on a database engine or server. Optionally, you can maintain a mirror of the transaction log for greater protection of vital data.
The Checkpoint Log
An SA database file is composed of pages. Before a page is updated (made dirty), a copy of the original is always made. The copied pages are the checkpoint log.
Dirty pages are not written immediately to the disk. For improved performance, they are cached in memory and written to disk when the cache is full or the server has no pending requests. A checkpoint is a point at which all dirty pages are written to disk. Once all dirty pages are written to disk, the checkpoint log is deleted.
Reasons for a Checkpoint
A checkpoint can occur for several reasons:
· The database engine is shut down
· The amount of time since the last checkpoint exceeds the database option CHECKPOINT_TIME
· The estimated time to do a recovery operation exceeds the database option RECOVERY_TIME
· The database engine is idle long enough to write all dirty pages
· A transaction issues a CHECKPOINT statement
· The database engine is running without a transaction log and a transaction is committed
The priority of writing dirty pages to the disk increases as the time and the amount of work since the last checkpoint grows. This is important when the database engine does not have enough idle time to write dirty pages. The database option CHECKPOINT_TIME controls the maximum desired time between checkpoints. The database option RECOVERY_TIME controls the maximum desired time for recovery in the event of system failure. Both times are specified in minutes.
When the database server is running with multiple databases, the CHECKPOINT_TIME and RECOVERY_TIME specified by the first database started is used, unless overridden by command line switches.
For a description of the command-line options, see “The database engine” in the on-line SA manual.
The Rollback Log
As changes are made to the contents of tables, a rollback log is kept for the purpose of canceling changes. It is used to process the ROLLBACK statement for recovering from system failure. There is a separate rollback log for each transaction. When a transaction is complete, its rollback log is deleted. (Note that the term transaction in this context refers to a related set of database operations, not a transaction in the sense of a single charge or credit entry in OM.)
The Transaction Log
For a condensed and SOS-specific discussion of transaction log management, see http://www.sosoft.com/fod/doc126-tranlog.pdf on the SOS web site.
All changes to the database are stored in the transaction log in the order in which they occur. If you should suffer a data loss, you could restore your most recent backup, then use the transaction log to restore all your changes since the last backup was done. In most cases, therefore, you can restore everything up to the time of your failure or loss, not just up to the time of your backup! The only price you pay for this additional security is the disk space taken by the transaction log. In the sections below you will learn how to move the log to another disk and how to clear it from time to time (after you have made and verified a full backup).
Although use of the transaction log is optional, by default it is enabled. If you run SA with no transaction log, a checkpoint is carried out whenever a transaction is committed. The checkpoint ensures that all committed transactions are written to the disk. Checkpoints can be time consuming, so you should run with a transaction log for improved performance as well as protection against media failure and corrupted databases.
For greater protection, SA allows you to maintain two identical transaction logs in tandem. This is called transaction log mirroring.
The transaction log is not kept in the main database file. The filename of the transaction log is set when the database is installed. For additional security against media failure, the transaction log may be written to a different device than the database file. Some machines with two or more hard drive letters only have one physical disk drive with several logical drives or partitions. If you want protection against media failure, make sure that you have a machine with two separate disk drives or use a storage device on another computer on the network. Note that by default, the transaction log is put on the same device and in the same directory as the database (\SOS\DATA). This default installation, although convenient, does not protect against media failure.
Placing the transaction log on a separate device can also result in improved performance by eliminating the need for disk head movement between the transaction log and the main database file.
Moving the Transaction Log
Use the SA DBLOG utility, located in the \SOS\SA\BIN32 folder, to move the transaction log to a different drive by setting a new transaction log name. Simply copying the file from one drive to another will not work because the location of the log is stored in the database.
Make sure that the database is not running.
From the \SOS\SA\BIN32 prompt, type, in lower case:
dblog -t <new log name> <database name>
The new log name should include the drive, path, and filename of the new log file, for example, d:\soslog\sostran.log .
The database name should include the drive, path, and filename of the main database file, for example, c:\sos\data\sosdata.db.
Once you have reset the location in the database using this procedure, physically move or copy the current transaction log to the new location and, optionally, name you have specified. You will then be able to restart the database engine normally. Remember to include the transaction log, in its new location in your daily backup!
Using a Transaction Log Mirror
A transaction log mirror is an identical copy of the transaction log, maintained at the same time as the transaction log. Every time a database change is written to the transaction log, it is also written to the transaction log mirror file. By default, SA does not use a mirrored transaction log, but you can readily add a transaction log mirror to your system. The procedure is described in the next section.
A mirrored transaction log is useful for extra protection of critical data. There is a performance penalty for using a mirrored log, as each database log write operation must be carried out twice. The performance penalty will depend on the nature and volume of database traffic and on the physical configuration of the database and logs, but in most cases will not be apparent to users of the system.
A transaction log mirror should be kept on a separate physical drive from the transaction log, so that if either drive fails, the other copy of the log keeps the data safe for recovery. Both drives should be on the same computer, however! Maximum protection of your data, therefore, would require three drives (physical drives, not just partitions on the same drive). The database files (the *.DB files) would be on one drive, the transaction log (sosdata.log) on a second, and the mirrored log (sosdata.mlg) on a third.
Creating a Transaction Log Mirror
Use the SA DBLOG utility, located in the \SOS\SA\BIN32 folder, to create a transaction log mirror file on another hard drive.
Make sure that the database is not running.
From the \SOS\SA\BIN32 prompt, type, in lower case:
dblog -m <new mirror log name> <database name>
The new mirror log name should include the drive, path, and filename of the new mirror log file, for example, d:\soslog\sosdata.mlg (mlg is the default mirror extension)
The database name should include the drive, path, and filename of the main database file, for example, c:\sos\data\sosdata.db
Removing a Transaction Log Mirror
If you have been using a log mirror and no longer want to do so, use DBLOG to discontinue mirroring:
Make sure that the database is not running.
From the \SOS\SA\BIN32 prompt, type, in lower case:
dblog -r <database name>
The database name should include the drive, path, and filename of the main database file, for example, c:\sos\data\sosdata.db
Erasing Transaction Log and Mirror Files
You can erase transaction logs and mirrors using the DBERASE command-line utility. Note that this command physically deletes the file from the disk, but does not terminate the use of either. As soon as you restart the database, the files will be recreated.
To delete a mirror log file only:
Make sure that the database is not running.
From the \SOS\SA\BIN32 prompt, type, in lower case:
dberase <mirror file name>
The mirror file name should include the drive, path, and filename of the mirror file that you want to erase, for example, e:\sos\data\sosdata.mlg
To delete a transaction log file but not its mirror:
Make sure that the database is not running.
From the \SOS\SA\BIN32 prompt, type, in lower case:
dberase <transaction log file name>
The transaction log file name should include the drive, path, and filename of the transaction log file that you want to erase, for example, d:\sos\data\sosdata.log
Validating the Transaction Log and Mirror
When a database that is using a mirror starts up, the database engine or server carries out a series of checks and automatic recovery operations to confirm that the transaction log and its mirror are not corrupted, and to correct some problems if corruption is detected.
On startup, the database engine checks that the transaction log and its mirror are identical by carrying out a full comparison of the two files; if they are identical, the database starts as usual.
The comparison of log and mirror adds to database startup time when you are maintaining a log mirror.
If the database stopped because of a system failure, it is possible that some operations were written into the transaction log but not into the mirror. If the database engine finds that the transaction log and the mirror are identical up to the end of the shorter of the two files, then the remainder of the longer file is copied over into the shorter file to produce identical log and mirror. After this automatic recovery step, the database engine starts as usual.
If the check finds that the log and the mirror are different in the body of the shorter of the two, one of the two files is corrupt. In this case, the database does not start, and an error message is generated saying that the transaction log or its mirror is invalid. If this situation should occur, consult “Recovering from media failure on a mirrored transaction log” in the on-line SA Database Administration Guide.
Validating the Database
From time to time prior to running a full backup, it is a good idea to verify that the database file is not corrupt. A small portion of the database could be corrupt without you ever knowing until it was too late to recover the data.
With the database engine (single user) or server (network) running, execute the Check Database tool in the database utility that comes with your SOS software. Enter a Security Administrator id and password, then click the Admin icon. Once at the administration console window, select Database Tools > DBA Utilities > Tools > Check Database.
If desired, you can run the same procedure from the command line or from within a batch file, using the following command, from the prompt in the SOS directory:
DBTOOLS /V
You may want to include this command in a desktop shortcut. It is an excellent idea to run this routine before or after your daily backup to be sure that you are backing up an intact database.
The validation procedure can take a little while to complete on a large database. It scans every record in every table and looks up each record in each index on the table. It can be run while the database is in use, but you may notice some performance impact while it is running.
Any messages produced by the utility are collected in a file on disk (\SOS\SOSVALID.LOG) and displayed when the procedure is complete. If the database file is corrupt, you must restore the DB files (but not the LOG file) from your previous intact backup. You may then be able to apply the current transaction log in order to bring the database up to date (see “Lost Database Files” on page 28).
Backing up Your Database
On-line Backups
In some organizations, operations continue around the clock and it is not possible to stop the database to perform a traditional backup. Under these circumstances, you can use the provided Copy Database utility to make a copy of the database, while it is running, in a different directory. Once the copy is complete, the duplicated files can be included in your regular backup even though the open production database files will be skipped by your backup software. (To avoid errors in your backup logs, configure your backup to skip over the production database files or folder.)
Run Copy Database interactively from the menus in the DBA Utilities program, which is located in the Admin Module. The Admin icon appears on the login window for users who are designated as Security Administrators. On the Admin menu, select Database Tools, then DBA Utilities.
Once the program has started, go to the Tools menu. Select Copy Database to make a live copy of the running database into a different directory. Note that for security reasons copying the database is only possible on the computer running the database software.
The Copy Database utility can be run against a standalone engine or network database server. The utility will not run from a network workstation for security reasons. Using the Copy Database utility on a running database is equivalent to copying the database files when the database is not running. In other words, it provides a snapshot of a consistent database, even while it is being modified by other users.
For a full description of the SA online backup facility, see “Use the Backup utility (dbbackup) to make a server-side backup” in the online SA Database Administration Guide, page 882 (pdf page 900). It is important to understand, however, that you will not be able to use the utility directly against the secure SOSDATA database. Rather, you must run it from within the DBA Utilities shell program provided by SOS as described above or using the DBTools command line explained below.
You also can copy the database from the command line, batch file, or desktop shortcut by executing DBTOOLS.EXE B=<target directory> from the SOS directory (but only on a standalone system or directly on the database server computer). For example, assuming that the SOS folder is located on the C: drive:
C:\SOS\DBTOOLS.EXE B=C:\SOS\BACKUP
would create a copy of the currently running database in the C:\SOS\BACKUP folder. Note that the folder must exist before you execute the command. SOS recommends that the destination for the copy operation be a different drive on the database computer than that on which the production database is located. That way, if your primary drive should fail, the most recent copy of the database on the secondary drive will be readily available to use in the restoration process.
Open File Backups Using Volume Shadow Copy Service
[SOS 2010 and later] SOS recommends the Copy Database procedure detailed above for best reliability. In addition, there is a side benefit that it leaves a backup copy of your database and log on a local hard disk as a redundant backup to the one you make on removable media, internet-based backup service, or both.
[SOS 2010 and later] That said, many commercial backup programs include the ability to backup files that are in use through the Volume Shadow Copy Service available on versions of Windows starting with XP. For best results, you should create and enable the SA VSS Writer service by executing the following command from the command prompt in the \SOS\SA\BIN32 directory:
dbsvc -as -s Automatic -t vss -w SAVSSWriter “c:\sos\sa\bin32\dbvss11.exe”
It is absolutely essential that you test your backup during a period when the database is actively in use by restoring your backup, restarting your system, and validating the restored database as described in “Validating the Database”, above .
Incremental Backups of the Transaction Log
You can carry out an off-line incremental backup by making a copy of the transaction log. Alternatively, you can carry out an online incremental backup by running the SOS Copy Database utility, and subsequently backing up the SOSDATA.LOG file in the normal fashion to tape or other removable media.
You should back up the transaction log daily. This is particularly important if you have the transaction log on the same device as the database file. If you have a media failure, you could lose both files. By doing daily backups of the transaction log, you will never lose more than one day of changes.
Daily backups of the transaction log are also recommended when the transaction log tends to grow to an unmanageable size between full backups and you do not want to get a larger storage device or do more frequent full backups. In this case, you can choose to archive and delete the transaction log by selecting the appropriate options in the SOS Copy Database utility.
There is a drawback to deleting the transaction log after a daily backup. If you have media failure on the database file, there will be several transaction logs since the last full backup. Each of the transaction logs needs to be applied in sequence to bring the database up to date, as described below. It is therefore extremely important to save all transactions logs that cover database activity after the most recent verified backup of the database files themselves!
SOS strongly recommends that Transaction logs be archived onto CD-R or other permanent media that will not be overwritten, and that these media be stored in a safe location, such as a fireproof data safe, or even better, off-site in a bank vault.
Backing Up, Renaming and Restarting the Transaction Log
Use of the following command will do a live backup of the transaction log into the target directory, but will also rename the backup with the form YYMMDD##.LOG (where ## is a sequential number starting with 00, in case you execute the command more than once on a single date) and restart the log. The result will be a copy of the current SOSDATA.LOG in the backup directory, but renamed based on the date, and a brand new, small SOSDATA.LOG file in the \SOS\DATA folder.
C:\SOS\DBTOOLS B=C:\BACKUPS -t -r -n
You can specify any existing directory on the server instead of “C:\BACKUPS”, but be sure to type the -t -r and -n in lower case.
If you prefer to work within a graphical interface, you can do this process from Database Tools > DBAUtils in the Administration module. Select Copy Database on the Tools menu, select the desired backup directory and check the -t, -r, and -n options as shown below.
Use the Copy Database utility to backup and restart your transaction log file.
To create the renamed backup copy in the current data directory, just type a period (.) for the Backup directory. The dated copy of the old log will be created in the same directory as the database files and new log file.
Data Recovery
System Failure
After a power failure or other system failure you should run the Windows’ scandisk utility to correct simple errors in the file system structure that might have been caused by the system failure. This step should be done before running any other software.
After a system error occurs, SA automatically recovers when you restart the database. The results of each transaction committed (written to the database) before the system error are intact. All changes by transactions that were not committed before the system failure are canceled, and therefore lost. In most cases that means you will only lose those changes that had not been completely saved prior to the failure.
The database engine automatically takes three steps to recover from a system failure:
1. Restore all pages to the most recent checkpoint, using the checkpoint log.
2. Apply any changes made between the checkpoint and the system failure. These changes are in the transaction log.
3. Rollback all uncommitted transactions, using the rollback logs. There is a separate rollback log for every connection.
Step 3 may take a long time if there are long uncommitted transactions that have already done a great deal of work before the last checkpoint.
Media Failure
Perhaps the most frequent cause of data loss is the failure of a hard drive or failure of drive controller electronics, leading to corruption of the data on the drive.
If you have backups, you can always recover all transactions that were committed before the media failure. Recovery from media failure requires you to keep the transaction log on a separate device from the database file. The information in the two files is redundant. Regular backups of the database file and the transaction log reduce the time required to recover from media failures.
The first step in recovering from a media failure is to clean up, reformat, or replace the device that failed.
The steps to take in recovery depend on whether the media failure is on the device holding your database file or on the device holding your transaction log. These discussions assume that your system stores the transaction log (or mirror) on a separate drive from the database files.
Lost Database Files
When your transaction log is still usable, but you have lost your database file, the recovery process depends on whether you keep or delete the transaction log on incremental backup.
If you have not deleted or restarted the transaction log since the last full backup, the transaction log contains everything since the last backup. Recovery involves four steps:
1. If you do not already have one, make a backup of the current transaction log immediately. The database file is gone and the only record of the changes is in the transaction log.
2. Restore the most recent full backup (the database file).
3. Change to the \SOS\SA\BIN32 directory.
4. Use the database engine with the apply transaction log (-a) switch to apply the transaction log and bring the database up to date:
dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\sosdata.log
Start the database in the normal way. The database engine will come up normally and any new activity will be appended to the current transaction log.
If you have archived and deleted the transaction log since the last full backup, each transaction log since the full backup needs to be applied in sequence to bring the database up to date.
Make a backup of all transaction logs immediately. The database file is gone and the only record of the changes is in the transaction logs.
Restore the most recent full backup (the database file).
Change to the \SOS\SA\BIN32 directory.
Starting with the first transaction log after the full backup, apply each archived transaction log by starting the database engine with the apply transaction log (-a) switch. For example, if the last full backup was on Sunday and the database file is lost during the day on Thursday:
dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\mon.log
dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\tue.log
dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\wed.log
dbeng11 c:\sos\data\sosdata.db -a d:\soslogs\sosdata.log
SA does not allow you to apply the transaction logs in the wrong order or to skip a transaction log in the sequence.
An easier approach is to put all the log files in the same directory, then use this command:
dbeng11 c:\sos\data\sosdata.db -ad d:\soslogs
where “d:\soslogs” is the directory in which you have placed all the relevant log files. The database will inspect the files and automatically apply them in the correct sequence.
After applying the transaction logs, start the database in the normal way. The database engine will come up normally and any new activity will be appended to the current transaction log.
Lost Transaction File
When your database file is still usable but you have lost your transaction log, the recovery process is as follows:
1. Make a backup of the database file immediately. The transaction log is gone and the only record of the changes is in the database file.
2. Restart the database with the -f switch:
dbeng11 c:\sos\data\sosdata.db -f
Without the -f switch, the database engine will complain about the lack of a transaction log. With the switch, the database engine will restore the database to the most recent checkpoint and then roll back any transactions that were not committed at the time of the checkpoint. A new transaction log will be created and the database will do a clean shutdown, after which you should be able to restart it normally.
Media failure on the transaction log can have more serious consequences than media failure on the database file. When you lose the transaction log, all changes since the last checkpoint are lost. By default, database checkpoints are set at 30 minute intervals. This will be a problem when you have a system failure and a media failure at the same time (such as if a power failure causes a head crash that damages the disk). Frequent checkpoints minimize the potential for lost data, but also create work for the database engine writing out dirty pages.
For running high-volume or extremely critical applications, you can protect against media failure on the transaction log by mirroring the transaction log or by using a special-purpose device, such as a storage device that mirrors the transaction log automatically.
Complete Sybase documentation for Sybase SQL Anywhere (SA) can be found by selecting Sybase Manuals on the Help menu in Office Manager (OM), Case Manager (CM), or Appointment Scheduler (AS). Alternatively, select Start > Programs > SOS Applications > Sybase Manuals. There are actually three SA guides:
SQL Anywhere Server SQL Usage, which includes an introduction to SA databases, queries, and other related topics. Many of these are not relevant to your “read only” access to the SOS database.
SQL Anywhere Server SQL Reference, which includes detailed information about SQL syntax.
SQL Anywhere Server Database Administration, which is perhaps the most useful in that it includes detail about management of the database and log files.
Another valuable source of SA information is the Sybase web site, http://www.sybase.com.
Specifications and Limits
The version of SA that comes with SOS Applications is locked to prevent changes to any SOS data by third party programs such as Microsoft Access. This restriction in no way limits your ability to view, read, report, or analyze SOS data with another program (assuming you have been granted access), but you will not be able to modify the data. Without a thorough knowledge of the intricacies of the SOS database and exactly how the many tables relate to one another, you could accidentally introduce data errors or inconsistencies by making a seemingly innocent change.
As you can see from the specifications below, you are extremely unlikely to run into any technical limitations of the database system.
| Database size |
Operating system maximum file size |
| Number of tables per database |
Up to 4 billion |
| Number of tables referenced per transaction |
No limit |
| Table size |
Limited only by file size |
| Number of columns per table |
45,000 |
| Number of rows per table |
Limited only by file size |
| Row size |
Limited only by table size |
| Number of rows per database |
Limited only by file size |
| Field size |
2 GB |
| Number of indexes |
2,048 per table |
| Maximum index entry size |
No limit |
Standalone Installations
When you run an SOS application in a standalone configuration, you are making use of three separate but related software systems: SOS’s application, Microsoft’s Open Database Connectivity (ODBC) system, and Sybase’s SA personal database server engine.
OM, CM and other SOS programs are called front-end or client applications. These applications do not directly manipulate the data stored in the database files. Rather they sends messages, commands, and requests to the Windows ODBC system. Using information stored in the SA driver installed on your system, ODBC translates these generic messages into the specific dialect that can be understood and acted upon by the SA database engine, also running on your system. The database engine then retrieves or modifies your data in accordance with the messages received through the ODBC interface and passes any requested data back to the client program through the ODBC layer.
Network Installations
Client/Server architecture was designed to make network computing more efficient. Work is divided between client workstations and a powerful server, thereby improving performance. Further, client/server applications create much less “traffic” on the network, also resulting in higher performance. Finally, because workstations do not directly control the physical data files, there is little chance of data corruption from power losses, reboots, and other disruptions at the workstation end of the system.
As with a standalone implementation, the operation of an SOS program involves several related software systems (the front-end program such as OM, CM, or the Appointment Scheduler, the Windows ODBC component, the SA client drivers) but also the network operating system on the workstation, the network operating system on the server computer and the SA server software. The network implementation also involves additional hardware components, including network interface cards, cables, other network devices, and, of course, the server computer itself. In a wide area network installation, there can also be routers, firewalls, and various other pieces of communications technology. If the SOS application has trouble reading or updating data in the database, any of these components could be to blame.
All of these potential points of failure could lead to challenging troubleshooting, but the presence or absence of the same problem at a different workstation can quickly isolate the problem to the server or workstation. See the Troubleshooting document in the SOS web site document library for pointers on problem-solving. The online user group (http://groups.google.com/group/sosoft) also can be a great resource. Another user may have had and solved a similar problem in the past.
Nothing that you do on your computer in the course of a day — or for that matter, a month — has anywhere near the significance of your backup procedure. A good backup will someday mean the difference between a minor inconvenience and a very costly disaster. It is only a matter of time before a system crash, burglary, fire, data entry error, or, heaven forbid, a ransomware infection will make your backup the only indispensable part of your system.
Consider these scenarios:
- You update the operating system (Windows) in your computer and subsequently find that you can no longer access your data. Your ledger and patient information are destroyed.
- You come into the office one day, only to find that every piece of office electronics you own is missing. On the further investigation, you find that all your tapes, data cartridges, and CD’s are gone as well, including those you had been using for your data backups.
- A power outage during a critical data update procedure corrupts several major data files.
- In an attempt to free up some disk space, you accidentally delete all the files in your SOS data directory.
- There is a fire at your office over the weekend and your computer system as well as all disks and tapes are literally melted.
- You have been making backups, but each day you use the same media for your backup. One day it is necessary to restore from your backup, but you find that the backups are not readable.
- You turn on your monitor and are faced with an announcement that all your files have been encrypted by a ransomware infection.
There and many other possibilities are out there, just waiting for the day that you can least afford the time and energy to deal with them.
What can you do to safeguard your data?
1. SOS Applications do not have a built-in backup program. Use high quality, brand name, data backup software, or the backup software that came with your tape, cartridge, CD/DVD drive, or other backup media. (Here at SOS we use Novabackup and Cloudberry Backup but there are many other possibilities.)
2. Configure your backup software so that all error checking, error correction, and/or verify options are turned ON. Also turn on the data compression feature, which is sometimes called something like “minimize space”. Enable the encryption option and if there is none, find other software. If there is an automatic compare feature, turn that on as well. We recommend that you always use the full backup option, rather that the differential or incremental options. In the event that you have to restore from a backup, having made a full-type backup will make restoring your data much easier. Save these options.
3. Next you may want to follow the instructions that came with your backup software for creating a setup or script that will permit you to backup only those files you want to save. You can, for example, have a setup that backs up only the data files in your \SOS\DATA folder, which is the folder containing the SOS database. In case of a problem when doing an update, it is a good idea to include the entire SOS folder and all its sub-folders.
4. All good backup programs include a notification feature that will tell the program to email a report to you at the end of every backup operation to let you know if any errors were encountered. Checking to be sure your backup is running correctly is critical; this option makes that part of the process quick and easy. Enable notifications and make reviewing the notification part of your daily procedure. SOS recommends that at least two members of your staff receive backup notifications.
5. Another feature offered by most backup programs is data encryption. If you will be transporting the backup out of the office, encryption is an essential safeguard to avoid a potential data breach. The loss or theft of your SOS backup would compromise the privacy of all your patients and result in serious and expensive HIPAA-related remediation, and potential civil or even criminal prosecution. Encrypting your backups, or the media on which you store them, is therefore absolutely mandatory. Make sure that you record the encryption key and that all parties who need it will be able to find it should the need arise, even years after you have gone on to greener pastures. Your backups will do you no good if you cannot decrypt them.
6. It is absolutely essential that you rotate your backup media. In short, what this means is that you never use the same cartridge, tape, or backup disk two days in a row. The bare minimum should be a rotation through three backup sets, but we strongly recommend that you purchase enough cartridges, tapes, or disks to allow you to backup each day of the week on a different tape or cartridge, plus several more for rotating off premises.
For example:
Use for backup on Monday Cartridge/tape/disk/CD/DVD #1
Use for backup on Tuesday Cartridge/tape/disk/CD/DVD #2
Use for backup on Wednesday Cartridge/tape/disk/CD/DVD #3
Use for backup on Thursday Cartridge/tape/disk/CD/DVD #4
Use for backup on first Friday of the month Cartridge/tape/disk/CD/DVD #F1
Use for backup on second Friday of the month Cartridge/tape/disk/CD/DVD #F2
(alternate the Friday backup media going forward.)
Note that there is a separate disk, cartridge or tape for each day of the week, Monday through Thursday. Friday will be our safety day, so there are five separate Friday backups. On the first Friday of the month, we make our backup and take it off premises. The following Friday you use your alternate Friday media and return the other Friday backup to the office.
The safety location is often the home of one of the people in the office, perhaps the owner of the practice or the person responsible for making the backups. Storing some backups off premises provides you with an extra margin of safety in the event of a fire, burglary, or other event that might result in the loss of the data on your hard disk, as well as any media stored near the computer. Some people use a fireproof box or safe at the office for the safety backups, but we see this as a compromise and strongly suggest storage in another location. For additional security, SOS recommends that you make periodic “archival” backups that are never over-written. Such archival backups can be made on CD-R, or DVD disks, assuming your backups will fit on a single optical disk. (Such disks are very inexpensive and take up very little space.) Archival backups should be stored in a secure off premises location, such as a bank safe-deposit box. This extra step will allow you to restore your financial data for an entire quarter or longer.
An alternative for archival backups is to make backups that are saved to an online repository. Amazon Web Services has an offering called Glacier that is perfect for archival storage of data and is ridiculously inexpensive (a fraction of a penny per gigabyte).
7. In addition to your multiple generations of local backups, you should consider using one of the many online backup services (do a web search for “online backup”), or have your IT consultant set up regular uploads of an encrypted backup to an off-site web server, preferably located far from your office. This copy will be your doomsday backup, to be used if a natural disaster or other event makes recovery of your regular backups impossible. With a backup of this sort, you can be back in business within a day or two even if your entire town were to be destroyed by a hurricane, flood, tornadoes, etc. The steps you would follow in the event of such a catastrophe should be written in your policy and procedures manual (another HIPAA requirement, by the way). Online backup services such as CrashPlan.com, Carbonite.com, iDrive.com, and MozyPro.com are inexpensive and easy to set up. In addition, most services save several versions of your backup, in case you need a copy of a backup from a couple of weeks ago and are not following the multi-generational strategy outlined above. If you go this route, be sure you select a vendor that provides for pre-transmission encryption. The only way you can be sure your data is not at risk is if you encrypt it BEFORE sending it off-site!
What we do here at SOS:
- Each night all critical data is backed up to another drive on the network, the destination folder being different each day of the week. That way we can very quickly restore data backed up over past week.
- Also, every night, using Novabackup, we backup the same data to removable external drives, one for each day of the week, and alternating on Fridays, as described above. The most recent backup goes off-site each night.
- SOS has copies of the four most recent end-of-week backups stored on Amazon’s somewhat more costly (a penny or two per gigabyte) S3 service. Each weekend the oldest weekly backup is replaced by a new one. Cloudberry Backup is used to create and transmit these backups to Amazon.
- Every month Cloudberry makes a permanent monthly archival backup on Amazon’s Glacier service, also using Cloudberry Backup.
The bottom line is that you can never have too many backups!
Note that as of October, 2013, new HIPAA rules went into effect that require you to execute a formal Business Associate Agreement (BAA) with any vendor who “creates, receives, maintains, or transmits protected health information”. That would seem to include online backup services. If you do not execute a Business Associate Agreement with this type of vendor, you might be in violation of the new HIPAA rules. At present, SOS knows of only one online backup vendor (Carbonite) that is willing to accept the level of responsibility and liability that such an agreement includes. Essentially, the vendor is on the hook if there should be a data breach. That could be a pretty darn big hook, so it is no wonder that they are not rushing to comply. Anecdotal reports indicate that MozyPro will sign a BAA if pressed, but I like that Carbonite has formal, published policies that state their status as a BA. There are probably other vendors who follow that policy as well, but we don’t have that information at present.
8. Have a disaster recovery drill on a regular basis. Start by shutting down your database and renaming your DATA folder in SOS to something else, such as DATASAVE. Now try to restore your backup, following the appropriate restore procedure for your backup software. When the restore is complete, open SOS to be sure the database is intact and contains all the data that you believe it should. If you cannot open the database, something is wrong with your backup procedure and you must correct it. If the program starts fine and you can access all your data, you know your backup procedure is working. You can now delete the DATASAVE folder.
If you follow the type of procedure outlined above, you can rest assured that your data will be safe and available should you ever have to restore from a backup.
Backing Up a Database While It Is Running
In some installations it is desirable to keep the database running 24 hours a day or to do a backup without stopping the database. You cannot backup a running SQL Anywhere database using any commercial backup software, so a special procedure must be used. (Even if you manage to make such a backup of the files while they are open, it is exceedingly unlikely that the database would run after restoring from it.)
There is a downside to running the database non-stop. There are several maintenance/clean-up procedures that run automatically when the database is started. If you never restart the database, it is possible that you might run into minor issues, such as the inability to log into the system with a particular user ID after that user disconnected from the database in an unusual fashion. See Database Tools in the Admin Module to do the cleanup manually. SOS recommends that you restart the database once each day to allow these maintenance procedures to run.
Even though you cannot backup the data files while the database is running, you can make a copy of the running database in a separate directory. You can then configure your backup software to backup the copy and to ignore the directory in which the production database files are located.
For this purpose, starting with SOS 2016 you will need user credentials for an account that has no rights other than to create a copy of the database files while they are in use. Backup-only user accounts are created on the Backup Users tab of the user account maintenance list in the Administration module. For full instructions, see Configuring Backup Users in the Administrator Guide (sosadmin.pdf, located in the SOS folder).
Create a batch file (CMD file) containing the following commands. This example assumes that the software is installed in the SOS folder on the C: drive. Make adjustments as appropriate. It is important to note that this command file must be run directly on the computer that runs the database. You cannot run it from a network workstation.
(SOS 2016 )
DBBACKUP -y -c "uid=<backup user account ID>;pwd=<backup user account password>;dsn=SOSDATA" <target directory>
Here is an example command line for SOS 2016, including an optional -y parameter to create
the directory if it doesn't exist, and to overwrite existing files:
DBBACKUP -y -c "uid=BU_BILL;pwd=PASSword#1;dsn=SOSDATA" C:\SOSBU
where <target directory> is an existing directory in which you want the copy of the database to be created. This directory must be located on the computer running the database. It should NOT be a shared folder on another computer! Make sure that this target directory is included in your backup configuration so that the database copy is backed up, and set the backup to skip the \SOS\DATA directory in which the running database files are located. Most backup software will fail to make usable backups of a running database!
You can run a database validation or database copy interactively from the menus in the “DBA Utils” progam in the Admin Module. Once the program has started, go to the Tools menu. Run the validation by selecting “Check Database”. You can run an ad-hoc live database copy by choosing “Copy Database” in the Tools menu. Generally, live backups are done as a Windows scheduled task that executes one or more times each day.
It is absolutely essential that you monitor your backups closely to be sure that the current database files are being copied to your backup media. If you are using commercial backup software, be sure to turn on the “verify” or “compare” operation and inspect your backup logs or notifications every day!
|
|
Print Friendly
