April 4, 2012 at 3:44 pm
Our counter response to Architectural Preference is going to be Customer Mandate.:-P
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 4, 2012 at 6:55 pm
Welsh Corgi (4/4/2012)
ok, I would probably have the Database in Full Recovery but change it to Simple before the load and change it back to Full then perform a complete Backup.That way if something happens during operational hours, I can perform a PIT Recovery Model if needed.
Keep your input coming. Thanks.
A data warehouse is normally stable during normal operations with changes only occuring during loads. Why would you need to do a PIT recovery during the day? Reload the backup after the loads completed and you are on your way.
April 6, 2012 at 1:16 pm
They believed me and all agreed on my side recognized that they are not doing things right.
I made several points; about a page of bullet items and it is in the grown ups hands.
Thanks for all of you input!:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 26, 2012 at 9:15 am
Any chance we can see this bulleted list? Thanks. I also need to convince my boss that cross database joins are just as fast as long as its on the same SQL Server instance.
I was also thinking that a separate DB for staging would make it easier to setup users and security groups. We don't want to mess with schemas.
July 26, 2012 at 7:59 pm
jungle.ed (7/26/2012)
Any chance we can see this bulleted list? Thanks. I also need to convince my boss that cross database joins are just as fast as long as its on the same SQL Server instance.I was also thinking that a separate DB for staging would make it easier to setup users and security groups. We don't want to mess with schemas.
Actually you would have better performance to have the Staging and Data Warehouse Database on different Servers.
You get better I/O. One example is that you read sequentially from the Staging and you write randomly to the destination. If the Databases are on the same server the disk heads are jumping all over the place unless you have the Staging and DW on a separate physical disk.
Also you have more processor and memory resources.
I had asked the question months ago about having Staging and the Data Warehouse of a different Server but I was told no keep them in the same server.
I just got audited today which goes through tomorrow and I already got dinged for having them on the same server.
I will let you know the outcome when I see the report.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 7, 2016 at 5:10 pm
Hey, I'd be interested in seeing the results of your audit, if you can paste it here somehow. Even if you have to summarize, or redact anything sensitive.
I'm in the same boat, boss wants to do Staging and Data Warehouse in the same database. Not sure I really like the idea.
September 7, 2016 at 5:29 pm
speedcat (9/7/2016)
Hey, I'd be interested in seeing the results of your audit, if you can paste it here somehow. Even if you have to summarize, or redact anything sensitive.I'm in the same boat, boss wants to do Staging and Data Warehouse in the same database. Not sure I really like the idea.
It is not good have staging and the DW on the same DB.
Try and put Staging an the DW DB on different Servers. I did not do so due to $ reasons.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 7, 2016 at 6:08 pm
We have the same cost concerns. Our system may not have the processing or access concerns that some others do where performance or throughput become an issue.
We are likely to have the two databases on the same server. Or it may all end up on one database. I'm just trying to get good reasoning to inform my boss on why having the staging and warehouse on the same database might not be good. A bulleted list of "why not to do it this way." 🙂
September 8, 2016 at 5:14 am
I have a lot to do so I can't come up with a bullet list.
Keep the Staging and DW database separate. The main reason from my perspective is it allows you to decrease the backup and recovery window.
If you put the databases on separate servers you increase your performance, more I/O, CPU, Memory.
Also I recommend moving the SSIS Packages to a BI Server. SSIS hogs up resources.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2016 at 6:57 am
speedcat (9/7/2016)
We have the same cost concerns. Our system may not have the processing or access concerns that some others do where performance or throughput become an issue.We are likely to have the two databases on the same server. Or it may all end up on one database. I'm just trying to get good reasoning to inform my boss on why having the staging and warehouse on the same database might not be good. A bulleted list of "why not to do it this way." 🙂
Some other points to consider:
- two databases allows you to run staging in SIMPLE recovery mode and the DW in FULL recovery increasing performance for staging and reducing log file footprint. this may be moot depending on your high availability solution, e.g. with mirroring/AGs you have to run FULL
- as Welsh mentioned, you have more flexibility in your backup and recovery strategy
To me these are the two primary reasons supporting keeping them separate.
If I were compelled to put all in one database I would I would look into logically separating the concerns by implementing schemas. This will generally keep things more organized into areas of concern and, more measurably, could make database security easier for you to implement and maintain as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 8, 2016 at 7:12 am
Welsh Corgi (3/23/2012)
Until today I never saw a Database that contained both the Staging Tables and Data Warehouse Tables in the same Database....
Until SQL Server 2012, it wasn't an option to backup file groups, so containing staging tables in a separate database was the only way to prevent backups from including a lot of unnecessary and volitile data. Even today, the case can be made for containing the staging tables, not just on separate database, but on a separate instance. Actually, you may even want to contain your staging environment outside SQL Server. For on-premisis staging databases, I've entertained the idea of installing Linux/MySQL on left over hardware.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply