Reporting Services Scale-Out Setup with Kerberos Delegation

A common configuration for SQL Reporting Services is to use a scale-out setup. The reason for this is the performance of the rendering (or pagination) of the reports is relatively processor intensive (at the time of this article SQL Server 2005) Along with this setup comes an intrinsic problem. By default, SQL Reporting Services uses integrated authentication with impersonation. In other words, SQL Reporting services uses an authentication scheme that integrates with Active Directory in order to provide access and administrative priveliges. When Reporting Services is installed on the same machine as the database engine, this does not pose any issues. However with a scale-out setup, it does. This obstacle is known as the “double-hop” issue. Why? Because user credentials cannot be passed from one machine to another without a setting up Kerberos Delegation. In this article we will go over how to accomplish this setup and the typical obstacles you may need to overcome. There are a few prerequisites to setting up Kerberos delegation. All computers accessing the application must be in the same domain. The time of all computers must be synchronized using the time service. Kerberos ports must be open if going through a firewall. Client browsers must be setup to allow integrated authentication. Clients must be domain users. All clients must be running Windows 2000 or greater. All client’s browsers must be IE 5+ Functional level of the domain is set to Windows 2003 (highly preferable but not required) Physical Layout Depending on your infrastructure design, […]

Continue reading ...

Find User Connection Count

[cc lang=”sql”] — Show users with highest connections SELECT login_name, session_count, last_batch_time FROM( SELECT login_name ,COUNT(session_id) AS session_count, MAX(last_request_end_time) AS last_batch_time FROM sys.dm_exec_sessions GROUP BY login_name ) t ORDER BY session_count DESC [/cc] In SQL Server 2008 you can also find out how many connections have been created to SQL Server since the last time it got restarted: [cc lang=”sql”] SELECT @@Connections [/cc]

Continue reading ...

Finding the total virtual, shared and AWE memory used

This query shows: Amount of memory allocated to the buffer pool Amount of memory consumed by BPool Amount of memory used by AWE [cc lang=”sql”] SELECT SUM(single_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] FROM sys.dm_os_memory_clerks WHERE type = ‘MEMORYCLERK_SQLBUFFERPOOL’ [/cc]

Continue reading ...

Transferring Large Amounts of Data using Batch Inserts

Below is a technique used to transfer a large amount of records from one table to another. This scales pretty well for a couple reasons. First, this will not fill up the entire log prior to committing the transaction. Rather, it will populate the table in chunks of 10,000 records. Second, it’s generally much quicker. You will have to play around with the batch size. Sometimes it’s more efficient at 10,000, sometimes 500,000, depending on the system. If you do not need to insert into an existing table and just need a copy of the table, it is better to do a SELECT INTO. However for this example, we are inserting into an existing table. Another trick you should do is to change the recovery model of the database to simple. This way, there will be much less logging in the transaction log. The WITH (TABLOCK) below only works in SQL 2008. [cc lang=”sql”] DECLARE @BatchSize int = 10000 WHILE 1 = 1 BEGIN INSERT INTO [dbo].[Destination] –WITH (TABLOCK) — Uncomment for 2008 ( FirstName ,LastName ,EmailAddress ,PhoneNumber ) SELECT TOP(@BatchSize) s.FirstName ,s.LastName ,s.EmailAddress ,s.PhoneNumber FROM [dbo].[Source] s WHERE NOT EXISTS ( SELECT 1 FROM dbo.Destination WHERE PersonID = s.PersonID ) IF @@ROWCOUNT < @BatchSize BREAK END [/cc] With the above example, it is important to have at least a non clustered index on PersonID in both tables. Another way to transfer records is to use multiple threads. Specifying a range of records as such: [cc lang="sql"] INSERT INTO [dbo].[Destination] […]

Continue reading ...

SQL Server Pagination using CTE and RowNumber

This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) select * FROM SQLPaging where ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!