where to install the new warehouse SQL Server?

[Amanda77]

I’m starting to build the DM design for BI data warehouse; I have two SQL server 2008 databases that will create the ETL to read from them what is the best physical structure I should consider? Should I create the new database warehouse on the same source database server? or should I install new SQL server instance for the warehouse database ? Or should I install MS SQL Server 2008 on the same server where my BI server is and create the database there? Which choice will be better for ETL best performance? by the way, my company bought Pentaho System for BI

[Jeff Smith]

Is there an option 3 - putting the DW on a 3rd server all by itself?

Otherwise, I would put the DW on the BI server, which I assume has the cubes. Sharing a server with the Transaction system is general frowned upon.

[ngalemmo]

I'm with Jeff. The more the merrier... and NEVER locate a data warehouse on the same server as the operational system.

With it understood that the BI applications (data warehouse, OLAP, Reporting, etc...) should always reside on a separate system, how many systems/servers will depend upon your expected workload and service levels.

The thing is, servers are dirt cheap and scrimping to create co-located systems opens a host of performance, backup, recovery, and availability issues. If you can't get a budget to procure sufficient hardware, there is no point even thinking about building a data warehouse. Hardware is a small fraction of the overall cost to implement and support a DW environment. The business needs to commit to a proper implementation, otherwise don't waist your time... the business is not interested.

[Jeff Smith]

By the way - management always buys the BI Software first which should bought last. It's like buying furniture first and then creating the blueprints for the house.

[Amanda77]

Thank you so very much for your answer, that's true, we already bought the system and now I'm responsible of the implementation
So virtual servers are also considered a bad idea?

[ngalemmo]

Just a personal opinion, I've always felt that operational and BI should be on physically separate and independent systems. Virtualization adds overhead and provides a point of failure common to both systems. It also doesn't help the real issue of I/O contention, which is the bottleneck for DW type applications.

[Amanda77]

Dear Jeff and ngalemmo
As you can tell im still newbie but im learning from the Two Kimball Data warehouse books which are realy great
But know I’m required urgently to give the right Server Spec for my company.
From what I understand so far, is that we should have:
1. One server for the BI application (Pentaho business Analysis and Pentaho Data Integration ), and the Pentaho already set their server Specs
2. One server for the SQL Server data warehouse (which will be connected to the existing two data sources on two SQL servers )
Is that the right “topology”? if so would you please send me the right specs for SQL Server 2008 (the largest database size we have is 35 G )
They are requesting High availability? Should I consider clustering the new SQL Server ?
Thank u

[Jeff Smith]

I think you are on the right track. Seperate servers for Transaction data and Warehouse.

35GB isn't very big so I wouldn't worry about clustering. You can do a lot with indexes, database files, and aggregations. An index can make a huge difference in performance. (Ihad a report that took an hour to run without an index and 15 seconds with the index). I would utilize partitions on the bigger fact tables even if it's not in the 100s of millions of rows as it makes it easier to deal with older data.

I would go with 4 files per file group. Seperate file groups for Facts and Dimension tables. And if you have a really big fact, I would put it in it's own file group.

The type of disk you are using can have an impact on how the files are laid out. We have a SAN which is kind of a mystery but we put the files on 1 drive. I/O is the big bottle neck. Use page compression on the tables - you want to minimize the size of the data to minimize the stress on I/O and let the CPUs do the work.

Oh, and more RAM is better than less RAM.

[Amanda77]

Thank you for the information, i like to learn more about indexing and file groups, any good resources, blogs you can recommend?
And could you be more specific please like how many processors needed And if 8 Ram is sufficent ?

[Jeff Smith]

8GB is a little anemic.

My database server has 128GB of RAM. I have about 100GB of data. I have 4 quad core processors.

More processors are better than fewer processors. And need is hard for me to assess. I'm not up on hardware.

[ngalemmo]

Jeff is better versed on the particulars of SQL server so he is a better source of specific information there.

Most definitely, the more memory the better, it allows the DBMS to cache more data. It is the one great truth that applies universally to almost every DBMS. One suggestion, if you are concerned about performance, you may want to consider a solid-state drive to store the DBMS. Given the thing is only 40GB +/- and 128GB memory drives are a few hundred dollars...

[Amanda77]

Thank you all for your help
I was able to provide good argument in front of my manger

[Sponsored content]