SAFE SQL Feature: Whither ROX?
Ed. Note: This is the eighth in a series of articles on the major new features in SAFE SQL. The following describes the new abilities of SAFE's Remote Office Extensions (ROX).

Remote Office Extensions has been very popular over the years, and in fact a main selling point of SAFE. The ability to run separate copies of SAFE at branch offices and have that data updated to a main office is a common feature in other industries, but SAFE has been unique in this regard.

Now with SAFE SQL, the rules are changing. Partly because of the nature of SQL databases, and partly because of Suntower Systems' ongoing direction towards more secure and scalable designs.

Replication
Whenever possible, we will be suggesting that our customers convert to using SQL Server directly through a broadband connection. In one sense, this may appear that users are no longer using Remote Office Extensions, but this is not so. In fact, they will still be operating on a Local copy of SAFE as now, but the database itself will be updating the offices routinely through a process called Replication.

Replication means that the server periodically downloads a version of itself to each remote office. If the main connection to the server goes down, users at the remote offices can continue to work on the 'replicated' version with no disruption. To them, it appears that nothing has gone wrong at all. When the Internet connection is re-established, the database automatically re-synchronizes the main office system with any new data entered at each remote office.

There are numerous advantages to this approach.

1. There is no longer any daily update process because there are no longer any separate branches per se. Since you will be constantly connected to the Internet, each office, though working on it's own data, is actually working with the main server's database.

2. Consequently, SAFE becomes a real-time system. So inventory control is much more accurate which can be critical if you have multiple offices withdrawing the same items from a common stock.

3. Since there are no update procedures to run, there is little or no worry about when to transfer files at night. This creates higher availability for firms which want to run 24/7.

4. Centralized backup happens automatically. If your main office backup program can properly backup/restore SQL Server Databases on the fly (this is an option with most backup software packages such as ArcServe and Veritas, you are assured that your backup will include all data from all linked branches.

Publish/Subscribe
In order to work this magic, SQL Server requires that the server and remote office computers create a set of rules referred to as Publishers and Subscribers. A Publisher is a SQL Server computer which makes it's data available for sharing. A Subscriber is a computer that uses that data. In ROX, the Main Office and the Remote Offices will be both be Publishers and Subscribers since each has data that it needs to pass along to the other. These rules are somewhat analogous to the Scripting rules in ROX for SAFE 5.1, however, the key difference is that these rules impact data moving back and forth in almost real time. In other words, the data is being updated using these rules between both Main Office and all branches automatically (usually every hour or so depending on how fast your Internet connection runs.)

The important thing to know is that we will set up the Publish/Subscribe rules for you to match your existing ROX Scripts, and all at no charge. (By the way, this is the main reason that data conversions have taken several weeks to schedule; we have to write and test a set of Publish/Subscribe rules to match your existing custom ROX Scripts.)

'Classic' ROX?
Now for some of you, it may not be possible to use Replication. Usually this is because you do not have broadband access. In this case, you can continue to use SAFE 5.1 at Remote Offices, however, in order to do this you will need to run a daily Transfer process on your Main Server in order to create  data files which can be read by your Remote Offices running SAFE 5.1. At the end of each day, you will run this 'Transfer' procedure, which will take 10-15 minutes, before you can run your file transfers.

In Conclusion
Replication is an important step forward for Remote Office Extensions. It combines the advantages of real-time access to Corporate data, with the safety to continue working at each branch if an Internet connection is lost. And since no periodic updates are required, availability becomes almost non-stop for all your branches, regardless of location.
 

Watch For Our Ad In Form Magazine!
No Hell has not frozen over. Suntower Systems has placed it's first advertisement in Form Magazine in five years which will run in the May Annual  Operations Software issue. Long time customers of ours know well our decidedly mixed feelings regarding ads in general and Form Magazine in particular, but the time seemed to be right to get our message out to the large number of new faces we have noticed in DMIA.

As always, your comments are most appreciated. If you have suggestions on future advertisements or promotions, please do let us know. Many of you have tremendous marketing talent and we value your input greatly. To get a preview of the ad, click here: www.suntowersystems.com/formmagad0503.jpg
 

Ciaran's Corner: SQL Performance Is A Learned Skill!
Standard Rant #28
First of all, not one, not two, but three of you have called with some demonstrable form of virus or other attack from outside last week. What pains me so is that in no case was the principal we spoke with even aware of my column or my ongoing harangues about security. Could I feel more unloved? But look, we know what is going on because: we get your e-mails! And every week your e-mails are laced with at least ten viruses. What can I do but point out the evidence and hope that you'll do something about it?

Now I know that many of you are not comfortable with tech issues, and that you do not have a good tech adviser. And I know this is not what you had in mind when you entered the glamorous world of business printing. I am sympathetic. But this is the world we live in. So repeat after me, please: 'I will get complete Anti-Virus Protection on every PC.', 'I will run an Anti-Spyware program.', 'I will make better backups.', 'I will have a professional install and validate a firewall for our broadband connection.'

Now don't you feel better? I know I do.

Speeding Up Queries
Some of you have complained that the first time you run a new Query in SAFE SQL that it actually runs slower. As a tech support manager I love when you nice people cut and paste bits from our ads ('Queries run up to 100 times faster!') and send them along with a some pithy remark like 'Haaaa!'. What a morale booster. Ah me....

Here's the reason. SQL Server (like all modern databases) actually learns as you use it. By some druid magic it keeps a running log of all the requests for data and internally optimizes the way it works based on your business. In the past, SAFE had certain reports and Queries that could always be counted upon to run quickly because we did the optimization in our code. Conversely, there were lots of reports which would, just as assuredly, run very slowly indeed, because we simply could not optimize everything.

So now, as you use SQL Server over time, the reports you use most often will actually run faster and faster with each iteration as the database learns your preferences. It may take a few tries, but after three or four runs, Queries that used to take several minutes in SAFE 5.1 should resolve in as little as 2-3 seconds with SAFE SQL. And better still, this improved performance does not take away from other users. When you run even intensive Queries it rarely has a large impact on other users as could happen in SAFE 5.1.

Til Next Time,

Ciarān Marron
Technical Support Manager
cm@suntowersystems.com

---

THIS DOCUMENT IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY. The information contained in this document represents the current view of Suntower Systems on the issues discussed as of the date of publication. Because Suntower Systems must respond to change in market conditions, it should not be interpreted to be a commitment on the part of Suntower Systems and Suntower Systems cannot guarantee the accuracy of any information presented after the date of publication. INFORMATION PROVIDED IN THIS DOCUMENT IS PROVIDED 'AS IS' WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND FREEDOM FROM INFRINGEMENT. The user assumes the entire risk as to the accuracy and the use of this document. This document may be copied and distributed subject to the following conditions:
1. All text must be copied without modification and all pages must be included.
2. All copies must contain Suntower Systems' copyright notice and any other notices
    provided therein
3. This document may not be distributed for profit.

---

End of E-News From The Suntower, Volume V #9