TechNet Top Questions - April 16, 2000
On This Page
SQL Server OLAP
Moving on Up to Multiprocessors
Exchange 5.5's Endless Appetite?
SQL Server OLAP
Brian Carlson, IT Pro extraordinaire, would like to make his SQL Server OLine Analytical Processing data available to his customers via the Internet. Here's his question.
Q. I am looking for some discussion/documentation on the features of OLAP. Some of the things I would need to do and don't know if they're possible are:
Can the MSSQLServerOLAP service be installed on a clustered server?
Can I publish/install the OLAP database on the Internet?
Are there generic products/components to allow viewing of the OLAP cubes?
I can't propose a solution that will require users of our site to purchase licenses.
These things are necessary so suppliers of products to our web site can view their sales information in a cube that I'll create. I don't know if OLAP is the tool I should be looking at but I have developed a CUBE and it has the reporting we want exactly.
Here's what IT Pro and database guru Simon had to say to Brian:
A. MS OLAP Server can be distributed almost anyway you want. You can administer any number of OLAP Servers from the Enterprise Manager.
We have used Seagate's "Seagate Info" product to deliver Microsoft OLAP cubes and more conventional Crystal reports to our user base. This product can distribute through a number of different interfaces. Seagate Desktop, Outlook, Web etc..... Suggest you have a look at their offerings, especially as the product is very cheap and extremely functional.
No I don't work for them, but I have worked on MDDB's for over 5 years and for the bucks this delivery mechanism is the best I have seen.
Kind Regards,
Simon Sutcliffe
Now Brian asked for documentation. Brian asks, I provide. What follows are TechNet Technical papers, and an ever growing list of books devoted to the subject of OLAP and Data Warehousing. This list should be useful to many IT Pros that are either looking into SQL Server OLAP capabilities for the first time, as well as those that are already familiar and want to dig even more deeply.
Microsoft TechNet Technical Articles
MS SQL Server 7.0 OLAP Services
MS SQL Server 7.0 Decision Support Services
The MS Data Warehousing Strategy
Books
The Data Warehouse Toolkit : Practical Techniques for Building Dimensional Data Warehouses
or from Wiley's website.
Professional Data Warehousing with SQL Server 7.0 and OLAP Services
SQL Server 7 OLAP Developer's Guide
Data Warehousing with Microsoft SQL Server 7.0 Technical Reference
Data Warehousing with MS SQL 7.0
Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
Books In the pipeline
Unlocking Olap with SQL Server 7 and Excel 2000 (publish date: May, 2000)
A Practical Guide to The Microsoft Olap Server (publish date: June, 2000)
Olap Services Fundamentals : Online Analytical Processing Technology for Microsoft SQL Server (publish date: September, 2000)
General (not Microsoft SQL Server specific)
Data Warehousing, Data Mining and OLAP
Gives comparisons on different vendor's RDBMS, OLAP, and Data Mining tools
Moving on Up to Multiprocessors
This next question seems innocuous enough, but I think it's a good one. IT Pro Justin Furch posts the following question, with a misleadingly straightforward subject "Multiprocessors." Justin's question:
Q. Does anyone know if Windows NT will automatically recognize the addition of a second processor, or do I have to reinstall the OS for it to utilize it?
In response to Justin's question, IT Pro Takao Hojo has the following advice:
A. The following Knowledge Base article is a complete guide to your environment:
"How to Manually Add Support for a Second Processor"
https://support.microsoft.com/default.aspx?scid=kb;en-us;156358&sd=tech
One important thing is to carefully maintain after setup. This is because there are still multiprocessor specific problems, which are in the improvement process.
Next, fellow IT Pro Robert Moir is generous enough to spend a not-insignificant amount of his time to go into to some pretty darn good detail. Robert also includes some really good advice about what should be done if the whole thing goes into theU well, let's say "dumpster." I've added a small piece (step 0) to Robert's excellent recommendations.
A. Basically you need to run a program called UPTOMP.EXE, which is available in the Windows NT Resource kit. What it does is upgrade the HAL to a multiprocessor version. Some hardware vendors have their own utility to do this (I'm thinking of Compaq here but there probably are others too) and you should use their utility if there is one available for your machine.
When it works well it works very well. Basically I personally have a 100% success rate with this tool, but I know people who have been just as correct in their approach as I was when I did it last, who had trouble. I know one person who did several identical servers in a row and they all went fine except the last machine, which went BSOD on them.
As for time, once you have done it a few times it can go pretty fast, but you should ALLOW enough time to reinstall Windows NT and restore from backup in your schedule for this work, just in case things go wrong. So a day is about right. There is a lot of stuff in the knowledge base which whoever is doing the upgrade should read beforehand but basically, to cut a long story short, this is how I did it.
Document any hotfixes that have been applied to the system. You can use HOTFIX.EXE, which is shipped with any post Service Pack 3 hotfixes that you've received from Microsoft Product Support. If you do not have this utility on the system, then you probably have not applied any hotfixes. More information on HOTFIX can be obtained from Knowledge Base article "166839 - How to Apply Windows NT Hotfixes Along with Windows NT Service Packs"
Backed up my victim server - Every piece of every drive, no exceptions. Probably overkill but I'd rather recover a server from tape with more data than I need on the backup than not enough data.
Verified that my backup was good, and that I could read from it to perform a restore if I had to. Update and verify integrity of Emergency Repair Disk.
Checked the event log for any signs of lingering problems that might leap up and bite you whilst doing the upgrade. If you find a sign of any, clear them before proceeding (you might want to do a reboot as part of this)
Shutdown server
Install new processor(s) - make any motherboard jumper changes required as per manual.
Boot to CMOS. You should see the system report two processors at this point. If you need to set anything in the bios to inform the system of your work do so at this point.
Once you are sure the system can see the 2 processors, reboot and allow Windows NT to start.
Run UPTOMP.EXE. Supply the original Windows NT CD when required, specify the correct type of HAL for your system (Which for most machines is MPS, but you should check with your h/w vendor) and allow uptomp to install it.
When the installation is complete DO NOT REBOOT THE SERVER YET, despite the fact that UPTOMP.EXE will be inviting you to do so, leave that reset button alone.
Apply your current service pack, and once that is done restart the computer.
You should see Windows NT report two processors during startup.
Re-Apply any hotfixes etc you have currently on the system, rebooting as needed.
Rinse and repeat for however many systems you want to do.
Congratulations! Enjoy your new multiprocessor system(s)
If you are unsure about any of this, get experienced help. btw... Did I mention backing up your system before even attempting this?
Robert Moir, Backoffice MVP
To search the MS Knowledge base use the link: https://support.microsoft.com/search/?adv=1
Exchange 5.5's Endless Appetite?
Next, IT Pro William R. is trying to figure out why his Exchange 5.5 seems to be eating up any and all memory that he throws at it. Exchange has a healthy appetite, but it knows when to put it's fork down and say "that's enough."
Q. I'm trying to figure out if something is amiss with my Exchange setup. I have a small Exchange implementation with 10 users. Four users use the exchange mailbox located on the exchange server, six others get their email daily via POP3 and download mail to PST files on their laptops.
When looking under Services in Task Manager I noticed that Store.exe (which I assume is the information store for Exchange) alone was taking up about 60 megabytes of RAM. the exchange server had 128 meg of RAM and was constantly running at 180 meg used (requiring a fair amount of paging I'd suspect.)
The RAM usage seemed excessive to me, but I just figured that Exchange was a resource hog, so I added an additional 128 meg of RAM. Now the system has 256 of physical memory and Store.exe has ballooned overnight to over 90 meg! Surely this can't be right for such a small exchange implementation....Can it?
Interestingly, when I stop all exchange services and restart them, the Store.exe drops down to only about 6 meg of RAM and then over time creeps back up to the 90+ meg that it currently utilizes. It does seem to stabilize, so I don't think there's a memory leak. If I add additional RAM is it just going to get gobbled up too? Any ideas why Exchange would seem to be so bent on making my server do a lot of paging? Am I missing something simple in the configuration? Any suggestions would be greatly appreciated.
Configuration:
256 meg RAM
Exchange 5.5, sp3
NT Server 4, sp 6a
10 users (four use mailbox, use pst files not located on server)
16 gig HD
IT Pro Tim Cox is quick to reply with this explanation:
A. Documentation shows that store.exe will use as much memory as it can get its greedy paws on and will release to other apps if and when necessary. That's the way a good application should work, isn't it? When you boot the server it drops to only that amt needed to start the services. As you open email and such it stores it (no pun intended) in RAM until booted by another process.
Well, Tim's right on the mark – Exchange will use whatever memory is available. If another application starts and needs a certain amount of memory, Exchange will give that memory back to the system (NT), whereupon NT makes it available to the other application.
There are a couple of pretty good Exchange 5.5 technical papers available on the TechNet website that explain things. The first is called "Best Practices for Exchange Database Management"
How Exchange Dynamically Changes Its Buffer Size
Before Exchange Server version 5.5, the buffer cache that the database engine used was of a fixed size. If Exchange was thrashing and needed more memory, the administrator had to manually change the buffer size. With Exchange 5.5, however, the database engine team made some innovative changes to the way that the database engine uses memory. They built dynamic buffer allocation into the database engine, so that the database engine dynamically grows or shrinks depending on how much memory is available and whether there is back pressure from other services running on the Microsoft Windows NT® Server-based computer. If memory is not being used by other services in the system, such as Microsoft Internet Information Service (IIS), the Exchange database engine will take up as much memory as it needs. When other services need memory, the Exchange database engine will give up some of its memory by flushing pages to disk and shrinking the size of its buffer.
The second technical paper is titled "Planning Your Implementation of MS Exchange Server". The paper defines a twelve-step process that customers should consider prior to implementation. This paper is designed to assist customers in the planning, optimizing and piloting of their design. Readers should note that the intent of the guide is to highlight key considerations but should not be considered a substitution for the Microsoft Exchange Server documentation.
One very useful tool that is included with the Exchange 5.5 product is the Performance Wizard. Here's a little taste of what this paper has to say:
The Microsoft Exchange Server Performance Wizard
Microsoft Exchange Server includes a planning and system optimization tool that will ensure that you get the most from your Microsoft Exchange Server computer. Based on your system configuration, the easy to use Performance Wizard will make recommendations concerning Microsoft Exchange Server file locations, memory usage and various other system parameters which will affect the performance of your Microsoft Exchange Server. Be sure to run Performance Wizard when you install Microsoft Exchange Server and whenever you change your system configuration.
These are good papers. Check them out!