Need an expert advice

Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in inputing
data.
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication so
I'll have to import the entire table(s) nightly. Would this affect my cubes?
2. Would it be better to import the data to a separate server (staging) and
then import the data from the second server to a third serer running MSAS?
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a report
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was changed.
So if I import the data to create a cube can I then snapshot that cube since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
Thanks
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
>
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/de...es_06_17zr.asp

> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
>
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/pro.../c07ppcsq.mspx
http://msdn.microsoft.com/library/de...tbpwithdts.asp

> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
>
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.

> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/is...04/DataPoints/

> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/pro.../anservog.mspx

> Thanks
|||what is the number of rows in the OLTP database?
what is your RDBMS on this server?
servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;
there is no timestamp, but can you create triggers in your database?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
> Thanks
|||Hi ?
(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
Well, situation normal....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.
In most businesses today a data warehouse is well justified based on
profit contribution.
If you go the DW route you will need to get to incremental
extracts...if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.
Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)
I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.
Please see my beginners page for the best books on the
subject...www.peternolan.com
Best Regards
Peter

Need an expert advice

Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in inputing
data.
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication so
I'll have to import the entire table(s) nightly. Would this affect my cubes?
2. Would it be better to import the data to a separate server (staging) and
then import the data from the second server to a third serer running MSAS?
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a repor
t
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was changed.
So if I import the data to create a cube can I then snapshot that cube since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
>
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/d...>
_06_17zr.asp

> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
>
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
http://msdn.microsoft.com/library/d...ntbpwithdts.asp

> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
>
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.

> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/i.../04/DataPoints/

> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/pr...n/anservog.mspx

> Thanks|||what is the number of rows in the OLTP database?
what is your RDBMS on this server?
servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;
there is no timestamp, but can you create triggers in your database?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
> Thanks|||Hi ?
(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
Well, situation normal....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.
In most businesses today a data warehouse is well justified based on
profit contribution.
If you go the DW route you will need to get to incremental
extracts...if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.
Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)
I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.
Please see my beginners page for the best books on the
subject...www.peternolan.com
Best Regards
Peter

need advices on database server Architecture!

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.

Need advice on system performance

Hi,

I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!

What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.

THANKS MUCHtootsuite,

This is one link you might want to check out.

http://www.microsoft.com/downloads/...&displaylang=en
Also, this site has a wealth of performance information:

http://www.sql-server-performance.com/
-- Bill

<tootsuite@.gmail.comwrote in message
news:1170959288.304029.270560@.l53g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!
>
What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.
>
THANKS MUCH
>

|||Thank you

- this link shows how to do a performance audit:

http://www.sql-server-performance.c...icles_audit.asp

Need advice on expanding to Direct Attached Storage

We are currently using an application which uses SQL Server 2005. Both sit on the same box.

This month we're adding another piece of software to our network on a new box which also depends on SQL Server. Since we don't have a directed attached storage device to store (as recommended by Microsoft), we're contemplating installing SQL Server again just for this application. Of course this means that we have to maintain two separate SQL Server databases.

Any thoughts on what we should do?

Thanks

Hi,

You can install and run two or more instances of SQL Server on a server. But this means increase in number of services and maintenance (like SP upgrades, etc). But if you have different instances, you can restart an instance without affecting the other.

If you think you may have such situations and your users (or customers) are different (so why they will bother for someone else's application), you can install as a seperate instance.

But you can configure your SQL Server security to give service to two different applications.

Eralper

http://www.kodyaz.com

Nearest-neighbour logic - please help!

Hi,
Hope you can help me with this one. I'm building a search engine with
a SQL Server 2000 box in the backend. If a user gets no result for some
search but there is a close match - I want that match to be suggested
(or any others).
Now I believe that the technology to use is called: "Ternary Search
Trees" (due to a fast implementation of the Levenshtein Distance
algorithm). However in SQL Server 2000 the equivalent to sue is called
"indexed views".
My question is am I right? Can anyone out there give me a few pointers
i nthe right directions please? Any comments/suggestions/help greatly
appreciated...
Cheers,
Al.Hi,
Indexed views would have nothing in particular to do with advanced search
algorithms.
You may wish to investigate the SOUNDEX and associated functions. See Books
Online.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<almurph@.altavista.com> wrote in message
news:1163420393.594947.317930@.k70g2000cwa.googlegroups.com...
> Hi,
>
> Hope you can help me with this one. I'm building a search engine with
> a SQL Server 2000 box in the backend. If a user gets no result for some
> search but there is a close match - I want that match to be suggested
> (or any others).
> Now I believe that the technology to use is called: "Ternary Search
> Trees" (due to a fast implementation of the Levenshtein Distance
> algorithm). However in SQL Server 2000 the equivalent to sue is called
> "indexed views".
> My question is am I right? Can anyone out there give me a few pointers
> i nthe right directions please? Any comments/suggestions/help greatly
> appreciated...
> Cheers,
> Al.
>

Nearest-neighbour logic - please help!

Hi,
Hope you can help me with this one. I'm building a search engine with
a SQL Server 2000 box in the backend. If a user gets no result for some
search but there is a close match - I want that match to be suggested
(or any others).
Now I believe that the technology to use is called: "Ternary Search
Trees" (due to a fast implementation of the Levenshtein Distance
algorithm). However in SQL Server 2000 the equivalent to sue is called
"indexed views".
My question is am I right? Can anyone out there give me a few pointers
i nthe right directions please? Any comments/suggestions/help greatly
appreciated...
Cheers,
Al.Hi,
Indexed views would have nothing in particular to do with advanced search
algorithms.
You may wish to investigate the SOUNDEX and associated functions. See Books
Online.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<almurph@.altavista.com> wrote in message
news:1163420393.594947.317930@.k70g2000cwa.googlegroups.com...
> Hi,
>
> Hope you can help me with this one. I'm building a search engine with
> a SQL Server 2000 box in the backend. If a user gets no result for some
> search but there is a close match - I want that match to be suggested
> (or any others).
> Now I believe that the technology to use is called: "Ternary Search
> Trees" (due to a fast implementation of the Levenshtein Distance
> algorithm). However in SQL Server 2000 the equivalent to sue is called
> "indexed views".
> My question is am I right? Can anyone out there give me a few pointers
> i nthe right directions please? Any comments/suggestions/help greatly
> appreciated...
> Cheers,
> Al.
>

Nearest-neighbour logic - please help!

Hi,
Hope you can help me with this one. I'm building a search engine with
a SQL Server 2000 box in the backend. If a user gets no result for some
search but there is a close match - I want that match to be suggested
(or any others).
Now I believe that the technology to use is called: "Ternary Search
Trees" (due to a fast implementation of the Levenshtein Distance
algorithm). However in SQL Server 2000 the equivalent to sue is called
"indexed views".
My question is am I right? Can anyone out there give me a few pointers
i nthe right directions please? Any comments/suggestions/help greatly
appreciated...
Cheers,
Al.
Hi,
Indexed views would have nothing in particular to do with advanced search
algorithms.
You may wish to investigate the SOUNDEX and associated functions. See Books
Online.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<almurph@.altavista.com> wrote in message
news:1163420393.594947.317930@.k70g2000cwa.googlegr oups.com...
> Hi,
>
> Hope you can help me with this one. I'm building a search engine with
> a SQL Server 2000 box in the backend. If a user gets no result for some
> search but there is a close match - I want that match to be suggested
> (or any others).
> Now I believe that the technology to use is called: "Ternary Search
> Trees" (due to a fast implementation of the Levenshtein Distance
> algorithm). However in SQL Server 2000 the equivalent to sue is called
> "indexed views".
> My question is am I right? Can anyone out there give me a few pointers
> i nthe right directions please? Any comments/suggestions/help greatly
> appreciated...
> Cheers,
> Al.
>