Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DB for Reporting Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 1:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:24 AM
Points: 24, Visits: 108
We have a policy here that no one gets access to the production databases ever. There is only 1 dedicated account that the application uses for connection. We have a new database/application coming on line soon that will follow this model. However, the users have indicated that need to have the ability to run ad-hoc queries against the database on a daily basis. Clearly the best way to accomplish this is to keep a second database for reporting purposes (on a different server) that we do allow users to access. That being said, what would be the best way to keep the reporting db up to date while also not requiring that everytime it gets updated I have to go and fix orphan accounts?
Post #1430601
Posted Wednesday, March 13, 2013 1:42 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
Replication works wonders for that.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1430604
Posted Wednesday, March 13, 2013 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:24 AM
Points: 24, Visits: 108
So replication will allow me to take one database with only 1 login and apply that to another database with multiple logins with wiping out said multiple logins?
Post #1430608
Posted Wednesday, March 13, 2013 1:59 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
Replication won't affect the logins on the either server. Replication won't affect the database users either, on the subscribing server.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1430620
Posted Wednesday, March 13, 2013 10:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
+1 for exploring Replication

This might be the most common use-case for implementing Transaction Replication with a real-time Push Subscription. A nice benefit is that you can have a separate index structure on the reporting server database and the data movement from the Publisher will not be affected. This comes in handy because most times the people using the Subscriber database for reporting have far different querying needs than do the people or applications querying the Publisher database.

The basic relevant how-to articles:

Step 1: How to: Create a Publication and Define Articles (SQL Server Management Studio) - SQL Server 2008 R2
Step 2: How to: Create a Push Subscription (SQL Server Management Studio) - SQL Server 2008 R

Replication is a complex subsystem within SQL Server and may be the one with the most variations on how you can set it up so I would recommend doing some reading and some testing in a non-prod environment before making a decision to go with it.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1430739
Posted Friday, March 15, 2013 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:34 PM
Points: 12, Visits: 89
You can also use SQL Server 2012 Always ON Availability Groups and using a secondary replica for reporting. This way you also accomplish redundancy and reporting at the same time.

http://msdn.microsoft.com/en-us/library/ff877884.aspx#AGsARsADBs

If that's not feasible then I would recommend transitional replication as well to maintain up to date data.

Hope this helped

Jay Beta



Post #1431598
Posted Friday, March 15, 2013 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
Jay Beta (3/15/2013)
You can also use SQL Server 2012 Always ON Availability Groups and using a secondary replica for reporting. This way you also accomplish redundancy and reporting at the same time.

http://msdn.microsoft.com/en-us/library/ff877884.aspx#AGsARsADBs

I am aware of the read-only routing which is a very nice feature in 2012 for splitting reads and writes, but from a reporting standpoint, does that preclude the addition of indexes to the read-only instance that are not on the primary-instance?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1431630
Posted Friday, March 22, 2013 9:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:34 PM
Points: 12, Visits: 89
Hmm, great point. I was not sure of the answer so I researched it a bit and it seems that no indexes of any type are allowed to be created on the secondary replicas. For SQL Server 2012 they are read-only unfortunately. All Indexes are inherited from the primary replica...

Jay Beta
Post #1434559
Posted Friday, March 22, 2013 10:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
I thought I read that but wasn't sure if I was missing something. Thanks for posting back.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1434564
Posted Wednesday, March 27, 2013 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:15 PM
Points: 34, Visits: 254
So, what's the best type of replication for this kind of setup?

My situation is - I need to, essentially, copy the database from a production server to a reporting server once every night. But, I don't want to overwrite any unique reporting-related views that have been created on the reporting server during this update/refresh of data. (Which is why I'm looking at replication as opposed to something more crude.)

So, basically, I don't need to keep the reporting DB updated throughout the day. A nightly refresh is fine.

I attempted to set up snapshot replication, but ran into all kinds of problems because when the replication process attempted to drop/recreate existing articles on the reporting server, it would error out because of dependencies in other articles. While I know you can alter the way the process handles existing articles, I wanted it to simply drop/recreate them.

How do the experts typically configure this type of replication?

Thanks.
Post #1435960
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse