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.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2014-08-27 : 22:05:09
|
| hi all,My idea of a data warehouse seems to be a bit different from others within my IT dept. To me, the data should be refreshed from productionin either realtime mode or at some agreed upon schedule. And all reports, queries should run against the data warehouse databases.But some in my dept. think it's fine to create a view ON the warehouse server that references tables on the production server. I say that kinda defeats the purpose of having a data warehouse and that you may as well create the view on the production server. (Obviously I intend to create the production tables in the data warehouse, refresh them and run reports against the local data in the warehouse)Thanks for your opinions. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-28 : 12:26:50
|
| I would be completely against views in the warehouse database referencing tables in the oltp prod database. Do they need real-time data for those specific aspects? I'd probably setup an Availability Group with a synchronous readable secondary and have them query what they need on the readable secondary.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2014-08-28 : 21:21:11
|
| Thanks Tara. This is 2008 R2 and due to a botched replication configuration (by someone else), I don't have the option of using any type of replication. I refresh several tables via a tsql job nightly. I learned today that will suffice for these new tables as well.The asynchronous read-only db sounds interesting but it would have to reside on the production server, correct? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-29 : 12:26:33
|
You posted in the 2012 forum.  The asynchronous read-only database would be on another server. We setup a 6-node AG cluster last year. 3 nodes are at our primary site, 3 nodes are at our DR site. The 3 nodes at the primary site are all synchronous. The first one is where the writes occur (OLTP), the second one is for reports, and the third is for failover purposes only. 2 of the nodes at the DR site are synchronous replicas. We don't have those setup as readable as the reporting needs require real-time data (and thus reporting occurs on the synchronous readable secondary). SQL 2012 has a limitation of 5 AG replicas, so the 3rd node at the DR site is in the cluster but not doing anything unless we failover to the DR site and then do a bunch of work to get it into the AG and then switch the 3rd node at the primary site to not be in the AG (plus the other replicas all flip roles).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2014-09-01 : 22:21:43
|
| Yes, my data warehouse is on a 2012 instance. But the source data resides on 2008 R2 :), which prevents me from using Always On.Fortunately, I don't have to worry about disaster recovery. we use a different technology for that and we perform a test failover twice a year. |
|
|
|
|
|
|
|
|
Definition of Data Warehouse