Drawing up the spec for our Datawarehose Server

I am in the process of drawing up the spec for our data warehouse server. The
setup that I am having to cater for is a server that will hold:
1) a replicated copy of the tables that are going to be used to extract that
data from as we have 22 offices this will be 22 databases. (ranging in size
from 500 Mb to 20 Gb)
2) A staging database
3) The Data warehouse database
4) Cognos OLAP Cubes
In terms of the hardware I am looking to get a dual processor box (with the
capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to limit
sql to a portion of this leaving a good amount available for the OS and the
OLAP Cubes.
The part that I am stuck on is the RAID config. It is envisaged that the
cubes will be refreshed once a week.
So far the RAID Solution I have come up with is below:
RAID 1 - For the OS, SQL Binaries etc.
RAID 5 - For the Database files
RAID 5 - For the Transaction Logs
RAID 5 - For the Cognos OLAP Cubes
What are your thoughts on the above?
Id stay away from RAID 5 (you're gonna hear this a lot I suspect).
If you want performance, you should try to go with RAID 1+0 AT Least for the
Data and Logs.
Get RAID Controller(s) with Battery Backed Cache as well.
Hope this helps
Greg Jackson
PDX, Oregon
|||So you appear to be looking for a one size fits all solution. This might
work for a short while during the ramp up phase but most DW installations
will split the functions apart as they run into contension.
From the notes:
You are planning on having the following on one server.
22 source databases with a total space used between 11GB and 440GB
SQL replication running against all 22 source databases
1 stagging DB
1 DW DB
Cognos PowerPlay
I assume IIS and some of the other Cognos products unless you are planning
on using the network to copy the cubes or direct LAN connect from clients.
What ETL tool will you be using DTS, straight TSQL, or third party product?
If third party product where will it be installed?
With this much source data, is there an estimate as to how large the DW
database will be?
Unless you are only popluating a very small amout of data this configuration
will be overloaded inside six months and the first two to three months is
usually used for initial development and limited user testing.
Sorry for the bad news but better to know up front...
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:BA7C6084-ACD6-4B55-9125-0165108B7D9B@.microsoft.com...
>I am in the process of drawing up the spec for our data warehouse server.
>The
> setup that I am having to cater for is a server that will hold:
> 1) a replicated copy of the tables that are going to be used to extract
> that
> data from as we have 22 offices this will be 22 databases. (ranging in
> size
> from 500 Mb to 20 Gb)
> 2) A staging database
> 3) The Data warehouse database
> 4) Cognos OLAP Cubes
> In terms of the hardware I am looking to get a dual processor box (with
> the
> capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to
> limit
> sql to a portion of this leaving a good amount available for the OS and
> the
> OLAP Cubes.
> The part that I am stuck on is the RAID config. It is envisaged that the
> cubes will be refreshed once a week.
> So far the RAID Solution I have come up with is below:
> RAID 1 - For the OS, SQL Binaries etc.
> RAID 5 - For the Database files
> RAID 5 - For the Transaction Logs
> RAID 5 - For the Cognos OLAP Cubes
> What are your thoughts on the above?
>

Drawing up the spec for our Datawarehose Server

I am in the process of drawing up the spec for our data warehouse server. Th
e
setup that I am having to cater for is a server that will hold:
1) a replicated copy of the tables that are going to be used to extract that
data from as we have 22 offices this will be 22 databases. (ranging in size
from 500 Mb to 20 Gb)
2) A staging database
3) The Data warehouse database
4) Cognos OLAP Cubes
In terms of the hardware I am looking to get a dual processor box (with the
capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to limit
sql to a portion of this leaving a good amount available for the OS and the
OLAP Cubes.
The part that I am stuck on is the RAID config. It is envisaged that the
cubes will be refreshed once a week.
So far the RAID Solution I have come up with is below:
RAID 1 - For the OS, SQL Binaries etc.
RAID 5 - For the Database files
RAID 5 - For the Transaction Logs
RAID 5 - For the Cognos OLAP Cubes
What are your thoughts on the above?Id stay away from RAID 5 (you're gonna hear this a lot I suspect).
If you want performance, you should try to go with RAID 1+0 AT Least for the
Data and Logs.
Get RAID Controller(s) with Battery Backed Cache as well.
Hope this helps
Greg Jackson
PDX, Oregon|||So you appear to be looking for a one size fits all solution. This might
work for a short while during the ramp up phase but most DW installations
will split the functions apart as they run into contension.
From the notes:
You are planning on having the following on one server.
22 source databases with a total space used between 11GB and 440GB
SQL replication running against all 22 source databases
1 stagging DB
1 DW DB
Cognos PowerPlay
I assume IIS and some of the other Cognos products unless you are planning
on using the network to copy the cubes or direct LAN connect from clients.
What ETL tool will you be using DTS, straight TSQL, or third party product?
If third party product where will it be installed?
With this much source data, is there an estimate as to how large the DW
database will be?
Unless you are only popluating a very small amout of data this configuration
will be overloaded inside six months and the first two to three months is
usually used for initial development and limited user testing.
Sorry for the bad news but better to know up front...
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:BA7C6084-ACD6-4B55-9125-0165108B7D9B@.microsoft.com...
>I am in the process of drawing up the spec for our data warehouse server.
>The
> setup that I am having to cater for is a server that will hold:
> 1) a replicated copy of the tables that are going to be used to extract
> that
> data from as we have 22 offices this will be 22 databases. (ranging in
> size
> from 500 Mb to 20 Gb)
> 2) A staging database
> 3) The Data warehouse database
> 4) Cognos OLAP Cubes
> In terms of the hardware I am looking to get a dual processor box (with
> the
> capacity to take 2 more in the future). With 3 or 4 Gb of RAM, and to
> limit
> sql to a portion of this leaving a good amount available for the OS and
> the
> OLAP Cubes.
> The part that I am stuck on is the RAID config. It is envisaged that the
> cubes will be refreshed once a week.
> So far the RAID Solution I have come up with is below:
> RAID 1 - For the OS, SQL Binaries etc.
> RAID 5 - For the Database files
> RAID 5 - For the Transaction Logs
> RAID 5 - For the Cognos OLAP Cubes
> What are your thoughts on the above?
>sql

Downgrading from enterprise to standard

Hi all,
I need to go from an enterprise edition of sql to a standard edition. Has anyone been through this process? is there an easy way to do this? or do I need to script all my jobs and logins etc and rebuild them later?
thanks in advance,
DanielBackup datbases and rebuild, sucks ass thank microsoft

Downgrade DTS Package

All
Is there a way to downgrade a SQL Server 2000 DTS Package so that it can be
imported into SQL Server 7. If so what is the process to migrate a SQL
Server 2000 DTS Package to SQL Server 7.
ThanksBackwards migration is not supported... You could create a script in SQL 2k
and try to load it,,, fixing any issues that you find... Or you could simply
start from scratch in SQL 7
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"David" <David@.discussions.microsoft.com> wrote in message
news:143BA10B-AAD6-4A76-B178-726FDA00EB75@.microsoft.com...
> All
> Is there a way to downgrade a SQL Server 2000 DTS Package so that it can
> be
> imported into SQL Server 7. If so what is the process to migrate a SQL
> Server 2000 DTS Package to SQL Server 7.
> Thanks|||Try
save DTS package as Visual Basic file
open in VB and modify the following section of the code
'----
' Save or execute package
'----
goPackage.SaveToSQLServer "SQL7SERVERNAME", "sa", "your SQL 7 sa password"
'goPackage.Execute
'tracePackageError goPackage
'goPackage.Uninitialize
Run the vb program.
Thanks & Rate the Postings.
-Ravi-
"David" wrote:
> All
> Is there a way to downgrade a SQL Server 2000 DTS Package so that it can be
> imported into SQL Server 7. If so what is the process to migrate a SQL
> Server 2000 DTS Package to SQL Server 7.
> Thanks

Downgrade DTS Package

All
Is there a way to downgrade a SQL Server 2000 DTS Package so that it can be
imported into SQL Server 7. If so what is the process to migrate a SQL
Server 2000 DTS Package to SQL Server 7.
Thanks
Backwards migration is not supported... You could create a script in SQL 2k
and try to load it,,, fixing any issues that you find... Or you could simply
start from scratch in SQL 7
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"David" <David@.discussions.microsoft.com> wrote in message
news:143BA10B-AAD6-4A76-B178-726FDA00EB75@.microsoft.com...
> All
> Is there a way to downgrade a SQL Server 2000 DTS Package so that it can
> be
> imported into SQL Server 7. If so what is the process to migrate a SQL
> Server 2000 DTS Package to SQL Server 7.
> Thanks
|||Try
save DTS package as Visual Basic file
open in VB and modify the following section of the code
'----
' Save or execute package
'----
goPackage.SaveToSQLServer "SQL7SERVERNAME", "sa", "your SQL 7 sa password"
'goPackage.Execute
'tracePackageError goPackage
'goPackage.Uninitialize
Run the vb program.
Thanks & Rate the Postings.
-Ravi-
"David" wrote:

> All
> Is there a way to downgrade a SQL Server 2000 DTS Package so that it can be
> imported into SQL Server 7. If so what is the process to migrate a SQL
> Server 2000 DTS Package to SQL Server 7.

> Thanks
sql

Downgrade DTS Package

All
Is there a way to downgrade a SQL Server 2000 DTS Package so that it can be
imported into SQL Server 7. If so what is the process to migrate a SQL
Server 2000 DTS Package to SQL Server 7.
ThanksBackwards migration is not supported... You could create a script in SQL 2k
and try to load it,,, fixing any issues that you find... Or you could simply
start from scratch in SQL 7
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"David" <David@.discussions.microsoft.com> wrote in message
news:143BA10B-AAD6-4A76-B178-726FDA00EB75@.microsoft.com...
> All
> Is there a way to downgrade a SQL Server 2000 DTS Package so that it can
> be
> imported into SQL Server 7. If so what is the process to migrate a SQL
> Server 2000 DTS Package to SQL Server 7.
> Thanks|||Try
save DTS package as Visual Basic file
open in VB and modify the following section of the code
'----
' Save or execute package
'----
goPackage.SaveToSQLServer "SQL7SERVERNAME", "sa", "your SQL 7 sa password"
'goPackage.Execute
'tracePackageError goPackage
'goPackage.Uninitialize
Run the vb program.
Thanks & Rate the Postings.
-Ravi-
"David" wrote:

> All
> Is there a way to downgrade a SQL Server 2000 DTS Package so that it can b
e
> imported into SQL Server 7. If so what is the process to migrate a SQL
> Server 2000 DTS Package to SQL Server 7.

> Thanks

doubts about report deployment

hi all

i have doubts abt report deployment

if i am deploying report then what process exactly happens?

also is any entry made in IIS or is any new file created in IIS?

if i am deploying report on remote server then what process exactly happens?

what r the entries or files that r created on remote server?

plz help me so that i will understand this deployment process very clearly.

waiting for reply.

When you deploy a report to the Report Server it is not storing a file in the webserver. The report is actually stored a binary blob in the reporting services database. This is really cool, because it means that all you have to do is restore your ssrs database if there is a problem.

|||

thanks for helping me in understanding this concept

it is really great.

but i still have some doubt, if i am deleting actual .rdl file of report that i have deployed will report still be executed?

plz reply.

& thanks again

|||Now you've got me confused. How can you delete an .RDL file that's stored inside of a database? If you are deleting the file out of your Report Project, and you have already deployed it. As far as I know it will still be in the Reporting Server database.|||

Note: since the original .RDL file is also stored in the database, you can retrieve the .RDL file from the published report on the report server e.g. in report manager, report properties, edit report definition.

-- Robert

Dose Perspective to an extent improve the cube process performance?

Hi, all experts here,

Thank you very much for your kind attention.

Would please any expert here give me any guidance and advice that if a Perspective of a cube really improve the performance of the process in terms of process speed.

Thank you very much in advance for any guidance and advice for that.

With best regards,

Yours sincerely,

Perspectives don't affect cube processing in one way or another. Think about Perspective vs. Cube as about View vs. Table.|||

Hi, Mosha, thank you very much for your guidance.

So is it in a way like views for tables make retriveving data faster from a perspective rather than a large cube as what views meant to tables. Dose that sense perspective mean to cube (besides in a way to well organize information in cube for specific perspective)? Thanks a lot.

With best regards,

Yours sincerely,

|||Retrieving data from perspective will have exactly same performance as retrieving data from the cube. Perspective is mostly just a way to hide some dimensions/measures/actions/calculated members to have less cluttered and more focused view on the cube. But all the queries go against the cube.|||

Hi, Mosha, thank you very much for your guidance.

With best regards,

Yours sincerely,

Domain Name

Hi All,
I am in the process of installing SQL Server 2005. Under Service Account I am selecting the domain user account option. It asks for the username, password and the domain name. How can I find out what the domain name is?
Thanks.I am in the process of installing SQL Server 2005. Under Service Account I am selecting the domain user account option. It asks for the username, password and the domain name. How can I find out what the domain name is?
Find My Computer on the start menu, choose properties, and look at the Computer Name tab. If You are not in a domian but workgroup, you should keep the domain field empty.

Btw, you are not planning to let the SQL Server Service run as an administrator account do you? That is really NOT a good idea.