SQL Server Thoughts from the Field

Enabling Kerberos Authentication for Reporting Services

Rob_Carrol — Wed, 23 Nov 2011 01:08:00 GMT

Recently, I’ve helped several customers with Kerberos authentication problems with Reporting Services and Analysis Services, so I’ve decided to write this blog post and pull together some useful resources in one place (there are 2 whitepapers in particular that I found invaluable configuring Kerberos authentication, and these can be found in the references section at the bottom of this post). In most of these cases, the problem has manifested itself with the Login failed for User ‘NT Authority\Anonymous’ (“double-hop”) error.

By default, Reporting Services uses Windows Integrated Authentication, which includes the Kerberos and NTLM protocols for network authentication. Additionally, Windows Integrated Authentication includes the negotiate security header, which prompts the client to select Kerberos or NTLM for authentication. The client can access reports which have the appropriate permissions by using Kerberos for authentication. Servers that use Kerberos authentication can impersonate those clients and use their security context to access network resources.

You can configure Reporting Services to use both Kerberos and NTLM authentication; however this may lead to a failure to authenticate. With negotiate, if Kerberos cannot be used, the authentication method will default to NTLM. When negotiate is enabled, the Kerberos protocol is always used except when:

Clients/servers that are involved in the authentication process cannot use Kerberos.
The client does not provide the information necessary to use Kerberos.

An in-depth discussion of Kerberos authentication is beyond the scope of this post, however when users execute reports that are configured to use Windows Integrated Authentication, their logon credentials are passed from the report server to the server hosting the data source. Delegation needs to be set on the report server and Service Principle Names (SPNs) set for the relevant services. When a user processes a report, the request must go through a Web server on its way to a database server for processing. Kerberos authentication enables the Web server to request a service ticket from the domain controller; impersonate the client when passing the request to the database server; and then restrict the request based on the user’s permissions. Each time a server is required to pass the request to another server, the same process must be used.

Kerberos authentication is supported in both native and SharePoint integrated mode, but I’ll focus on native mode for the purpose of this post (I’ll explain configuring SharePoint integrated mode and Kerberos authentication in a future post). Configuring Kerberos avoids the authentication failures due to double-hop issues. These double-hop errors occur when a users windows domain credentials can’t be passed to another server to complete the user’s request. In the case of my customers, users were executing Reporting Services reports that were configured to query Analysis Services cubes on a separate machine using Windows Integrated security. The double-hop issue occurs as NTLM credentials are valid for only one network hop, subsequent hops result in anonymous authentication.

The client attempts to connect to the report server by making a request from a browser (or some other application), and the connection process begins with authentication. With NTLM authentication, client credentials are presented to Computer 2. However Computer 2 can’t use the same credentials to access Computer 3 (so we get the Anonymous login error). To access Computer 3 it is necessary to configure the connection string with stored credentials, which is what a number of customers I have worked with have done to workaround the double-hop authentication error.

However, to get the benefits of Windows Integrated security, a better solution is to enable Kerberos authentication. Again, the connection process begins with authentication. With Kerberos authentication, the client and the server must demonstrate to one another that they are genuine, at which point authentication is successful and a secure client/server session is established.

In the illustration above, the tiers represent the following:

Client tier (computer 1): The client computer from which an application makes a request.
Middle tier (computer 2): The Web server or farm where the client’s request is directed. Both the SharePoint and Reporting Services server(s) comprise the middle tier (but we’re only concentrating on native deployments just now).
Back end tier (computer 3): The Database/Analysis Services server/Cluster where the requested data is stored.

In order to enable Kerberos authentication for Reporting Services it’s necessary to configure the relevant SPNs, configure trust for delegation for server accounts, configure Kerberos with full delegation and configure the authentication types for Reporting Services. These steps are outlined in greater detail in the "Manage Kerberos Authentication Issues in a Reporting Services Environment" whitepaper in the resources section at the end of this article.

Service Principle Names (SPNs) are unique identifiers for services and identify the account’s type of service. If an SPN is not configured for a service, a client account will be unable to authenticate to the servers using Kerberos. You need to be a domain administrator to add an SPN, which can be added using the SetSPN utility. For Reporting Services in native mode, the following SPNs need to be registered

--SQL Server Service
SETSPN -S mssqlsvc/servername:1433 Domain\SQL

For named instances, or if the default instance is running under a different port, then the specific port number should be used.

--Reporting Services Service
SETSPN -S http/servername Domain\SSRS
SETSPN -S http/servername.domain.com Domain\SSRS

The SPN should be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered

SETSPN -S http/www.reports.com Domain\SSRS

--Analysis Services Service
SETSPN -S msolapsvc.3/servername Domain\SSAS

Next, you need to configure trust for delegation, which refers to enabling a computer to impersonate an authenticated user to services on another computer:

Location

Description

Client

1. The requesting application must support the Kerberos authentication protocol.

2. The user account making the request must be configured on the domain controller. Confirm that the following option is not selected: Account is sensitive and cannot be delegated.

Servers

1. The service accounts must be trusted for delegation on the domain controller.

2. The service accounts must have SPNs registered on the domain controller. If the service account is a domain user account, the domain administrator must register the SPNs.

In Active Directory Users and Computers, verify that the domain user accounts used to access reports have been configured for delegation (the ‘Account is sensitive and cannot be delegated’ option should not be selected):

We then need to configure the Reporting Services service account and computer to use Kerberos with full delegation:

We also need to do the same for the SQL Server or Analysis Services service accounts and computers (depending on what type of data source you are connecting to in your reports).

Finally, and this is the part that sometimes gets over-looked, we need to configure the authentication type correctly for reporting services to use Kerberos authentication. This is configured in the Authentication section of the RSReportServer.config file on the report server.

<AuthenticationTypes>

<RSWindowsNegotiate/>

</AuthenticationTypes>

<EnableAuthPersistence>true</EnableAuthPersistence>

</Authentication>

This will enable Kerberos authentication for Internet Explorer. For other browsers, see the link below. The report server instance must be restarted for these changes to take effect.

Once these changes have been made, all that’s left to do is test to make sure Kerberos authentication is working properly by running a report from report manager that is configured to use Windows Integrated authentication (either connecting to Analysis Services or SQL Server back-end).

Resources:

Manage Kerberos Authentication Issues in a Reporting Services Environment
http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSRSKerberos.docx

Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23176

How to: Configure Windows Authentication in Reporting Services
http://msdn.microsoft.com/en-us/library/cc281253.aspx

RSReportServer Configuration File
http://msdn.microsoft.com/en-us/library/ms157273.aspx#Authentication

Planning for Browser Support
http://msdn.microsoft.com/en-us/library/ms156511.aspx

Hosting the Performance Dashboard Reports in SSRS

Rob_Carrol — Tue, 28 Sep 2010 16:57:00 GMT

I blogged a while back about modifying the SQL Server 2005 Performance Dashboard reports to run on SQL Server 2008. I’ve since been working with several customers who use these reports for performance troubleshooting, but who would like to host them on their Reporting Services platform so they can be viewed online instead of within SQL Server Management Studio. So over the past few days I’ve been killing time on flights and trains doing just that. So here goes…

It’s actually very simple to get the reports up and running on Reporting Services. All you have to do is install the Performance Dashboard reports, create a new Report Server project in BIDS (or Visual Studio) and import the .rdl files from the directory they were installed to (right-click the solution name in Solution Explorer –> Add –> Existing Item).

They use a shared data source, so you need to update this to point to the SQL Server instance you want to monitor (making sure that you have enabled the instance for use with the Performance Dashboard reports first), deploy the reports and data source to your Report Server and you’re ready to roll.

However, this means that you can only look at one server. To monitor multiple servers, you would would need to repeat the process and host a separate copy of the reports for each one… not very scalable !

The solution I’ve come up with (and which you can download in the attached .zip file) requires you to publish the reports only once and use a parameter to dynamically determine which server we want to connect to in our data source. This parameter is set in the Performance_Dashboard_Main.rdl file when you first launch the report and is used as an expression in the report data source to dynamically build the connection string.

We need to modify the existing data source to be an embedded data source as using expressions in connection strings is not supported with shared data sources.

This parameter is then passed through to subsequent linked reports to build the dynamic data source connection for those reports as well.

I’ve created 2 versions of the Performance_Dashboard_Main report (Performance_Dashboard_Main.rdl and Performance_Dashboard_Main_CMS.rdl) which I’ve included in the attached solution file. The first one uses a free-text field to enter the server name and the second one uses the new Central Management Server (CMS) functionality in SQL Server 2008 to dynamically populate a drop-down list of servers you have already registered on your CMS… very cool !

These reports have been designed and tested to work on SSRS 2008, however you can use the same technique to host them on SSRS 2005 or 2008. The data source for the reports can also either point to SQL Server 2005 or 2008 instances (as long as you’ve followed the instructions for modifying the reports for SQL Server 2008 first). As I’ve mentioned before, these reports are not a replacement for the fantastic new Management Data Warehouse functionality of SQL Server 2008, but can provide another valuable tool to help DBA’s analyse performance issues.

Download the reports, have a play and let me know what you think !

SSUG Presentation 19th August

Rob_Carrol — Fri, 20 Aug 2010 11:15:00 GMT

Thanks to all who attended the SSUG meeting in Edinburgh last night. I've included my slides and demo scripts in the attached file (SSUG_190810_ExecutionPlans.zip). I've also included some additional references relating to discussions we had during the course of the evening. If you need any other information, give me a shout.

Creating a Statistics-only "Cloned" Database:
http://support.microsoft.com/kb/914288
http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/21/cloning-in-sql-server-2005.aspx

Database Tuning Advisor (DTA) - Reducing the Production Server Tuning Load
http://technet.microsoft.com/en-us/library/ms190389.aspx

Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527.aspx

Improving Performance of Dynamic SQL
http://msdn.microsoft.com/en-us/library/ms175170(SQL.105).aspx
http://msdn.microsoft.com/en-gb/magazine/ee236412.aspx

See you all at the next meeting !

Rob

Scottish SQL Server User Group Meeting 19th August

Rob_Carrol — Thu, 12 Aug 2010 14:24:19 GMT

The next Scottish SQL Server User Group meeting will take place on Thursday 19th August at the Microsoft offices in Edinburgh. I'll be talking about SQL Server execution plans and how to use them to identify issues in your application code and Martin will be educating us all with his knowledge of Extended Events. Pizza and refreshments will be provided as always, full details and registration information can be found below...

http://www.sqlserverfaq.com/events/241/SQL-Server-User-Group.aspx

SQL Server Wow64 Perfmon Issues

Rob_Carrol — Mon, 31 May 2010 00:57:00 GMT

I recently came across some issues running perfmon on Windows Server 2003 where the OS is 64-bit and SQL Server is 32-bit (Wow64). The SQL Performance counters were not appearing as the default Perfmon is 64-bit, which is unable to read the 32-bit SQL counters. Running the 32-bit version of Perfmon allows us to read the SQL counters. This can be done by either running the C:\WINDOWS\syswow64\smlogsvc.exe file directly or running the following from the command-line:

mmc /32 Perfmon.msc

So far, so good. However, attempts to create a performance counter log were failing as the Performance Logs and Alerts service was configured to use the 64-bit version of the smlogsvc executable. This was fixed by modifying the following registry keys (the usual warnings regarding modifying the registry apply here):

1)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SysmonLog\ImagePath, change ImagePath value from %SystemRoot%\system32\smlogsvc.exe to
%SystemRoot%\syswow64\smlogsvc.exe
2)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\SysmonLogEventMessageFile, change EventMessageFile value from
%SystemRoot%\system32\smlogsvc.exe to %SystemRoot%\syswow64\smlogsvc.exe

Perfmon can then be started with the /32 switch (or run the C:\WINDOWS\syswow64\smlogsvc.exe file directly) and the SQL counters succesfully logged.

How It Works: Almost Everything You Wanted To Know About The SQL Server (2005, 2008) Performance Counter Collection Components
http://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works-almost-everything-you-wanted-to-know-about-the-sql-server-2005-2008-performance-counter-collection-components.aspx

SQLBits VI Slides Available

Rob_Carrol — Sun, 18 Apr 2010 20:50:00 GMT

I had a great time at SQLBits VI last week and would like to thank everyone for coming along to my session :-) I've attached the slides from the session "SQL Server 2008 Reporting Services Scalability and Performance" for you to download.

Cheers !

SQLBits VI Conference - London 16th April

Rob_Carrol — Wed, 17 Mar 2010 13:41:00 GMT

I am very happy to announce that I've been chosen as a speaker for the SQLBits VI conference in London on the 16th April. The theme for this conference is performance and scalability and I'll be talking about some of the new features in SQL Server Reporting Services 2008 in this area. There is a great line-up of sessions and speakers and best of all... it's FREE to attend !

Full details of the conference and how to register can be found here. Looking forward to seeing you there !

Scottish SQL Server User Group Meeting – 25th Feb

Rob_Carrol — Tue, 16 Feb 2010 23:37:00 GMT

The next Scottish SQL Server User Group meeting will take place in the Microsoft Edinburgh office on Thursday the 25th February. Quest Software’s Iain Kick will be talking about performance troubleshooting and Alistair Board and Martin Bell will be talking about their experiences and resources available for learning SQL Server. As always, it promises to be a great event. Registration details can be found on the link below… looking forward to seeing you there !

http://www.sqlserverfaq.com/events/212/EndtoEnd-SQL-Server-Performance-Troubleshooting-with-Iain-Kick.aspx

Mirroring Multiple SQL Server Databases on a Single Instance

Rob_Carrol — Thu, 11 Feb 2010 13:07:57 GMT

This is a question I get asked a lot by customers (last week being the latest) and the answer really depends on which platform you are running on. There is a support policy in Books Online that states that we only support a maximum of 10 mirrored databases per instance on 32-bit systems, however in the 64-bit world we don’t have any such limitations. The limitation in 32-bit systems is due to the number of worker threads required for mirroring sessions and the fact that we are limited to a finite amount of memory for thread allocations.

However, these restrictions do not apply on 64-bit SQL Server systems and the SQLCAT team has just released a whitepaper showing that it’s possible to mirror in excess of 100 databases on a single SQL Server instance !! The article also has information to help you calculate the value of max_worker_threads and max server memory appropriately.

The article can be found here and is definitely worth a read if you plan to mirror multiple databases on a 64-bit instance of SQL Server:

http://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx

Caching SSRS Reports for Performance

Rob_Carrol — Thu, 11 Feb 2010 01:28:00 GMT

I’ve been fairly quiet on the blogging-front recently, but I have been busy working with SQL Server Reporting Services (SSRS) ! I've also been fortunate enough to present a session to customers on SSRS scalability at a recent Business Intelligence Operations Day in Reading, UK. One thing that struck me from that day was that very few people are aware of the caching options available in Reporting Services and how they can be used to improve report processing performance. A quick show of hands showed that no-one in my session was making use of caching, and only one person required real-time reporting on their data.

When you create a report and deploy it to your report server, the default behaviour is to always run the report with the most recent data and not to implement any caching. Actually, SSRS does implement session level caching for each user request “under the covers” to improve report performance and to provide a consistent experience across a single browser session. Data remains consistent during the report session and is unaffected by changes in the underlying data source. This article provides a good discussion of report session caching.

Unless you need real-time data in your reports, then hitting the underlying data sources each time you run the report is unnecessary and can hurt performance. In most cases, the business requirement is to report on historical data (whether that is from the last 15 minutes, hour, day or week) so we can implement caching to improve the report processing times based on these requirements. If we have no caching, a new instance of the report is created for each user who opens or requests the report; each new instance contains the results of a new query. With this approach, if ten users open the report at the same time, ten queries are sent to the data source for processing. If the report server load is heavy, or if the reports take a significant time to retrieve data and process, then this can have a negative impact on the performance of the Report Server and the underlying databases executing the report queries.

We can implement 2 forms of caching in Reporting Services: temporary cached reports and report snapshots. A temporary cached instance of a report is based on the intermediate format of a report (report data merged with the layout information before any rendering is applied). The report server will cache one instance of a report based on the report name. However, if a report can contain different data based on query parameters, multiple versions of the report may be cached at any time. For example, suppose you have a parameterised report that takes a region code as a parameter value. If four different users specify four unique region codes, four cached copies will be created. The first user who runs the report with a unique region code creates a cached report that contains data for that region. Subsequent users who request a report using the same region code get the cached copy.

With this approach, if ten users open the report, only the first request results in report processing. The report is subsequently cached, and the remaining nine users view the cached report. Cached reports are removed from the cache at intervals that you define. You can specify intervals in minutes, or you can schedule a specific date and time to empty the cache. Not all reports can be cached. If a report prompts users for credentials or uses Windows Authentication, it cannot be cached unless you change the data source to use stored credentials instead. There are certain other events that will invalidate the cache and cause it to be flushed for temporary cached copies of a report. These include if the report definition is modified, report parameters are modified, data source credentials change, report execution options change or if the cache expiration timeout is reached.

A report snapshot is a report that contains layout information and data that is retrieved at a specific point in time. A report snapshot is usually created and refreshed on a schedule, allowing you to time exactly when report and data processing will occur. If a report is based on queries that take a long time to run, or on queries that use data from a data source that you prefer no one access during certain hours, you should run the report as a snapshot. A report snapshot is stored in the intermediary form in the ReportServer database, where it is subsequently retrieved when a user or subscription requests the report. When a report snapshot is updated, it is overwritten with a new instance. The report server does not save previous versions of a report snapshot unless you specifically set options to add it to report history.

Not all reports can be configured to run as a snapshot. For example, you cannot create a snapshot for a report that prompts users for credentials or uses Windows integrated security to get data for the report. Also, if you want to run a parameterised report as a snapshot, you must specify a default parameter to use when creating the snapshot. In contrast with reports that run on demand, it is not possible to specify a different parameter value for a report snapshot once the report is open.

So how do we identify which reports to cache to improve performance? By default, Report Server saves all the details of reports that have executed in the Report Server database. This information can be accessed via the ExecutionLog2 view in SSRS 2008 or by this method in SSRS 2005. Robert Bruckner has written an excellent blog post detailing the information contained in the ExecutionLog2 view, which is highly recommended reading. Using this data, we can analyse where these reports are spending most time during report execution (data retrieval, report processing or rendering). There’s a great article on the SQLCAT site on Reporting Services Performance Optimisations which can help you to address some of these issues. Long-running reports or frequently accessed reports are going to give us greater performance improvements, so these would be good candidates for caching. The figure below shows an extract of data after running the following statement on the Report Server database:

SELECT * FROM [ReportServer].[dbo].[ExecutionLog2]

So we can see that this report is taking 30 seconds to retrieve the data from the underlying data source. We can specify that this report is cached and that the cache will expire every 30 minutes by adjusting the Report Execution properties as shown below:

The next time someone requests the report, the data will be retrieved from the data source and then cached in the ReportServerTempDB. Any subsequent requests from any user for that report (with the same parameter values) will be rendered from the cached copy. The output from the ExecutionLog2 view will now be as follows:

As we can see, the report is now rendered from cache and the data source not queried, resulting in a huge performance improvement. If we change the parameters of the report, then we will go back to the data source to retrieve the new data and a temporary cached copy of the report will be created for the new combination of parameters:

I worked recently with a customer who had set up caching for a report but they were using dynamic date functions in the values for their parameters. Every time the report was executed they had different values for the start and end date parameters, so they were constantly hitting the database with new queries even though they only required to report on the data from the previous evening. We were able to schedule a snapshot of the report to run each evening and then render the reports from that snapshot throughout the day… report execution time went from 20 minutes to a few milliseconds as a result ! The benefit of scheduling a snapshot is that the data is already pre-loaded in cache ready for users the next morning. We don’t suffer the initial performance hit that the first user would experience (in order to refresh data) if we used a temporary cached report.

As you can see, utilising SSRS's caching mechanisms can provide a very powerful way to improve report processing performance and to lighten the load on your database servers !

Scottish SQL Server User Group Meeting - Oct 8th

Rob_Carrol — Thu, 24 Sep 2009 18:38:00 GMT

I have the great pleasure of speaking at the next Scottish SQL Server User Group meeting being held in Mocrosoft's Edinburgh ofice on Thursday the 8th of October. Full details can be found on the UK User Group site. My talk will focus on some of the great tools we have for collecting and analysing SQL Server performance data. Martin Bell MVP will also be talking about using Powershell with SQL Server, which should be excellent as always.

Hope to see you there !

Troubleshooting SQL Server 2008 Performance Problems

Rob_Carrol — Sun, 12 Apr 2009 23:26:00 GMT

A new whitepaper covering the topic of performance troubleshooting in SQL Server 2008 has now been released. You can download the paper from the following link:

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

SQL Server 2005 Multi-Site Clustering with Windows Server 2008

Rob_Carrol — Sun, 15 Mar 2009 22:12:00 GMT

I was working recently with a customer who was looking to deploy a SQL Server 2005 cluster across 2 geographically dispersed sites using Windows Server 2008. They were looking to utilise the new clustering improvements in Windows Server 2008 to build a highly available SQL Server solution. The customer required automatic failover between the sites in the event of a disaster, but their current solution required manual intervention by an administrator in order to failover to the disaster recovery site. Automatic failover would increase application availability, and reduce the complexity of the solution. Each site has it's own SAN storage and the customer planned to replicate data between each site using SRDF replication.

This led me to do further research into clustering SQL Server in this type of environment. Windows Server 2008 introduces greater flexibility in the choice of Quorum configuration. The concept of quorum moves away from the requirement of a shared storage resource, but now refers to the number of votes needed to establish a majority. All nodes and a witness resource can get a vote, which removes the disk as the single point of failure as in previous clustering models. The 4 Quorum Models available are:

Node and Disk Majority
Disk Only
Node Majority
Node and File Share Majority

As there is no shared storage between the nodes in a multi-site cluster, 2 of these Quorum models are suitable for multi-site clustering: Node Majority and Node and File Share Majority. Node and Disk Majority and Disk Only should only be used in a multi-site cluster if specifically directed by your storage vendor as your disk replication software needs to support these configurations.

Node and File Share Majority:

This allows the creation of up to 16 nodes with no shared disks. A file share acts as a witness, meaning that a 2 node cluster will have have 3 votes, so connectivity can be lost by either one of the nodes or the witness and the cluster can still continue to function.

A cluster quorum configured to use a node-and-file-share majority is a great solution for multi-site clusters. The file share witness can reside at a third site independent of either site hosting a cluster node for high disaster resilience. A single file server can serve as a witness to multiple clusters (with each cluster using a separate file share witness on the file server).

This configuration gives the highest resilience as the cluster can automatically recover from the loss of any one site without manual intervention.

The File Share Witness (FSW) needs to be in the same forest as the nodes and be running Windows Server 2003 or Windows Server 2008. For maximum resilience, it is best to locate the FSW at a 3rd site separate from the cluster nodes. The FSW does not need to be attached to shared storage and should NOT be a node in the same cluster.

Node Majority:

A node-majority cluster consists of 3 or more nodes without shared storage. Each of the nodes has a vote and there is no shared disk vote. A majority of votes are necessary to operate the cluster if 2 nodes fail in a 3 node cluster, then the remaining node drops out of the cluster. An administrator can manually over-ride this and force the remaining node to start. When the other nodes come back, majority quorum is achieved again and the cluster comes back online seamlessly.

This configuration works best with an odd number of cluster nodes as it is not enough to have half the cluster nodes functioning in this model. If four nodes were set up in a node-majority configuration, the cluster would continue to operate with the loss of one node but not with the loss of two nodes. You should use an odd number of nodes with Node Majority as 4 nodes can only survive 1 failure, which is the same as 3 nodes.

The node-majority quorum configuration can work when there is more than one cluster node at each site. Consider a multi-site cluster consisting of five nodes, three of which reside at Site A and the remaining two at Site B. With a break in connectivity between the two sites, Site A can still communicate with three nodes (which is greater than 50 percent of the total), so all of the nodes at Site A stay up. The nodes in Site B are able to communicate with each other, but no one else. Since the two nodes at Site B cannot communicate with the majority, they drop out of cluster membership. (Were Site A is to go down in this case, in order to bring up the cluster at Site B, it would require manual intervention to override the non-majority.)

As a result, the Node Majority configuration does not give automatic failover between sites as nodes 4 and 5 cannot achieve quorum. In this situation, you would need to manually force a failover.

SQL Server Networking Considerations:

Windows Server 2008 now allows nodes in the same cluster to reside in different network subnets and communicate across network routers. However, be aware that SQL Server 2005 and 2008 still require all cluster nodes to reside in the same network subnet, so you will still need to set up virtual local area networks (VLANs) to connect geographically separated cluster nodes. This can have some benefits with regard to client response times though, as DNS replication may impact client re-connection times in the event of a failover from one site to another. VLAN's allow DNS names to stay the same, so can increase availability.

Storage Considerations:

As there is no shared storage between the nodes in a multi-site cluster, the main consideration is how to keep the data replicated between the sites. The choice of 3rd-party replication solution is important and can have a major effect on how you deploy your cluster. As such, you should work closely with your storage vendor from an early stage in the design process.

Synchronous replication results in no data loss, but requires shorter distances between nodes and higher bandwidth to avoid write latency from impacting performance. Asynchronous allows you to stretch cluster nodes across longer distances, however there is a potential for data loss in the event of a failure. Asynchronous data replication also assumes a large enough network bandwidth to keep up with data changes and does not significantly impact application performance.

Conclusion:

As data replication is key in a multi-site SQL Server cluster, work with your storage vendor from an early stage to ensure they support your cluster configuration. Multi-site clustering allows you to achieve high availability and disaster recovery, however it can be a costly and complex solution. You should evaluate your business requirements first and then decide on the best technology to meet these. It could be the case that Database Mirroring, for example, could be used to give you the required level of resilience across geographical sites.

In this case, the customer chose to implement a 2-node, 3-site solution using the Node and File Share Majority quorum model, with a File Share Witness located in the 3rd site. This gives site-level resilience in the event of a disaster and also allows automatic failover between the cluster nodes without having to re-write client applications, meeting the business requirements.

Additional Resources:

Windows Server 2008 Multi-Site Clustering Whitepaper

TechNet Webcast: Geographically Dispersed Failover Clustering in Windows Server 2008

TechNet Webcast: Failover Clustering and Quorum in Windows Server 2008 Enterprise

Support Webcast: Microsoft SQL Server 2005 Failover Clustering on Windows Server 2008

How to: Create a New SQL Server 2005 Failover Cluster (Setup)

Performance Dashboard Reports for SQL Server 2008

Rob_Carrol — Wed, 18 Feb 2009 17:20:00 GMT

During a recent visit to Seattle for a Microsoft conference, I learned from my colleague Michael Thomassy that it's possible to run the SQL Server 2005 Performance Dashboard reports on SQL Server 2008, with a slight modification. There is a great new feature in SQL Server 2008 called Performance Data Collection, which I have blogged about in the past, and this is excellent for tracking SQL Server performance over time across your 2008 estate. There is also the excellent revamped Activity Monitor in SQL 2008. However, if you want to continue to use the Performance Dashboard reports, which many DBA's have found invaluable, they are not supported in SQL Server 2008. If you try to install the Performance Dashboard reports, you get the following error:

Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6
Invalid column name 'cpu_ticks_in_ms'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_Main_GetCPUHistory', because it does not exist or you do not have permission.

The reason for this is due to a change in the sys.dm_os_sys_info DMV from SQL Server 2005 to 2008 (the cpu_ticks_in_ms column has been removed in 2008 http://msdn.microsoft.com/en-us/library/ms175048.aspx). Download and install the performance dashboard reports as normal (but save the files in the Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard directory) and then modify the setup.sql file as shown below before running it against your SQL Server 2008 instance.

Please note that SQL Server 2008 has introduced new wait types that the Performance Dashboard reports currently don't handle. I would strongly recommend using the new Management Data Warehouse reports in SQL Server 2008 in order to get the best user experience. However, this workaround will help you get the Performance Dashboard Reports up and running on your SQL Server 2008 instances (see the screenshot below).

Scottish SQL Server User Group Meeting - 29th January

Rob_Carrol — Wed, 28 Jan 2009 00:41:00 GMT

I have uploaded my slides from my Upgrading to SQL Server 2008 session at the recent Scottish SQL Server User Group meeting held in Edinburgh on the 29th January. It was good to see a number of new faces at the meeting, and if you fancy coming along in the future, register for updates at the url below:

http://www.sqlserverfaq.com/

If you have any other questions, please drop me an email.

CU #3 for SQL Server 2008 Released

Rob_Carrol — Tue, 20 Jan 2009 09:39:00 GMT

On January 19, 2009 Microsoft shipped the Third Cumulative Update for SQL Server 2008 RTM.

This CU represents 36 Resolved Issues and 30 Unique Customer Requests. More details and download details can be found here:

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

SQL Server 2008 Upgrade Technical Reference Guide Released

Rob_Carrol — Wed, 26 Nov 2008 13:11:00 GMT

The SQL Server 2008 Technical Reference Guide has just been released, covering the essential steps and best practices to upgrade existing 2000 and 2005 instances to 2008. There is also a list of other upgrade resources available at http://msdn.microsoft.com/en-us/library/cc936623.aspx

These guides are intended to suppliment the information already available in SQL Server 2008 Books Online.

SQL Server 2008 Cumulative Update #2 Released

Rob_Carrol — Tue, 18 Nov 2008 14:09:00 GMT

Cumulative Update package 2 has now been released for SQL Server 2008. See the following KB article for full details:

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

Scottish Area SQL Server User Group

Rob_Carrol — Fri, 07 Nov 2008 02:53:00 GMT

I'm happy to announce that the next Scottish SQL Server User Group will be hosted at Microsoft Edinburgh on the evening of the 13th November. Full details of the agenda and how to register can be found on the UK SQL Server User Group site here:

http://sqlserverfaq.com/events/144/Scottish-Area-SQL-Server-User-Group-Jammin-SQL-Server-2008.aspx

The theme of the meeting is "Jammin' with SQL Server 2008", so I'm looking forward to some freestyle SQL Server 2008 demos and discussions !

Weird Problem with Virtual PC 2007

Rob_Carrol — Wed, 16 Jul 2008 18:54:00 GMT

Had a weird problem with Virtual PC 2007 where the blue screen would flash up when I ran the program, yet the console window would not appear even though Virtual PC was running in the task manager and the system tray. The Virtual PC console had effectively disappeared. All this was bad enough, but this happened to me when I was visiting a customer site and just about to demo some cool new features of SQL Server 2008 ! I had even checked my demos before setting out that morning, so this was very weird (and not to mention embarrassing) ! After uninstalling and reinstalling and scratching my head for a while, I came across the solution here...

http://www.essjae.com/blog/default.asp?id=43

SQL Server 2008 - Performance Studio

Rob_Carrol — Fri, 20 Jun 2008 19:50:00 GMT

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. If you do want to create a custom Profiler collection, set up a trace in Profiler then select the "Save as Trace Collection" option. This will then script out the XML trace definition for you which can then be executed against your SQL Server system... simple as that !

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

Indexing Strategies

Rob_Carrol — Tue, 17 Jun 2008 10:02:00 GMT

I attended a SQL Server User Group meeting earlier this year and heard a presentation about favourite DMV's. Mentioned in the discussion was sys.dm_db_index_usage_stats and that got me thinking about indexing strategies. I have been involved in performance troubleshooting databases that have used a variety of indexing stategies, ranging from none to lots of narrow indexes on practically every column ! However, there is no right and wrong indexing strategy, it depends entirely on your application and the type and frequency of queries being executed against the database.

So how do you know if you have a problem? For me, it's usually when users tell me that the application is "running slow" or they get timeouts. However, poor perfomance can be open to interpretation. There could be a whole host of factors to take into account when you are dealing with web applications, such as network connections and web servers problems. However, this shows that you need to have good benchmarks in place in order to compare performance over a period of time. Another sign could be high CPU, high memory usage or increased disk IO activity. The SQLCAT team has a post detailing the top OLTP performance issues on 2005 and some basic performance counters to monitor can be found here.

So once you've determined that there is a problem, what do you do? If you know the specific query that is causing the issue, then you can verify the query execution plan. You do this by chosing the 'Display Estimated Execution Plan' option under the 'Query' menu in Management Studio. This does not execute the query, but returns the estimated execution plan based on the statistics the server has. As a result, you need to ensure that the statistics are up to date, or you may get the wrong results. It's also a good idea to turn on statistics IO. Things to look out for in the execution plan are table or index scans and hash aggregates. Scans imply that there are no indexes for SQL Server to use or the indexes are not selective enough and SQL Server has decided it's less expensive to run a scan. Bear in mind that a clustered index scan is exactly the same as a table scan. Hash aggregates have to create a worktable i.e. a temp table in TempDB. Watch out for Hash aggregates as Statistics IO does not show the cost of the worktables, which can often be very expensive. As a rule of thumb, anytime you see "Hash" in your plan it means temp tables and this can be done better ! Another cool thing about showplan is that you can force the queries to use different indexes and run them side by side. This will show the execution plan for both queries and the cost of each relative to the batch. This is a quick way to see which index choices are most expensive.

However, on shared systems with multiple applications running against the SQL Server instance, chances are you will not know the queries that are causing the performance issues and you will need to do some digging. You can use DMV's in 2005, but as I work in a mixed environment (2000 and 2005) I prefer to use profiler to give me an idea what is going on. I run a server side trace and remotely log the results to a file as oppossed to a SQL Server table to minimise overhead on the system. I will typically run this for short periods (10 - 15 mins) just to get a feel for the queries executing against the server. This is a quick way to see if there are any expensive queries running and how frequently. This gives me some clues as to which database is causing the problems. Once I know this, I can really focus in on that database application. More information on running Profiler traces is available here.

Once I have identified the database, the hardest part is deciding which queries to index. Lots of frequently run queries can give you a far bigger overall performance gain than a large query run once a month. It is important to use Profiler and run traces over longer periods to get a feel for the mix of reads and writes. Never build an index in isolation, always consider the workload over the course of time. You can then combine these traces with DTA in order to fully analyse your workload and come up with the proper indexing strategy based on your application's specific needs. It is also worth restoring a backup of your database on a test system and use this for running your DTA analysis on. I cannot stress highly enough, do not create indexes for indexes sake. I often ask the question when interviewing, "when would droping an index actually help to increase performance"? A lot of people are conditioned to believe that you need to build lots of indexes to increase performance, but this is not true. If you have lots of inserts, updates and deletes then this is a significant overhead to keep all these indexes updated if they are not required in the first place.

Before going off and building indexes it is worth looking at alternative options. It is a good idea to update your statistics to ensure that SQL Server has most up to date information to work out the optimal execution plan. If you are working with stored procedures, you can consider recompiling them. You can also consider re-writting the code, especially if you are using cursors!

Generally, SQL Server does better with wider indexes (covering several columns) and fewer of them than it does with narrower ones. Narrow indexes will require a bookmark lookup to get the rest of the data if you don't cover the query. Bookmark lookups are expensive and SQL Server may even decide not to use the index at all and perform a table scan. In this situation, the narrow indexes will not be used and are an unecesary overhead. You can use the DMV sys.dm_db_missing_index_details to see which multi-column indexes could give better performance. Another word of warning here if you are using the 2005 DMV's, such as sys.dm_db_index_usage_stats. Remember that the data in these DMV's is not persisted across server shutdowns, or database restores. Do not assume that because the DMVs show that an index has not been used you can safely drop it. This only means that it hasn't been used since the cache was cleared out. If you are going to rely on the DMV's, you need to persist the data over a period of time and then analyse it. This is easy to do as you can select directly from the DMV's into tables, which can then persist the data. A good time period would be to persist the data every 30 mins. Paul Randal has a post here with information how to persist the index usage data from DMVs.

The final thing I'd like to say about indexing (for the time being !) is that you can't just create indexes and walk away. You need to regularly maintain them in order to reduce fragmentation. Fragmentation in indexes can be caused by inserts, updates and deletes and can be a problem if you have a busy system. By default, indexes are created with 100% fill factor, which means they are densely packed as soon as you create them. If you then need to insert rows or update data, SQL Server will need to carry out page splits in order to do so. To avoid this happening, it is advisable to create indexes with a fill factor of around 80 - 90%, however this may vary depending on the frequency of data updates. Not only will this increase the performance of inserts and updates, it will also keep fragmentation at a minimum. You can check for fragementation using DBCC SHOWCONTIG (2000) and sys.dm_db_index_physical_stats (2005) and remove fragmentation using DBCC INDEXDEFRAG or DBCC DBREINDEX in 2000, and ALTER INDEX REORGANIZE or REBUILD in 2005. There is further information and advce here for rebuilding indexes and updating statistics.

This is a huge topic and I could go on all day, but the importance of good indexing cannot be stressed highly enough ! In summary, you need to ensure that you understand your application's workload, you need to check that the indexes you have are actually being used and that you are not missing any indexes. Finally, you need to maintain your indexes so they are kept in optimal shape. Good indexing means good performance which means happy users !

[This was originally posted on http://sqlblogcasts.com/ in February 2008]

SQL Server 2005 Performance Dashboard

Rob_Carrol — Thu, 12 Jun 2008 14:25:00 GMT

I've had one or two requests recently to set up the Performance Dashboard reports on SQL Server so I've created this blog post to step through the process. Firstly, the Performance Dashboard was created to allow customers and support engineers to monitor the general performance characteristics of a server and investigate the cause of any performance problems that may occur. The goal of the Performance Dashboard is to reduce the time spent discovering the problem so effort can be focused on actually resolving it.

The SQL Server 2005 Performance Dashboard Reports are available for download here. The Performance Dashboard reports are built on the SQL Server 2005 dynamic management views, accessing data that is already captured by SQL Server 2005. Consequently there is no performance impact of using the dashboard except when you actually open/refresh a report. This allows engineers and customers to go much further on a performance problem while the problem is happening. The Performance Dashboard requires SQL Server 2005 SP2 or later.

Install the Dashboard by running the msi from the link above, which will install to a default location of Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard. After setup finishes, open Management Studio and connect to the server and run the SETUP.SQL script (once for each SQL instance you want to monitor). Then from Object Explorer select the server, right mouse click and choose Reports – Custom Reports and browse to find the PERFORMANCE_DASHBOARD_MAIN.RDL file. This report is the only report intended to be directly loaded from SSMS; all other reports are accessed as a drill through off of the main report. The help file, PERFDASH.CHM has details about setup and permission requirements, how each report is accessed, details about the general methodology used in the dashboard and how you can use/interpret the information on each individual report.

SQL Server 2008 RC0 Available

Rob_Carrol — Tue, 10 Jun 2008 11:26:00 GMT

The release of SQL Server 2008 has hit another milestone with the news that Release Candidate 0 (RC0) has been made available for public download this morning. This was initially released to Technet Plus and MSDN subscribers last Friday. It is expected that this will be the final release prior to SQL Server 2008 RTM in Q3 this calendar year. This release includes support for upgrading failover clusters from previous versions of SQL Server, as well as several bug fixes and minor improvements that have been made in response to the customer feedback Microsoft has received from previous CTPs.

Interest and excitement is continuing to build for the release of 2008, with 100,000 people downloading the last CTP (CTP6 Feb), and 250,000 total downloads of SQL Server 2008 CTP's. I am also seeing huge amounts of interest from customers who are looking to upgrade straight to 2008 to get the full benefit of features such as Policy Based Management, Resource Governor, Filestream data, Business Intelligence and Compression.

OpsDB SQL Server Automation Tools Released

Rob_Carrol — Wed, 04 Jun 2008 02:16:00 GMT

A great set of SQL Server automation tools has just been released by Microsoft onto the SQL Server Community Worldwide site...

http://sqlcommunity.com/ScriptsTools/OpsDBOperationsDatabaseforSQLServer/tabid/275/language/en-US/Default.aspx

These are excellent resources to add to your DBA toolset.