SQL, .NET and everything Microsoft

Many of you have asked me the performance impact of:

a)    Running a default profiler trace

b)    Enabling page checksum

Although there is only theoretical information available in BOL, Linchi Shea (SQL Server MPV) has some good posts on empirical analysis on these features.

Recommended that you read them!

Performance Impact of Enabling Page Checksum and Default Trace
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

Performance Impact of Querying SQL Server 2005 Scheduler-Related DMV's

http://sqlblog.com/blogs/linchi_shea/archive/2007/01/18/performance-impact-of-querying-sql-server-2005-scheduler-related-dmv-s.aspx

Happy reading.

Sanchan

Well, as mentioned earlier, the SQL Server Support team now has an official blog and I am busy blogging there.

For all those SQL Server aficionados, I encourage you to visit our blog. You will find really good content out there which will help you resolve some of your issues on your own.

See you there:

http://blogs.msdn.com/sqlblog

As for my articles, you can find them at:

http://simple-talk.com/author/sanchan-sahai-saxena/

I am currently in the process of writing the second installment of my article Fine Tuning your Database Design in SQL 2005. It will be published at Simple-Talk very soon. I will keep you posted.

Thanks

Sanchan

We recently posted a blog about SQL 2005 Security enhancements, focussing on encryption and decryption.

Check it out and let us know what you think.

http://blogs.msdn.com/sqlblog/archive/2006/11/02/part-i-data-security-enhancements-in-sql-server-2005.aspx

We are seeing this issue for many customers installing SQL 2005 SP1.

Check out the resolution for it at our support blog

http://blogs.msdn.com/sqlblog/archive/2006/10/09/_1C20_Unexpected-error-occurred_1D20_-during-SQL-Server-2005-SP1-install_2E00_.aspx

I wrote another article for Simple-Talk.com and it is available to view now.

Fine Tune Your Database Design in SQL 2005

http://simple-talk.com/sql/sql-server-2005/fine-tuning-your-database-design-in-sql-2005/

Let me know what you think.

Sanchan

We have recently added Reporting Services as a blog category for our SQL Server Support Blog.

Would encourage you to check it out.

http://blogs.msdn.com/sqlblog/default.aspx

I am running a survey on Query Notification and would appreciate if you could fill it out.

http://blogs.msdn.com/sqlblog/archive/2006/09/30/778143.aspx

Thanks

Sanchan

Well, I have been blogging at a new blog home.

http://blogs.msdn.com/sqlblog/

This blog is setup by the SQL Server Support team at Microsoft and I have been blogging there for quite sometime now.

Check out this blog for my latest writings. You will find lot of good stuff on SQL Server here.

Cheers!

Sanchan

Sometime ago, I blogged about SQL Server 2005 installation and how you can use Add/Remove programs to add or remove components in SQL 2005.

I recently published a KB article on this – KB 922670.

Please refer to it for more information on this topic and future updates from MS’s side.

Many customers have asked me if they can install SQL 2000 and SQL 2005 on the same box, side by side.

The answer to this is YES.

You can install SQL 2000, SQL 7.0 and SQL 2005 on the same box. However, there are certain special considerations that you should be aware off.

Co-existence of multiple versions of SQL server

First, refer to this comparison sheet published by Microsoft at the MSDN website.

This sheet can be used as a handy reference to decide if your planned configuration is supported or not.

What next?

If you already have SQL 2000 installed, then you have to make sure that it is atleast SP3 and above before you attempt to install SQL 2005.

This is very important and critical because instances of SQL 2000 prior to SP3 don’t relinquish port 1434 when SQL Browser Service starts.

Since SQL 2000 is already running on the machine, SQL listener service is listening on UDP port 1434. When you install SQL 2005, it installs SQL Browser service and tries to start it. While starting, SQL Browser service will wait for 5 seconds for SQL 2000 to relinquish port 1434. If it doesn’t, then Browser service will not start.

If SQL 2000 is below SP3, then you have to stop SQL 2000 before you can install SQL 2005.

In case you are building a new machine and have to install SQL 2000 and SQL 2005, install SQL 2000 first and then move to SP3 or above. Then install SQL 2005.

UPDATE 09/28/06

I blogged more about it here.

Today, one of my articles got published at Simple Talk Magazine in the UK.

In this article, I take a deep dive into the nuts and bolts of implementing SQL Server 2005 Query Notification using SQLCacheDependency object in ASP.NET 2.0.

You can take a peak at it here:

http://www.simple-talk.com/sql/sql-server-2005/using-and-monitoring-sql-2005-query-notification/

As a next step, I will be posting a FAQ for all the questions that I receive through this article and through my blog on Query Notification.

If you have any questions, please feel free to leave a message either at my blog OR at the simple-talk website and I will be glad to answer them.

Thanks and have a great time reading this article.

Well, the last month has been really hectic and I have been involved with a lot of stuff that I never planned for – as you can see, I didn’t get much of a chance to blog this month.

But now that things are back to normal pace, I am coming back!!!

Recently, one of my customers had an Eval edition of SQL 2005 which expired. But they had some critical data that they wanted to retain. I helped them out and thought will blog about it too.

Eval Edition of SQL 2005

SQL Server 2005 Evaluation edition provides the same functionality as the Enterprise Edition – however, it has a pre-set expiration date.

You can learn more about the eval edition and download it from here.

Upgrading from Eval Edition

In an ideal scenario, you would install SQL 2005 Eval edition to evaluate the features and then upgrade to the full blown versions (Standard or Enterprise Edition) for production use. For more supported upgraded paths from Evaluation Edition, refer to the Version and Edition Upgrades section at MSDN.

To perform an in-place upgrade of SQL 2005 Eval edition to other SQL Server 2005 full blown editions, simply run the setup and it will identify the existing installation, and will run an upgrade check. It will then let you know if the upgrade is possible or not. If the upgrade is possible, simply go through the setup wizard and it will automatically upgrade the already installed instance of eval edition.

Upgrade from Eval Edition 2005 after it has expired

Now, this becomes a little tricky. You have to first uninstall SQL 2005 Eval edition and then install the full blown edition on the machine.

In case you want to keep the existing databases, you can use the SAVESYSDB parameter at command prompt. For more information on this, refer to KB 914158

In case you are still running SQL 2000 Eval edition (I would doubt that), you can refer to KB 281574 for steps on how to upgrade the same to a full blown edition of SQL 2000.

This is the first installment in my FAQ series.

In this blog entry, I have consolidated all the questions that I have received from my readers about available disk subsystems in the market and their compatibility with SQL Server.

I have tried to address them here.

Q : I have an unused NAS (Network Attached Storage). I wanted to know if SQL Server supports files located on NAS and if there are any issues that I should be aware of?

A : By default, SQL Server doesn’t support data files located on a NAS. However, trace flag 1807 overrides this behavior.

With NAS, there are special considerations for SQL Server performance that you should be aware off. Please refer to the KB 304261 for more information on this.

Q : Does Microsoft recommend SAN over NAS or vice versa for SQL Server?

A : Microsoft generally recommends that you use a Storage Area Network (SAN) or locally attached disk for the storage of your Microsoft SQL Server database files because this configuration optimizes SQL Server performance and reliability.

However, any certified NAS device can also be used with SQL Server. Please refer to the KB 304261 for more information on this.

Q : I have heard that SQL Server can be used Internet Small Computer System Interface (iSCSI). Is that true? If yes, then are there any issues that I should be aware off?

A : Yes, SQL Server can be used with iSCSI and Microsoft supports this configuration.

However, Microsoft recommends that you use special caution here. For more information on this, refer to KB 833770.

To check if your iSCSI hardware components are qualified, refer to the Microsoft iSCSI Hardware Device Qualified list here.

Q : Many third-party vendors are providing disk drive caching solutions. Can SQL Server be used in conjunction with these solutions? Are there any known issues?

A : Caching controllers and disk subsystems can be safe for use by SQL Server. However, you should check with your hardware vendor to be sure that the disk subsystem has been specifically tested and approved for use in a data critical transactional relational database management system (RDBMS) environment.  

For more details, refer to KB 86903.

Also, SQL Server implements Write-Ahead Logging (WAL) which ensures data integrity and recovery.

Refer to KB 234656 article that describes SQL Server’s implementation of WAL and how it interacts with the hardware based caching solutions.

Q : What is the difference between System Area Network and Storage Area Network?

A  : SAN terminology is used interchangeably for both System Area Network and Storage Area Network. However, there are differences in their implementations. Refer to KB 264135 for more details.

I will continue to update this post with more such questions. Also, let me know if you have anything on your mind that you would like me to address or cover through this blog. Thanks.

Just a quick note, Microsoft has published a great whitepaper on TempDB. You can download it here:

http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc

I will add more blog content about TempDb soon.