DB for Reporting

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » DB for Reporting

15 posts, Page 1 of 2

1 2 »»

DB for Reporting

Rate Topic

Display Mode

Topic Options

Author

Message

mcopa

Posted Wednesday, March 13, 2013 1:34 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:54 AM
Points: 14, Visits: 62

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

SQLRNNR

Posted Wednesday, March 13, 2013 1:42 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

Replication works wonders for that.

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

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1430604

mcopa

Posted Wednesday, March 13, 2013 1:45 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:54 AM
Points: 14, Visits: 62

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

SQLRNNR

Posted Wednesday, March 13, 2013 1:59 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

Replication won't affect the logins on the either server. Replication won't affect the database users either, on the subscribing server.

Post #1430620

opc.three

Posted Wednesday, March 13, 2013 10:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

+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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1430739

Jay Beta

Posted Friday, March 15, 2013 9:41 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 03, 2013 7:16 AM
Points: 12, Visits: 57

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

opc.three

Posted Friday, March 15, 2013 10:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

Jay Beta (3/15/2013)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1431630

Jay Beta

Posted Friday, March 22, 2013 9:34 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 03, 2013 7:16 AM
Points: 12, Visits: 57

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

opc.three

Posted Friday, March 22, 2013 10:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696, Visits: 11,715

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434564

sickpup

Posted Wednesday, March 27, 2013 8:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:40 PM
Points: 28, Visits: 166

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 »

15 posts, Page 1 of 2

1 2 »»

Permissions