SQL Server Knowledge Sharing Network

The latest instalment of Cumulative Update package for SQL Server 2008 R2 is available for download, before implement it on production make sure you test the installation.

Build 10.50.1809.0 (cumulative update) and the parent build of this build is 10.50.1807.0.

The list of issues are as follows with their KBA:

I hope this CU11 will fix the issue in relation to my previous blog post Linked Server reporte schema changes issue.

To download and further details refer to the KBA2633145  Cumulative update package 11 for SQL Server 2008 R2.

Another instalment of Cumulative Update package for SQL Server 2008 SP3 is available now, which is CU2 and the build number is known as 10.00.5768.00.

As usual this CU2 for SQL2008 SP3 contains hotfixes for issues that were fixed after the release of SQL Server 2008 Service Pack 3 (SP3).

KBA2633143 list the following article numbers about more information on the fixes:

How to obtain this cumulative update package

loadTOCNode(2, 'moreinformation'); A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. 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 2008 service pack that contains the hotfixes in this cumulative update package.

If the cumulative update is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the cumulative update package.

Note 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 website: http://support.microsoft.com/contactus/?ws=support link.

___________________________________________________________________________________________________________________________________________________ 

The next instalment of Cumulative Update package for SQL Server 2008 Service Pack 2 is availabel for download, the build number for this CU7 will be 10.00.4323.

Further the cumulative update package includes updates for all the component packages. However, a cumulative update package updates only those components that are installed on a system. You cannot apply this CU7 straight away, the pre-requisite is SQL 2008 SP2. Here is the big list of bugs and issues fixed with this CU7 for SQL2008 SP2:

• CU#6 KB Article: http://support.microsoft.com/kb/2582285
• CU#5 KB Article: http://support.microsoft.com/kb/2555408
• CU#4 KB Article: http://support.microsoft.com/kb/2527180
• CU#3 KB Article: http://support.microsoft.com/kb/2498535
• CU#2 KB Article: http://support.microsoft.com/kb/2467239
• CU#1 KB Article: http://support.microsoft.com/kb/2289254

Further I also recommend to know about new naming schema for these software update packages from KBA822499 and information about Incremental Servicing Model from this KBA935897 link. Don't forget to test the CU package before applying it on production.

Thanks to Adam Morton for giving me chance to present, since last year and half we have been trying to get connected in Usergroup presentation. I must admit its a good attendance and presence of questions was good which keeps up the enthusiasm during the presentations.

I presented two topics that surrounds on Best Practices for SQL Server users: SQL Server Upgrade, take help of tools and best practices & SQL Server Integration Services Best practices implementation - Performance and Monitoring.

You can download the session:SQLRelay_UpgradeBPA.pps here.

You can download the session: SQLRelay_SSIS BPA.pps here.

(Thanks Martin for correction on city name)

Well, its time now again for all DBAs and System Administrators out there to do some real-world testing on their SQL Server 2008 instance platform, service pack 3 for SQL Server 2008 is released now. If you are already testing the CTP version then its not a big issue to perform the test and upgrade the existing instances of 2008 version.

Once you apply the SP3 for SQL Server 2008 the version will be 10.00.5500.00 and also the download page clearly states the key enhancements out this SP3 release:

• Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3. In addition, we have increased the performance & reliability of the setup experience.
• In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows.
• Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled.
• Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped.
• Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor).
• Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan.

However I would recommend to go through the detailed list of new features and improvements that are included in SQL Server 2008 SP3, review the Release Notes. documentation link. Further this SP3 for SQL Server 2008 contains the cumulative updates of SQL Server 2008 SP2 cumulative update package 1 to 4, and fixes to issues that have been reported through our customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting.

To download the respective platform service pack file go to:SQL Server 2008 SP3 and for Express version go to SQL Server 2008 SP3 Express link.

Packt Publishers are running an offer campaign on Microsoft books in specific to SQL Server, SilverLight and SharePoint for the month of September 2011. How magical is this key S technologies book offers!

From 01st September to 10th September we have an offer for SQL Server technologies books.- 10 days of SQL Server.

From 11th September to 20th September we have an offer on SharePoint technologies books - 10 days of SharePoint

From 21st September to 30th September we have an offer on SilverLight technologies books - 10 days of Silverlight.

Whats the catch -  20% off print books & 30% off eBooks!

For more information and details on this offer visit Packt's September Offers on Microsoft books page. 

There is no doubt that Microsoft is keeping the DBAs busy with the frequent releases of Cumulative Update packages and CTP releases on existing (2008, 2008 R2 and Denali) versions!

Now here is the news about SQL Server 2008 Service Pack 3 CTP release from SQL Server Release Services team, this means there is no support offered from CSS and only ... read more...

I believe by this time most of you might have registered/noted your diaries with the dates about upcoming SQLBits 9 conference.

If you haven't registered yet then, hurry-up - Registration is Now Open, like the previous events this is also a 3 day based conference.  The format will be the now familiar 3 days of top SQL Server content given by SQL Server Experts from around the world, including Microsoft Certified Architects (MCA), Microsoft Certified Masters (MCM) and Microsoft Most Valued Professionals (MVP).

The main advantage and feedback I hear from the previous attendees that for this conference you need not fly outside of UK, easily reachable from many parts of the UK. Having a registration on SQLBits site will get you advantage of browsing the valuable SQL Server based content from the previous events to get a feel for the range or sessions, they are freely available to download as well.

I have missed to present/attend the last two SQLBits conferences due to pre-occupied appointments and this time I'm presenting a training day session titled:

High Availability and Disaster Recovery for the Novice DBA  

Description: For the day we will go through the skills and techniques required for Developers (users) to become a DBA to manage number of areas in data platform. As a DBA you should develop a strong game plan to cover the key aspects of availability and performance. In this session we will go through the end-to-end concepts of DBA learning for developers and enthusiastic users to become a DBA. The tasks include the techniques online that can be used to automate and streamline the data platform management.

With a few mouse clicks you can register for this session - Click To Register

See you there for another mind-boggling learning days!

How to change the default path of .cache files of a data collector after the Management Data Warehouse (MDW has been setup?

This was the question asked by one of the DBAs in a client's place, instantly I enquired that were there any folder specified while setting up the MDW and obvious answer was no as there were left default. This means all the .CACHE files are stored under %C\TEMP directory which may post out of disk space problem on the server where the MDW is setup to collect.

Going back to basics the data collector uses the msdb database extensively for storing configuration information, run-time information, auditing, and collection history information. So it is essential to resize msdb system database with a proper value (atleast 500MB as per my practice) and it must be present on the instance of SQL Server that runs the data collector. In addition to that the relational engine takes care of data collection activities and all the configuration-related tables and views include information about collection sets, collector types, and collection items are given with specific SCHEMA names to identify. The tables and views also contain global data collector parameters, such as the location of the management data warehouse. Database roles for the data collector and management data warehouse are also stored in msdb system database.

As the repository is managed in system database it is essential to know about data collector run-time component which is hosted as a stand-alone process called Dcexec.exe on the server where MDW is setup. It is an important service and component that manages data collection based on the definitions provided in a collection set, and can accept any collection set as input. The data collector run-time component is responsible for loading and executing the SSIS packages that are part of a collection set, you may see the cached and non-chached mode options while setting up the MDW on that instance.

All the collection set options and upload modes are managed by SQL Server Agent service using SSIS packages and the two modes are (as per BOL):

• Non-cached mode. Data collection and upload are on the same schedule. The packages start, collect and upload data at their configured frequency, and run until they are finished. After the packages finish, they are unloaded from memory.
  
• Cached mode. Data collection and upload are on different schedules. The packages collect and cache data until they receive a signal to exit from a loop control-flow task. This ensures that the data flow can be executed repeatedly, which enables continuous data collection.
   

As referred above the collection mode will use the temporary storage area that is used extensively by the data collection services to store property values, collection sets, calculations and any values that are exposed by collection sets. This is considered as a raw data that is obtained by the collection process and used as aggregated data for the upload methods. During the setup of MDW you will be presented to change the CACHE folder and if no value specified then defualt TEMP directory on the server will be used. Going further it is essential and best practices to store these .CACHE files on a different drive where data or log files are not located, I would suggest to use the same folder where TEMPDB data file is located.

Coming to the subject line here is the process to modify the folder once MDW is up and running:

1. Run SQL Server Management Studio tool, and then connect to the instance of SQL Server 2008 R2 in which the error occurs.
2. Expand the Management folder, right-click Data Collection, and then click Properties.
3. If a directory is specified as a Cache directory directory, the directory that is specified is the location of the Data Collector cache files.
   
   If a directory is not specified as a Cache directory directory, the default cache directory is the local temporary directory of the account that runs the collection set.
4. This account may be the SQL Server Agent service account. For example, the collection set is run by an account that is named “SQLMDW”. In this example, the temporary directory of this account is located in a path that resembles the following:  C:\Users\SQLMDW\AppData\Local\Temp

Further I would encourage to go through the following chapters from my book titled -Microsoft SQL Server 2008 R2 Administration cookbook:

• Chapter 2: Administrating the Core Database Engine
• Chapter 3: Managing the Core Database Engine
  Chapter 5: Managing Core SQL Server 2008 R2 Technologies
• Chapter 7: Implementing New Manageability Features and Practices
• Chapter 8: Maintenance and Monitoring
• Appendix: More DBA Manageability Best Practices