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
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Definition of Data Warehouse
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

385 Posts

Posted - 08/27/2014 :  22:05:09  Show Profile  Reply with Quote
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 production
in 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

USA
37143 Posts

Posted - 08/28/2014 :  12:26:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jbates99
Constraint Violating Yak Guru

385 Posts

Posted - 08/28/2014 :  21:21:11  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 08/29/2014 :  12:26:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jbates99
Constraint Violating Yak Guru

385 Posts

Posted - 09/01/2014 :  22:21:43  Show Profile  Reply with Quote
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.

Edited by - jbates99 on 09/01/2014 22:26:57
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.05 seconds. Powered By: Snitz Forums 2000