- Data Driven Subscriptions In Sql Server 2008 Standard Edition Download
- Data Driven Subscriptions In Sql Server 2008 Standard Edition Online
I have been working with SSRS since its initial release in 2004 and have written about it extensively over the years, mostly favorably. Despite its myriad shortcomings, obtuse errors and required hacks to make it work the way I want, it’s always somehow managed to tackle any business reporting requirement that I’ve thrown at it.Recently, I worked on a SSRS project where the customer wanted to consolidate over 1000 reports, from two existing SQL Server 2008 R2 Enterprise SSRS deployments, into a single SQL Server 2016 ReportServer database, and then deploy that database to a new 2-node production cluster. We would use a scale out deployment to attach two Report Server Web Portal front ends, one per node, to the clustered ReportServer database. The idea was that should one node go “belly up”, the database instance containing the ReportServer database would failover and be available on the secondary node, and the remaining Report Server Web Portal, also on the secondary node, would continue to render the reports that drove their retail business.Sounds straightforward? It was, until I realized the new production cluster would have SQL Server 2016 Standard Edition, not Enterprise Edition. It required a bit of creativity, but this article describes my solution, its merits and drawbacks.
Scaling Out SSRSThe ability to “” SSRS to multiple frontend Web servers, so that one or more report server instances can share a single report server database, has always been an Enterprise Edition feature, and remains so in SQL Server 2016.When I first joined the project, I confess that “separation of duties” meant that I was blissfully unaware of the need for my solution to work on Standard Edition. The customer was busily building the new production cluster, using Cluster Shared Volumes on Windows Server 2016.
In the meantime, I was working in the Staging environment, merging reports from the two existing SQL Server 2008 R2 Enterprise SSRS deployments into a single SQL Server 2016 ReportServer database, on the Staging Server. This Staging Server had Developer Edition, which is unbounded feature-wise.Merging reports from two ReportServer databasesI won’t cover how I merged the reports into a single ReportServer database and prepared it for migration to SQL Server 2016. For more information, please review the following link on using RS.EXE to migrate objects from one SSRS instance to another vetted the new ReportServer database, I had to migrate it to the production cluster and my plan had been to use the scale out deployment native to SSRS Enterprise.Figure 1 – Scaling out SSRS, Enterprise solution.Here, we have two cluster nodes ( SQLServerNode01 and SQLServerMode02), and one SQL Server instance ( Server4SQL01). In this Enterprise solution, the single ReportServer database is on a clustered instance so can failover to either node, and the two SSRS Web Portal front end applications are configured to connect to that single ReportServer database, providing the “scale out” capability that Enterprise Edition offers. No matter which node is active, or otherwise available, Reporting Services can render the reports via the same URL, which would use the clusters virtual name, in this case Server4, thus: was then that I discovered that the 2-node cluster was only to have SQL Server 2016 Standard Edition, with SP1.
In Standard Edition, scale out deployments are not allowed so only one node could serve as the Report Server Web Portal that connected to the ReportServer database. While the database itself could failover from node to node, the Web Portal would not be available in the event that SQlServerNode01 went completely offline. My immediate response was to call the customer in panic and explain that we could not connect to a single ReportServer database from two different Web front end nodes. He explained back in large dollar signs the exact cost to implement Enterprise Edition on these two 32-core nodes.One gulp later, and with my back against the drawing board, we agreed upon a new possible solution. My proposed SSRS Standard Edition scale out architecture would look as shown in Figure 2.Figure 2 – Scaling out SSRS, The Rodney Landrum Standard SolutionNow, we have two cluster nodes ( SQLServerNode01 and SQLServerMode02), and two SQL Server instances ( Server4SQL01 and Server5SQL02).
The theory was that we restore same ReportServer and ReportServerTempDB database on both instances on the cluster, and configure SSRS on each node. In theory (this quickly became my favorite qualifier), this would allow the SSRS Web Portal front ends on SQLServerNode1 and SQLServerNode2 to run independently, dividing the load between the two nodes. When failover occurred, it should still be possible to access reports from either Web Portal, using the cluster virtual name like without any other changes, regardless of which node “owned” the SQL Server instance where the ReportServer databases resided.I could see some potential challenges, of course. This solution would require double deployment of new and modified reports and some additional logic to enable or disable subscriptions, controlled by SQL Agent depending on which SQL Server instance was “active”. It would also require query logic that could compare the two databases to ensure they were in sync.However, it should work!
Happily, on this occasion, theory and practice were closely aligned. It did not take long to implement and test this solution, and to my amazement every step went flawlessly.
I give due credit to Microsoft for the obvious effort they have put in to improving the customer experience with these latest releases.The following steps describe all that is required to complete the solution of deploying Reporting Services on two nodes of a multi-instance failover cluster using two identical ReportServer databases that housed all of the customer’s reports. Restore Prepared ReportServer and ReportServerTempDB Databases on PrimaryI had prepared a single ReportServer database, on a separate SQL Server 2016 Staging server called SQLTest01, into which I merged the reports from two different Reporting Services deployments. However, in other circumstances, I could just as easily have performed all of the prep work using the ReportServer database on the source SQL Server 2008 R2 instance. All you need to do is to back up the ReportServer and ReportServerTempDB databases from the source server and restore them on both on each of the SQL Server 2016 instances, on the cluster.It is important to ensure the database names stay the same as they were on the source system because these names, especially for ReportServerTempDB, are actually hard coded in many or the stored procedures, when SSRS is configured.
SELECT. FROM sys. DmosclusternodesNodeNamestatusstatusdescriptioniscurrentownerSQLSERVERNODE020up0SQLSERVERNODE010up1In my proposed solution, I will drive the logic using the owner node for Server4SQL01 but, of course I will need to recognize the owner node of Server5SQL02 as well because I do not want the same subscriptions running on both SQL Server instances. I will provide the subscriptions logic job in a future article.
Data Driven Subscriptions In Sql Server 2008 Standard Edition Download
Install and Configure SSRS on Secondary NodeIf SSRS is not installed on the secondary cluster node, as was the case for me, a couple of extra steps are required. Logged onto the secondary node, SQLServerNode02 in my case, you can run Setup.exe from the installation media, with one caveat: since this is a cluster, SQL Server will check to see if there is already an instance running in the cluster and if so the installation will fail with an “Existing clustered or cluster-prepared instance” error and you will need to exit the installation. To get around this issue all you have to do is run Setup.exe from the command line and bypass the check. Exe / SkipRules = StandaloneInstallHasClusteredOrPreparedInstanceCheck / Action = InstallThis will initiate the install without the cluster instance check and will allow you to continue with the Reporting Services installation. This step assumes that you are familiar with installing a new Reporting Services native installation. If not, please refer to this link: Just remember that you will only need the “ Reporting Services – Native” option and nothing else such as the Database Engine Services, and so on.For the installation, I chose to install a new SSRS instance as opposed to adding features to existing instance. If I choose to add features to an existing instance this too would fail and you would be warned that the instance ( SQL01 or SQL02) already exists and cannot be created.
I created a new SSRS instance called SQL03. END;The results in Figure 13 tell me that I have one more report on Server5SQL02 than I do on Server4SQL01 and 4 more “Not Known” items. Rodney Landrum went to school to be a poet and a writer. And then he graduated so that dream was crushed. He followed another path, which was to become a professional in the fun filled world of Information Technology. He has worked as a systems engineer, UNIX and network admin, data analyst, client services director and finally as a database administrator.
The old hankering to put words on paper, while paper still existed, got the best of him and in 2000 he began writing technical articles, some creative and humorous, some quite the opposite. In 2010 he wrote, a title his editor disdained, but a book closest to the true creative potential he sought; he still yearns to do a full book without a single screen shot, which he accomplished in 2019 with his first novel, Chronicles of Shameus He currently works from his castle office in Pensacola, FL as a senior DBA consultant for Ntirety, a division of Hostway/Hosting.
Hello everyone,I am hoping to get some ideas and avoid going down the wrong track with some reports I am working on. We are running SQLServer 2008 Standard and I need to set up subscriptions for 2 different reports. Data-driven subscriptions would be ideal but that optionis not available in Standard and we are not upgrading to Enterprise edition any time soon.Both reports need to accept parameters to pass to procedures that get the data for the report. One of the parameters also needs to be used to determine where the report should be saved and what file name to use in the directory.
Data Driven Subscriptions In Sql Server 2008 Standard Edition Online
The reports need to be exportedto Excel before they are saved in a directory on a network drive.I have been searching and reading for days now and have found little to nothing on the subject that would lead to a solution. There is a post showing how to get around the problem with the prompt when SSRS exports a report to PDF, which could work for Excelexports, but it is shown as a web application. I am not sure if a web appliation is the best way to get my reports delivered since they have to run on a schedule.Have you found a way to implement a data-driven subscription in SSRS 2008 standard? HOw?Have you found a tool to buy that helps with this? I'd love to hear about it.Any and all help is greatly appreciatedThanks all.