Reason for multiple filegroups/ files?

SQL2K
SP4
If my data is spread accross a Raid array, is there any real reason to use
multiple filegroups/ files? Will I see any performance benefit? Will I only
see it on VLDB's. If I do use them, is there a good way to group them
logically?
TIA, ChrisRChrisR
>Will I see any performance benefit?
Probably, if you put the filegroup on another PHYSICAL disk
>Will I only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
Yes , it makes sence only for VLDB
Look, you will have to investigate it carefuly, I mean how you are going
to restore in case of failure or somethinmg like that , it is not so easy
to maintain and I have my doubt that nowadays we can really gain
perfiomance benefit from creating a new file group . Perhaps you might
think about adding a new datafile .NDF and seeing how it works
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
> only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>|||Add on ; please take a look into below URL's. But to make use of the full
advantage you may need multiple disk controller as Uri suggested.
http://www.databasejournal.com/features/mssql/article.php/1439801
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=42&rl=1
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u4aAfSPKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> ChrisR
>>Will I see any performance benefit?
> Probably, if you put the filegroup on another PHYSICAL disk
>>Will I only
>> see it on VLDB's. If I do use them, is there a good way to group them
>> logically?
> Yes , it makes sence only for VLDB
>
> Look, you will have to investigate it carefuly, I mean how you are going
> to restore in case of failure or somethinmg like that , it is not so easy
> to maintain and I have my doubt that nowadays we can really gain
> perfiomance benefit from creating a new file group . Perhaps you might
> think about adding a new datafile .NDF and seeing how it works
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> SQL2K
>> SP4
>> If my data is spread accross a Raid array, is there any real reason to
>> use
>> multiple filegroups/ files? Will I see any performance benefit? Will I
>> only
>> see it on VLDB's. If I do use them, is there a good way to group them
>> logically?
>> TIA, ChrisR
>>
>|||At the very least I always recommend creating one secondary filegroup to
place all the user objects and leave just the system objects in the primary
filegroup. This makes it cleaner and easier to do piecemeal restores if
needed. From there it depends on how you use your data and what your
hardware config is like. If you want to separate different groups of objects
such as Non-clustered indexes from the Clustered / tables onto different
drive arrays you need at least two filegroups. If you know this is something
you may do in the future but aren't now it still may make sense to separate
them into multiple FG's to make the move easier down the road. If the db is
relatively small, your hardware is decent you may never see any advantages
of multiple files or filegroups.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
> only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>|||Thanks everyone, this is all as I suspected.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>

Real-time data minig

We have completed the implementation of data mining algorithms.Now we have to build an API for real time data mining.Is that possible in SQL Server?

Please explani what you mean by "real-time" data mining.

Also, by completing implementation of algorithms, do you mean you have completed implementation of algorithms that integrate into Analysis Services?

|||Closeing this thread due to no response

Really need some suggestion on SQLCacheDependency.

I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.

The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.

I am using 2.0 + SQL 2005.

Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.

Any suggestion?

Noone help? no MVP in this forum? or I should post this on .NET 2.0 forum?

Does the problem exist? or it is not a problem, just my code? in either case, I think I need some confirmation.

I have been used the cache for almost one year, I still have the same issue today. Will someone from SQL team or ASP.NET team see this post?

|||This is pretty late but have you solved your problem yet? Did you find a solution?|||

No, I can not find a way to test what is wrong, it never works consistent for me. Now I can not rely on notification from db. Every time when I change my record, I have to tell my web server to clear the relative cached items.

|||Post some code where you are inserting the item in the Cache.

Thanks,|||

string cacheKey = LinkSites.GetMappedKey(virtualPath, fileid.ToString()); // this will return a key from virtualPath
if (!String.IsNullOrEmpty(cacheKey)) frd = (FileRecordData)HttpContext.Current.Cache[cacheKey];
if (frd == null)
{
int siteid = 0;
SqlCacheDependency scd = null;
lock (_connection)
{
try
{
SqlCommand sqlcmd = new SqlCommand("select ownerid,id,uniqueid,parentid,category,name,content,dated=isnull(updated,created),created,updated,isdirectory from dbo.link_sourcestore whereid=@.id", Connection);
sqlcmd.CommandType = CommandType.Text;
SqlParameter sqlparam;
sqlparam = sqlcmd.Parameters.Add("@.id", SqlDbType.Int);
sqlparam.Value = fileid;

scd = new SqlCacheDependency(sqlcmd);
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
if (!reader.HasRows) return null;
reader.Read();
siteid = LinkRoutine.Convert(reader["ownerid"], 0);
frd = GetRecordData(reader);
}
}
catch (Exception e)
{
ErrorHandler.Report("GetCachedFileRecord 2 [" + realVirtualPath + "," + virtualPath + "]", e);
return null;
}
}

if (scd != null)

{
frd.CacheKey = cacheKey;
frd.CacheDependency = scd;
HttpRuntime.Cache.Insert(cacheKey, frd, scd, Cache.NoAbsoluteExpiration, new TimeSpan(24, 0, 0), CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(LinkCacheHandler.RemovedCallback));
}
}

It just read the record and add into cache, when cache item removed, call the static method RemovedCallback in LinkCacheHandler, LinkCacheHandler is posted below. After I restart IIS, it will work for a while, 5, 10 or more minutes, but after a while, even I set breakpoint in RemovedCallback, I don't get anything when I change the record. (when I call my clear cache method, which will remove all records from cache, it runs to the breakpoint. So the callback is fine)

public class LinkCacheHandler
{
public static void RemovedCallback(string k, object v, CacheItemRemovedReason r)
{
if (!k.Contains("system/cache.ascx"))
{
LinkSites._cacheLog += "RemovedCallback[" + DateTime.Now.ToString() + "]<br/> " + k + ((v is FileRecordData)?(" : " + ((FileRecordData)v).CacheKey) : "") + " " + r.ToString() + "\n<br/>";
LinkSites.NotifyCacheObject(k);
}
}
}

|||There are various conditions on which it wont work. Like I think you need to use the full names in your SQL SELECT queries like dbo.Categories instead of Categories. Also you cannot use the aggregate functions like SUM, COUNT and things like that. Make sure your queries are free from all those. You can check book online to find more restrictions.

I also see that you are using HttpRuntime.Cache.Insert. Try using HttpContext.Current.Cache.Insert instead.

Hope it helps!|||

Well, you can tell from my code I pasted I didn't break any of the rules. And the situation is not it never works, it just doesn't work stable.

I read an article talks about HttpRuntime.Cache and HttpContext.Current.Cache, they don't have difference, except the HttpRuntime.Cache is a little efficient.

There are many articles about how to use cache, but they are the same, just list out some general commands. I guess what I find out is how to trace the cause of the problem when the cache stop working. You know there are several steps to push the change from db to web server. So far, I havn't found any article goes deep on that.

|||

Quick note: I believe HttpContext.Current.Cache implicitly calls HttpRuntime.Cache so they're functionally identical.

Beyond that, the one thing I can mention that I discovered at first is that a callback isn't called every time the database changes, it's called every time the cache is invalidated. This obviously means that if you haven't repopulated the cache, you won't hit your breakpoint when you change your data.

This makes perfect sense: the callback is for cache item invalidation and there's no item to invalidate if the cache isn't repopulated. Nevertheless it caught me off-guard when I started working with the SqlCacheDependency (though I don't know why it should have).

I don't know if you are having a similar brain fart, but otherwise I can also suggest you might try checking out the SQL Server MSDN forums here:http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1.

I've had some pretty good luck getting questions answered there.

|||

Hmm, it doesn't look like it can happen in my code. Everytime cache item dependency changes, the cache item will be removed from cache, and it will call the callback. And when I use a object, I always try to get it from cache. If it is not in cache, I will read it and insert it in cache before use it.

When it stopped working, I found the items were still in cache, but it doesn't be removed when data changed.

|||

Well, what I mentioned is more about operator-error/misperception than faulty code, but if the item is still in the cache, you're right: you're not experiencing the problem. I know it sounds silly, but I spent about 15 minutes wondering why my callback function wasn't getting hit when data changed, and it was simply because the data was already invalidated/ not in cache.

What you say makes me a little nervous, however, since I'm using the SqlCacheDependency in a similar way. If you nail down the problem please post back here and let us know what was up so we know how to avoid potential stability issues!

|||

I hope I can, I havn't solved the problem for a year. Since I started to use the beta last year.

Another thing may not may relate to this, you can see I set my cache to NoAbsoluteExpiration and expire date is 24 hours. But if no activities on the site, all cached items will be cleared after 20-30 minutes. I had set the applicaiton pool to make sure it doesn't recycle worker porcess. This happens on both my dev and live servers.

|||

There is a know issue with .Net 2.0 and SQLCacheDependency that causes the caching to stop working after 5 minutes. Here is the link to the hot fix.

http://support.microsoft.com/kb/913364/EN-US/

Tyler

Real to datetime - how to...?

Hi,

I would like to convert real data type to datetime type. Example:
I have a real data type which is: 23,613456 (23 hours and 0,613456). I
would like to have it in hh:mm:ss format. How to do this? Can I use
convert/cas function?

Thanks for help
Rgds
Mariomachina (mm(at)home.pl) writes:

Quote:

Originally Posted by

I would like to convert real data type to datetime type. Example:
I have a real data type which is: 23,613456 (23 hours and 0,613456). I
would like to have it in hh:mm:ss format. How to do this? Can I use
convert/cas function?

There is a slight confusion here. datetime is a binary format. hh:mm:ss
is a string. But this is possible, although accuracy with the real data
type os poor. This how you do it:

declare @.d real
select @.d = 23.613456
select @.d = @.d /24
select convert(char(8), convert(datetime, @.d), 108)

The division with 24 is necessary, because a datetime value consists
of two parts, which could be interpreted as a decimal number, with
the integer parts being number of days since 1900-01-01 and the
fractional hours being something since midnight.

Of course, I have no idea whether your interpretation of 23,613456
agrees with what the above gives you.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Real Time ROLAP different then ROLAP

What is the benefit of setting the proactive cache to Real Time ROLAP. Wouldn't this be the same as ROLAP. Confused on why there is such an option.

Books online doesn't seem to answer it clearly.

Proactive caching adds the ability to automatically detect changes in source data and update Analysis Services objects appropriately. In the case of Real-Time ROLAP (which was also an option in AS 2000), the AS server can detect data changes (via events, polling or client-initiated notification) and invalidate its in-memory cache - without proactive caching, explicit processing would be needed to drop the cache.

http://msdn2.microsoft.com/en-us/library/ms175646.aspx

>>

Real Time ROLAP

OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency). This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users.

>>

|||

Thanks. I'm getting closer to understanding.

How can the latency be zero? If in the Real Time ROLAP case, reprocessing of the cube is required to modify aggregations (assuming non 0% aggregation). This would imply not zero latency, but the latency it takes to reprocess the cube. To me it seems like Real Time MOLAP is more likely to have the latest data over Real Time ROLAP. What am I missing?

|||

I don't believe that there is a standard setting such as Real Time MOLAP - with pure MOLAP, there will be some latency while the cache is rebuilt. However, proactive caching policy can cause queries to be directed to the fact table (ROLAP mode) while the MOLAP cache is being rebuilt - but this would no longer be pure MOLAP.

According to BOL below, no aggregations are stored for Real Time OLAP, so there is probably no significant reprocessing time involved when data is updated - [MS] folks could provide more detailed info:

http://msdn2.microsoft.com/en-us/library/ms174915(SQL.90).aspx

>>

Partition Storage (SSAS)

...

Real-time ROLAP is typically used when clients want to see changes immediately. No aggregations are stored with real-time ROLAP.

>>

|||

Hi Deepak

Could you please send me some code sample and "how to " for send client notification to Analysis Server?

Thanks alot.
Regards;
Rakesh

Real Time ROLAP different then ROLAP

What is the benefit of setting the proactive cache to Real Time ROLAP. Wouldn't this be the same as ROLAP. Confused on why there is such an option.

Books online doesn't seem to answer it clearly.

Proactive caching adds the ability to automatically detect changes in source data and update Analysis Services objects appropriately. In the case of Real-Time ROLAP (which was also an option in AS 2000), the AS server can detect data changes (via events, polling or client-initiated notification) and invalidate its in-memory cache - without proactive caching, explicit processing would be needed to drop the cache.

http://msdn2.microsoft.com/en-us/library/ms175646.aspx

>>

Real Time ROLAP

OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency). This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users.

>>

|||

Thanks. I'm getting closer to understanding.

How can the latency be zero? If in the Real Time ROLAP case, reprocessing of the cube is required to modify aggregations (assuming non 0% aggregation). This would imply not zero latency, but the latency it takes to reprocess the cube. To me it seems like Real Time MOLAP is more likely to have the latest data over Real Time ROLAP. What am I missing?

|||

I don't believe that there is a standard setting such as Real Time MOLAP - with pure MOLAP, there will be some latency while the cache is rebuilt. However, proactive caching policy can cause queries to be directed to the fact table (ROLAP mode) while the MOLAP cache is being rebuilt - but this would no longer be pure MOLAP.

According to BOL below, no aggregations are stored for Real Time OLAP, so there is probably no significant reprocessing time involved when data is updated - [MS] folks could provide more detailed info:

http://msdn2.microsoft.com/en-us/library/ms174915(SQL.90).aspx

>>

Partition Storage (SSAS)

...

Real-time ROLAP is typically used when clients want to see changes immediately. No aggregations are stored with real-time ROLAP.

>>

|||

Hi Deepak

Could you please send me some code sample and "how to " for send client notification to Analysis Server?

Thanks alot.
Regards;
Rakesh

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> > How does one implement real time reporting off of data from the OLTP
> > systems. Right now we run nightly extracts and load into OLAP DBs to
> > minimise locking,etc while the extracts are being run. However our
> > customers
> > now want to see real time data as soon as possible and we cannot give
them
> > access to the live OLTP systems. Are there any ways to implement this
> > efficiently ?
> >
> >
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?
Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>
|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them[vbcol=seagreen]
>

Real time Replication with ms sql

hello,

I want to create real time replication of my databaser server.I have two database srevers,one is master which having real time data and another slave,where i want to replicate data from master .

plz help me to configure solution

Thanks,

Chetan S. Raut.

what you need is Transactional Replicaton. Read about this in BOL

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm

Madhu

Real time replication over geographically divers sites

Hi,
Myself and a colleague have been charged with making two SQL servers
(SQL Server 2000 STD) replicate in real time, this is to allow a
helpdesk system to be updated in either the UK or South Africa.
Unfortunately as the helpdesk system we use is not by our own design
we have to use the same reference range.
Our fear is that a user would enter data in the UK taking the next
available reference at the same time as one in SA which would result
in clashing data!
Considering that the latency between these sites can be as high as
500ms what do you think would be our best solution?
Many thanks for any advice offered.
John...
What happens if the internet goes down?
You could run Merge replication every 5-10 minutes with no chance of data
clashes for tables that use identities.
Jim.
"John Norton" wrote:

> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...
>
|||That depends on what the cost of latency is.
For something like this you should probably look at a citrix solution where
the UK and SA users both logon to the same database at the same time.
However, you can probably do this using replication if you relax the latency
requirements to a minute or more, and then use bi-directional transactional
replication, if and only if you can partition your data - possibly by using
dbcc checkident to create different ranges in the UK and SA.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Norton" <JNorton@.DPS-integro.co.uk> wrote in message
news:a2f128be.0503310645.16813d3d@.posting.google.c om...
> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
Thanks
There are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>
|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from[vbcol=seagreen]
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not
>
|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a stored
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehouse
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to update
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> from
> once
> not
>
>

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
ThanksThere are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not[vbcol=seagreen]
>|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a store
d
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehous
e
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to updat
e
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> from
> once
> not
>
>

Real Time Data

I currently have an application which retrieves stats from a SQL database that are updated throughout the day. My application updates every 3 seconds to retrieve these stats but I found that it's quite expensive to the server's CPU when there are several clients running my application. Are there any other methods for extracting the data that won't require so much CPU. The fact that the database is being hit so much isn't good either. I have already written a webservice hoping that this would improve things but I'm not sure it has.

Hi,

is it really necessary to have real time data? Actually I mean, does the data gets updated every 3 seconds or less? If not you could, in your webservice, make use of the Cache object and have, you need SQL 2005 for this I believe, have SqlCacheDependency so that only when something in a table changes only then the Cache gets updated.

Grz, Kris.

|||The data are phone stats which are used to show calls received in an application.The application shows desks in a plan and is updated when a phone is ringing etc... This is why it needs to be in real time but I can't think of any better way to do this. The call events are stored in a table called CallRecords but I also have a snapshot table which the application uses. It selects all from this table which has the desk number, answered, missed and total calls columns which are incremented based on each event that comes through to CallRecords.|||

Hi sichiu,

The accuracy of data and performance of the server are always a pair of conflicts.

You can for better performance, you can cache data and set an expire duration. However, that will cause data not being real time.

In this case, setting the expiration duration is very important.

Another way is that you can cache data locally, and use SqlDependency class to notify your app when the data is changed through SQL notification service. For more information, please check

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

Real Time BI with SQL 2000

Where can I find some real examples or some articles/links of real time BI
with SQL 2K ?
Hello Hassan,
Check out this MSDN article.
http://msdn.microsoft.com/library/de...l-timeolap.asp
When searching for more examples look under ROLAP (Relational OLAP).
Myles Matheson
Data Warehouse Architect

Real Time BI with SQL 2000

Where can I find some real examples or some articles/links of real time BI
with SQL 2K ?Hello Hassan,
Check out this MSDN article.
real-timeolap.asp" target="_blank">http://msdn.microsoft.com/library/d...al-timeolap.asp
When searching for more examples look under ROLAP (Relational OLAP).
Myles Matheson
Data Warehouse Architect

Real Time Backup Practises

Hi All,
I was questioned by my boss to come out with a solution that can provide 100% data protection.
The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
Many thanks in advance for any feedback.
--
Bernard GohHi,
I recommend you to perform a transactional level backup every 10 minutes
time. THis will be the best approch to
have the most recent data backed up in a real time system with less cost.
If you have a seperate system with SQL Server licenses then you can do :-
1. Transactional replication with PUSH Subscription.
This will ensure that you have the latest data in your standby server
all the time. Publisher will Push the data to subscriber immediately
--
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||I'd suggest if your boss is after 100% data protection, and I would guess
100% uptime, you need to look at clustering. There is no real way to get
100% data protection through backups alone, you can get close, but
completely there. The only option really is to ensure that any hardware
failure does not cause any downtime or data loss, so fail over clustering is
probably the way forward.
Keith
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||Bernard,
This requirement is a balancing act between 100% data protection and
cost. When you say "data protection", do you mean availability? i.e.
could your data be unavailable for users for 1 day, but still
recoverable within a long time frame?
The best (and most expensive) way I can think of is to use a clustered
server with the data files residing on a SAN. You then replicate this
SAN off-site using the SAN replication technology synchronously to
another SAN. If you do not do this synchronously you will be exposing
yourself to data loss, albeit this will be small on an asynchronous
replication solution over small distances, even transatlantic, the data
loss should only be 30 secs ~ 1 minute, and with my testing I have
managed 30 secs transatlantic on HDS equipment.
Transactional replication is another solution worth exploring, but there
are many caveats in using transactional replication as a high
availability solution. Check out this article:
SQL Server 2000 High Availability Series: Implementing Transactional
Replication
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx
Other solutions will likely not be 100% protected. Log shipping is way
short of this requirement, as is backup/restore. I'm looking forward to
Database Mirroring in Yukon, however this even will not provide 100%
guarantee against data loss.
If I had the budget, I would go with a clustered replicated SAN. You may
wish to test the performance of this as replicating synchronously has
performance implications.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
SummerCoke wrote:
> Hi All,
> I was questioned by my boss to come out with a solution that can provide 100% data protection.
> The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
> My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.