SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Database Mirroring OR logshipping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swathi3003
Starting Member

20 Posts

Posted - 07/15/2013 :  06:40:08  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/15/2013 :  06:56:26  Show Profile  Reply with Quote
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

20 Posts

Posted - 07/15/2013 :  07:36:37  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/15/2013 :  13:07:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37157 Posts

Posted - 07/15/2013 :  13:08:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
799 Posts

Posted - 07/15/2013 :  14:34:11  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/16/2013 :  01:19:25  Show Profile  Reply with Quote
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

20 Posts

Posted - 07/16/2013 :  02:40:25  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/16/2013 :  04:39:23  Show Profile  Reply with Quote
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

United Kingdom
8 Posts

Posted - 07/16/2013 :  11:32:30  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/16/2013 :  14:51:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
8 Posts

Posted - 07/26/2013 :  07:44:27  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 07/29/2013 :  13:06:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
8 Posts

Posted - 07/30/2013 :  03:02:55  Show Profile  Reply with Quote
Thanks tkizer (shame this forum does not have a 'thanks' feature).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000