First one in the series of SQL Server 2008 version, here it comes Cumulative Update package 1!

What kind of fixtures are available in this CU1 package? 

Cumulative Update 1 contains hotfixes for the Microsoft SQL Server 2008 issues that have been fixed since the release of SQL Server 2008.

After you apply this CU1 on SQL 2008 instance the build number should be 10.00.1763.00, this is to help you that whether the application has been applied successfully or not. As a best practice and I prefer/recommend that you must test hotfixes before you deploy them in a production environment. As you may be aware that these hotfixes will not have easy rollback steps such uninstallation and being the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.

KBA956909  link gives list of SQL Server 2008 builds that were released after 2008 version has been RTM'd. As you may be aware that the 2008 version is multi-language, so as the hotfixes and you can apply this CU package which consists all the component packages within one cumulative hotfix package for all the languages. CU package is intelligent enough to identify to install updates only those components are installed on the system, another catch to apply this CU package is you must enable SMO & DMO extended stored procedures before you apply this update package.

In similar to Cumulative Update Package for SQL Server 2005 this 2008 package can only be obtained by contacting Microsoft Customer Service and Support (CSS) and don't forget to test perfectly before applying them to the production server, better be safe than sorry!

As usual it is better to remind you about the Incremental Servicing Model (ISM) about these Cumulative Updates from the SQL Server team and also worth to mention about New naming schema for SQL Server product from this  822499 link.

Cross post from my blog: Power_of_MDW

In continuation to the CumulativeUpdate package posts here for SQL Server 2005 SP2 we have another set of cumulative update package no.9 ready for testing. TO obtain this read the notes from Microsoft:

.... Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.

If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific cumulative update package. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:

For more information this Cumulative Update package 9 refer to this KB953752 article.

Just a bit of basics about DateTime datatype in SQL Server, it has two different date/time columns. They are DATETIME and SMALLDATETIME and the difference between these two SQL Server date/time data types. SQL Server has a number of default formats it expects raw date and time data to be in when inserting dates into a DATETIME, or SMALLDATETIME columns. If you attempt to insert a date that does not match one of the default date formats, then SQL Server will reject the date/time value. If your raw data does match one of the default formats, SQL Server will automatically convert your raw data into a DATETIME value without any special consideration.

Sometimes I get confused with how to deal with various types of formats when you have the data which is globalized in terms of users usability. So here I would like to give you a lead and links on various datetime data types within SQL Server:

Demystifying the SQL Server DATETIME Datatype (one of my favourite)

Advanced SQL Server DATE and DATETIME Handling

Date and Time Manipulation in SQL Server 2000

The ultimate guide to the datetime datatypes

Ok its time to update your local copies of SQL Server 2008 Books OnLine (BOL) too, as we have RTM version in hand.

You may have installed it during CTP releases and even during short-time of RC0 release, but as always it is a good practice to update as the current installation is latest and updated.

MSDN Developer Center page provides wide range of users to download the BOL as follows:

Download Books Online from the Download Center

• English
  
• Chinese (Simplified)
  
• Chinese (Traditional)
  
• French
  
• German
  
• Italian
  
• Japanese
  
• Korean
  
• Russian
  
• Spanish
  

So don't wait and you can easily download the relevant language BOL to update your local copies.

Since the days of SQL 2000 version I have been doing the same and it proved very useful when you need to find about any information SQL Server in particular!

After I knew about the SQL 2008 RTM release news I have blogged SQL2008RTM-official here and as usual I have attempted to download the setup media from Technet which took a while (3 hours) to download the file, due to the obvious reasons of MSDN/TEchnet premium subscribers all over the world were attempting the same.

Ok, I have got the setup media and to test upon I have downloaded the files for following editions:

SQL Server 2008 Enterprise. SQL Server 2008 Enterprise is a comprehensive data management and business intelligence platform that provides enterprise-class scalability, data warehousing, security, advanced analytics and reporting support for running business-critical applications. With this edition, it is possible to consolidate servers and perform large-scale online transactional processing.

SQL Server 2008 Web. SQL Server 2008 Web is designed for highly available, Internet-facing Web-serving environments running on Windows Server. SQL Server 2008 Web provides the tools necessary to support low-cost, large-scale, highly available Web applications or hosting solutions for customers.

As the SQL Server 2008 Web edition is quite interesting and suitable for web-related applications as referred above. I need to test the editions in order to ensure the Web edition is suitable for set of clients I support to reduce the costing of deploying the SQL Server 2008 within their data platform, interesting eh.

Extracted the setup files and as one of the best practice I haven't attempted to install SQL Server to my laptop directly (remembering the Old-CTP6 installation issue), rather I have created a new Virtual Machine (Virtual Server 2005) with Windows Server 2008 to install. So far so good, now the flurry of troubles are started after a double-click of setup  file on [en_sql_server_2008_web_....] directory!

First one is :

• SQL Server does not install the .NET Framework 3.5 software development kit (SDK). However, the SDK contains tools that are useful when you use the .NET Framework for SQL Server development. You can download the .NET Framework SDK from the .NET Framework Web site.
  
  
• Requirements to restart computers during SQL Server Setup: Installation of the .NET Framework requires a restart of the operating system. If Windows Installer installation also requires a restart, Setup will wait until .NET Framework and Windows Installer components have installed before restarting.
  

Next one is requisite for Visual Studio (for a moment I wondered what this is for!)

Rule "Previous releases of Microsoft Visual Studio 2008" failed.

Struggled to find out why the setup is bombing out with same error (2 times), a couple of searches on MVP private newsgroups found the resolution, bingo!

956139 Visual Studio 2008 SP1 may be required for SQL Server 2008 installations

Then all good with the installation and completed with no further errors, phew.

Failed to start SQL Server services, with no obvious errors or indication. Inspite of checking the local policy on the server to ensure the relevant service account has got privilege to logon locally with ADMIN privileges. Like the one below:

Finally I have found the detailed information and resolution on the issues that might occur on Windows Server 2008 Firewall issues - Windows-SQLServer_2008_FirewallWatchout with an excellent post by Shawn Hernan (thanks).

Hope you might fall into one of the above issues or all of them and I believe this should help.

Microsoft press announces that SQL Server 2008 is available for users and as per PressRelease :

...announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company’s acclaimed data management and business intelligence platform. This version of SQL Server provides powerful new capabilities such as support for policy-based management, auditing, large-scale data warehousing, geospatial data, and advanced reporting and analysis services. SQL Server 2008 provides the trusted, productive and intelligent platform necessary for business-critical applications.

It is indeed a great news for the SQL Server users about 2008 RTM, as their journey begins since last year from CTP4 onwards from this CTP-Post & [SQL Server 2008 features and getting through CTP, RC, RTM. Are we there yet?] here.

So now the question is about number of editions that are available, it will be (as per BOL):

SQL Server 2008 Enterprise. SQL Server 2008 Enterprise is a comprehensive data management and business intelligence platform that provides enterprise-class scalability, data warehousing, security, advanced analytics and reporting support for running business-critical applications. With this edition, it is possible to consolidate servers and perform large-scale online transactional processing.

SQL Server 2008 Standard. SQL Server 2008 Standard is a complete data management and business intelligence platform that provides best-in-class ease of use and manageability for running departmental applications.

SQL Server 2008 Workgroup. SQL Server 2008 Workgroup is a reliable data management and reporting platform that delivers secure, remote synchronization and management capabilities for running branch applications. This edition includes core database features and is easy to upgrade to the Standard or Enterprise edition.

SQL Server 2008 Web. SQL Server 2008 Web is designed for highly available, Internet-facing Web-serving environments running on Windows Server. SQL Server 2008 Web provides the tools necessary to support low-cost, large-scale, highly available Web applications or hosting solutions for customers.

SQL Server 2008 Developer. SQL Server 2008 Developer allows developers to build and test any type of application with SQL Server. This edition features all of the functionality of SQL Server Enterprise but is licensed only for development, test and demo use. Applications and databases developed on this edition can easily be upgraded to SQL Server 2008 Enterprise.

SQL Server 2008 Express. SQL Server 2008 Express is a free edition of SQL Server that features core database functionality including all of the new SQL Server 2008 data types, in a small footprint. This edition is ideal for learning and building desktop and small server applications, and for redistribution by ISVs.

SQL Server Compact 3.5. SQL Server Compact is a free embedded database designed for developers and is ideal for building stand-alone and occasionally connected applications for mobile devices, desktops and Web clients. SQL Server Compact runs on all Microsoft Windows platforms, including the Windows XP and Windows Vista operating systems, and on Pocket PC and smartphone devices.

As Microsoft keeps its words about costing on deploying this product, in terms of licensing as per License_pressrelease information.

So whats the catch on the downloads, it is now available to MSDN and TechNet subscribers and will be available for evaluation download from Aug. 7, 2008. SQL Server 2008 Express and SQL Server Compact editions are available for free download now FromHere.

It is evident that SQL Server 2005 component handles memory differently as compared to SQL Server 2000 version. As one of the KBA refers SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth. This is changed as per BOL documentation and you can get more information by referring to the topics such as: memory architecture, Server memory options, enabling AWE memory & Enabling Memory Support for Over 4 Gb of Physical Memory.

Also the compilation below as per one of the SSP member (Dave):

32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (referred from SYSMON)
Total Server Memory = 690MB (referred from SYSMON)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB

So the Target Server memory equals to the Maximum Server memory on the server.

64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (SYSMON reports)
Total Server Memory = 397MB   (SYSMON reports)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB

Here is the catch where the Target Serve memory is shown less than Maximum Server memory availabile. 

Once for all within SQL Server 64 bit edition ensure you have applied the 'Lock Pages in Memory' to the SQL Server service account in order to take into account on higher memory set. Also bear in mind any resource intensive activity within SQL Server will consume more memory during at that point of time of your testing, so better to run during less traffice hours and no scheduled jobs.

So when you talk about SQL Server 2000 you should think about MemToLeave area which is reserved for SQL alone, that is calculated as follows: 

max worker threads value (default is 255) * Stack Size (default is 512KB) + external needs setting

Bear in mind the default memtoleave is 128MB until SQL 70 version and increased to 256MB within SQL 2000 version. For this reason it is suggested to leave the settings as is in order to ensure that SQL Server always has a contiguous region of address space available in the MemToLeave area. The default setting of DYNAMIC memory for SQL Server grows and shrinks its memory usage by committing and de-committing buffers from the buffers reserved at startup. In any case the LazyWriter process you see within task manager is responsible for growing and shrinking the buffer pool. Always you see is memory usage increase and do not decrease, that means SQL will not release unless it is asked by windows process. What you see above matches to:

Target Server Memory (KB) – The total amount of dynamic memory the server can consume. This corresponds to the buffers reserved at SQL Server startup.
Total Server Memory (KB) – The total amount of dynamic memory the server is currently using. This corresponds to the sum of buffers committed in the BPool and OS buffers of type "OS In Use."

In addition to above warning message you would also see the error stated in ThisBlogpost which are generated when the 'working set'  of SQL Server 2005 process reaches half of its allocated memeory, which is 50 percent of memory that is committed for the process. Sometimes this is associated with a decrease in the performance too where the Windows process is trying to reduce the SQL 2005 process usage. Additionally to get what is the usage percent you could take help of DMV sys.dm_os_ring_buffers:

SELECT *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

Additionally if you are getting this constantly better to open up a case with Microsoft support for a hotifx, as most of them are resolved with such fixes.

Are you desperate to get Relase To Manufacturing (RTM) release of SQL Server 2008?

Well, long wait for SQL Server 2008 is nearly over.

It is an official announcement that SQL Server 2008 will be on the August price list.  This means that customers and partners will soon be able to place their orders for SQL Server 2008 as we get ready to RTM in Q3 of this year. As per Bob Kelly, Microsoft Corporate Vice President of Infrstructure Server Marketing announced during Worldwide Partner conference on July 09th 2008. Good news for finance people is cost of deploying SQL Server 2008 in terms of licensing will same as SQL Server 2005 costs (no additional or small prints).

You may be aware that we have seen a preview/rehearsal of SQL Server 2008 during June 2008 which is RC0, more from this SQL Server 2008 Release Candidate - public download is available now. post here and for more joy don't forget to upgrade the local SQL 2008 documentation aka BOL - SQL Server 2008 RC0 and 3.5 Compact edition Books Online - download and update your copy  fyi.

So it is defiently a long wait for the users to get hands-on with RTM on SQL Server 2008 which has been announced during June 2007 (CTP) and promised earlier to release by February 2008, as you may refer SQL Server 2008 features and getting through CTP, RC, RTM. Are we there yet?  post here.

So having the official releases of Windows Server 2008, Visual Studio 2008 and now with SQL Server 2008 it will be an interesting career path for IT Pros & Developers in coming years (atleast until 2010). More interestingly I would say how many companies out there will be officially jumping into pool of these 3 hard-core 2008 version products immediately and no wait until next Service pack release, better deploy first than waiting for long time.

Being one of the launch leader of HeroesHappenHere & MVP got a free copy of Windows Server/Visual Studio/SQL Server 2008 DVDs and now can't wait to get my hands on RTM which is the final product of version 2008 on SQL Server.

More to come.....

I believe it has been a while that we have seen the security hotfix from Microsoft Security team on SQL Server (alone). The latest security bulletin has announced important security patches that are related to SQL Server in addition to Windows operating system. They quote that "...With the release of the bulletins for July 2008, this bulletin summary replaces the bulletin advance notification originally issued July 3, 2008. For more information about the bulletin advance notification service, see Microsoft Security Bulletin Advance Notification....".

The Severity rating has been given as important which means you have to test the referred hotfix from this KBA Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (941203) link. As that goes in specific to SQL Server the following are for Windows alone: Vulnerabilities in DNS Could Allow Spoofing (953230), Vulnerability in Windows Explorer Could Allow Remote Code Execution (950582) & Vulnerabilities in Outlook Web Access for Exchange Server Could Allow Elevation of Privilege (953747) links.

So what is your practice in deploying such security hotfixes within your environment?

Don't forget to test the patches before deploying them on to the production, that might cause unprecedented downtime and with prior testing you will be able to catch the issues in hand. Also it is recommended to deploy Baseline Security Analyzer tool MBSA that  allows administrators to scan local and remote systems for missing security updates as well as common security misconfigurations.

If you are new to this type of alerts or patches then you must visit the Security Guidance for Update Management link that provides additional information about Microsoft’s best-practice recommendations for applying security updates.

So the recommendation is that to apply the update at the earliest opportunity. For more information on the known issues refer to this KBA 941203 which has documented the currently known issues that customers may experience when installing this security update.

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