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.

Microsoft Windows Share-Level Security

Earlier versions of Windows operating systems used the FAT (File Allocation Table) and FAT32 (similar to FAT, but for larger disk volumes, as well as other features) file systems. Beginning with Windows NT, the NTFS file system enabled a more secure file system. FAT and FAT32 don't provide the mechanisms to store security attributes, such as access control lists (ACLs), for files or directories, as NTFS does.

The main benefit of using NTFS is that it does allow you to store a lot more information about a file or a directory. When you use the NTFS file system to format a disk, you can apply user-level security permissions on individual files or directories. You can still create file shares using Windows sever operating systems, but NTFS allows you to further define which files/directories a user can access when using the file share. For an environment that requires a high degree of security, the NTFS partition is the choice to make. Additionally, the Windows 2000 and Server 2003 operating systems allow for other features that make NTFS a more secure choice, including the capability to encrypt and decrypt data on-the-fly when storing or retrieving it from disk. You can also choose to compress data on files so that less disk space is used to store files.

For either of these options, just right-click on a folder and select the properties page from the menu that appears. You'll see an Advanced button on the General tab. After clicking that button, you'll see two important check boxes. The first is Compress Contents to Save Disk Space. The second is Encrypt Contents to Secure Data. Select either or both of these check boxes to enable that feature for the folder.

The only reason to format a disk using FAT or FAT32 is if you are going to dual-boot the computer, and one of the earlier operating systems (such as Windows 9x) will be used. This is because Windows 9x systems are not capable of using an NTFS partition. You can create one partition and format it using FAT, and create additional partitions using NTFS for Windows NT and later operating systems, such as Windows 2000, Server 2003, and XP. However, this sort of dual-boot setup should be used only in an environment where security is not an important issue, such as a standalone computer (one not connected to a network). This is because a FAT partition does not let you set file or directory partitions and does not support encryption.

Another example is in your home, where you don't have such strict security requirements. For example, you might need to use an older software application that will not run under newer Windows operating systems. Even then, if you are connected to the Internet, you should consider the implications of using FAT or FAT32 on a home computer because many hackers regularly scan IP addresses looking for vulnerable systems. If you stay online for extended periods browsing the Internet—or if you're online all the time using a broadband connection such as a cable or DSL modem, then a FAT- based disk is wide open for planting a Trojan horse and other malicious programs. If you use NTFS instead, and set up your user accounts correctly, you can potentially head off this sort of problem. This is because on NTFS partitions you can set permissions for every file or directory on a one-by-one basis.

Single computers and small LANs typically use an out-of-the-box firewall solution, such as a DSL/cable router, which can offer some degree of protection, such as Network Address Translation (NAT). However, by applying permissions on an NTFS formatted disk, you can further enhance your security.

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!