Subscriptions To Operational Reports And Options For Scaling Out
Sep 26, 2007
We use timed subscriptions to do almost all of our reporting. Reports are delivered (primarily via e-mail and printer) once they are completed and users don't have to "watch the pot boil" so to speak.
Apparently SSRS has some load balancing capability whereby it lets only a limited number of threads/reports run concurrently. We often reach this max and lock ourselves up on some very long-running reports, causing other important reports to wait a long time.
We've added some operational reports (ie. document prints) to the mix. These reports run off of OLTP data. They are very fast and very high priority. Waiting on them is not an option. Is there some way we can get SSRS to work on these operational reports in preference to other types of reports (eg. "just for kicks" reports)? I think we'd almost like to add another SSRS server and dedicate it to the operational reports. Ideally the new SSRS server would use the same Report Server database but would only work on subscriptions for certain documents.
Has anybody else tried to solve this problem? This MS document does really address subscriptions or load balancing by report: http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx
I have received some reports and I have been asked to decide whether these reports can be developed as an operational report or Analytical Reports.
Basically I wanted to understand what points needs to be considered while deciding whether I should go for Analytical reporting (Cubes) or Operation Reporting.
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
We have 7 SSRS reports subscription which execute every night at various time intervals.
Some times, due to database unavailability subscriptions can not access the source database, so these subscriptions continuously try to connect the database. (Even after setting connection time out.)
When the database is up again, all these subscriptions create bottleneck for the database and the whole system crashes.
So we want to stop subscriptions after some time interval (lets say 2 hours after it starts),
After that time interval what ever may be the status of the subscription (processing, done or fail) it should stop.
Please let me know the solution, Thanks in advance.
I am very new to RS and I have a scenario that I am not sure how to enact in the application I am building.
I have a report that generates remittances for an EFT payment file. The Remittances in this file are either printed or emailed to the company they are form. The output type and recipients email address are stored in the Db.
I would like the user to be able to click my Generate Remittance button, and instead of the report being rendered on screen, I would like it to automatically send all remittances marked for printing to the printer, and all marked email to be sent to the email address that is in the Db for that report.
I am thinking that my application will need to run the report twice, the first time reporting on only the ones needing printing (but then how do I automatically send the report to the users default printer without the report being rendered on screen), and then a second time reporting on the emailed ones and then automatically emailing them off to the individual recipients.
If anyone can shed some light on what I am needing to do, or maybe even suggest a better way to go about it, it would be much appreciated.
Under My Subscriptions I have a report that runs daily. This report allows me to select certain items from a dropdown in parameter fields or select all. Some days the report fails and I get the follow error: The subscription contains parameter values that are not valid. When I click Edit to review, the parameter field is blank and my selections have been unselected. This report has several parameter fields but only only one or two of the parameter fields are blank, the others are ok.
I have been asked to move our entire system over from SQL2000 to SQL2005, i eventually got our database and Cubes over to 2005 and converted all my reports to 2005 and deployed them successfully on RS2005
All the subscriptions have been made and they seem to work when i click to view them. Although when i create a DDS for all my reports to run then they fail. Now i've got over 5000 reports to build and i'm definately not going to create each report manually. My SQL Reports huild successfully, it just seems to be the MDX Reports with a problem, i;ve checked my stored procs and they seem fine, i've even seen the [Clients].&[All.Clients] difference from SQL2005 ---->&<------ that is not in SQL2000.
If someone has, or has had the same problem before would you please help me. Any advice or suggestions would be more than welcome.
I have a Daily Invoice Report that has subscriptions for all customers and is run every night. Â The report has parameters of @date & @Customer. Â We had issues with invoices not being sent due to the Owner on the reports was disabled in Active Directory. Â We do not know how many reports did not get sent out so I am tasked with creating a script to rerun the reports for each day in August. Â I have queried the subscriptions, but need to figure out how to rerun the reports using the existing parameters set in the subscription, increment the report parameter date, and execute the emailing of the report.
Here is my query that gives me all the subscription information, but how do I rerun the job for all subscriptions for each date in August?
SELECT USR.UserName AS SubscriptionOwner    ,SUB.ModifiedDate    ,SUB.[Description]    ,SUB.EventType    ,SUB.DeliveryExtension
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
CDC is creating additional tables under System tables.
What is the performance overhead on the database by creating these tables?
I am going to access the CDC records through one ETL tool. Once read the data I am going to delete the records.
If frequency of changes are more once reading the data there may be few records will be added to the CDC. Is CDC is going to truncate the tables or only read records?
Hello,I read several articles of newsgroup about the bulk delete, and I foundone way is to:-create a temporary table with all constraints of original table-insert rows to be retained into that temp table-drop constraints on original table-drop the original table-rename the temporary tableMy purge is a daily job, and my question is how this work on a heavyload operational database? I mean thousand of records are written intomy tables (the same table that I want to purge some rows from) everysecond. While I am doing the copy to temp table and drop the table whathappens to those operational data?I also realized another way of doing the bulk delete is using BCP:1) BCP out rows to be deleted to an archive file2) BCP out rows to be retained3) Drop indexes and truncate table4) BCP in rows to be retained5) Create indexesAgain the same question: When I'm doing the BCP is there any insertionblocking to my original table? What happens to my rows meantime to beinserted?Does BCP acquire an exclusive lock on the table which prevents anyother insertion?Does any one have an experience with a BCP command for querying out 2million records, and how long will it take?I appreciate your help.
Hi, assuming a data driven web application was written, and it is used by a huge number of customers, we can set up some load balancing infrastructure, where multiple web servers run to process all requests. In this case, the database might run on its own machine. But what can be done if one machine is not enough for the database anymore? Is it possible to build some kind of database cluster? And if yes, how does the application work with such a cluster? Is there still ONE connection string or is it needed to query multiple SQL servers? Long story short; Can a SQL Server database cluster be handled as ONE SQL Server instance from outside, even it is hosted on multiple machines?And if yes, what edition of SQL Server 2005 is required? RegardsMarco Buerckel
I have web application for which I need to come up with a scalability plan. The application right now is running on 2 computers, the front end is running Tomcat with JSP (Java Server Pages). The back end is MS SQL 2000
The database is running fine now, but the file size and CPU utilization is growing and I need to figure out a way to distribute this database over several machines.
I looked at MSCE and setting up a cluster, but I think that the solution will NOT work in an intensive enterprise environment. (Am I wrong?). For one thing, there is a hard limit of 16 nodes in a cluster. Also, it requires a shared RAID or FibreChannel Array. These solutions are very expensive. Also, it's not easy to add additional capacity if you reach the maximum of the RAID array. For example, if you have a FibreChannel Array with 8 Terrabytes of capacity, if your DB uses up all 8 terrabytes, you have to buy a whole new enclosure.
One of the major objectives I have is to minimize cost and support unrestricted database growth.
Here is what I would like to achieve. If any of you know of a solution or something that YOU have USED which might give the functionality I require .. I would greatly appreciate your feedback.
Wish List:
1) Run single SQL Instance on regular PCs, which host 1 or More Tables (Table level partitioning) of a very large database.
2) Distributed JOINs on Tables residing on different physical PCs
3) Multiple Machines hosting same table (Fault Tolerance)
4) Single Virtual IP for database to Application Front End.
-----------
I looked at C-JDBC which is an open source middleware that can do some of the things above. I liked it, but it doesn't seem to support Distributed JOINs which means that our application - which requires quite a few tables JOINs - won't be able to achieve Table level partitioning.
Can anyone recommend any clustering middleware which you can recommend?
I'm currently developing an ASP.NET site with SQL Server 2005 Standard and I'd like to ask a question about the future of the database. It needs to have continuity and performance. I'm thinking about doing replication or mirroring for continuity and table partitioning for performance. I admit I've never done any of those before and I'll learn about them but they're not needed at this time. The question is, I'm currently designing the database and do I have to anything for consideration for those things I'm thinking of implementing later? For example, I'm using Identity in my tables but I've heard about identity crisis using replication with identity columns, therefore I'm thinking of using Guid's but now I fear the Guid column index itself will be the slowdown factor in the first place.
Any suggestions to consider? I'd appreciate any opinions.
Hi, My application is about to scale up significantly, and it seems that SSB could be very useful to help me scale it right, especially I like the multiple readers feature.
So, here's the deal €“ my application is about to get lots of records (peaks could gets up to €“ 300k records per hour). Each record must be processed separately, but could be processed in parallel €“ no dependency between rows (gut feeling tells me not to overuse parallelism, but I€™ll do my tests). It takes about 10ms-15ms to process a single row; I don't mind sliding to non peak hours if needed. Each record is constructed of about 10 columns.
My questions:
1. Is SSB the right solution as a buffer queue? any famous use case is similar to my problem ?
2. What is the fastest easiest way to serialize / deserialize each record?
I would prefer not using CLR integration due to performance issues and stick with tsql for now. I don€™t necessarily prefer XML serialization. If binary serialization works faster, it€™s fine with me.
I recently configured SQL Server 2012 AlwaysOn Availability group using two nodes - a primary and one secondary read only replica. Â The group is residing on a windows 2012 cluster with an smb file share as the quorum. Â I am able to successfully failover through SQL and through the windows 2012 cluster. Â When I look at the group dashboard on the primary server and view the Operational state of each node I notice an odd value. Â The secondary role server is listed as Unknown. Â I also noticed that the Availability replicas node icons in object explorer are displaying the same icon on the primary server but on the secondary server, the primary server is shown as a server with a question mark.
Am I missing a permissions setting or is this normal behavior.
For example:
ServerA is the primary ServerB is the secondary ServerA Â lists the servers in Object Explorer as:
ServerA (Primary)ServerB (Secondary) ServerB Â lists the servers in Object Explorer as:
ServerAÂ ServerB (Secondary)
The primary is never listed a primary on the secondary server. Â Again failovers are working properly, but I want to be sure I am not missing a setting somewhere.
I have a pivot chart that displays 3 data series.Series 1 values range from 17 to 106.Series 2 values range from 1 to 18.Series 3 values range from 0 to 1.When I display all three series on the chart, the value axis labels run from1 to 120 with major unit lines at intervals of 20.When I remove Series 1, the chart automatically adjusts itself and thevalue axis labels run from 1 to 20 with lines at intervals of 2, thusnicely accommodating the value ranges of Series 1 and Series 2.All this is fine. But when I remove Series 2, leaving only Series 3,the labels run from 1 to 1.2 with interval spacing of .2. Since thesevalues are counts, it makes little sense to show decimal values.Can anyone provide advice on how to keep these axis labels fromshowing decimal values, if a user removes Series 1 and Series 2?Thanks.- Bob
Read only transacted replication to about 60 clients. 100 or so transactions a day (8 hours), transaction (record) is probably about 8K/transaction. Reliable connection over 100base-T. Latency - 15 minutes would be great.
This doable? -- I couldn't find a timing/ sizing model any place.
We have a 3 month old quad processor/dual core server running SQLServer 2005 and already it is getting close to hitting the CPU wall.An 8 way CPU box is prohibitively expensive and out of the question. Iam looking desperately for a way to TRULY scale out SQL server...in thesame way that IIS can be scaled out via App Center.The "in the box" solution for SQL Server 2005 scaling out is the DMV.Unfortunately this solution makes the system less available rather thanmore (one server outage takes them all out for that table) and requiresserious rearchitecting of the software to use. Contrast this to IISand AppCenter where each added server makes the system more available,and requires no rearchitecting to work.Before someone says "what you want can't be done in adatabase"...Oracle has an application server middleware product thatlets you do both of the above. Just plug a new server with Oracle onit, and you've doubled your capacity. But SQL Server 2005 doesn't yethave a similar capability.So I read with great interest the following article that talks aboutwhy this is the case with SQL Server. There are two issues that makeit very difficult to do:http://www.sql-server-performance.c...ability_availab...You can create a crude pool using replication, but the performancetimes look horrendous.However, the article also talks about the latest developments in thisfield...specifically MIDDLEWARE that can create a scale out solutionthat is more available and that requires simply adding new servers toscale up.I found two companies which seem to offer this new capability:http://www.metaverse.cc/newsevents.asp?cid=17999andhttp://www.pcticorp.com/product.aspxBoth companies appear to have patents or a patent pending on theprocess. I tried to contact metaverse but got no reply, despite theirrecent press release. I just emailed Pcticorp today to see if I couldlearn more about their product.My question for this group is:Does anyone have experience with either of the two products (or anyothers that provide this capability)?Many thanks in advance for your help.Ian Ippolitohttp://www.rentacoder.com
I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.
I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.
Please help me if I can look similar packages performing? Thank you!!
I've a report projects where each report has a number of columns and the spec is to have all the columns print-out on the same page. Is there a setting that will auto-scale these columns to fit to the page or will I have to edit the font size and widths manually on each report to fit to the page?
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
My issue is with converting multi-value parameters:
In Crystal Reports, you can set a parameter to accept multiple vales (Discrete, Range or Discrete and Range).
As an example:
I have a database table with a column called ID. I can create a parameter called param_id and set the options of the parameter to "Allow multiple range values".
With this setup, I can limit the result set of the report by comparing the param_id parameter to the ID column in the database. Because param_id is a multi-value range parameter, I can pass it the following data: 1 - 50 60 - 80 150 - 127
This will only return results within those ranges.
Does anyone know if SSRS provides this kind of functionality?
Is there a way to copy reports from other users "My reports" folder? I am logging onto the management studio reporting services using an administrative account and I am able to view the reports from all users' "My Reports" folder. But I am not able to export them as an rdl file. I am able to export reports from other common folders, but not from the "My Reports" folder.
The reason I need to do this is some of the users have created some reports in one environment and the reports are available in their respective "My Reports" folder. I need to move these reports to their corresponding "My Reports" folder in another environment.
We are running Reporting Services on an Application server, side by side with a web application. The server exists outside our corporate domain due to the fact that our external vendors need access to the application (not the reports). Our web app uses a third party mail system to funnel the application email thru our corporate exchange server. Since Reporting Services is not connected to our domain, it can not authenticate to send reports via e-mail subscriptions. I am looking for alternatives to make this work from outside the domain.
I have created data drvien subscriptions in Report Manager (rs 2005). When the reports are run in Report Manager they give a status ie 5 processed of 5 Total-2 errors. Is it possible to find out what these errors are as no indication is given?
We've got a lot of user subscriptions that need to be re-run (data was bad on the initial run). Is there anyway to do this programatically or behind the scenes? We're looking for a way to avoid going into each subscrption and re-running.
