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.

Protocol Negotiation and Session Setup

SMB has a built-in mechanism that is used by the client and server to determine the other's capabilities so that a common protocol version can be established that the two will use for the network con• nection. The first SMB message that the client sends to the server is one of the SMB_COM_NEGOTIATE type. The client uses this message to send the server a list of the dialects it understands. The server selects the most recent dialect it understands from the client's list and returns a message to it.

The response the server returns depends on the type of client. The information includes the dialect selected and can include additional information, such as buffer sizes, supported access modes, time and date values, and security information. After the client receives this response, it can continue to set up the session by using the SESSION_SETUP_ANDX message type.

If the initial server response indicates that user-level security is being used, this message type can be used to perform a user logon. The client sets a value in the message header called the UID (user ID) for the account it wants to use. It also supplies the account name and password to the server by using this message type. If these values are validated by the server, the user can continue to use the UID to make subsequent accesses.

Other setup functions that are performed by using SESSION_SETUP_ANDX include the following:

• Set the maximum values for the size of buffers that will be used in the message exchange.
• Set the maximum number of client requests that can be outstanding at the server.
• Set the virtual circuit (VC) number.

If the VC passed to the server is zero and the server has other circuits open for the client, it will abort those services, assuming that the client has rebooted without freeing those services first. To properly close a session, the client uses the message type LOGOFF_ANDX, which causes the server to close all files associated with the user's UID.

Accessing Files

Other SMB message types are used to traverse the resource directory and to open, read, write, and close files. First, the user must connect to the resource by using the TREE_CONNECT message. The mes‑

sage includes the name of the resource (server and share name) and, for earlier clients that do not perform logons, a shared password. The server responds by sending the user a value called the TID (Tree ID), which will be used in SMBs exchanged for this connection.

After the connection has been established, several basic SMB command formats can be used to manipulate files and directories that reside on the share. For example, the CREATE_DIRECTORY message is used to create a new directory in the file share's directory structure. The client passes the pathname for the new directory, and the server creates the directory, provided that the client has the appropriate access rights or permissions. The DELETE_DIRECTORY SMB message can be used to remove a directory, again based on the functions allowed for the username.

Opening and Closing Files

The OPEN message is used by a client to open a file. The path for the file is given, relative to the file share root. The client specifies the access that is desired, such as read, write, or share. If the file is successfully opened, the server returns a File ID (FID) to the client, which is used to further access the file using other SMB message types; it is similar to a file handle, which most programmers will recognize.

The server also returns data to the client indicating the actual access that was granted, which is read- only, write-only, or read/write. The CLOSE message is sent by the client to tell the server to release any locks held on the resource fileheld by the client. After this message, the client can no longer use the FID to access the file, but it must instead reopen the file and obtain a new value.

When a client does not know the exact name of a file that it wants to open, the SEARCH message can be used to perform a directory lookup. This function enables wildcards to be used, and the server response can include more than one filename that matches the request.

Microsoft Distributed File System (DFS) continue...

Creating a DFS Root

Creating a DFS tree is not a complicated task when using a graphical interface such as the one provided by the MMC and by the wizards that walk you through setting up your file system. To create a new DFS root, use the following steps:

1. Click Start, Programs (All Programs for Windows Server 2003), Administrative Tools, Distributed File System.
2. When the management console appears, select New Root from the Action menu. A wizard pops up. Click Next.
3. You can select to create a file system that makes use of the Active Directory for storing the DFS configuration information. You can also select to not use the Active Directory. For the purposes of this example, I've chosen to not use the Active Directory. After making the selection, click Next.
4. The next dialog box prompts you to enter the name of the server that will host the distributed file system. The default is usually the server on which you are running the wizard. Use the default or enter another server. Click Next to continue.
5. The wizard next prompts you to enter the name of the new root DFS file system. Note that what you enter here shows up in the field Share to Be Used at the bottom of the dialog box. You can use the name of an existing share in the Root Name field, or create the share to be used after you complete this wizard.

6. If you entered a share that does not yet exist, you can now choose the folder to share by entering it in the field or by using the Browse button.

7. Finally, the wizard displays a summary of the information you have entered. Click the Finish button to complete the process of creating the new DFS root.

Adding Links to the DFS Root

After you've created the initial root for your file system, you can add one or more links. Links, as yo will recall, are actual directories (or folders, depending on which terminology you prefer) that will be accessible from your DFS root.

DFS is a domain-wide file system. You can easily browse a file share located on another server in the domain. Thus, you can hide the physical location of files from users. Instead of having to remember servers and share names, the user only has to remember the share name. Because the share name is domain-wide, you can't use the same share name on one server that you do on another. Users can then simply connect to the share \ \domainname\share. They don't need to know the server names on which the actual folders in this share are located.

You can use the Action menu to add or delete a DFS root. You can also use the Action menu to manage links and replication.

DFS does not add any additional security features to the file system. Instead, the usual rights and permissions that are already in place on the server are used when evaluating a client's access to a file or directory in the DFS tree.

One major difference between NFS and DFS is that DFS is built using SMB messaging techniques for the most part and is not compatible with all NFS servers. If you have a mixed-environment network in which most of your data files are offered via NFS on Unix servers, it would be more economical to acquire PC-based NFS client software than it would be to replace all your existing servers with NT DFS servers.

Microsoft Distributed File System (DFS)

Like NFS, DFS employs a tree structure for file systems. A directory that is being imported by a client is attached at a point somewhere in the local file system, where it is then made available to applications as if the directory and its files were local.

The Windows Server family (both 2000 and 2003 versions) includes DFS as an integral part of the operating system. The interface is written as a snap-in to the Microsoft Management Console (MMC) tool, making administration a simpler process. A wizard prompts you through setting up a DFS root, and from there on out you can add, modify, or remove directory paths from the DFS tree. Paths represented in the DFS tree can come from one or more servers on the network. A tree is not bound by a single host.

In Windows NT 4.0, directory replication allowed you to create copies of directories on multiple systems, keeping them in sync. This functionality was replaced in Windows 2000 Server with Distributed File System (though it is also possible to download DFS for Windows NT 4.0). DFS is now included as a part of modern Windows operating systems. DFS allows you to move away from the \ \server\

sha rename concept to one based on the domain. Instead of having to remember (or browse and find) on which server a particular file share is hosted, you can use DFS to create shares that are global to the domain. That is, if you're a member of the domain, you can specify the share as \ \domainname\ share. DFS also allows for replication. This means that you can have more than one copy of the data being shared, but only have to use the global domain-wide share name to connect. This provides for some fault tolerance. If a server goes down that happens to host a replica of a share, then the other servers that contain copies of the share can be connected to by users. Note that this does not provide any kind of failover if a server crashes. The user can, however, restart her work by reconnecting to the share. The connection will be made to another replica of the file share.

Important terms to understand when dealing with DFS are listed here:

• Root—A server can have only one root installed. It is just about the same thing as a file share, with a few differences, which will become apparent shortly.
• DFS link—Under the root you create DFS links. These are links to file shares that will be available under the root you have created. This means you can create a single root yet place multiple shared directories under the root so that only one file share connection is necessary. If not, users would have to connect to each directory as a separate file share.
• Replica—You can create shares that replicate the data in other shares. This can be done to provide for both load balancing and a degree of fault tolerance.