Professional SQL Server 7.0 Programming

The Enterprise Manager

For the purposes of this book, we're not going to cover everything that EM has to offer, but let's take a quick run down of the things you can do from EM:

• Create, edit and delete databases and database objects
• Create, edit and delete Data Transformation (DTS) packages
• Manage scheduled tasks such as backups and the execution of DTS package runs
• Display current activity, such as who is logged on, what objects are locked, and from which client they are running

• Setup web publishing jobs
• Manage security, including such items as roles, logins, and remote and linked servers
• Initiate and manage the SQL Mail Service
• Create and manage Full-Text Search Catalogs
• Manage configuration settings for the server
• Create and manage both publishing and subscribing databases for replication

In addition to the tasks that truly belong to EM, you can also run several of the other SQL Server tools, such as the Query Analyzer and the SQL Server Profiler from EM menu choices.

We will be seeing a great deal of the EM throughout this book.

Data Transformation Services (DTS)

Your friend and mine - that's what DTS is. I simply sit back in amazement every time I look at this addition to SQL Server. To give you a touch of perspective here, I've done a couple of Decision Support Systems (DSS) projects over the years. (These are usually systems that don't have online data going in and out, but instead pull data together to help management make decisions.) They have gathered data from a variety of sources and pumped them into one centralized database to be used for centralized reporting.

These projects can get very expensive very quickly, as they attempt to deal with the fact that not every system calls what is essentially the same data by the same name. There can be an infinite number of issues to be dealt with. These can include data integrity (what if the field has a NULL and we don't allow NULLS?) or differences in business rules (one system deals with credits by allowing a negative order quantity, another doesn't allow this and has a separate set of tables to deal with credits). The list can go on and on - so can the expense.

With the advent of DTS, a tremendous amount of the coding that had to be done for these situations can either be eliminated or, at least, simplified. DTS allows you to take data from any data source that has an OLE DB provider, and pump it into a SQL Server table.

While transferring our data, we can also apply what are referred to as transformations to that data. Transformations essentially alter the data according to some logical rule(s). The alteration can be as simple as changing a column name, or as complex as an analysis of the integrity of the data and application of rules to change it if necessary. To think about how this is applied, consider the example I gave earlier of taking data from a field that allows nulls and moving it to a table that does not allow nulls. With DTS, you can, during the transfer process, automatically change out any null values to some other value you choose (for a number, that might be zero, or, for a character, it might be something like "unknown").

We will be looking into DTS in some depth in Chapter 18.

MS DTC Administrative Console

What is the Distributed Transaction Coordinator (DTC) you ask? Well, the Reader's Digest version is that DTC makes sure that a group of statements that you're running either happen in their entirely, or are "rolled back" such that they appear to never have happened at all. SQL Server has it's own way of managing this kind of stuff, but DTC is special in that can deal with more than one data source even non-SQL Server data sources.

Let's say that you're running a bank. You want to transfer $100 from account "A" in your bank to account "B" in another bank. You wouldn't want the $100 dollars to be removed from the account in your bank unless it really is for sure going to be deposited in the other bank account - right? DTC is all about being sure that it happens this way.

The MS DTC Admin Console is actually more of a monitoring application than something that allows you to change much. Mostly, it just allows you to see what transactions are going through the DTC, and change the rules for logging transactions. (A transaction is a group of things that you want to apply this "all or nothing" rule to either all succeed or they all fail.)

I know I'm repeating myself here - but we will learn much more about transactions and DTC in Chapter 16.

Performance Monitor

Those of you who are familiar with NT's Performance Monitor will immediately recognize this tool. Much like the DTC, the performance monitor is really not about setting things - it allows you to understand how your current settings are affecting the system's performance. The Performance Monitor allows you to check out things like what percentage of reads are coming from cache as opposed to needing to be read off the hard disk. (Reads from the hard disk can be as much as hundreds of times slower than a read from cache.) In short, the SQL Server Performance Monitor is exactly the same as the NT Performance Monitor; save that it starts up by default with several SQLrelated measurements - for example, the percentage of reads coming from the data cache...