SQL Server 2005 Best Practices
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/nKishore/sqlserver2005bestpractices.asp
-
What do you mean by "Avoid code that assumes all metadata is publicly visible"? As of SQL Server 2005 Microsoft is now recommending usage of the (documented) catalog views and dynamic management views for accessing metadata.
And regarding the list of documented columns that return NULL or 0, well all of those columns are part of catalog views that exist only for backwards compatibility with the old system tables. For instance, instead of using sysindexes (which is actually called sys.sysindexes to be exact) you should use sys.indexes.
-
Well written article, but a couple of points.
It doesn't really get into Best Practices much at all. With the exception of some SQL 2000 best practices that will prevent an upgrade, there's almost nothing here in terms of the list of "do this", "don't do that" and "this would be a good guideline to follow."
Also, you talk about database mirroring, yet, according to the documentation provided, this is not currently supported by MS so this can hardly be considered a "Best Practice."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
How can you state "Usage of DBCC commands is not supported in SQL Server 2005"? This is just not true.
Some DBCC commands are deprecated (e.g. DBCC PINTABLE), but such a blanket statement is false.
Terry
-
I agree. I have been using DBCC commands in 2005 for months with few issues. That is an odd phrase.
This article, will ok, was not a best practices. It was more of a "new stuff" article.
The author did not mention that the semicolon is a statement terminator used in t-sql. Using 7 and 2000 you could write sql statements for pages and never use a semicolon. In 2005, using the semicolon is still not mandatory; it is ANSI-compliant.
You will notice that the code from Microsoft uses it and there are a few gotchas. For example, if you use the CTE(which was not mentioned in the article) in a batch of statements, the statement before the CTE definition must have a semicolon.
Quand on parle du loup, on en voit la queue
-
And where is the whitepaper?
"Please refer to this white paper for a better understanding of partitions in SQL server 2005."
Sounds like some cut + paste?
-
I think what Nanda meant was that the meta data is now secured along with access to the objects themselves, so you can't assume that any given user has access to any particular piece of meta data. At least that's how I read it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Yes, that might be what was meant. Not very clear though..
-
Hmmm - not really a best practices list - more a new features description.
Would a best practice be 'turn off CLR enabled' sp_configure option until ordered to turn on by senior management
' -
Well, first of all you do not need to, CLR is disabled by default. But enabling it does not really do a lot unless anyone starts to use it. Being told by senior management to enable it would make me very disheartened, since that is not something they should concern themselves with. But I do think that there are many compelling cases for enabling and using it, even though I am in general quite reluctant of it.
-
I would agree with the other comments, nice article but hardly best practice. One thing that was not mentioned was the use of the Upgrade Advisor, which comes with SQL Server 2005. I would highly recommend this prior to making an migration moves whatsoever as it provides you with a comprehensive list of tasks to be performed prior to migration, and once migrated setting the compatibility level to 90.
For those looking for a resource on Partitioning in SQL 2005 Kimberly Tripp has a good paper on it. http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
I agree with Chris H regarding the use of CLR. While I have not enabled it as yet I am certain that someone will present me with a strong enough business case where I will need to enable CLR in SQL. I have not come to grips with a process for allowing developers to deploy, but one thing is for certain no code will be deployed until I have looked at the code and bless it.
Microsoft is deprecating many commands as we know them today in SQL 2000 for better or for worse. Again running the Upgrade Advisor will assist greatly in the task of mitigating these issue.
John
-
Good review of new features.
-
Is there a "real" best practices guide for SQl Server 2005? If so, google can't find it. Does a best practices guide really exist? This article is definitely not a best practices guide.
-
It's not exactly a "best practices" guide but here is the link to the msdn operations guide (as it currently exists) for SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks for the link. It should help me get started. However, I don't understand how a company can "roll out" a new product without guides on how to use it properly. Thanks for the help DC Peterson, it's much appreciated.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply