i recently started working for a smaller company. they purchased a new
sql server for dev and qa purpose, a developer's box. there would be
sql2k on it for .net development. it's raid 10 hardware (very high
performance) with 2GB ram.
My question is, besides the sql server service, the network admins want
to use this physical server as a test/qa server for the followings: SQL
reporting server, OLAP (for some warehouse/ETL/cube development),
IIS/web server (for intranet), and application server for vb.net
applications.
as a DBA, should i ok that? what would be the strong reasons not to do
so (dump all the stuff on the same dev/qa box)? i've never encountered
situation like this before because i used to work for larger
organizations and the application, reporting, IIS/web servers were
always seperate physical servers.
also, where should Visual Source Safe program reside ideally? on a file
server? application server? or on a database server?
any good article links would be greatly appreicated as well.
thank you guys!!
SteveIt depends. <wink>
What does your production environment look like? Your QA server should
hopefully mimick the production environment as closely as possible.
If it just for development purposes and you are not doing any major load
testing on the system, then I don't really see a huge problem with putting
all of those programs on there. I would suggest you add another 2GB of RAM
though.
As far as SourceSafe is concerned, I would probably go with a different
source control program. There are several out there that integrate with
VS.NET and use SQL Server as the storage engine. This is especially useful
if you are doing development over a VPN from home as they use HTTP or .NET
remoting and are much faster than VSS and much more sturdy (IMO).
That's my .02 cents.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||generally your dev environments are going to be a punch of "poo"
I would be OK with that.
My opinion is that each individual developer should have entire app on their
OWN dev box. (DB Server, App Server, Web Server, the works).
The QA environment and/or staging environment needs to more closely mimic
Prod.
Greg Jackson
Portland, OR|||thank you for the quick response. yes, I do understand that QA server
should mimick the production environment as closely as possilbe. in the
case, most application development on this server will be for internal
use with limited users. in terms of ram size, i know more is better but
have to limit to 2BG because the version of our sql server we use.
in terms of VSS, what other source control programs you'd recommend?
and where should they be implemented?
thank you|||> in terms of VSS, what other source control programs you'd recommend?
> and where should they be implemented?
> thank you
>
http://www.sourcegear.com/vault
I would put it on a machine that's in your DMZ or that's somehow got HTTP
access to it so you can (if needed) access it over the internet w/ the
client (Vault client or vs.net)|||You might want to look into using vmware. You'll need to increase the RAM to
4 (or more) GB though.
Bob
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1111685730.536432.312550@.f14g2000cwb.googlegroups.com...
> i recently started working for a smaller company. they purchased a new
> sql server for dev and qa purpose, a developer's box. there would be
> sql2k on it for .net development. it's raid 10 hardware (very high
> performance) with 2GB ram.
> My question is, besides the sql server service, the network admins want
> to use this physical server as a test/qa server for the followings: SQL
> reporting server, OLAP (for some warehouse/ETL/cube development),
> IIS/web server (for intranet), and application server for vb.net
> applications.
> as a DBA, should i ok that? what would be the strong reasons not to do
> so (dump all the stuff on the same dev/qa box)? i've never encountered
> situation like this before because i used to work for larger
> organizations and the application, reporting, IIS/web servers were
> always seperate physical servers.
> also, where should Visual Source Safe program reside ideally? on a file
> server? application server? or on a database server?
> any good article links would be greatly appreicated as well.
> thank you guys!!
> Steve
>|||CVS
GAJ|||"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1111687330.998586.126860@.o13g2000cwo.googlegroups.com...
> thank you for the quick response. yes, I do understand that QA server
> should mimick the production environment as closely as possilbe. in the
> case, most application development on this server will be for internal
> use with limited users. in terms of ram size, i know more is better but
> have to limit to 2BG because the version of our sql server we use.
> in terms of VSS, what other source control programs you'd recommend?
> and where should they be implemented?
> thank you
>
I would still up the RAM. Just because it's there, doesn't mean SQL will
actually use it. I am assuming you are running standard edition of SQL.
The other 2GB that SQL isn't using would be used by the other applications.
Rick|||thanks guys, i know more is always better for the RAM. but the server
price between the 2GB ram and 4GB ram is significant. the server has
been purchased. a little late for that. but it's good to know for the
next purchase.
Showing posts with label architecture. Show all posts
Showing posts with label architecture. Show all posts
Monday, March 12, 2012
Need advice on ETL architecture...
I am about to design (I think it could be considered as) ETL system and here
are my thoughts on how it should work at a very high level.
My intention in this post is to find out if there is a better way to design
this.
Data is originally entered in an as400 system.
On nightly basis, openquery from sql server is used to get all the tables
into "extract_database" as is from as400.
"User_database" takes data on nightly basis from "extract_database"
and converts data into user friendly tables. These tables could be
used by front end programmers to write tsql to drive the front end.
If we have a table called "Sales" then on nightly basis "Sales" table would
get refreshed from as400 and if there is a change in particluar sales order
then I want "user_database" to have the latest change in Sales table.
Some how I also want to document the change in Sales history. In other words
I don't want to lose any data change from day to day.
I also would like to have "history_database" which would keep track of
record changes or deletion.
In summary, "extract_database" has the copy of what exactly got imported,
"user_database" has the converted data so that front end application could
tap into it, and "history_database" is used for reporting.
For daily data refresh, I would like to daily keep track of data imported
for each table.
Are there any white papers, articles, web sites, books that address this
nightly database refresh design and best practices'
Please excuse me in advance if I missed some thing.
TIA..Read about the audit tools that you can buy to do this.|||What you are describing is a transactional data warehouse. The ETL (Extract
Transform Load) programming is just the tip of the iceburg.
"sqlster" <nospam@.nospam.com> wrote in message
news:AB2E77C4-8B79-4CC4-8B0D-2FF079239E1C@.microsoft.com...
>I am about to design (I think it could be considered as) ETL system and
>here
> are my thoughts on how it should work at a very high level.
> My intention in this post is to find out if there is a better way to
> design
> this.
> Data is originally entered in an as400 system.
> On nightly basis, openquery from sql server is used to get all the tables
> into "extract_database" as is from as400.
> "User_database" takes data on nightly basis from "extract_database"
> and converts data into user friendly tables. These tables could be
> used by front end programmers to write tsql to drive the front end.
> If we have a table called "Sales" then on nightly basis "Sales" table
> would
> get refreshed from as400 and if there is a change in particluar sales
> order
> then I want "user_database" to have the latest change in Sales table.
> Some how I also want to document the change in Sales history. In other
> words
> I don't want to lose any data change from day to day.
> I also would like to have "history_database" which would keep track of
> record changes or deletion.
> In summary, "extract_database" has the copy of what exactly got imported,
> "user_database" has the converted data so that front end application could
> tap into it, and "history_database" is used for reporting.
> For daily data refresh, I would like to daily keep track of data imported
> for each table.
> Are there any white papers, articles, web sites, books that address this
> nightly database refresh design and best practices'
> Please excuse me in advance if I missed some thing.
> TIA..
>
are my thoughts on how it should work at a very high level.
My intention in this post is to find out if there is a better way to design
this.
Data is originally entered in an as400 system.
On nightly basis, openquery from sql server is used to get all the tables
into "extract_database" as is from as400.
"User_database" takes data on nightly basis from "extract_database"
and converts data into user friendly tables. These tables could be
used by front end programmers to write tsql to drive the front end.
If we have a table called "Sales" then on nightly basis "Sales" table would
get refreshed from as400 and if there is a change in particluar sales order
then I want "user_database" to have the latest change in Sales table.
Some how I also want to document the change in Sales history. In other words
I don't want to lose any data change from day to day.
I also would like to have "history_database" which would keep track of
record changes or deletion.
In summary, "extract_database" has the copy of what exactly got imported,
"user_database" has the converted data so that front end application could
tap into it, and "history_database" is used for reporting.
For daily data refresh, I would like to daily keep track of data imported
for each table.
Are there any white papers, articles, web sites, books that address this
nightly database refresh design and best practices'
Please excuse me in advance if I missed some thing.
TIA..Read about the audit tools that you can buy to do this.|||What you are describing is a transactional data warehouse. The ETL (Extract
Transform Load) programming is just the tip of the iceburg.
"sqlster" <nospam@.nospam.com> wrote in message
news:AB2E77C4-8B79-4CC4-8B0D-2FF079239E1C@.microsoft.com...
>I am about to design (I think it could be considered as) ETL system and
>here
> are my thoughts on how it should work at a very high level.
> My intention in this post is to find out if there is a better way to
> design
> this.
> Data is originally entered in an as400 system.
> On nightly basis, openquery from sql server is used to get all the tables
> into "extract_database" as is from as400.
> "User_database" takes data on nightly basis from "extract_database"
> and converts data into user friendly tables. These tables could be
> used by front end programmers to write tsql to drive the front end.
> If we have a table called "Sales" then on nightly basis "Sales" table
> would
> get refreshed from as400 and if there is a change in particluar sales
> order
> then I want "user_database" to have the latest change in Sales table.
> Some how I also want to document the change in Sales history. In other
> words
> I don't want to lose any data change from day to day.
> I also would like to have "history_database" which would keep track of
> record changes or deletion.
> In summary, "extract_database" has the copy of what exactly got imported,
> "user_database" has the converted data so that front end application could
> tap into it, and "history_database" is used for reporting.
> For daily data refresh, I would like to daily keep track of data imported
> for each table.
> Are there any white papers, articles, web sites, books that address this
> nightly database refresh design and best practices'
> Please excuse me in advance if I missed some thing.
> TIA..
>
Subscribe to:
Posts (Atom)