|
|
-
I am at the beginning of touring user groups around the UK so on train journeys back from a user group meetings I have had time to write out my thoughts on some of the new features that are available in SQL Server CTP 1.
The vision from Microsoft regarding this release looks the same as it was in SQL Server 2008 R2
Empowering IT – Making it easier for IT departments to manage the scalability and availability of SQL Server
Dynamic Development - Making it easier for database developers to develop SQL Server databases in a consistent application
Pervasive Insight - extending the reach of BI to include business users as well as IT professionals and developers
What is notable is the fact that Microsoft will scale SQL Server to different platforms including
Appliances - SQL Server editions including Parallel Data Warehouse and Fast track that have a dedicated purpose using SQL Server
Box - the traditional implementation that most will be familiar with running a specific edition of SQL Server on a server.
Cloud - SQL Azure, a cloud-based service offering data storage capabilities
The capabilities that are available on each platform will vary, in this blog I will concentrate on the new features that will be available on the box with the release of SQL Server Denali CTP ( Community Technical Preview) 1 available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9
In no particular order, the following are a summary on some features of SQL Server Denali CTP 1
HADRON
One features that addresses the scalability and availability vision is HADRON or High Availability Disaster Recovery Always ON. This is a technology that tightly integrates the database mirroring architecture with Windows Clustering. The key benefit that this has over database mirroring is that up to 5 secondary servers can be defined, which are readable and can be queried. Like database mirroring, endpoints are used to connect between primary /secondary instances like in Database mirroring. Furthermore as the architecture is very similar to database mirroring, Database Mirroring configuration from SQL Server 2005 onwards can be upgraded to HADR.
CREATE SERVER ROLE
You now have the ability to create user defined server level role on an instance of SQL Server. This provides a greater degree of granularity when setting security at an instance level. As a result the sp_addsrvrolemember and sp_dropsrvrolemember are being deprecated and being replaced with the ALTER SERVER ROLE instead. Server roles can be created using Transact-SQL or within SQL Server Management Studio.
Startup Options
You can now graphically configure SQL Server startup options within the instance properties in SQL Server Configuration Manager
Contained databases
Contained databases are databases that contain all the objects and metadata for a database without having dependencies on the SQL Server instance. This makes it easier to move databases between different instances of SQL Server. Contained databases can be defined in one of three configurations:
NONE – This means that the database does have dependencies on the SQL Server instance. This is how all databases created on a SQL Server instance are defined by default
FULL – As yet not available in Denali CTP 1, my understanding is that objects defined in these databases do not have dependencies on the SQL Server Instance
PARTIAL – Some objects have dependencies on the SQL Server instance, other objects are contained within the database
Transact-SQL additions and enhancements
There are a number of T-SQL additions and enhancements in this release of SQL Server including Paging, SEQUENCE and THROW
Paging
The ORDER BY clause has been enhanced to include the ability to page results. Example from Books Online include:
USE AdventureWorks2008R2;
GO
--Using Order by to return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;
-- Use OFFSET to skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;
-- Use OFFSET to skip 0 rows and return only the first 10 rows from the sorted result set using FETCH --NEXT.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
-- OFFSET and FETCH values can be defined within variables
DECLARE @StartingRowNumber tinyint = 1 ,
@FetchRows tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC OFFSET @StartingRowNumber ROWS FETCH NEXT @FetchRows ROWS ONLY;
GO
SEQUENCE
A feature available to ORACLE database professionals is now available to SQL Server database professionals. SEQUENCE enables you to define a sequence of numbers which is stored and retained in memory. My good friend Aaron Bertrand has two excellent blog pieces on how to implement SEQUENCE and the performance impact that I would recommend:
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspxhttp://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/13/sql-server-v-next-denali-take-one-part-sequence-and-one-part-set.aspxTHROW
You now have greater flexibility with the TRY…CATCH Transact SQL statements with THROW being introduced in SQL Server Denali. This raises an exception and then transfers execution to a CATCH block.
SSIS enhancements
I will be blogging more on these changes in the future however to areas stand out regarding SSIS which will make life a lot easier
Usability
The usability of SSIS has been improved within Business Intelligence Development Studio. The ability to undo and redo changes in the designer is a small change, but of massive benefit while developing SSIS packages. Copy objects between packages is a lot easier and you can customise the toolbox more easily.
Deployment
There is a new project deployment methodology that will deploy SSIS at a project level rather than the package level that we have been previously used to. The package method of deployment still exists and is referred to as the legacy deployment model. The following tables is taken form Technet Wiki
http://social.technet.microsoft.com/wiki/contents/articles/project-deployment-overview-in-sql-server-quot-denali-quot-ctp1-ssis.aspx
that shows the differences and similarity between the project deployment methodology and the legacy deployment methodology
| When Using the Project Deployment Model... |
When Using the Legacy Deployment Model... |
| A project is the unit of deployment. |
A package is the unit of deployment. |
| Parameters are used to assign values to package properties. |
Configurations are used to assign values to package properties. |
| A project, containing packages and parameters, is built to a project deployment file (.ispac extension). |
Packages (.dtsx extension) and configurations (.dtsConfig extension) are saved individually to the file system. |
| A project, containing packages and parameters, is deployed to the Integration Services catalog on an instance of SQL Server. |
Packages and configurations are copied to the file system on another computer. Packages can also be saved to the MSDB database on an instance of SQL Server. |
| CLR integration is required on the database engine. |
CLR integration is not required on the database engine. |
| Environment-specific parameter values are stored in environment variables. |
Environment-specific configuration values are stored in configuration files. |
| Projects and packages in the catalog can be validated on the server before execution. You can use SQL Server Management Studio, stored procedures, or managed code to perform the validation. |
Packages are validated just before execution. You can also validate a package with dtExec or managed code. |
| Packages are executed by starting an execution on the database engine. A project identifier, explicit parameter values (optional), and environment references (optional) are assigned to an execution before it is started. |
Packages are executed with the dtExec and DTExecUI execution utilities. Applicable configurations are identified by command-prompt arguments (optional). |
| During exeuction, events that are produced by the package are captured automatically and saved to the catalog. You can query these events with Transact-SQL views. |
During execution, events that are produced by a package are not captured automatically. A log provider must be added to the package to capture events. |
| Packages are run in a separate Windows process. |
Packages are run in a separate Windows process. |
| SQL Server Agent is used to schedule package execution. |
SQL Server Agent is used to schedule package execution. |
BISM
BISM or Business Intelligence Semantic Model is one of the major additions of SQL Server Denali that will change the way we potentially work with Business Intelligence. The most common misconception of BISM is that people think that it is specific to Analysis Services. It is not, it is hosted by SQL Server Analysis Service, however Crescent (the new adhoc reporting tool complementing SSRS) must have a BISM model in order to work. A second misconception is that the introduction of BISM marks the end of traditional Analysis Services solutions that uses UDM. Again this is not the case. UDM will still play an important role that can be summarised by the following graphic which I do not know who created but I believe came from a TechED session
The BISM is designed to combine the UDM model used by SSAS with the SMDL model used by previous Report Builder applications, representing the model in a relational manner while getting the BI sophistication from UDM. However BISM will evolve over time and as UDM is a more mature model you will still see the relevance of UDM within organisations for years to come. Specifically if you require advance calculation that cannot be currently handled by DAX, (the new querying language for PowerPivot that extends to BISM). Then you will be likely to use UDM. BISM is designed to store the data in the VertiPaq in-memory column store. VertiPaq uses compression algorithms along with multi-threaded query processing that delivers fast performance in retrieving huge data volumes. So much so that this technology is also being introduced in the Database Engine in SQL Server Denali with the new feature of columnar indexes - this is Vetipaq again. So if you have data that cannot fit into the memory, then UDM becomes a consideration. I say that as Vertipaq can be configured to query against data sources therefore preserving the memory that vertipaq uses, but that is for another blog.
To quote the SSAS team from the following blog http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx
“You might ask – Why do we have two types of models in Analysis Services? Which one should I use? Is the UDM going to be deprecated now that we have the BISM? The answer is NO! Analysis Services just got a lot better in SQL Server “Denali” with the BISM and VertiPaq and DAX! However the UDM is a mature and industry leading technology and is here to stay. Let me repeat – UDM (OLAP) models are not being deprecated!”
So there are exciting times in this area and I watch and partake with interest as it develops through time
Crescent
In February 2010, I was sat in a room with two other MVPs (Microsoft Valuable Professionals) in Redmond as 5 members of the SSRS team wanted to show us the future of SSRS adhoc reporting . While the demonstration was being given, the MVPs where like kids in a sweetie shop. I don’t mind admitting I was drooling, and it has been very difficult to keep quiet about it since then. Like BISM, Project Crescent is designed to complement the existing report designer in Business Intelligence Development Studio and Report Builder applications that is provided by SQL Server Reporting Services. It relies on a BISM model to work, but its compelling selling point is that if present reports in a Silverlight front end browser that is extremely dynamic. For example, rather than looking at a static chart, the chart can be replayed like a DVD over time to see the result grow within the chart. This is certainly a very useful tool that can be stored in a SharePoint gallery, and while it may be touted as an adhoc reporting tool, I envisage BI professionals using it to create some standard reports that are currently created in Business Intelligence Development Studio. Check out http://www.youtube.com/watch?v=FfRpfCav9hg for your first taster…its awesome!
So there are some of my thoughts on what is available in SQL Server Denali CTP1, I know that I have missed out some feature but I hope that future blogs will give you more information about specific topics
Thanks
Chris
|
-
Hi
I will be speaking at the UK DevConnections on Analysis Services at the ExCeL conference centre in London on 13th-15th June 2011. Join top SQL Server names such as Paul Randall, Kimberley Tripp, Simon Sabin and Allan Mitchell ( to name a few), at the IT & DevConnections powered by Microsoft UK in London on 13th-15th June 2011.
- With UK DevConnections you can combine SQL Sessions with other Microsoft technology stacks. Microsoft and leading independent industry presenters will deliver in-depth presentations and cutting edge sessions on
- SharePoint
- Windows
- Exchange and Unified Communications
- SQL Server
- Silverlight
- ASP.Net
- Virtualisation
- Cloud and Azure
As a speaker I have 3 discount codes that entitles you to attend for £100 + VAT on the 14th and 15th June. Thats two days for £120. Email Chris.TestaONeill@EMC.com with the subject of UK Devconnections. The first three I receive will get the code
Furthermore, there are preferential hotel rates for this event at:
https://logicalvenues.vbookings.co.uk/b/pentonlondon0611/
So if you want to attend a conference with a wide spectrum of technologies, then DevConnections may be up your street
Thanks
Chris
|
-
Before installing SQL Server Denali CTP1, there are a number of pre-installation checks that should be considered before installing Denali. At the moment the Denali CTP (Community Technical Preview) version that is available to the public is SQL Server Denali CTP 1 and can be downloaded from here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9
There are a number of different editions (versions) of SQL Server Denali that is available; each edition provides different features and maximum hardware support. SQL Server Datacenter and SQL Server Evaluation are the only SQL Server editions enabled on this CTP. The information provided here is for DataCenter edition, which is the highest edition available to SQL Server.The considerations are broadly broken down into two areas; the hardware required running SQL Server Denali and the software. From a hardware perspective the following is the minimum hardware requirements to install SQL Server Denali CTP 1.
Memory
- Minimum:512 MB
- Recommended: 2.048 GB or more
- Maximum: Operating system maximum
CPU
- Minimum: 1.4 GHz
- Recommended: 2.0 GHz or faster
Hard Disk Space
| Feature |
Disk space requirement |
| Database Engine and data files, Replication, and Full-Text Search |
711 MB |
| Analysis Services and data files |
345 MB |
| Reporting Services and Report Manager |
304 MB |
| Integration Services |
591 MB |
| Client Components (Other than SQL Server Books Online components and Integration Services tools. |
1823 MB |
| SQL Server Books Online Components to view and manage help content1 |
375 KB | Note that these minimum requirements do not account for the system resources that you will require to run the operating system.
I have to add at this point that I have a number of installations of SQL Server Denali CTP 1 on my laptop. I use a 64 bit Toshiba Satellite L550-D that is triple booted to Windows 7, Windows 2008 and Windows 2008 R2. This laptop has an AMD Athlon II Dual Core M300 processor with 4GB of memory. In all instances I have installed Denali into a virtualised environment running Windows Server 2008 R2. In my Windows 2008 partition Denali is installed within Hyper-V. In Windows 7, I have used Virtual PC 2007 with Denali installed within it. (purely for convenience so I do not have to reboot into the windows 2008 partition to play J). As with any CTP version of software, I always advise that you should install it within a virtualised instance so if any problems arise, it should not affect your host operating system or installed applications. The software requirement can include the following operating systems:
- Windows 7; Windows Server 2008 R2; Windows Server 2008 Service Pack 2; Windows Vista Service Pack 2
- Microsoft Internet Explorer 7 is also required
In my excitement to perform the installation a message appears that informs you that you have to download the following to files:
Windows6.0-KB948465-X86 which can be downloaded here http://www.microsoft.com/downloads/en/details.aspx?familyid=a4dd31d5-f907-4406-9012-a5c3199ea2b3&displaylang=en
and Windows6.0-KB956250-x86 which can be downloaded here http://www.microsoft.com/downloads/en/details.aspx?FamilyID=74a30045-74b5-4165-8917-b65e5ebd8902&displaylang=en
What I did find is that upon installing these updates it took a couple of reboots before the installation could be performed. There is a better way that can help you to avoid strife. When you start the setup the SQL Server Installation Centre is presented.
![]() 
Use the System Configuration Checker (underlined) to confirm whether you have the hardware and software available to perform a successful installation of SQL Server, By clicking on the System Configuration Checker link. This will return the following report.
You can click on the view detailed report link to provide more information.
So with that in mind you can quickly check if your machine has the capabilities to run SQL Server Denali CTP 1 before you install it.
Chris
|
-
-
Over a number of months I have had customers and members of the SQL Server community express concerns over the new release of SQL Server 2008 R2 Enterprise Edition. They have been given the impression that with the new release they would only be entitled to 1 instance per processor license due the release of two new editions in SQL Server 2008 R2 known as Data Centre Edition and Parallel Data Warehouse Edition
Being at the MVP Summit last week gave me the opportunity to speak directly with the SQL Server team who led me to a resource that confirms that SQL Server 2008 R2 Enterprise Edition will still allow multiple instance of SQL Server. Specifically 25 instance per processor license. While this is lower that the 50 instance that are currently allowed in SQL Server 2008, I know that this news will certainly ease the concerns that some of you, who have asked this question.
You also have the ability to run 4 vurtual machines on this edition.
The link for this information can be found here
http://download.microsoft.com/download/7/B/6/7B65BDD8-9D5E-42A5-A8AA-AD61FD8265E2/SQLServer2008R2EditionsDatasheet.pdf
If you require more instance or virtual machines, then consideration should be given to using Data Center Edition of SQL Server 2008 R2.
More information can be founnd here
http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx
Thanks
Chris
|
-
If the reports that you create for your organisation contains common items, perhaps images such as company logos, or report properties such as language or pagination settings. You can create a report template that can then be called from within Business Intelligence Development Studio (BIDS)
1. Create a Project and Report within BIDS.....make a note of the project location for your BIDS project
2. Add the components to the report that you want to include in the report template, then save the report, close down BIDS
3. In Windows Explorer, browse to the project folder and copy the report
4. Paste the report to the following location
C:\Program Files\Microsoft Visual Studio Version\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProjects
To use the template you have created
1. Create a Project within BIDS
2. Right Click the Report folder, point to Add and then click on New item
3. Within the Add New Item dialogue box, your report template appears, select the template and define a name in the name text
You report template is added to the report with the objects presented that you added when you created the report
|
-
A recent customer engagement required a setup of a monitoring solution for SSAS, due to the time restrictions placed upon this, native Windows Performance Monitor (Perfmon) and SQL Server Profiler Monitoring Tools was used as using a third party tool would have meant the customer providing an additional monitoring server that was not available.
I wanted to outline the performance monitoring counters that was used to monitor the system on which SSAS was running. Due to the slow query performance that was occurring during certain scenarios, perfmon was used to establish if any pressure was being placed on the Disk, CPU or Memory subsystem when concurrent connections access the same query, and Profiler to pinpoint how the query was being managed within SSAS, profiler I will leave for another blog.
This guide is not designed to provide a definitive list of what should be used when monitoring SSAS, different situations may require the addition or removal of counters as presented by the situation. However I hope that it serves as a good basis for starting your monitoring of SSAS. I would also like to acknowledge Chris Webb’s awesome chapters from “Expert Cube Development” that also helped shape my monitoring strategy:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!6657.entry
Simulating Connections
To simulate the additional connections to the SSAS server whilst monitoring, I used ascmd to simulate multiple connections to the typical and worse performing queries that were identified by the customer. A similar sript can be downloaded from codeplex at http://www.codeplex.com/SQLSrvAnalysisSrvcs. File name: ASCMD_StressTestingScripts.zip. Performance Monitor
Within performance monitor, a counter log was created that contained the list of counters below. The important point to note when running the counter log is that the RUN AS property within the counter log properties should be changed to an account that has rights to the SSAS instance when monitoring MSAS counters. Failure to do so means that the counter log runs under the system account, no errors or warning are given while running the counter log, and it is not until you need to view the MSAS counters that they will not be displayed if run under the default account that has no right to SSAS. If your connection simulation takes hours, this could prove quite frustrating if not done beforehand J
The counters used……
|
Object |
Counter |
Instance |
Justification |
|
System |
Processor Queue legnth |
N/A |
Indicates how many threads are waiting for execution against the processor. If this counter is consistently higher than around 5 when processor utilization approaches 100%, then this is a good indication that there is more work (active threads) available (ready for execution) than the machine's processors are able to handle. |
|
System |
Context Switches/sec |
N/A |
Measures how frequently the processor has to switch from user- to kernel-mode to handle a request from a thread running in user mode. The heavier the workload running on your machine, the higher this counter will generally be, but over long term the value of this counter should remain fairly constant. If this counter suddenly starts increasing however, it may be an indicating of a malfunctioning device, especially if the Processor\Interrupts/sec\(_Total) counter on your machine shows a similar unexplained increase |
|
Process |
% Processor Time |
sqlservr |
Definately should be used if Processor\% Processor Time\(_Total) is maxing at 100% to assess the effect of the SQL Server process on the processor |
|
Process |
% Processor Time |
msmdsrv |
Definately should be used if Processor\% Processor Time\(_Total) is maxing at 100% to assess the effect of the SQL Server process on the processor |
|
Process |
Working Set |
sqlservr |
If the Memory\Available bytes counter is decreaing this counter can be run to indicate if the process is consuming larger and larger amounts of RAM. Process(instance)\Working Set measures the size of the working set for each process, which indicates the number of allocated pages the process can address without generating a page fault. |
|
Process |
Working Set |
msmdsrv |
If the Memory\Available bytes counter is decreaing this counter can be run to indicate if the process is consuming larger and larger amounts of RAM. Process(instance)\Working Set measures the size of the working set for each process, which indicates the number of allocated pages the process can address without generating a page fault. |
|
Processor |
% Processor Time |
_Total and individual cores |
measures the total utilization of your processor by all running processes. If multi-proc then be mindful only an average is provided |
|
Processor |
% Privileged Time |
_Total |
To see how the OS is handling basic IO requests. If kernel mode utilization is high, your machine is likely underpowered as it's too busy handling basic OS housekeeping functions to be able to effectively run other applications. |
|
Processor |
% User Time |
_Total |
To see how the applications is interacting from a processor perspective, a high percentage utilisation determine that the server is dealing with too many apps and may require increasing thje hardware or scaling out |
|
Processor |
Interrupts/sec |
_Total |
The average rate, in incidents per second, at which the processor received and serviced hardware interrupts. Shoulr be consistant over time but a sudden unexplained increase could indicate a device malfunction which can be confirmed using the System\Context Switches/sec counter |
|
Memory |
Pages/sec |
N/A |
Indicates the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays, this is the primary counter to watch for indication of possible insufficient RAM to meet your server's needs. A good idea here is to configure a perfmon alert that triggers when the number of pages per second exceeds 50 per paging disk on your system. May also want to see the configuration of the page file on the Server |
|
Memory |
Available Mbytes |
N/A |
is the amount of physical memory, in bytes, available to processes running on the computer. if this counter is greater than 10% of the actual RAM in your machine then you probably have more than enough RAM. monitor it regularly to see if any downward trend develops, and set an alert to trigger if it drops below 2% of the installed RAM. |
|
Physical Disk |
Disk Transfers/sec |
for each physical disk |
If it goes above 10 disk I/Os per second then you've got poor response time for your disk. |
|
Physical Disk |
Idle Time |
_total |
If Disk Transfers/sec is above 25 disk I/Os per second use this counter. which measures the percent time that your hard disk is idle during the measurement interval, and if you see this counter fall below 20% then you've likely got read/write requests queuing up for your disk which is unable to service these requests in a timely fashion. |
|
Physical Disk |
Disk queue legnth |
For the OLAP and SQL physical disk |
A value that is consistently less than 2 means that the disk system is handling the IO requests against the physical disk |
|
Network Interface |
Bytes Total/sec |
For the NIC |
Should be monitored over a period of time to see if there is anb increase/decrease in network utilisation |
|
Network Interface |
Current Bandwidth |
For the NIC |
is an estimate of the current bandwidth of the network interface in bits per second (BPS). |
|
MSAS 2005: Memory |
Memory Limit High KB |
N/A |
Shows (as a percentage) the high memory limit configured for SSAS in C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini |
|
MSAS 2005: Memory |
Memory Limit Low KB |
N/A |
Shows (as a percentage) the low memory limit configured for SSAS in C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini |
|
MSAS 2005: Memory |
Memory Usage KB |
N/A |
Displays the memory usage of the server process. |
|
MSAS 2005: Memory |
File Store KB |
N/A |
Displays the amount of memory that is reserved for the Cache. Note if total memory limit in the msmdsrv.ini is set to 0, no memory is reserved for the cache |
|
MSAS 2005: Storage Engine Query |
Queries from Cache Direct / sec |
N/A |
Displays the rate of queries answered from the cache directly |
|
MSAS 2005: Storage Engine Query |
Queries from Cache Filtered / Sec |
N/A |
Displays the Rate of queries answered by filtering existing cache entry. |
|
MSAS 2005: Storage Engine Query |
Queries from File / Sec |
N/A |
Displays the Rate of queries answered from files. |
|
MSAS 2005: Storage Engine Query |
Average time /query |
N/A |
Displays the average time of a query |
|
MSAS 2005: Connection |
Current connections |
N/A |
Displays the number of connections against the SSAS instance |
|
MSAS 2005: Connection |
Requests / sec |
N/A |
Displays the rate of query requests per second |
|
MSAS 2005: Locks |
Current Lock Waits |
N/A |
Displays thhe number of connections waiting on a lock |
|
MSAS 2005: Threads |
Query Pool job queue Length |
N/A |
The number of queries in the job queue |
|
MSAS 2005:Proc Aggregations |
Temp file bytes written/sec |
N/A |
Shows the number of bytes of data processed in a temporary file |
|
MSAS 2005:Proc Aggregations |
Temp file rows written/sec |
N/A |
Shows the number of bytes of data processed in a temporary file |
|
-
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=”MaxActiveReqForOneUser” Value=”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
https://connect.microsoft.com/SQLServer/feedback/details/565575/extending-reporting-services-configuration-manager-rscm
|
|
|
|