Search results matching tag 'SQL Server 2008 R2'

SQL Server Page Restore

Ana — Tue, 10 Jan 2012 06:00:00 GMT

We all know how backup is important. I usually say that restore is more important than backup. We can create backup but if we don’t know how to restore it, or we cannot because it isn’t correct we have nothing. So always check your backups and restore it on different locations. In situation when you need to react quickly restore practice can save you a lot of times and nerves.

In this post I decided to show how to use page restore. Page restore isn’t something that has to be done on a regular basic, but every DBA should be familiar with technique and requirements in case page restore is needed.

Page restore requirements and limitations

Page restore is future available from SQL server 2005 version. Enterprise edition allows online page restore (restore of metadata pages require offline restore) while standard edition support only offline page restore.

Page restore is a nice future especially if you have a large database and a few damaged pages. As with all nice futures, page restore has some requirements and limitations. To be able to use page restore database must to be in full or bulk logged recovery model. Page restore must start with full, file or filegroup backup and there must be an unbroken chain of log backups up to the current log file. All log backups must be applied so that the page is brought up to date with the current log file.

Be aware that page restore cannot be used to restore: transaction log, full-text, allocation pages (GAM, SGAM, PFS, DIFF, ML), file header page (PageId =0 ), boot page (PageId =9).

How to identify damaged pages?

Damaged pages can be identified in multiple ways.

1. Sometimes, executing some queries on database can result in an error 824 (invalid page checksum error).

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xbf649245; actual: 0xbf648ec5). It occurred during a read of page (1:80) in database ID 9 at offset 0x000000000a0000 in file ‘D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CorruptDB.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Analyzing the error we can see that database with databaseid = 9 has damaged page 80 on file 1 (page 1:80).

2. Damaged pages can be identified with DBCC CHECKDB command. Example results:

Msg 8928, Level 16, State 1, Line 2
Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:80) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data), page (1:80). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘CorruptTable’ (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘CorruptDB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptDB).

3. dbo.suspect_pages table on msdb database. It contains one row per page that failed with an 823 error or an 824 error.

4. Check the SQL Server Error Log, and look for errors related to corrupted pages

How to perform page restore?

To perform page restore we need full backup containing un-damaged pages, differential and all transaction log backups to the current time. In the RESTORE DATABASE statement, we need to use the PAGE clause to list the page IDs of all of the pages to be restored. If we use offline page restore we first need to create backup log with NORECOVERY option. In this example, I will use online page restore.

--Restore database
RESTORE DATABASE CorruptDB PAGE = '1:80'
FROM DISK = 'D:\Backup\CorruptDB_full.bak'
WITH NORECOVERY
-- Restore log
RESTORE LOG CorruptDB
FROM DISK = 'D:\Backup\CorruptDB_log1.bak'
WITH NORECOVERY

/*
Processed 1 pages for database 'CorruptDB', file 'CorruptDB' on file 1.
RESTORE DATABASE ... FILE= successfully processed 1 pages in 0.023 seconds (0.339 MB/sec).
Processed 0 pages for database 'CorruptDB', file 'CorruptDB' on file 1.
RESTORE LOG successfully processed 0 pages in 0.002 seconds (0.000 MB/sec).
*/

In the example above, full backup with pageid = 80 is restored with option norecovery. Then we restored log with norecovery option. If we try to restore log with RECOVERY option without taking the last backup we would get an error:

Processed 0 pages for database ‘CorruptDB’, file ‘CorruptDB’ on file 1.
The roll forward start point is now at log sequence number (LSN) 43000000026100001. Additional roll forward past LSN 43000000042700001 is required to complete the restore sequence.
RESTORE LOG successfully processed 0 pages in 0.002 seconds (0.000 MB/sec).

You can query database and all data are available except damaged page. If we try to query data from table containing damaged page after we restored it with NORECOVERY option we will get an error:

ERROR:

Msg 829, Level 21, State 1, Line 1
Database ID 9, Page (1:80) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

To finalize page restore we need to take log backup and to restore it with RECOVERY option.

-- Backup DB log
BACKUP LOG CorruptDB
TO DISK = 'D:\Backup\CorruptDB_log2.bak'
-- Restore DB log with RECOVERY option
RESTORE LOG CorruptDB
FROM DISK = 'D:\Backup\CorruptDB_log2.bak'
WITH RECOVERY

Now we can query our table without error.

Offline page restore procedure is slightly different. With offline restore we first need to take log backup with NORECOVERY option. It will put database to restoring state. Then we need to restore full database using PAGE clause and NORECOVERY option. Then differential and all transaction log backup need to be applied with NORECOVERY option. Last transaction backup, taken with NORECOVERY option, should be restored with RECOVERY option.

Republished with author's permission. See the original post here.

DBCC CHECKDB WITH ESTIMATEONLY: Do you trust it?

Ana — Thu, 10 Nov 2011 06:00:00 GMT

Yesterday I run into interesting situation with DBCC CHECKDB WITH ESTIMATEONLY command. Later I find out that it’s a known bug in SQL Server 2008 R2, but I experienced it for the first time and I think that it’s a good topic for my post. Here is the story.
Yesterday, after I restored database from the backup I decided to run DBCC CHECKDB on it. Data file size of tempdb database was 2.5GB. I know that DBCC CHECKDB is a resource-intensive task and it also require space in tempdb. I wanted to be sure that tempdb size is appropriate to accommodate the CHECKDB requirements so I run DBCC CHECKDB WITH ESTIMATEONLY.

DBCC CHECKDB(DatabaseName) WITH ESTIMATEONLY

Here is the output:
Estimated TEMPDB space needed for CHECKALLOC (KB): 40443
Estimated TEMPDB space needed for CHECKTABLES (KB): 13619

It’s ~ 53MB. I expected larger values because database occupies 28GB of space. But I thought that tempdb with 2.5GB of data file size is enough because there will be no other workload on the server.

I run DBCC CHECKDB(DatabaseName) and command completed successfully in about 11 minutes. Later I noticed that the size of tempdb data file is 3GB and data file auto growth occurred around the time of the DBCC CHECKDB execution.
That was very strange. Estimated tempdb space was about 53MB but it used more than 2.5GB of tempdb space. It is almost 50 times more than estimated space needed and it can lead to problem especially on very large databases. I decided to run DBCC CHECKDB command again and see usage of tempdb database.
Here is the script:

DECLARE @read BIGINT
DECLARE @write  BIGINT

SELECT  @read = SUM(fs.num_of_bytes_read),
@write = SUM(fs.num_of_bytes_written)
FROM    tempdb.sys.database_files AS df
INNER JOIN    sys.dm_io_virtual_file_stats(2, NULL) AS fs
ON fs.file_id = df.file_id
WHERE df.type = 0

DBCC CHECKDB(DatabaseName)

SELECT  (SUM(fs.num_of_bytes_read) - @read) / 1024. / 1024. AS tempdb_read_MB,
(SUM(fs.num_of_bytes_written) - @write) / 1024. / 1024. AS tempdb_write_MB,
(SELECT  internal_objects_alloc_page_count * 8. / 1024
FROM    sys.dm_db_task_space_usage
WHERE   session_id = @@SPID) AS internal_objects_alloc_size_MB
FROM    tempdb.sys.database_files AS df
JOIN    sys.dm_io_virtual_file_stats(2, NULL) AS fs
ON fs.file_id = df.file_id
WHERE df.type = 0

Results:
tempdb_read_MB tempdb_write_MB internal_objects_alloc_size_MB
3192.21093750000 3192.60156250000 3194.562500

From results, we can see that more than 3.1GB of data are written and read during DBCC CHECKDB execution.
I executed few more tests with different databases to see a difference between estimated tempdb space and the real usage during CHECKDB operation and only first database had issue with it.

I googled and found the excellent blog post by Paul Randal (blog|twitter): How does DBCC CHECKDB WITH ESTIMATEONLY work? At the end of the post Paul wrote:

”PS Beware also that I’ve heard of several cases of a bug in SQL Server 2008 R2 where the output is incorrectly very low. The dev team is aware of this issue and are working on it.”

Be careful if you need to rely on DBCC CHECKDB WITH ESTIMATEONLY output. You can be surprised, especially if you are working with large or very large databases.

Microsoft SQL Server 2008 R2 Administration Cookbook - eBook and paperback Book - quick answers to common problems #sql #book

sqlmaster — Wed, 25 May 2011 23:25:00 GMT

This is something big in my life to announce, it gives pleasure and happiness to share about my first book (ebook and hard paper format) titled : Microsoft SQL Server 2008 R2 Administration Cookbook is released and out now. This book has an unique approach as a task based real-world best practices in a cookbook style. Foreword by (thanks to) Brad McGehee, SQL Server MVP & Director of DBA Education – RedGate software.

My thanks to the technical reviewers: Vinod Kumar from Microsoft India, Ravikanth Chaganti - SharePoint MVP and Venkatesan Prabu - SQL Server MVP for their efforts to shape up the book with full-blown technical content. Also special thanks to Hemant Goswami - SQL Server MVP [Author - Microsoft SQL Server 2008 High Availability book]. Not to mention about the Technical Editors and Development Editors from Pactk Publishing who has guided me all the way through in this journey, glad to work with these bunch of professionals.

What you will learn from this book :

Discover best practice solutions for upgrading to SQL Server 2008 R2
Employ the new enhancements in SQL Server 2008 R2
Build a strategic multi-server management
Automate administration tasks
Implement the inter-operability of SQL Server Analysis Services and SQL Server Reporting Services with SharePoint 2010 services
Manage and administer core SQL Server 2008 R2 Business Intelligence Services
Implement and manage essential Availability and Programmability enhancements
Implement best practices and build troubleshooting techniques on a multi-instance and multi-database environment
Provides Advanced Administration techniques for SQL Server 2008 R2 as a book or eBook
Covers the essential Manageability, Programmability, and Security features
Emphasizes important High Availability features and implementation
Explains how to maintain and manage the SQL Server data platform effectively

Detailed Information

Language : English
Paperback : 468 pages
Release Date : May 2011
ISBN : 1849681449
ISBN 13 : 978-1-84968-144-5
Author(s) : Satya Shyam K Jayanty

Get In more detail with Table of contents and here is the Sample chapter for download, have a look at PacktLib.

Buy 'Microsoft SQL Server 2008 R2 Administration Cookbook' with 'Microsoft SQL Server 2008 High Availability' and get 50% off both the eBooks. Just add both the eBooks to your shopping cart and enter 'msqls8reb' in the 'Enter Promotion Code' field. Click 'Add Promoitonal Code' and the discount will be applied.

SQL Server Migration tools and Best practice anlayzer #sql #sqltools

sqlmaster — Thu, 12 May 2011 10:49:00 GMT

If you are a DBA in a heterogenous data source enviornment then SQL Server Migration Assistant is a must-have tool in hand.

During consulting assignments I go through I keep the tools in hand that will help me to carry on the important tasks efficiently.

SQL Server Feature pack is the name that are available to download for free and recently few of them had latest releases too, here is the information about migration assistant tools :

Microsoft® SQL Server® Migration Assistant

Customer, Partner, Developer

Further I would also recommend all the users (not only DBAs) to download and run the Best practices analyzer tool on your environment that can report handful information:

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer

Customer, Developer

SQL Server 2008 R2 Service Pack 1 CTP - test and feedback #sql #sqltools #ctp

sqlmaster — Wed, 11 May 2011 11:02:00 GMT

SQL Server 2008 R2 service pack 1 contains all the cumulative update pack releases since RTM release. This means SP1 CTP consist the CU Package releases from 1 to 6, as CU7 has released on April 18th 2011.

So before you test the SQL2008R2SP1 CTP, I would encourage to go through the release notes that gives information about list of issues that are fixed and what's new with this release:

The following features are new in Service Pack 1 (SP1).

Dynamic Management Views for increased supportability. sys.dm_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.

ForceSeek for improved querying performance. Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.
Data-tier Application Component Framework (DAC Fx) for improved database upgrades. The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.

Disk space control in PowerPivot for SharePoint. This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

Fixed various issues. This update fixes many knowledge base issues that are listed in this master KB article.

Depending upon the number of SQL Server 2008 R2 instances and their editions you must test this CTP release, as it will be a standard policy in enterprises to deploy the new product until the service pack release. The list of SQL Server 2008 R2 editions are:

SQL Server 2008 R2 Parallel Computing Edition
SQL Server 2008 R2 Datacenter Edition
SQL Server 2008 R2 Enterprise Edition and Developer Edition (non-production)
SQL Server 2008 R2 Standard Edition
SQL Server 2008 R2 Web Edition
SQL Server 2008 R2 Workgroup Edition
SQL Server 2008 R2 Express Edition (free)

Here are the download links: Download IA64 bit SP1CTP, Download X64 bit SP1CTP and Download X86 SP1CTP files, and for further read on Download SQL Server 2008 R2 SP1 Readme notes. If you haven't deployed Windows Server 2008 R2 and to get more information about installing and running SQL Server 2008 R2 on Windows Vista and Windows Server 2008 refer to this link .

Finally a best practice note that do not install this CTP release on existing production/good-working SQL Server 2008 R2 instances, always test the CTP releases before you deploy them to production platform.

Happy testing!

Microsoft SQL Server 2008 R2 Administration Cookbook - Book and eBook expected June 2011. Pre-order now!

sqlmaster — Mon, 07 Mar 2011 21:53:00 GMT

Over 85 practical recipes for administering a high-performance SQL Server 2008 R2 system.

Book and eBook expected June 2011. Pre-order now! Multi-format orders get free access on PacktLib,

This practical cookbook will show you the advanced administration techniques for managing and administering a scalable and high-performance SQL Server 2008 R2 system. It contains over 85 practical, task-based, and immediately useable recipes covering a wide range of advanced administration techniques for administering a high-performance SQL Server 2008 R2 system and solving specific administration problems. It shows how the SQL Server 2008 R2 system can be taken further.

Administer a scalable and high-performance SQL Server 2008 R2 system using advanced administration techniques

What you will learn from this book :

Discover best practice solutions to upgrading to SQL Server 2008 R2
Employ the new enhancements in SQL Server 2008 R2
Discover the new features and add enhancements to Scalability, Manageability, and Security features
Build a strategic multi-server management
Automate administration tasks
Implement the inter-operability of SQL Server Analysis Services and SQL Server Reporting Services with SharePoint 2010 services
Manage and administer core SQL Server 2008 R2 Business Intelligence Services
Implement and manage essential Availability and Programmability enhancements
Implement best practices and build troubleshooting techniques on a multi-instance and multi-database environment

Approach

This book offers practical, task-based, and immediately usable recipes covering a wide range of advanced techniques for administering a high-performance SQL Server 2008 R2 system. In addition to its cookbook style, which ensures the solutions are presented in a clear step-by-step manner, its explanations go into great detail, which makes it good learning material for everyone who has experience in SQL Server and wants to improve. The book is designed in such a way that you can either read it chapter by chapter or refer to recipes that you want in no particular order.

Although the book is focused on specific Microsoft SQL Server 2008 R2, most of the concepts and explanations are also applicable to SQL Server 2008.

Cumulative Update packages for SQL Server 2008 R2 RTM & SQL Server 2005 SP3 released

sqlmaster — Tue, 19 Oct 2010 13:00:00 GMT

Cumulative update package 12 for SQL Server 2005 Service Pack 3 released

Here is another instalment of Cumulative update package 12 for SQL Server 2005 SP3 is ready for download & testing.

The build number for CU package will be 9.00.4311, also after applying this CU12 you may need to restart the server to apply the changes. Also a stand-alone package of SQL Native Client (SQLNCLI) is available to download. Further to know about list of files that are updated by this CU12 refer to KBA2345499 and an additonal note states that:

You must enable the SQL Server Management Object (SMO) extended stored procedures and the SQL Server Distributed Management Object (SQL-DMO) extended stored procedures before you apply the cumulative update package. For more information about the SMO and DMO XPs option, see SQL Server 2005 Books Online.

Note SQL Server 2005 Books Online notes that the default setting of these stored procedures is 0 (off). However, this value is incorrect. By default, the setting is 1 (on).

The above KBA refers the list of other KBAs on the list of bugs that are fixed within this CU12:

VSTS bug number	KB article number	Description
508225	2268122	FIX: "User 'guest' does not have permission to run DBCC checkdb for database 'mssqlsystemresource'" error message when you run the DBCC CHECKDB('master') command on a computer that is running SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2
548812	2276717	FIX: Error code 1456 when you add a witness to a new database mirroring session that has the same database name as an existing database mirroring session in Microsoft SQL Server 2005 and in Microsoft SQL Server 2008
531413	2295460	Fix: Replication error message when you register a custom resolver to a merge replication in SQL Server 2005 if a BIGINT column contains a NULL value
527698	2295878	FIX: The SQL Server 2005 SP3-based table still grows after an insert error occurs if you try to insert LOB data
541779	2389533	FIX: "Infinite recursion detected" error message when a drillthrough action is used in SQL Server 2005 Analysis Services SP3 CU7
549739	2397173	FIX: A blank cell is returned when you query XML data by using the "Results to Grid" mode in SQL Server 2005 Management Studio if a large amount of data exists
548975	2407172	FIX: An exception occurs before the error output for invalid data of an "XML Source" task is redirected in SSIS 2005 or SSIS 2008
557130	2413430	FIX: "Cannot locate version record and page is not allocated" error message occurs when you rebuild an index by using the ONLINE option in SQL Server 2005
564139	2428229	FIX: Error message when you use Query Designer in SQL Server 2005:"Unable to find an entry point named 'GetWindowLongPtr' in 'user32.dll'"

Click on this link to View and request hotfix downloads and as usual if the CU package is available for download then within the specified KBA section will have the link, if not you need to contact your local MS customer service & support center to obtain this CU package.

Further Microsoft Releases Services blogs gives a good resource of previous CU KBA links for your information:

CU#11 KB Article: http://support.microsoft.com/kb/2258854
CU#10 KB Article: http://support.microsoft.com/kb/983329
CU#9 KB Article: http://support.microsoft.com/kb/980176
CU#8 KB Article: http://support.microsoft.com/kb/978915
CU#7 KB Article: http://support.microsoft.com/kb/976951
CU#6 KB Article: http://support.microsoft.com/kb/974648
CU#5 KB Article: http://support.microsoft.com/kb/972511
CU#4 KB Article: http://support.microsoft.com/kb/970279
CU#3 KB Article: http://support.microsoft.com/kb/967909
CU#2 KB Article: http://support.microsoft.com/kb/961930
CU#1 KB Article: http://support.microsoft.com/kb/959195

______________________________________________________________________________________________________________________________________________________________________

Cumulative Update package 4 for SQL Server 2008 R2 released

SQL Server 2008 R2 has got another instalment of Cumulative Update 4 package release, that contains the hotfixes that have been packaged since the release of R2 RTM release.

The build number for this release will be 10.50.1746.0, by default these builds are cumulative and release contains all the security fixes that were included in the previous CU release. So it is always better to download and test each of these CU packages seperately if you are facing such issues on SQL Server 2008 R2 instance.

KBA2345451 refers about list of bugs that are fixed with this CU4 release:

VSTS bug number	KB article number	Description
549034	969845	FIX: Error message when you run a SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 Integration Services package (SSIS) after you install the .NET Framework 2.0 SP2 or the .NET Framework 3.5 SP1: "ADO NET Source [1424]"
548984	980570	FIX: "Fulltext index error during compression or decompression" error message when you rebuild the full-text search index in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2
548986	980832	FIX: "A system assertion check has failed" error message when you run a Transaction-SQL query in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2
451119	980849	FIX: You experience high latency for peer-to-peer distribution agents in Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2
548988	981120	FIX: Error message when you enable TDE on a database that has the snapshot isolation enabled in SQL Server 2008 or in SQL Server 2008 R2
548990	982034	FIX: The translation feature does not work when you perform a grouping action in an Excel PivotTable that uses SSAS 2008 or SSAS 2008 R2 as the data source
548996	982854	FIX: "There is insufficient system memory in resource pool 'internal' to run this query" error message when you run a full-text query that uses compound words in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2
548998	982857	FIX: Error message when you configure a transactional replication for a database in SQL Server 2008 or in SQL Server 2008 R2
549014	983577	FIX: The export process never finishes when you export a report that has nested tablix items in Microsoft SQL Server Reporting Services 2008 to the extensions that are supported by the hard page-break renderers
549019	2028406	FIX: Blank spaces between some items are generated incorrectly in a SQL Server 2008 or a SQL Server 2008 R2 report
549021	2028755	FIX: An incorrect result is returned when you run a LIKE query with wildcards and the Latin1_General_100_CS_AS collation in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2
549040	2200755	FIX: Error message when you run a MDX query in SSAS 2008 or in SSAS 2008 R2
549042	2220252	FIX: A SQL Server 2005 report that uses the visibility toggles feature runs incorrectly in SQL Server 2008 or in SQL Server 2008 R2
547877	2222998	FIX: An incorrect cardinality estimate is returned when you run a query in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2
549032	2248999	FIX: An index corruption occurs when you update a view in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2
549044	2255410	A query returns inconsistent results before the data cache is warmed up in SSAS 2008 or in SSAS 2008 R2
561761	2261403	FIX: The "SQL Server:SSIS Pipeline 10.0" performance object does not appear in the "Performance object" list while another user accesses some SSIS pipeline counters in SQL Server 2008 or in SQL Server 2008 R2
549030	2264562	FIX: A full-text query that uses CONTAINS or FREETEXT predicate together with the "OR" operator takes a long time to run in SQL Server 2008 or in in SQL Server 2008 R2
561767	2276330	FIX: The "CHANGETABLE" function is slow in SQL Server 2008 and in SQL Server 2008 R2
519628	2276495	FIX: "File system error" occurs when you try to run a process update operation on a dimension in Microsoft SQL Server 2008 R2 Analysis Services
561769	2276717	FIX: Error code 1456 when you add a witness to a new database mirroring session that has the same database name as an existing database mirroring session in Microsoft SQL Server 2005, Microsoft SQL Server 2008, and Microsoft SQL Server 2008 R2
561772	2278042	FIX: A derived column transformation may generate incorrect results in SSIS 2008 or in SSIS 2008 R2 when a column is derived both in read-only usage and in read/write usage
524544	2284503	FIX: You encounter two issues when you use Microsoft SQL Server 2008 R2 Master Data Services (MDS)
528558	2291551	FIX: Error occurs when you try to configure a remote report server database by using Reporting Services Configuration Manager in the Datacenter edition of SQL Server 2008 R2 Reporting Services
526900	2291705	FIX: The AMO, ADOMD.NET, and XMLA assemblies for Microsoft SQL Server 2008 R2 have incorrect dependencies on the Microsoft.SharePoint.dll assembly and the Microsoft.AnalysisServices.SharePoint.Integration.dll assembly
536667	2293053	FIX: The column header ID incorrectly contains prefix information when you use the "ReportViewer" control of Visual Studio 2010 to run a report locally
547891	2295869	FIX: An unresolved deadlock occurs when a database is recovered in SQL Server 2005 SP3 or in SQL Server 2008 R2
534262	2297308	FIX: "A previous filestream configuration attempt was incomplete" error and an access violation occur when you perform post-setup configuration of the FILESTREAM feature on a SQL Server 2008 or SQL Server 2008 R2 failover cluster
534101	2300689	FIX: You cannot restore a backup of a database on another instance of SQL Server if you disable transparent data encryption before you create the backup in SQL Server 2008 or in SQL Server 2008 R2
544205	2344694	FIX: Certain change tracking functions and DMVs do not work correctly after an automatic database mirroring failover occurs in SQL Server 2008 R2
536267	2359606	FIX: "System.OverflowException: Value was either too large or too small for an Int32" error message occurs when you try to generate a report in Microsoft SQL Server 2008 R2 Reporting Services
561798	2380361	FIX: Error message when you run the "sp_createstats" stored procedure in SQL Server 2008 or in SQL Server 2008 R2 if the table contains a nondeterministic computed column
561800	2380435	FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2
561804	2384766	FIX: The ANSI_NULLS property of an indexed view is changed to FALSE after the indexed view is replicated to the subscription database in SQL Server 2008 or in SQL Server 2008 R2
561806	2387686	FIX: "A MOF syntax error occurred" error message occurs when you try to install SQL Server 2008 or SQL Server 2008 R2
549656	2397020	FIX: "Access is denied. [0x80070005]" error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows
548720	2401453	FIX:"Search failed: Failed to retrieve data for this request" message when you search for some SQL Azure database objects in the "Object Explorer Details" tab of SQL Server 2008 R2 Management Studio
554322	2407088	FIX: "Sys.ArgumentNullException" error occurs when you open a drillthrough report in a Report Viewer Web part if you use the SharePoint Integrated mode in SQL Server 2008 R2 Reporting Services
555924	2410425	FIX: You receive an exception when you try to create an MDS database in SQL Server 2008 R2 MDS if a non-MDS database table is named "tblSystem"
555933	2412136	FIX: The Entities Explorer grid displays few rows in a SQL Server 2008 R2 MDS website if you use a low screen resolution
560368	2421014	FIX: "A severe error occurred on the current command" error message when you use the Freetexttable or the Containstable function on an indexed view in a CTE query in SQL Server 2008 R2
554345	2428397	FIX: "Type mismatch" error message occurs when you try to delete an embedded PowerPivot database from an instance of SQL Server 2008 R2 Analysis Services that is installed in the SharePoint Integration mode
558922	2431362	FIX: Error message when you try to create a named query in a project that uses Microsoft SQL Server 2008 R2 Parallel Data Warehouse as data source: Keyword not supported

Further if you need to know the list of files that are modified with this CU release, refer to the specified KBA above.

To download you need to visit this View and request hotfix downloads page to select the relevant hotfix which requires an email address to submit the request, that page will have individual files according to the relevant (X86/X64/IA64) platforms.

Set a Winning Rhythm with SQL Server 2008 R2. Join the EXCLUSIVE Webcast Series!

sqlmaster — Tue, 28 Sep 2010 09:20:00 GMT

Continuing with our promise of providing you in-depth insights into latest Microsoft Technologies, Microsoft brings to you a series of exclusive webcasts on SQL Server from 29^th September to 5^thOctober, 2010. Join these demystifying sessions by industry veterans to get rare insights on the best practices that help you better leverage cool features of this technology.

Date & Time

Webcast Topic

29^th Sept., 2010
(2:30 pm - 3:45 pm)

Plan Caching and Recompilation with SQL Server
By Amit Bansal

Performance tuning is a broad area and SQL Server has many hidden features which are lesser explored by many. One such concept is around plan caching which was introduced with SQL Server 2005. This feature has been enhanced and made more accessible to developers and admins alike. Join this demystifying session to know more about Plan Caching and understand how the recompilations affect performance if any.

Click here on the day of the event to join the session!

30^th Sept., 2010
(2:30 pm - 3:45 pm)

High Availability options Explored with SQL Server
By Balmukund Lakhani

The High Availability options with SQL Server are plenty. Most of us use various options like Logshipping, Mirroring, Clustering, Replication, and yet are not sure when to use one over the other. This session is about best practices in using these HA options and what ‘don’ts’ need to be followed while using certain options.

Click here on the day of the event to join the session!

1^st Oct., 2010
(2:30 pm-3:45 pm)

SQL Server Reporting Services Performance Monitoring and Tuning
By Arun Balachandran

Tuning SQL Server Reporting Services is one of the many challenges every BI deployment person is faced with. This session talks about some of the tuning aspects of Reporting services introduced with SQL Server 2008 R2. The session will provide insights into On-Demand rendering engine that enhances the behavior of SSRS with the R2 release. Do join in to know more about Renderer and use of Execution Log for performance troubleshooting.

Click here on the day of the event to join the session!

4^th Oct., 2010
(2:30 pm - 3:45 pm)

Practical Performance Troubleshooting - Best Practices
By Praveen Srivatsa

Database performance tuning is one of the top issues in the minds of any DBA or developer. This session talks about some of the practical tips ranging from normal standard out-of-the-box tuning to some interesting internals which a seasoned DBA must be aware off. We will talk about Index Maintenance, VLF Usage Verification, and more. This is something you don’t want to miss so as to prepare yourself against any mishap happening on your production environments.

Click here on the day of the event to join the session!

5^th Oct., 2010
(2:30 pm - 3:45 pm)

Achieve Enterprise-wide Compliance Standard using PBM
By Venkatesan Prabu Jayakantham

Nowadays, maintaining security standards and adhering to compliance has become a challenging task. This session provides a great hands-on experience to deal with policy creation and evaluating the company standards using the newly created policies. Let’s take a practical problem and solve it straight away with our in-built policy and also let’s get an in-depth experience of creating our own user-defined policy. This session is flavored with three different angles of policy creation to fulfill compliance standard in enterprise wide servers.

Click here on the day of the event to join the session!

List of free resources and tools specific to SQL Server 2008 R2 & Visual studio 2010

sqlmaster — Mon, 27 Sep 2010 16:24:00 GMT

Whenever the new version of SQL Server is released the first thing I search for Books Online update to upgrade the local copy. In similar to that Microsoft have been releasing lot of free resources and tools that can enhance the learning experience for Developers, DBA and Architects. So here are few resources that I would like to refer about R2 product specific:

SQL Server 2008 R2 Feature Pack

This R2 Feature Pack is a collection of stand-alone packages which provide additional value for SQL Server 2008 R2. THe important ones I refer:

Microsoft® SQL Server® 2008 R2 Books On-line

Microsoft SQL Server 2008 R2 Books Online is the primary documentation for SQL Server. Visit the SQL Server 2008 Books Online page on the Microsoft Download Center.

Audience(s): Developer, DBA

Microsoft® SQL Server® 2008 R2 Upgrade Advisor

Microsoft SQL Server 2008 R2 Upgrade Advisor analyzes instances of SQL Server 2000, SQL Server 2005 and SQL Server 2008 in preparation for upgrading to SQL Server 2008 R2. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

Microsoft® SQL Server® 2008 R2 Native Client

Microsoft SQL Server 2008 R2 Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 R2 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 R2 features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

Audience(s): Customer, Partner, Developer

You can download the appropriate feature pack that you wanted individually or simply download all of them.

As a part of Self-Service BI feature in SQL Server 2008 R2 you can download the Reporting Services Add-in that is specific for SharePoint 2010:

SQL Server® 2008 R2 Reporting Services Add-in for Microsoft SharePoint® Technologies 2010

The Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft SharePoint® Technologies allows you to integrate your reporting environment with SharePoint to experience the benefits of using the collaborative environment provided by SharePoint.

For Developer specific I recommend Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008, Microsoft Chart Controls for .NET Framework Documentation & Microsoft Chart Controls for Microsoft .NET Framework 3.5 that are specific to ASP.NET and Windows Forms Chart Controls for .NET Framework 3.5 SP1. Also on the SharePoint Technologies refer to Microsoft SharePoint Foundation 2010 download.

Finally for entire Visual Studio 2010 related packs refer to List of free downloads to enhance Visual Studio 2010 & SQL Server 2008 R2 knowledge post.

Here are few free tools and references to download that can enhance the Visual Studio 2010 knowledge:

Visual Studio 2010 and .NET Framework 4 Training Kit

Brief Description

June 2010 Release of the Visual Studio 2010 and .NET Framework 4 Training Kit

Visual Studio 2010 Keybinding Posters

Brief Description

Reference posters for the default keybindings in Visual Studio 2010 for Visual Basic, Visual C#, Visual C++ and Visual F#

.NET Framework 3.5 Common Namespaces and Types Poster

Brief Description

November 2007 Edition
The .NET Framework 3.5 Common Namespaces and Types Poster

Patterns for Parallel Programming: Understanding and Applying Parallel Patterns with the .NET Framework 4

Brief Description

A document providing an in-depth tour of implementing a variety of parallel patterns using the .NET Framework 4.

Moving to Visual Studio 2010 e-Book

Brief Description

It's a book that will help professional developers move from previous versions of Visual Studio (starting with 2003 and on up).

Microsoft Visual Studio 2010 Professional Trial - Web Installer

Brief Description

Microsoft Visual Studio 2010 Professional is an integrated environment that simplifies the basic tasks of creating, debugging and deploying applications. Let Visual Studio 2010 Professional unleash your imagination and easily deliver your ideas.

Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010 - RTM

Brief Description

Microsoft® PowerPivot for Microsoft® Excel 2010 provides ground-breaking technology, such as fast manipulation of large data sets (often millions of rows), streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft® SharePoint 2010.

StreamInsight Updates 1.0 - get from Cumulative Update package 2 for Microsoft SQL Server 2008 R2

sqlmaster — Thu, 23 Sep 2010 11:38:00 GMT

One of the newest additions from SQL Server 2008 R2 is StreamInsight, Its high-throughput stream processing architecture and the Microsoft .NET Framework-based development platform enable you to quickly implement robust and highly efficient event processing applications.

This is helpful for the data sources and data-stream that include financial based applications such as stock exchange trading or organisation dealing with SEO & Web analytics applications. For more information on development and getting started with StreamInsight, see:

Getting Started

Planning and Architecture

Development

Operations

In addition to the above information here is the updates about StreamInsight 1.0 that are released as a part of Cumulative Update package 2 for SQL Server 2008 R2. When you apply this CU2 then the build number for SQL Server 2008 R2 instance should be 10.50.1720.0 and similar build numer for the StreamInsight 1.0 updates is 10.50.1724.0.

KBA refers the following information with regard to .NET Framework 4.0 development and compatibility with .NET Framework 3.5 version:

loadTOCNode(2, 'summary'); All StreamInsight assemblies and the StreamInsight host (StreamInsightHost.exe) are compiled by using the .NET Framework 3.5. However, you can develop StreamInsight applications by using the .NET Framework 3.5 or the .NET Framework 4.0.

For StreamInsight applications that are compiled by using the .NET Framework 4.0

•	You can load adapters and user-defined extensions that are compiled with either the .NET Framework 3.5 or the .NET Framework 4.0.
•	You can connect to an external StreamInsight host that is compiled with either the .NET Framework 3.5 or the .NET Framework 4.0.

For StreamInsight applications that are compiled by using .NET Framework 3.5

•	You cannot load adapters and user-defined extensions that are compiled by using the .NET Framework 4.0.
•	You can connect to an external StreamInsight host that is compiled by using either the .NET Framework 3.5 or the .NET Framework 4.0.
•	If you want to host .NET Framework 4.0 assemblies in .NET Framework 3.5 applications (such as StreamInsightHost), you must specify the .NET Framework 4.0 runtime in the App.config file as follows: `<?xml version="1.0"?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> </startup> </configuration>`