As you probably know, Service Pack 2 for SQL Server 2005 introduces the vardecimal storage format. Below is a step-by-step example using the AdventureWorks database. There are a few incorrections in BOL SP2 CTP2 although these are not major problems and can be easily resolved; I imagine discussions are ongoing about naming conventions for the vardecimal options.
USE master ;GO
-- Enable vardecimal on databaseEXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON' ;GO
-- Check the vardecimal storage format state for all databases in the instanceEXEC sp_db_vardecimal_storage_formatGO
-- Enable vardecimal compression at the table levelUSE AdventureWorksGO-- Note: The BOL example incorrectly references 'decimal data compression'EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 1GO
-- Does not show vardecimal propertiesEXEC sp_help 'Sales.SalesOrderDetail'
-- So, use the TableHasVarDecimalStorageFormat objectpropertyUSE AdventureWorks ;GOSELECT name, object_id, type_descFROM sys.objects WHERE OBJECTPROPERTY(object_id, N'TableHasVarDecimalStorageFormat') = 1 ;GO
-- Under the covers, this uses sys.dm_db_index_physical_stats to calculate the stats-- Documented in BOL CTP2 as sp_estimatedecimalcompressionEXEC sp_estimated_rowsize_reduction_for_vardecimal 'Sales.SalesOrderDetail' ;
-- Clean-up / disable vardecimal storage formatUSE AdventureWorksGO
-- Disable table-level storage formatEXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 0GOUSE master;GO
-- Disable database propertyEXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'OFF' ;GO
The Service Pack 2 CTP 2 download will be available very soon from here: http://www.microsoft.com/sql/ctp.mspx. In the meantime, please take a look at the What's New readme document which is available here: http://go.microsoft.com/fwlink/?LinkId=71711. This provides a comprehensive review of the new and improved features. I particularly like the fact that SSIS is no longer required for maintenance plans. The vardecimal storage format is also a welcome addition.
We have just released a new whitepaper on MSDN which is worth a read: Architecting Service Broker Applications http://msdn2.microsoft.com/en-us/library/aa964144.aspx
You can determine the formatted sector size of the database using DBCC FILEHEADER ('<dbname>'). The DBCC command actually returns quite a lot of information but we are only interested in the sector size column.
In case you were wondering, Longhorn & Vista will only support SQL Server 2005 Service Pack 2. This should be out as a Community Technology Preview (CTP) build sometime soon (I'll blog about the forthcoming changes once we have publicly announced the CTP details). A more detailed explanation on SQL Support for Longhorn & Vista is posted here: http://www.microsoft.com/sql/howtobuy/sqlonvista.mspx
Well, not what I'd call a big update to MPS reports but still worth noting if you intend gathering diagnostic data for a 64-Bit environment.
The download page for MPS report has been updated (following a recent discovery on a x64 PoC) to reflect that some of the MPS tool variants support 64-Bit e.g. the MPS report tool for SQL Server. The important notes section previously stated that the tools do not support 64-Bit. Refer to the important notes section listed here: http://www.microsoft.com/downloads/details.aspx?FamilyID=CEBF3C7C-7CA5-408F-88B7-F9C79B7306C0&displaylang=en "Not all MPSReport versions are currently supported to run on 64bit Operating system versions. Please view associated readme.txt for specifics".
The PFE variant also fully supports a 64-bit environment http://www.microsoft.com/downloads/details.aspx?familyid=00ad0eac-720f-4441-9ef6-ea9f657b5c2f&displaylang=en
Only just seen this via the SQL Server Storage blog here http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx
SQLIOSim certainly looks interesting and seems a step beyond sqliostress.
Is now available here: http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
If you ever open a support case with Microsoft PSS, we may ask you to run the MPS Reports tool to gather system data and configuration information. A new version has recently been released in the downloads section called Microsoft Premier Services Reporting Utility (PFE version). This captures just about everything possible i.e. SQL, exchange, registry, event logs etc. Download and take a look: http://www.microsoft.com/downloads/details.aspx?fa...
Just seen this on the gotdotnet Microsoft Pattern & Practices community server, Architecture and Design Guidelines for Business Intelligence Applications. I'm very interested in the BI area so this should provide some good guidance.
Here is a synopsis:
"Welcome to Microsoft Patterns & Practices Business intelligence architecture and design guide. This guide gives you prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform. The Architecture and Design Guidelines for BI Applications provides prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform. This guide is intended for software architects and developers who are developing Business Intelligence applications using SQL Server 2005 - Integration Services and Analysis Services"
Go here: http://www.gotdotnet.com/codegallery/codegallery.a... for the info.
Just got back from vacation (Crete was very nice but the 40 degree heat was intense) so sorry about the lack of posts. As usual, the inbox is overflowing with the usual announcements (some interesting) and unwanted junk. I noticed that CTP5 for Visual Studio for Database Professionals has been posted so I thought I'd share the links.
All the changes since CTP4 are documented here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=650514&SiteID=1
The download is here: http://www.microsoft.com/downloads/details.aspx?FamilyID=4014554E-903A-4A62-B429-2B027321C32D&displaylang=en
A recorded webcast (from 8th August) is available here: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032304010&EventCategory=5&culture=en-US&CountryCode=US
I'll post more useful resources and information as soon as I have trawled through my inbox.
SQL Server 2005 Books Online has received another refresh (and so has the samples).
The July BOL Update is here: http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
The July samples update is here: http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en
Visual Studio 2005 Team Edition for Database Professionals CTP4 is here: http://www.microsoft.com/downloads/details.aspx?familyid=C2FFA0E9-B68E-4411-9C90-14697AC8FB95&displaylang=en
...and an interesting note from Euan on improving launch time of SQL Server 2005 Management Studio http://blogs.msdn.com/euanga/archive/2006/07/11/662053.aspx
The SQL Server 2005 JDBC Driver 1.1 CTP (June 2006) includes support for integrated authentication and database mirroring and a number of performance enhancements. In addition, this release includes an expanded help system that has sample applications to help you learn how to use the driver with a SQL Server 2005 database.
Two new whitepapers from the SQL Storage Team:
Working with TempDBhttp://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc Physical Database Storagehttp://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/PhysDBStor.doc
A few interesting whitepapers comparing SQL Server 2005 to Oracle (and particularly the use of RAC) have just been posted here http://www.microsoft.com/sql/prodinfo/compare/oracle/default.mspx.
On a slightly different note (non-SQL Server), I am disappointed that PDF support is being pulled from Office 2007. I've been using this a lot in Beta 2 however I hear it might be included as a free download but not sure yet. Brian has a good post about the reasons for removing PDF support http://blogs.msdn.com/brian_jones/archive/2006/06/02/613702.aspx. This is Adobe's official response http://www.adobe.com/aboutadobe/pressroom/statement.html
Yes, this is a post-SP1 update for SQL Server 2005 (available for public download). I have to look twice to make sure I wasn't seeing things! It includes fixes which didn't make the SP1 cut-off point.
Go here for more information: http://support.microsoft.com/kb/918222
I really should keep a closer eye on those SQL Server knowledge base RSS feeds! There are two new SQL Server 2000 Post SP4 hotfix rollup packages available both for the Database Engine and Analysis Services.
You can get them here:
Hotfix rollup package for SQL Server 2000 Analysis Services Service Pack 4 Build 2174 http://support.microsoft.com/kb/907323
A cumulative hotfix package is available for SQL Server 2000 Service Pack 4 build 2187 http://support.microsoft.com/kb/916287
To coincide with the SP1 release..the SQL Server 2005 Books Online April 2006 update is available here: http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en
There are loads of new and updated topics, especially for SSIS.
...and was published on 19th April, get it here http://www.microsoft.com/sql/sp1.mspx
The BOL update will follow...
1. I keep finding useful pieces of information on our website which I wasn't aware of, like this SQL Server 2005 Ugrade Handbook http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
2. Norb Technologies have produced a nice tool, Visual Defrag tool for SQL Server which produces a graphical representation of table/index fragementation (and it's compatible with 7.0/2000 and 2005, go here for more info http://www.norbtechnologies.com/default.asp)
3. A new page appeared recently which talks about SQL Server 2005 support on Windows Vista and Windows Server Longhorn. Bottom line, at the moment, is that all editions of SQL Server 2005 with Service Pack 1 CTP are supported on these pre-release platforms. There are as always known issues so I suggest you take a look at the info http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx
4. I've ditched Adobe in favour of Foxit Reader. Why? well, it is so much faster to launch PDF documents (I've started using this a lot more in Office 2007 beta due to the Save As integration). Take a look: http://www.foxitsoftware.com/pdf/rd_intro.php
Hot on the heels of the SP1 CTP and BOL update, are a whole bunch of component updates http://www.microsoft.com/downloads/details.aspx?familyid=ae94bb12-c839-4b4f-a71b-412fb3a0500e&displaylang=en.
"The Community Pack is a collection of standalone install packages that provide additional value for SQL Server 2005. It includes:
I'd also recommend taking a look at the new report packs that are available here http://www.microsoft.com/downloads/details.aspx?familyid=d81722ce-408c-4fb6-a429-2a7ecd62f674&displaylang=en.
The SQL Server 2005 SP1 CTP has been available since last Wednesday, here is the URL http://www.microsoft.com/downloads/details.aspx?FamilyID=39ebef74-1c29-49fe-8221-c94f2b52f6c6&DisplayLang=en and interestingly enough, there is also an SP1 CTP update to the Books Online documentation which is here http://www.microsoft.com/downloads/details.aspx?familyid=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&displaylang=en.
This is a direct link to the readme file for the SP1 CTP http://download.microsoft.com/download/8/3/8/838be27d-e469-43e5-8ea0-e67c7159ac12/ReadmeSQL2005SP1.htm
And finally, here is a list of the bugs which have been fixed in this build http://support.microsoft.com/kb/913090