|
|
You're in the
sql server data warehouse
group:Warehouse Server Requirements
sql server data warehouse:
Hello,
We're considering creating a data warehouse with either SQL Server 2000 (which we have now) or SQL Server 2005 (which we hope to go to at some point in the future). Currently, we have a MSSQL 2k server running with around 20 - 30 separate databases. My question is, is there a best practice guide to what your server layout between transactional databases and warehouse databases should be? I'm being told I have to justify getting a new server to house just the data warehouse instead of putting it as yet another database on our active sql server. My gut tells me having the warehouse live on the same server as the transactional databases is a bad idea, but I can't find a definitive document proving this. Any help would be appreciated - thanks!
generally a DW server required a dedicated server because the hardware
optimization is different. an OLTP required random access and the usage of the log file is high compared to a DW database. a DW database required more sequential access than random access. There is near no log usage. the tempdb database is generally more used in a DW. Also when you start queries against a DW database your need more resources. its not a "simple and quick" transaction with just few bytes to read or write, you have to scan large amount of data and this required more memory and CPU. This will slow down the OLTP access when a user create complex queries against the DW database. but... if your DW database will be small (few GB) maybe you can share the server, insure that your disks can handle more requests. the number of users must be consider too. if only few users access the DW database and if these users also access the OLTP databases, then a shared server is better (1 user do 1 action at a time) can you describe the estimated size of your OLTP DB and DW database? [quoted text, click to view] "Clint" <cmueller@online.nospam> wrote in message
news:5621B98C-3825-4EC5-A3F8-303EAE5E2AE9@microsoft.com... > Hello, > > We're considering creating a data warehouse with either SQL Server 2000 > (which we have now) or SQL Server 2005 (which we hope to go to at some > point > in the future). Currently, we have a MSSQL 2k server running with around > 20 - > 30 separate databases. > > My question is, is there a best practice guide to what your server layout > between transactional databases and warehouse databases should be? I'm > being > told I have to justify getting a new server to house just the data > warehouse > instead of putting it as yet another database on our active sql server. My > gut tells me having the warehouse live on the same server as the > transactional databases is a bad idea, but I can't find a definitive > document > proving this. > > Any help would be appreciated - thanks! > > Clint
Hello Clint,
Dataware house usually is organized to archive history data of OLTP Server. If data is simply archived for preservation, it is not available or organized for use by analysts and decision makers. If data is allowed to accumulate in the OLTP so it can be used for analysis, the OLTP database continues to grow in size and requires more indexes to service analytical and report queries. These queries access and process large portions of the continually growing historical data and add a substantial load to the database. The large indexes needed to support these queries also tax the OLTP transactions with additional index maintenance. These queries can also be complicated to develop due to the typically complex OLTP database schema. A data warehouse offloads the historical data from the OLTP, allowing the OLTP to operate at peak transaction efficiency. High volume analytical and reporting queries are handled by the data warehouse and do not load the OLTP, which does not need additional indexes for their support. As data is moved to the data warehouse, it is also reorganized and consolidated so that analytical queries are simpler and more efficient. Online analytical processing (OLAP) is a technology designed to provide superior performance for ad hoc business intelligence queries. OLAP is designed to operate efficiently with data organized in accordance with the common dimensional model used in data warehouses. A data warehouse provides a multidimensional view of data in an intuitive model designed to match the types of queries posed by analysts and decision makers. OLAP organizes data warehouse data into multidimensional cubes based on this dimensional model, and then preprocesses these cubes to provide maximum performance for queries that summarize data in various ways. For example, a query that requests the total sales income and quantity sold for a range of products in a specific geographical region for a specific time period can typically be answered in a few seconds or less regardless of how many hundreds of millions of rows of data are stored in the data warehouse database. It's usually recommended that you use different boxes for OLTP and OLAP (datawarhouse) for performance and manintenance purpose. You may want to refer to the following articles for more related information: Data Warehouse Design Considerations http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part5/c1761.msp x?mfr=true Microsoft SQL Server 2005 Analysis Services Performance Guide http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b 2011c/SSAS2005PerfGuide.doc MICROSOFT SQL SERVER SQL Server OLTP vs. data warehouse performance tuning http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1083557,00.html? bucket=ETA&topic=301333 Hope this information is helpful. Please feel free to let's know if you have further questions or comments on this. Thank you. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at <http://msdn.microsoft.com/subscriptions/support/default.aspx>. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for your reply, Jeje and Peter!
To answer your question, Jeje - right now we're estimating the DW size will be pushing a few gig. The current transactional DBs are well over a few gig, and that's before the data's been transformed into a more "reportable" format. As far as users are concerned, I'd estimate at least 10 - 20 concurrent users, probably more as the data going into the warehouse grows. It appears the common consensus is that the OLAP and OLTP servers do fundamentally different things, and as such it's best to put them on separate servers. Am I right with that assumption? Thanks again, Clint [quoted text, click to view] "Jeje" wrote:
> generally a DW server required a dedicated server because the hardware > optimization is different. > an OLTP required random access and the usage of the log file is high > compared to a DW database. > > a DW database required more sequential access than random access. There is > near no log usage. > the tempdb database is generally more used in a DW. > > Also when you start queries against a DW database your need more resources. > its not a "simple and quick" transaction with just few bytes to read or > write, you have to scan large amount of data and this required more memory > and CPU. This will slow down the OLTP access when a user create complex > queries against the DW database. > > but... > if your DW database will be small (few GB) maybe you can share the server, > insure that your disks can handle more requests. > the number of users must be consider too. if only few users access the DW > database and if these users also access the OLTP databases, then a shared > server is better (1 user do 1 action at a time) > > can you describe the estimated size of your OLTP DB and DW database? > > "Clint" <cmueller@online.nospam> wrote in message > news:5621B98C-3825-4EC5-A3F8-303EAE5E2AE9@microsoft.com... > > Hello, > > > > We're considering creating a data warehouse with either SQL Server 2000 > > (which we have now) or SQL Server 2005 (which we hope to go to at some > > point > > in the future). Currently, we have a MSSQL 2k server running with around > > 20 - > > 30 separate databases. > > > > My question is, is there a best practice guide to what your server layout > > between transactional databases and warehouse databases should be? I'm > > being > > told I have to justify getting a new server to house just the data > > warehouse > > instead of putting it as yet another database on our active sql server. My > > gut tells me having the warehouse live on the same server as the > > transactional databases is a bad idea, but I can't find a definitive > > document > > proving this. > > > > Any help would be appreciated - thanks! > > > > Clint >
Absolutely, if you at all can put them on different servers. I would also
recommend reading stuff on the links Peter provided, its a time well spent. MC [quoted text, click to view] "Clint" <cmueller@online.nospam> wrote in message news:D688F11F-DC77-499A-A2D9-4D032DB514F8@microsoft.com... > Thanks for your reply, Jeje and Peter! > > To answer your question, Jeje - right now we're estimating the DW size > will > be pushing a few gig. The current transactional DBs are well over a few > gig, > and that's before the data's been transformed into a more "reportable" > format. As far as users are concerned, I'd estimate at least 10 - 20 > concurrent users, probably more as the data going into the warehouse > grows. > > It appears the common consensus is that the OLAP and OLTP servers do > fundamentally different things, and as such it's best to put them on > separate > servers. Am I right with that assumption? > > Thanks again, > Clint > > "Jeje" wrote: > >> generally a DW server required a dedicated server because the hardware >> optimization is different. >> an OLTP required random access and the usage of the log file is high >> compared to a DW database. >> >> a DW database required more sequential access than random access. There >> is >> near no log usage. >> the tempdb database is generally more used in a DW. >> >> Also when you start queries against a DW database your need more >> resources. >> its not a "simple and quick" transaction with just few bytes to read or >> write, you have to scan large amount of data and this required more >> memory >> and CPU. This will slow down the OLTP access when a user create complex >> queries against the DW database. >> >> but... >> if your DW database will be small (few GB) maybe you can share the >> server, >> insure that your disks can handle more requests. >> the number of users must be consider too. if only few users access the DW >> database and if these users also access the OLTP databases, then a shared >> server is better (1 user do 1 action at a time) >> >> can you describe the estimated size of your OLTP DB and DW database? >> >> "Clint" <cmueller@online.nospam> wrote in message >> news:5621B98C-3825-4EC5-A3F8-303EAE5E2AE9@microsoft.com... >> > Hello, >> > >> > We're considering creating a data warehouse with either SQL Server 2000 >> > (which we have now) or SQL Server 2005 (which we hope to go to at some >> > point >> > in the future). Currently, we have a MSSQL 2k server running with >> > around >> > 20 - >> > 30 separate databases. >> > >> > My question is, is there a best practice guide to what your server >> > layout >> > between transactional databases and warehouse databases should be? I'm >> > being >> > told I have to justify getting a new server to house just the data >> > warehouse >> > instead of putting it as yet another database on our active sql server. >> > My >> > gut tells me having the warehouse live on the same server as the >> > transactional databases is a bad idea, but I can't find a definitive >> > document >> > proving this. >> > >> > Any help would be appreciated - thanks! >> > >> > Clint >> >>
Clint,
you would be well advised to read this document. http://www.inmoncif.com/registration/whitepapers/ttcapac-1.pdf You need to register first... Bill has been at the forefront of this stuff for 20 years now....this paper is quite old but still valid.....figure 2 will tell you why you should not mix OLTP and DSS processing on the one machine.... And, if the powers that be in your company do not believe Bill and the wealth of experience available today, well, they will deserve what they get... LOL! PS. There are lots of tech topics, papers, etc available today so you want to read through them to learn from other peoples mistakes... Best Regards Peter www.peternolan.com [quoted text, click to view] On May 7, 9:48 pm, Clint <cmuel...@online.nospam> wrote: > Hello, > > We're considering creating a data warehouse with either SQL Server 2000 > (which we have now) or SQL Server 2005 (which we hope to go to at some point > in the future). Currently, we have a MSSQL 2k server running with around 20 - > 30 separate databases. > > My question is, is there a best practice guide to what your server layout > between transactional databases and warehouse databases should be? I'm being > told I have to justify getting a new server to house just the data warehouse > instead of putting it as yet another database on our active sql server. My > gut tells me having the warehouse live on the same server as the > transactional databases is a bad idea, but I can't find a definitive document > proving this. > > Any help would be appreciated - thanks! > > Clint
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |