Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Database Mirroring OR logshipping

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-15 : 13:07:48
For reports we are currently using transactional replication, but we will be using the read-intent option of AlwaysOn once we upgrade to SQL Server 2012.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-15 : 13:08:23
In my opinion, database mirroring and log shipping are not at all good choices for a reporting environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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.

Go to Top of Page

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 question

Whats the period upto which you need data in your reports? is it realtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 13:06:43
Yes it will be queued up, so it will proceed once the table is no longer locked.

No need to do another snapshot.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Fatboy40
Starting Member

8 Posts

Posted - 2013-07-30 : 03:02:55
Thanks tkizer (shame this forum does not have a 'thanks' feature).
Go to Top of Page
   

- Advertisement -