In continuation with the BOL update topic on SQL2008-RC0_BOL post here I have had troubles in installing the RC0 Books Online on to my laptop.

As per the error below I thought it may be problem with the Windows Installer service on my laptop (Vista Ultimate) and tried to install afresh on a central monitoring server (Windows 2003 server) & local machine (Windows XP), still no luck to install the documentation.

On my laptop I have Windows Installer service 3.1 and that is not sufficient to carry out this BOL installation, so searched Microsoft KBA to see if there is any latest version is available. Bingo, here it is Windows Installer 4.5 is available and you need to download and apply on to the machine where you want to upgrade the SQL Server 2008 RC0 Books ONline documentation.

Hope this helps.

As in continuation with the latest release of SQL Server  2008 that is Release Candidate, I strongly recommend you update your local copies of SQL Server Books ONline aka BOL.

Here is the link to download the latest copy of SQL Server 2008 RC0 BOL - Link_to-Download and also I recommend the SQL Server 2008 Compact Edition documentation upgrade, such as CompactEdition information.

Usage of SQL Mail is quite common within SQL Server environment, recently I have involved at one of the client's site with the error:

Msg 17930, Level 16, State 1, Line 0

xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

Further details on the issue the server (Windows 2003) has been upgraded  from SQL Server 2000 to 2005, no issues reported on database side except on the alert side of sending the emails for failed jobs. The first question I have asked them is that why not use Database Mail instead of SQLMail as DBMail qualities include (as per BOL):

• No Microsoft Outlook or Extended Messaging Application Programming Interface (Extended MAPI) requirement. Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. You can use Database Mail without installing an Extended MAPI client on the computer that runs SQL Server.
• Process isolation. To minimize the impact on SQL Server, the component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.
• Failover accounts. A Database Mail profile allows you to specify more than one SMTP server. Should an SMTP server be unavailable, mail can still be delivered to another SMTP server.
• Cluster support. Database Mail is cluster-aware and is fully supported on a cluster. 

Due to the legacy application that was used here will have only use SQLMail, so thats the story to keep with this mailing solution until it is re-written to use Database Mail. So as per the pre-requisite for SQLMail they have installed Outlook 2000 with service pack3 and default mail is outlook with relevant mapi profiles existence. As usual search under Knowledge Base articles referred the links such as:

How to configure SQL Mail to use an Internet mail server by using Outlook 2003

How to configure SQL Mail

Common SQL Mail problems

Scalability is an important aspect in every application, a common achievement within OLTP environment and still applicable to OLAP (Anlaysis Services) environment. In any if the application or a cube or mining model may be unavailable for querying because of a hardware or software failure, also it could be one of the reasons such as unavailable because it needs to be processed.

In this case running multiple instances of SQL Server can be managed easily in terms of performance & scalability, this doesn't applicable on an easy terms for Analysis Services applications as it may cause performance issues. It may be general recommendation that hiking the processor, memory, and disk resources on the server, but more you need to concentrate on scaling the instances of SQL Server and Analysis Services across multiple computers. To achieve this say if the relational database resides along with an Analysis Services database that is suffering hard performance issues, then you can plan move these databases to a separate computer. As a baseline you need to consider the network speed and bandwidth that exist between the Analysis Services database and its underlying databases. Many times the usual suspect of network problems are missed due to the reason of slow or congested, moving the underlying databases to a separate computer will cause affect processing performance.

When you talk about Scalability then Clustering comes into mind as it can be used to scale out the applications, but if query performance is poor but you cannot increase the processor and memory resources on the local server, consider deploying an Analysis Services project onto two or more production servers. In this case you may need additional component such as Network Load Balancing application to make these instances into that cluster.

A simple list of configuring the scalability environment is goes by:

• Ensure to keep similar level of service pack/hot fixes on all the SQL Servers, Reporting Services & Analysis Services instances if the databases (relational or Analysis Services) are shared  across the servers.
• In  case of Reporting Services server ensure to test and apply the update on all the associated reporting servers that serve the same scalable shared database or databases.
• Microsoft recommendation on Scalability side of hardware that no limit on the number of instances, but having a baseline on shared database configuration to 8 servers per database.
• In the case of Reporting Services database and volumes, make sure to keep mounting a set of reporting volumes onto your reporting servers, use the same drive letter on every server to facilitate managing the volume across the different servers. 
• In terms of Collation & Sort order ensure to keep all of the instances to use same sort order.
• Ensure to maintain same set of hardware components such CPU, Disks and even memory settings in similar on all of the instances. In general it is better to go, otherwise if any issue arises it will be hard to identify on which instance it has gone wrong.
• If the relational or reporting database is spread across the shared servers then ensure to test and deploy synchronized update than a rolling update of scalable shared database.

So when talking about new product features you would be looking at scalability & performance features that can be achieved, similar to this SQL Server 2008 has such advantages. Refer to this  New-Data-Warehouse_Scalability_Features within SQL Server 2008 that introduces the new performance and manageability features for data warehousing across all these components. All these features contribute to improved scalability

Did you see the new branded logo and desktop background for SQL Server 2008? 

Until recently many users asked about logo for SQL Server 2008 as Windows Server 2008 & Visual Studio 2008 has got their own logos to represent.

This new logo for SQL Server 2008 is entirely different and the theme is created as:

Enabling people to manage, mine, and interpret data, with high satisfaction and success.

Also see what kind of branding colors Microsoft has adopted from this DataPlatform-Insider blog post. 

This is a change to SQL Server 2005, your data any time, any place.

Ok its time to change above blog theme too .

Are you visiting Tech-ed, North America?

Then Don't miss the opportunity to know more about Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy.

Tech·Ed offers the premier technical education conference just for IT professionals. Microsoft and industry experts will share their expertise about how to architect, deploy, manage, and help secure a connected enterprise. With only a few weeks to go, there’s still time to register for Tech·Ed IT Professionals. Don’t miss out on all the great content and learning opportunities. Register now.

This year I'm leading the Panel Discussion topic on Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy.

Who should attend?

Architects, DBAs, Developers, IT Pros & Database Managers...

What is your take-away?

This is first time Microsoft has divided the TECH-ED North America conference into 2 sections, Developer & IT-PRO. 

Why Attend Tech-ed?

IT Pros, you'll gain new solutions and new ideas for being more productive from sessions, product assessments, and interaction with experts and peers.

• Gain real-world learning from the Microsoft IT support staff--your IT professional peers who are finding and addressing critical issues before you deploy.
• Acquire insights from Microsoft and industry experts on the products you work with every day, and improve the security, interoperability, troubleshooting, and management of your existing IT infrastructure solutions now.
• Get up-to-speed on the latest technologies, and prepare for the challenges of tomorrow.

Developers will come face-to-face with the cutting edge of the latest development trends and gain hands-on experience with the newest and coolest development tools and platforms.

• Acquire the skills you need to build more streamlined, scalable applications.
• Explore considerations and real-world guidance for programming mobile devices, distributing applications as a service, building Windows Embedded solutions, developing Windows Vista compatible applications, and integrating Web solutions.
• Speed up your application development time by putting into use Microsoft application lifecycle management tools. Gain knowledge of the latest trends and upcoming technologies to address your toughest business challenges.

So What I'm doing there?

Presenting 1 BreakOut session:

DAT373 Self-Taught Database Administrator Techniques from Microsoft SQL Server 2000 to 2008

1 TLC Session: 

DAT58-TLC Microsoft SQL Server 2005 Online Index Operations for DML Concurrency: Guidelines and Best Practices

Panel Discussion:

Tech-ed Online Panel: Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy

Blogger buttons courtesy: Microsoft

Be a part of the experience. Help spread the excitement about Tech Ed 2008!

-Satya SKJ (SQL Server MVP)

Whenever any user (forum or usergroup) asks me about Upgrade gotchas and FAQs I will refer this SQLUpgrade-blurb post that I have made from my experience. Further I would like to enhance the topic of upgrade and things you need to watch about Collation settings, think the task when you have to perform the consolidation of databases from a 2000 version to 2005 version.

By default and as a best practice you must run through the SQL Server 2000 instance using Upgrade Advisor (problems you might get on that post) in order to get prior information on upgrade process. So when you think about default collation, the SQL will be selected with SQL_Latin1_General_CP1_CI_AS collation within 2000 version and change within 2005 version will be system databases running Latin1_General_CI_AS. This is where you need to careful to adopt the relevant collation setting for your master database and TEMPDB database! Yes, by default usage of when databases running SQL_Latin1_General_CP1_CI_AS they will be creating and referencing temp tables. As in documentation the TEMPDB is used more within SQL Server 2005, posts such as WhatToDo-WhenTEMPDB_Full will help you too.

Make sure you haven't changed any of the Windows locale to your local language, such as to UK that will have British English as language. As per the design the collation for tempdb always uses the default collation of the server. So in order to use with new collation settings within the upgraded environment you have an option to script out the databases schema having COLLATION setting turned on and edit the scripted text to use new collation, then reapply the changed script. When it comes to the data SSIS is only saviour to transfer the data between source and target database(s), though it is not an easy option but only way out if you have no other choice to change your code or ignore the changed collation settings.

They (MS) said DTS will be wiped out after SQL Server 2005 and will not be supported, more and more SSIS is promoted to use. But within SQL Server "Katmai" DTS is included with a provision of management, run-time, and design-time support for DTS packages that were created by using the SQL Server 2000 tools and object model.

By using SQL 2008 CTP I can easily manage the SQL 2000 version DTS packages, though they are very basic ETL based packages to handle. As to use with SQL 2005 I have more practised with SSIS in this case.

As you are aware since SQL 2005 DTS and Integration Services are completely different products. There were times where community was under impression that SSIS is not a version upgrade from DTS, but it is. As it is evident you can easily manage and install both DTS and Integration Services on the same server. Run both DTS and Integration Services packages on the same server when both products are installed. Even if the SQL Server 2000 tools are not present on the server, you can run DTS packages by using the updated version of the DTS runtime that is installed with Integration Services.

The above are still applies to SQL 2008, with DTS packages that are created in SQL 2000 version can be saved in MSDB instance, for SSIS packages you can only save on an instance that is running SQL Server 2005 or a later version.

Within SQL 2008 using DTS designer you can modify  DTS packages even if you haven't got the SQL 2000 on your machine, get more information about DTS designer from here.

Wait and watch for more supportability for DTS in SQL Server 2008 version.

Further links and refernces on SSIS:

Creating a Simple ETL Package Tutorial

Integration Services Tutorials

Microsoft SQL Server Product Samples: Integration Services - Home

Deploying Packages Tutorial

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly!

Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design the DBCC SHRINKFILE operation is a single-threaded operation that means you cannot define or configure the server to use multiple CPUs or a dedicated CPU. So troubleshooting the performance problems on a database system is very tricky, more important is where to look for a problem and for instance it is frustating to see why system reacts in such a bad way even for a simple query execution and this is where you need to look at how CPU, Memory & disks are performing during this operation

Coming to the subject the referred DBCC operation needs to perform the exercise of moving database pages from tail of the file to the beginning in order on the data file, with one page at a time though. SO you need to be careful to select this operation on a huge database as it will tend get the server down to its knees for a single simple-query execution, also the SHRINKFILE operation often make it defragmentation from bad to worse and in many situations I have seen this increases the file logical fragmentation whereby you will see huge difference in performance to produce few hundreds of rows result set.

If you see such performance issue then look at from a high level, there are two paths to identifying CPU performance problems. The first is reviewing the system's hardware performance, an exercise that helps determine where to look when you head down the second path, reviewing the server's query efficiency. This second path is usually more effective in identifying SQL Server performance issues. Unless you know exactly where your query performance issues lie, however, you should always start with a system performance evaluation that will lead to go on both of these routes. On such SQL Server instances where the relational engine actually handles its own extremely efficient queuing and threading to the Operating Sysstem and having CPUs with hyper-threading is a common scenario, this will have the affect to overload the physical CPUs on systems with already high CPU utilization. This is where the threads operation converted as queues from SQL Server with multiple requests to perform work on multiple schedulers. This is where the Operating System struggles to cope up to switch the context of the threads back and forth on the physical processors to satisfy the requests that are being made even if the two logical processors are sitting on top of the same physical processor.

Taking back to the SHRINKFILE operation on the database table(s) with a clustered index(es) on a huge table (rows) then you will see much degraded performance because of the heaps and those heaps have many non-clustered indexes, where it is different to the  clustered index situation. In this case the SHRINK operation of moving the pages having with IMAGE data or Large Object Blob will be too slow, as it has to read the data from each page to arrange. Further the most content of an index/table resides at the end of the file, you can rebuild the indexes to move them to the front end of the file. Also bear in mind to keep a close watch of Transaction log space on the databases in addition to the TEMPDB which is used extensively in SQL Server 2005 version.

So by the end you should ensure to schedule the SHRINK operation during the less traffic hours and when server consists with multipe CPUs to take advantage of performance to finish the operation in timely manner.

You may be aware that or seen within your Database platform about slower disk performance than expect having multiple disks in Windows Server 2003 environment when you use a hardware-based redundant array of independent disks (RAID) or a software-based RAID. This could be due to one of the reasons such as starting location of partition is not aligned properly with a stripe unit boundary in the disk partition that is created on that RAID. Further information we can refer from one of the KBA929491 article that:

A volume cluster may be created over a stripe unit boundary instead of next to the stripe unit boundary. This is because Windows uses a factor of 512 bytes to create volume clusters. This behavior causes a misaligned partition. Two disk groups are accessed when a single volume cluster is updated on a misaligned partition.

Windows creates partitions that are based on a predefined number of sectors. The starting location for a disk partition in Windows Server 2003 is either the 32nd or the 64th sector, depending on the information that is presented to the operating system by the mass storage controller.

It is the default configuration from the disk partitions that they reserve the first sector for code and number of sectors with the starting sector information, thats how the next sector will consists the information about data part. In this regard Microsoft recommends to use DiskPart.Exe tool which can be used to create disk partition and specify the starting offset, before that it is recommended to findout what is the current size set of partitioned disks and you can get more information about the Diskpart.exe tool from this DiskPart-TechnetArticle and Exchange-Server_IO_Alignment article links.

Within the recent project tasks I have proposed to use disk partition as 64K blocks for the disks that are used for transaction logs, as there is no doubt it enhances the performance when writing the huge chunk of logs for a database (if required). Also I have discussed with Microsoft Development team about the recommendations they use internally with 64K for both data & log, further I suggest to refer Bob Dorr's explanation on how best system can make use of it during backup/restores link. One of my colleague (Ken) has raised the concern that "I can see that sometimes it would help but not always. My *guess* is that SQL won't always have 64k of data that needs to be written urgently (e.g. after a COMMIT). I guess it all depends on the algorithm's used and I've not seen them described in detail anywhere".

Further I have found that in the past when the Infrastructure team raised a query with Microsoft team it has been discourage to set 64K blocks to the drives other than Data disk. The usual disk layout seperation we follow for the SQL Server setup as follows: