TechNet Top Questions - December 6, 1999

Article
12/22/2008

Greetings! Here we provide detailed answers to popular questions submitted to TechNet via the answer forums and feedback alias. TechNet's Lon Collins, Microsoft Support Professional, provides you with answers and tips that can help many of you working in the trenches to deploy, maintain and support Microsoft products.

This Week:

Controlling outside users access rights to your Web Site

Michael Linning asks:
We are setting up an extranet project using Site Server 3.0 which will provide users with different type of functions such as Ordering, Order Status, Account Status, etc.

What's the best way to define these access rights and attach them to users? Membership Directory, P&M, database or other?

In addition, we want to have attributes such as Order Limit (can only order up to $50,000). I think the Membership Directory would be best for this?

Michael:
The Microsoft Market sample site included in Microsoft Site Server 3.0 Commerce Edition has examples that will guide you. In addition to the Market sample site, the West Coast Sales sample site has examples that set both a credit limit and a minimum purchase amount. By using these sample sites as a reference you will be able to implement all of the features you describe.

SQL Server performance problems - what to do?

Konstantin Krivtsov posts a question to the SQL Server Answer Forum:
I need to know what causes problems with performance on my server - is it hardware configuration, or can improperly tuned SQL cause these problems? When I monitor the performance on the loaded server, it periodically gets 100% CPU and hard disk usage for a couple of minutes with 15 users.

My database is about 600 mb in size, with largest table about 450000 rows in it. I need to understand if a hardware upgrade can help me, or it would be just money spent in vain.

Current server config is PII/400 256Mb RAM Asus P2B Seagate Cheetah 4.5 GB.

Thanx in advance Konstantin:
The answer to the question "is it hardware configuration, or can improperly tuned SQL cause these problems?" is Yes!

Performance tuning, troubleshooting and optimization any relational database is such a large and interrelated topic that it is impossible to say one way or another that "yes, you need another processor" or "re-index your tables". In other words (here I get to use my favorite relational database introductory reply), "It depends". It depends on how the queries are written, the indexing implemented, the amount of data increase and change, user activity, and, yes, hardware - just to name a few. With SQL Server (as it is with any RDMS product) it can also depend on what version of the product you have there may be new features in a later release that can enhance performance.

Regarding SQL Server, there have been several enhancements made to version 7.0 that affect query performance (in addition to other types of performance, like backup/restore to mention one). So, what this boils down to is that you need to analyze the specifics of your system that impact performance. Every SQL Server database implementation will be different which does not mean that there arent guidelines that database administrators can employ to analyze and improve query performance on their SQL Servers. There are resources to help you with that. Here are some that will get you started.

Books Inside Microsoft SQL Server 6.5 from Microsoft Press

Inside Microsoft SQL Server 7.0 from Microsoft Press. .

Note: Both books include chapters on Design and Query Performance and on Configuration and Monitoring for Performance. Check out Chapter 14, Optimizing Query Performance, from Inside Microsoft SQL Server 7.0 on the TechNet site.

Microsoft SQL Server 7.0 Resource Guide from the BackOffice Resource Kit 4.5. Contains a section called "Performance Tuning" that addresses the server tuning issues in great detail. Also, in the same book, there are chapters that address the Index Tuning Wizard, the SQL Server Query Processor, memory planning and management, and other performance-related topics. This resource guide goes into some fairly deep detail.

Knowledge Base Articles

Q86903 "SQL Server and Caching Disk Controllers"

166967 "Proper SQL Server Configuration Settings"

For SQL Server version 4.2x through 6.5, please see 110352 "Optimizing Microsoft SQL Server Performance"

Two excellent white papers, both authored by Henry Lau:
"MS SQL Server 7.0 Performance Tuning Guide"

"MS SQL Server 6.5 I/O Performance Tuning Quick Reference"

How to avoid getting stuck in a Windows 2000 corner

This question comes in to the Windows 2000 Forum:

"As far as I can see, I cannot create a repair disk in either Beta 3 or RC2 and even if I could the files wouldn't fit!"

Answer: The way you create an Emergency Repair Disk (ERD) in Windows 2000 is different. Windows 2000 does not include the Rdisk.exe program that was used in Microsoft Windows NT 4.0 and earlier versions to create an ERD. To create an ERD in Windows 2000:

Click Start, point to Programs, point to Accessories, point to System Tools, and then click Backup.
On the Tools menu, click Create an Emergency Repair Disk.
Insert a blank, formatted disk in drive A, and then click OK.

The registry in Windows 2000 is too large to fit on one disk. Therefore, the registry hives are no longer included on the ERD. With the ERD in Windows 2000, you can only:

Inspect the startup environment
Verify the Windows 2000 system files
Inspect the boot sector

Upgrade from SQL Server 6.5 to SQL Server 7.0 stopped cold

IT Pro Doug Farr posts in the SQL Server Forum:
I am upgrading SQL 6.5 to 7.0. I am at 'Import Server Setting from Master' when I get the following error message:
**Export Logins-1.err**
LOGINSID: Unable to open SQL Server registry key

Doug:
Try this.

Open REGEDT32 on the 6.5 server.

Change Registry Key permissions on the 6.5 server to FULL CONTROL for the Everyone group for the following registry keys. Be sure you check "Replace all Subkeys".

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\MSSQLSERVER
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\SQLEXECUTIVE
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLServer

Stop and Restart MSSQLServer on the 6.5 machine and try the upgrade again.

That is it for now. Check out the new TechNet Top Questions on December 20, 1999.

Illustration by Elizabeth Anderson, MSN Staff