SQL Server 2005 Service Pack 2 (March 6, 2007)
Chat Topic: SQL Server 2005 Service Pack 2
Date: Tuesday, March 6, 2007
**Please note:****Portions of this transcript have been edited for clarity
Katheryn (Moderator):
Good Morning and welcome to the SQL Server 2005 Service Pack 2 Chat!
Katheryn (Moderator):
I would like to start by having the experts introduce themselves:
Introductions
Jeff Bernhardt [MSFT] (Expert):
I’m Jeff Bernhardt, development manager for SSIS. I’ve been at Microsoft for 13 years working with data most of that time. For SP2 I pushed for the improved SSIS connectivity diagnostic logging and I dealt with the VSA incompatibility (that hopefully none of you will hear about).
Grant [MSFT] (Expert):
Hi, I'm Grant Culbertson. I managed the overall SP2 release effort. I've been at Microsoft for 8 yrs.
Amy Lewis - MSFT (Expert):
Hi, I’m Amy Lewis and I’m a PM for the SLP Team. I’ve been with MSFT for 6 years.
Carolyn [MSFT] (Expert):
Hi, I'm Carolyn with the Reporting Services team. I'm the program manager responsible for the Reporting Services integration with SharePoint in SP2.
PeterSad [MSFT] (Expert):
I am Peter Saddow the Program Manager for the SP2 installer. I have been working on the SQL Server SP1 and SQL Server SP2 installers for the last 1.5 years.
Brian - SSRS [MSFT] (Expert):
I'm Brian Welcker, Group Program Manager for Reporting Services. To see what's new in Reporting Services in SP2, see https://msdn2.microsoft.com/en-us/library/bb283536.aspx#BKMK_ReportingServices.
Mahesh [MSFT] (Expert):
Hi, my name is Mahesh and I am the test lead for Merge replication. I have been with Microsoft for 4 years.
Michael Entin [MSFT] (Expert):
Hi, I'm Michael Entin, I'm a technical lead in SSIS team.
AjayMa [MSFT] (Expert):
My name is Ajay Manchepalli and I am Lead Program Manager for SQL Server Release Customer Programs such as TAP and public CTP. I have been w/ Microsoft since 1998 taking on various roles involving SQL Server.
Sshekar [MSFT] (Expert):
Hi, My name is Sangeetha Shekar, Tester, Plan Cache, Programmability team.
Umachandar [MSFT] (Expert):
My name is Umachandar "UC". I work in the SQL Performance Engineering Team.
Glenn [MSFT] (Expert):
My name is Glenn Gailey and I wrote the SP2 readme file.
Katheryn (Moderator):
Thanks to these great experts for being here today to answer questions about SQL Server 2005 SP2! Let's get started. Please be sure to check "Ask the Experts" when asking your questions.
Start of Chat
Grant [MSFT] (Expert):
Q: According to SSWUG.ORG, there should be a hotfix for SP2 posted today. Can someone comment?
A: You may have heard about an issue we blogged about regarding Maintenance Plan cleanup task intervals (https://blogs.msdn.com/sqlrem/archive/2007/02/24/Maintenance-Plan-Changes-in-SQL-Server-2005-SP2.aspx). Last night we refreshed the main SP2 upgrade packages to address this, and we also plan to issue a public hotfix very soon - possibly today. You will see an announcement posted on the main SP2 landing page (https://www.microsoft.com/sql/sp2.mspx) when this is available. [Ed: This public hotfix was issued on 3/6, see https://support.microsoft.com/kb/933508 for details.]
Brian - SSRS [MSFT] (Expert):
To get things rolling, who has deployed SP2 (either successfully or unsuccessfully)?
Brian - SSRS [MSFT] (Expert):
Q: I already have deployed service pack 2 with success.
A: Excellent.
Brian - SSRS [MSFT] (Expert):
Q: we are getting ready to install sp2 in our test environment for SAP Netweaver products.
A: Cool. Are you planning on using the SSRS / SAP BI integration? If you are successful, It would be great to post the details to the forums on MSDN.
Carolyn [MSFT] (Expert):
Q: We are also looking at Sharepoint as a large rollout this year and SP2 reporting services seem to provide beneifts, can you comment?
A: We've done quite a bit of work in SP2 to integrate Reporting Services with SharePoint. You can now directly view and manage Reporting Services reports in SharePoint and get all of SharePoint's great workflow and collaboration capabilities.
Jeff Bernhardt [MSFT] (Expert):
Q: Is expectable to have a 64-bit JET provider released soon for Excel to be used in SSIS? I use an Itanium OS.
A: There are no plans (that I am aware of) for a 64bit version of JET.
Grant [MSFT] (Expert):
Q: Deployed SP2 in dev only. No hiccups. We're still in the planning stages for SQL2005 rollout, and we were waiting for SP2 for the enhanced SQL 2000-2005 migration functionality.
A: Excellent, glad it's going well so far. :)
Brian - SSRS [MSFT] (Expert):
Q: Have the page numbering issue been resolved in Reporting Services (web numbering and then printing them by numbers and grouping). I'm not a Reporting Services type but my delvelopers keep complaining about this.
A: Reports are not physically paginated until you use print preview (or export to a physical page format like PDF). This allows you to change the paper size, margins, etc. and have the ourput repaginated. Unfortunately, this means that the HTML page numbering isn't going to always reflect the physical numbering. For example, we don't do horizontal page breaks in HTML. If you have a custom application, you could bypass the HTML view entirely.
Amy Lewis - MSFT (Expert):
Q: Hi! Can I put in the command line for the installation the ADDLOCAL and UPGRADE parameters at same time?
A: Can you provide more details on what you are doing?
Brian - SSRS [MSFT] (Expert):
Q: We are concerned about some of the backward-compatibility issues listed in the SP2 documentation. Has anyone encountered these?
A: Other guests on the chat can respond to this one. Just post to the guest chat.
Brian - SSRS [MSFT] (Expert):
Q: Can you explain about the 'All' option in SSRS, it was in, then out, then in. We had to 'code' to implement this in. What was the thought process behind the in and out? What migration issues we should consider when using the new 'All' feature?
A: I blogged on this at https://blogs.msdn.com/bwelcker/archive/2006/08/14/700189.aspx.
Katheryn (Moderator):
Hi all, since we started late with this chat, we will extend the chat 10 minutes past 10am PT...so we have about 30 more min. left in the chat. Please be sure to get your questions in!
Umachandar [MSFT] (Expert):
Q: I noticed that SP2 still assumes that the master database data and log files are in the same location (meaning the resource database is too) but if someone moves the data and log files of both (which you would have to if you do that to master) it will fail.
A: Yes, this is still the case. Resource database location depends on the master database. The following topics will help: Resource Database - https://msdn2.microsoft.com/en-us/library/ms190940.aspxMoving System Databases - https://msdn2.microsoft.com/en-us/library/ms345408.aspx
Bill Ramos [MSFT] (Expert):
Q: I love the reporting added to the studio manager. Are there plans to have additions to it such as for replication?
A: Hi Rich - Are you referring to the ability to add your own custom management reports within SSMS? The capability should work with replication nodes.
Brian - SSRS [MSFT] (Expert):
Q: Reading one of the other questions, I was wondering what enhanced migration utilities Service Pack 2 has for SQL 2000 to 2005?
A: Actually, we were kind of puzzled by that as well. Maybe JoshG can explain what he is thinking of.
Grant [MSFT] (Expert):
Q: we are getting ready to install sp2 in our test environment for SAP Netweaver products.
A: This is great. Good luck with your deployment!
Bill Ramos [MSFT] (Expert):
Q: Is there a reason that vardecimal cannot be used as a datatype upon creation of a table?
A: Hi Rich. The introduction of the vardecimal functionality was designed to minimize the impact on the overall product. Ideally, you would have wanted to specify this as part of the Create Table statement, but we weren't sure how the feature would change in the future so we went the sp_tableoption route. We also could not accommodate changes to the Table designer within the SP2 timeframe.
Bill Ramos [MSFT] (Expert):
Q: On the reports in SSMS > I guess I could but the reports that were created with SP2 had some I hadn't thought of creating. The only one that is bugging me is the ability to find out what specific transactions are failing so I can isolate the table in PTP.
A: Right - You could actually create your own reports to help make this information more visible. Check out Paul Mestemaker’s series on customer reports at https://blogs.msdn.com/sqlrem. This also allows the replication team to add reports for downloading over time.
Christian (Moderator):
We have a few minutes left for today’s chat. If you have any last minute questions, please submit them asap. The Experts will answer as many questions as they can in the time remaining. Thanks!
Grant [MSFT] (Expert):
Q: On SSAS SP1 + Hotfixes server configuration properties, there is a parameter (UseCachedPageAllocaters) that optimize the time needed for client applications (such Excel 2007) open/connect a cube. With SP2 this parameter is not supported or was replaced?
A: You’ll have to contact CSS – on occasion we recommended using server properties to remedy performance issues. Many of these were fixed – but CSS will know for sure.
Jeff Bernhardt [MSFT] (Expert):
Q: Several speakers at the fall SQL Magazine conference mentioned that SP2 would fix some little annoyances when migrating 2000 objects to 2005. I thought there were improvements in migrating maintenance plans and/or DTS packages.
A: There have been no improvements in the package migration wizard to convert DTS packages into SSIS packages. I cannot comment on any changes made by the management tools team with regards to migrating objects between versions of the server.
Michael Entin [MSFT] (Expert):
Q: Several speakers at the fall SQL Magazine conference mentioned that SP2 would fix some little annoyances when migrating 2000 objects to 2005. I thought there were improvements in migrating maintenance plans and/or DTS packages.
A: One "annoyance" has been fixed in DTS package migration wizard: it fixes handling of the special characters inside task or step names. Wizard could fail before SP2 if the DTS package has an object with slash, space, or other character that is not allowed by SSIS. Now this is fixed.
Glenn [MSFT] (Expert):
Q: Sharepoint and SP2, I was wondering if any more information can be provided on the enhancements provided in SP2, links, caveats, etc.
A: We have collected a list of the product feature and functionality changes introduced in SP2 into the What's New in SQL Server SP2 topic in the Books Online update (https://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx) and on the Web (https://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm). CSS also publishes a list of SP2 fixes at https://support.microsoft.com/kb/921896.
Grant [MSFT] (Expert):
Q: Is the entire script from this webchat sent to us at the end?
A: We will be posting a transcript of this chat on the TechNet Chat site at https://www.microsoft.com/technet/community/chats/trans soon. Incidentally, we'll make sure that any questions that remain unanswered at the end of the chat will be answered in that transcript.
Grant [MSFT] (Expert):
Q: has anyone already installed SP2 on SAP Netweaver products and were there any issues?
A: SAP was part of the TAP program for SP2, an intensive testing program where they deployed several pre-release builds into production and informed us of any issues they encountered so we could address those prior to shipping.
Grant [MSFT] (Expert):
Q: There were some performance issues encountered with TOPCOUNT fix in SSAS for hotfix 2185. They were partially resolved in the hotfix. Were further improvements done?
A: We changed the semantics of such queries to return different results. Now the topcount function applies the slice before the expression is evaluated. So now when you ask for top n customers in USA, we don’t evaluate the top 10 across the world and then reduce the set to those in USA (returning possibly fewer than n) but we only consider USA. See https://www.sqljunkies.com/WebLog/mosha/archive/2006/11/12/slicer_axis_interaction_deep_exists.aspx for more information on this. A related technique is to use more attributes in the partition strategy, ones which are used in slicing for the query subcubes. For details on how this can help and an example using topcount, see https://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx.Finally, we improved many aspects of Analysis Services performance in SP2 and you should experiment to see if the changes apply to your situation.
Grant [MSFT] (Expert):
Q: Has anyone successfully set up an HTTP SOAP endpoint? I have not got that feature to work get an HTTP error on connection.
A: There are no known issues with this configuration, so you should post in the SQL Server Forums on MSDN with more details about your scenario.
Grant [MSFT] (Expert):
Q: Does SP2 support DTS packages still or does it require a migration to SSIS?
A: We have not dropped support for DTS with the SP2 release. Whatever you did previously in SQL 2005 should still work fine after applying SP2.
Grant [MSFT] (Expert):
Q: I just like to thank all the experts coming out!
A: Thanks for your interest and participation!
Christian (Moderator):
Well that wraps it up for today’s chat. Thanks to everyone for participating, and also thanks to the Experts for being here to answer everyone’s great questions.