| Author |
Topic |
|
swathi3003
Starting Member
23 Posts |
 Posted - 2013-07-15 : 06:40:08
|
| Hello friends, I want to maintain another database only for Reports.what is the better ways for achieving this Database Mirroring Or Log shipping??Please suggest best way..Thanks& Regards------------swati |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-15 : 06:56:26
|
| What kind of reporting you're looking at? Is it static reporting like what you do in OLAP systems or do you want real time reporting? If former, you could even do a database snapshot and report out of it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
swathi3003
Starting Member
23 Posts |
Posted - 2013-07-15 : 07:36:37
|
| Application generates SSRS reports as per given details.....If we use databse snapshot..does original database changes reflect to snapshot database? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-07-15 : 14:34:11
|
quote:
Originally posted by tkizer
In my opinion, database mirroring and log shipping are not at all good choices for a reporting environment.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I think it really depends on the reporting requirements. If you need near real-time reporting, without impacting the production system then your options are replication or AlwaysOn (SQL Server 2012 with a cluster).If you do not require near real-time and can support reporting with data up to 24 hours (or more) old, then log shipping, replication, database mirroring (with database snapshot) and backup/restore are all available options. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 01:19:25
|
quote:
Originally posted by swathi3003
Application generates SSRS reports as per given details.....If we use databse snapshot..does original database changes reflect to snapshot database?
Thats what i asked in my earlier questionWhats the period upto which you need data in your reports? is it realtime?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
swathi3003
Starting Member
23 Posts |
Posted - 2013-07-16 : 02:40:25
|
| yes its real time... i need to get reports based on given values..if i enter today's date i need to get all data till today |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 04:39:23
|
quote:
Originally posted by swathi3003
yes its real time... i need to get reports based on given values..if i enter today's date i need to get all data till today
till today or including today?If former, then you can take snapshot at end of each day and use it for next days report activity If latter, replication will be what you'll be interested in.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Fatboy40
Starting Member
8 Posts |
Posted - 2013-07-16 : 11:32:30
|
| Sorry to hijack this thread, if I use 'Transactional Replication' ('Transactional Publication' as the publication type) for real time reporting against the data is the 'subscriber' db online ?.If I'm presuming that the subscriber db is online, if for example I ran a very complex query against the subscriber db and locked a table whilst it ran will the 'streamed transactions' going to the subscriber db publisher just queue up until the lock has gone or will I end up with a corrupted subscriber.Finally, sorry for all the questions, can I have a publisher which is SQL 2008 R2 and a subscriber which is SQL 2012 ?. Do the publisher and subscriber have to be the same MS SQL versions ?.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-07-16 : 14:51:31
|
| Transactional replication can be *near* real-time, but it is always behind the publisher. And if you have big data changes, index rebuilds, etc, then the subscriber can become very latent. Regarding the question of complex query/queued up from Fatboy: if you are locking the table, then replication will be unable to write the new data for that table. Why is it locking the entire table? I think you need to fix that first before proceeding. Get your query and indexes in order and think about using RCSI isolation level.The publisher and subscriber do not have to be the same versions of SQL Server. You can even have Oracle be a subscriber.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Fatboy40
Starting Member
8 Posts |
Posted - 2013-07-26 : 07:44:27
|
quote:
Originally posted by tkizer
Regarding the question of complex query/queued up from Fatboy: if you are locking the table, then replication will be unable to write the new data for that table...
Thanks Tara.The write to the subscriber table won't be queued up at all and then complete once the lock has gone ?.Also once the subscriber is active can you take another snapshot of the publisher DB and push this out to the subscriber so that you can be certain that all data is present and correct ?.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Fatboy40
Starting Member
8 Posts |
Posted - 2013-07-30 : 03:02:55
|
| Thanks tkizer (shame this forum does not have a 'thanks' feature). |
|
|
|
|
Database Mirroring OR logshipping