Answers from Microsoft - 20 Jun 2001

Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's group program manager for SQL Server management tools, at [email protected]

I need to set up a SQL Server 2000 active/active cluster, but the information I found on the Internet is about SQL Server 2000 active/passive clustering. Where can I find information about active/active clustering?

Active/active SQL Server 2000 clustering simply requires two or more installations of active/passive clustering. So, you can install SQL Server 2000 on the second node the same way you do on the first node.

I'm experiencing a SQL Server 7.0 performance problem. I have a table with a nonclustered index on OWNER_PER_ID and APPT_REPT_FLG columns and a clustered index on ROW_STATUS and OWNER_PER_ID columns. Before I execute a SELECT statement to retrieve data from this table, I declare variables @P1 through @P5 and set the values Y, Y, N, I-K56, and Y (respectively) to them. When I execute the statement using the parameters, Query Analyzer uses the nonclustered index and performance is poor. However, when I run the same statement and hard-code Y instead of using the variable @P2, Query Analyzer uses the clustered index and performance is excellent.

What's the difference between using the variable @P2 and hard-coding Y in the SELECT statement's WHERE clause? And how should I configure SQL Server to use the correct index when I can't hard-code the value in the SELECT statement?

The plan that SQL Server chooses depends on the statistics on the APPT_REPT_FLG index. SQL Server assumes a standard number of matching rows when SQL Server sees a variable in the WHERE clause. However, when the query optimizer knows the actual value, such as the Y above, you can check the statistics on the index to determine approximately how many rows the restriction will actually apply to. For example, on your SQL Server 7.0 system, if SQL Server sees a variable to match against with equality, the query optimizer assumes that 10 percent of the rows will be returned. Clearly you'll get better results when the query optimizer has all the information possible, including values in the WHERE clause.

I'm running SQL Server 2000 clustered on a Windows 2000 Service Pack 1 (SP1) machine. Whenever I do a failover or move resources to another computer, I get the following Application event-log warning and error message:

EVENT ID 19011

Supersocket info: (SpnRegister):Error 1355

Several SQL Server 2000 Win2K users have posted the same occurrence on the public newsgroup microsoft.public.sqlserver.server. Others report that the problem occurs when the application starts. No one in the forum has an answer. TechNet and the Microsoft Developer Network (MSDN) don't offer a solution either. If I can fix this problem, I'll feel a lot better about putting my SQL Server 2000 running on Win2K SP1 into production. Can you help?

The error message you're getting is telling you that the SQL Server service isn't running as LocalSystem or as a domain administrator, so SQL Server can't register a Kerberos-related Service Principal Name (SPN) in your Active Directory (AD) domain. This error message is expected in your situation, so you can safely ignore it.

In SQL Server 7.0, database users can open a table and view all or some of its data from within Enterprise Manager. I want to secure the tables, and disabling this feature would help immensely. How can I disable it?

Users can open a table or view data through Enterprise Manager only when they have SELECT permissions on the table or view. So, removing users' SELECT permissions prevents users from performing these operations.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.