June 2010 - Posts

 We have acquired two additional tickets to attend the SQL Server Master Class with Paul Randal and Kimberly Tripp  For a chance to win these coveted tickets In the subject line type MasterClass and email SQLFAQ@torver.net before 9pm on Sunday night  The winners will be announced Monday Morning  Don’t worry if you have already purchased a ticket, should you be win, your ticket cost will be reimbursed


Posted by Testas | 1 comment(s)

IT professionals will use Reporting Services Configuration Manager to perform post installation tasks for SQL Server Reporting Services. Introduced in SQL Server 2005, Reporting Services Configuration Manager provides an intuitive interface to perform tasks including specifying the report server database, report manager url, and indeed one of the first post installation tasks that should be performed is backing up the encryption keys that are used to protect the sensitive information within the rdl files.


Many of the options that are selected within Reporting Services Configuration Manager are written to a number of configuration files including the rsreportserver.config file located in C:\Program Files\Microsoft SQL Server\Report Server InstanceName\Reporting Services\ReportServer folder.When opening this file you will notice that there are more configuration settings within the rsreportserver.config file than is available through the Reporting Services Configuration Manager Interface. As a result there are additional configuration options that can be defined within this file.


A customer was having a problem performing stress tests against a new Report Server that would be going live for an enterprise reporting system. One aspect of the stress test was to fire 50 connections from a single user account. When performing the stress test an error described that the maximum active request had been exceeded. Within the rsreportserver.config, there is a key that is added to the file:


<Add Key=”MaxActiveReqForOneUserValue=”20”/>


Changing the value from 20 to 50 accommodated the needs of the stress test, however, a wider question should be asked pertaining to this setting when implementing Reporting Services to a production environment. Within an intranet environment, the default setting is appropriate when network bandwidth is high, users are known and demand for reports is particularly high from a group of users.


However, when deploying a Reporting Server solution to an extranet, or the internet, you may want to consider reducing this setting to reduce to scope of connections that can be acquired by a single user and placing unnecessary pressure on the report server. I do hope that Reporting Services Configuration Manager evolves to include an advanced page that includes an intuitive interface to change configuration settings such as the MaxActiveReqForOneUser, and also configure rendering and data extensions and define secure connection levels to the report server. All these options can be configured within the rsreportserver.config file, and these are setting that customers would like to see in Reporting Services Configuration Manager in the future.


If you think that the SQL community would benefit from this addition, you can vote on it at Microsoft Connect




Posted by Testas | 1 comment(s)


Here is a problem I was emailed last night




SSRS 2008 on Windows 7 or Windows 2008 Server is configured with a user account that is a member of the administrator's group that cannot access report Manager without running IE as Administrator and adding  the SSRS server into trusted sites. (The Builtin administrators account is by default made a member of the System Administrator and Content Manager SSRS roles).


As a result the OS limits the use of using elevated permissions by removing the administrator permissions when accessing applications such as SSRS



Resolution - Two options


  1. Continue to run IE as administrator, you must still add the report server site to trusted sites
  2. Add the site to trusted sites and manually add the user to the system administrator and content manager role
    1. Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.
    2. Click Allow to continue.
    3. In the URL address, enter the Report Manager URL.
    4. Click Tools.
    5. Click Internet Options.
    6. Click Security.
    7. Click Trusted Sites.
    8. Click Sites.
    1. Add http://<your-server-name>.
    1. Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
    1. Click Add.
    1. Click OK.
    2. In Report Manager, on the Home page, click Folder Settings.
    3. In the Folder Settings page, click Security.
    4. Click New Role Assignment.
    5. Type your Windows user account in this format: <domain>\<user>.
    6. Select Content Manager.
    7. Click OK.
    8. Click Site Settings in the upper corner of the Home page.
    9. Click security.
    10. Click New Role Assignment.
    11. Type your Windows user account in this format: <domain>\<user>.
    12. Select System Administrator.
    13. Click OK.
    1. Close Report Manager.
    1. Re-open Report Manager in Internet Explorer, without using Run as administrator.


Problems will also exist when deploying SSRS reports from Business Intelligence Development Studio (BIDS) on Windows  7 or Windows 2008, therefore you should run Business Intelligence Development Studio as Administor


information on this issue can be found at <http://msdn.microsoft.com/en-us/library/bb630430.aspx>

Posted by Testas | 2 comment(s)


The winner of the SQL Server MasterClass competition courtesy of the UK SQL Server User Group and SQL Server Magazine!  


Steve Hindmarsh  


There is still time to register for the seminar yourself at:




More information about the seminar   


Where: Radisson Edwardian Heathrow Hotel, London


When: Thursday 17th June 2010  This one-day MasterClass will focus on many of the top issues companies face when implementing and maintaining a SQL Server-based solution. In the case where a company has no dedicated DBA, IT managers sometimes struggle to keep the data tier performing well and the data available. This can be especially troublesome when the development team is unfamiliar with the affect application design choices have on database performance. The Microsoft SQL Server MasterClass 2010 is presented by Paul S. Randal and Kimberly L. Tripp, two of the most experienced and respected people in the SQL Server world. Together they have over 30 years combined experience working with SQL Server in the field, and on the SQL Server product team itself. This is a unique opportunity to hear them present at a UK event which will:
  • Debunk many of the ingrained misconceptions around SQL Server's behaviour   
  • Show you disaster recovery techniques critical to preserving your company's life-blood - the data   
  • Explain how a common application design pattern can wreak havoc in the database
  • Walk through the top-10 points to follow around operations and maintenance for a well-performing and available data tier!
Please Note: Agenda may be subject to change


Sessions Abstracts


KEYNOTE: Bridging the Gap Between Development and Production  


Applications are commonly developed with little regard for how design choices will affect performance in production. This is often because developers don't realize the implications of their design on how SQL Server will be able to handle a high workload (e.g. blocking, fragmentation) and/or because there's no full-time trained DBA that can recognize production problems and help educate developers. The keynote sets the stage for the rest of the day. Discussing some of the issues that can arise, explaining how some can be avoided and highlighting some of the features in SQL 2008 that can help developers and DBAs make better use of SQL Server, and troubleshoot when things go wrong.   SESSION ONE: SQL Server Mythbusters


It's amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server - after many years helping people out on forums, newsgroups, and customer engagements, Paul and Kimberly have heard it all. Are there really non-logged operations? Can interrupting shrinks or rebuilds cause corruption? Can you override the server's MAXDOP setting? Will the server always do a table-scan to get a row count? Many myths lead to poor design choices and inappropriate maintenance practices so these are just a few of many, many myths that Paul and Kimberly will debunk in this fast-paced session on how SQL Server operates and should be managed and maintained. 


SESSION TWO: Database Recovery Techniques Demo-Fest


Even if a company has a disaster recovery strategy in place, they need to practice to make sure that the plan will work when a disaster does strike. In this fast-paced demo session Paul and Kimberly will repeatedly do nasty things to databases and then show how they are recovered - demonstrating many techniques that can be used in production for disaster recovery. Not for the faint-hearted! 


SESSION THREE: GUIDs: Use, Abuse, and How To Move Forward 


Since the addition of the GUID (Microsoft’s implementation of the UUID), my life as a consultant and "tuner" has been busy. I’ve seen databases designed with GUID keys run fairly well with small workloads but completely fall over and fail because they just cannot scale. And, I know why GUIDs are chosen - it simplifies the handling of parent/child rows in your batches so you can reduce round-trips or avoid dealing with identity values. And, yes, sometimes it's even for distributed databases and/or security that GUIDs are chosen. I'm not entirely against ever using a GUID but overusing and abusing GUIDs just has to be stopped! Please, please, please let me give you better solutions and explanations on how to deal with your parent/child rows, round-trips and clustering keys! 


SESSION 4: Essential Database Maintenance


In this session, Paul and Kimberly will run you through their top-ten database maintenance recommendations, with a lot of tips and tricks along the way. These are distilled from almost 30 years combined experience working with SQL Server customers and are geared towards making your databases more performant, more available, and more easily managed (to save you time!). Everything in this session will be practical and applicable to a wide variety of databases. Topics covered include: backups, shrinks, fragmentation, statistics, and much more! Focus will be on 2005 but we'll explain some of the key differences for 2000 and 2008 as well.

Speaker Biographies

Kimberley L. Tripp 
Paul and Kimberly are a husband-and-wife team who own and run SQLskills.com, a world-renowned SQL Server consulting and training company. They are both SQL Server MVPs and Microsoft Regional Directors, with over 30 years of combined experience on SQL Server. Paul worked on the SQL Server team for nine years in development and management roles, writing many of the DBCC commands, and ultimately with responsibility for core Storage Engine for SQL Server 2008. Paul writes extensively on his blog (SQLskills.com/blogs/Paul) and for TechNet Magazine, for which he is also a Contributing Editor. Kimberly worked on the SQL Server team in the early 1990s as a tester and writer before leaving to found SQLskills and embrace her passion for teaching and consulting. Kimberly has been a staple at worldwide conferences since she first presented at TechEd in 1996, and she blogs at SQLskills.com/blogs/Kimberly. They have written Microsoft whitepapers and books for SQL Server 2000, 2005 and 2008, and are regular, top-rated presenters worldwide on database maintenance, high availability, disaster recovery, performance tuning, and SQL Server internals. Together they teach the SQL MCM certification and throughout Microsoft.In their spare time, they like to find frogfish in remote corners of the world. 


Speaker Testimonials


"To call them good trainers is an epic understatement. They know how to deliver technical material in ways that illustrate it well. I had to stop Paul at one point and ask him how long it took to build a particular slide because the animations were so good at conveying a hard-to-describe process."

"These are not beginner presenters, and they put an extreme amount of preparation and attention to detail into everything that they do. Completely, utterly professional."

"When it comes to the instructors themselves, Kimberly and Paul simply have no equal. Not only are they both ultimate authorities, but they have endless enthusiasm about the material, and spot on delivery. If either ever got tired they never showed it, even after going all day and all week. We witnessed countless demos over the course of the week, some extremely involved, multi-step processes, and I can’t recall one that didn’t go the way it was supposed to."

"You might think that with this extreme level of skill comes extreme levels of egotism and lack of patience. Nothing could be further from the truth. ... They simply know how to teach, and are approachable, humble, and patient."

"The experience Paul and Kimberly have had with real live customers yields a lot more information and things to watch out for than you'd ever get from documentation alone." “Kimberly, I just wanted to send you an email to let you know how awesome you are! I have applied some of your indexing strategies to our website’s homegrown CMS and we are experiencing a significant performance increase. WOW....amazing tips delivered in an exciting way!  Thanks again”