The Premiers

Cumulative update package 9 for SQL Server 2005 Service Pack 2

2008-08-20T12:33:00Z

Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Cumulative Update 9 contains hotfixes for SQL Server 2005 issues that have been fixed since the release of Service Pack 2.

Note This build of the cumulative update package is also known as build 3282.

http://support.microsoft.com/kb/953752/en-us

Itay Braun Premier Field Engineer - SQL Server and BI Microsoft Services - UKE-Mail: itayb@microsoft.com Mobile: +44-796-928-9996 Blog: http://sqlblogcasts.com/blogs/thepremiers/Veni Vidi Fixit

New TechNet Events: Register Now

2008-08-07T23:47:00Z

We are pleased to announce a new series of TechNet experts. These are delivered by Microsoft technical professionals and deliver level 200 technical "how to" information for IT professionals. They are free to attend.

Virtualisation and Management Technologies
21 August 2008, Liverpool: Microsoft Virtualisation and Management Technologies
9 September 2008, Reading: Microsoft Virtualisation and Management Technologies

11 September 2008, Edinburgh: Microsoft Management and Virtualisation Technologies

SQL Server 2008
10 September 2008, Reading: SQL Server 2008 Unleashed
7 October 2008. Manchester: TechNet: SQL Server - Under New Management

23 October 2008, Exeter: TechNet: SQL Server - Under New Management

A bit of Fun

10 September 2008, London: Microsoft "After Hours" - The Sequel

Exchange Server 2007

29 October 2008, London: Recipient management, policies and permissions in Exchange Server 2007

Licensing
2 October 2008, Reading: An Introduction and overview of Microsoft Licensing

21 October 2008, Reading: Understanding Microsoft's Server Product Licensing in a Virtual Environment

Windows Powershell

14 October 2008, Birmingham: TechNet: Windows PowerShell: Around the Data Centre in 80 Scripts

Unified Communications
2 September 2008, Reading : TechNet: the Microsoft Vision for Unified Communications

Small Business Server 2008 and Essential Business Server 2008

22 October 2008, Bristol: Taking Care of Business Every day with Small Business Server 2008 and Essential Business Server 2008

29 October 2008, London: Taking Care of Business Every day with Small Business Server 2008 and Essential Business Server 2008

Windows Server 2008

16 October 2008, Edinburgh: Windows Server 2008 - What's New and Exciting

European Event

3-7 November 2008, Barcelona: TechED EMEA IT Professionals

Analysis Services Useful Links

2008-08-07T23:11:00Z

In the attached document I listed:· Analysis Services 2005 Tools· Analysis Services 2005 White Paper· List of recommended books· List of some relevant SQL Server 2005 Tools Any comment is much appreciated

Enjoy.

Useful Reporting Services Links (good kickstart with the product)

2008-08-01T11:22:00Z

Reporting Services 2005 useful Links

Written By:

Itay Braun Premier Field Engineer - SQL Server and BI Microsoft Services - UKE-Mail: itayb@microsoft.com Mobile: +44-796-928-9996 Blog: http://sqlblogcasts.com/blogs/thepremiers/

Veni Vidi Fixit

Administration

Administering a Report Server Database

http://msdn.microsoft.com/en-us/library/aa972237.aspx

Querying and Reporting on Report Execution Log Data

Reporting Services stores execution data across several tables in the report server database. Because the internal table structure does not present report execution log data in a format that is accessible to users, you should extract the log data and store it in a separate local report execution log database.

http://technet.microsoft.com/en-us/library/ms155836.aspx

Reporting Services Log Files

A report server uses a variety of log files to record information about server operations and status.

http://technet.microsoft.com/en-us/library/ms157403.aspx

Configuring Reporting Services for Scale-Out Deploymenthttp://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx

Webcasts

Introduction to SQL Server 2005 Reporting Services (Part 1 of 6) (Level 200)

This six-part series presents a comprehensive introduction to Microsoft SQL Server 2005 Reporting Services for developers. This series is heavy on demos and coding, and light on Microsoft Office PowerPoint. No previous knowledge of Reporting Services is required. In the first webcast, we provide an overview of Reporting Services.

We start by using the wizard to build a simple report. Then we learn about the design tools in the SQL Server Business Intelligence Development Studio. Additionally, we learn about report delivery options, including URL access, the Web service, and the Report Builder.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032282905&EventCategory=5&culture=en-US&CountryCode=US

Introduction to SQL Server 2005 Reporting Services (Part 2 of 6) (Level 200)

This second webcast looks in depth at techniques for delivering canned reports to end users, including URL access, subscriptions, and the Web service. These provide both a push model of report delivery, where a report is automatically delivered, and a pull model, where a user gets a report on an as-needed basis.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032283016&EventCategory=5&culture=en-US&CountryCode=US

Introduction to SQL Server 2005 Reporting Services (Part 3 of 6) (Level 200)

This third webcast focuses on the Report Builder, a tool for ad hoc reporting. We start by examining the Report Builder Client, the tool that helps end users to create ad hoc reports. Next, we learn about the Report Builder Model Designer, a developer tool that allows creation of the business model that represents the underlying data structure.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032283022&EventCategory=5&culture=en-US&CountryCode=US

Introduction to SQL Server 2005 Reporting Services (Part 4 of 6) (Level 200)

In this fourth webcast, we examine some more advanced ideas surrounding report design. Learn how to create tabular reports, matrix reports, linked reports, charts, and sub-reports. This webcast also includes reports that use cascading parameters and dynamic SQL queries.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032283046&EventCategory=5&culture=en-US&CountryCode=US

Introduction to SQL Server 2005 Reporting Services (Part 5 of 6) (Level 200)This six-part series presents a comprehensive introduction to Microsoft SQL Server 2005 Reporting Services for developers. The fourth webcast in this series delved deeper into advanced reporting designs. This fifth webcast focuses on SQL Server Reporting Services extensibility. We demonstrate a custom data source for SQL Server Reporting Services that reads data that is not accessible through SQL queries. We also demonstrate techniques for including custom code in reports.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032283054&EventCategory=5&culture=en-US&CountryCode=US

Introduction to SQL Server 2005 Reporting Services (Part 6 of 6) (Level 200)

This six-part series presents a comprehensive introduction to Microsoft SQL Server 2005 Reporting Services for developers.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032283061&EventCategory=5&culture=en-US&CountryCode=US

More webcasts:· MSDN Webcast: End-User Ad Hoc Reporting with SSRS (Level 300)· MSDN Webcast: Integrating Windows SharePoint Services with SQL Server Reporting Services (Level 200)· MSDN Webcast: Intelligent Reporting: Using the Visual Studio 2005 Report Viewer Controls (Level 200)

Virtual Labs:

Last Update 2008-07-02. Check here http://msdn.microsoft.com/en-gb/virtuallabs/aa740409.aspx

for the complete list of labs

· MSDN Virtual Lab: Business Intelligence Reporting Services · MSDN Virtual Lab: Building Integrated Business Intelligence Solutions Leveraging SQL Server 2005 and SharePoint Server 2007 · MSDN Virtual Lab: Business Intelligence- Using Business Scorecard Manager 2005 and ProClarity for Performance Management · MSDN Virtual Lab: Business Intelligence- Report Builder · MSDN Virtual Lab: Business Intelligence- Integration Services · MSDN Virtual Lab: SQL Server 2005 Reporting Services · MSDN Virtual Lab: SQL Server 2005 Report Builder · MSDN Virtual Lab: SQL Server 2005 Report Viewer Controls

More useful DMV queries

2008-07-28T19:16:00Z

in the attached file you can find queries about Blocking, DiskIO, Memory, Running Queries, Indexes etc ...

Itay Braun Premier Field Engineer - SQL Server and BI Microsoft Services - UKE-Mail: itayb@microsoft.com Mobile: +44-796-928-9996 Blog: http://sqlblogcasts.com/blogs/thepremiers/

Veni Vidi Fixit

Microsoft Security Bulletin MS08-040 - Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (Important)

2008-07-09T09:48:00Z

Executive Summary

This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.

This security update is rated Important for supported releases of SQL Server 7.0, SQL Server 2000, SQL Server 2005, Microsoft Data Engine (MSDE) 1.0, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2000 Desktop Engine (WMSDE), and Windows Internal Database (WYukon). For more information, see the subsection, Affected and Non-Affected Software, in this section.

The security update addresses the vulnerabilities by modifying the way that SQL Server manages page reuse, allocating more memory for the convert function, validating on-disk files before loading them, and validating insert statements. For more information about the vulnerabilities, see the Frequently Asked Questions (FAQ) subsection for the specific vulnerability entry under the next section, Vulnerability Information.

Recommendation. Microsoft recommends that customers apply the update at the earliest opportunity.

http://www.microsoft.com/technet/security/Bulletin/MS08-040.mspx

Virtual Earth -> Sql Server 2008 Geospatial Data Generator

2008-07-09T00:00:00Z

Virtual Earth -> Sql Server 2008 Geospatial Data Generator

Check this out!

The Virtual Earth -> Sql Server 2008 Geospatial Data Generator (http://mikeo.co.uk/demo/sqlspatial/default.aspx ) is a great tool which generates SQL Server 2008 Geospatial data.

Draw the polygon on the Virtual Earth Map and the T-SQL which inserts the geospatial data is generated automatically.

Cool.

Special Thanks to Keith Burns, a Data Architect in Microsoft, who told me about this website.

Cheers,

Itay Braun Premier Field Engineer - SQL Server and BI Microsoft Services - UK

E-Mail: itayb@microsoft.com Mobile: +44-796-928-9996 Blog: http://sqlblogcasts.com/blogs/thepremiers/

Veni Vidi Fixit

SQL Server 2005 (64-Bit) Multi-Instance Installations

2008-07-02T05:38:00Z

When you start Installing a SQL Server 2005 (64-Bit) Multi-Instance Farm environment, you may encounter this particular error message during the Installation process itself. Your first SQL Server 2005 ‘Default’ Instance would be successfully installed and then patched upto Service Pack 2. However, when your second ‘Named’ Instance was started – this specific error message might be displayed during the SQL Server 2005 Database Services Installation process : [Microsoft][SQL Native Client][SQL Server]Password validation failed. The Password does not meet Windows Policy requirements because it is too short. To continue, correct the problem, and then run SQL Server Setup again. Launch script file C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Install\instmsdb.sql

The reason that this error message occurs is because your Windows Server 2003 GPO (Group Policy) may be stipulating a password which is greater than 8 characters and this particular password "Yukon90_" (located in the instmsdb.sql file) is only 8 characters in size :

create certificate [##MS_AgentSigningCertificate##] encryption by password = 'Yukon90_' with subject = 'MS_AgentSigningCertificate'

A workaround solution has been discovered, in relation to this Microsoft KB (Knowledge Base) Article : Error message in the SQL Server Errorlog file after you upgrade SQL Server 2000 to SQL Server 2005 on a server that is running Windows Server 2003: "Unable to update password policy" – http://support.microsoft.com/kb/936892/en-us.Which provides us with the information that this Trace Flag (-T4606) can be used to bypass the Windows Server 2003 Local GPO Password Policy requirements for a SQL Server 2005 Installation. This Trace Flag is added into the Windows Server 2003 System Registry before any subsequent Instances of SQL Server 2005 (whether that be 2, 3, 4, 5, 6 etc) commence, it’s entered into this Registry Key Hive : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters

Where the ‘x’ represents the actual numerical increment to the SQL Server 2005 Multi-Instance Environment.

Using BDF_Set_Key.exe

In order for this Registry Key entry to become consistent with any subsequent SQL Server 2005 Instances, a specific executable has been created which will provide us with some continuity here. This BDF_Set_Key.exe will need to be placed into the <Root_Installation_Path>\SQL Setup folder and executed (double-clicked) from this location before the next Installation (2^nd, 3^rd or 4^th etc) of SQL Server 2005 – which will in essence add this Trace Flag (-T4606) into the System Registry to the appropriate position. For example:

During the First SQL Server 2005 (64-Bit) Installation Process as a ‘Default Instance’ –
Database Services would be Installed into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN folder
Analysis Services would be Installed into the C:\Program Files\Microsoft SQL Server \MSSQL.2\OLAP\BIN folder
Reporting Services would be Installed into the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\Report Server\BIN folder
Second SQL Server 2005 Installation as a ‘Named Instance’ –
Database Services would then be Installed into this C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\BINN folder

Therefore this Trace Flag (-T4606) would then be added to this System Registry Hive : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer\Parameters

When you execute this BDF_Set_Key.exe, a Windows NT Command Window will ‘pop-up' - along with this Dialogue Box :

You can verify that this Trace Flag has been successfully added into the Windows Server 2003 System Registry by checking the output “BDF_Set_Key.log” file, which will be positioned in the same folder structure (<Root_Installation_Path>\SQL Setup) from where this BDF_Set_Key.exe file was launched.

Using BDF_Del_Key.exe

Once your next SQL Server 2005 Installation has completed successfully and received the Service Pack 2, this System Registry Key can then be removed from the Hive using this ‘BDF_Del_Key.exe’ executable file. The file should also be in this position <Root_Installation_Path>\SQL Setup folder and executed (double-clicked) from there. When this file is run, as before a Windows NT Command Window will ‘pop-up' along with this dialogue box :

You can verify that this Trace Flag has been successfully removed from the Windows Server 2003 System Registry by checking the output “BDF_Del_Key.log” file, which will be positioned in the same folder structure (<Root_Installation_Path>\SQL Setup) from where this BDF_Del_Key.exe file was launched.

These BDF_Set_Key.exe and BDF_Del_Key.exe files have been attached to this Web posting as "BDF_Keys.zip". There is also an MSDN forum posting here : Re: SP1 install problem with password - http://forums.microsoft.com/msdn/showpost.aspx?postid=2238519&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

Thank you,

Gareth Ford

Premier Field Engineer - Microsoft | Services | Mob:+44(0)7791 191780 | 0870 501 0800 (Technical support UK only) http://www.microsoft.com/uk/support http://www.microsoft.co.uk/premierpeople/ Microsoft Limited (company number 01624297) is a company registered in England and Wales whose registered office is at Microsoft Campus, Thames Valley Park, Reading. RG6 1WG

Blog: http://sqlblogcasts.com/blogs/thepremiers/ People Talk, I deliver

SQL Server 2008 - Performance Studio

2008-06-20T17:11:00Z

I've been looking at this new feature of SQL Server 2008 in order to demo it to customers, and I have to say it's pretty cool ! Along with Resource Governor and compression, it's one of my favourite new features and a good reason to consider an upgrade. Performance Studio builds on the concept of the Database Reports in SQL Server 2005 and the Performance Dashboard introduced in SQL Server 2005 SP2. Like it's predecessors, it's built on top of standard DMV's but with one crucial advantage... data is historical and is persisted across service restarts. Previously, you had to roll your own code in order to persist DMV data, now SQL Server 2008 gives you it straight out the box.

Setting it up is easy... expand the 'Management' folder and then right-click 'Data Collection' then 'Configure Management Data Warehouse'. This opens a wizard which guides you through the configuration of the Management Data Warehouse (MDW). Select the 'Create or Upgrade a Management Data Warehouse' option and enter your server details. You can either configure a new database or use an existing one in order to collect the performance data.

Once the MDW has been configured, run through the wizard again and select the 'Set up Data Collection' option. At this point you need to also ensure that SQL Server Agent is running or else the performance counters will not be uploaded to the database. Also, SQL Server Integration Services is required to manage collections. There are 3 new database roles in the MDW database: MDW Admin, MDW Writer, MDW Reader. It's a good idea to restrict access to this database, especially if you are collecting sensitive data. Performance Studio will only collect data against SQL Server 2008 databases, so unfortunately you can't use it to monitor older versions of SQL Server. MSDB is used to store the log entries, Agent jobs and SSIS packages.

When creating the MDW, plan for data growth of up to 250 - 500 MB a day, depending on your query plans and consider running an archive job to aggregate summary data before SQL Server runs it's purge job after 14 days. Regarding performance overhead, Microsoft detected approximately 3 - 4% increase in CPU performance on it's TPC-C tests, which is fairly low overhead.

Performance Studio comes with 3 built-in Collection Sets: Disk Usage Collection Set, Query Activity Collection Set and Server Activity Collection Set. The Disk Usage Collection Set collects data every 6 hours and retains it for a default of 730 days. It gathers data and log disk usage and plots them over time. This gives a nice visual view of data file growth over time.

The Query Activity Collection Set uploads query activity every 15 minutes and retains it for 14 days. It caches active sessions and requests from DMV's every 10 seconds. It uses dm_exec_query_stats and uploads the 3 most "interesting" queries and any queries where the query plan has changed. What constitutes an interesting query, I have no idea :-) These can then be viewed graphically based on CPU, Duration, Total I/O, Physical Reads or Logical Writes. You can drill-down into the reports to show the query text, query plan, showplan and any missing indexes identified that could improve the query execution.

The Server Activity Collection Set may turn out to be the most useful performance tuning weapon. Data is uploaded every 15 minutes to the MDW and is collected every 10/60 seconds depending on the particular counter. This data is retained for 14 days before being purged. It collects data on Server CPU usage, Memory, Waitstats, Disk I/O and Network Usage, amongst others. Again, you can click through these reports for detailed information.

In addition to the built-in functionality, you can also create your own collection sets, however be careful doing this or you may end up collecting huge amounts of data, particularly if you run a SQL Trace collection. However, if you do want to run a Profiler collection, set up a trace in Profiler, select "Export" then "Script trace definition for SQL Trace Collection Set" option. This will then script out the XML trace definition for you which can then be executed against your SQL Server system... pretty cool !

For more information, see the following Webcast. It's well worth taking a look !

https://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032349947&CountryCode=US

Cheers,

Rob Carrol

PFE SQL Server

Microsoft UK

Microsoft SQL Server 2008 Feature Pack RC0, June 2008

2008-06-17T13:22:00Z

The Report Builder in the Feb CTP was a preview and doesn’t ship in the RTM box. However, Report Builder 2.0 is in the RC0 Feature Pack from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=089A9DAD-E2DF-43E9-9CD8-C06320520B40&displaylang=en

CU# 8 for SQL Server 2005 SP2 Released

2008-06-17T07:52:00Z

Today (6/16/2008) we shipped the eighth Cumulative Update for SQL Server 2005 SP2 On Time. This CU represents· 50 Resolved Issues· 39 Unique Customer Requests All teams across SQL and CSS deserve kudos for their continued success in shipping Cumulative Update releases. We continue to make improvements to the CU process and we appreciate your feedback; please look for CU#9 announcement soon! As per POR, this CU is not released as a public download, but as a hotfix download to http://hotfix. The associated cumulative KB article has also been published, whereby customers are directed to contact CSS to get the CU build. Customers can also obtain the hotfix package through the new self service feature by clicking on the “Hotfix Download Available” button found at the top of the KB article. Public KB Article

SQL 2008 OpsDay - Data and Backup Compression

2008-06-06T07:49:00Z

Hi all,

Just wanted to thank all the attendees that participated to yesterday's event on SQL Server 2008 in TVP - Reading.

We've received great feedback on the content of the day, and I really hope everybody went back home (for some of you I heard it was quite a long way!) sure that SQL Server 2008 is a great product full of new features and enhancements which are just what you need for your business activities.

I want to follow-up some questions which I've received during the event, and leave some links to review if you want to further learn about Data and Backup Compression.

Questions:

"Data compression is an enterprise only feature. Is it possible though to restore compressed backup on standard?"

Though creating compressed backups is supported only in SQL Server 2008 Enterprise Edition and later, every SQL Server 2008 or later edition can restore a compressed backup.

"Are there some guidelines to tune performances of backup compression"?

Yes, one interesting article from Technet and from SQL CAT: