March 13, 2013 at 1:34 pm
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?
March 13, 2013 at 1:42 pm
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[/url]
Learn Extended Events
March 13, 2013 at 1:45 pm
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?
March 13, 2013 at 1:59 pm
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[/url]
Learn Extended Events
March 13, 2013 at 10:25 pm
+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
March 15, 2013 at 9:41 am
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
March 15, 2013 at 10:36 am
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
March 22, 2013 at 9:34 pm
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
March 22, 2013 at 10:07 pm
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
March 27, 2013 at 8:54 am
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.
March 27, 2013 at 8:59 am
In my experience the most common setup that suits a separate reporting database where the indexes can be different from the primary database is Transactional Replication with a Continuous Push Subscriptions. This means that as soon as changes are committed to the Publisher database they are immediately Pushed to the Subscriber(s) making the data available to reports.
Configuring, Modifying, and Disabling Publishing and Distribution (Replication) - SQL Server 2008 R2
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2013 at 9:51 am
Unfortunately, we are forbidden (for various, nonsensical reasons) from pulling data from the production DB outside of the overnight window.
So, would you recommend something other than snapshot replication for this? (Or, if snapshot is indeed the best method, is there a smooth way to overcome the dependencies problems I referred to in my earlier post?)
Thanks.
March 27, 2013 at 11:01 am
I have only used Snapshot for a few isolated tables, e.g. a mostly static lookup or staging tables that needed to also be copied to another server nightly, so nothing with inter-article (or is it intra) dependencies. What kind of issues are you having? Foreign key issues? You can opt not to replicate FK constraints.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2013 at 11:23 am
It was quite a few problems - some were key related, others were not.
(Also, as a side note, I have no real control over the schema of the PROD DB.)
If I opt not to replicate FK restraints, is that found on the same options page where I set the behavior of replication for existing articles?
Thanks again.
March 27, 2013 at 1:17 pm
If I opt not to replicate FK restraints, is that found on the same options page where I set the behavior of replication for existing articles?
Sorry, I won;t be much help in deciphering how to use SSMS for this. I use T-SQL to setup Replication. In the call to sp_addarticle you'll define your options using @schema_option.
http://msdn.microsoft.com/en-us/library/ms173857(v=sql.105).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply