| Author |
Topic |
|
josh2009
Yak Posting Veteran
81 Posts |
 Posted - 2010-05-04 : 12:05:01
|
| Hi,Trying to configure/install reporting services for SSRS. Data will be from two separate database residing in different servers. When configuring the report servers, since data will be from two separate servers, should there be two report servers configured as well, one for each server? Or can there be just one report server connected to the 2 different servers? Any help will be greatly appreeciated. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-04 : 13:07:41
|
| one report server is enough. you can create linked servers to other server from it and then access the object in it using four part naming convention. Alternatively you can use OPENROWSET to access other server data which doesnt require linked server connection but its usually preffered for adhoc access.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-04 : 15:48:02
|
Thanks for the quick reply. Very much appreciated. Now I have to look into creating linked servers. One question though, is there going to be an issue if two report servers are created, one for each database? And what would be the disadvantage of having two report servers? Thanks again.quote:
Originally posted by visakh16
one report server is enough. you can create linked servers to other server from it and then access the object in it using four part naming convention. Alternatively you can use OPENROWSET to access other server data which doesnt require linked server connection but its usually preffered for adhoc access.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-04 : 16:34:52
|
None of the reports would need to connect to 2 different databases. One of the databases actually is a mirror image of the other with the exception of being a day older. With our purchased system, the vendor's reporting application, which uses SSRS reports, copies the production database into a separate database and is essentially a day old. This copy is stored in a different db in a separate server. As for the "true" prod db, this is where statistical reports would be ideally created using SSRS. Right now we are using Crystal Reports and I'm looking at SSRS as a potential replacement for CR. Thanks again. quote:
Originally posted by tkizer
I'm not sure that a linked server or OPENROWSET is necessary. Do any reports need to connect to two different databases? If they don't, then you would just have two data sources defined in Report Manager and then each report would point to the right data source.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-04 : 17:26:55
|
Our SQL server db is installed in a cluster. Does that pose a potential problem for installing SSRS?That's a good idea. So basically you replicate real-time transactions by persisting them in the second db. Do you use after insert, update triggers running stored procs to replicate your prod db? Thanks.quote:
Originally posted by tkizer
Cool, then you don't need linked servers or OPENROWSET. We use transactional replication to keep our second database more current than a day old. It keeps it practically real time with production, so we have no need to run reports on prod. All reports run on this second server. This has saved us from tons of performance issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-04 : 17:31:29
|
quote:
Originally posted by josh2009
Our SQL server db is installed in a cluster. Does that pose a potential problem for installing SSRS?
If SSRS is also to be installed on the cluster, then you need to be aware that SSRS is not cluster-aware as far as I know. Our config puts SSRS on a separate server.quote:
Originally posted by josh2009
That's a good idea. So basically you replicate real-time transactions by persisting them in the second db. Do you use after insert, update triggers running stored procs to replicate your prod db?
No, we use transactional replication, which is a SQL Server technology that replicates DML transactions to another database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-04 : 17:46:04
|
We have 2 physical DBs, one basically a mirror of the other, and for our prod db we connect to a "virtual" db pointed to one of the 2 DBs. This is our true prod db. So, what would be a recommended way to install SSRS to point to our prod db? Thanks.quote:
Originally posted by tkizer
quote:
Originally posted by josh2009
Our SQL server db is installed in a cluster. Does that pose a potential problem for installing SSRS?
If SSRS is also to be installed on the cluster, then you need to be aware that SSRS is not cluster-aware as far as I know. Our config puts SSRS on a separate server.quote:
Originally posted by josh2009
That's a good idea. So basically you replicate real-time transactions by persisting them in the second db. Do you use after insert, update triggers running stored procs to replicate your prod db?
No, we use transactional replication, which is a SQL Server technology that replicates DML transactions to another database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josh2009
Yak Posting Veteran
81 Posts |
Posted - 2010-05-04 : 18:02:58
|
I see. Thanks for the info and recommendation. We would most likely install SSRS in a separate server. Thanks again.quote:
Originally posted by tkizer
The recommendation is to always put SSRS on its own server since SQL Server should be on a dedicated machine. We put SSRS on the web server.But this config requires you to license SSRS as well. If SSRS is on the same machine as SQL Server, then you don't need additional licenses.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Reporting Services and Report Server