I just got back from an on-site assignment in Denver. W were migrating to SQL 2008 on a new set of Windows 2008 clustered servers and ran into an installer bug. We completed the install of the first node but the install failed for the second node because of an invalid product SKU. The SKU was valid. It was slipstreamed in by Microsoft when the product was purchased and downloaded. It appeared correctly in the SKU input box just before the error. We tried several times with the same result and eventually called Microsoft.
Microsoft is well aware of the bug and has a hotfix which, unfortunately, didn't work for us either. Microsoft and others have also posted workarounds which all involve running setup from the command prompt with a varying number of parameters, depending on who you are talking to. Microsoft's example has an enormous number of parameters which you can find by following links from these pages: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036
http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/13a256b0-b95b-46a7-92d5-17a0b763f8dd/
What finally worked for us was very simple: Execute this in the command prompt while logged in as an admin:
setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER"
By the way, the install process for clustered instances has changed significantly. Now the cluster wizard has been broken into two wizards, one creates a single-node cluster (whatever that means). Then you must go back to the setup start screen and run the Add Node wizard for each node you want to add. You must run it from the node you want to add, not the primary node. SQL Server binaries are not moved to the second node until you do that.
Tuesday, December 23, 2008
Friday, December 5, 2008
Amateurism and SQL Server
The Marketing department drives everything at Microsoft, especially product design and development. A while back this group decided that SQL Server was an "administration-free" database. Their premise is that my grandmother should be able to design, construct and manage an enterprise data platform without having to bother about learning much of anything.
For example, the table creation wizard allows a completely unqualified person to actually create a table. It defaults to creating a primary key on an identity column using a clustered index.
This does insure that the table 1) has a PK 2) has a clustered index, and 3) the clustered index isn't going to cause page splitting, even though the table creator has little idea what any of this means. This is a good thing for someone with a small startup that needs a database and can't afford professional help. However, I see it in large corporations done by people who are considered professionals.
I have had discussions with many of them and found that because the wizard's default behavior, They believed that a primary key HAD to be clustered. Also because of the wizard's defaults, they have confused an identity with a real primary key. When I ask them what other column or group of columns is unique in the table, they don't know what I'm asking.
While helpful for an amatuer application, these defaults virtually assure that the database will not scale as a company grows. The placement of the clustered index is critical to good performance. Placing it on an identity in every case is a big mistake.
A nonclustered index will handle an identity lookup as well as a clustered index, so why waste the only clustered index on something that can be done as well by a nonclustered index? Clustered indexes can do certain things much better than nonclustered indexes, like range searches returning many records.
It only makes sense to place the clustered index on a column where it will make a difference. However, deciding which column to place it on requires knowledge and creative thought of which the wizard is not capable and apparently many db designers are not either. It is not that they don't know how to handle the issue: it is that they don't even know there is an issue.
Next post, why an identity is not a primary key.
For example, the table creation wizard allows a completely unqualified person to actually create a table. It defaults to creating a primary key on an identity column using a clustered index.
This does insure that the table 1) has a PK 2) has a clustered index, and 3) the clustered index isn't going to cause page splitting, even though the table creator has little idea what any of this means. This is a good thing for someone with a small startup that needs a database and can't afford professional help. However, I see it in large corporations done by people who are considered professionals.
I have had discussions with many of them and found that because the wizard's default behavior, They believed that a primary key HAD to be clustered. Also because of the wizard's defaults, they have confused an identity with a real primary key. When I ask them what other column or group of columns is unique in the table, they don't know what I'm asking.
While helpful for an amatuer application, these defaults virtually assure that the database will not scale as a company grows. The placement of the clustered index is critical to good performance. Placing it on an identity in every case is a big mistake.
A nonclustered index will handle an identity lookup as well as a clustered index, so why waste the only clustered index on something that can be done as well by a nonclustered index? Clustered indexes can do certain things much better than nonclustered indexes, like range searches returning many records.
It only makes sense to place the clustered index on a column where it will make a difference. However, deciding which column to place it on requires knowledge and creative thought of which the wizard is not capable and apparently many db designers are not either. It is not that they don't know how to handle the issue: it is that they don't even know there is an issue.
Next post, why an identity is not a primary key.
Subscribe to:
Posts (Atom)
