The magazine of the Melbourne PC User Group An Introduction to Microsoft SQL Server Ray Watson |
|
Ray Watson discusses the feasibility of using SQL Server Software — particularly
Microsoft SQL Server — to increase data storage reliability, response speed and
the security of electronic data from unauthorised |
This is for people who are responsible for providing a robust data environment
for significant electronic data, especially in a multi-user environment. It may
be particularly relevant to Microsoft Access developers. One option is to buy a
program that backs up the data at regular intervals (say once an hour). This
specialist software, which can back up Access database files that are open and
in use, would cost about $AU400. Even then, you could lose an hour of data
input, which over four users is still "a pain". Then, take into account
programmers' time to check that the MDB file backed up in the previous hour
hasn't also been corrupted, and perhaps having to painstakingly migrate data to
the current live version.
The Developer's View
Once upon a time I knew a software developer who used to say, there should only
ever be two commands you need to issue to a computer.
His witticism identifies two of the most pressing problems for computer users,
Reliability — to have it work the same way every time, and Usability — which
button do I press now and will I lose my job if I screw up?
The more successful examples of computer software are often provided by Web site
designers who must cater for the lowest level of user skills while attracting
maximum usage. There are many Web sites that are not only useful, but also easy
to use. For example, I love to book the cheapest flight on the Virgin Airline
Web site. Mind you, I can do it only if I use Microsoft Internet Explorer, as
the opening
screen becomes an indecipherable mass of bright red if I use my currently
preferred browser, FireFox.
Lets get down to a more personal level. How would you feel when you saw two days
of data keyed into an Acess MDB file and then reduced to an "Unrecognised
Database Format" when you try to open it for use? Worse still, how do you go
about explaining to the trusting departmental manager, who has just spent
$10,000 on the system, that this is not a typical event. but expected in any
kind of multi-user environment. Sure there are backups, and original source
documents from which to recover (perhaps), but it is a big upset to office
routine and user and investor confidence.
Having been through this experience, some years ago I gradually began
investigating more stable solutions to the data storage problem. The answer is
specialist data storage and retrieval software of the likes of MySQL, Microsoft
SQL Server, Oracle etc. In the beginning, learning about SQL Server software
felt like taking on a University Degree. Also, the usability of the software was
in inverse proportion to its reliability, primitive! The good news is that a
product like Microsoft SQL Server is now not only a reliable
way of storing crucial data, it is actually comprehensible for a lone user or
small business team. I speak here of Microsoft SQL 7, not the recently released
SQL 2005 which I have not yet had the opportunity to evaluate.
I will restrict observations to the Microsoft SQL product, because that is the
area of my experience. I can say I have thoroughly enjoyed the feeling of
confidence when developing serious data storage solutions using a product that
is robust, fast and allows me to sleep peacefully at night not having to worry
about whether the whole system is going to fall over tomorrow, and cause someone
to ask for their money back.
The Enterprise Manager's View
Due to the limitations of software like Microsoft Word and Excel for effective
data storage, many businesses have developed quite useful Microsoft Access
solutions. One of the joys of such systems is the ability to have multiple users
update lists of parts, people, procedures and customers at the same time. If you
own or use such a system, you probably realise that this is done through the
magic of the Local Area Network. You are probably aware that the say, G:\Data
drive you
see in Windows Explorer, is actually sitting on another computer, on another
office floor or even another building.
You are aware that any document you place
in G:\Data folder can be read and accessed by any other employee in the
organisation. You also know that the file G:\Data\Orders.Mdb is an Access data
file, that you can "open" to view current orders, customer names etc the instant
they are created by other employees. Wow, such power.
For the business owner, lookout! Sure, the file Orders.MDB is available for
instant update, reporting, etc, but it is also available for a potential
ex-employee or a "sharp operator", for copying and unintended use. The fact is
that hundreds of MDB files with customer details, product prices etc. have been
copied, taken to the 'next' job and handed to competitors over the years,
without anyone in the organisation even knowing. With the advent of MP3 players
and USB "hard drives", people can walk in and out of a business premises with a
copy of years of corporate data.
This is possible because the Access data store
is viewable by the ordinary operator as a physical file. In the case of
Microsoft Access even passwords are no protection, as a short visit to the
Internet Newsgroups will confirm. Also, the sad reality is that Access data
files are not designed for much more than 2 or 3 simultaneous users, and cannot
be considered a reliable way of storing important data.
In the case of SQL Server software, there is no need for the "shared drive" and
the subsequent visible data store (eg, G:\Data\Orders.mdb) to be available to
normal operators. The data that is provided from, and to the user is transmitted
throughout your network via the same "invisible" protocol that the Internet uses
(TCP IP). It is also provided with very robust security features. And to top it
all off, the ability of an SQL server to deliver fast answers is extraordinary.
My observation is that complex queries running on your own personal computer
will execute much faster when
using your local copy of SQL server, than when you're using your own copy of
Microsoft Access.
The Cost Considerations
Full blown (Enterprise) versions of Microsoft SQL Server Software may be
regarded as relatively expensive up front (Over $2000), but the reliability and
lower maintenance costs quickly recover the initial expense.
Let's look at a small multi user Access Database system as an example. If you
have on average, four users keying in data, and at 1.00 pm the dreaded
"Unrecognised Database Format" message appears. Someone is faced with restoring
last night's backup, and re-entering the data that four users had achieved since
the start of business that day, and that's if they have kept a detailed list of
the changes they made. This may take the four users all of two hours — a huge
chunk of their working day, even if it's possible to do.
Microsoft SQL Server is much more robust, but things like Server failure (Hard
Drive problems etc) are a rare possibility. With Microsoft SQL Server, a 2-stage
approach to storing data is taken. First it writes the new, changed and deleted
data to a relatively simple sequential Log File with a date and time stamp. Then
it sends these "transactions" to the permanent tables. Only when the data is
safely stored and verified as being "saved" in the live data tables,
does it remove transactions from the Log File. If the SQL Server software
"crashes", and has to be restored on a new server for instance, the operator
restores the permanent data AND the Log File.
Any Log File information that
didn't make it to the data files before the crash is then saved and verified.
During the Restore process, the operator can specify a date and time range for
the transactions. This is handy if you need to re-create data from a definite
point in time, to make it easy for administrative staff to determine up to what
time the data has been saved.
If the financial cost is a bit daunting initially, Microsoft supplies a FREE
version of SQL Server limited to eight users. It is known as MSDE (Microsoft
Desktop Edition). It lacks the two maintenance utility programs usually supplied
with Microsoft-SQL, but it is possible to use Microsoft Access to create tables,
to do data updates etc. I have found Microsoft Access 2000 is nowhere near as
easy or powerful in updating and maintaining an SQL databases. The utility
programs (Enterprise Manager and SQL Analyser) do a much better job.
Implementation and Use of Microsoft SQL Server
The installation of Microsoft-SQL is perhaps easier than Microsoft Office in
many ways, with fewer choices to worry about. All the standard options are the
most used and useful settings, and fairly experienced users should have no
difficulty. It can be installed on a local machine for testing and development,
and/or a "server" or
other networked computer.
SQL Server software does not appear as a regular application program on the
computer on which it's installed. It sits quietly in the background doing its
work with only a small Service Manager Icon displayed reassuringly on the
Windows Taskbar. This Service Manager enables the software to be started and
stopped by the operator. It is the job of other application
programs to send and retrieve data to/from the SQL Data Store, if they have the
right passwords. This technique may be loosly compared to the Internet, where
you use a Browser, which is the front-end program to retrieve and submit data
to/from remote locations, and the data exists within the browser only
temporarily.
There are a variety of ways of "talking" to SQL Servers. Having been involved
with Microsoft Access for a long time, I've found it tends to be the "front-end"
program that gets used a lot. However, it is not the only tool or method
available, and that's a good thing for flexibility within an organisation.
Indeed, SQL Servers (ie. MySQL, Microsoft SQL Server, Oracle etc) are invariably
the data repositories for most Web Sites around the world.
If you wish to use Microsoft Access as the front end, you can view the data
stored in an Microsoft SQL Server Database much like it appears in a normal
Access MDB. This is achieved by creating an ADP Access Database. The unusual
thing is that all the data you "see" in the .ADP file is actually stored in the
SQL database. If you want to develop a serious business solution though, the
recommended method is to use an ordinary MDB Access database, and issue
instructions to Microsoft-SQL Server programmatically. Incidentally, for any
Microsoft Access users who would like to get a good start using Microsoft SQL
with Access, the book Microsoft Access Developers Guide to SQL Server, by SAMS
publishing was very useful to me. It makes a lot of very practical suggestions
and provides some very useful insights, explained in a matter-of-fact way.
Microsoft Access developers might consider the ease of issuing a command like
...
... as a neat, no-nonsense way to add a new field to an existing data table
without harming saved data.
Compare that to any code you may have seen to modify
an Access MDB table while the database is open and being used by multiple users,
and doing it without risk of a software crash.
In the latest project I've been involved with, we even saved Bitmap and JPEG
images into the SQL Tables. In many cases, Binary Large Objects (BLOBS) are
recommended to be stored in a separate folder, and referenced by name from the
database. This tends to complicate backup and maintenance procedures, so in this
case the images became part of the data stored in an SQL Table.
Mastering the Techniques
The benefits of SQL Servers may be easily identified, but then comes the
challenge to master the technology.
With sophisticated software, we invariably ask the expert developers to teach
it. They may be experts on the software, but not necessarily in how to teach new
users. My experience is that getting the information conveyed to me in a way
that it made sense was the biggest hurdle to mastering the topic.
Now I am comfortable with the use of Microsoft SQL Server, I understand that one
doesn't have to master the product to take advantage of its power. Many topics
that students have pressed upon them in formal "pressure cooker courses" are
totally irrelevant to getting great service from the product.
For example, I attended several formal starter courses on Microsoft SQL Server.
Invariably the topic of distributing the data (storing data files across
multiple servers) was covered in great detail. What was completely missing from
the notes (and supporting exercises) was the most basic fact, that the files
(minimum 2) that contain the SQL data can be placed in any
directory you like, even on a local PC. They can be backed up or copied like any
disk file, they are no more difficult to manage really than any Microsoft Word
(.DOC) or Excel (.XLS) or Microsoft Access (.MDB or .ADP) file on your computer.
Fifteen minutes of demonstration, and another 15 minutes of the student simply
playing with the concept could have shed more light on the topic than 45 minutes
of constant talking or reading. Granted, many students can deduce that concept
from the advanced presentation, but they are in the minority of learning types.
Moreover, the average person is left without this critical first concept, and
more importantly, the confidence that they can sufficiently understand the basic
workings of the software so they do not lose any useful data they create.
In Microsoft SQL 7, the user interface is quite foreign and disjointed to users
who have been accustomed to software with highly developed user interfaces. As
mentioned previously, we actually use two programs separately. One program is
called Enterprise-Manager, the other is SQL Analyser. They were obviously
designed by a committee. If you have used Microsoft Access extensively, it is
possible to get some leverage out of its vastly superior SQL Designer (Query
Builder). In many instances, I have designed tables and queries within Access
and then copied them over to SQL Analyser for testing and rework.
From a new users perspective, another thing to appreciate is that there is a
procedural language within SQL Server that allows quite complex processing to be
carried out. It is called Transact SQL. The language and syntax is a clunky
batch file type of construct, and the interface is very simplistic, but you can
construct some
quite elaborate processes (including repetitive loops and temporary variables)
that have the advantage of being very fast. I understand that the new version of
Microsoft SQL Server 2005 has improved the user programs quite a bit.
Also provided with the Enterprise Version of SQL Server is a help file (SQL
Books Online) which is very handy; it has easy to understand and detailed
explanations of a huge range of subjects. Then of course, newsgroups, discussion
sites abound on the Internet as a great source of information.
SQL Server technology is an established solution for fast and reliable data
storage systems. Mastering the use and concepts makes life much less frustrating
for users and developers, and quickly repays the effort expended to master the
technology.
Certainly, in the near future I plan to do a review of Microsoft SQL Server
2005, as part of my ongoing training. I look forward to sharing discoveries with
other members.
Future Possibilities
If there are sufficient numbers of members within the Group who are interested
in delving into SQL Server technology, it may be possible to organise a SIG
dedicated to the topic, and perhaps encourage the group to run training courses.
I will gauge the level of interest from specific feedback to this article.
Please email me at
rwatson@melbpc.org.au.
About the Author
Ray Watson is a widely experienced database designer and developer having worked
in a wide variety of industries over the past two decades.
Reprinted from the March 2006 issue of PC Update, the magazine of Melbourne PC User Group, Australia
