Putting New Data into SQLSpyNet with the DTS Wizard

The DTS Wizard is a very flexible tool that allows us to import data from almost any data source, including Excel, DB2, Oracle, Access, and even Text files. Why do we need this? Many organizations have data spread throughout the company. Charles will have a spreadsheet of his customers, Mary will have a small Access database that has all her suppliers, and Jenny, the secretary, might have a text file (word document) with the names and phone numbers of all the staff members.

With all of these disparate types of information floating around the office it's pretty obvious that data management can become a real nightmare! Duplication of data is inevitable, and retrieval is almost impossible.

But here comes SQL Server 2000 to the rescue! With the introduction of DTS in SQL Server version 7.0, many organizations have been able to relieve the pain of trying to get all these disparate pieces of information together. The DTS Wizard is a point-n-shoot approach to import (or even export) data into our database. We go through a series of steps, selecting the database into which to enter the data, where the data is to come from, and so forth.

This is all fine for simple data, but what happens when we have more complex data that doesn't have a clear definition between first name and surname? DTS can even take care of this, with your help of course.

Within a DTS package we can define VBScript that can be used to manipulate, format, or rerun a process. This gives us great flexibility when it comes to altering our data either before, during, or after the insert to the database.

There are many aspects to DTS packages, and we will only have a quick look at the basics. I suggest that you play with DTS packages in SQL Server 2000 because they rock! And the flexibility and control you have over the import/export process will surprise you.

Before we actually insert the data into our database, we are going to delete all the data out of our database. This will allow us to start from a clean slate. But just before we do this though, let's back up our database.

Backing Up the Database Before the Transfer

You might be wishing you didn't have to do this, but do you want to know the cool thing? You do not have to write the backup statement again! Because we wrote a backup task earlier (see Chapter 11, the section titled "Scheduling Jobs"), we can actually force it to run immediately. This means we can create a full database backup just by right-clicking on the job (under Management, SQL Server Agent, and then Jobs), and selecting Start Job.

You will see that the status of the job is set to executing. When the job has finished, the status will change to either Succeeded or Failed.

So there we go, a backup done nice and simply, with no extra code!

Debugging Stored Procedures

I have performed many tasks in numerous different roles, and one of the most frustrating has been debugging stored procedures. But no more! The ability to debug stored procedures as though we were debugging any development platform code is part of one of the enhancements to SQL Server 2000's Query Analyzer. We can insert break points, step into, step over, and so forth. This is wonderful for those of us who have tried to monitor what is happening in a stored procedure.

Previously we could do this with SQL Server 7.0 and Visual InterDev, but there was a lot of overhead in setting it up. With the new debugging tools, all we do is right- click and select Debug. How simple is that?

We have the standard debugging windows as well. We can get the values of variables from the Watch window and view the procedures that have been called and not completed in the Callstack window. This makes it easy to migrate from a development environment to using SQL Server 2000. So you Access developers out there must be getting really excited by now!

What? No More Room?

One of the trouble spots a DBA must keep an eye on is conserving a computer's most precious resources, memory and disk space.

If we have several databases on one server, we can find that we run out of disk space, and if that happens, our databases will fail.

Of course, in Spy Net's fictional scenario, that could mean World War III! But in the real world, running out of space can still cause serious problems, especially in mission-critical databases such as utilities or emergency response systems. In this section, we look at the causes of resource failure and several ways to avoid down time, including managing file and log size.

How Memory Affects Database Transactions

The memory-deprived databases will fail because tempdb is where most of the changes that you make to your data are performed before they are committed to disk. If you have enough RAM available, SQL Server 2000 will put as much of your database as it can up into RAM. After all, it is much faster to read from RAM than to scan a disk for the information. However, if RANI is a short commodity or you have concerns about the amount of disk space your database is eating, relax, because we have even have control over that.

When you are in Enterprise Manager you have the option to view how much space your data files for your database are allocated and how much is used. To see this information, simply click the SQLSpyNet (or any other) database within Enterprise Manager, and you will see a screen.

Shrinking Your Data Files to Reduce the Database

When we are talking about shrinking our data files we are not actually referring to the process of compacting them like a zip program would.

If we shrink our data files, we remove unused data pages. For example, if we had a table that had five data pages on which it stored the data, and we deleted two pages worth of the data, although our table would have only three pages that actually stored data, SQL Server 2000 still would have five pages allocated to the table.

When we shrink the data files, we just get rid of the extra two pages that the table was using. This does, however, have restrictions, but I think you get the idea.

What do we do when our data files are too large? Although we cannot shrink an entire database smaller than its original size, we can shrink our data files smaller than their original allocation sizes. We must do this by shrinking each data file individually by using the DBCC SHR I NKF I LE Transact-SQL statement. This allows us to reallocate how much space the given data file is allowed to use.

Protocol Negotiation and Session Setup continue…

Readingand Writing

The SMB protocol uses the READ and WRITE message types to perform I/O operations on a file for the client. Using the READ request, a client can request that the server return information from the file by specifying a number of bytes and an offset into the file. The server returns the data, indicating the actual number of bytes returned, which can be less than requested if the user tries to read past the end of a file.

The WRITE command updates a file in a similar manner. The client sends in the data that will be written, indicating the number of bytes to write and an offset into the file where the write operation will begin. If the request causes a write past the end of the file, the file is extended to make it larger. The server sends a response telling the client the number of bytes that were written. If the number is less than the requested value, an error has occurred.

To increase read/write performance, the READ_RAW and WRITE_RAW message types can be used to exchange much larger blocks of information between the client and the server. When these are used, the client must have only one request issued to the server. In one send, the server will respond with data that can be as many as 65,535 bytes in length. The WRITE command works in the opposite direction, allowing the client to send a large buffer of raw data to the server for a write operation.

Locking Mechanisms

Locking allows a particular client exclusive access to a file or a part of a file when it is shared on th network. In SMB, the capability to create a lock is called an opportunistic lock, or oplock for short. This is better explained by looking at the way in which it works. A client can create a lock on a resource using three kinds of locks. The first is an exclusive lock, in which the client has exclusive access to the data held by the lock. A batch oplock is one that is kept open by the server when the client process has already closed the file. A Level II oplock is one in which there can be multiple readers of the same file.

The locking process consists of the client requesting the type of lock it wants when it opens the file. The server replies to the client with the type of lock that was granted when it responds to the open request.

A lock gives the client the capability to efficiently manage buffer space it uses when accessing a file over the network. For example, if a client has exclusive access to a file and is performing writes to it, it can buffer a lot of the newly written information before having to send it to the server to update the file. This can provide a reduced number of network packets when updating a file. A client that has an exclusive lock on a file can also buffer read-ahead data to make reading a file much faster.

These locks are called opportunistic locks for a reason. A client can be granted exclusive access to a file if no other client has it open at the time of the request. What happens when another client needs to read the file? The server notifies the first client that it needs to break the exclusive lock. The client then flushes its buffers so that any data that has not been written to the file is processed. The client then sends an acknowledgment to the server that it recognizes that the exclusive lock has been broken.

Batch oplocks are used to reduce the amount of traffic on the network when some programs require continual reopening of a file to obtain commands, as when a batch command procedure is executed.

For example, a batch procedure executed by the command processor usually opens a file, locates the next line to be executed, reads that line, closes the file, and then executes the command. The problem with this is that these steps are taken for each command line in the procedure, resulting in multiple file open/closes that are not really necessary.

This procedure for reading individual lines from a file is done by using a batch oplock whereby the client can read the data from its local read-ahead cache instead of reopening the file on the remote server to get each line.

Level II oplocks were new with the NT changes to SMB. This kind of lock allows more than one client to have a file opened for reading. When a client must read from a file that is opened by another exclusively, the server informs the current client that its exclusive lock has been broken and is now a Level II oplock. No client that has a Level II oplock will buffer data to or from the file. Thus, after the lock has changed to a Level II oplock (and the first client has flushed any data in its buffers), both clients can continue reading the file.

Printing Protocols and Printing Languages

A printer language is not the same as a printer protocol. For example, PostScript and PCL (Printer Control Language) are languages that describe how a document is to be rendered into the final printed product by the printer. When a printer is directly connected to a printer port on a computer, the printer language is important and is used by the software driver to format the information being sent to the printer.

A printer protocol, however, is used to send the formatted job, both data and instructions compiled using the printer language, to the print device. A few protocols will be detailed here that are more specific in their use and implementation; that is, they generally are used for communicating with a printer.

Several protocols are used for network printing. Some are proprietary protocols used by only one computer or network operating system (NOS). Others, such as 1pr/lpd—which was first developed for use on Unix systems—have been implemented in many environments. Data Link Control (DLC) is an IBM protocol that has been adapted for use on many printers although it is not used much today.

Also covered is the newest printing protocol: the Internet Printing Protocol (IPP), which was created by a working committee of the Internet Engineering Task Force (IETF). Development is underway for standards to further define this new protocol. You will find, however, that both Windows 2000/2003/XP and NetWare 6.x already support IPP. Novell even sells its iPrint as a separate product that can be used in a non-NetWare environment.

Using Ipr/lpd and the TCP Stream Protocols

TCP/IP was originally developed for the Unix operating system (OS), of which there are several flavors. Depending on the version of Unix (as well as Linux) running on a workstation or server, you will find that TCP/IP printing falls into one of two major types:

• BSD (Berkeley System Distribution) Spooling System
• SVR4 (System V, Release 4) Printing System

The BSD system uses the 1pr (line printer remote) program to send files to printers. The printers can be connected to a network, or to a computer. Whichever way, the 1pd (line printer daemon) receives these print requests and interacts with the Ipr to send the print job to the printer. The / et c/ p r int cap text file is used to set up characteristics for each printer. The SVR4 Unix system uses the 1p (line printer) program and the 1psched daemon (printer scheduler) to print files. Although the SVR4 system is considered more sophisticated because it has several utility commands for managing the system, the BSD system probably is easier to manage in a networked environment.

When using either of these methods, the actual print commands and data are sent to the printer in the payload section of a TCP/IP packet.

Although all Unix and Linux systems support TCP/IP printing, many support other protocols as well. For example, Red Hat Linux can also be configured to use SMB (Server Message Block) to connect to a Windows server (or a Unix/Linux computer configured to offer printing services using SMB).

The 1pr/lpd protocols work well, but are mostly used by older operating systems.

TCP/IP stream sockets the Unix provide yet another way to use TCP/IP to connect to a printer. Streams are a two-way communication TCP/IP session between the computer (or print server). When using TCP/IP streams, you need to specify a port (also known as a socket in Windows terminology). Thus the address of the networked printer, paired with a port number, provides a unique address so that the data exchange can be accomplished.

Data Link Control Protocol (DLC)

For all practical purposes, DLC is rarely found anymore within today's networks, although it might still be found on legacy systems. This IBM protocol was widely used during the early days of networked printing, as well as for other applications. However, more robust protocols, such as TCP/IP, have made this protocol a less desirable solution for network printing.

The DLC protocol was developed by IBM primarily for use in connecting to mainframe computers as part of its Systems Network Architecture (SNA) specifications. DLC can also be used to establish terminal sessions with AS/400 computers. In addition to the HP Jet Direct card, you will find that other vendors also make network cards for printers that can use DLC. For example, the Brother NC-600X and NC-2010h network cards both can be used for this purpose. However, you'll find that DLC no longer is present in Windows systems starting with Windows) XP.

System Daemons and Services continue…

Delegating Authority

In a network of any size other than a simple workgroup, it is usually necessary to delegate authority to other administrators or middle-level management personnel. When you find that you must create accounts that have privileges to perform administrative functions, do not give carte blanche access to every account. Keep track of the exact functions an account will be used for, and grant only the access rights and permissions needed.

For example, if an operator will be performing backup functions on a server, he does not need to have full rights and privileges on the server. Under Windows Servers, you can place the user's account into the Backup Operators user group to give him the capabilities he needs, without compromising all files on the system. If you have users who must be able to add or modify user accounts, check the operating-system documentation and give the users access only to the resources and data files they need.

User Accounts

Generic accounts might seem like a good idea at first thought, but they provide nothing in the way auditing. If you simply let one or more users share the root account on a Unix system, or the Administrator account on a Windows server, you will have no way of determining tracking, and resolving who did what when something goes wrong. Indeed, because you can grant the same capabilities to any new account you can create, why not do so?

Give each user who requires elevated capabilities her own account, and grant the necessary privileges to the account. This way you can track each user to be sure she does not abuse her account or use it in a way you do not expect.

When you have more than one user using the same account, there is also the likelihood that the password will be compromised and someone who is not authorized to use the account will do so.

Application Servers, Print Servers, and Web Servers

One particularly common error you can make is to put all your eggs in one basket. Instead of using one server to provide print services or file services or Web services, many administrators use one server to provide all three. This is not necessarily a good idea.

Specialized servers can limit the damage that can be done by intruders and also can make it easier to delegate authority so that a particular administrator can concentrate on a limited set of functions for a certain server. Web servers are particularly prone to attempts by hackers to intrude onto your network. New applications and technologies are being developed and deployed all the time, and the newer they are, the more likely it is that they will have bugs or other loopholes that make them more risky than other applications that run on the network.

Placing sensitive data files on a Web server simply because it is convenient to use the machine's resources is not a good idea if it is also being used as a Web server. Make it more difficult to get at these files by dedicating a file server computer to them instead.

Delegating servers is almost like delegating authority to users. When you divide up resources and partition them into manageable groups, you make it less likely that an attack on one object will result in damage to all objects.

Denial of Service (DoS) attacks are very common on networks now. This kind of attack can be done by a malicious person who takes advantage of a known weakness in a protocol or an implementation of a particular service. One common mistake that administrators make when setting up an FTP site is to place it on an ordinary server.

For example, you might want to have an FTP server that allows customers to log in to your system and download information, patches, or other files. You also might want to be able to let them upload files or messages to your site. If you are going to allow anonymous FTP access, be absolutely sure that the service is configured so that it can access only a dedicated disk or set of disks. Do not allowanonymous access to an FTP service that writes to a system disk or a data disk that is important in your network. It is quite easy for an outsider to simply fill up the disk with meaningless data, causing a system to lock up or crash, depending on the operating system. If an important data disk becomes full, it can cause an extended period of downtime, putting employees out of work for hours while yo try to first determine the cause and then remedy it!

Physical Security Measures

Preventing unauthorized access to resources means that you must first prevent unauthorized access the physical components that make up the network. This includes user workstations, servers, netwo cables and devices, and so on. After the network connection leaves your physical area, such as when you connect to an outside Internet provider, you lose control over the physical aspects of the network. At that point, you must rely on other techniques, such as encryption or tunneling, to mainta security. However, the equipment over which you have control should be closely monitored to en that no one is tampering with anything in a manner that might serve to defeat the security policy effect at your site.

Locking the Door

As silly as it might seem, the simple door lock is an often-overlooked security device. You wouldn't leave your front door at home unlocked all the time, would you? The servers in your network that hold valuable or sensitive data should not be sitting out on a desktop or in an unlocked room where anyone can access them. Routers, hubs, switches, and other devices should be similarly protected. Wiring closets and computer rooms should have a lock on them or be protected by some sort of mon itoring on a 24-hour basis. If you have a round-the-clock operations staff, you might not need to lock the computer room. But if that staff consists of only one person during any particular period, get a lock for the door! Ideally, access to these secure areas will be tracked and logged, such as through employee badge readers. With very sensitive systems, you may even want to go as far as securing physical access through biometric authorization systems. Biometric systems, though still in their infancy, can help ensure that someone cannot gain access to a secure area simply by borrowing or stealing a physical token.

Backup media, such as tapes or writable CDs, should be treated the same as live data. Don't back up a server or your own personal workstation and then leave the tape cartridge or CD lying on the desk or in an unlocked drawer.

Uninterruptible Power Supply (UPS)

Keeping data secure can mean keeping it out of the hands of those who are not permitted to view it. It also can mean keeping the data safe from corruption. As more and more business-critical information is being committed to electronic form, it is important to take steps to be sure that it is not unintentionally compromised. A good UPS will p for itself the first time you have to spend daysreconstructing a database or reinstalling programs that become unusable due to a power outage or another problem of this sort.

Most computer operating systems have features that will work with a UPS so that the UPS can perform an orderly shutdown when it detects that power has been lost. If you are using a battery-backu UPS that has only a limited supply of power, an orderly shutdown can save a lot of problems when compared to a system crash.

Disposing of Hardware and Media in a Secure Manner

When you upgrade your network and bring in new workstations or servers, it is a generous thing to give employees, or an organization such as a school, your old equipment if it is still usable. However, you should establish a policy which dictates that all hard disks are to be erased and, when appropriate, a legal copy of the operating system reinstalled on it. If you leave important information on a computer you give away, don't be surprised when you see it again.

There is also the legal aspect to this. If you give away an old computer system, do you have the legal right to keep the software packages and install them on a new system? Probably not, unless you have a site license or another license that allows you to do so. For that reason, do not give away a computer that has applications installed on it unless you intend to give away the software packages also.

Disposing of used floppy disks, backup tapes, and tape cartridges also poses a potential security threat. It is better to destroy these information carriers than to give them away without being absolutely sure that you have purged them of any recoverable information. A bulk magnetic eraser can be a good security tool to use before disposing of this kind of stuff.

The Two Sides of Security

Locking the computer-room door is a preventive measure intended to keep out those who have no business being there. Preventive measures should be taken for software access mechanisms also. However, no matter how good you are at putting into place the access control mechanisms to protect resources, there is always going to be someone who will try, and possibly succeed, in breaking through. For this reason, you also must be able to keep audit trails of events on the network so that you can determine whether someone is trying to break your security, or whether indeed they have done so.

Before the Fact: Controlling Access

Controlling access to the network is done by several common mechanisms:

• User accounts and passwords

• Physical identifiers

• Resource protections

In many operating systems, the concept of a resource owner is important in this scheme. For example, OpenVMS and Windows 2000/Server 2003/XP keep track of the user who creates a resource, such as a file. That owner is able to change the protections applied to the file and can grant others the permissions needed to use the file. To a lesser degree, the same can be said for Unix/Linux operating systems.

Identifying Users

In a homogeneous network in which only one user account and password is required for access to permitted resources throughout the network, system management is not usually a complicated matter. Windows 2000/XP and Server 2003 allow for the creation of areas of control, called domains, that operate as security boundaries. Users in a domain can be granted access to resources on any computer, either server or workstation, that the network administrator wants to give them. In addition, trust relationships can be established between domains when administrators cooperate, making it possible for the user to still use only one username and password to connect to resources throughout the network. From Windows 2000 and more recent Windows versions, group policies can be used to further control access to important resources