Best Practice Ezine #12 – SQL

Best Practice Ezine.  Computer Performance. Advertise

Guy’s Best Practice & Litmus Tests Ezine  #12 �  An Introduction to SQL

Contents for Ezine #12

I was surprised by the high showing of SQL in our online poll: ‘Which topics are you most interested in?’  Well, taking heed of the survey result, here are some tips to get you started with SQL 2000 (Structured Query Language).

Compared with Access and other small databases, SQL is ‘The Real Deal’.  What I like about proper relational databases like SQL and Oracle is the way they use transaction logs to ensure the integrity of your data.  In fact, what you learn about SQL’s logs you can apply to Active Directory and Exchange databases.  While SQL is a powerful and comprehensive product, it is easy to setup at least so that you can run some experiments.

  1. Network managers.  Techies who look after Exchange, now need to install a SQL server.

  2. Programmers.  Database specialists transferring from Oracle, PowerBuilder or Access.

  3. People who need a working knowledge of Transact statements to extract information from a SQL database.

If you are not familiar with transact statements, here is an example.  Suppose you wanted to run a query to find how many Doctors there are in your Medical database.  Here are the commands:

Use Medical  

Select * from Personnel where job = ‘Doctor’

Use Medical   ‘ (Means connect to the Medical Database)

Select * from Personnel where job = ‘Doctor’   ‘ (Means go to the ‘Personnel’ table and return all rows where the field ‘Job’ equals Doctor.)

See more examples of transact statements here

Guy Recommends: The Free IP Address Tracker (IPAT) IP Tracker

Calculating IP Address ranges is a black art, which many network managers solve by creating custom Excel spreadsheets.  IPAT cracks this problem of allocating IP addresses in networks in two ways:

For Mr Organized there is a nifty subnet calculator, you enter the network address and the subnet mask, then IPAT works out the usable addresses and their ranges. 

For Mr Lazy IPAT discovers and then displays the IP addresses of existing computers. Download the Free IP Address Tracker

Getting you started.

With my theme of ‘Getting you started’ I will concentrate on the role of :
1)  Network managers.  Compared with Exchange, installing SQL 2000 is the proverbial ‘piece of cake’.  About the only preparation you need it to decide on an account for the SQL service.  This is rather like getting a Jockey to ride your horse.  In this case you need an account that will logon to the SQL Service.

Just a few more pointers, and you will be ready to begin.  SQL has two authentication modes, SQL and Windows.  Make Windows Authentication your first choice.  SQL Authentication means the users are stored in a separate database rather like NT 4.0’s SAM database.  In a nutshell, choose Windows Authentication, but be aware that CA is the ‘Super User’ in the local SQL account.

Talking of Authentication, not only does each user need to log on to the very SQL server, but they also need to be given permission to use each database stored on that server.  As expected, there are built-in roles that you can assign to users for access to both the server and to the database.

Where can you get SQL 2000? – try Microsoft for a free evaluation copy.

One of the great features of SQL is the Northwind sample database which comes with the product.  Which ever ‘hat’ you wear, Northwind is a fantastic resource for learning by playing.  People often ask me to recommend a good beginners book – no need.  SQL has BOL (Books on line); here is the most comprehensive reference guide I have seen.  Tell the truth, I have seen at least two SQL books for sale which are just rip-offs of BOL.

Now I am not a SQL specialist, so if I say the BASIC configuration is straightforward, then perhaps you will believe me.  However, I am not saying that 2) Programming SQL is easy, however, to me, designing the database is separate skill which is really not required by the 1) Network Manager.

I find that people who are interested in SQL wear one of these three hats:

Wearing your Network Administrator’s hat, there are four main areas of responsibility.

  1. Installing the database and making sure the files have enough disk space.  (Naturally protected by RAID)
  2. Setting the permissions for users to access the server and the individual SQL databases.
  3. Backup – SQL has its own rock solid backup system.  So you can backup to disk, then off to tape.
  4. Monitoring – checking the Server is running smoothly.  Here is an interesting area to automate and proactively protect your SQL server.

As I said at the beginning, this is just a taster to get you started.  I leave you with two challenges, install SQL See more about configuring SQL here.

See more interesting free computer utilities

Here are my reviews of more useful computer tools.  Most of these programs are free, while others are major applications, but time-limited.  One common theme is that Solarwinds give you a free specialist utility, and then supply a more comprehensive suite for larger organizations.  To let you into a secret; for small networks the free tool is all you’ll ever need.

E 202 Permissions Monitor  • E 190 Network Device Monitor   • E 181 Config Generator

E 166 IPAM  •E 161 OB IT  •E 159 Kiwi Syslog Review  • E 156 Windows Network Monitor

Real Time Netflow Analyzer  •Syslog Utility  • Ezines Home  • Ezines Home