Archiving data to a seperate database.

Hi all,
Would appreciate some thoughts on the best way to do this scenario.
This is not a large database in the number of tables, but has about 100,000
new rows per day. Data is inserted only, and no queries / SPs are being run
against the data.
Database 1 (transactional database)
Holds all current data, and all data up to 100 days old.
Database 2 (historic database)
Holds all data and is max 15 mins older than Database 1.
So, what would be the best way to keep the historic database current?
Replication, triggers, agent running SPs, keeping it within 15mins sync to
the transactional database, and deleting records older than 100 days from th
e
transactional database...
Would appreciate anyones thoughts on this...
-MarkFor 100000 rows, in general, one could write a simple job & schedule it to
run every day. The job can have a script that copies the historical data to
the destination & purges it from the source.
Anith|||My choices...
Part 1: Removing from Active db.
Use a nightly job to delete all records over 100 days (You do have a Date
Added Column? Is it indexed?)
Part 2: Moving new data to 'Historic' db
Trigger that copies the INSERTED table to the 'Historic' db.
For this application, a trigger would be have the same or less
operational impact, and the maintenance would be significantly
less. This assumes, of course, that security and connection
issues allow the use of a Trigger.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MarkieMark" <u20627@.uwe> wrote in message news:5e6ed66fd1456@.uwe...
> Hi all,
> Would appreciate some thoughts on the best way to do this scenario.
> This is not a large database in the number of tables, but has about
> 100,000
> new rows per day. Data is inserted only, and no queries / SPs are being
> run
> against the data.
> Database 1 (transactional database)
> Holds all current data, and all data up to 100 days old.
> Database 2 (historic database)
> Holds all data and is max 15 mins older than Database 1.
> So, what would be the best way to keep the historic database current?
> Replication, triggers, agent running SPs, keeping it within 15mins sync to
> the transactional database, and deleting records older than 100 days from
> the
> transactional database...
> Would appreciate anyones thoughts on this...
> -Mark|||Arnie,
My thoughts were with triggers to. The only issue I had was if the historic
database went off-line for some reason etc., that the databases would get ou
t
of sync. Haven't been able to get my head round that little problem yet.
-Marksql

Architectural Design Question ?

We currently have a number of SSAS 2000 physical cubes and a single KPI virtual cube that our finance department reports from.A colleague is prototyping a new approach in SSAS 2005 and I’m looking for some feedback.

Please see http://www.dailyware.com/SSAS2005Design.jpg for a high level overview.The lines in red illustrate custom .Net applications that push aggregated data from cubes to some other reporting source (SQL tables, XML?) that our finance department will report from.

Thought that come to my mind include: many points of failure, significant custom development required, none-mainstream, high maintenance cost….

Thanks,

Gary

Same as in AS2000 , in AS2005 you can consolidate several different cubes into a single one using new feature called Linked Measure groups.

Another point here is: you should look into providing your end users not only with static reports, but give them ability for Ad-hoc analysis of your data. There are quite a few applications talking directly to Analysis Services.
For instance, you will see great improvements in Pivot Tables in upcoming release of Office 2007.

As for the building redundancy into your system, you can use ether NLB clustering or MSCS ( microsoft clustering services ) solutions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I agree...my concern with the proposed design is the fact that it will make it difficult to use several tools coming out later this year. Moreover, our users have requirements to drill into data and this model makes that difficult.

Thanks for the input,

Gary

|||Let me ask another question....do you consider the attached design to be very non-mainstream and possibly difficult to support?|||

To save maitanance costs assosiated with having many cubes, many customers choose to create a single cube ( where possible) with multiple measure groups.

This is one of the main advantages switiching to AS2005, you can bring several fact tables into a single cube and point your reports and client appications to it.

You would start splitting cube into separate once if your calculation logic becomes complex and begins to slow down query performance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Gary,

Edward already provided detailed feedback - I would just add that, what jumped out at me was the KPI (SQL/XML) layer between the cubes and reporting applications. What is its purpose - you can easily create static reports directly from cubes, in addition to the ad hoc analytic capabilities that Edward mentioned?

What I can conceive of, in the context of KPI's, is the need for supporting metadata beyond what is provided for KPI's in AS 2005. Things like the role of a KPI within the framework of a customized Performance Management Scorecard - an example of this would be the Business Scorecard Manager:

http://office.microsoft.com/en-us/assistance/HA012225141033.aspx

Applying a .css to a .rdl file

Hello all,

I'm using Reporting Services 2005 and have been developing reports using Visual Studio 2005. There will eventually be a large number of reports, all of which will need to adhere to my company's standard look and feel.

To this end I was wondering if it were possible to apply a .css stylesheet to the contents of a .rdl report?

I've been digging round the web and can't seem to come across a definitive answer either way.

I am fairly new to Reporting Services so apologies in advance if this has been asked before.

Thanks

I believe there is no way of doing this

Apply sql2k post sp4 fix 2162 on cluster

I applied sql2k post sp4 fix 2040 and 2162 on active node of the cluster without error, the version number is 8.00.2162. When failover the cluster and double check version number again, it's 8.00.2040. That means fix 2162 didn't push to passive node. So I tried apply fix 2162 on current active node but stopped with KB904660(ENU) setup error: An error in updating your system has occurred. Can't find any log file, and OS is win2k3 enterprise edition with sp1. Anyone got similar issue?

You should get a higher build of Hotfix package. The issue you are reporting is a known bug and has been fixed in later builds.

thanks

Raja

Information on this page is posted as is and no warranty as to the accuracy of any of the presented information

Apply code to parameter before query execution

Hello, is there any way to perform custom calcuations on a parameter before
retrieving the data set? I have a table with an encrypted account number
field. The encryption is done at the application level using the .NET
encryption libraries. I need to prepare a report retrieving the transactions
for a particular account. Obviously, I cannot use the plain text account
number as the query parameter. I need to take the user input, encrypt it, and
then use the encrypted value as the parameter. I could perform the encryption
on one of the asp.net pages and pass the encrypted value as a url parameter,
but I would prefer to allow the users to type in the account number on the
report page itself. Is there a way to do this? (I am using SQL Server 2000
Reporting Services, not 2005) Thanks!No problem. When you tie a report parameter to a query parameter you can
first manipulate it in an expression. The fact that they are two different
things (query parameter versus report parameter) is very important.
In the dataset tab click on the ..., parameters tab. This is where the
mapping occurs. On the right instead of selecting the parameter select
expression.
Your expression would look like this:
= Code.Mycode(Parameters!MyParam.value)
You can do code behind report or you can use custom assemblies. Search books
online using the work Code.
There are hoops to go through with custom assemblies, mostly dealing with
permissions. I haven't done this myself but plenty of people have.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Daniels" <JoelDaniels@.discussions.microsoft.com> wrote in message
news:DE91CBA6-67D0-4EBD-A07B-9F6B770DEFF9@.microsoft.com...
> Hello, is there any way to perform custom calcuations on a parameter
> before
> retrieving the data set? I have a table with an encrypted account number
> field. The encryption is done at the application level using the .NET
> encryption libraries. I need to prepare a report retrieving the
> transactions
> for a particular account. Obviously, I cannot use the plain text account
> number as the query parameter. I need to take the user input, encrypt it,
> and
> then use the encrypted value as the parameter. I could perform the
> encryption
> on one of the asp.net pages and pass the encrypted value as a url
> parameter,
> but I would prefer to allow the users to type in the account number on the
> report page itself. Is there a way to do this? (I am using SQL Server 2000
> Reporting Services, not 2005) Thanks!

Application Event log message

Recently, I have been seeing the following event(s) in the application event
log on a number of my SQL servers. I have Googled this and get the
impression that it can just be ignored, but these are becoming more
frequent, and was wondering if anyone here could shed a bit more light on
the cause, and any possible way of preventing them. All instances running
SQL 2000 SP3 (or SP3a)
Source: SQLSERVERAGENT
Category: Alert Engine
Event ID: 322
Message: The data portion of event 19004 from MSSQLSERVER is invalid.
R - MCSE (2003, 2000, NT4), MCSA (2003, 2000), MCDBA (SQL2000)Its just a bug...
http://support.microsoft.com/default.aspx?scid=kb;en-
us;230393
Peter
>--Original Message--
>Recently, I have been seeing the following event(s) in
the application event
>log on a number of my SQL servers. I have Googled this
and get the
>impression that it can just be ignored, but these are
becoming more
>frequent, and was wondering if anyone here could shed a
bit more light on
>the cause, and any possible way of preventing them. All
instances running
>SQL 2000 SP3 (or SP3a)
>Source: SQLSERVERAGENT
>Category: Alert Engine
>Event ID: 322
>Message: The data portion of event 19004 from MSSQLSERVER
is invalid.
>R - MCSE (2003, 2000, NT4), MCSA (2003, 2000), MCDBA
(SQL2000)
>
>.
>|||Thanks for the response. I had already seen that KB article, but was
wondering if anyone had identified what causes this to start occurring. For
well over a year, I have not seen this message, then recently it started to
appear. I am leaning towards an upgrade by our ITS team of SMS 2000 to SMS
2003, and the application of the new SMS advanced client (not sure if SMS
uses SNMP to communicate with clients, and vice-versa). This seems to
roughly coincide with the start of these messages. In the meantime, I will
configure my monitoring software to stop alerting me of these messages.
Any thoughts?
R
"Peter the Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:29be01c47df4$9064e4c0$a601280a@.phx.gbl...
> Its just a bug...
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;230393
> Peter
>
> >--Original Message--
> >Recently, I have been seeing the following event(s) in
> the application event
> >log on a number of my SQL servers. I have Googled this
> and get the
> >impression that it can just be ignored, but these are
> becoming more
> >frequent, and was wondering if anyone here could shed a
> bit more light on
> >the cause, and any possible way of preventing them. All
> instances running
> >SQL 2000 SP3 (or SP3a)
> >
> >Source: SQLSERVERAGENT
> >Category: Alert Engine
> >Event ID: 322
> >
> >Message: The data portion of event 19004 from MSSQLSERVER
> is invalid.
> >
> >R - MCSE (2003, 2000, NT4), MCSA (2003, 2000), MCDBA
> (SQL2000)
> >
> >
> >.
> >|||The only thing I can think of is to go through the check
list below
http://www.microsoft.com/technet/prodtechnol/sms/sms2003/de
ploy/spgsms03/spsmsaph.mspx
Apologies for the lack of answers.
Peter
>--Original Message--
>Thanks for the response. I had already seen that KB
article, but was
>wondering if anyone had identified what causes this to
start occurring. For
>well over a year, I have not seen this message, then
recently it started to
>appear. I am leaning towards an upgrade by our ITS team
of SMS 2000 to SMS
>2003, and the application of the new SMS advanced client
(not sure if SMS
>uses SNMP to communicate with clients, and vice-versa).
This seems to
>roughly coincide with the start of these messages. In the
meantime, I will
>configure my monitoring software to stop alerting me of
these messages.
>Any thoughts?
>R
>"Peter the Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:29be01c47df4$9064e4c0$a601280a@.phx.gbl...
>> Its just a bug...
>> http://support.microsoft.com/default.aspx?scid=kb;en-
>> us;230393
>> Peter
>>
>> >--Original Message--
>> >Recently, I have been seeing the following event(s) in
>> the application event
>> >log on a number of my SQL servers. I have Googled this
>> and get the
>> >impression that it can just be ignored, but these are
>> becoming more
>> >frequent, and was wondering if anyone here could shed a
>> bit more light on
>> >the cause, and any possible way of preventing them. All
>> instances running
>> >SQL 2000 SP3 (or SP3a)
>> >
>> >Source: SQLSERVERAGENT
>> >Category: Alert Engine
>> >Event ID: 322
>> >
>> >Message: The data portion of event 19004 from
MSSQLSERVER
>> is invalid.
>> >
>> >R - MCSE (2003, 2000, NT4), MCSA (2003, 2000), MCDBA
>> (SQL2000)
>> >
>> >
>> >.
>> >
>
>.
>

Anything new to expect with SQL Fulltext searching?

would love to see improved proximity searching, specifically the ability to find terms within x number of words of another term.

thanks-
And I'm throwing in a bit more: How about the support for various languages? Will more languages be supported?|||And how about access to the Full Text index for term frequencies?|||

Hi Andrew,

we have lots of news in the next FTS. Unfortunately, the ability to specify x number of words in NEAR is not yet sure whether it is going to make it on time for the release or not. We are aware of its importancy and working on it as we can, however, there are timelines and schedules we can not move as we would want. Be sure we will try hard to push this feature into our next release. If nto possible by then, be sure it will be in the top 5 features we address immediatelly after.

Thanks.

|||Yes. The nest FTS will include a wider range of supported languages out of the box. We are currently working on the integration of >40 new word breakers (languages). If everything goes as we expect, the next SQL Server release will have a significantly improved language coverage story in FTS.|||Yes. Our next FTS release will provide tools to access the raw data of the FTIndex by Administrators only. These tools will allow you to see the content of the FTIndex and all its metadata associated. This will be extremely useful to find answers or problems associated with your Search application. Frequency of terms will be part of the results returned by these new tools (DMVs).|||

Are the new DMVs in the current June CTP or are you saying they will be in one of the upcoming 2008 CTPs? I'm very interested in this capability for use in data mining, implementation of semantic search, etc.. On that note, are there any plans for your team to address semantic search?

|||

The new improved FTS capabilities are coming in a future CTP. As to semantic search: We are looking into semantic search capabilities, but for 2008 we only provide you the thesaurus support that is available already in SQL Server 2005 (which some improved manageability).

Best regards

Michael

|||Really excited about the inclusion new FTS features in SQL Server 2008. Did the "abililty to specify x number of words NEAR" make it into the latest beta? If not, do you think it will it be in the full version release? I am working on a project that requires this feature and am hopeful that it will become available soon.

Anything new to expect with SQL Fulltext searching?

would love to see improved proximity searching, specifically the ability to find terms within x number of words of another term.

thanks-
And I'm throwing in a bit more: How about the support for various languages? Will more languages be supported?|||And how about access to the Full Text index for term frequencies?|||

Hi Andrew,

we have lots of news in the next FTS. Unfortunately, the ability to specify x number of words in NEAR is not yet sure whether it is going to make it on time for the release or not. We are aware of its importancy and working on it as we can, however, there are timelines and schedules we can not move as we would want. Be sure we will try hard to push this feature into our next release. If nto possible by then, be sure it will be in the top 5 features we address immediatelly after.

Thanks.

|||Yes. The nest FTS will include a wider range of supported languages out of the box. We are currently working on the integration of >40 new word breakers (languages). If everything goes as we expect, the next SQL Server release will have a significantly improved language coverage story in FTS.|||Yes. Our next FTS release will provide tools to access the raw data of the FTIndex by Administrators only. These tools will allow you to see the content of the FTIndex and all its metadata associated. This will be extremely useful to find answers or problems associated with your Search application. Frequency of terms will be part of the results returned by these new tools (DMVs).|||

Are the new DMVs in the current June CTP or are you saying they will be in one of the upcoming 2008 CTPs? I'm very interested in this capability for use in data mining, implementation of semantic search, etc.. On that note, are there any plans for your team to address semantic search?

|||

The new improved FTS capabilities are coming in a future CTP. As to semantic search: We are looking into semantic search capabilities, but for 2008 we only provide you the thesaurus support that is available already in SQL Server 2005 (which some improved manageability).

Best regards

Michael

|||Really excited about the inclusion new FTS features in SQL Server 2008. Did the "abililty to specify x number of words NEAR" make it into the latest beta? If not, do you think it will it be in the full version release? I am working on a project that requires this feature and am hopeful that it will become available soon.