|
|
You're in the
sql server notification services
group:Suggestion about using Notification services needed
sql server notification services:
could You please give a suggestion whether Notification services (NS) should be used in following situation ? We have two SQL Server databases (lets call it A and B) on the same SQL server instance. 'A' database contain common data that could be reused across various applications. 'B' database is dedicated to particular application. There is a USER table located on 'A' which is also replicated to database 'B' (but not backwards!). We also have some USER_EXT table that references table USER on 'B'. But there is no 'USER_EXT' table on 'A', because this table contains project specific data. Some of our business component (we use COM+) is intended to manage user data. In order to insert a new user, it should insert a record into USER on 'A', then wait until its replicated to 'B' and only then insert project specific data into USER_EXT (because, as mentioned earlier, this table references USER by foreign key). The problem is that business component must not insert into 'B' until data from 'A' is replicated into 'B. We don't want our business component to query database 'B' until data is replicated because it would create additional database load. We have never used NS before and thought that maybe it would suit our needs to inform business component when data is replicated. I have read some NS books online and studied Stock sample. The first impression is that NS is not intended to be used in situation like this. At least event providers and notificaton generators of sample applications are supposed to run in specified time intervals (e.g 30 seconds). But would it be efficient to run NS when this interval is short enough - in our case it shouldn't be longer than few seconds. Another issue is that notification are supposed to be delivered to some 'devices'. In our case there is no devices, but of So, the question - would You use NS is situation mentioned above or maybe You have some better suggestion ? If You would, then does the following solution looks OK for You: - there would be a trigger created on 'USER' on 'B' that fires on insert of new record; - trigger executes something like (example taken from 'Stored procedure ference') DECLARE @BatchID bigint EXEC NSEventBeginBatchStockEvents N'StockEP', @BatchID OUTPUT EXEC NSEventWriteStockEvents @EventBatchId=@BatchID, @StockSymbol=N'AWKS', @StockPrice=68.14 EXEC NSEventFlushBatchStockEvents @BatchID and inserts events into event table of NS application. - custom delivery protocol would deliver notification to some Remoting object; - business components (COM+) would be able to access Remoting object (maybe subscribe to its events or smth. like that) and get notification when data is replicated from 'A' to 'B'. It would be nice to hear if You have better ideas. Thank You for Your time and suggestions! Linas programmer
Hi Linas Kricenas,
My answer your question of using NS for this requirement is "No". NS is designed for delivering notifications..using it like a watcher on a resource would hamper the system.. say u r using NS, the delivery notification of User insert in B database should be thru a filesystem. And your com+ component polls this folder as filesystemwatcher. instead of using NS, you can very well have procedure which writes a file to a particular folder. NS runs lot of stored procedure calls in regular intervals..so this solution is not recommended. You can go for a trigger --> which fires a stored procedure and this procedure writes a file to watch folder. hth [quoted text, click to view] "Linas Kricenas" <aaa@aaa.com> wrote in message news:%23C5ZsClnEHA.3684@TK2MSFTNGP10.phx.gbl... > Hi everybody, > > could You please give a suggestion whether Notification services (NS) > should > be used in following situation ? > > We have two SQL Server databases (lets call it A and B) on the same SQL > server instance. 'A' database contain common data that could be reused > across various applications. 'B' database is dedicated to particular > application. > > There is a USER table located on 'A' which is also replicated to database > 'B' (but not backwards!). We also have some USER_EXT table that references > table USER on 'B'. But there is no 'USER_EXT' table on 'A', because this > table contains project specific data. > > Some of our business component (we use COM+) is intended to manage user > data. In order to insert a new user, it should insert a record into USER > on > 'A', then wait until its replicated to 'B' and only then insert project > specific data into USER_EXT (because, as mentioned earlier, this table > references USER by foreign key). > > The problem is that business component must not insert into 'B' until data > from 'A' is replicated into 'B. We don't want our business component to > query database 'B' until data is replicated because it would create > additional database load. We have never used NS before and thought that > maybe it would suit our needs to inform business component when data is > replicated. > > I have read some NS books online and studied Stock sample. The first > impression is that NS is not intended to be used in situation like this. > At > least event providers and notificaton generators of sample applications > are > supposed to run in specified time intervals (e.g 30 seconds). But would it > be efficient to run NS when this interval is short enough - in our case it > shouldn't be longer than few seconds. > Another issue is that notification are supposed to be delivered to some > 'devices'. In our case there is no devices, but of > > So, the question - would You use NS is situation mentioned above or maybe > You have some better suggestion ? > > If You would, then does the following solution looks OK for You: > > - there would be a trigger created on 'USER' on 'B' that fires on insert > of > new record; > - trigger executes something like (example taken from 'Stored procedure > ference') > > DECLARE @BatchID bigint > EXEC NSEventBeginBatchStockEvents N'StockEP', @BatchID OUTPUT > EXEC NSEventWriteStockEvents > @EventBatchId=@BatchID, > @StockSymbol=N'AWKS', > @StockPrice=68.14 > EXEC NSEventFlushBatchStockEvents @BatchID > > and inserts events into event table of NS application. > > - custom delivery protocol would deliver notification to some Remoting > object; > - business components (COM+) would be able to access Remoting object > (maybe > subscribe to its events or smth. like that) and get notification when data > is replicated from 'A' to 'B'. > > It would be nice to hear if You have better ideas. > > Thank You for Your time and suggestions! > > Linas > programmer > > > > > > > > >
Hi Linas -
SQLNS could probably be used in your scenario, but I don't think it's necessarily the most appropriate solution. So, I'd agree with avnrao. However, there is one point of which you should be aware. SQLNS' SQL Server event provider does periodically query a remote database to look for new or updated records. That of course does add a certain amount of overhead to your existing database. However, you don't have to use the SQL Server Event Provider. You can call event stored procedures in the SQLNS database from a sproc or trigger in your database. Look up the following sprocs in SQLNS BOL. NSEventBeginBatch<EventClassName> NSEventFlushBatch<EventClassName> NSEventSubmitBatch<EventClassName> NSEventWrite<EventClassName> HTH... Joe Webb ~~~ Get up to speed fast with SQL Server 2000 Notification Services http://www.mannpublishing.com/Catalog/BookDetail.aspx?BookID=6 [quoted text, click to view] avnrao wrote:
> Hi Linas Kricenas, > > My answer your question of using NS for this requirement is "No". > NS is designed for delivering notifications..using it like a watcher on a > resource would hamper the system.. > > say u r using NS, the delivery notification of User insert in B database > should be thru a filesystem. And your com+ component polls this folder as > filesystemwatcher. > instead of using NS, you can very well have procedure which writes a file to > a particular folder. NS runs lot of stored procedure calls in regular > intervals..so this solution is not recommended. > > You can go for a trigger --> which fires a stored procedure and this > procedure writes a file to watch folder. > > hth > > "Linas Kricenas" <aaa@aaa.com> wrote in message > news:%23C5ZsClnEHA.3684@TK2MSFTNGP10.phx.gbl... > >>Hi everybody, >> >>could You please give a suggestion whether Notification services (NS) >>should >>be used in following situation ? >> >>We have two SQL Server databases (lets call it A and B) on the same SQL >>server instance. 'A' database contain common data that could be reused >>across various applications. 'B' database is dedicated to particular >>application. >> >>There is a USER table located on 'A' which is also replicated to database >>'B' (but not backwards!). We also have some USER_EXT table that references >>table USER on 'B'. But there is no 'USER_EXT' table on 'A', because this >>table contains project specific data. >> >>Some of our business component (we use COM+) is intended to manage user >>data. In order to insert a new user, it should insert a record into USER >>on >>'A', then wait until its replicated to 'B' and only then insert project >>specific data into USER_EXT (because, as mentioned earlier, this table >>references USER by foreign key). >> >>The problem is that business component must not insert into 'B' until data >>from 'A' is replicated into 'B. We don't want our business component to >>query database 'B' until data is replicated because it would create >>additional database load. We have never used NS before and thought that >>maybe it would suit our needs to inform business component when data is >>replicated. >> >>I have read some NS books online and studied Stock sample. The first >>impression is that NS is not intended to be used in situation like this. >>At >>least event providers and notificaton generators of sample applications >>are >>supposed to run in specified time intervals (e.g 30 seconds). But would it >>be efficient to run NS when this interval is short enough - in our case it >>shouldn't be longer than few seconds. >>Another issue is that notification are supposed to be delivered to some >>'devices'. In our case there is no devices, but of >> >>So, the question - would You use NS is situation mentioned above or maybe >>You have some better suggestion ? >> >>If You would, then does the following solution looks OK for You: >> >>- there would be a trigger created on 'USER' on 'B' that fires on insert >>of >>new record; >>- trigger executes something like (example taken from 'Stored procedure >>ference') >> >>DECLARE @BatchID bigint >>EXEC NSEventBeginBatchStockEvents N'StockEP', @BatchID OUTPUT >>EXEC NSEventWriteStockEvents >> @EventBatchId=@BatchID, >> @StockSymbol=N'AWKS', >> @StockPrice=68.14 >>EXEC NSEventFlushBatchStockEvents @BatchID >> >>and inserts events into event table of NS application. >> >>- custom delivery protocol would deliver notification to some Remoting >>object; >>- business components (COM+) would be able to access Remoting object >>(maybe >>subscribe to its events or smth. like that) and get notification when data >>is replicated from 'A' to 'B'. >> >>It would be nice to hear if You have better ideas. >> >>Thank You for Your time and suggestions! >> >>Linas >>programmer >> >> >> >> >> >> >> >> >> > >
Hi Linas -
If you have the hardware and licenses, SQLNS can be put on a remote server. See the remote database deployment and scale out deployment scenarios in the BOL. But you've probably already looked into that I suspect. Another tip to minimize the impact on your existing database: If practical, you can have your event provider submit all data to be include in the notifications to the SQLNS system. That way the generator will not have to go out and query your database when the rules fire; it'll have everything it needs to generate notifications. HTH... Joe Webb ~~~ Get up to speed quickly with SQLNS http://www.mannpublishing.com/Catalog/BookDetail.aspx?BookID=6 [quoted text, click to view] Linas Kricenas wrote:
> Hi Joe, > > I have implemented event generation exactly as in Your example. > Event is generated by NS stored procedures fired by a trigger when some > replicated data are inserted into DB table. > > So, there is no need to query database periodically for event generation, > but still there is a need to do so for notification generation (I mean Event > rule query defined in Subscription class of ADF). And that's the thing I > don't like most (because of database load). > > Thanks for Your opinion! > > Best regards, > Linas > > "Joe Webb" <joew@webbtechsolutions.com> wrote in message > news:%23TWNEM%23nEHA.628@TK2MSFTNGP10.phx.gbl... > >>Hi Linas - >> >>SQLNS could probably be used in your scenario, but I don't think it's >>necessarily the most appropriate solution. So, I'd agree with avnrao. >> >>However, there is one point of which you should be aware. SQLNS' SQL >>Server event provider does periodically query a remote database to look >>for new or updated records. That of course does add a certain amount of >>overhead to your existing database. >> >>However, you don't have to use the SQL Server Event Provider. You can >>call event stored procedures in the SQLNS database from a sproc or >>trigger in your database. Look up the following sprocs in SQLNS BOL. >> >>NSEventBeginBatch<EventClassName> >>NSEventFlushBatch<EventClassName> >>NSEventSubmitBatch<EventClassName> >>NSEventWrite<EventClassName> >> >>HTH... >>Joe Webb > >
Hi avnrao,
thanks for Your suggestions! Best regards, Linas [quoted text, click to view] "avnrao" <avn@newsgroups.com> wrote in message news:ucqDl5snEHA.2140@TK2MSFTNGP11.phx.gbl... > Hi Linas Kricenas, > > My answer your question of using NS for this requirement is "No". > NS is designed for delivering notifications..using it like a watcher on a > resource would hamper the system.. > > say u r using NS, the delivery notification of User insert in B database > should be thru a filesystem. And your com+ component polls this folder as > filesystemwatcher. > instead of using NS, you can very well have procedure which writes a file to > a particular folder. NS runs lot of stored procedure calls in regular > intervals..so this solution is not recommended. > > You can go for a trigger --> which fires a stored procedure and this > procedure writes a file to watch folder. > > hth
Hi Joe,
I have implemented event generation exactly as in Your example. Event is generated by NS stored procedures fired by a trigger when some replicated data are inserted into DB table. So, there is no need to query database periodically for event generation, but still there is a need to do so for notification generation (I mean Event rule query defined in Subscription class of ADF). And that's the thing I don't like most (because of database load). Thanks for Your opinion! Best regards, Linas [quoted text, click to view] "Joe Webb" <joew@webbtechsolutions.com> wrote in message news:%23TWNEM%23nEHA.628@TK2MSFTNGP10.phx.gbl... > Hi Linas - > > SQLNS could probably be used in your scenario, but I don't think it's > necessarily the most appropriate solution. So, I'd agree with avnrao. > > However, there is one point of which you should be aware. SQLNS' SQL > Server event provider does periodically query a remote database to look > for new or updated records. That of course does add a certain amount of > overhead to your existing database. > > However, you don't have to use the SQL Server Event Provider. You can > call event stored procedures in the SQLNS database from a sproc or > trigger in your database. Look up the following sprocs in SQLNS BOL. > > NSEventBeginBatch<EventClassName> > NSEventFlushBatch<EventClassName> > NSEventSubmitBatch<EventClassName> > NSEventWrite<EventClassName> > > HTH... > Joe Webb
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |