August 2007 - Technical Rollup Mail - SQL
News
SQL Server 2005 is the Data Platform Leader:
Microsoft SQL Server 2005 is a comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. SQL Server 2005 allows companies to gain greater insight from their business information and achieve faster results for a competitive advantage.
https://www.microsoft.com/sql/prodinfo/overview/default.mspx
Microsoft SQL Server 2008
SQL Server 2008 (formerly known as SQL Server code name "Katmai"), the next release of Microsoft SQL Server, provides a comprehensive data platform that is secure, reliable, manageable, and scalable for your mission-critical applications. SQL Server 2008 enables developers to create new applications that can store and consume any type of data on any device, and enables all your users to make informed decisions with relevant insights. Microsoft is releasing a Community Technology Preview (CTP) version of SQL Server 2008 in conjunction with the Microsoft Tech-Ed 2007 conference.
https://technet.microsoft.com/en-us/sql/bb498264.aspx
https://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
Documents
SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned
The observations in this paper are based on tests run in the SQL Server Customer Lab for a customer who needed to scale up full-text search to a much greater potential volume. The paper describes the customer scenario, provides an overview of SQL Server 2005 full-text concepts that bear on the results, and offers lessons learned and recommendations for using full-text queries on large catalogs.
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx
Scale-Out Querying with Analysis Services
This white paper describes how to set up a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services so that you can handle a large number of concurrent queries to your Analysis Services servers. Load-balanced querying ensures that readers of OLAP cubes can consistently query for the latest aggregations throughout the day and distribute the load of all queries among the available servers. This scale-out querying architecture optimizes cube processing time, increases the frequency of cube update, and makes processing more robust as you can afford more frequent processing and transparent error recovery.
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/scoqryas.mspx
Analysis Services Processing Best Practices
The recommendations in this white paper provide best practices for processing in Microsoft® SQL Server™ 2005 Analysis Services. These performance optimizations are based on real-world implementations with some of our top customers and partners who use SQL Server 2005 Analysis Services Enterprise Edition.
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ssaspbpr.mspx
Predeployment I/O Best Practices
The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
Comparing Tables Organized with Clustered Indexes versus Heaps
In SQL Server 2005, any table can have either clustered indexes or be organized as a heap (without a clustered index.) This white paper summarizes the advantages and disadvantages, the difference in performance characteristics, and other behaviors of tables that are ordered as lists (clustered indexes) or heaps. The performance for six distinct scenarios where DML operations are performed on these tables are measured and detailed observations presented. This white paper provides best practice recommendations on the merits of the two types of table organization, along with examples of when you might want to use one or the other.
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
Deployments and Tests in an iSCSI SAN
iSCSI SANs offer an alternative for building Storage Area Networks. Consolidating storage in a SAN offers storage management and scaling benefits for datacenters. iSCSI support in Windows Server 2003 makes connecting servers to an iSCSI SAN easy and affordable. This paper describes the deployment and testing results of SQL Server 2005 using the Microsoft iSCSI Initiator with an EqualLogic iSCSI SAN. It helps you understand best practices and the benefits of using an iSCSI SAN with SQL Server 2005.
https://msdn2.microsoft.com/en-us/library/bb649502.aspx
SQL 2000 - Yes! Lots of VLF's are bad! Improve the performance of your triggers and log backups on 2000
A transaction log is broken up into VLF’s (Virtual Log Files (not 'Fragments' as I state - thanks Kalen)), these files, I'll call them fragments from now on because you may get confused with the log file itself) vary in size and in SQL Server 2000 the number and size of them can have a big impact on the performance of various operations that involve the transaction log, specifically here I talk about Triggers and Log Backups. In SQL 2005 they appear to have fixed the performance problem that is currently in SQL 2000.
https://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07.aspx
Downloads
SQL Server 2005 Best Practices Analyzer (July 2007)
The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment. This download is the July 2007 release of SQL Server 2005 Best Practices Analyzer.
ADO.NET Entity Framework June 2007 CTP
This CTP contains updates to the ADO.NET Entity Framework since the Visual Studio Codename "Orcas" Beta 1 release, including changes in Object Services, Query, Entity Client, and the Entity Data Model Wizard in Visual Studio. Some of the new features include IPOCO, detaching from long-running ObjectContext instances, multiple entity sets per type, support for referential integrity constraints, span support, transactions, serialization, no more default constructors in code-generated classes, improvements to stored procedure support, access to the underlying store connection, directory macros in the entity connection string to support hosted scenarios, native SQL read-only views, UNICODE support in Entity SQL, query plan caching, and canonical functions in Entity SQL.
Hotfix Rollup for SQL Server 2000 Analysis Services Service Pack 4 - Build 2174 for x86 and x64 Based Systems
SQL Server 2000 Analysis Services 2000 build 2174 contains hotfixes that were originally released for SQL Server 2000 Analysis Services Service Pack 3, but were not included in Service Pack 4.
Hotfix Rollup for SQL Server 2000 Analysis Services Service Pack 4 - Build 2174 for Client Systems
SQL Server 2000 Analysis Services build 2174 contains hotfixes that were originally released for SQL Server 2000 Analysis Services Service Pack 3, but were not included in Service Pack 4.
https://www.microsoft.com/downloads/details.aspx?FamilyID=a643980a-26a4-44c1-9b50-53e20e7210b5&DisplayLang=en
Microsoft Codename "Astoria" - May 2007 Community Technology Preview add on
The first CTP of Microsoft Codename "Astoria" shipped in May of 2007 and included a client library to facilitate access to Astoria services from applications built using the .NET Framework.
This add-on extends the CTP by introducing a version of the client library that can run inside the Microsoft Silverlight 1.1 environment. This version of the library also includes an extension to the API that allows developers to execute queries asynchronously, a more natural interaction model for a web-centric application.
Microsoft® Visual Studio® 2005 Team Edition for Database Professionals Service Release 1
This service release addresses the top issues that were found through feedback from customers and partners. This release includes the following features:
• Cross-database references
Support is improved to enable you to reference objects in different databases by using database project references or referencing a database metafile (.dbmeta). This support will reduce or eliminate the cross database reference warnings within a database project.
• Improved file support within SQL Server file groups
You may define files within file groups as database project properties instead of having to create files and file groups within the pre-deployment storage script.
• Variables
A Variables page is added to the database properties. This new page enables you to define setvar variables for use in the deployment scripts. Additionally, SR1 supports the latest service pack release from Microsoft SQL Server 2005 (SP2). The SR1 also supports the Windows Vista operating system.
Events / WebCasts
SQL 2005 Webcasts and Virtual Labs
SQL Server 2005 is data management and analysis software that delivers enhanced security, availability, and scalability to mission-critical data applications—while making them easier to develop, deploy, and manage. Choose from a wide range of live and on-demand webcasts. Or take part in a virtual lab, which allows you to cut your teeth on the new platform by experimenting with its different capabilities.
TechNet Webcast: The Value of Business Intelligence with SQL Server 2008 (Level 200) – Thursday, August 09, 2007 11:30 AM Pacific Time (US & Canada)
As the platform for the end-to-end Microsoft Business Intelligence (BI) offering, Microsoft SQL Server 2008 builds on the strong momentum in the BI market. Attend this session to gain insight into the new key features available in the next release of SQL Server. We also learn how the scalable BI infrastructure of SQL Server 2008 can help enable information technology to drive BI throughout your organization and deliver intelligence where users want it.
TechNet Webcast: The Value of Business Intelligence with SQL Server 2008 (Level 200)
TechNet Webcast: Server Consolidation with SQL Server (Level 300) – Thursday, August 23, 2007 8:00 AM Pacific Time (US & Canada)
Microsoft SQL Server supports multiple options for server consolidation, providing organizations with the flexibility to choose the consolidation approach that best meets their requirements in order to centralize data services management and reduce hardware and maintenance costs. By providing centralized management, auditing, and monitoring capabilities, SQL Server makes it easy for you to manage multiple databases and data services, significantly reducing administrative overheads in large enterprises. SQL Server can provide you with the reassurance of industry-leading performance and scalability, and unprecedented control over server resource utilization to maximize the performance of your consolidated data services. Join this webcast to find out more about the various options for server consolidation with SQL Server, and best practices to get the most out of your environment.
TechNet Webcast: Server Consolidation with SQL Server (Level 300)
MSDN Webcast: Data Programmability and SQL Server 2008 (Level 200) – Friday, August 31, 2007 9:00 AM Pacific Time (US & Canada)
A comprehensive data programmability platform enables developers to build data-centric solutions that target desktops, mobile devices, online Web servers, and enterprise servers using a variety of products, programming frameworks, Web services, and data connectivity technologies that work together to access and manage data. Join this webcast as we discuss the new data programmability features that are supported by Microsoft SQL Server 2008.
MSDN Webcast: Data Programmability and SQL Server 2008 (Level 200)
Secrets to Fast Detection and Recovery from Database Corruptions
How can you tell whether your data is corrupt ? If you have corruption, how do you work out what's wrong with the database ? How do you ensure you have a valid backup ? If you don't have a valid backup, how and what do you repair ? If you do have a backup, how do you work out whether you should restore or repair ? And at what granularity ? How do you go about determining what went wrong in the first place ? It's all about limiting downtime and data-loss when a corruption occurs—from knowing the tools, to understanding the choices, to planning a successful strategy. Some of the features discussed in this session are: torn-page detection and page checksums, IO read-retry, backup checksums, consistency checks (DBCC CHECKDB and related commands), and database repairs. Facing database corruption is almost inevitable in every DBAs career—make sure you're prepared when it happens to you.
https://blogs.msdn.com/sqlserverstorageengine/
https://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=549
Further on-demand webcasts are available here: https://www.microsoft.com/events/series/technetsqlserver2005.mspx
New KB’s
SQL Server 2000 Standard Edition
FIX: The server stops responding, the performance is slow, and a time-out occurs in SQL Server 2000
https://support.microsoft.com/?kbid=925419
FIX: Error message when you create a merge replication for tables that have computed columns in SQL Server 2000 Service Pack 4: "The process could not log conflict information"
https://support.microsoft.com/?kbid=927186
SQL Server 2005 Standard Edition
FIX: Error message when you run a SQL Server 2005 Integration Services (SSIS) package that contains a Fuzzy Lookup transformation: "System.Data.SqlClient.SqlException Error number 8101 is invalid"
https://support.microsoft.com/?kbid=936728
FIX: Error message when you import data from a flat file in a SQL Server 2005 Integration Services (SSIS) package: "There is a partial row at the end of the file"
https://support.microsoft.com/?kbid=937969
FIX: A database is marked as suspect when you update a table that contains a nonclustered index in SQL Server 2005
https://support.microsoft.com/?kbid=934734
FIX: Error message when you commit a distributed transaction that contains a query that retrieves data from a linked server in SQL Server 2005: "Transaction manager has cancelled the distributed transaction"
https://support.microsoft.com/?kbid=937517
FIX: You may receive an access violation or error 942 when you drop a database in SQL Server 2005
https://support.microsoft.com/?kbid=938962
The service pack update or hotfix installation stops unexpectedly when you try to install either Microsoft SQL Server 2005 Service Pack 2 or a hotfix for SQL Server 2005 SP2
https://support.microsoft.com/?kbid=936488
FIX: Error message when you try to use Database Engine Tuning Advisor to analyze the performance effects of a workload that includes an indexed view in SQL Server 2005: "Tuning process exited unexpectedly"
https://support.microsoft.com/?kbid=917131
Error message when you use SQL Server Management Studio to connect to SQL Server 2005 Integration Services from a client computer: "Connect to SSIS Service on machine '<SSISServer>' failed Access is denied"
https://support.microsoft.com/?kbid=940232
FIX: A Service Broker endpoint stops passing messages in a database mirroring session of SQL Server 2005
https://support.microsoft.com/?kbid=940286
FIX: Error message when you use Service Broker in SQL Server 2005: "An error occurred while receiving data: '64(The specified network name is no longer available.)'"
https://support.microsoft.com/?kbid=940287
FIX: Error message when you run a query in SQL Server 2005: "Cannot insert duplicate key row in object <TableName> with unique index <IndexName>"
https://support.microsoft.com/?kbid=937533
FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: “The execution of a full-text query failed. The content index is corrupt.”
https://support.microsoft.com/?kbid=938243
SQL Server 2005 Mobile Edition
FIX: A .NET Compact Framework 2.0-based device application that calls the SqlCeEngine.Verify method against a SQL Server 2005 Compact Edition database stops responding
https://support.microsoft.com/?kbid=938786
FIX: Error message when you try to synchronize a SQL Server 2005 Compact Edition subscription again: "The common generation watermark is invalid at this replica since it does not exist"
https://support.microsoft.com/?kbid=938050
SQL Server 2000 Analysis Services
You obtain an incorrect result or receive an error message when you run an MDX query against an instance of SQL Server 2000 Analysis Services
https://support.microsoft.com/?kbid=939567
Error message when you try to connect to an instance of SQL Server 2000 Analysis Services by using Decision Support Objects: "Data source name not found or no default data source"
https://support.microsoft.com/?kbid=940020
SQL Server 2005 Analysis Services
FIX: The process stops responding and the CPU utilization reaches 100 percent when you process a partition in SQL Server 2005 Analysis Services
https://support.microsoft.com/?kbid=936261
FIX: Some MDX queries may run slower and consume more memory after you install SQL Server 2005 Analysis Services Service Pack 2
https://support.microsoft.com/?kbid=936863
FIX: You receive an access violation when you process an object or alter an object after you run an MDX query against the object in Microsoft SQL Server 2005 Analysis Services
https://support.microsoft.com/?kbid=938682
FIX: An MDX query may be slow if it reads from an aggregation that has less than 4,096 records in SQL Server 2005 Analysis Services
https://support.microsoft.com/?kbid=936254
Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"
https://support.microsoft.com/?kbid=940019
FIX: Error message when you use ADOMD.NET in an application to connect to an instance of SQL Server 2005 Analysis Services: "A connection cannot be made Ensure that the server is running"
https://support.microsoft.com/?kbid=916828
FIX: Error message when you browse a cube in SQL Server 2005 Analysis Services: "The query could not be processed The data provider didn't supply any further error information"
https://support.microsoft.com/?kbid=915999
FIX: The SQL Server 2005 Analysis Services service does not start if you try to use Japanese_Unicode_CI_AS as a collation for the instance of Analysis Services
https://support.microsoft.com/?kbid=917479
FIX: The second user can view only the same members that the first user can view when two users connect to an instance of SQL Server 2005 Analysis Services
https://support.microsoft.com/?kbid=915836
FIX: The MDSCHEMA_PROPERTIES rowset may return the incorrect data type for the user-defined properties in SQL Server 2005 Analysis Services
https://support.microsoft.com/?kbid=917492
FIX: Error message when you try to build a mining model that contains lots of data on a 32-bit SQL Server 2005 Analysis Services server: "Memory error: Allocation failure: Not enough storage is available to the process command"
https://support.microsoft.com/?kbid=932331
FIX: The value of a cell is not updated correctly when you use multiple UPDATE CUBE statements inside a single transaction in SQL Server 2005 Analysis Services
https://support.microsoft.com/?kbid=918757
FIX: Some records in the fact table may not be processed when you process a dimension that contains many attributes or many members in SQL Server 2005
https://support.microsoft.com/?kbid=938712
SQL Server 2005 Reporting Services
Blank space appears in the file when you export a report to a physical page format file in SQL Server 2005 Reporting Services or in SQL Server 2000 Reporting Services
https://support.microsoft.com/?kbid=938943
FIX: You cannot select the child parameters even after you select the parent parameter when you use the Report Viewer Web part on a SharePoint site to view a SQL Server 2005 Reporting Services report
https://support.microsoft.com/?kbid=938915
FIX: You receive an error message when you try to access a report after you configure SQL Server 2005 Reporting Services to run under the SharePoint integrated mode